Overview: This post discusses the approach we used in delivering alternate hierarchies for financial reporting within a SQL Server 2008 R2 environment. Part 1: the solution, which is a valid alternative if you don’t have unary operators. Part 2: workaround we implemented to deal with a unary operator issue.

To Give Credit Where Credit is Due: Leo Furlong of Intellinet was the architect of this design. I helped implement it, but he was the brains.

Part 1: DW and SSAS Solution for Alternate Hierarchies

Business Requirements

The requirements presented to us included:

1. For financial reporting, each account may reside in one or more hierarchies for reporting. Example: the Sales Returns & Allowances account participates in the GAAP Reporting, Tax Reporting, and Internal Reporting Hierarchies; it does not participate in the Segment Reporting Hierarchy.

2. For the same account, a unary operator (+/-) may differ between hierarchies. Example: in the Internal Reporting Hierarchy, the Discounts account is subtracted from its parent, whereas in the Segment Reporting hierarchy it is added to its parent.

3. The rollup levels are ragged (unbalanced). Example: The GAAP Reporting hierarchy has 6 levels, whereas the Internal Reporting Hierarchy has 11 levels.

4. Sorting of the members within each level of the hierarchies needs to be customized (i.e., alphabetical is not sufficient).

Sample of the results one hierarchy would be intended to produce:

The Solution

Relational DW

From a relational data warehouse perspective, we implemented a many-to-many bridge table to handle the relationship between accounts and reporting hierarchies.

Dim Account: Grain is one row per distinct account. This table has the direct relationship to the fact tables.

Dim Account Reporting Hierarchies: Grain is one row per account + each hierarchy the account belongs to.

Each row has a recursive relationship to its parent row.
Contains all account attribute fields, including the unary operator (+/-) field.
Each field has a sort value (retrieved from MDS – discussed next).

Following is a simplified version of the data model:

Master Data Services

Both Account dimensions are populated (via SSIS ETL) from a Master Data Services model.

Each reporting hierarchy was set up in MDS as an Explicit Hierarchy, which allowed for the hierarchies to be ragged. An Explicit Hierarchy also permits drag & drop ordering of the members within each level (whereas a Derived Hierarchy does not).

Analysis Services

Dim Account: Displays just a few attributes (such as Account Type, Account Name & Number, etc). Because it’s directly related to the fact table, it has a “regular” relationship to the measure group.

Dim Account Reporting Hierarchies: The only visible attribute is the the parent-child hierarchy which serves up the reporting levels. The unary operator was delivered using the functionality built into a parent/child dimension in SSAS. There’s a few other attributes, set to be hidden, which are exposed through member properties. This dimension has a “many to many” relationship to the measure group, through the bridge measure group, as shown here:

Initially it seemed like this solution would work really well. It would have worked beautifully if we had no unary operator issues to contend with.

The remainder of this discussion deals with a workaround we had to implement due to a unary operator issue. If you don’t have unary operator data, the above solution is a valid option to consider.

Part 2: Dealing with Unary Operator Issues

The Unary Operator Problem We Ran Into

The unary operator did not function properly when “subtraction” accounts were involved. What we found consistently was the immediate parent of any subtraction accounts aggregated properly (ex: Level 2-C below); however, the parent of the parent (ex: Level 1 below) did not. The difference was always 2x the total of the subtraction account children. Aggregations where the children were always addition worked just fine.

Example:

After doing some research, we narrowed down the problem to being related to the many-to-many relationship. It’s currently documented in this Connect issue. Since only 10 people have logged that they can reproduce the bug as of mid-2011, I’m not betting Microsoft finds it a huge priority to get it fixed. Hence, a workaround was born…

Workaround to Handle Unary Operators

The immediate thing we wanted to do was eliminate the many-to-many relationship and convert it to a regular relationship. The goal was: if we can eliminate the many-to-many relationship, all other aspects of our solution would likely work as designed. Therefore, we did the following:

Left the existing fact table as-is (i.e., it still is the “primary” fact table in the DW). Leave the existing dimensions as-is.
Implemented a secondary fact table (ex: Fact Finance Hierarchies in the screen shot below) which flattened out each fact record with the hierarchy it belongs to. Caution: this does create duplicate fact records. This secondary fact table is populated at the end of the ETL process. These records are created physically in the ETL process at the client’s request; a very valid alternative would be to create this flattened table using a view.
In the SSAS Data Source View, remove the existing fact table. Replace it with the secondary fact table which has one record for every hierarchy record.
Change the relationship to be Regular; remove the bridge measure group.

Following is a simplified version of the data model after the workaround, from the perspective of the SSAS data source view:

After this implementation, the unary operator aggregates worked properly when we browsed the cube. Although there’s a number of ways to handle it, we found this to be the most straightforward & easy to maintain for our client.

Comments & alternative ideas are welcomed!