View Full Version : Auto Generated Table from PowerPivot

2014-08-05, 02:36 PM
I have not played with PowerPivot all that much, so this may be a simple task that I am just unaware of (such as GENERATE). With that said, is it possible to have a PowerPivot table, that would return a table? Reason I asi, I would like only one of the columns of data returned, and would like to use cube calcs to pull in the data from an OLAP cube based upon the column from the PowerPivot model.

If it is possible, next problem I could see right from the start, would be "how many rows would be in the table?" Is it possible to tell a table how many rows to contain based on the row count in a PowerPivot Model?

Bob Phillips
2014-08-05, 02:47 PM
You can filter the rows and the columns that you pull into Power Pivot, so you can control exactly what is added to your model. And then you can also hide columns from the client, so that even though they are in the model, the pivot table won't see them.

2014-08-05, 03:00 PM
Guess I am thinking size wise. If I have a PowerPivot model that has a 100K+ rows, and a cube that contains over 200 Million, would it not be easier to just pull the 100K rows into a PP table, then return to a table (maybe cube ranked member here), and then just use cube value functions to pull the data for the table? Or would it make no size difference if you pull all 200 million rows of data in and then just filter to return to a report?

Bob Phillips
2014-08-05, 03:29 PM
As I said, you can filter the rows when you import into PowerPivot to reduce the number of rows. And depending upon the cardinality of the columns, you will get compression of the data, so 64bit PP can handle millions of rows. But cube functions can be very expensive, they call back to the cube, whether that be a server based cube in SSAS or a PowerPivot model cube, on each change, so have to be used carefully.

2014-08-05, 03:36 PM
Good point. I'll role with PP and run with filter. Thanks again sir.

2014-08-05, 08:02 PM
I actually used a reverse link for this and it worked fine (created a table with the data model (has refresh capability)). I did put some cube calcs in, just to minimize size, and all turned out good.