A common design approach to deal with something like store inventory, is to use periodic snapshot fact tables. You decide what grain (daily/weekly/monthly) you need, and build your periodic snapshot in the following manner:
Even though it would be possible to average the “Quantity_On_Hand” measure over time, these measures typically remain non-additive and should be viewed at the specified grain. To make things much more interesting (ok…complex), it sometimes (ok…usually) happens that you cannot retain daily snapshots forever due to size\space constraints. A few solutions come to mind (like Temporal Fact Tables) for that scenario, but let’s assume the following for the purposes of this blog post:
- You agree to store snapshot data on a daily, monthly and yearly basis.
- Data from the last daily snapshot of the month will be stored as the monthly snapshot.
- Data from the last daily snapshot of the year will be stored as the yearly snapshot.
- The last 31 daily snapshots will be retained.
- The last 12 monthly snapshots will be retained.
- Yearly snapshots will not be deleted.
The retention policy described above allows you to have quite a bit of coverage at different levels of detail (grain), keeping record counts somewhat under control for measure values that change very frequently.
To keep things simple and easy to maintain, I would typically opt for separate snapshot fact tables to cover each level of detail. It would look something like this:
(relationships of only one fact table is shown, to avoid ER-diagram hell)
Once the back-end structure is in place, the next challenge to overcome is to find the best way to display these inventory measures in a cube. The simplest method would be to create separate measures for each level of detail (i.e. Daily_Quantity_On_Hand, Monthly_Quantity_On_Hand, etc.), but doing this creates clutter and confusion for the end user.
We ultimately need to find the best way to use one set of (visible) inventory measures in the cube, using values from a different fact table depending on what level of the date hierarchy our end user is looking at. This is where scoped assignments come into play, enabling us to overwrite or replace the values of a measure.
Side Note – While this blog post focuses on one particular scenario and use of scoped assignments, it would be appropriate to point to a few excellent blog posts by Chris Webb, in which he explains all the basics around scoped assignments:
- Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated Measure
- Aggregating the Result of an MDX Calculation Using Scoped Assignments
Continuing with our inventory example, let’s assume that your cube’s date dimension and attribute relationships look as follows:
What is left to do at this point, is to add all the periodic snapshot fact tables and create calculated measures. By default, Analysis Services will create a measure group for each fact table, and there really isn’t any way around that. To deal with that little nuance, you can do the following:
- Add all periodic snapshot fact tables to the cube. This will create 3 different measure groups in this case…one for each fact table (and level of detail).
- Add an identifier to each measure, to indicate the grain. In the image below, I chose to add either “Daily”, “Monthly” or “Yearly” for distinction.
- Remove the word “Daily” from the daily-level measure group name, for display purposes and because we need a visible measure group for our calculated measures.
- Change the “Visible” property to “False” for all measures in all 3 measure groups. A measure group will not be visible if all the measures within it aren’t visible. Doing this effectively hide the measure groups we don’t want to see, but the measures contained within them are still accessible to our scoped assignments.
The image below shows what the end result will look like:
The final step is to create the calculated measure(s), and use scoped assignments to perform all the magic of choosing the appropriate measure for the different levels in our date dimension:
-
First, we create an “empty” measure with null values throughout:
-
Afterwards, we create the nested scoped assignments as shown below:
Note that we’ve used the attribute hierarchies (and not the user hierarchy) in the scoped assignments…which is a recommendation in Chris’s blog posts. The attribute relationships we defined in our dimension, create the necessary references between attributes and ensures that we still see the correct results…even when using the user-defined hierarchy.
