0

I am currently trying to put together an EDW Bus Matrix to summarize an existing data warehouse/data model I have in place, but am not sure of the best way to show a particular relationship.

In my data model for a manufacturing business, I have a dimensional table for jobs/positions, and a dimensional table for workcenters.

Workcenters indicate the process where production was being done. Jobs indicate the position an individual was working in. A job may relate to one or more workcenters.

These two dimensions are joined through a foreign key connecting to a 'manning category' table/field. For example, there may be a job on each shift, or multiple people working on the same line that correspond to the same manning category. Likewise, multiple workcenters may have the same manning category if they are closely linked or run together at the same time.

I would like to try to summarize this into a matrix to make it easy for people to understand the relationship as well as help with future planning, but is there a common practice for handling this? I have not been able to find much information on this topic.

While by and large I am sticking to a standard star schema approach, there are a few examples like this in my model that require me to join dimensions through these snowflake type outer dimensions. It seems like this is fairly well accepted when necessary, but I was wondering if other have encountered this issue when trying to summarize their DW in an EDW Bus Matrix.

Any advice would be appreciated.

kanderson
  • 178
  • 2
  • 13
  • _I would like to try to summarize this into a matrix to make it easy for people to understand the relationship as well as help with future planning, but is there a common practice for handling this_. You're talking about visualisation and crosstabs. This is always a job for the visualisation / reporting tool. You should generally avoid generating crosstabs in the database as reporting tools manage this much better. – Nick.Mc Apr 11 '21 at 06:52
  • 1
    Dimensions are normally related via a fact table. What are the scenarios (i.e. what facts at what grain) where you couldn't add these 2 dimensions directly to a fact table but instead have to use the relationship? – NickW Apr 11 '21 at 09:19
  • The workcenter and job dimensions are on fact tables. The manning category, which has a relationship to either workcenters or jobs, is not. I could put it on the fact tables and achieve something similar, but it seems odd to have an additional fact which by nature is something that is defined on the the dimension tables, rather than something that may change for each fact. For the purpose of creating a bus matrix though, maybe that is the best way to illustrate the relationship. I was looking if there was a standard way to show this in a matrix, not whether it should be in the db or vis tool. – kanderson Apr 11 '21 at 17:00
  • 1
    A bus matrix shows the relationships between your business processes (rows) and your common dimensions (columns). You could add your manning category dimension as a column to your bus matrix but there is no way of showing a relationship between dimensions (apart from implied via a common fact table) - that's not what a bus matrix is for. If you want to show relationships between tables then use an ER diagram – NickW Apr 11 '21 at 19:49
  • Thanks NickW, that answers the question. I have the relationship shown in an ER diagram already, just wasn't sure if there was a standard way of incorporating it into a bus matrix. I'll go with that! – kanderson Apr 11 '21 at 20:53

0 Answers0