When starting a data warehouse or business Intelligence effort, we go through a step-by-step process to get the information necessary to design and construct the pieces to satisfy the effort. We do our Project Planning, then go through the Requirements, Design, Development, Testing, and Implementation phases. Basically, we follow a cookie-cutter approach.
DW/BI lifecycle steps
- Planning
Identify Project Scope
Project Planning - Gather Requirements/Requirements Definition
User interviews / Digesting Reports and Artifacts - Logical Design
Technical Architecture Design
Dimensional Modeling
BI Application Design - Physical Design
- Development
ETL Development
BI Application Development - Testing
- Deployment
- Maintenance/Support
*Note: additional items fall under each step, but only certain ones are highlighted here.
You may define and implement the above steps differently, but they would not vary much in composition. Ralph Kimball’s “The Data Warehouse Lifecycle Toolkit” and “Data Warehouse Toolkit” are key resources in my library and heavily influence my approach to BI projects.
What is a Bus Matrix?
One key item Kimball provides in the Requirements Phase is a Bus Matrix. At first, I barely gave it much thought; now I go out of my way to make sure I have one. It is both a design tool and a project artifact. It is a simple representation of the dimensionality to be associated with your data warehouse/BI environment, a guide to the logical design phase, and a mechanism to communicate the data in the overall architecture back to the business. It lists out subject areas and dimensions the environment we’ll build out and support.
When starting to build a bus matrix, you will take information from user interviews/surveys, and artifacts, such as reports. As you coalesce and boil the information down, start building key statements you can leverage. Statements such as, “I need to report on sales, by time, by product, and by customer,” as an example. The first part of the statement helps identify a subject area (focus of the business and metrics/measures), and the second part—every item that has a “by” in front of it—is identifying a dimension. Every time I hear “by” I immediately think of a dimension. The dimensions give context—or textual supporting information—to your subject area. Dimensions should be universal and will support 1 to n number subject areas. But how do I look at it in a clear, cohesive manner? This is where the Bus Matrix comes into play.
Example
Here, I reverse-engineered a Bus Matrix from the AdventureWorksDW Database. This will give you an idea of how the single matrix and representation communicate what is provided in this data warehouse.
Using Excel, you can easily start to construct, and keep refining until you believe you have a complete picture of what is to be built. You will see the dimensions you need to build and conform so it can used uniformly across your environment. For instance, when you report on a product, you want to be able to report consistently across any subject area that references it. You will see subject areas that will be the focus of your reporting and are representative of the business.
Next steps
Once you have your matrix, you can then take each reference on the rows and columns and create a tab in your workbook for each item and start drilling into the specific details it will have. Once you have details on your subject areas and dimensions, you can use it to start building your logical data model, then the physical data model, ETL processes, and so forth. Focus on laying out one subject area of a time, and the next thing you know you’ll be well on your way to your logical model. Remember, your Bus Matrix will not be doing a single pass and done. It will be iterative and updated as new information is received or refined. Lastly, if you need any help working out a logical, step-by-step approach to laying out your BI/DW projects, please contact us. You’ll be glad you did.
