The Curious Case of Financial Consolidation

One of the goals of a data warehouse is to provide a single source of the truth, as the basis of management information across the organization. Data is collected from various transactional systems, is integrated and aggregated, and made available through business intelligence tools and performance management applications. According to analyst firm Gartner, performance management comprises financial consolidation, financial reporting, scorecards, planning and budgeting, and profitability management.
However, many finance professionals question the need for a data warehouse as the source for their financial consolidation process, instead of linking financial consolidation directly to their general ledgers. They are right. I'll discuss why, from an architectural, procedural and practical perspective.

Although financial consolidation is generally accepted to be an important, if not crucial, part of performance management, financial consolidation has some characteristics fundamentally different from other a management processes. In fact, as a management process it looks more like transactional process. Most management processes, such as plan-to-act or analyze-to-adjust are iterative of nature, and very hands-on. Users take an explorative view on the data, where one question leads to another. Financial consolidation, as part of record-to-report, in contrast, behaves more like transactional process, such as order-to-cash or procure-to-pay. Financial consolidation should be linear of nature, and as hands-off as possible. The level of automation and standardization should be as high as possible, to secure a reliable, auditable and repeatable process. As a consequence, for financial consolidation purposes there is no added value for adding the data warehouse as an intermediate storage stage in a further transactional process.


Financial data has specific requirements regarding the chain of custody. It needs to be 100% accounted for, auditable and every step needs to be retraceable. Based on Sarbanex-Oxley regulations both the CFO and CEO need to personally sign for the accuracy of the information. Routing the financial data from the general ledger through the data warehouse before feeding it to the financial consolidation system is not only an unnecessary step, but it endangers the chain of custody. It introduces governance issues, as the data warehouse processes and houses many types of information owned by multiple business domains.

Due to US GAAP and IFRS regulations, financial data transformations are highly standardized. Although the ETL (extract-transform-load) or data integration tool used by the data warehouse may functionally be able to deal with the complexity of financial data transformation and integration, there is no point in reinventing the wheel, if this logic exists prebuilt in standard financial consolidation packages. Best-of-breed prebuilt functionality allows finance professionals to make allows finance professionals to make ledger-to-headquarters mapping decisions directly, and see the impact on the balance sheet balances, financial ratios and accounts. These requirements, particularly in multiledger environments, can live at odds with IT-managed complex ETL processes that should be focused on maximum control and stability.

Does this mean that the Finance department should be excluded from the data warehouse? Not at all. The majority of finance run processes, and a large part of finance-related information are managerial of nature. Operational management needs to have insight in the financial consequences of their operational decisions, and require financial management information. Financial management, in return, needs to be able to access operational management information, to gain insight in the business' value drivers, and as such improve and maintain financial predictability. Also from a planning perspective, operations and finance need to be integrated. Financial and operational information both belong in the data warehouse, in an integrated manner.
Once it is understood that financial consolidation is fundamentally not a management process, but an operational process, the architectural issue disappears. The financial consolidation system, like any other transactional system, should be seen as a source to the data warehouse. This might raise the issue of timeliness. Financial consolidation takes time, and other types of data might be routed into the data warehouse sooner or with a higher periodicity. That problem can be solved like with any other source system; distinguishing between preliminary and final data loads.

Keeping financial consolidation outside the data warehouse architecture is not an 'exception to the rule', but a matter of architectural soundness, combined with an understanding of governance issues and simply a practical solution. Key to keeping the architecture 'clean' is the understanding that financial consolidation is not a management process, but transactional of nature.


Excellent article. I use this train of thought for chart of account design as well. Often the number of chart of account segments can grow substantially, when you take into account all plan-to-act or analyze-to-adjust activities. My approach is to leave this information within the datawarehouse, which can utilise the slice and dice capabilities, and summize the high level, consistent segments within the ledger, which at a minimum must support regulatory reporting requirements.

Posted by Thuy on September 09, 2009 at 07:50 AM PDT #

Frank, You have a good point, and made it very clear. However, I have two remarks: 1) During a (operational) financial consolidation process, you cannot always work with the final statutory figures of all your entities due to time-constraints (some are done earlier than others). So, on many occasions you will start with whatever is finished, and proceed with the figures that are later available (shoot on moving ducks). This means that the consolidated figures already include the not-yet-finished figures also. Auditors are horrified by such a situation, because if you access the statutory database directly, they will be unable to see what 'last minutes' booking entries have been added/"made behind their backs", and, more important, what effect these bookings have on the consolidated statements and the entries they have already performed. So working with "frozen situations" (=datawarhouse with time-stamp) is often a absolute requirement, which I am afraid is not possible with a direct access. 2) You will also be surprised how many entries are made AFTER the consolidation. In order to match your Equity over the years, such bookings are to be filtered out. This can be done by looking at the time-stamp in the statutory system (if present), but is IMHO easier to do when working with last year's frozen situation against you can match it. Regards, Martin van Wunnik ARSIMA Projects

Posted by Martin van Wunnik on September 10, 2009 at 08:09 PM PDT #

Frank, I read the article searching for an answer to the question: What are pros and cons of sending both financial and non-financial information from the operational systems to the data warehouse and from there feed the general ledger? The alternative now is feeding the general ledger directly from the operational systems, and feeding the warehouse with the same information but in more detail at the same time. "Same information at the same time" sounds as bad architectural practice. What is your opinion? Best regards, Frida Wessels

Posted by guest on December 17, 2009 at 08:29 PM PST #

Post a Comment:
Comments are closed for this entry.



« July 2016