Modeling Fact tables for a Hierarchy

View previous topic View next topic Go down

Modeling Fact tables for a Hierarchy

Post  jayan on Thu Aug 02, 2012 12:53 pm

I have the following requirement and need advice on the best design approach -

Categories -> Employee, Products, Customers/Vendors, Cigarattes etc.;
We have location hierarchy: Chain -> Operations -> Market -> District -> Community -> Store.

1. Specific metrics are associated for each category. Employee category has metrics like High Risk count, LineVoids etc;
and Products category has metrics like Shrink%, SAGP% etc; Similarly each category has specific metrics.

2. Metrics are analyzed between different operations, Markets, Districts..

3. Timeframe analysis is different for different categories. Employee metrics are aggregated at week level for last 4 weeks.
Rx P&L is aggregated at Month level. Other categories are analyzed in different timeframes.

Here are my questions -
i) What is the best practice in modeling the dimensional hierarchy table for location? I'm planning to have a denormalized table for the location hierarchy.
ii) I would like to know how I should design the fact tables with specific metrics for each category. My approach is have fact tables for each category and store the metrics associated to that category in that specific table. The granularity of all the fact tables are at the store and week level.

Please suggest me if there is a better approach to design the hierarchy table and the fact tables. Also, the loading strategy of the fact table.

jayan

Posts : 3
Join date : 2012-08-02

View user profile

Back to top Go down

Can someone please respond?

Post  jayan on Fri Aug 03, 2012 1:52 am

Can someone please respond?

jayan

Posts : 3
Join date : 2012-08-02

View user profile

Back to top Go down

Re: Modeling Fact tables for a Hierarchy

Post  ngalemmo on Fri Aug 03, 2012 4:58 am

There are two ways to represent hierarchies in a dimensional model, either flat as you suggested in #1, or as an exploded hierarchy bridge table.

Either one works and both are acceptable.

As for the metrics, it make sense to break out employee metrics and product metrics into different fact tables.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling Fact tables for a Hierarchy

Post  winipcfg on Fri Aug 03, 2012 5:35 am

ngalemmo wrote:There are two ways to represent hierarchies in a dimensional model, either flat as you suggested in #1, or as an exploded hierarchy bridge table.

Either one works and both are acceptable.

As for the metrics, it make sense to break out employee metrics and product metrics into different fact tables.

Would you mind if you explain a little bit more about the bridge table?


winipcfg

Posts : 2
Join date : 2012-07-30

View user profile

Back to top Go down

Question on linking the category dimension to individual facts

Post  jayan on Fri Aug 03, 2012 1:51 pm

Thanks for your earlier response. Here is the link that explains the concept of Bridge table.
http://jsimonbi.wordpress.com/2011/01/15/kimball-hierarchy-bridge-table/

Here are few more questions that I have -

I would like to take advice in modeling the date dimension for the below scenario -

The business wants to analyze the Employee metrics in the below manner -

PeriodID MetricID LocationID ProductID Line Void% Line Amount
10 1 10010 26 23.4% 4000

We are recieving the Employee related and other metrics as above. Period id indicates a particular week, however the Line amount "1000" indicates the amount for the last 4 weeks at the store level (granular level in the location hierarchy).. Customers/Vendors category information is stored at the 13 week level..

Please let me know how to model the data dimension for the above requirement and also the keys that I should include in each of the Facts.

jayan

Posts : 3
Join date : 2012-08-02

View user profile

Back to top Go down

Can someone please respond?

Post  IIT'ian on Mon Aug 06, 2012 5:51 pm

Can someone please respond?

IIT'ian

Posts : 2
Join date : 2012-08-06

View user profile

Back to top Go down

Re: Modeling Fact tables for a Hierarchy

Post  LAndrews on Tue Aug 07, 2012 12:13 pm

I'm not sure what you are looking for here.

The dimensions are modelled independent of the fact. The date dimension typically has one row for each day.

You're challenge is with your facts. You need to define the grain of the fact, and then establish the date context of the measurement.

If the grain of your data is daily, then the fact is associated with the date dimension for that day.
If the grain of your data is weekly, then the fact is associated with the week. You can either have a separate week dimension, or you can use your date dimension (e.g. use Monday for the week facts).

Your example is more about the reporting requirement than the data model .

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Modeling Fact tables for a Hierarchy

Post  ngalemmo on Tue Aug 07, 2012 6:44 pm

And if you use a date row from a date dimension, it is a good idea to assign a role name to the fact FK column so it is clear you are representing a particular time period beyond date. For example, if the date pk column is date_key, and you have a monthly aggregate fact, use month_date_key as the fact fk column name.

It doesn't matter which row it points to, as long as it is a row within the month you want to represent. Normally you establish a convention (such as the first or last day of the month) so that such a key is consistently assigned across facts. When used in this manner, you ignore dimension columns that don't apply.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modeling Fact tables for a Hierarchy

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