Building a Flexible and Efficient Client-Side PowerPivot Solution in Excel 2013

I considered doing this as multiple blog postings but then as I worked on I thought of how much I hate it when I see “To be continued ….” on the screen at the peak of a TV show … Then having to wait a week (if I do in fact tune in then) to see the rest.

So this is going to be an end-to-end solution in one “Mother of All” blog posts.

First order of business is that the sample database used in this example is available at http://powerpivotsdr.codeplex.com/downloads/get/90939 . The database is AW_CompanySales.accdb.

What am I going to write here?

1. Make an argument for development of Client-Side Excel solutions that takes advantage of the PowerPivot technologies.
2. Detail an end-to-end PowerPivot solution for use on a Client version of Excel 2013 that uses VBA to change the query settings of Data Connections using the Object Model of Excel VBA and refresh them accordingly at run-time.
3. Detail “Best Practices” for data connections and maintenance that will support this particular approach to a PowerPivot/Excel solution.
4. Detail a surprisingly simple and flexible way to refresh your PowerPivot cache “on-the-fly” that produces a solution that :
   1. Uses direct connections to existing data sources (SQL Server databases, SQL Azure, SSAS Databases, SharePoint PowerPivot Services, Access files, Oracle, MySQL, Excel Spreadsheets, Web Services, Power Query sources, and existing corporate Data Warehouses and Data Marts) rather than creating yet another data warehouse in SharePoint PowerPivot Services – which is great in my opinion – but to me is nearly exclusively an Enterprise opportunity rather than a Departmental or personal needs. Basically this technique is PowerPivot solutions for the other 98% 😉 ..
   2. Has a very small template for distribution through SharePoint Doc libraries or simply from the user’s desktop
   3. That could easily be used as a source for Enterprise SharePoint PowerPivot Services
   4. That lives in a client Excel file and thus has availability to all the capabilities of VBA as well as Workbook and Worksheet and Project Protection
   5. Allows for the integration of PowerPivot data into larger more complex Excel models that really only have a presence in the Windows Client environment with all the power that provides.
   6. That provides fast refresh times using smaller data sets as opposed to when the Workbook has to import and store ALL data from ALL tables in RAM before it can do any analysis inside Excel.
      

Here is what we are going to build:

1. A Workbook where the user chooses the Calendar Year they wish to analyze and Excel refreshes the PowerPivot cache using VBA returning ONLY the data for the year selected directly from the data source tables in an Access database.
2. A Pivot Table that includes a calculated field (measure) using the CALCULATE() DAX function created in PowerPivot to calculate sales from Canada:

1. A Query Worksheet that includes Product and Country names supplied by RELATED() DAX functions in the core table of the model driven by 2 Slicers:

Important : Even if you are an experienced PowerPivot user and you know how to make connections to external data pleeeaaase follow ALL the steps I detail here exactly regardless. There is method in my madness that will become obvious in the end. Dick

 

The SharePoint Vision of Excel

There sure is a lot of exciting talk on the Internet and at seminars about the incredible new capabilities in Excel 2013 using the PowerPivot Add-in. The bulk of this is based on a plan to create Excel Models using PowerPivot and then subsequently publishing them through Microsoft’s SharePoint to SharePoint’s PowerPivot Services into a data warehouse dedicated to the creation of Web-based reports with lotsa pretty pictures, charts, maps and colours. This is a really exciting prospect for the Enterprise developer no doubt and looks like a great technology.

But We Still Need the Desktop and We Still Need Good Data

But Excel on the desktop will still be around and in fact without people knowledgeable in client-side Excel there will NEVER be any content for the Web-based PowerPivot/ PowerView world. It all starts in an Excel file, it all needs people like you to build the very content they will be publishing. It also involves getting access to the data you want to analyze in a location and a format that you can use. Frankly this is the weakest link of the whole PowerPivot story, and one that I will be focusing on here in this blog.

But why would people discourage you from using PowerPivot as a strictly client-side offering?

I don’t know. I have my suspicions of course, but don’t care to discuss them now ;-).

I, Dick Moffat, am driven by the needs of my clients and potential clients than the need for Microsoft to support decisions they have made about the direction of Office.

So Microsoft is going full-tilt to pull as much as possible into their SharePoint/Cloud environment and PowerPivot appears to be a key player in this. But at the same time there is still a product called Excel and I hope beyond all hopes that Microsoft will continue to respect the power and reach of client-side Excel alongside their browser-based offerings. The irony is that without client-side Excel there is no content for SharePoint PowerPivot Services. So the first thing that I think has to be created is a strong, capable, useable client-side PowerPivot “story”. And that’s what I’m going to show you here J.

This solution is for the other 98% of Excel users out there ;-).

Let’s Build a Simple PowerPivot Client-Side Solution

With the shipment of Excel 2013 Microsoft has integrated the PowerPivot Add-In Caching Technology natively in Excel and has made this available to Excel data users as an alternative to the traditional PivotCache. Superficially this is a classic case of “six of one half a dozen of the other” if you wish to simply provide the same functionality using this new technology rather than the old one. In fact it is pretty easy to convert an existing Excel Pivot Table over to a PowerPivot driven one in just a few minutes.

In this blog I will demonstrate how to use the new technology as the source for your client-side pivot tables – and how you will get exposed to a staggering set of capabilities that you never realized could be possible – let alone inside little old Excel J.

FYI I am building this example using32 bit Excel 2013 on a 32 bit Windows 7 VM with 4692 MBs assigned. There is no doubt that everything would be much faster with a 64 bit OS and software but it will work with 32 bit, it’s just that sometimes during development you may have to close and reopen Excel to move forward – not a totally unfamiliar task for Office Developers?.

Getting Your External Data

Our first task is simply to get at the data we are going to use. This may not seem as sexy as pivoting data and creating cool DAX functions but without this none of that other bit is going to happen beyond a nice demo, I’m sorry to say.

For several versions now Excel has provided the ability to connect directly to any data source that ships with an OLEDB Provider and use this data as an External Data Source for their Pivot Tables. This continues to be available in Excel 2013 when used in Windows with the full client version that you all have sitting on your desktops and I assume will only be enhanced in future versions. I know that many Excel Pros have a passing familiarity with Pivot Tables while many of you have delved into them and like me many of you have spent years working around the quirks and limitations of this fantastic capability. Trust me when I say that with the release of Excel 2013 with the PowerPivot Add-In it is time to get into this right away.

So let’s lay the groundwork here for creating a PowerPivot based Pivot Table:

1. We need a well-structured relational database as a data source first and foremost. I find the simplest available example is any of the Adventure Works sample databases available on-line from Microsoft. In the “real world” you must eventually connect to your own corporate data sources (something I will discuss in later blogs at length) but this will show you the core skills right away.
2. AW is available in both Access and SQL Server formats out there. Both will work basically the same for this example but for convenience I will use the Access version.
3. Create a Folder on your PC or on your network and place the Adventure Works Access database in it.
4. Open Excel 2013, making sure that the PowerPivot Add-In is installed and chosen as a COM Add-In such that it shows up on your Ribbon Bar.
5. Start with the blank Excel file you see when you open the application.
6. Save the blank file to that same folder that the Access file is in (for good order sake) and call it something like PP PT Data Example.xlsx.

You are now ready to go…

Important: It is absolutely necessary for this example to create your Connections to External data from the Excel UI rather than from inside the PowerPivot interface. You could also create your Connection using Power Query btw but NEVER through PowerPivot if you want to use the technique I am showing you here. You must also NEVER change the name of any PowerPivot table or of any PowerPivot Field inside the PowerPivot UI
I guess a “Trust me” Is in order here(?)

Choose the Data Tab on the Ribbon and you will see on the left hand side the “Get External Data” choices. You will also see a button that says “Existing Connections”. When you make a “Connection” to an external data source Excel creates a “Connection” in which all the properties required to successfully bring in External data are stored. This will live inside the file and will travel with it. It is not necessary to create a DSN or an ODC file to make this happen and in fact I never use either with my Excel models so that they are in fact totally portable. All I ever need are “Connections” inside my Workbook.

So let’s get our data from our Access file. First click on “From Access” on the Ribbon and you will see this dialog:

When you open the “AW_Company Sales.ACCDB” file you will be shown a list of Tables and Queries. Scroll down and select the table “Product” at the bottom and click “OK”.

This table simply lists the products available in the application. This is the “one” to the “many” side of a relationship with the “Total Sales” table that references the ProductKey from the Product table that applies to each sales line in the “Total Sales” table. “Total Sales” has more than 1 million records so it won’t fit on an Excel worksheet. But this is ultimately no problem with PowerPivot in Excel as we’ll see.

In the meantime let’s just open “Products” and see what happens.

The next dialog you’ll see is this:

This is very similar to the dialog for this procedure that’s been available in Excel for several versions. It does however have 2 very interesting new features that we will take advantage of thanks to PowerPivot.

Only Create Connection

We can choose to use this Access table as the source for a Pivot table directly from this dialog but in previous versions of Excel you would choose Table or Pivot Table or Chart to reveal your results in the Excel Worksheet. But in this example we are going to select “Only Create Connection” and we are also going to\

Add this data to the Data Model

If you check this option Excel will use the PowerPivot cache to store the data returned by this Connection, thereby taking advantage of the compression in that technology and making this new data available to all the capabilities of PowerPivot (Pivot Table, DAX functions, KPI’s, etc., etc.). You MUST check this option for every Connection you create in Excel 2013.

But stop !!

Before clicking “OK” let’s click on the “Properties” button in the lower left and make one change to our new “Connection”.

Connection Name

At the top of the dialog change the “Connection Name” to “Product”

So click “OK”. Then click “OK” one more time.

And What Do You Have?

You are now staring at a blank Worksheet inside what appears to be a blank Workbook… Well not exactly. The data from the Product table is now copied into the PowerPivot cache inside the Workbook and is available for use in many ways.

Choose the PowerPivot Tab of the Ribbon and click on the green “Manage” button on the left:

And you will see the results of your effort in the PowerPivot UI showing a table called “Product” with 606 records:

If you save the file at this point either in the Excel or PowerPivot UIs this table of data will be saved in compressed format to the disk. The idea of compression in PowerPivot is covered in many other places but suffice to say it shrinks a whole lot ;-). This is a good thing.

What Am I Going To Do With This Table Now?

Oddly we’re actually not going to do anything to this table at this time. It is now up in the PowerPivot data cache and we will use it, but not just now.

Just save the apparently empty file to disk.

Let’s Get Some Sales Data

Now we’re going to get some REAL useful data into our model so we can analyze sales in various ways (including by Product BTW).

So:

1. Choose the Data Tab.
2. From Access.
3. Hunt down our Access database.
4. Select the table “Total Sales” from the list of queries and tables.
5. Set “Only Create Connection” and “Add this data to the Data Model”
6. Choose “Properties”
7. Let’s make some changes here:

Connection Name

Set Connection Name to “Total Sales”.

Connection String

This string of text includes all the settings required by the “Microsoft.ACE.OLED,12.0” Provider to connect to the particular database you selected. If you had chosen to create a SQL Server Connection this box would reflect that you would be using a different Provider with a whole different set of settings. But these are nearly totally provided for you automatically.

Command Type

The key change to make here is to change the Command Type to “SQL”

Command Text

Change the Command Text to “Select * from [Total Sales]” (without the quotes). It is absolutely necessary that you define the CommandType at time you create the Connection. It cannot be changed later and needs to be “SQL” for reasons that will become obvious later. Make it so !!

It is good form to put the square brackets around the table names here, by the way, because sometimes the names you see will be considered by the Provider as some type of internal special term and also you need them if there is a space in the table name anyway

The result will be exactly the same as if you selected just the Table but further on you will be using this to filter your data inside the Connection itself by using “Select” statements that could in the end be as complex as you could ever need. Keep in mind that in this example our Select statement is standardly simple and would work with just about any relational data source out there but you have to make sure that any SQL statement you put in the Command Text box is in the correct syntax for the product you will be extracting your data from.

1. Click OK twice

In the lower right you will be able to watch the records count up as it is “Retrieving data …” until 1,370.000. Lotsa data !

Now save the file to disk.

Important: Note that because you chose to create a SQL select statement as the source for this Connection the name of the table in PowerPivot is “Query” and not “Total Sales”. Every subsequent new table created from a SQL statement will be named sequentially “Query1″, Query2”, etc. DO NOT CHANGE THE NAME OF “Queryxx” TABLES IN POWERPIVOT. This is kinda unfortunate, but the fact is that if you change any table or field name of PowerPivot Tables in the PowerPivot UI you will NOT be able to make any further changes to the data source of that table from inside Excel and the macros we are going to build for this file will simply NEVER work again – not good …

 I will prove that this is a necessary evil but not an impossible one to live with. You just have to know the data in each of your query tables. Please trust me on this one for now… you can disagree with me later if you wish.

One More Table

We need one more table in our Model. That is a table that lists all dates throughout the history of the database. Sure there are other ways to get this info from the [Total Sales] table but work with me on this:

1. Choose the Data Tab.
2. From Access.
3. Hunt down our Access database.
4. Select the table “SalesDate” from the list of queries and tables.
5. Set “Only Create Connection” and “Add this data to the Data Model”
6. Choose “Properties”

1. Set Change the Connection Name to “Dates” but leave it as a table.
2. Click OK twice…..

Now save your file and go have a look at how big it is on your drive … mine is 29 megabytes in size. But believe me that is a major compression level compared to storing this data inside Excel Worksheets (which would actually not be possible) or even if you pull the data up into the traditional Pivot Table cache. And we are going to be able to do so much more with this as well !!

I hope you’re proud that you now have a file that looks like it’s empty but actually is 10’s of megabytes in size. Well done !

So What Now?

Remember that my key focus of this blog is to help you with the acquisition of Data more so than the creation of sexy Pivot Tables. “You can’t have one without the other” (as the saying goes. Another phrase that would apply is “You gotta walk before you run” or “Don’t put the cart before the horse” J etc….

But we WILL produce some sexiness once we have all the skeleton of our solution in place – Have no fear.

Let’s See Our Data

Do you remember how we chose NOT to drop the data for our Connections onto the Worksheet as a Table or Pivot Table? We will do that now of course but treat the data connection portion of all solutions as one process and the using of this data in Excel as the second step.

We are still working on the first step however and we have more to do there.

Set Up Our Relationships

We have 3 tables in our PowerPivot cache at this point. These are individual tables from a single source but they could be from multiple sources if that’s what you’re working with.

Important Point !! …

These tables demonstrate the core theory behind relational databases that is correctly applied in the Adventure Works database. That principal in laymen’s language states “have one piece of information about your data in one place and one place only”. Then you use the “keys” of your tables to make the connections between the tables. This is the basis of all Relational Databases as envisioned by the great Dr. Edgar Codd only 50ish years ago.

The “One to Many” Relationship

These tables are examples of “One to Many” relationships.

That means that in the large table in PowerPivot (“Query1” based on the “Total Sales” table) there are potentially “many” records for any product that AW sells. If they only sell a Product once maybe they should drop it from their offerings?

But like any good relational database AW has a master product table where EACH product is listed ONCE and ONCE ONLY. Then when you create new sales of a specific product the record for each detail line of the sale includes a single number (or “key”) that is the value in the “key” field of the “parent” table of products. In our example the Product table has a primary key for each record called “ProductKey” and any records in the Total Sales table of a specific product has that same number in its own “ProductKey” field. A good database will force that this relationship ismagaed bth in the Unser Interface of applications that would “feed’ the table, but they also can enforce this relationship at the table level so it is impossible to add a ProductKey that is not in the Products tables. This prevents GIGO (Garbage In = Garbage Out).

So the core advantages of this “One to Many” relationship are:

1. “Many” side tables are smaller
2. The “One” side table can be changed and all changes will automatically be available for reporting and analysis of the data in the “many” one.
3. The “One” side table can have multiple field describing the Product (like multi-lingual names) that do not have to live also in the “many” table.
4. The “one” side table can also be the “many” side of another relationship (like with Product Sub Categories which can then have a one to many relationship with a Product Categories table). Just brilliantly simple.

So we have nice “clean” data to play with here (something that is not always the case and about which I will talk another time).

But we have to explicitly tell PowerPivot that we wish to use some of these one to many relationships that are built into our data. So:

1. Go to the PowerPivot UI
2. Select the Design tab
3. Click “Create Relationships”

1. In this dialog you elate the 2 sides of the “One to Many” relationship. You can choose to start with either the one or the many side table and PowerPivot will figure out what you mean. If however this relationship is not acceptable (i.e. there is no unique key in the product table – which CAN happen if your source is not “clean”). But it will work for us here.
2. Click OK

By establishing this relationship when you bring the EnglishProductName field from the Products table into a Pivot Table or Report it will automatically match the Productkeys up and show you ONLY the English name for each record. Frankly without this relationship in PowerPivot if you add the EnglishProductName into a reports that includes data from Total Sales ou will get a “Cartesion Effect” whereby every record in the product table will be matched to every record in the Total Sales table and your result will not only be useless, misleading and wrong but it will be exponentially HUGE!! Not good.

So with this relationship established you can not only include the EnglishProductName in a Pivot table of Sales data but you can filter the report based on the text rather than the ProductKey number. Much more “user-friendly”.

Another Relationship

We also have dates in our Sales table kind of. These also need to have their own relationships to the third table called “SalesDate”. Note that this table in PowerPivot has the same name as the source table. That is because we just set the Connection to a “table” in the Access database, we did not create a “select” query to get our data. Trust me that this WILL all be explained in the end. A little teaser I can add here is that in the solution we are building we will never be “filtering” this table to return a subset of the data in it. Therefore we will just be refreshing it as is unlike the Total Sales table which has become the “Query” table and you must NEVER change that name! I consider this a nuisance but a minor and absolutely necessary one. Hint, hint.

If you look at the “Query” table you will see an OrderDateKey, DueDateKey and a ShipDateKey. These value are expressed in the format “yyyymmdd” as an Integer which is kinda cute and works well for dates in this context (although to really get value out of them you have to use our SalesDate table to good advantage. The problem with using this date format is that it eliminates the use of Excel (and PowerPivot’s) date mathematics functions. You can do simple math with this format but not the really cool stuff.

So for our model we will make a relationship to the SalesDate table from these fields so we can convert these dates to actual datetime fields for our use in PowerPivot.

Let’s open the Create Relationships dialog yet again:

For our simple example we are only going to make a relationship for the ShipDateKey field in the Query table but we could make a relationship between any or all of the DateKey fields. If you look at the SalesDate table you will see that each record has a field that is in the same format as the Datekey fields in the Query table. This will work as long as the SalesDate table has unique records in it by date. We will be using an interesting technique to take advantage of this relationship … Hint, hint.

So Let’s Use Our Data in a Pivot Table

I’d like to see my sales by Product for a particular Ship Date. Maybe this isn’t a realistic example, but please think of this as a simple example that we all can understand conceptually and then apply these techniques in far more interesting and realistic ways later in your work.

We can easily now create a Pivot Table that includes data from the Query and the Product tables:

1. Choose Insert A Pivot Table from the Insert Tab of the Excel Ribbon
2. Choose “Use External data source”
3. Click “Choose Connection”

1. Choose the “Total Sales Table” Connection and click “Open”

1. You COULD add this Pivot Tables data to your Data Model at this point as well but let’s not. But trust me this would open up many other opportunities beyond the scope of this example – but one thing at a time.
2. Click “OK” and you will get the Pivot Table design range in your Worksheet.

Populate the Pivot Table

Now that we have a Pivot Table on our Worksheet let’s drop some data on it:

1. Choose the Product Table and add the EnglishProductName to the Pivot Table

1. Select the Query Table and add SalesAmount, TaxAmt and Freight to your Pivot Table:

See how we have already take advantage of one of our relationships namely the one between Product and Query on the ProductKey fields?

1. Now let’s bring the Fiscal Year from the into this analysis so we can filter to just show one year’s data at a time:

Now save your file….

Are We Done Yet? No way!

Well we could be done, but we are about to get to the real “story” here.

Background

1. Our data source, the Adventure Works database, is a very small database in the grand scheme of things
2. Despite that, we are still connected to a table (Total Sales) with over a million records in it.
3. My AW file is now 28 megabytes despite the incredible compression that PowerPivot brings to bear
   1. Can’t very well email this around
   2. Just publishing this file to SkyDrive took over 2 minutes
   3. I would like to have an empty template that I can fill with data after I open it based on what data I would like to see ONLY.
4. I am daily confronted with databases with 100’s of millions of records in which I REALLY am only interested in a subset
   1. Current Year
   2. This Year and Last
   3. A particular Division or Office
      

Challenge

To find a way to bring ONLY the records THAT I REALLY NEED into my PowerPivot cache of my Excel file to do my analysis, report or query. I don’t need or want to import ALL the data from ALL my data sources just to get one year’s data.

Solution

We will:

1. Create a Pivot Table to list our Calendar Years on a private Worksheet
2. Create a Slicer so we can select each Calendar Year
3. Copy this Slicer to the Worksheet with our Pivot Table
4. Create 2 formulas on a Secondary Worksheet where we can begin assembling a new SQL Select statement based on our Slicer choice
5. Create a single simple VBA procedure to change the Command Text of the “Query” table Connection at run time
6. Create an event that fires every time a new Slicer item is selected that
   1. Changes the Command Text of the Connection behind the “Query” table
   2. Refreshes the Connection thereby refreshing the Pivot Table

Let’s do it! It’s only got a couple of quirky things that will require a pretty good basic understanding of VBA and of Excel string formulas (just for fun).

Create Our New Pivot Table

1. Let’s create a new Worksheet and call it “DatesPivot” (just for good order sake).
2. Sitting on cell A1 we choose the Insert tab and Click on “Pivot Table”.
3. Choose “Use External data source” and Click “Choose Connection”
4. Choose the Tables Tab of the dialog and select “SalesDates”

1. Click “Open” and then “OK” to create a new Pivot Table design range.
2. From the List of the SalesDate fields on the right select “CalendarYear” and drop it in as a Filter for the Pivot Table

1. Then select cell B1 on the sheet so that you are inside the new Pivot Table

Create Our New Slicer

On the “Insert” tab hunt down the “Slicer” button and click it

Select “CalendarYear” in the list of fields in the “SalesDate” table and click “OK”

We now have a Slicer on our screen listing the Calendar Years that match with Ship Dates in the “Query” table. So if we do this right we will be able to select a year and see only the data in the Pivot Table for that year …. But not in the way you might think ;-).

While the Slicer is still selected “Cut” it to the Clipboard, switch to the Worksheet with our Pivot Table, select a nice area off to the Right (for now) and Paste the Slicer in from the Clipboard.

So we now have a Slicer on the first Worksheet with no connection to what’s on that Worksheet but rather that is capable of driving the value in cell B2 of the Pivot Table you just created on the “DatesPivot” Worksheet. Cool eh? No ? Be patient ….

Let’s Write A Little Code

I am a believer that EVERY spreadsheet EVER designed needs at least a little macro to make it function efficiently. I have done many, many huge Excel projects with hundreds of Procs and Functions driving totally automated, complex and business –critical Excel solutions.

On the other hand this example is going to have the teeniest macro you’re ever going to see but the value it will reveal will, I hope, be startling in it’s potential.

Let’s start by switching to the VBA UI and Insert a new module to begin with:

I suggest you copy THIS code into Module1:

Sub ConnectData()

With ThisWorkbook.Connections(“Total Sales”).OLEDBConnection

txtDate = Mid(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1), _

Len(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1)) – 4, 4)

.CommandText = Array(Range(“txtSQL”).Value & Range(“txtSQLWhere”).Value & txtDate)

.Refresh

End With

End Sub

I fully realize that it is ALWAYS better to write the code yourself than to just copy and paste (to this day I still probably can’t build an Access Recordsetclone procedure to drive a Combo box to find a record on a form from scratch. I always copy and paste and edit L ). But we will look at each line in detail:

With ThisWorkbook.Connections(“Total Sales”).OLEDBConnection

First, I am a BIG fan of the “With” statement for no other reason than how it organizes all my actions against a specific Object in one nice package. And it looks nice.

The Connections object is the key to everything we are going to do here. Notice that this code is referring to ThisWorkbook.Connections. I am NOT interested in this data procedure at talking to any kind of PowerPivot object – we are only playing with Excel objects.

Important: All source data operations MUST be done in the Excel object model. Any changes in the Connections themselves in this model made in the PowerPivot UI will lead to a locking of the Properties of the Connections and you will no longer be able to use any macro against them from Excel.

They will sort of “go to the other side” and can never come back.

Here is what you will see in the bottom left of the Connection dialog if you do:

Notice how the “Connection string”, “Command Type” and “Command Text” boxes have “greyed out” and look at the two lines of text in the bottom left … you’re screwed !

I do not consider this a negative in any way. This is just a new “Discipline” for you to remember. Now that we are going to be doing BI in Excel “Discipline” is going to be VERY important. This is not your Father’s spreadsheet – and I should know because I’m old enough to be the father of all of you probably ;-).

Important: This does not however mean that you can NEVER change the field names in a “Query” or replace the Table we have here with a much more powerful Query or a View or SP if this is a SQL Server source. No way, in fact all you need to do is change them AT THE SOURCE. If its Access then your best trick would be to create a Query with the
fields you want and the names that work for you based on the raw table (or tables) you want to use. In SQL you can do the same using Views and Stored Procedures. In fact you can actually write entire SQL strings right in the Command Text box that joins table and aggregates and everything you need going against the original tables with no need to have create rights on the data source server or file anyway – but that’s for another day.

The OLEDBConnection reference reflects the fact that Excel uses OLEDB to connect to most standard database formats and thus you are going to play with the OLEDBConnection here while there are also DataFeedConnections, ModelConnections, ODBCConnections and TextConnections. I assume that this will work with any type of Connection but I have found that the vast majority of corporate databases have an OLEDB Provider in your install (but others (such as MySQL can be downloaded as needed).

I know that this technique works with OLEDBConnections so it will work when connecting to SQL Server, Access, Excel, Oracle, and MySQL databases at least.

txtDate = Mid(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1), _

Len(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1)) – 4, 4)

This code is actually a single line but has the ” _” line extension for VBA at the end of the first line of text (of course J). Frankly getting the value of a Slicer selection into your spreadsheet for use in a formula or in VBA code is not pretty at this point in time, but not impossible.

If you click on “2003” in our new Slicer (named Slicer_CalendarYear automatically – thank you) and ask for ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1), it will return “[SalesDate].[CalendarYear].&[2003]” and not 2003. That value is VERY useful in a different context, most notable if you are going to use CUBE formulas (also later), but in our case we really want to just strip out the Year I selected and I did it using Mid(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1), Len(ThisWorkbook.SlicerCaches(“Slicer_CalendarYear”).VisibleSlicerItemsList(1)) – 4, 4). This formula in VBA accepts the fact that we will ALWAYS be looking for a Calendar Year that will ALWAYS be the end of that string with a “]” at the end. But since the name of the Slicer might change in different contexts this code ends up being nearly agnostic or at least is very easy to tweak for different scenarios.

.CommandText = Array(Range(“txtSQL”).Value & Range(“txtSQLWhere”).Value & txtDate)

This line has 3 interesting features.

1. The Text for the Command Text MUST be wrapped in an Array function. You cannot simply pass a string on its own (don’t know why, don’t care).

1. I am using the Range object to “suck” values out of my spreadsheet to feed this code from 2 named ranges “txtSQL” and “txtSQLWhere”. I use this technique all the time as it allows me to take advantage of the spreadsheet’s cells, ranges and formulae to assist me in my code in a way that is flexible and most importantly visible and easy to change. Plus sometimes I can use a value in a formula in a cell both on the spreadsheet AND in my VBA code – a “one-stop shop”. So:
   1. We have to create a new Worksheet in the Model, Let’s rename it Variables just because that’s what I call it ;-).
   2. In cell B2 put the SQL statement you will be using “select * from [Total Sales]”. Noted that this is the Object in the source database NOT any name assigned inside Excel or powerPivot.
   3. In B3 put the following text “where left(ShipDateKey,4) =” (without the quotes).
   4. Name B2 txtSQL and name B3 txtSQLWhere

In this way you can make changes to your model’s data sources in the spreadsheet using all the functional capabilities of Excel. I know this is not normal for a “Coder” but it should be for any “Excel “Coder” IMHO.

So when this line runs it simply concatenates the select statement with the Where Clause and appends to year you selected on the end producing the syntactically correct statement

select * from [Total Sales]where left(ShipDateKey,4) = 2003

and assigns it as the Command Text of the existing Connection in Excel which is also providing the data to PowerPivot through this “Add this Data to the Data Model” check box when you create the Connection.

.Refresh

The code then Refreshes the Connection thus feeding a new data set into the PowerPivot “Query” table and thus into all object relying on this Table in PowerPivot and in Excel. Nice !

Run our new code

Select a year in your slicer and then using the Macro menu in Excel run the procedure “ConnectDay”. You will see a relatively short import process (faster with 64 bits and when you have a faster processor(s) in your machine.

Then go into PowerPivot and you should see that the data in the “Query” table is only from the Year you selected in the Slicer and the row count is significantly less than 1.3 million. But all your relationships are still intact and any Tables or Pivot Tables in your spreadsheet will ref;ect the new data you have JUST imported. And you file if saved is going to be significanty smaller than when there is 1.3 million records (or even more in the real world).

Connecting all the parts

So we have one thing left to do and that is we need to make Excel run this code every time the user selects a different year in our little Slicer.

1. Switch to the VBA UI
2. Switch to the module of the worksheet with JUST the Pivot Table for the dates Slicer
3. Copy THIS code into that module

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

ConnectData

End Sub

Having to rely on a Pivot table’s Update is a really unfortunate thing but it definitely does the job when the data feeding the Slicer is in fact inside a Connection up in PowerPivot. It IS possible to assign a macro to a Slicer directly but as soon as you do that the Slicer stops functioning (?). I have no idea what that’s all about – but if that Slicer is pushing the result as the value to be used in a Filter of a Pivot Table (even if that Pivot Table consists of only that value), you can respond to a Update of the Pivot Table on the Worksheet. The secret is to stick that little Pivot Table its own Worksheet with nothing else on it. That Worksheet can then be Hidden or even xlVeryHidden – it serves no purpose in the UI.

So the code above fires whenever the Slicer changes the Filter in the Pivot Table and then the Event tied to that Worksheet for PivotTableUpdate and that runs our ConnectData subroutine that redefines the Command Text of the Connection behind the “Query” table and refreshes all objects in PowerPivot and in Excel that are dependent on the Query table. “Sweet” – as one of my favourite clients says J.

This does not however mean that you can NEVER change the field names in a “Query” or replace the Table we have here with a much more powerful Query or a View or SP if this is a SQL Server source. No way, in fact all you need to do is change them AT THE SOURCE. If its Access then your best trick would be to create a Query with the fields you want and the names that work for you based on the raw table (or tables) you want to use. In SQL you can do the same using Views and Stored Procedures. In fact you can actually write entire SQL strings right in the Command Text box that joins table and aggregates and everything you need going against the original tables with no need to have create rights on the data source server or file anyway – but that’s for another day.

Important: I must add here that once you have the Worksheet_PivotTableUpdate Sub in place you will find that it will tend to fire off when you are developing your solution in aggravating ways.  I personally comment out “ ’ ” the ConnectData call in this Sub while I am developing and remove the apostrophe when I need to test the app or when I deploy it.

Are We Done Now?

We can be done. If you click on the Slicer and change the Year the data in the “Query” table will be refreshed and everything reliant on it will change accordingly. All this in a file with an extraordinarily small footprint one that can:

1. Be emailed or posted in a reasonable size and time
2. Can be rounded out with all the capabilities available in VBA
3. Can be rounded out with all the capabilities available in Excel itself

But there are several REALLY important things left:

1. With this simple code,:

Sub ClearData()

With ThisWorkbook.Connections(“Total Sales”).OLEDBConnection

.CommandText = Array(Range(“txtSQL”).Value & Range(“txtSQLWhere”).Value & “1990”)

.Refresh

End With

End Sub

which you can run before you publish your Spreadsheet, you will produce an empty “Query” table thus producing the smallest possible footprint and not spreading perhaps sensitive data around with the file.

1. You could even make the two “one” table as SQL query Connection types and clean them out before shipping as well and then include a refresh of them on the opening of the Workbook in code.
2. Then you can refresh them on opening of the file one time.
3. Most significantly, you can set a flag that indicates that this file is NOT to be refreshed once the correct data for the period is imported so you can pass it around with a subset of data included as, for example, daily, monthly or quarterly reports. In this way users who even do not have PowerPivot installed can use your models – they only need Excel 2013 or higher.
4. In the PowerPivot UI or in the Pivot Table Calculated Fields you can augment your analysis with powerful DAX functions with NO EFFECT on your ability to change the sources of your Connections – as long as you ONLY change those sources from the Excel UI.
   

Let’s add some DAX

1. Add a new table from the AW database called Geography using the techniques I showed you above calling the connection “Geography”
2. Create a Relationship between the GeographyKey fields in “Query” and this new “Geography” table.
3. In the PowerPivot UI at the bottom of the “Query” table add this “Calculated Field”.

   Canada Sales:=CALCULATE(sum([SalesAmount]),Geography[EnglishCountryRegionName]=”Canada”)

   This will provide you with the total Canadian sales for the combination of “tuples” in any Pivot Table based on the “Query” table. I suggest you buy Rob Collie’s great book “Dax Formulas for PowerPivot” from www.powerpivotpro.com to get the basics of DAX.
   

4. Also In the PowerPivot UI in the “Query” table you add two new Calculated fields on the far right:
   1. EnglishCountryName =RELATED(Geography[EnglishCountryRegionName])
   2. EnglishProductName =RELATED(Product[EnglishProductName])

Important: Once again, remember that adding these PowerPivot capabilities to your “Query” table in no way affects the source of the data connection it is based on from Excel … so Excel’s Object Model can still change the SQL behind this table. No probs. Don’t change any of the source field names or the table name in PowerPivot is all.

 

Let’s Create a Query Table

1. On a new Worksheet you can go to the Data Tab, choose Existing Connections, choose the Tables tab and choose the “Query” table. Then insert this into your Worksheet as a Table.
2. While in this new tables you can create two new Slicers that will drive the filtering of this list you just created automatically allowing the user to do detailed queries of the data for year selected.
3. You can rearrange the fields in this table to show the EnglishProductName and EnglishCountryName fields near the beginning of the table.

Go back to the first two Pictures in this document to see how this result can look.

And all of this is done using PowerPivot and you can still use the macro driven by the Calendar Year Slicer to import just the data for the year you wish.

This is just the tip of the iceberg in this model as you can now add all kinds of bells and whistles to your model using DAX, using the basic capabilities of \excel itself (which seems to be forgotten in most discussions of PowerPivot IMHO). You can even capture your Connection string from the Connection table, insert that string into a cell and make changes there like data file or in the case of SQL Server you can change the server and the database from inside the Workbook, changing it at the same time as you run your ConnectData subroutine. There’s basically no end to the cool things you can now do using Excel and VBA with PowerPivot.

I should also add that although I have not tried it I assume that this same technique can be accomplished using Visual Studio Tools for Office (VSTO) as well. I’d be interested to see if anyone makes that happen.

Conclusion – Is This Big Stuff? I Think So!

As I have mentioned here before, I have been involved professionally in database reporting for several decades. Frankly I believe that the reason for this is that I spent the first ten years of my working life doing painful manual accounting, reporting and analysis in the Commodities business and I wanted to help others do better (and make a living). The revelation of VisiCalc and subsequently Lotus 1-2-3 back in 1984-85 was a turning point in my career and in my life.

Frankly, rather than being a “Turning Point” in my life, PowerPivot in Excel 2013 is pretty much the thing I have been waiting all these years for… It is here! I did not focus strongly on PowerPivot 2010 because it had no automation “story” and I found it was a good 1.0 version. I have waited until now to promote PowerPivot 2013 because frankly I know of very few corporations that have gone to Office 2013 yet – and that wouldn’t make me a lot of money now would it?

But now the world is moving to Office 2013 and frankly many who still haven’t are in need of something like PowerPivot in Excel to justify that move very soon.

I hope I have now shown you a way for those without a SharePoint PowerPivot Server to get huge productive value out of this technology. I hope it is well received.

I am sure there are going to be those who do not want to hear about this technique as it might take away from the use of the SharePoint PivotTable Services but I seriously doubt that will be the case – in fact I believe that the opposite will be the case – without the ability to create Client-Side PowerPivot solutions there will be a lot less “content” for those Server data stores.

We’ll see I guess.

Thoughts?

Dick Moffat