What is a Bus Matrix?
“The Enterprise Bus Matrix is a Data Warehouse planning tool and model created by Kimball”
Below I will explain what that means.
What is the purpose of a Bus Matrix ?
Bus matrix allow us to present the relations between measure group (business process) and dimensions (group by / filter by). By creating a bus matrix it is much easier to understand the overall objective of the data warehouse by both technical and business people which helps a lot when it comes to understanding each other.
How a Bus Matrix is created?
Before I explain how to create a bus matrix let's clarify two definisions: Measures, Measure Group and Dimensions
Measures is everything what you can measure in a business process so basically any "fields" where you can apply aggregate fuctions like min,max, count, sum. An example of a measure is "Total Visits" = count of visits. So when you visited this webpage our count of visits increased by 1.
Measure Group is simply a collection of measures related to one "business process" for instance we can have Visit as a business process and we can measure it by "Total Visits" but also we can have "Average Time Per Visit" or "Average Position from search engine". All of these measures belong to one measure groups (business process) called Visits.
Dimensions are "a subject" important from business perspective with a set of attributes that you can use to group by or filter measures. For instance you can have Country dimension that will have two attributes: Country Name and Continent. So you can show Total Visits by Country or Continent (or both). So if you visited this website from UK total visits would increase by 1 for UK country and Europe Continent. You could also show total visits by country (group by) for Europe only (filter by).
NOTE: Dimension "natural key" is on the lowest level of "the subject" and you use attributes (other fields) to describe it usually on higher level. Date is a very good example. You have Date dimension which "natural key" is date for instance 14th of April 2012 and you have attributes (fields) that describe it for instance DayOfWeek: Saturday, Month: April, Year: 2012. The same applies to other dimension and thanks to this approach you will generally have very few dimensions but with many attributes inside that is very easy to understand and intuitive to business users.
So let's explain how I created my bus matrix. I created it in Excel spreadsheet.
As you can see below my "empty (inside)" bus matrix has dimensions on the first column starting from date and finishing on Source and Measure Groups in first row and contains Visits, Impressions and Links.
Before you create bus matrix it is a good idea to create a list of measures and dimensions by asking yourself or business users to describe their business world using words “something by something“ where the first "something" is measure/business process and second "something" is something we want to group by (or filter by) for example : Visits by Page , Visits by Keyword, Visits by Country.
Please see below my list that is taken out from Building a datawarehouse day 1 requirements gathering
Once your list is created you can use to create measure group / business process (left side of something in our case) and dimensions (right side of something in our case) and put a *in the intersection between measure group and dimension which shows relationships (very easily).
Please see below final result of a bus matrix.
Emil: Hi Katie it's me Emil! I believe this is your first bus matrix you created.... well done! It is clear and I can see straight way you missed several relationships for impressions and links :p (which were not covered in my questions & answers and are only obvious to end users) but that is good because that is the purpose of bus matrix! to see big picture and see if there is anything missing :D .... let's discuss on day 2 building a data warehouse.... but I can give you a general tip: Date dimension is almost always part of every measure group (business process).
Hope that helps