Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Querying PowerPivot DMVs from Excel

with 17 comments

One of the more popular posts on my blog is one I wrote just over a year ago on binding the results of an MDX query to a table inside Excel. I was thinking about it again recently when I was looking at the list of DMVs (=Dynamic Management Views – views that can be queried using SQL in SSAS and which contain all kinds of useful admin data) available in Analysis Services and noticed several new ones in 2008 R2 that are PowerPivot-related; I assume these are the DMVs that the Sharepoint management dashboard uses to track usage of PowerPivot models after they’ve been uploaded, but it struck me that it would also be cool to have this information available for PowerPivot models while they’re still in Excel. Wouldn’t it be good to query a DMV from Excel? Well, here’s how.

First of all, take an Excel workbook with a PowerPivot model in it. Go to the Data tab and click on Connections, and you’ll see the connection that is created automatically to the PowerPivot model:

image

This is the connection we want to use to run our DMVs. We now need to be able to use a table to show the results of our query, and this requires something similar to the method Greg Galloway described after I published the above post. First, on a new sheet open a connection to any relational data source you have handy such as SQL Server and import a table from that data source into a table in Excel. I used the DimProductCategory table from Adventure Works, and did this by going to the Data tab, clicking on From Other Data Sources and then From SQL Server, and running the wizard. The result is this:

image

Then go to the Connections dialog and copy the connection string from the PowerPivot connection shown in the first screenshot above (found when you click Properties and go to the Definition tab), then go to the SQL table you’ve just created, right-click and select Table and Edit Query, then paste the PowerPivot connection string into the Connection textbox, change the Command Type to Default, and then put your query into the Command Text box. I also had to add an extra connection string property setting Locale Identifier=1033 to get things working on my machine (and re-add it every time I edited the query), but I suspect this might not be necessary if you have a US English machine. Anyway, here’s what my connection string looked like:

Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue; locale identifier=1033

And here’s the dialog:

image

Having done this, when you click ok you’ll see the table update with the contents of the query.

Of course you can enter any MDX query here but I’m going to stick to talking about DMVs. So what useful information can you get from a DMV then? Vincent Rainardi has a great post on SSAS DMVs here which covers all the useful ones and has plenty of syntax examples, but here are some things you might want to do with PowerPivot.

First of all, to get a list of all the DMVs supported you can run the query:

select * from $system.discover_schema_rowsets

To get a list of tables in your model along with the dates they were last edited and when the data was last updated, use the following query:

select cube_name, last_schema_update, last_data_update from $system.mdschema_cubes

image

…although I’ve noticed some weird behaviour with the dates for some tables, so be careful using it.

To get a list of the number of distinct values in each column, use:

select dimension_name, table_id, rows_count from $system.discover_storage_tables

image

The query:
select * from $system.discover_storage_table_columns

gives more metadata on table columns; however:
select * from $system.discover_storage_table_column_segments

…although it gives some information on the amount of memory allocated to different columns, does not give the complete picture on memory usage. For that you need to use:
select * from $system.discover_object_memory_usage

image

This gives a full breakdown of memory usage (in the OBJECT_MEMORY_NONSHRINKABLE column) by each object in the PowerPivot model. It’s not all that easy to interpret this information though, because it only gives the memory used directly by each object and you also need to take into account the memory used by all the objects ‘owned’ by a given object too. It’s also worth pointing out that this is not the same view of memory usage that is given by looking at the temp folder created by Vertipaq, which Vidas has blogged about here and here; it shows the size of the database when it has been loaded into memory as opposed to the size of the database when it is persisted to disk, and there can be a big disparity between the two.

How can we make sense of the data returned by discover_object_memory_usage? We load it back into PowerPivot of course! I created a linked table and then a calculated column called OBJECT_PATH concatenating OBJECT_PARENT_PATH and OBJECT_ID using the following expression:
=[OBJECT_PARENT_PATH]&"."&[OBJECT_ID]
This gave me the full path of each object in a format that’s directly comparable with the object’s parent as stored in OBJECT_PARENT_PATH.

I then created a calculated measure with the following expression to return the amount of memory used by each object, including the objects it owns, in KB:

=(SUM(Memory[OBJECT_MEMORY_NONSHRINKABLE]) + CALCULATE(SUM(Memory[OBJECT_MEMORY_NONSHRINKABLE]),FILTER(ALL(Memory), COUNTROWS(FILTER(VALUES(Memory[OBJECT_PATH]), IFERROR(SEARCH(Memory[OBJECT_PATH],EARLIER(Memory[OBJECT_PARENT_PATH])), 0)=1))>0)))/1024

It’s then easy to see the memory used by the cubes and dimensions that make up the PowerPivot model:

image

And the memory used by objects associated with the columns in a particular table:

image

All of which is very useful if you’re trying to work out what’s eating memory in your PowerPivot model. If anyone comes across any other interesting thing to do with DMVs for PowerPivot then please let me know…

Written by Chris Webb

February 23, 2011 at 5:00 pm

Posted in PowerPivot

17 Responses

Subscribe to comments with RSS.

  1. [...] This post was mentioned on Twitter by Romit Mehta, Dan English. Dan English said: RT @rawmeet: Querying PowerPivot DMVs from Excel http://bit.ly/fV6MiP #powerpivot #BI [...]

  2. Chris

    Thank you for the great tip! I was looking for way to retrieve the last refresh date of PP and didn’t find/get any plausible solution (http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/thread/05d6ae28-76e8-49a3-a1ef-ecdbabf0f2ea)
    When I added the extra connection string property setting Locale Identifier=1033 an error was prompted. Instead I had change the Authentication Settings to ‘None’ This did the magic.
    It is also an intresting way to document your formulas. If column formulas could be retrieved it the same way, it would make live easier.

    Eddy

    February 24, 2011 at 3:52 pm

    • As I said in the post, though, I’ve seen some weird behaviour around the last refresh dates and it doesn’t always seem to be correct. I’ve not had a chance to work out why though.

      Chris Webb

      February 24, 2011 at 4:29 pm

  3. [...] are views that can be queried using SQL in SSAS and which contain all kinds of useful admin data. Chris Webb posts a popular post about querying PowerPivot DMVs from [...]

  4. This technique is pretty cool actually! Not necessarily for querying metadata, but for enabling PowerPivot writeback functionality :-)

    Select * from [Sandbox].[$MyTable] generates a fully editable table with Powerpivot data which CAN be used as a Powerpivot linked table. Et voila, Powerpivot writeback without static GetPivotData() stuff. Hmmm pretty cool.

    Maybe something to write a blog post about?

    Frenk van Beekveld

    July 20, 2011 at 10:13 pm

  5. [...] obviously possible, armed with hints from the comments on Macro’s post, and from this Chris Webb post and a late night twitter conversation with Mark Stacey, I decided to [...]

  6. [...] features, show detail (aka drill-thru). (An alternative method which can be used in PowerPivot V1 is detailed here  – if using V1, only DMV and MDX queries would be [...]

  7. [...] could see all this too? Well, they can, using a technique very similar to the one I blogged about here. Back in BIDS, after the initial deployment of the database, I added a new Analysis Services [...]

  8. Chris, I am running the following query to get a list of measures and their formulas/expressions from my PP model.

    SELECT DISTINCT [Table], [Object], [Expression]
    FROM $system.discover_calc_dependency
    WHERE Object_Type = ‘Measure’ and [Table] = ‘Data’

    I have found that it returns measures that have already been deleted from the model.
    Are measures and their expressions persistent in the model even after they are deleted?

    ruve1k

    November 4, 2011 at 4:57 pm

  9. Chris,
    Small optimalization. You can concatenate the object_path in the source query.

    SELECT
    OBJECT_PARENT_PATH + ‘.’ + OBJECT_ID AS OBJECT_PATH, *
    FROM $system.discover_object_memory_usage

    Kind regards,

    Frederik

    Frederik

    February 1, 2012 at 1:08 pm

  10. For unknown reasons, the pretty approach to retrieve DMV content in Excel, the approach no longer works with Excel 2013. Exists there another solution for this version of Excel ?

    With best regards
    Michael

    Michael

    August 8, 2013 at 8:02 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: