Author Archives: SQLGator

Summit v.20: The Most Wonderful Time of the Year

It is hard to believe that this year will be the 20th PASS Summit.  What is even more difficult to believe is that this will be my seventh trip to the conference.  I have never before stopped to count the number until now and to be honest it feels like that number is much lower than I would’ve thought it would’ve been as it feels like a part of my soul.

PASS Summit is an annual pilgrimage to Mecca. A journey that we all make in the Autumn to dip our bodies in the ever-loving glow of SQL Server.  It is a chance to spend a week with thousands of like-minded people all with which love data. From beginners to experts, we come from all corners of the world to descend upon Seattle and mingle with Microsoft all in the name of learning and networking in the world of data.  Ahhhhh, I am excited once again writing these words and thinking about the things I will do the first week of November in the Pacific Northwest.  My bags are packed and I am ready to go but I am getting a little ahead of myself as the trip is not for another six weeks.

Will you be there?  Are you on the fence?  Do you need a gentle push?  Feel free to ask me anything here or on twitter.  Let me also remind you that next week the price will be going up so maybe you need to act today!  Register Now!  Come by and say hi and introduce yourself as I would love to meet you in Seattle.


30 Days to Success in Power BI: Day Twenty-Two Drill Down Visuals

Welcome back to day twenty-two of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day twenty-one? If so, here is the link to refresh your memory.

Now that we’ve learned how to drill down in a matrix table, let’s see how to drill down in a visualization.  Highlight the matrix that we used in days twenty and twenty-one and select the Stacked Column Chart in the visualization menu. This will convert our matrix table to the stacked column chart using the same selected fields OrderDate and SalesAmount as shown in Figure 1.

Figure 1 – Stack Column Chart

This is the same data now represented as a chart.  If we right click on one of the stacks, as shown in Figure 2, we see a Drill Down option. Notice that there is also a Show Next Level and Expand to Next Level options.

Figure 2 – Drill Down Options

  • If we choose Drill Down it takes us to Sales Amount by Year and Quarter and in this case it is 2013.
  • If we choose Show Next Level, it takes us to Sales Amount by Quarter. This is an aggregation for all years by quarter. There is a difference here between seeing a drill down into 2013 data and looking at the next level of the hierarchy.
  • If we choose Expand to Next Level, as shown in Figure 3, we see a break-down by year and quarter for Sales Amount.

Figure 3 – Aggregates by Year and Quarter

These are subtle differences but very important differences.  We can continue drilling up or down to see the various levels for this chart in much the same way that we did for the matrix table. Note that the up and down arrows are available above the visualization here as well. Stay tuned for the drillthrough in our next installment.

30 Days to Success in Power BI: Day Twenty-One Drill Down

Welcome back to day twenty-one of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day twenty? If so, here is the link to refresh your memory.  In our last post, we saw how to setup the drill down capability. Today we will see it in action.

To recap quickly, we were using the AdventureWorksDW2014 data warehouse and we added  SalesAmount and OrderDate from the FactInternetSales table.  We want to insert a matrix table onto the report as those work with the drill down feature whereas the regular table visualization does not.

Figure 1 – Initial Sales Amounts

In Figure 2, we can see drill down icons for the Matrix visualization. The down arrow on the right side of the toolbar enables the drill down capabilities for the visualization.  The three arrow icons in the left hand corner have the following functionality:

Figure 2 – Drill Down Icons

  • The Up Arrow drills up a level from the current level of the hierarchy.
  • The Double Arrow drills down to the next level of the hierarchy (as seen in Figure 3).

Figure 3 – Move Down One Level

  • The Single to Double Arrow expands down all to the next level of the hierarchy (as shown in Figure 4).

Figure 4 – All Down One Level

With this feature we can roll up and down allowing Power BI to do the aggregations along the way.  In Figure 5, we can see the bottom level showing sales aggregates for year, quarter, month, and day. At this point you can visualize the roll up for the totals.  We have now learned to use the drill down feature of Power BI. Congratulations!

Figure 5 – All The Way Down


30 Days to Success in Power BI: Day Twenty Drill Down Setup

Welcome back to day twenty of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day nineteen? If so, here is the link to refresh your memory.

Today we are going to demonstrate the drill down functionality in Power BI.  One of the key requirements for the drill down feature is the need for a hierarchy to allow us to drill up and/or down. The previous Hank Aaron data did not lend itself well to hierarchies.  One of the most common automatic hierarchies is the date hierarchy. When we have a date field in our data, Power BI will automatically generate a date hierarchy which consists of year, month, day, and quarter. That gives us four levels to drill up or down with.

Because it is common in business intelligence reporting to generate reports from the data warehouse, it seems only logical that we are going to use the AdventureWorksDW2014 version of sample data to demonstrate this functionality. The free sample data warehouse database can be download from Microsoft here.

For demonstration purposes, connect to the AdventureWorksDW2014 database and load the fact and dimension tables as shown in Figure 1 below.

Figure 1 – Load OrderDate Hierarchy and SalesAmount

Notice how OrderDate is split out into Year, Quarter, Month, and Day fields. This is a hierarchy. If we do not want to have one of these levels in our hierarchy we can remove it here by selecting the X on the right side of the field on the left side of Figure 1.  For example, maybe we do not care to get aggregate rollups for quarters, therefore we can remove the Quarter field from this hierarchy here. Thus our drill down will only consist of year, month, and day aggregations.

At the same time, I should mention that if we chose not use the drill down functionality on this particular field hierarchy and we just want to view the date as a date, we can switch back to a regular date field as shown in Figure 2.

Stay tuned to the next installment to see the drill down in action!

Figure 2- Selecting a Regular Date Instead

30 Days to Success in Power BI: Day Nineteen Take Power BI to Another Level

Welcome back to day nineteen of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day eighteen? If so, here is the link to refresh your memory.

For the next few blog posts we are going to discuss taking Power BI to another level. We are going to discuss drill down (or up) and drillthrough.

Let’s explain the terminology first!

With drill down, we can go from a high level on a report like Year to Date Sales to the lowest detail that we wish to show.  We can decide how many levels of drilling.  So let’s say our first level is corporate-wide so we might see one row for 2017 that has sales data totals for the entire corporation. If we drill down to the next level, we can see that inside the corporation we now have totals by global regions. If we drill down a little further we might see totals by country and then totals by smaller regions and so on.  Drilling up is simply going back up that chain.  This adds an entirely new dimension to our reports that eliminates the need for multiple static reports.

Drillthrough, on the other hand, provides the ability to drill through to another page or report for detail and thus filter by the field that you selected when you chose drillthrough.  For example, when we have our drill down and we get to the country detail on our report mentioned above, we could drillthrough to a second report that shows sales details and it would then filter by that country.

If this sounds a little confusing, do not fret…we will go through it in the next couple of posts.  Stay Tuned!


30 Days to Success in Power BI: Day Eighteen Choosing the Right Visual

Welcome back to day eighteen of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day seventeen? If so, here is the link to refresh your memory.

Let’s look at visuals today.  The hardest part to designing a Power BI report is deciding which visuals to use that will best convey the message of your report or dashboard. I could go into a detailed analysis of each of the default visuals, but a few days ago I came upon the following visual reference from (which is an excellent and highly recommended Power BI reference site) and thought I would pass it along instead of reinventing the wheel:

The page where the image was found ( has a download option for the PDF for your daily use.  Enjoy!

30 Days to Success in Power BI: Day Seventeen Asking Questions

Welcome back to day seventeen of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day sixteen? If so, here is the link to refresh your memory.  Now that we have some insights loaded into our dashboard on, we can use the Q&A feature of Power BI. What is Q&A?

It is just what you think it would be: question and answer, only this time your Power BI dashboard end-user asks the question of your data and the software returns an answer!!!! This sounds like some straight-up Skynet Terminators taking over the world science fiction, right? Well, of course, we are data people so now WE HAVE THE ULTIMATE COSMIC POWER!!! OK, maybe not that cool, but still!

Let’s dive in and take a look!  In Figure 1, we see our original dashboard and if you look right above the pinned insight “G and PA” you can see the “Ask a question about your data” field area where we can begin typing. Once you click into this field it will show you a different screen without the pinned visualizations and will show you some sample columns to select or you can begin typing your question.

Figure 1 – Dashboard

In essence, Q&A provides answers in the form of charts and graphs to natural language questions in real-time type-ahead fashion. Watch the progression in Figures 2 through 5 as we type ‘hr by year’ as our question. Also, notice that typing HR first gives us a 755 HR total in the chart responding to our request.  Remember that our dataset is called Standard Batting so you can see that Figure 2 shows us the HR column of that dataset as a selection. This is useful if the column was found in multiple locations.

Figure 2 – Typing HR for Home Runs


Figure 3 – Continuing to Type for Home Runs

Figure 4 – Power BI Guessing the Column is Year

Figure 5 – Now We Have an Answer to Our Question

We’ve now successfully utilized the Q&A feature of  If you did not see that feature, make sure that you have it turned on as shown in Figure 6 below. Stay tuned for the next day in this series!

Figure 6 – Turn on Q&A in the Dashboard Settings

30 Days to Success in Power BI: Day Sixteen Data Insights

Welcome back to day sixteen of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day fifteen? If so, here is the link to refresh your memory.

Now that we’ve uploaded our report to, we can take advantage of a neat little feature called Quick Insights. From the Reports screen, select the light bulb icon and generate the insights as shown in Figure 1.

Figure 1 – Quick Insights

This generates some helpful and interesting insights from our dataset. However, some of them might be useless because we know our data and might lead to a “buyer beware” situation.  In addition, some of them are obvious to baseball fans, such as Figure 2, but with your data maybe you want a correlation pointed out in the data.  We generally know that the more games you play, the more plate appearances you should have. But now we have data to back up the correlation!

Figure 2 – Correlation between Plate Appearances and Games

There were some interesting insights provided such as Figure 3. The tool found a correlation between walks (BB) and age for a certain subset of data: when Hank Aaron played for the Milwaukee Braves. Is that because he became a better, more patient hitter?

Figure 3 – Correlation Between Age and Walks (BB)

In Figure 4, we have an additional correlation, between Strike Outs and Walks (BB) for the Milwaukee Braves. Fascinating!

Figure 4 – Correlation Between Walks and Strike Outs

Keep in mind that there were many insights generated for our data set. Many of them were not useful in this example such as the number of times Hank was caught stealing by the position he played. Because we know the data, there really is not a useful need for this relationship. But we get to decide which insights are useful and save those to our dashboard by clicking on the push pin icon in the upper corner. This will prompt us to select or create a new dashboard, as seen in Figure 5. We will explore dashboards further in day seventeen. Stay tuned!

Figure 5 – Pin the Insight to a Dashboard

30 Days to Success in Power BI: Day Fifteen Publishing Our Report

Welcome back to day fifteen of our thirty-day series on Success in Power BI!  Have you forgotten where we left off from day thirteen (as we updated on fourteen)? If so, here is the link to refresh your memory. Today we will publish our Power BI Desktop visualizations to a the Power BI service in order to share with other users.

The first step to doing this is creating an account on Don’t worry, there is a free account which lets you publish up to 1GB of data, share dashboards, and  have access to cloud sources.  If you need more than that, pricing information can be found here.

On the top ribbon, select the Publish icon. You will then be prompted for your account and password as shown in Figure 1.

Figure 1 – Enter Account Information

At this point, the report will be published to the Power BI service as shown in Figure 2 and 3. That was easy, right?

Figure 2 – Publishing to the Power BI service

Our report is now in the cloud! Are you excited yet? I am! Stay tuned to see where we go next!

Figure 3 – Our Report in the Cloud

30 Days to Success in Power BI: Day Fourteen Update Power BI

Welcome back to day fourteen of our thirty-day series on Success in Power BI!  Today we will take an opportunity to switch gears as this morning I got a notification on my desktop to update my Power BI installation. This is one of my absolute favorite features of Power BI: MONTHLY UPDATES with new features!

Being a consultant for Innovative Architects, this makes my job much harder because I may tell a client one thing in March and then that is incorrect in April. I am perfectly OK with that because: NEW FEATURES!!!!

So if you are following this blog in real-time, you are going to want to update your Power BI as well to the March 2017 version to prevent any incompatibilities with my version.  Look at the features and improvements in Figure 1.

Figure 1 – New Features

Check out the last item on the list…split column by delimiter.   Does that sound familiar?  It should as we did that manually using calculated columns in Day Ten. This is the perfect example of what I referred to earlier at IA. I may have told a client that you could not easily split a column by delimiter. Now you can. Isn’t that fabulous?  Thanks Microsoft!  Below is the video from them on the new features.

Stay tuned to see where we go next!


%d bloggers like this: