Bridge table use with KPI categories

View previous topic View next topic Go down

Bridge table use with KPI categories

Post  othersider2 on Wed Sep 05, 2012 8:37 am

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!

othersider2

Posts : 3
Join date : 2012-09-05

View user profile

Back to top Go down

Re: Bridge table use with KPI categories

Post  ngalemmo on Wed Sep 05, 2012 1:11 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bridge table use with KPI categories

Post  othersider2 on Wed Sep 05, 2012 1:20 pm

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

View user profile

Back to top Go down

Re: Bridge table use with KPI categories

Post  ngalemmo on Wed Sep 05, 2012 6:51 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bridge table use with KPI categories

Post  othersider2 on Sat Sep 08, 2012 7:29 am

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

View user profile

Back to top Go down

Re: Bridge table use with KPI categories

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum