How to create the semantic model for CRM ActivityPointer

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!

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,