UNIONing or partitioning tables. RRS feed

  • Question

  • I have a table in PowerPivot imported from SQL Server and it is several million rows long. When I want to refresh the table, I have to import the same several million rows all over again just for the sake of say 10,000 rows that were generated yesterday. It takes a while... What I would much rather do is have a PowerPivot table that has all the rows to say the start of this month, and UNION ALL it to another PowerPivot table which is just the rows from this month, then once a month I refresh the big table, instead of every time I need to use it. 
    Sunday, September 7, 2014 2:36 AM


  • What you're asking cannot be done directly in Power Pivot. True partitioning can only be done in SSAS Tabular.

    What you can do as a workaround is build two tables in Power Pivot, one for data up to the end of last month, and one for the data from this month (I'd recommend making views/procedures on your SQL Server to explicitly enforce this behavior, so you can have simple queries from the Power Pivot end). You would have to re-write your measures to add across each of these tables.

    Further, you won't be able to refresh with the workbook refresh button, rather you'll have to refresh the Power Pivot table individually for this month's data.

    • Proposed as answer by Michael Amadi Monday, September 8, 2014 7:20 PM
    • Marked as answer by Michael Amadi Monday, September 15, 2014 6:20 PM
    Monday, September 8, 2014 2:30 PM