Forum Discussion
Advocate ICRM Modelling in Power BI
This question is largely theoretical so if I don't receive any answers I won't be surprised. I am looking for tips on how to model CRM data in Power BI. Specifically, we use Microsoft CRM and there is a concept of a "Sales Funnel". In my organization, the end goal of a CRM power bi report revolves around reporting the success rate of converting things through the sales funnel. For instance, a user may use the website and this generates a Lead, then a salesperson contacts the user and verifies some information and they become an opportunity, with some luck the opportunity becomes a quote and the quote becomes an order. We want to be able to analyze the conversion rate between each status. In Microsoft CRM, they are able to do this with activities by having a FromEntityId column where the id in the column may reference a record in any number of entities rather than in a traditional foreign key relationship where it would only reference one entity.
My team and I have tried a number of different approaches to reporting this in power bi and we have succeeded; however, the models seem bloated and slow in my opinion. Our current approach is modelling each conversion as it's own fact table i.e. FactQuotedOpportunities, FactOrderedQuotes. The issue I've found is that a measure that must jump across multiple fact tables and conformed dimensions is incredibly slow.
I know this is a very general modelling question but if anyone has found a good way to model this in Power BI I would love to hear it. Even if it is just a hint at the approach they found. I am sure every company is different in how they use Microsoft CRM or other CRM's.
We have something working but I would like to scrap it so I am intentionally not posting more information in hopes of someone giving a tip that completely changes the way I approach this. I have read up on SQL BI and Kimball and consider myself relatively good at power bi modelling but this one stumps me.
Thanks
2 Replies
- amitchandak
Super User
Yanant1020 , To have a funnel, You can use measures like
1. Opportunity Count/Amount
2. Quote Count/Amount
3. Order Count/Amount
If you are creating a single fact there will be stages, if you have three facts, these will be measures. If needed, create a calculation group on top of this
You need to have a star schema. The Thumb rule is Facts do not talk with each other and dimensions do not talk with each other. Fact interacts with dimensions and Dimension interacts with facts.
You need to have the Date, Employee, Customer, and Geography dimensions. You can have more than one date table if needed. But one common date table that should join with all facts(unless it is dateless fact)
Assume customer master has city id and order fact also city id, then you will not join geography with both. You will merge customer and geography to have a customer dimension with the city, and state information. and geography as a dimension only to join with facts
Master and details transaction table will merge to create facts
Hope this can help
https://www.sqlshack.com/implementing-star-schemas-in-power-bi-desktop/
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
Yanant1020When I initially posted that we have solved this with multiple fact tables I should have clarified that we do NOT connect the fact tables. They are connected through conformed dimensions (i.e. DimQuote connects to FactOrderedQuote and FactQuotedOpportunity). While this pattern works, it results in really poor performance when trying to view the Sales amount for an opportunity. This would be calculated by transversing 6 tables and 5 relationships. DimOpportunity -> FactQuotedOpportunity <- DimQuote -> FactOrderedQuote <- DimOrder -> FactOrderLine.
I have not found a way to transform this into a single star, especially considering multiple sets of M:M relationships. For instance, an opportunity can generate multiple quotes and a quote can generate multiple orders. Also one order may result from multiple quotes and one quote may result from multiple opportunities.
