To create the reports we needed from the two cubes in our new model, we had to build a virtual cube that contains all the dimensions of the TrialBalance cube, the Reports dimension from the ReportStructure cube, and two measures: Value from the TrialBalance cube and Include_In_Rep from the ReportStructure cube.
The Include_In_Rep measure in the ReportStructure cube contains a non-zero value for each of the accounts to include in any particular report line. The Value measure from the TrialBalance cube stores the actual value for each account. If we multiply these two measures, we get the value of each account included in the report line and a 0 for accounts that aren't included. (Figure 6 shows an example of TrialBalance accounts that will be included in a particular report line.)
The multiplication is complex because the Value and Include_In_Rep measures have different dimensionality (i.e., the two measures relate to different dimensions). Value measure lacks the Reports dimension and the Include_In_Rep measure lacks the Time, Organization, Variables, and ChartOfAccounts dimensions. When you compute values in a virtual cube, measure cells contain only values at the All level for dimensions that aren't common to all the underlying cubes. For those dimensions, you can use the ValidMeasure() function to return the measure value from the cell at the All level coordinates.
Because the Value and Include_In_Rep measures have different dimensionality, we had to use the ValidMeasure() function to force dimensions that aren't common to all the cubes to their All level, as the following code shows.
[Measure].[Report Value] AS
'ValidMeasure
([Measure].[Value]) *
ValidMeasure([Measure].[ Include
In Rep])'
The result is a calculated measure, Report Value. What we actually wanted to include in the final result is the total of Report Value across all the accounts. The right place for this total is in the All member of the ChartOfAccounts dimension. So, we first had to calculate the Report Value for all accounts, then aggregate them into the All member. Listing 1 shows the code to create the Report Value calculated measure. Note that, while calculating the All member's value, we excluded it from [ChartOfAccounts].Members. If we didn't, we'd have an infinite recursive formula.
We still had one hole in our solution. We needed to avoid aggregating the Include_In_Rep field across the ChartOfAccounts dimension because we didn't want to have a parent account in any report that included one of its descendants. However, we did want to aggregate measures across the Reports dimension. As Russ Whitney noted in his Mastering Analysis column "Alternative Aggregations" (March 2003, InstantDoc ID 37707), Analysis Services doesn't natively support aggregating a measure across one dimension and avoiding it across another one. To solve the problem, we created a custom rollup in the ChartOfAccounts dimension, as the code in Listing 2 shows. In the case of the Value measure, we aggregated all leaf-level descendants of the current account, and for the Include_In_Rep measure, we simply used the non-leaf member data.
Now, we can easily produce common financial reports like the one that Figure 2 shows. If our users need further detail about any line in a report, they can cross-join the Reports and ChartOfAccounts dimensions, suppress zero, and get a drill-down report like the one that Figure 7 shows.
One word of warning: When you create the ReportStructure table, be sure you don't directly or indirectly include an account more than once in the same report. Doing so will produce double counting. The relationships in Figure 3 illustrate the kind of duplication that can occur. Account 101 is included twice in report line A, once directly and once indirectly, through its parents.
An important benefit of our solution is its flexibility. If users in our organization want to add or change reports, they can update the ReportStructure and Reports tables, then reprocess the small ReportStructure cube. We don't have to change the ChartOfAccounts table or reprocess the large TrialBalance cube. You can apply this modeling technique to many kinds of applications other than financial reporting. Wherever you need to create large numbers of dynamic subsets of members of a given dimension and create a hierarchy of these sets, this technique provides the solution.
End of Article
Prev. page
1
2
[3]
next page -->
