Weekly Snapshot
I am trying to recreate this in Sisense. My Challenge is how do I count the total appointments in my system on a given day and then make sure that number never changes even though the state of appointments will change. for example:
On week 1 I counted 10 open appointments. 3 for this week, 5 for next week and 2 for the following week.
During the Week 1 the following happened, 3 appointments were marked as attended, 2 appointments for next week were canceled and we added 3 new appointments, one for next week and two for the following.
On week 2 I want to still show that we had 10 for last weeks snapshot, and that we have 8 open appointments as for right now.
I have attached some charts to show, hopefully, what I am trying to do.
-
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.
-
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
-
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
-
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ł
Please sign in to leave a comment.
Comments
5 comments