Results 1 to 6 of 6

Thread: Auto Generated Table from PowerPivot

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    Auto Generated Table from PowerPivot



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    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.

  3. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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?

  4. #4
    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.

  5. #5
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    Good point. I'll role with PP and run with filter. Thanks again sir.

  6. #6
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •