Use Simple SQL to Manage your data in Power Pivot

When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from the database. The problem is that you need to know SQL to be able to do this. So right from the start I decided this second approach wasn’t a very useful tool for the typical Excel user.

Recently though I have come across some real life challenges that I needed to solve. I have a very large “master” workbook that has all my measures and all my data (for several years) loaded – it is almost 400 MB and contains more than 40 million rows of data. I keep a master copy so I don’t have to re-write all these measures every time I have a new request for a report. But on the flip side, if someone wants a report that say contains just 10 products, I don’t want to send them the entire 400 MB workbook.

I started off by writing bespoke views containing a subset of data directly in SQL Server and then re-pointing my workbook to this new view. But I soon realised that this was going to create a mess in my SQL Server DB. I know I can manually select each product I need from the filter list in the table editor, but this is hard work if you have more than a couple of products.

So what to do?

Then I remembered the alternate SQL Query Editor and thought this may be the solution. And indeed it is. This is how I go about solving this particular business problem. It is easy to learn – just follow the pattern I use below.

First clone your Master Workbook

Think of this Master Workbook as a growing asset you incrementally build and improve as a Self Service BI champion. Load up all your data, create the relationships and write all your useful and reusable measures in this workbook. Now you have your master, you can take a copy of this workbook and give it a new name each time you have a new reporting need. Clone the master and give it a new name.

Then refresh the clone copy with a sub set of data

This is where you get to use a simple SQL WHERE clause to tell Power Pivot to bring in ONLY the sales and products you need. I will be using the SQL Statement as follows.

WHERE productkey IN enter, a, list, of, your, product, keys, here, separated, by, commas

SQL is a very simple language and it is easy to learn. There are lots of online resources to learn from. In this case, you don’t need to know SQL at all, you can just copy the pattern I will show you. SQL uses “English recognisable language” so you will quickly work out what it is doing. In the most simple form, a SQL statement goes like this (this is the syntax I will be using in this demo).

SELECT some columns of data
FROM   some table
WHERE  some condition exists

Create a list of the product codes you want to include in your new workbook

Now you will want to create a list of products that you want in your report. To do this, I create a list of the product codes in my Excel workbook that I am using. I copy the list of codes into a blank area of the spreadsheet and then use them to create the last part of the WHERE clause shown above (just the IN bit). I have a little macro to help with this which you can copy and use if you want, or you can just type it out manually.

Here is the VBA code

Sub CombineCommasSQLCode()
    Dim Cell As Range, mySelection As Range
    Set mySelection = Selection
    For Each Cell In mySelection
        If Cell.Address <> mySelection.Address Then
            If Cell.Address <> mySelection.Range("A1").Address Then
                If Cell.Value <> "" Then
                    mySelection.Range("A1").Value = mySelection.Range("A1").Value & ", " & Cell.Value
                    Cell.Clear
                End If
            End If
        End If
    Next Cell
    mySelection.Range("A1").Value = "in (" & mySelection.Range("A1").Value & ")"
End Sub

So now you have your IN clause stub, it is time to go to your sales table and limit the data that is returned to Power Pivot to this list of product codes. To do this, go to Power Pivot, select the correct table (sales in this case) and then click on DESIGNTABLE PROPERTIES. From there you can change the data fetch method from “TABLE PREVIEW” to “QUERY EDITOR”.

Then you just add the WHERE clause to the existing SQL Statement. If you need to find the field name for your productkey, it is the same as the table column name (assuming you didn’t change it).

Repeat for the product table

Once you have done this for your Sales table, you should repeat the process for your products table. No point bringing in product data for products that are not in your sales table.

I have produced a video showing the end to end process below.

The website I used in the video to format the SQL code can be found here => http://www.dpriver.com/pp/sqlformat.htm

Hope you like this trick and find inspiration for other ways to use the Query Editor. I would love to hear back from you how you use the Query Editor – post your comments below.