In the first parts of this article, we looked at a few methods of getting user controlled configuration values into a cube such that the value can be changed simply by changing a value in a table. (I could have easily labeled these as “data driven solutions” but feel that term is grossly overused these days.) All the methods outlined previously could suffice if the requirements allowed, but eventually, the requirements are going to overtake the capabilities of the design and you will be left with some rework.
To add some complexity to our requirements, the business users have handed down the following:
In this fourth and final installment of this series, we’ll meet all the requirements above by pulling out some fancy T-SQL. We used the PIVOT clause in the last solution. This one will add the LEAD windowing function.
The method outlined in Part 3 worked to a point, but if the single value was ever changed, it would change it for all of time. That falls short of our new requirements. Explained in a little more detail: What we need is a way to enter a starting (default) value (#1), and then any new values. Also, the lazy business users only want to have to put in the new value ONCE and have THAT value be the ‘new default’, to remain in effect until changed again (#2). Item #3 says they want a way to, in essence, slide the effective date forward or backward in time. They also want a way to revert back to the default, whatever that default happens to be, without even know the original default value (#4). And finally, they want it at the Fiscal Week level, not the Date or Day level (#5).
If you missed it in Part 2, we will be using a Configuration table and an abbreviated Date dimension table:
Next, and new to this solution, we’ll need an override table that joins the Date Key and Configuration ID as follows:
As with any table, we’ll need some sample data:
Note that I am only inserting one day per fiscal week when in reality there would be 7.
Again from Part 2, We’ll add the two values to the main configuration table:
Now add a couple of override values to the override table
For the sample override values above, I have purposefully staggered the overrides between ID’s 1 and 2 so they do not happen on the same week.
To bring it all together, we’ll build a view one block at a time. Start with a SELECT statement that uses the LEAD function as follows:
In the query above, the [EndWeek] column represents the [FiscalWeekKey] of the NEXT Override Value for that particular [ConfigurationID] based on the “PARTITION BY ConfigurationID” statement. If that [EndWeek] values happens to be null, it means that is the last entry chronologically for that configuration ID.
We’ll also need a list of distinct Fiscal Weeks from the dimDate table:
Finally, wrapping the above two statements in respective common table expressions, we’ll bring them all together. As before, the CROSS JOIN between WEEKS and the Configuration view yields the Cartesian product of Weeks and Configurations. In our case, 10 weeks and 2 configurations yields 20 rows. But also note the ON clause of the LEFT OUTER JOIN to the [OVR] table: We’re making an equal join on [Configuration ID] (no surprise there) and the next two lines make sure that the override value is joined to the correct Fiscal Weeks. If there are 4 weeks that a particular Override Value is in effect, it is these two AND lines in the joining that will make sure it happens.
Also note the first ISNULL ( , ) function to get the default value if the row is missing an override value. To see this in action, add the following row to the CubeConfigurationTable and re-run the SELECT statement.
Note that as of Week 201348, Configuration ID 2 (“My Floating Configuration”) reverts back to the default of 1.2345
And lastly, we need to PIVOT the whole mess by adding the appropriate T-SQL clause at the end:
Now, by adding additional entries to the Override table, users can specify when a new value becomes effective. After the entry is added, editing its FiscalWeekKey will change when it becomes effective.
Leveraging an existing Date dimension table, and adding two simple tables for default and override configuration values, we have a view that meets all the requirements. Also, to slide the effective date of any one configuration override involves simply editing the week it becomes effective. It will remain in effect until it is overridden again by another entry.
To add a third Configuration value to the cube would involve the following actions: