Weekly Snapshot

Comments

5 comments

  • Avatar
    Malinda Jepsen

    In "Kimball speak", you need a snapshot fact table. (see https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/periodic-snapshot-fact-table/). The only way I know to do it is to store the daily snapshots in this type of fact table.

     

     

    0
    Comment actions Permalink
  • Avatar
    Matt Holtey

    Thanks Malinda, I was hoping for a way around having to create another table in my Schema but I think you're right, this is the best way to get the data. 

     

    Thanks!!

    0
    Comment actions Permalink
  • Avatar
    Andrew Block (Edited )

    Matt,

    Just a thought, couldn't you create a custom fact table that is accumulative and is built once a week which would summarize the open appointments you have say on each Monday?

    First create a custom 'WeekOf' field, then create a custom fact table that summarizes the count of open appointments by the WeekOf field 1x a week on your choosing and set this custom table to 'accumulate'. Set the cube to build once a week.

    Example:

    Select WeekOf, Count_Distinct(Appointments.ID) as Appointments

    From Appointments

    Group By WeekOf

    This is not a perfect solution because it your build fails you would miss that day but just an idea. (We actually do this with a daily inventory table and turn it into a monthly snap shot to show trending.....this could be accomplished in our ERP system but sometimes the costs/time isn't worth it so we do some non-critical snapshots in Sisense)

    You could also utilize the csv export plugin on your build and then union that data back into your table and show weekly open appointments. 

    Let me know if this helps 

    Thanks

    0
    Comment actions Permalink
  • Avatar
    Malinda Jepsen

    Andrew,

    The only difference between our solutions is that I persist the snapshot rather than relying on the cube to be my source. By exporting the csv you do have a way that the data is persisted. I would also caution about cube changes that might require a full build. If your csv file contains all that data, you would be okay. It depends on the amount of data you have and how knowledgeable your data designers are (so they don't make a mistake and overwrite old data).

    Malinda

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    I Agree with Malinda, relying on Accumulated table in EC, can be quite risky. Any unforseen failure on ECube that requires a full rebuild and there is no going back. You could try this:

    1. Build a Chart with current staus (as of current day)

    2. Rebuild that daily and use a python plugin that exports a widget to csv. Every day a new csv will be generated

    3.Upload it back to ECube via CSV connector pointing to a folder storing those CSV's.

    This should help you gather data in required format and should be fairly autoamted way of doing it.

    Regards,

    Michał

    0
    Comment actions Permalink

Please sign in to leave a comment.