How to extract Datawarehouse Bus Matrix from Cognos Framework Manager Model

Data Warehouse Bus Matrix, shown below, is a key design tool representing the organization’s core business processes and associated dimensionality. It’s the architectural blueprint providing the top-down strategic perspective to ensure data in the DW/BI environment can be integrated across the enterprise. This design tool can be used at different level during the life cycle of project:

-       High level requirement, decide what dimension with what facts should be built

-       Communication between business requirement and technical design

-       Detail design to denote the relationship between columns from fact and dimension.

We can also some retrieve this information from Cognos framework manager model.  There are different ways to get this information. This document provides a very practical way to do it.

Step 1:  Open model.xml from FM model from XML notepad, then remove all nodes except relationships, then save it as model2.xml

 

Step 2:  Open saved model2.xml from Microsoft Excel with spreadsheet

 

Step 3:  Further remove all columns except 4 major columns as below

-       Remove all detail columns

-       Remove name space such as business layer

-       Make sure all dimension listed on left and facts on right side

-       Remove duplicated records

 

Step 4:  Make the spreadsheet as three columns below

Step 5:  Pivot table and filter whatever dimensions and facts, please note that you can use grand total to identify usage of dimension and sort them

Step 6:  Copy spreadsheet and paste as value and then make it is presentable as the first screenshot.