Tag Archive for Analysis Services

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

Many times, clients ask me to hard-code various values into their cube logic, things like conversion rates or other common factors. This article series will cover several different methods for adding user specific configuration values to an Analysis Server (Dimensional Model, as opposed to a Tabular Model) cube. As with any technical solution, there may be other methods available. As the series progresses, the techniques will get more and more involved, but in conjunction, they will be more flexible. As you may already be aware, there are essentially three ways to get ‘stuff’ into a cube: as a Calculated Member, as part of a Dimension Member, or as a Measure in a Measure Group. In Part 1 of this series, we will be hard coding some values as MDX calculations. Most of the methods in this series will work with any version of SQL Server and Visual Studio. I will try to point out limitations in versions when they arrive.

Part 1: Static Conversion Factors as an MDX Calculated Member

If a conversion factor isn’t ever going to change (like converting miles to feet, or vice versa), we can simply add an MDX statement in the Calculation design tab as a static value. Open the cube project in Visual Studio, double-click on the cube object in the Solution Explorer to open the cube in design mode. Click the Calculations tab at the top. Enter the following MDX Statements in Script View:

If you prefer Form View over Script View, your screen for the first MDX Member would look like this:

The purist reader may point out that the latter measure is simply the inverse of the former, which is absolutely true. However, I would reply that a) it doesn’t take up any more cube space or processing resources, and b) I prefer to always be multiplying my conversion factors instead of worrying about multiplication or division depending on the direction of the conversion.

For example, I would prefer the following MDX calculation:

to this one:

The end result of the two statements is exactly the same, but logically to me, the first one makes more sense.

Note here that the VISIBLE property of the two base conversion factors are set to 0 or False (hidden) because in this instance, I don’t need to let the users see them.

This method is about as simple as it gets. But that simplicity comes with a price. The conversion rate between miles and feet is never going to change, but something like a business specific factor can, and most likely will change. Using the above method would require a Business Intelligence developer to edit the MDX code within the cube and re-deploy it. Starting in Part 2 of this series, the methods will involve saving the configuration value outside the cube and independent of the code.