fact table design question

SSAS 2005 - I have the following 3 tables:

T1: dimA_id, dimB_id, dimC_id, prod_id, dollar_amt_1

T2: dimA_id, dimB_id, dimC_id, cat_id, dollar_amt_2

T3: cat_id, prod_id.

cat_id and prod_id has a parent to child relationship.

In this case, I have to build two fact tables, right? There is no way to combine T1 and T2 into one fact table because the dollar_amt 1 and 2 are at a different level of prod_id and cat_id. I just wanted to make sure that I am doing the right thing. It seems that there are repeated data (dimA_id, dimB_id, dimC_id) in both tables.

Thanks.

But if cat_id/prod_id are modelled as a Parent-Child dimension (ie. using a single key for members at all levels, with a parent key), then data could be loaded at both levels from a single fact table.|||Good point. I will give it a try. Would this be a better appoach than the two fact-table one?|||Should be more straightforward, at least - but large parent-child dimensions can cause performance problems. If this dimension is small, it may not be a concern.|||The cat table has more than 1/2 million records and the prod table is 2-3 times bigger than the cat table. Is this considered large?|||

According to the AS 2005 Performance Guide, that is large, but you could try it and check:

Parent-child hierarchies

Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.

If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels.