Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Eric Curran]

Hi All,

I did a search here and on the Dynamics 365 usergroup but did not see what I was looking for.

I am looking for a way to better understand the table structure from d365 online so I can create user activity reports in power bi as the provided reports in d365 aren’t up to par.

Is there somewhere where I can get a better idea of the table structure?

I’ve connected my pbi desktop to the odata feed for our d365 instance but there are hundreds of tables and I’m not sure where to start to obtain the information I’m looking for.

Thanks!

Eric

——————————
Eric Curran
IT Systems Analyst
Cincinnati OH
——————————

[Hasham Niaz]

Hi,

I think you need to understand Dynamics 365 online, this is no more about user tables.

The back-end architecture is based on entities, which could be spreaded across multiple tables.

Sam recently shared a blog which might be helpful for you to get started;

https://www.linkedin.com/pulse/power-bi-dynamics-365-crm-go-from-data-insights-minutes-sam-kumar?lipi=urn%3Ali%3Apage%3Ad_flagship3_profile_view_base_recent_activity_details_all%3BUdGVoV2WT9ONov2imfFbWg%3D%3D&licu=urn%3Ali%3Acontrol%3Ad_flagship3_profile_view_base_recent_activity_details_all-link

Regards,

——————————
Hasham Bin Niaz
Sr. BI Consultant
Karachi, Pakistan
——————————
——————————————-

[Eric Curran]

Thanks for your detailed reply, that blog post seems to have quite a bit of information so I will read through it.

Eric

——————————
Eric Curran
IT Systems Analyst
Cincinnati OH
——————————
——————————————-

[Brad Wallace]

Eric,
The table structure is a beast.  Attaching directly to D365 with PowerBI will require A LOT of time to remove unnecessary columns.  If you have the Azure BYOD (bring your own database), then it is easier to develop a SQL query from that database.  If you don’t have that option, then you’ll have to attach directly to D365, or customize the out-of-the-box PowerBI Sales Activity report template.

Regarding the entities.  D365 doesn’t have an “Activity” entity.  It breaks them out.  Specifically, you’re probably interested in the following entities: phonecall, email, appointment, etc.  It some cases, only an ID is present for a descriptive field, so you may have to join on another table to get the actual value of the descriptive field.

We’ve been in this exact spot.  We chose the BYOD option because it was suggested by the community and Microsoft.  It is much easier to manage Power BI reporting from D365 using this.  But, you’re paying a small Azure SQL Database bill.

I hope this helps,

——————————
Brad Wallace
RJ Corman Railroad Group, LLC
Nicholasville KY
859-881-6806
——————————
——————————————-

[Garth Woods]

Check out the XRMToolbox plugin “Power Query (M) Builder“, which makes it very easy to create Power BI reports based on Dynamics 365 entities…

——————————
Garth Woods
VP Information Technology
Marine Ingredients
Mount Bethel PA
5702606908
——————————
——————————————-

[Jefferson Daniel]

Yes the Query builder is amazing. Very useful for the initial data setup, and quite the time saver. Will easily cut the development time in half from what it use to take. A lot easier to deal with FetchXML vs the out of the box connection setup Power BI does with Dynamics. There a few gotcha with the query builder as you start doing more advance stuff, so helps to paste the code in Notepad++ or something and work the rest from there.

——————————
Jefferson Daniel
Sun Valley Solar Solutions
——————————
——————————————-

[Daniel Demers]

Hi Eric,

I’ve gone thru this same process in the past few months. The way you approach this will depend if you want to go straight from Power BI to Dynamics or if you prefer to use the Azure Data Export Service and do some of your discovery/grunt work in SQL via SSMS. I’ll assuming your going straight from Power BI to Dynamics 365 CRM. I would recommend to look at the SnapShot product, there’s a free versions which quickly and easily allows you to get a snap shot of all your tables and fields including any custom entities.

Which table you use for your activity report will more than likely which data you want to report on but another great tip I would suggest is have a look at the free content pack that Microsoft released for the sales manager. This PBIX file will give you a good starting point to find fields and tables, but just remember it won’t necessarily have your custom entities.
 https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/administering-dynamics-365/dn708055(v%3dcrm.8) 

One of the first tables you’ll want to start with is activitypointers.

——————————
Daniel Demers
Chief Digital Information Officer
Richard Jay Laundry
61423566444
——————————
——————————————-