Using Account Intelligence to streamline your Financial Reporting
by Garry Farrell, Sydney Consultant
On the surface financial reporting may look simple. However, have you ever tried to create a profit and loss statement or balance sheet using a hierarchy? Typically these reports are built in Excel or other reporting tools, where a complex design is required to get the layout and calculations correct. Below I will talk about how to simplify and streamline financial reporting, whilst maintaining accuracy and repeatability using Microsoft’s Account Intelligence.
Account Intelligence is a feature of Microsoft’s Analysis Services, which allows cube designers to create dimensions that are mapped to standard account classifications for financial reporting and will aggregate appropriately based on the account type (e.g. Asset, Liability, Income and Expense). This support for a parent-child hierarchy enables a semi-additive approach, to handle when we need to subtract amounts in the hierarchy such as total revenue – total expenses rather than simply adding as the default aggregation does. The dimension is given a specific type of Account. Having the design in the cube ensures that all reports will have accurate data as all changes are inherited by cubes that use the dimension.
The characteristics of Account Intelligence are:
- Parent-Child hierarchy
- Unary Operator (Custom aggregation)
- Sort Order
- Account Type
- Custom Member FormulasA dimension of type Account must be created as the first step. These characteristics can be set manually or set using the Account Intelligence wizard.
Figure 1- Account Intelligence Wizard
The account dimension requires a parent-child relationship. This relationship uses the key of the dimension as the child and the parent key column from the dimension table as the parent. The parent-child relationship is used to aggregate the measures.
The unary operator column from the dimension table controls the type of aggregation. Possible operators are +, -, /, * and ~ to define how the aggregation will be handled as an addition, subtraction, division or multiplication. The tilde denotes that no aggregation will occur and an undefined operator will use the default operation of sum.
The sort order is used to sort the items within a level when placing them on a report in sets. The sort order will also sort the items within levels when browsing the hierarchy in tree view in an application such as Excel.
The account type column from the dimension table allows for the categorisation of the accounts. The standard types are Income, Expenses, Assets, Liabilities, Statistical, and Balance. At my current client they use Revenue instead of Income which we mapped in the database. In Solution Explorer, right-click the Analysis Services project and click edit database. The default aggregation function can also be set here.
The custom member formulas are used to replace the default aggregation with an MDX like formula. We implemented these for the accounting KPIs or ratios such as Revenue/Costs. These members can be used the same as other members of the hierarchy with measures and cube slicing.
The following diagram shows how the properties are set once the Account Intelligence Wizard has been run.
Account Intelligence works well and is easy to configure. It makes creating financial reports simple by implementing the unary operator and custom member formulas. The parent-child hierarchy that is required is easy to work with when creating reporting. A word of warning about alternate hierarchies, if you have alternate hierarchies you will need to consider carefully how to implement these for you solution. The parent-child hierarchy only handles one hierarchy per parent-child dimension.
To find out more about how you could use Account Intelligence to streamline your financial reporting contact us.