Aggregate Fact for Hierarchy Levels

I am building a data warehouse. My lowest grain for Sales Fact is order per line item.

Fact:
Sales Order

Dimensions:
DIM_Country, DIM_RepLevels, DIM_RepUnits, DIM_Reps, DIM_JUNK_OrderDetail, DIM_JUNK_OrderHeader, DIM_Promotoin, DIM_Products, DIM_ShippingInfo, DIM_JUNK_OrderShipping, DIM_Payment_Type, DIM_Order_Source

Business also maintains sales rep parent child hierarchy. There are about 400000 active and inactive reps. I am building a bridge table for maintaining hierarchies with different levels. Business needs to rollup rep’s monthly sales into different buckets, like Sales per Rep Unit downline/upline, Team sale, Family sale (Parent + (everybody downline recruited by Parent - person recruited by parent at same level), Word Sales (Everybody under parent) etc. Since there are about 10 mil orders in order table, it could be very expensive to rollup different buckets or run the reports from the lowest grain fact table especially when they want to run them very frequently. I am thinking to create an aggregate fact table (grain at Rep,Month) that will hold these summary with additional attributes like world sale, family sale, team sale etc.

My question is:
1. Is my approach right?
2. Since Reps are promoted/demoted to different levels based on their sales each month, I am thinking to have slowly changing dimension type 2 for bridge table. Does anybody see any issue with that or is there any other kind of approach for this situation?
3. Do you suggest to have aggregated view fact over aggregated fact table?

Please advise.

There is another post where i think a similar process is being modeled
http://forum.kimballgroup .com/t2205-fact-tables-for-invoice-lines-with-further-granularity

1. Yeah, sure. If monthly summaries are fine for the analysis, using a much smaller table always helps query performance.

2. The pattern to maintain a hierarchy with a type 2 dimension is the same as with a type 1 dimension. The only difference is changes are more frequent. A change to a type 2 dimension generates a new key. A hierarchy structure is simply the relationship of keys. So, you expire the old key relationship and add the new key relationship (if you are keeping hierarchy history),

3. Not sure what you are asking. If it's 'is it ok to create aggregate tables from other aggregate tables?' Sure, why not? It's a practical matter more than anything else. There are costs every time you introduce a new data structure. If the new aggregate provides significant benefit to the cost, its fine. But most of the time, a greater aggregate doesn't really provide significant performance gains.

If the question is 'should I create views of the aggregate tables?' That is more of an interface issue than a database issue. Views are logical and provide an end-user access path that is not tied to physical database structures. They don't hurt, but the need to create them depends on how users will access the database. Most decent BI tools have metadata layers that are essentially views of the underlying database. So views in the database itself are redundant for that application.

Or did you mean something else?

Thanks Nick.

We need to keep the hierarchy history, so type 2 will be required. Do you advise to store Rep. Unit number in same bridge table since I need to store unit history along with levels or maintain seperate type 2 dimension for units? Units or Levels may change every month. So there can be multiple child levels under one unit, and multi units under parent rep. They get their sale commissions based on their levels.

Rep AA - Unit 1 - Level 10
>>Child AAA - Unit 2 - Level 6
>>>>Child AAAA - Unit 2 - Level 4
>>Child AAB - Unit 3 - Level 7
>>>>Child AABA - Unit 3 - Level 5
>>>>Child AABB - Unit 3 - Level 4
>>Child AAC - Unit 4 - Level 7
>>>>Child AACA Unit 4 - Level 5
................... AND SO ON

ngalemmo wrote:
If the question is 'should I create views of the aggregate tables?' That is more of an interface issue than a database issue. Views are logical and provide an end-user access path that is not tied to physical database structures. They don't hurt, but the need to create them depends on how users will access the database. Most decent BI tools have metadata layers that are essentially views of the underlying database. So views in the database itself are redundant for that application.

Or did you mean something else?

I am building the aggregated fact from the lowest grain, Item Order Fact table. Business will run all kinds of other reports from this grain.
I was referring to if it is advisable to create an aggregate fact view from lowest grain (order fact table) or create a new aggregate fact table from the lowest grain. I am leaning more towards creating a table rather than logical view, but wanted to hear what general practice is.

developerpete wrote:There is another post where i think a similar process is being modeled
http://forum.kimballgroup .com/t2205-fact-tables-for-invoice-lines-with-further-granularity

Thanks developerpete. For some reasons, the link does not work.

You create aggregates to resolve performance issues. Smaller fact, faster queries. A view is simply a query, so creating one will not help performance because you are still querying the same atomic level fact. If you want a aggregate, create a new fact table at a lesser grain.

As for the hierarchy, what is Unit and Level? Are these attributes of the rep? If so, they belong in the rep dimension. Maintain the hierarchy as a recursive parent/child relationship. You could do this in the same dimension, an maintain a hierarchy bridge table as relationships change.

ngalemmo wrote:
As for the hierarchy, what is Unit and Level? Are these attributes of the rep? If so, they belong in the rep dimension. Maintain the hierarchy as a recursive parent/child relationship. You could do this in the same dimension, an maintain a hierarchy bridge table as relationships change.

Units are when Reps are eligible to recruit other reps under them after they obtain Director Level (Level 6) or above (based on their sale goals). Level is a reps hierarchy within a unit and they have different commissions for different level goals. I know it gets little complicated. But Level is an attribute of Rep. Unit is maintained in a separate table. I am thinking to make a hierarchal bridge (type 2 to maintain history) for levels and create another dimension type 2 for units.