PowerPivot – Accessing meta cube data using the DMV commands

If you are a bit familiar with MS Analysis Services Cubes and MDX queries.

Well PowerPivot uses a built-in cube to store the data.

Well we can use the Dynamic Management Views commands to see some of the inner-side of the cubeSmile

Here we go :

1. First step go to the Excel and connect to a database source like MS SQL or SharePoint

2. Next create a PowerPivot Data Set of the data source and create a list or a Pivot Table of that data source.

3. Go to Data -> Connections –> Properties

image

To see the connection properties

Connection string : 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

image

4. Go to a blank sheet and create a new Connection from there.

image

Go to Properties

image

5. Create new Connection settings using some DMV commands

image

select * from $system.discover_schema_rowsets

This will list all the available commands

image

Some more examples here below :

select * from $system.MDSCHEMA_CUBES

This will list all PowerPivot Cubes in your Excel file.

image

select * from $system.DBSCHEMA_CATALOGS

image

select * from $system.discover_storage_tables

clip_image002[5]

select * from $system.discover_object_memory_usage

image

Read more how to format this nice example above, here :

http://cwebbbi.wordpress.com/2011/02/23/querying-powerpivot-dmvs-from-excel/

Chris Webb has more interesting tips on his blog.

Tip:

If you install the Excel OLAP extension.

http://olappivottableextend.codeplex.com/

You can access the MDX for reading the data from the PP Cube.

image

image

image

It seems that you can get access to the cube as well using MSOLAP data source using the AMO library and scripting.

I will try to publish an example later on Smile

Advertisements

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

%d bloggers like this: