Arquivo

Archive for the ‘Agregations’ Category

Unary Operators to control Analysis Services hierarchy aggregations

Link:http://www.bidn.com/blogs/JoseChinchilla_SQLJoe/ssas/2211/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. Bur, 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 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.

AverageOfChildren – Effects of semi-additive measures on other dimensions

Effects of semi-additive measures on other dimensions

http://msolap.wordpress.com/2009/08/23/effects-of-semi-additive-measures-on-other-dimensions/

Erstellt am 23. August 2009 von hbuchta

Semi-additive measures are measures that don’t use the same aggregation method along all of the cube’s dimensions. In SSAS the time dimension plays an important role here. For example, if you choose the ‘AverageOfChildren’ aggregation method, the measure is averaged over the time but summed up over all other dimensions. When do we need this? Well, usually semi-additive measures are used when working with snapshot data like stock levels, balances of accounts etc.

For an example, let’s look at a car park. We want to analyze the number of cars that are in one of our two park houses at a certain time. For our example we use a very simple fact table looking like this (I simplified the time to just 2 days, so imagine we’re counting the cars at 1pm):

As you can see, someone also wanted to analyze the weather at each park house and each day so that we can easily see the effect the weather has on the parking behavior (obviously more people took the car when the weather was bad…).

Based on the fact data above we use a very simple data model looking like this:

 

 

Now let’s do some analysis on that model. First we analyze by date and park house:

This clearly shows the semi-additive behavior of our measure: For each row (combining the two days) the value is averaged while it gets summed up across the park houses (columns) as expected. At day 1, 1pm we had 10 cars in car park P1. At the same time on day 2 there were 30. Of course we cannot add these values together but the average makes sense. So the meaning of the grand total is ‘at 1pm there was an average of 20 cars in our car park P1′.

Now, what would happen if we start analyzing by the weather? As the weather is not our time dimension you might expect values to add up as a simple sum. So let’s try:

Hmm, what happened here? The sum would have been 75 but our OLAP query results in a value of 50. What had happened and what is the total? You can easily see that it is neither the sum nor the average of the above values.

In order to clarify this behavior, let’s also include the time in our analysis:

Using this view we can clearly see that SSAS made no mistake. The total number of cars being in our park houses at an average weather is 25 (average of 20 and 30, line one in the grid above), so this is correct. On the other hand, we had 30 cars in our park houses at day 1 (20 in a park house with average weather at that day and 10 in a park house with good weather) and 70 at day 2 giving a total average of 50. So this is also correct.

So the total for the weather is not the average or the sum of it’s detailed values but the average of the summed up values.

This is a very simple example of how semi-additive aggregation of a measure also influences aggregation against non-time dimensions as well.

With this in mind you are now well prepared to look at the following analysis based on the same source data:

This time we left the time dimension completely out of the analysis resulting in averages along both other dimensions (park house and weather) as all values we actually see are already averages.

Surely, it’s less confusing when looking at a single point of time like in the example below, where everything sums up neatly:

Categorias:Agregation Types