Relation between two fact tables having a one to many relation

Hi can I join two fact tables having a one to many relationship. first fact table around 30 mill lines, second aroun 56 mill. lines. I want to make a report over a small period.

For me is the first fact table then acting like a dimension.

Hello,

      We are also experiencing a similar issue. We want to design a fact table for subscribers doing voice calls. Ideally speaking when a subscriber say 'A' party calls to 'B' party , then a call detail record is generated with the details of the call made. The details present are like below :

A_PARTY_MOB_NUMBER
B_PARTY_MOB_NUMBER
CALL_USAGE (in seconds)
CALL_CHARGE (in currency)
BALANCE_INFO (BALANCE_1_ID,BALANCE_1|BALANCE_2_ID,BALANCE_2|BALANCE_3_ID,BALANCE_3........|BALANCE_N_ID,BALANCE_N)

Balance Info is coming under one column as above.

Now from the above we can see that number of balances used can by any depending on the call Party. May be 1 or 3 or 10 or 15.

Also another point here is that each Balance has a meaning like say BALANCE_1 is core balance BALANCE_2 is Free minutes etc. But the BALANCE definition can change dynamically say BALANCE_2 is Free minutes from 01-12-2015 to 15-12-2015 and from 16-12-2015 to 9999-12-31 it is Free SMS.

So when designing the fact table for each call entry made how can i store the BALANCE Info ??? Since it may vary from subscriber to subscriber.

Currently we have decided to do the following splitting the record in to two fact tables

F_VOICE_CALL_FACT
RECORD_KEY (Primary Key)
A_PARTY_MOB_KEY
B_PARTY_MOB_KEY
CALL_USAGE
CALL_CHARGE

F_VOICE_CALL_BALANCE_FACT
RECORD_KEY (Foreign Key referencing to Primary Key of F_VOICE_CALL_FACT)
BALANCE_KEY
BALANCE_VALUE

Like this i have one to many relationship between F_VOICE_CALL_FACT and F_VOICE_CALL_BALANCE_FACT.

Is this a correct approach. Please help me with this ...

Thanks,
Suhrid Ghosh

Without any other information you must assume all fact tables have a M:M relationship between them, so if you know it is 1:M how bad can it be?

The basic method to combine two facts is to first aggregate each fact based on the common dimensions (keys and/or attribute values) on which you intend to join, then join the aggregates on the common dimensions.  If you know it is 1:M then you can join the tables without aggregating first.

As far as the balance table goes, are you trying to capture a running balance on a call by call basis?  If so, that is about all you can do.  I would include the MOB_NUMBER for whose balance it is in the table.  I would also add a measure that contains what was consumed on that call for the particular balance category.

One premise of dimensional design is a fact table should stand on its own.   The 'child' fact should contain all appropriate dimensions that are found in the 'header' fact.  You should not need to join facts if all you want to report is balances. The need to join facts should always be an exception rather than the rule.

Hello ,

       Yes its a 1:M relationship we are trying to built here since we are breaking a single call record into two facts , one which contains the call information and the other which contains the balances used for the call. Since here number of balances used for a call is uncertain , we cant keep fixed number of balance column in the fact table. So we have splitted this. So for one call say 3 balances were used the the first fact would contain one record and the second fact would contain 3 records for the same call with a joining key between both the facts. I couldn't find any other way apart from this.

Will it be better if i put dimension keys in the second fact such as Mobile_Num_Key , Customer_Key , Account_Key , Rate_Plan_key ,  Date_key , Time_key which are already present in first fact which you have told to do.

Also we need to run aggregation on top of this two fact tables which would be on a daily granularity one mobile number one record per day like below :

Date,Customer_Num,Acc_Num,Mobile_Number,Total_Call_Count,Total_Call_usage,Total_Call_Revenue,Rated_Call_Usage
,Zero_Rated_Call_Usage ...

Here Rated_Call_Usage and Zero_Rated_Call_Usage is derived from the Secondary Balance Fact table based on the balances used for a single day for the mobile number.

So in this case i need to join both the tables. Hope you get what i am trying to do.

Thanks for looking into this in such a short time. Appreciate your help.

Thanks,
Suhrid Ghosh

Yes, each fact should contain all the appropriate dimensions. You do not have FK relationships between facts, that is relational modeling, not dimensional modeling.

As far as the daily aggregate goes, yes, you join (or union) the two tables after they have been summarized to the common grain.

Thanks for the tips ... Appreciate your help in such a short notice ...