two fact tables: CRM and ERP (finance)

[texmex]

Hello Community: 

My challenge is I have a pull from our CRM database that has the usual suspects....leads, customer, and of course revenue potential.  

My other data set is from our ERP system which contains our actual revenue from orders received.  

My task is to somehow marry these two data sets in a way that I can pull our 50 and 80% probability revenue from the CRM data, and merge it into our ERP data so we can see both forecasted revenue (from CRM), and actuals (ERP). 

It seems I need to have two fact tables - one from the CRM sales opportunities, and the other from the raw ERP data which would typically be the main "fact" table.   And then somehow combine the sales pipeline forecasts with our actuals to end up with a total view of the financials.    Any suggestions on how to data model this would be VERY appreciated.   

[v-yulgu-msft · Accepted solution]

Hi @texmex ,

Please post sample data of the two fact tables loaded into desktop and show us your desired output after combination. How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu