By BI Team
Last updated: 09.02.2017
SQL Power BI SQL Bits

SQL Bits is a yearly conference for those who work on the information side of IT. It is usually held at a different venue each year and spans several days. Anyone involved in any aspect of data management, business intelligence and data science can find something useful at a SQL Bits event.

This year's event was extra special because it provided the launch vehicle for SQL2016, and as we have the word 'launch' in there it didn't take long for the organisers to decide on a space theme and from there - well, these are IT people after all!

No stereotypical IT geeks here then!

What's it all about?

So what happens at SQL Bits and why would I, or you, want to go?

This event is so important that Microsoft chose it to be the event in the UK to launch the latest iteration of their flagship database project, SQL Server 2016. Every well-known blogger or author in data information world based in the UK, and abroad, is at this event. It's a fantastic opportunity to meet and greet some of the greats of the data information world, putting faces to the familiar names from all those reference books and blogs that have saved your bacon on many a fraught occasion. People like Alberto Ferrari (SQLBI), Kendra Little (Brent Ozar), Itzik Ben Gan (TSQL), Chris Testa-O’Neill (BI), Chris Webb, and Joseph Sirosh (Corporate VP Microsoft Data). Not only do you get to meet them, but some of them will be taking training sessions, giving you the opportunity to learn from the best.

Where is it?

This year's event took place at Liverpool's Albert Docks, a perfect venue, with accommodation in easy reach of the venue, local bars and restaurants and a short walk into Liverpool itself if you didn't fancy staying around for the SQL Bits quiz night. However, the event has been held in Manchester and Liverpool previously.

The SQL Bits event was a 4 day epic, with days one and two given over to full day training sessions from some of the best in their field. From cap hpi; Becky, Simon, George and I booked in for a full day session on days one and two, and here's a little bit about some of those sessions.

Kendra Little

We attended the day one session taken by Kendra Little, late of Brent Ozar, an MVP and Microsoft Master, and an expert in the art of SQL Server performance tuning.

She also has interesting hair and glasses and is quite witty.

Here is my twitter exchange with her....

However, her real skill is in performance tuning and particularly, Indexing

So what did we learn?

We learnt what to look for, how to look for it and basically just to have a go! Every query problem was tackled in the same way, look at the current performance, have a performance improvement goal, try index
We learned how to look at the query statistics:

  • how long it was taking,
  • how many reads the query did,
  • the CPU time
  • ...and how to read the query plan.

We then looked at indexes in detail, working through problem scenarios

  • which will be the key columns,
  • what does it mean to have included columns - clustered or non-clustered,
  • what’s a filtered index and how can that help?

Kendra presented all this in a brilliant, easy to understand manner which kept you interested and engaged throughout. I think by the end of the day, Becky and I were filled to the brim with new found knowledge and enthusiasm for Indexing. Unlikely I know, but true.

This was an excellent day and for this day alone, SQL Bits was worth it.
Thanks Kendra.

Best things learnt:

  • If a query is highly selective then don't need to cover the whole query with the index, just be selective with the column choice for the index.
  • Don't trust the Index hint - useful for testing not reliable for production
  • Not all scans are full scans - a Top 100 query will index scan until it reaches 100. It depends on the data... understanding the data distribution is essential to designing the right query
  • Sort order in an index doesn't make any difference unless the query includes an equality expression on the sorted column
  • what's included in a clustered index will automatically be included in a non-clustered index
  • Indexing for Group by - consider pre-aggregated tables as a solution
  • don't include LOB columns in a non-clustered index
  • use dynamic management views to investigate tables which are missing indexes - Quick indexing wins!
    • sys.dm_db_missing_index_group_stats
    • sys.dm_db_missing_index_groups
    • sys.dm_db_missing_index_details

Links: http://www.littlekendra.com/

Alberto Ferrari

The session I attended on Day two was with the BI expert, Alberto Ferrari.
I've read a number of his books and I am an avid follower of his blog so I was excited to be taking part in his session on Power BI.
He also has a great name, but he did tell me that the only Ferrari he owned was about 10 cm long!

Alberto is the founder, and the principal author, along with Marco Russo, of the blog SQLBI.com.
Anyone who has ever had anything to do with SSAS or SSIS will have read at least one of their books.
To have the opportunity of a full day session with someone like Alberto is what makes SQL Bits very special.

However, for Alberto, the day could only have been more disastrous if the pod had set on fire. He couldn't connect to the wi-fi, his laptop rebooted without warning, he couldn't connect to his PowerBI site , he told us to have a break at the wrong time and got told off, things he said that weren't in Power BI were now there… Well, everyone has an off day!

The above issues aside, this was an excellent session on a subject which seems to be on everyone's agenda in the world of data, as well as R, Big Data and Data Science.
So what a great opportunity to learn from one of the best.

Key Points

If you're new to Power BI, it's all about empowering the end user, known as the third wave for BI, following on from the second wave which is self-service BI.
The third wave is nothing to do with Independence Day, but is classed as “BI for everyone”.

Power BI is a cloud based business analytic service that enables:

  • Fast and easy access to your data
  • Data discovery and exploration
  • Insights from any device
  • Ability to allow anyone to visualise and analyse data
  • Natural Language Queries (Q&A)
  • Visual Interactions
  • Easy to create dashboards
  • Quick Insights - when data is uploaded or connected to, Power BI will auto generate (if you want it to) dashboards identifying insights into that data.
  • Fully featured developer tool available offline to do more complex work – Power BI desktop
    • merge data from multiple sources
    • add together data from multiple files
    • apply complex formulae to data
    • it has its own query language (M)
    • formula language (DAX)

To find out more, visit his site, SQLBI.Com

Session Days

As well as full day sessions there are two full days of roughly one hour long sessions, meaning about 70 sessions across the final two days of the conference. Even better, the last day is completely free!
These sessions cover the whole range of topics for the DBA (e.g. SQL Server Clustering), Dev (e.g. TSQL Tips and Tricks) and BI (e.g. The Power BI hour), there simply is, something for everyone.
Unless you’d gone there expecting to see Coldplay, then you might have been disappointed!

As if that wasn't enough, on the Friday there was a keynote speech for the launch of SQL 2016 by the Corporate Vice President of the Data Group at Microsoft, Joseph Sirosh: 2016 keynote
Not only that, you had the SQL Bits Friday Night Party, a chance to let your hair down and dress like a Wookie.

The main goal of SQL Bits is to help educate as many SQL professionals as possible, and to that end most of the material for sessions at SQL Bits is available even to people who don't attend.
Take a look at some of the content available: SQL Bits Content

This was my first time at SQL Bits, and overall it was well worth the cost and effort. I would definitely go again in the future.
You get to learn from the best, network, have fun, keep up to date with what’s happening in your field of work and even meet a Storm Trooper.

What more could one ask for?! SQL Bits