I have learnt a lot over the last year working full time as a Power Pivot professional. And some of the things that I dismissed as “not useful” very early on have turned out to be very useful indeed. The second option in the Table Import Wizard is a case in point.
Very early on I dismissed this as being “not useful” for Excel users of Power Pivot (although I could clearly see the benefit for SQL professionals that could already write SQL code). But I have to say I was wrong about this and I think there is a lot of value in this second option for Excel users.
I was re-reading the excellent book by The Italians over the weekend. I find that reading quality books for a second and third time is a worthwhile and rewarding pursuit. Often things that didn’t mean a lot the first time you read will have a new meaning and relevance the second or third time around. Anyway when I got to the part about importing data from a database, there in front of me was reference to the “Design Button” which appears on the “less preferred option 2” import screen.
I have been using this less preferred option 2 screen for some time now (read about other tricks I use here) but I have never noticed this button before.
So how do I use this button?
This button is very useful when you want to import data from a database but you don’t have access to an IT buddy to help you out by writing the SQL code you need. Now it is not the greatest user interface tool that you will ever encounter, but it is very do-able for the average Excel user.
Let’s consider the following scenario using Adventure Works reporting DB for SQL Server. In this example, I don’t want to bring in the full low level transactional data from the source database but instead I want to bring in a summary table for a defined period of time.
eg Give me a table that summarises sales by day and by territory, and also how many invoices there were for the year (say 2003). So I really want a table something like this to put into Power Pivot.
Here are the wizard driven steps to complete this task.
Go through the normal “Import from Database” steps as you normally would for Power Pivot. Select your server name and database name in the wizard.
Select the previously unloved option 2
Give your query a name (this will be your table name inside Power Pivot) and then click the Design button.
Expand the table navigation pane on the left and select the columns of data needed. In this case we want order date, Territory Key, Order Number and Extended Amount.
In the selected fields section (top right), click the “Group and Aggregate” button and then select the “group by” actions needed for each field. We want to group by order date and territory key, then do a distinct count on order number and sum up the extended amount. These are not difficult concepts for Excel users to grasp even if you don’t know database design or the SQL language. “Group By” simply means “give me a sub total at this level”.
Finally you apply a couple of filters to limit the results to the calendar year 2003.
After you click OK, the Design Wizard returns the appropriately written SQL code that will be used to fetch the data you need from the database.
And this SQL code is not hard to read or understand. Once again, well within reach for the average Excel user that is learning Power Pivot. In fact you can even change the SQL code here (to change the returned column names as an example).
Change this part
SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Count Distinct_SalesOrderNumber]
,SUM(fctSales.ExtendedAmount) AS Sum_ExtendedAmount
to this
SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Total Orders]
,SUM(fctSales.ExtendedAmount) AS [Total Sales]
Then click Finish and finalise the import of your summary table.
From here you can go ahead and bring in more tables of data, or start working on your data model as required.
Personally I think any Excel user that is regularly importing data from a database should invest a small amount of time learning how to read and write the SQL language. 12 months ago I classed my SQL skill as “knows what it looks like, can read it, but not good at writing it”. 12 months later having used SQL quite a bit for Power Pivot data imports, I class myself as “competent”. So if I can do it, you can do it too. And UI tools like the one above can help you get started, and you will be writing your own SQL code without help before you know it. Then there will be no stopping you.
Matt Allington is full time Self Service BI professional based in Sydney Australia
