Report users usually want to see reports in a format like the one in Figure 2, but they also want the ability to drill down from each report line into the accounts that contribute to that line. To create this kind of dynamic report, our team first tried adding the report structure into the ChartOfAccounts dimension. However, as Figure 3 shows, the joined structure creates a network hierarchy in which a child can have more than one parent. Analysis Services doesn't support this structure.
The second solution we tried was to create multiple hierarchies in the ChartOfAccounts dimension, with a different hierarchy for each report. We quickly discovered that option was also impractical for several reasons. First, our organization uses more than 100 reports, which means we'd need an equally large number of hierarchies. Because Analysis Services works behind the scenes to create a full dimension for each hierarchy, this option would significantly decrease database performance. Second, in a model such as ours, every report includes only a small subset of the leaf-level members of the ChartOfAccounts dimension, but Analysis Services requires that all leaf-level members be included in all hierarchies. Third, our organization's list of reports—and the inclusion of accounts in each report—tends to change frequently. If we had a separate hierarchy for each report, we would have to frequently restructure the whole database.
Finally, we realized that the solution was hidden in the problem. Our problem was that there were many-to-many (M:N) relationships between the report lines and the accounts. A basic rule in the mathematical model behind OLAP is that if two lists of entities create M:N relationships, the lists can't define two levels of the same dimension; instead, you must create two dimensions. So, we needed to separate the report lines from the chart of accounts, then map the relationships between the report lines in a Reports table and the accounts in the ChartOfAccounts table.
To accomplish this remodeling, we first created a new table and a new dimension—Reports—that reflected the structure of the reports without containing the chart of accounts. Reports is a parent-child dimension that contains all the lines in all reports. Figure 4 shows the Reports dimension table rows that correspond to the report lines in Figure 2. The table's UO field contains for each report line a unary operator that controls how level-member values roll up to their parent's values. If the unary operator is +, the value of the member is added to the aggregate value of the preceding sibling members. If the unary operator is -, the value of the member is subtracted from the aggregate value of the preceding sibling members. The unary operator value ~ means that the value of the member is ignored in the rollup process. In the Reports table, we created another field called Order, which determines the sort order for displayed members; we use this member property instead of sorting by member name or member key.
Our team's second step in remodeling table relationships was to map the relationships between the report lines in the Reports table and the accounts in the ChartOfAccounts table. Our new map created a new fact table called ReportStructure, which ties the chart of accounts to the individual report lines. This table contains three fields—Report_Line_ID, Account_ID, and Include_In_Rep—and includes a record for any account from any level that's included directly (i.e., not through its parents) in any leaf-level report line. If we want to include an account in a report line, we have to add a record to this table. The value of the Include_In_Rep field shows how to include a given account in the report. A value of 1 means "include as is" and a value of -1 means "change sign, then include." The Include_In_Rep field can have any value (not only a 1 or -1), and as you'll see in a moment, the model will multiply the account's data by this coefficient before including it in the report line.
The ReportStructure table has two keys: Account_ID and Report_Line_ID. By using this table as a fact table related to the ChartOfAccounts and Reports dimensions, we created a second cube—the ReportStructure cube—which maps the M:N relationships between accounts and report lines.
Because our team might include in our reports accounts at any level—not only the leaf level—we had to enable non-leaf members with data in the ChartOfAccounts dimension. To do so, we set the ChartOfAccounts dimension's Members with Data advance property to Non-leaf data hidden.
Analysis Services has some special features that are tied to its Measures dimension, and we used those features to convert the account data to report-line values. We moved into a separate dimension called Variables all the items that made up the Measures dimension in our previous model and left the TrialBalance cube with one measure, Value. Figure 5 shows our final model.
Be aware that a drawback to moving measures to another dimension is that you can no longer use special features in your OLAP front end that are tied to the Measures dimension. For example, in our case, we discovered limitations to on-the-fly calculations that involved the Measures dimension. But we devised a workaround for this problem that's satisfactory for our situation, and although the workaround is too complex to cover in this article, it lets us keep the items in the Variables dimension as measures without losing the benefits of our solution.
Prev. page
1
[2]
3
next page 
