Customer experience | Sales, Customer Insights,...

PowerBI Data Model

(1) Share

Report

Posted on by a_mat11

Hello Dynamics Community -

I am new to Dynamics 365 from a business analyst perspective, and am attempting to support my sales and marketing team with their requested reports. I have about 3 years experience in PowerBI so I am good on the 'how to build a report piece' once I get to that point, but I am having much trouble with building a data model. There are just hundreds of tables that are overwhelming and confusing to figure out how they all relate. I do see some relationship information in the customization section of Dynamics but I am questioning - is there not a standard data model available from Microsoft with relationships, etc. pre-built?

Or user-friendly views to connect to? Do organizations really weed through these hundreds of tables and fields themselves to get reporting going? Looking for something like the one available in the PowerBI service except available in the desktop so I can customize it with my own calculations and custom fields. I have already spent dozens of hours and only have a miniature slice of a model going.

Not being a hugely technical developer I feel somewhat stuck in the middle between this basic Power BI service report supplied and blogs about things that go well over my head.

I am willing to pay for a training/etc. as well if that exists somewhere. I found a lot of trainings that were 'how to connect' or 'how to use dynamics' but none for the business analyst side on what to do after you get connected other than it saying - okay you'll see all the tables, now go build - and it obviously doesn't work like that.

Appreciate any leads - trainings, webinars, documentation; I will say that I have spent the better part of a week googling this so I'm not sure what I am looking for is out there.

I have the same question (0)

All responses (4)

Answers (0)

muzzamal 5 on at

Like (0)

Report

RE: PowerBI Data Model

Hi,

I am also facing the same issue. The requirement was to create a dashboard with data from Accounts, Leads and Opportunity tables. Account Manager and Company Name filters should filter the leads and opportunities data on the same page. The relationships Power BI created automatically were not working so had to study the model, create and test the relationships manually. Still there is a lot of confusion in joining these three tables. I also downloaded the XRM toolbox, the advanced settings in the Dynamics 365 where all the fields and relationships are but nothing was helpful in creating the optimum data model. I am still looking for the solutions.

Was this reply helpful? Yes No
a_mat11 5 on at

Like (1)

Report

RE: PowerBI Data Model

Thank you very much, I appreciate it.

At this point, we have decided to hire out a consultant for a few hours to help get the ball rolling.

I am hopeful a quick overview of the model and data flow can get me where I need to be to complete the rest myself.

Was this reply helpful? Yes No
Suggested answer

Thijs van der Klei 780 on at

Like (1)

Report

RE: PowerBI Data Model

Gents,

Finding your way in the huge Dynamics 365 data model is challenging. I don't have a miracle trick that does the job. What I can offer you is some tooling.

The first thing I would do is check the XRM Toolbox. There is a tool called 'Entity Relation Diagram Creator' that you can use to produce an ERD of a part of the data model. In my example I just selected the Account en Contact entities. In XRM Toolbox there are more data related tools, like 'Metadata browser'.

Then you could install an extension in your Edge or Chrome browser: 'Metadata Browser for Dynamics 365 / Power Apps'.Being logged in in your environment, you can display metadata like relationships.

There is also a solution you can import in your environment. This adds functionality to browse your metadata.

Finally, as an end user I would stick to the user interface. End users can relate to information they see in forms and views. In the form editor, just double click the field you would like to have in your report. Then you can find the schema name.

I hope this helps you a little bit.

Kind regards,

Thijs van der Klei

for more information:
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/introduction-entities

Was this reply helpful? Yes No
Adil365 50 on at

Like (0)

Report

RE: PowerBI Data Model

I am currently going through the same process and I'm in the same boat. I work on the Dynamics 365 CRM application, so I know our tables & data, and have been working closely with my colleague who's a pro with Power BI. We tried using Power BI's autodetect relationship feature and it created 100s of bad relationships. I also tried using Microsoft's Sales dashboard app which you can download in Power BI Service but it is basically useless because it doesn't incorporate custom fields or entities, unless I'm missing something.

What we've done is build our Dynamics 365 CRM Power BI data model from scratch because there didn't appear to be any resources or information on how to best do this, which is very surprising to me.

While we've built a data model, and built a working sales dashboard, I know the data model isn't robust or flexible enough to meet our business dashboard and reporting needs. I really hope some one who's been through this already can provide more insights on this - power bi data modeling best practices, guidelines, and insights working with Dynamics 365 data.

Was this reply helpful? Yes No