Challenge of Financial Reporting in Modern Analytics Platforms like Power BI
Updated: Sep 5, 2022
Different departments or subject areas may have specific requirements and goals for building visualization and data structures. Reporting requirements of the finance domain have some peculiar needs that should be understood and addressed to ensure successful delivery and adoption.
“Not all data models are created equal!”
Finance reporting requirements mainly consist of Financial Statements - Profit and Loss Statements, Balance Sheet, and Cashflow. Understanding finance data structures and basic knowledge of financial statements is essential to cover these requirements in a modern analytics tool like Power BI or Tableau. You can read more about the functional aspect of financial statements here.
We will be addressing one such quirk related to hierarchies in this blog. We will be handling the solutions in the subsequent blog series. Understanding the issue in depth will help customers to establish patterns and change management processes to overcome them.
Historically, financial reporting requirements have been served well with multi-dimensional data models, e.g., Analysis Services Multidimensional Models or SAP BW Cubes. A financial reporting solution should address the need for hierarchical reporting– to be more specific, and the product must be able to handle ragged hierarchies. It is essential to deliver financial statement templates, although it is not the same as hierarchical reporting.
When creating hierarchies, there are two basic choices: balanced or unbalanced (ragged).
A balanced hierarchy has symmetrical levels or an equal number of levels. Each member of a given level will have the same number of members above and below. Typical examples include date hierarchy with levels like Year > Quarter > Month > Day. The balanced or the balanced hierarchies are native to the Tabular model and are easier to create. The data can be summarized and shown in the hierarchical format using any column or attribute. Some tools refer to this as an attribute-based hierarchy as well.
Ragged hierarchy, on the other hand, can have varying levels. Common examples include an organizational chart where a high-level manager has departmental and non-managers as direct reports. In the example below, the Executive Assistant and Corporate Secretary report directly to the CFO and do not have any direct reports.
In other words, a ragged hierarchy is different because the logical parent of at least one member is not at the level immediately above the member. The hierarchy descends to varying levels for different drill-down paths when this occurs. This is critical as the data model and the visualization engine must accommodate unique drill-down paths with different levels.
A Financial Statement Templates are a particular case of hierarchical data representation where the data doesn't necessarily roll up or belong to the same group. In the example below, the users want a particular layout with empty rows and Gross Margin % in the Profit and Loss statement. The exact layout is a crucial
aspect of such templates.
The modern tabular/columnar data models struggle with ragged hierarchies. The ragged hierarchies are handled natively in the multi-dimensional model, while the balanced hierarchies are handled natively in a tabular model. The multi-dimensional models made the single key figure hierarchical reporting possible.
Not addressing this can lead to disillusionment, and the finance team is left with the wrong impression about the modern data warehousing platforms. Most customers are left wondering,
“Why can’t the modern analytics platform do what was accomplished rather easily by legacy data platforms?”
When the customer reaches this point, they have already lost faith, and implementation teams are left fighting the perception battle. Once the initial impression is formed, it is nearly impossible to get the trust of the end users. Reach out to us to learn how to address the finance teams' hierarchical and structured financial reporting needs.