Datawharehouse Fact Table Geneate

Currently, I am building fact table base my ODS tables and dimension tables. My ODS Table like

1.dateWiseData

sid_date
total_impressions
revenue

2.devices(Mobile PHONE,PC,etc)

sid_date
device_id
device_name
total_impressions
revenue

3.AdvertiserData

sid_date
advertiser_id
advertiser_name
total_impressions
revenue

Fact Table

id
sid_date
devicesID
advertiserID
total_impressions
revenue

Here, We facing issue to join all three table data in single fact table. In this case we have three different total impression and revenues on each tables. But in fact table we just add only single total impression. How we can calculate it and join then. We tried different join technicians but didn't find perfect solution.

Please help us handle this case

What are the first three tables? Dimensions or aggregate facts? How would you expect a join to work?

Your data is in different level of detail and there is no correlation between this data. For example your phone revenue cannot be same as revenue from advertisement. And “dateWiseData” is summarize data by date. Is “dateWiseData”  sum of revenue from device data and advertisement data?
Why do you want to save data into 1 fact table.

Thank You for very quick reply
Yes, You got my exact point regarding "dateWiseData'. Its summer of devices and advertiser data.

All tables are related with like date If I Group by sid_date of advertiser/devices and sum of total_impressions/revenue it is exact same as "dateWiseData".

We are not stick with 1 fact table is there any alternate also appreciated.

zoom wrote:Your data is in different level of detail and there is no correlation between this data. For example your phone revenue cannot be same as revenue from advertisement. And “dateWiseData” is summarize data by date. Is “dateWiseData”  sum of revenue from device data and advertisement data?
Why do you want to save data into 1 fact table.

Thank You For quick reply

What are the first three tables?
1.dateWiseData :- Its our ODS table. In that we are storing date wise impression and revenues.
SID_DATE | Total Impression | Revenue
2015-12-05 | 100 |500

2.devices :-Its our ODS table. In that we are storing devices  wise impression and revenues with particuler date.  
SID_DATE | DEVICE_ID | DEVICE_NAME | Total Impression | Revenue
2015-12-05 |10001|PHONE | 50 |250
2015-12-05 |10002|COMPUTER| 50 |250

3.AdvertiserData : Its our ODS table. In that we are storing advertiser  wise impression and revenues with particuler date.  
SID_DATE | DEVICE_ID | DEVICE_NAME | Total Impression | Revenue
2015-12-05 |20001|Advertiser1| 40 |200
2015-12-05 |20002|Advertiser2| 20 |100
2015-12-05 |20002|Advertiser3| 40 |200

And we want fact table like

Fact Table

id
sid_date
devicesID
advertiserID
total_impressions
revenue

In fact table we sum up(Group By) sid_date data then Total_Impression and Revenue will exactly same as dateWiseData (table) on particular date.
In fact table we sum up(Group By)sid_date, devicesID data then Total_Impression and Revenue will exactly same as devices (table)on particular date.
In fact table we sum up(Group By)sid_date, advertiserID data then Total_Impression and Revenue will exactly same as AdvertiserData (table)on particular date.


Let us know if you need more information from our side.

ngalemmo wrote:What are the first three tables?  Dimensions or aggregate facts?  How would you expect a join to work?

If data in "dateWiseData" represents a summary of devices and advertiser then you do not have to store it unless you are addressing SQL performance issue..... meaning if data summarization takes too long to get result back then create a another Fact table and store that summarized data there. It is acceptable to store your devices and advertiser data into 1 fact table.
Make sure when you insert devices data into the Fact table then your advertiserID is null. And when you insert your advertiser data then devicesID is null.


Fact Table

id
sid_date
devicesID
advertiserID
total_impressions
revenue

One way you can implement is create two facts table which will store the data device and advertiser wise and have a summary table or a materialized view to store date wise. How is data in date wise related with device and advertiser? is date wise a sum of device and advertiser data for each date.

thanks