locked
Union tables that use different refresh frequencies and have over 1m rows RRS feed

  • Question

  • Hi, 

    I have two tables, 'small_table' and 'large_table', with identical columns but different numbers of rows.

    small_table contains dynamic data and is refreshed from SQL Server frequently, large_table contains over 1m rows of static data and is refreshed very infrequently.

    I want to generate data visualisations off a union of the two tables (i.e. appending rows of one to another).

    For performance reasons, I do not want large_table to be automatically refreshed whenever the union is made.

    Some things I have tried.

    1. PowerQuery's Append: It always auto-refreshes the tables it is unioning.
    2. Separate Workbook as a barrier to auto-refresh: Cannot create a connection to the PowerPivot Data Model in another workbook, and the Excel Tables are limited to 1m rows.
    3. Separate .csv as a barrier to auto-refresh: Reading a local csv is not noticeably faster than just refreshing from the database directly, and the initial setup of writing the csv then reading it in is quite slow.

    Ideally, I would like to do everything inside PowerPivot and have the ability to build a 3rd PowerPivot table off the union of two others without automatically triggering a refresh, but am open to alternative suggestions.

    Many thanks, Tom


    • Edited by tommarkallen Wednesday, January 20, 2016 10:43 PM
    Wednesday, January 20, 2016 10:43 PM

Answers

  • There is no easy way to do this in PowerPivot.

    If all of your measures are additive you could bring both tables in separately into PowerPivot and create the same relationships for both tables then hide one of them.

    So if you had table relationships like:

       Date - big_table - Product

    You would add small_table and also relate it to both Product and Date

               big_table
            /               \
    Date                     Product
           \                 /
            small_table

    Your measures would then just add the result of summing the columns from each table.

    eg.

    [Sales Amount]= SUM(big_table[Sales]) + SUM(small_table[Sales])


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Thursday, January 21, 2016 7:07 AM
    • Marked as answer by Michael Amadi Friday, January 29, 2016 6:37 AM
    Thursday, January 21, 2016 1:02 AM

All replies

  • There is no easy way to do this in PowerPivot.

    If all of your measures are additive you could bring both tables in separately into PowerPivot and create the same relationships for both tables then hide one of them.

    So if you had table relationships like:

       Date - big_table - Product

    You would add small_table and also relate it to both Product and Date

               big_table
            /               \
    Date                     Product
           \                 /
            small_table

    Your measures would then just add the result of summing the columns from each table.

    eg.

    [Sales Amount]= SUM(big_table[Sales]) + SUM(small_table[Sales])


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Thursday, January 21, 2016 7:07 AM
    • Marked as answer by Michael Amadi Friday, January 29, 2016 6:37 AM
    Thursday, January 21, 2016 1:02 AM
  • Thanks for your help Darren

    As you say, it restricts what sort of measures we can use, but this could be worth looking into.

    Cheers, Tom

    Thursday, January 21, 2016 10:25 PM