PowerPivot – Connect to an SSRS Report

September 17, 2014

SSRS is a very nice reporting platform that comes along with MS SQL.

Using the free Report Builder client you can make some very nice reports and publish or schedule them to run on your intranet.

But what if you want to reuse valuable information in the reports ?

Well we can you use MS PowerPivot to access the data over the internet on you client.

Open PP and choose the in the menu From Report.

Next point to the URL on you Reporting Intranet or SharePoint if you are running in mixed mode.


When you click next it will grab the TABLIX data grid in your report.


As easy as that.

This is how the bits and pieces fall together in your MS BI environment. Smile

PowerPivot – Accessing meta cube data using the DMV commands

August 17, 2014

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


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


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


Go to Properties


5. Create new Connection settings using some DMV commands


select * from $system.discover_schema_rowsets

This will list all the available commands


Some more examples here below :

select * from $system.MDSCHEMA_CUBES

This will list all PowerPivot Cubes in your Excel file.


select * from $system.DBSCHEMA_CATALOGS


select * from $system.discover_storage_tables


select * from $system.discover_object_memory_usage


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


Chris Webb has more interesting tips on his blog.


If you install the Excel OLAP extension.


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




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