A data warehouse is a great thing in concept, and a wonderful thing in actuality!
Who could argue with the concept: “a central repository for data integrated from various sources, organized for reporting and analysis.”
Anyone who, in actuality, has worked in Business Intelligence can appreciate the absolute necessity of “centralized data” organized purposefully for reporting and analysis. It’s a given that a successful BI rollout depends on well-structured, purposefully organized data. Unfortunately, more often than not, things are not purposefully organized beforehand, and that workload always lands on someone.
A well-explained rationale for this Data Warehouse (DW) and Business Intelligence (BI) nexus is spelled out in the article, aptly titled: The Role of Data Warehouse in Modern Business Intelligence. The piece describes, in basic terms, the definition/capabilities of a data warehouse and its purpose in a BI strategy.
At one point it says that a data warehouse “is used to store current and historical data of interest to an organization and is used to create analytical reports for knowledge workers throughout the enterprise.”
The former is true, about storing current and historical data. However, rare is the case that the data warehouse itself is “is used to create…reports,” at least as the only means to do so. Even if there are in-built reporting (and data-analysis) tools in a data warehouse, a company would almost certainly depend on a BI product for most robust, distributed reporting and analytical capabilities.
We wholeheartedly agree, then, that a data warehouse (whether created in a DW application or done by skilled staff) is foundational to a good BI strategy…but it would be a mistake to assume that a well-created data warehouse, while necessary, is alone sufficient for a high-performance BI solution.
The plain fact is thata BI solution must provide for much more than what a data warehouse offers.
Consider the revealing comment that a DW “stores current and historical data”: while this is unarguably true, it is also a fact that a data warehouse is rarely used for future-based Business Intelligence data. (And when it is used for future-based data, a DW performs lamely—the subject of a different blog.)
By “future-based” data we mean data related, most often, to budgeting and forecasting. Beyond those uses there’s all manner of planning data that is not stored in a DW: price planning, payroll planning, and on and on, even to “macro” activities like strategic planning.
Where does this data live? Almost always within mammoth Excel files. Higher-level staff may not admit this…they may think that the statutory “plan” data must, like other application data, live in back-end relational database tables.
But IT and FP&A staff, from bitter experience, know the truth: Business Intelligence plan data is all over the place, in ten-thousand and one disparate spreadsheets.
While it may be so that some plan data finds its way back into a data warehouse (or other relational tables), this spreadsheet-plan-data problem is true to the world over. Plan contributors send their budget figures in spreadsheets, which then makes their way into bigger and ever-expanding spreadsheets, whereby Excel becomes not only a frighteningly unstable planning application (for budgeting/forecasting/etc.) but also the data store of last resort for BI plan data—precisely the data that companies need most to drive strategies forward.
Does this fact—that plan data typically (and problematically) lives in Excel—negate the need for a data warehouse for Business Intelligence purposes? Of course not: well-structured data will always be necessary for “historical [to] current…reporting and analytics.”
That said, a different—we should say complementary—strategy should be considered for plan data.
One such choice is to use a data warehouse that allows you to manage the database tables through its own interface, while at the same time offering OLAP capabilities for Planning activities.
The ability to do these things at the same time At the same time—and this is most relevant as concerns budgeting and forecasting applications and the like—is especially useful. If you can conceptualize that certain capabilities are game-changers, like accepting “write back” plan numbers; on-the-fly aggregations and formula results; centrally stored metrics, and; capabilities to accommodate large numbers of users who need to work collaboratively in planning models, then you are well on your way!
If this sounds interesting, check out the Planning solutions from PARIS Technologies which include a data warehouse and a OLAP modeling capabilities.
