cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dennisr
Helper I
Helper I

Can Power BI access Dynamics CRM saved queries?

Can Power BI access Dynamics CRM saved queries such as System-Defined Views or User Queries?

(If so, how?  I don't see them in the Navigator popup when I connect to CRM source)

 

 

7 REPLIES 7
Anonymous
Not applicable

Power BI can't do this at the moment. When connecting to Dynamics I use an odata connection, you can apply filters in the odata url that you could replicate an advanced find with.

I'm actually more interested in "Public Views" which can include linked entities...

 

I've just tried Power BI: Get Data > Other >  OData Feed;

My custom entity fields are of Data Type 'Option Sets', and they don't seem to come down into PowerBI.

I see Odata supports the $expand  option - do I need to use that just for Option Sets?

 

Some of the nice things about Views are

  - users can set them up in CRM

  - uses the 'Display Name" of fields

  - Linked Entities

 

 

@dennisr Another thing to look into depending how in-depth you are getting on reporting for your CRM solution is the new solution templates. Release notes here. This appears to offer up the ability to gain a huge shortcut to implementing a full solution. I wouldn't know, because I just finished implementing our CRM in Power BI solution a week before they released this. 😞 Tears were shed.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Tom - RE: "Power Query will give you the expand option "

Would you please clarify that?   I'm in PowerBI Desktop, and the Fields listed do not include any fields defined as Option Sets

 

@Seth_C_Bauer Most of my entities are custom, so I don't think Solution Templates will help me

Anonymous
Not applicable

When you select a field that is an option set or a lookup value through oData through Power Query it should atomatically give you the option to get the values (if it's an option set) or the lables if it's a lookup value.

 

I have a simple odata url that pulls StatusCode from Opportunity:

.../xrmservices/2011/OrganizationData.svc/OpportunitySet?$select=StatusCode

 

When that appears in the Query Editor you get the option to expand the record:

 

odata example.png

 

into the option set values:

 

odata example 2.png

OK, I found the 'Expand'.  However, it's a pain to have to expand dozens of fields.  It seems that doing an expand has Power BI desktop make a metadata query back to Dynamics, which takes a long time - 40-50 seconds per field (!), since they need to be done one at a time.  Clearly, there needs to be a setting in Power BI that automatically does the expand and selects the Value or Label, and/or the ability to expand multiple fields at the same time.

(Note, that whe you use FetchXml with the SDK, it does automatically get the underlying value/label. )

Anonymous
Not applicable

I'd also love the ability to connect to existing advanced finds and agree that it would be useful for us as well. Might be worth adding it as a suggestion! 🙂

 

In terms of option sets, select them in the oData query in the normal fashion and Power Query will give you the expand option to get the value that you then might want to map to labels.

 

The $expand in oData is used for getting data from related entities.

 

For instance I have a query that points to the out of the box Incidents entity but then goes off to Account and Contact to collect data from the related records, then apply a filter for the creation date:

 

$expand=incident_customer_accounts,incident_customer_contacts&$filter=CreatedOn ge datetime'2014-01-01T00:00:00'

 

Personally I use a solution that is avaliable on the XRM tools codeplex that is a massive help, it generates the URL for you and I've only ever had to do minor tweaking, it also will work with your custom entities and attributes.

 

I don't know the rules on posting links but a quick google search for dynamics CRM odata query designer will point you to the XRM tools codeplex.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors