Designing an Enterprise Data Warehouse – where to start?
When faced with a new Enterprise Data Warehouse development, it’s hard to know where to start. Many people make the mistake of starting with some reporting requirements. Unfortunately, this leads to narrow silos that can’t accommodate new requirements. Initially, it’s better to anchor your high-level design to the core business processes of the business. If you do this, you will meet 80% of your reporting requirements without ever seeing them!
To align your Data Warehouse with the strategy, objectives and core processes of the business, you first need to understand the concept of the Organizational “Value Chain” and how this leads to Business modelling and Data Warehouse design. This ultimately feeds back into strategic decision-making.
Understanding the Organization’s Value Chain and conducting Business modelling to define the Data Warehouse design massively enhances the value derived from Reporting and Analysis.
Organization Value Chain
What is an Organizational Value Chain?
Organizational “Value Chains” were first introduced by Michael Porter in his book “Competitive Advantage“.
A value chain is a set of connected activities an organization carries out to create value (i.e., products and services) for its customers. The value created for the customer should be greater than the cost of production, and the result is the margin. A value chain is a systematic way of examining these internal activities and how they interact to enhance customer value, margin and competitive advantage.
Porter divided his activities into the following:
- Primary activities. Primary activities are related directly to the production, distribution and sale, maintenance and support of products or services.
- Support activities. These activities support the primary functions above, E.g. Procurement, Human Resources, IT, Accounting, Legal, Administration, etc.
By understanding the collection of primary and supporting activities, a data warehouse designer can start to form a view of the data warehouse that aligns with the activities and strategic goals of the business.
To learn more about the Value Chain, I recommend watching this video – Value Chain Analysis EXPLAINED | B2U | Business To You – YouTube.
Business Modelling
“Business Modeling” describes the process of capturing details of the Organizational Value Chain in a format appropriate for Data Warehousing. The actual Data Warehouse “design” work happens in this process.
What is Business modelling? – “The act of describing the properties and relationships of a set of business activities and the business entities involved in the business activities.”
- Business activities: The primary and supporting activities identified in the Organizational Value Chain.
- Business entities: The “who”, “what”, “where”, “when”, and “why” that participate in or are produced by the activities.
The Business Modeler must distinguish between business activities and business entities. This distinction is key to success. In later sections, you discover that business activities become one or more Facts in the data warehouse, and Business Entities become Dimensions.
The Business Model also serves the purpose of identifying the integration of business activities across business entities. I.e. The business entities used in common across activities. These common entities become the conformed dimensions of the Data Warehouse.
The Business Model also provides a common language to the business. It becomes the basis of a Business Glossary or Data Dictionary.
Use a Data Warehouse Bus Matrix to capture the business model
You can use a Data Warehouse Matrix to capture and communicate the Business Model at a high level.
A “Data Warehouse Bus Matrix” describes the high-level design of a Data Warehouse. At a glance, it shows all the facts and dimensions of a data warehouse and their relationships in a table-like ‘matrix’.
for example:
The Data Warehouse Bus Matrix is a vital tool for documenting the design of the data warehouse, the value chain activities it covers, and their relationships with Business entities. It can quickly communicate the value chain coverage, scope, status and high-level design to stakeholders, developers and end-users.
Grab your free Data Warehouse Bus matrix template ->
“I’ve used this Bus Matrix spreadsheet on all my projects to help design, plan and estimate data warehouses and communicate the scope and progress to stakeholders.”
The feedback loop between the Value Chain, Business Modeling and the Data Warehouse
If executed correctly, there is a circular relationship between aligning your data warehouse with the value chain and the feedback from the data warehouse into value chain strategic decision-making.
- The Value Chain drives the Business Model.
- The Business Model defines the Data Warehouse design.
- The Data Warehouse Matrix captures the Data Warehouse design.
- Reports and Analysis source their data from the Data Warehouse.
- Insights and Analysis feedback into strategic decisions about the value chain, increasing customer value, margin and competitive advantage.
The objective is Reports and Analysis that reflect the Value Chain, Business Model and Data Warehouse design, aligning reporting to business goals and delivering data and analysis that feeds directly into strategic decision-making.
Importantly, the common business language permeates at every stage, driven by the Value Chain and Business Model.
