Dynamics CRM and Power BI

[Titus_D]

Hi Experts,

This is the first time i am using power BI and connecting Power BI and Dynamics CRM, since this was my fist time there were lots of back and forth until i understood that Power BI desktop is the place where i have to do everything and then publish. There are couple ofproblems i ham running in to.

The powerBI desktop is very slow and often get the message saying ran out of memory, i have connected only 8 entities and no one have more than 50,000 rows, is there a problemin the way i have conencted the dataset oritsacomputer problem, i have 8 GB RAM.

I am little confused in setting relationships especially in the below case to which one should i connect the System User Table. I want to connect it to Opportunities and Account. I tired connecting and the numbers are not matching because in our organisation the owner of an account can create oportunities in other Accounts, so now if i put a filter on the opportunities with the owner name the Results dont include the opportuunies that the sales person owns in Other Accounts owned by other users. And the vise versa too, Accounts owned by users is missing some because someopportunities in the account are owned by someone else. How do i over come this.

[lbendlin]

Your PC is a little short on RAM. You may want to upgrade to 16 or 32 GB.

The data model entirely depends on the business question you are trying to address.  Ask yourself which entities are depending on which other entities.  What are you measuring, and what are your filters. 

If you are measuring a value in a depending table then that is a "fact" table and should sit in the middle of the data model.  If you want to show your data by a particular field that also controls other fields via hierarchy then that table is a "dimension"  and should sit on the outskirts of the data model.  Filter direction goes mostly from the outside to the inside.

[Titus_D]

Hi Ibendlin,

Thanks for your response.

I think i am missing something very basic in the way i am modelling it. Below is the one i wanted to resolve. The part that confuses me is a user can own an account and opportunities under the account, at the same time the user can just own the account and the opportunites under the account can be owned by another user

I am looking to find a way where i can pull a report on the number of Opportunites created by a user even if the account is not owned by that user.

I am also looking to to pull a report where the number of accounts owned by a User and and it should return Accounts even if the opportunity under them are not owned by them.

Is there any articles you can point me to find a solutions for this

[lbendlin]

Opportunities usually have Opportunity Teams in addition to an Opportunity Owner.   Your data model would be something like 

Accounts => Opportunities <= Opportunity teams <= Reps

What you definitely don't want are circular relationships. Some of that you can control via active/inactive relationships and with the filter direction controls.

[Titus_D]

Thanks for your response.

We do not do team ownership all of them are owned by the sales reps

[Titus_D]

In my case the dimention table is the user table and the primary key to connect the user table exist in both Opportunity and Account. If i connect the dimention table with Account and try to slice data in Opportunity, opportunity belonging to the user residing in accounts not owned by the user dont get reported, how do i get over this.

Microsoft Dyanmics CRM has Advanced find which does this like a piece of cake but i am not able to do the same slicing in Power BI because i am not able to relate them in the way they should be and i dont know how.

Kindly help.