How to create the semantic model for CRM ActivityPointer

[krish42]

Hi all,

  I have a table in Azure SQL DB which is the data exported from Microsoft CRM ActivityPointer table.

This table has a field named RegardingId which refers to multiple tables in CRM(Task, contact, account, salesorder, quote, incident, email, phonecall, lead, oportunity bla bla).

What is the best way to create the data model to retrieve all the data from the related tables along with the Activity details?

Contact and Account tables directly relat to the ActivityPointer table whereas the other tables relate as below

ActivityPoint -> Opportunity -> Contact -> Account

or

ActivityPoint -> Opportunity -> Account

Do I need to split RegardingId into multiple columns in the ActivityPointer table and relate to other tables?

Or Do I need split ActivityPointer into multiple Fact tables which is very complex?

Please suggest

[alish_b · Accepted solution]

Hey @krish42 ,

It is a little difficult to recommend you an exact solution based on this information. On a higher level though, you should make sure that you follow star schema and you could go through this rich documetation from Microsoft about the same: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If you require more focused help, please representative sample or example of your data so that we could understand it better and provide feedback accordingly (because what might feel obvious to you can sound alien to us).

Hope it helps!

[Thejeswar · Accepted solution]

Hi @krish42 ,

The Details you have given are not sufficient enough to give a very precise direction on setting up a data model.

But at high level you can keep into consideration the following points while re-designing this

1. The Model should be as close to a Star Schema as possible (i.e. 1 Fact and Multiple dimensions)

2. Since the Activity and Contact can be connected directly to the fact table, that should be set up

3. Instead of having a wide table focus on having a vertically long table. With very limited understanding of your data, I guess introducing separate columns for the regarding id, would only introduce lot of NULLs in to the table.

In the section where you have mentioned the relationship for the other tables, it may not be possible to explain on the next actions without much context on what columns are involved in the relationship between each of these tables

If possible share a pbix with some dummy data for the tables that you are trying to put into a datamodel. That will make things faster to resolution

Regards,

[alish_b · Accepted solution]

Hey @krish42 ,

It is a little difficult to recommend you an exact solution based on this information. On a higher level though, you should make sure that you follow star schema and you could go through this rich documetation from Microsoft about the same: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If you require more focused help, please representative sample or example of your data so that we could understand it better and provide feedback accordingly (because what might feel obvious to you can sound alien to us).

Hope it helps!