THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

More on importing Measures from SSAS cube in #PowerPivot

Several months ago I wrote about the issue importing measures from an Analysis Services cube in PowerPivot. The short story is that, when you define the MDX query, all columns are imported as Text. Of course, this is not a good thing for the numeric measures. The workaround is to change Data Type and then refresh data of the table. However, you might have two issues:

  1. You import the table twice. If loading data requires is long, this is twice long
  2. You might have an error in conversion when you change Data Type of the column (because existing strings cannot be converted correctly into numbers – changing Data Type the conversion happens on these strings, while another code path is executed when you refresh data from the data source having a column already defined as numeric).

The best thing to do in order to avoid both issues is:

  • Define your MDX query adding a slicer that will return no rows in your table
  • Change data type of the imported Measures in your PivotTable columns
  • Change the MDX query by removing the slicer

I hope this will be fixed in the future, anyway.

Published Monday, March 28, 2011 1:41 PM by Marco Russo (SQLBI)



Ådne Hovda said:

This is one annoying little issue that makes it hard to push PowerPivot (which is otherwise great) to clients. Do you know of any cell property that could be added to the measure in the cube that will hint PowerPivot about the correct data type?

September 30, 2011 4:31 AM

Marco Russo (SQLBI) said:

Unfortunately no - you have to wait for PowerPivot version 2 (Denali).

September 30, 2011 9:55 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement