Tag Archive for Analysis Server Cube

Adding User Configurations to an Analysis Server Cube

Part 4: Default Configurations with Overrides

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:

  1. A default configuration value
  2. A place to enter override values, but ONLY for the date upon which the new value is to take affect
  3. Ability to easily change the effective date of a particular value
  4. Ability to revert back to the default value on a particular date
  5. And just to make it interesting, the granularity needs to be at the Fiscal Week level, not at the Date level.

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.

Recap:

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:

  1. Adding a row to the Configuration table, and any overrides to the Override table.
  2. Editing the PIVOT clause of the view to include the value as a pivoted column.
  3. Refreshing the cube’s Data Source View to include the new column from the view.
  4. Adding the item as a new measure in the Configuration Measure Group.

 

 

 

 

 

Adding User Configurations to an Analysis Server Cube

Part 3: One Row for Each Dimension Member

In Part 2, we created a view that pivoted the Configurations table around a single [Dummy Key] value of -1 and joined that to a dimension. And the dimension we picked had to be one that would never be used with the Configuration Measure Group because the sum of the configuration measure was only available at the UNKNOWN dimension member. Now, perhaps we come to realize that there is no good candidate dimension to which we can join our configuration measure group because it needs to be available to ALL dimensions. What now?

To make this work, we will need to edit our view from Part 2. First, select a dimension with only a few members. I chose my Date dimension for this solution. We’ll need to create the dimension table and populate it with some sample data.

Create the table …

Insert some data …

Now alter the view by adding a CROSS JOIN to the dimension table, and substituting the dimension’s [Date Key] for the static [Dummy Key] as follows:

The CROSS JOIN of the sub-select will return the Cartesian product of all rows in the Date dimension table (7) and all rows in the Configuration table(2), or 14 rows. Each Configuration will have the exact same value for all dates. The PIVOT statement will bring those 14 rows back to 7 rows, with three columns: [Date Key] and two configuration columns.

Refreshing the Data Source View will remove the [Dummy Key] field and add the new [Date Key] column in its place. Next we need to edit the Dimension Usage tab such that the Configuration Measure Group is joined to the Date dimension on the [Date Key] field. But browsing the cube now along any other dimension will yield surprising results: The Configuration value is SUMMED for the number of days in the slice! Well, obviously as that is what we told it to do when we created the Measures back in Part 2! Go back to the Cube Structure tab and edit the individual Measure aggregations in the Configurations Measure Group to “First Non Empty” (if you have Enterprise Edition) or Min or Max (if you have only Standard Edition). Now, regardless of the number of rows in the cube slice, the aggregation will always yield the value desired.

As with Part 2, we still have a single point outside the cube where configurations are stored, but the configurations are available to all dimensions.

Adding additional Configuration entries would involve the following steps:

  • Add a row to the Configuration table
  • Edit the view to include the field based on its [Configuration Name]
  • Refresh the cube project’s Data Source view to get the new field included
  • Add a new Measure to the Configuration measure group.

So far, our solutions have all resulted in global configuration values that if changed, will change for all dimension slices. In our case this is for all dates in the Date dimension. In the next part, we’ll explore a few different methods that will allow for entering configuration values that are date dependent. Since I started using the Date dimension for this series I’m going to stick with it for clarity, and also because that is usually how configurations are needed for the business.