Bridge table use with KPI categories
2 posters
Page 1 of 1
Bridge table use with KPI categories
My application requires that we track performance on several key performance indicators (KPIs) over time. At the end of the month, each KPI is measured (KPIs can have different units of measure) and divided by its monthly target, yielding a percentage score. The scores of all the KPIs are stored in an aggregated fact table.
KPIs are categorized into a categorization hierarchy. There are multiple category levels (e.g., category, sub-category, sub-sub category, etc), and each category can either contain a sub-category or link directly to a KPI. I think the bridge table pattern described in Ralph's Data Warehouse Toolkit book would apply well to this situation, but I'm not sure whether the KPI would be a category itself, or if it is a separate entity that has a relationship to the category through the bridge table. In other words, should the design be:
1) Category Table --> Category Bridge Table --> KPI Table --> Fact (score)
or
2) Category Table --> Category Bridge Table --> Fact (score)
Can anyone help? Thanks!
KPIs are categorized into a categorization hierarchy. There are multiple category levels (e.g., category, sub-category, sub-sub category, etc), and each category can either contain a sub-category or link directly to a KPI. I think the bridge table pattern described in Ralph's Data Warehouse Toolkit book would apply well to this situation, but I'm not sure whether the KPI would be a category itself, or if it is a separate entity that has a relationship to the category through the bridge table. In other words, should the design be:
1) Category Table --> Category Bridge Table --> KPI Table --> Fact (score)
or
2) Category Table --> Category Bridge Table --> Fact (score)
Can anyone help? Thanks!
othersider2- Posts : 3
Join date : 2012-09-05
Re: Bridge table use with KPI categories
I guess either way would work, but a bridge requires a single dimension table (i.e. a homogeneous hierarchy), which could be done. Going down to KPI level may introduce query challenges.
Re: Bridge table use with KPI categories
Thanks for the reply. Would you be able to give an example of a query challenge that might stem from including the KPI in the hierarchy?
othersider2- Posts : 3
Join date : 2012-09-05
Re: Bridge table use with KPI categories
If KPI was a member in the hierarchy it implies each KPI is in a different row. How does the user query the table if they want to see multiple KPI's on the same line in the report? How easy or difficult this is depends on the tool and the skills of the user.
Re: Bridge table use with KPI categories
Wouldn't that be true of not just the KPI, but any member of the hierarchy? I use Cognos, so I'm hoping it is powerful enough to get around the issue you identified. Thanks for helping!
othersider2- Posts : 3
Join date : 2012-09-05
Similar topics
» How to resolve a many to many relationship? Bridge Table or Utilize the same fact table?
» separate fact table/different grain - do I need a bridge table
» Data in a fact or dimenzion table or bridge table
» Bridge tables versus massive junk dimensions
» Bridge table help
» separate fact table/different grain - do I need a bridge table
» Data in a fact or dimenzion table or bridge table
» Bridge tables versus massive junk dimensions
» Bridge table help
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum