Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Bridge table use with KPI categories

2 posters

Go down

Bridge table use with KPI categories Empty Bridge table use with KPI categories

Post  othersider2 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

Back to top Go down

Bridge table use with KPI categories Empty Re: Bridge table use with KPI categories

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

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

http://aginity.com

Back to top Go down

Bridge table use with KPI categories Empty Re: Bridge table use with KPI categories

Post  othersider2 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

Back to top Go down

Bridge table use with KPI categories Empty Re: Bridge table use with KPI categories

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

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

http://aginity.com

Back to top Go down

Bridge table use with KPI categories Empty Re: Bridge table use with KPI categories

Post  othersider2 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

Back to top Go down

Bridge table use with KPI categories Empty Re: Bridge table use with KPI categories

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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