Why do you need a Data Warehouse ?

You want great Business Intelligence

like this

But your Data looks like this

and that’s just the start of your problems

Your Data

• Is spread across multiple systems. How do you join across all these data sources ?
• Has quality issues. * This is a real life case.
• Has quantity issues. There is too much data. Imagine querying across 5 years of 1.5BN sensor records, to breakdown into average hourly buckets. That would take a LONG time in a standard relational database.
• Loses History.  For example, imagine a sales person who works in region A. All the sales made by the sales person roll up into the sales figures for region A. Imagine said sales person moves from region A to region B. Now suddenly, if you are reporting directly from the operational system, the sales person’s past sales now roll up to the sales figures for region B. Undesirable.

So… How do you get from this…

to this…

Option 1 – Self Service BI

Its quick, but you duplicate your wrangling/modelling effort, loose consistency across reports, and loose history. Many companies have hundreds of reports… It’s not maintainable or sustainable in the long term….

Option 2 – Data Warehouse

Centralized, Single version of the truth that delivers end user productivity.

The downside is its time consuming…

That is where Data Warehouse Automation comes in …

Dimodelo Data Warehouse Studio – Data Warehouse Automation Tool

The benefits of a data warehouse

• The ability to integrate data from multiples source to provide analysis across business domains. I.e. across financial, HR, operations, sales etc.
• Single version of the truth. One of the issues encountered when users report from operational systems, is the inconsistencies they can create between reports using their own queries, formulas and definitions. Meetings become about measure definitions, instead of about strategy. A data warehouse provides consistent measures, periods, rollups, ranges, KPIs etc. across the business.
• Keep historical data and do analysis of the past as it was in the past. For example, imagine a sales person who works in region A. All the sales made by the sales person roll up into the sales figures for region A. Imagine said sales person moves from region A to region B. Now suddenly, if you are reporting directly from the operational system, the sales person’s past sales now roll up to the sales figures for region B. Undesirable. A Data Warehouse has methods of preventing this issue, keeping past sales associated with region A, and new sales are attributed to region B.
• End User Productivity. In any organization there is a subset of people who spend part, or all of their day producing information in one form or another. Typically they spend much of their time wrangling dirty data. One of the benefits of a data warehouse is end user productivity. All that data manipulation is already done, and the users can concentrate on analysing and responding to information, rather than producing it.
• Eliminate Personnel Risk. Business logic is quite often locked up in spreadsheets or visualizations (Power BI, Tableau, Qlik etc) managed by individuals. If that person leaves, there is no one left to manage that spreadsheet(s). Centralizing your business logic (measures, KPIs etc.) means they can be managed and handed over in an orderly way.
• Remove load from operational systems. A single analytical query can cause major performance issues for an operational system. Separating reporting and analysis load to a data warehouse removes adverse impacts on operational systems.
• Complex measures and data augmentation. A Data Warehouse provides consistent data augmentation for reporting purposes that aren’t available in a source system. A data warehouse provides analytics functions like relative period (e.g. MTD, YTD etc), periodic and rolling calendars (e.g. Christmas Period, Public holidays etc.) , definition of acceptable ranges, targets, KPIs, aggregation or disaggregation of data, and periodic balances (e.g. end of month balances).
• A Data Warehouse can keep historical data beyond the normal retention period of operational systems.