Using Unary Operators to control Analysis Services hierarchy aggregations

Analysis Services hierarchy aggregations can be easily controlled using unary operators. For example, in accounting there are GL accounts that are grouped in major GL account groups and used in different financial statements like Profit & Loss Statement, Income Statement, Trial Balance, and Balance Sheet. These GL accounts may affect the balances of the major GL account groups and  financial documents differently, adding  or subtracting to the balance.

A simpler example, might involve sales quotas for a Sales Department. For example, in some organizations the Sales Department is broken down into sales teams with salespeople assigned to those teams. The overall Sales Department quota is broken down into smaller quotas among these sales teams and the sales team’s quota is then broken down into quotas assigned to the team members. In some organizations, Sales Managers often assign these quotas evenly throughout the sales teams and sales team members. Others, in order to guarantee a fat bonus check decide to raise the bar and assign sales quotas that are higher to the overall department’s sales quota. Figure 1 below shows an organizational structure chart of our sample Sales Department with sales quotas that don’t sum up evenly.

Figure 1. Sales Department organizational structure chart and sales quotas

As can be seen in Figure 1, the total Sales Department quota is $150,000. The Sales Manager in this case decided to push their teams harder and assigned a sales quota of $60,000 to each team. In a perfect scenario, if all sales teams meet their sales quota, the total sales for the Sales Department would be $180,000, which is $30,000 more than the department’s sales quota. Accordingly, each team, in order to impress their Sales Manager and win a free meal and margaritas at the local Mexican joint, decided to exceed the team’s sales quota by assigning higher personal sale quotas.

In our BI solution, we are required to create an Analysis Services cube in which the Sales Manager can track these sales quotas at each level of the Sales Department organizational chart as seen in Figure1. But, we cannot simply sum up the sales quotas, because the totals would not match up. We could approach these several ways, but our requirement is to replicate Figure 1 with a single “SalesQuota” measure with no MDX or additional measures involved.

The Solution

In order to achieve the requirement imposed above we can make use of a special attribute property in Analysis Services called UnaryOperatorColumn to control how level members of our hierarchy contribute to the aggregated value of the level parent.

In a regular scenario, the parent’s sales quota at each level would be equal to the sum of its children’s sales quota. That would have been the case if the Sales Manager in our example had decided to distribute the overall Sales Department quota equally among each sales team and the individual sales team members would have done the same for their personal sales quota. In other words, each sales team’s quota would have been $50,000 each ($150,000 / 3). For Sales Team A, for example, each sales person’s quota would have been $16,66.67 each ($50,000 / 3).

But in our scenario, the parent’s sales quota at each level should not be equal to the sum of its children’s sales quota. The only solution then, is to create an “artificial” child at each level that holds the parent’s sales quota and specify this “artificial” child to be the only child contributing to the parent’s total. This is possible through the UnaryOperatorColumn attribute property in the Dimension designer in BIDS. In Figure 2 you can see the available Unary Operators that can be used in this property along with the resulting behavior as described in Books On Line (http://msdn.microsoft.com/en-us/library/ms175417.aspx).

Figure 2. Unary Operators.

The UnaryOperatorColumn attribute property value, as the name implies, is a pointer to a table column that holds one of the Unary Operators listed in Figure 2. For our Sales Department quotas example, we would need a column that holds the unary operator for each level member in our Sales Department dimension. The Sales Department dimension source table would look as shown in Figure 3.

Figure 3. Sales Department dimension source table

Notice that this table is a naturalized Parent Child table. Also, notice that an “artificial” child was created with the same name as its parent. This “artificial” child is the child that will hold the parent’s sales quota value and is the only value that will be used in the parent aggregation. The way we control this aggregation, is by assigning to this artificial child the ‘”+” Unary Operator and the rest of the children the “~” Unary Operator.

At the lower level in our hierarchy all the way to the right in Figure 3, each sales person is assigned a “~” as its Unary Operator  in the SalesPersonUnaryOperatorColumn and only the “artificial” child receives a “+” as its Unary Operator. The same applies for the Sales Team level in our hierarchy, only the “artificial” child is assigned the “+” Unary Operator.

The FactSalesQuota table that holds the sales quotas would look as shown in Figure 4:

Figure 4 FactSalesQuota fact table

The Analysis Services project

Once we have defined our underlying table structure, we can take a look at the Analysis Services project. Figure 5 shows the basic project definition with the Sales Department Dimension and Sales Quota measure.

Figure 5. Analysis Services project definition

The Sales Quota Measure

The sales quota measure is a straightforward column based measure. It is based on the Fact Sales Quota table and is a simple SUM aggregation. Figure 6 shows the sales quota measure definition.

Figure 6. Sales Quota measure

The Sales Department Dimension

The Sales Department dimension is a simple dimension with the necessary attributes needed to design a drilldown hierarchy. Figure 7 and 8 show the Sales Department dimension definition, user hierarchy and attribute relationships.

Figure 7 Sales Department definition

Figure 8 Sales Department attribute relationships

A very important step in any user hierarchy definition is to specify the key columns at each level. In this case, the only attribute that needs a composite key column definition is the Sales Team attribute. The key column definition is shown in Figure 9.

Figure 9 Sales Team attribute key columns

The deployed cube with the Sales Department definition provided so far would look as shown in Figure 10.

Figure 10. Deployed cube

Notice that the “artificial” children show up and that the children’s sales quotas are being added to the parent’s aggregated amount. To fix the aggregation issue we need to define the UnaryOperatorColumn attribute properties for the children level members. We can hide the “artificial” children by changing the HideMemberIf user hierarchy level property to Parent.

Figure 11 and 12 show the values for the UnaryOperatorColumn property for the Sales Person and Sales Team attributes.

Figure 11. Sales Person UnaryOperatorColumn value

Figure 12. Sales Team UnaryOperatorColumn value

Figure 13 and 14 show the user hierarchy level property HideMemberIf

Figure 13 Sales Person user hierarchy level HideMemberIf property

Figure 14 Sales Team user hierarchy level HideMemberIf property

The Results

Once these properties have been changed the resulting deployed cube should look exactly as required. Figure 15 shows the exact aggregation behavior as in Figure 1.

Figure 15 Final results

Conclusion and Considerations

The solution presented above provides the exact results as dictated by our requirements and is meant as a means to exemplify and understand how unary operator can be used to control aggregation behaviors in user hierarchies.

There are other options to provide the same results, but this solution requires no calculated members or MDX at all. While this solution works, you need to keep in mind that there are some performance consideration regarding unary operators and parent child hierarchies. The key is always to TEST! TEST! TEST!

Sample Files

You can download the project files used in this post here.

Advertisement

SSAS errors: DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

When attempting to browse an Analysis Services cube you may be presented with the following error message:

DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

The error message is self-explanatory. There is a Default Member being referenced and was not found. But, pinpointing where this “DefaultMember” is being referenced may me a bit challenging.

The Root Cause

The root cause of this issue is quite simple: a deleted or renamed measure is still being referenced by its original name. Some of the areas where this deleted or renamed default measure could still be referenced by its original name include:

1. Cube DefaultMeasure property
2. Perspective DefaultMeasure property
3. Calculations

The fix

The fix may involve adding the measure back or renaming the measure to its original name or digging through several areas of the cube where the deleted or renamed measure is still being referenced by its original name. Here are your options:

1. Add or rename the measure back to its original name.
This may not be the desired option, but it could be the easiest and quickest way to fix the issue.

2. Modify each Perspective’s DefaultMeausure property
Unfortunately, deleting or renaming a measure does not “trickle-down” where it is being referenced in a perspective. The original measure’s name will remain.

3. Change the cube DefaulMeasure property to the new measure name.
Similar to Perspectives, deleting or renaming a measure does not “trickle-down” its new name to the cube’s DefaultMeasure property. You will need to select a new measure in the DefaultMeasure property.

4. Modify the cube XML code
This option may be a little daunting to some, but in the end it is the quickest way to make sure the original measure’s name is replaced by the new measure’s name. To do this, in Solution Explorer right click on the Cube’s name and select View Code. The XMLA definition will open. At this point, press CTRL+H or click on Edit->Find and Replace->Quick Replace.The Quick Replace window will open. Type the original measure’s name in the “Find what” textbox and type the new measure’s name in the “Replace with:” textbox.

Dynamically generate current Year, Month or Date member with MDX

MDX can be extended with Visual Basic functions like the FORMAT() and NOW() functions to dynamically generate the current year, month or date member. For example, it can be used as part of a named set to generate current year budget and actuals. It can also be used as part of a filter in a Performance Point dashboard.

First, a little review of VB NOW() and FORMAT() function:

NOW()

The NOW() VB function returns the current system date and time. It is similar to the T-SQL GETDATE() function.

FORMAT()

The FORMAT() VB function returns a string formatted according to instructions contained in a format String expression.
Source: http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx

There is an extensive list of characters you can use to create your own date and time formats. You can see the complete list here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

For example, you can extract the year portion of your date in the following formats:

y: Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
Example: FORMAT(NOW(), “y”)
Result: 1 (for any date in 2011)

yy: Displays the year in two-digit numeric format with a leading zero, if applicable.
Example: FORMAT(NOW(), “yy”)
Result: 11 (for any date in 2011)

yyy or yyyy: Displays the year in four-digit numeric format.
Example: FORMAT(NOW(), “yyy”) or FORMAT(NOW(),”yyyy”)
Result: 2011 (for any date in 2011)

FORMAT() and NOW() with MDX

The following MDX example uses the Adventure Works 2008R2 Analysis Services OLAP cube. Suppose we need to return all Internet Sales for the year 2011. The MDX script would look something like this:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2011]

This works just fine and will return the aggregated sales of 2011. (Note: the Adventure Works 2008R2 does not contain sample sales data for 2011, result will be an Empty dataset).

But what if instead of a specific year, you are asked to return the Internet Sales amount for the current year? You have 3 options:

1. On January 1st at 12:01 AM every year you need to change your MDX script to the new current year.
   CONS: You might be passed out from celebrating New Year’s Eve and most probably you will not get to it until a few days out into the new current year.
2. You could use some MDX functions like LasNonEmpty and LastChild to figure out the last amounts recorded by the most recent date.
   CONS: You may have forecast data or simply bad data with transactions occurring on dates out into the future. (Trust me, I’ve seen it)
3. You can build a dynamic MDX script that will return the current year based on the system datetime.
   CONS: It may be too sexy for your cube.

The trick is to construct the Date dimension member using the STRTOMEMBER MDX function. The STRTOMEMBER MDX function stands for “String to Member” and as the name implies, it will convert a string to a dimension member. In this case, we want to generate the [Date].[Calendar Year].&[2011] dimension member from a string using the Year portion of the system datetime.

The dynamic string will look like this:

StrToMember(“[Date].[Calendar Year].&[“+FORMAT(NOW(), “yyyy”)+”]”)

Now we can use it in our MDX script:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE StrToMember(“[Date].[Calendar Year].&[“+Format(now(), “yyyy”)+”]”)

The same applies if you want to select or filter data for the current month. In the following example I’m be using the Month level from the Calendar Hierarchy and specifying the month of July (7) for the year 2011:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].&[2011]&[7]

We can generate the current month member as follows:

SELECT [Measures].[Internet Sales Amount] ON COLUMNS FROM [Adventure Works] WHERE StrToMember(“[Date].[Calendar].[Month].&[“+Format(now(), “yyyy”)+”]&[“+Format(now(), “M”)+”]”)

Note that to specify the Month portion we use CAPITAL letter M because it is case-sensitive. Smallcase letter m stands for minute. The complete list of characters can be viewed here: http://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx

I have been Knighted

As I announced on my 2000-2010 a Decade in Retrospect blog post I have joined the ranks at Pragmatic Works and started my first day today. I’m excited to join such a great professional team, full of experts in the SQL Server and Business Intelligence field. There is no waste of time here. Lots of engagements from the get go. Less than an hour at work, I got in my first brainstorming session. An hour later I have been assigned to two client engagements I will be involved with during the next few weeks. I will also be involved with the Virtual Mentoring services. If you have purchased Virtual Mentoring hours from us, I might be giving you a call soon! 

 The atmosphere here at the new offices is laid-back and you can feel a sense of teamwork from all of the guys. If you are looking to make a career move, Pragmatic Works is your best choice. Lots of expansion going on, great carer opportunities and lots of expertise in-house to tap into.

 

Business Intelligence: Decaffeinated Please!

For many Database Administrators, Data Analysts and other IT and Business Professionals, Business Intelligence (BI) and Data Warehousing (DW) may be a new and uncharted territory with no clear path towards the destination. Others, who have already jumped head-on into the Business Intelligence journey, may be facing challenges that can potentially put their efforts at the risk of failure. For this reason I put together a presentation titled Business Intelligence: Decaffeinated Please!

In this presentation I cover terms and acronyms associated with Business Intelligence and Data Warehousing. I make a differentiation of what Business Intelligence is and what it is not. I also go over my “10 Rules of Wisdom” towards BI success based on lessons learned from personal experience as well as from insight gained from leading authors and speakers in the BI universe. The purpose of my 10 Rules of  Wisdom is to serve as a guideline for anyone involved in a BI initiaive or in its planning stages.

My “10 Rules of  Wisdom” for Business Intelligence Success are listed below:

1. The Business Intelligence solution needs strong executive management support. Keyword: Sponsor
2. The Business Intelligence solution must add value to the organization and be trustworthy.
3. Always have a Business Intelligence roadmap in clear view.
4. Define iterative success criteria.
5. Choose the right technology based on your business and user needs.
6. The BI solution needs to be understandable and documented.
7. The BI solution needs to be accessible.
8. The BI solution must be able to grow according to the business needs.
9. The BI solution needs to adapt to new business analysis needs.
10. Partner with experts in the field to help you achieve your goals.

These and other topics can be found on my PowerPoint presentation below which I continuosly enhance for my presentations for SQL Saturdays and User Groups.

 (click on image to download)

Slides updated 3/24/2011

Creating a Top 1, Top 10, Top n Customer or Product List using Named Sets in SSAS Part 1 of 3

Just like stored procedures in SQL Server, Analysis Services provide  a similar functionality through stored MDX expressions called named sets.  SQL server stored procedures allow you to:
– Return a set of data from your database
– Can be reutilized by being called in scripts
– Are named descriptive of the data they return (ideally)

Similarly, SSAS allows you to create reusable pieces of MDX code that can be called in other MDX scripts by its name or alias and have the same characteristics as described above. Additionally, these reusable pieces of MDX code may or may not use parameters just like parameter-less stored procedures.  In essence a Named Set is a predefined subset of your cube or a sub-cube. For purpose of simplicity I will go over named sets that do not use parameters.

One of the most common queries requested by Sales and Marketing Managers is a list of Top 10 Customers.

This list of customers may be required in ad-hoc queries as well as weekly, monthly, quarterly or yearly sales reports. This is where Named Sets in SSAS are most beneficial. Instead of rewriting the same MDX query over and over and having to change it everywhere you use it each time the criteria for Top 10 customers changes (believe me it will), you simply call this named set and whatever changes you make to the named set trickles down to wherever you call it.

The general MDX syntax to define this Named Set is:

CREATE SET CURRENTCUBE.[Top 10 Customers]
AS
TopCount
(
  (Existing [Customer]. [Customer].[Customer].Members]),
  10,
  [Measures].[Sales Amount]
);

As you can see the MDX function that helps us easily create this list is TopCount(). The keyword *Existing* forces the data set to be reevaluated each time dimension criteria changes, for example if different time period such as Year,  Quarter or Month is selected or if the cube is sliced by a particular product. In this case, the Top 10 Customers list will be generated based on the criteria applied.

Conversely if you need to create a list of the 10 least profitable customers or least profitable products for example, you can use the BottomCount() MDX function in a similar fashion.

Special attention needs to be taken when using BottomCount() MDX function for customers or products that had no correponding sales amount for the given criteria. For example if during last month 15 customers have no sales [Sales Amount]=0 or [Sales Amount] is NULL, then BottomCount() customers list will be made up of customers with $0 or NULL  sales amounts. These $0 and NULL sales amount records can be filtered out using the Filter() MDX function for $0 values and with the NONEMPTY() MDX function.

In Part 2 of this post I will be providing some examples of Named
Sets using the Filter(),  NonEmpty() and another useful function called Item() which allows you to select a particular tuple from a set.

In Part 3, I will be explaining the difference between TopCount() vs. Head() and BottomCount() vs Tail() MDX functions. In some instances a particular function might be simpler and more efficient.

Variance calculation using MDX calculated measure in SSAS

Actual vs. Expected Variance Analysis focuses on the difference between the projected or expected amounts and the actual amounts. These type of analysis are part of an important set of metrics for decision makers in all type of organizations and can be applied to Budgets, Production, Revenues, Profits, Efficiency and many other areas.

In SSAS OLAP cubes, these variances could be calculated through several ways. An easy and simple way to accomplish this is through calculated members using MDX in SSAS. There are several other ways to accomplish this calculation but for the scope of this post I chose a simple and quick way for beginner MDX’ers.

The Math
The basic formula for variance is:

Variance = Expected – Actual

The Requirements

You have been tasked to create an Actual Budget Variance cube in Analysis Services for the CEO of your company. In this cube the CEO is interested in viewing the Budget, Actual Budget, Expected Budget and Actual Budget Variance by Department and by Quarter.

Let’s assume we already have the Actual Budget Variance SSAS cube solution already created with a Department and a Quarter dimension as well as with the three Measures already loaded in our OLTP Data Warehouse (Budget, Budget Expected, Budget Actual):
(Click on image to expand)

We need to dynamically calculate the Actual Budget Variance as follows:

Actual Budget Variance = Budget Expected – Budget Actual

To create this calculation in SSAS follow these steps:

1) Open the Analysis Services Project Solution in Business Intelligence Development Studio (BIDS) and click on Calculations Tab and create a new calculated Member as follows:
(Click on image to expand)

2) Save the Solution and Process the cube.

3) Once processed, click on Browser Tab to browse  the cube. Expand the Measures group and you should see the new calculated member. Drag all measures and both dimensions into the browsing area. Group the Department Dimensions under the Quarter Dimension by moving Department column to the right. You can expand and collapse the Quarter Dimension to show/hide the details. It should look something like this:
(Click on image to expand)

From this example you can understand the basics of creating a calculated member using MDX. You can see the full script of the MDX calculated member by clicking on the Script View icon in toolbar under Calculation.
(Click on image to expand)