Modeling Fact tables for a Hierarchy

[jayan]

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]

Can someone please respond?

[ngalemmo]

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.

[winipcfg]

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?

[jayan]

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.

[IIT'ian]

Can someone please respond?

[LAndrews]

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 .

[ngalemmo]

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.

[Sponsored content]