What Came First? The Data Mart or the Data Warehouse?
So you decided that you can’t avoid Business Intelligence (BI) any longer. Starting from scratch may seem like a daunting task, but it is actually a very fortunate position. Starting from a blank slate can be much easier than trying to put past BI problems onto the right track. Very often people ask, “Do I build a Data Mart first or the Data Warehouse?” The answer is “Yes.” When you are just starting out, the most important consideration is keeping your eye on the big picture. Nobody can deliver the entire Enterprise Data Warehouse in one big project. If you are trying this approach, you are probably taking on a project that is too large and will likely suffer from continuously changing business requirements. Instead, it is important to try to deliver many, smaller projects that can demonstrate that they are delivering business value in a short period. These successes can ensure continued funding for future phases.
When mapping out your first BI project, step back and think big about what the end result should look like years from now. What major subject areas (or data marts) would you like to include in your Business Intelligence solution. Potential data marts could include:
- Finance
- Healthy & Safety or Sustainability
- Operations
- Marketing
- Human Resources
After considering the fully realized solution, ask yourself what data these subject areas (or data marts) have in common. At a minimum, they will share a common calendar. They could also share other common data and hierarchies such as:
- Geography (Locations, Offices, Sites, Regions)
- Organization (Business Units, Departments, Lines of Business)
- Account (Categories, Ledgers)
- Company (Subsidiary, Legal Entities)
- Customer (Client, Member)
- Projects (Programs, Initiatives, Campaign)
- Employee (Manager, Responsibility)
In addition to these dimensions that the subject areas might have in common, there may be a number of dimensions that don’t contain business data, but are used to describe the data (meta data). An example of this is a metrics-based data mart used to support a performance management function:
- Metric – A list of metrics or KPIs that the business calculates and monitors
- Scenario – The different types of metric data (Actual, Budget, Target, Forecast)
- Time Data View – The different ways that the data can be pre-aggregated (Quarter-to-date, Year-to-date, Previous Year, Last period)
Having decided on the data that could be shared across each data mart or subject area, you are now aware of the data marts will overlap. Understanding this, you will need to keep the data sources or future data marts in mind when you design and build these dimensions.
“When mapping out your first BI project, step back and think big about what the end result should look like years from now.”
The first data mart will need to lay the ground work for the common dimensions that will be used by the first project as well as the other components that will assist in scheduling processes and data validation. The back-end tables that manage your data warehouse might include:
- Refresh History – a table that contains 1 record for each refresh of the data. This will have an ID, the refresh date range (start and end dates), the status of the refresh (in progress, failed, completed) and any other attributes that you’d like to track for each refresh
- Refresh Parameters – a table that contains only one record. This record is a copy of the most recent record from the Refresh History table. A one-record table can be joined to any SQL statement as a Cartesian product (i.e. CROSS JOIN). This can add the start and end dates to any query to restrict the result-set to just the new and changed records
- Refresh Audit – This table will contain the audit or data validation results for each refresh. Row counts and data quality checks can be run on the data to validate that the refresh not only completed, but the result is correct and the data can be reported with confidence. This table should be linked to the refresh record in the Refresh History table that describes which refresh this audit relates.
- Greg Kirkham














