Dynamics CRM Report using Excel 2013, oData and PowerQuery.

Cannot say how excited was when I finally found a way to solve the problem of reporting Dynamics CRM Online data with all its restrictions. Hail Excel. Not any Excel, but Excel 2013. Well not just Excel alone, but it was the glue that is bringing different technologies together and made this possible. This is an extension of my previous post on getting around the 10K record limitation.

Working for my customer to build a report that brings Contacts and its associated Account information. These are the ways I tried and their limitations. This is a CRM Online deployment and we have close to 40K contacts and an almost equal number of accounts. I did narrow the record count to by using filters judiciously to not make the system crawl. With these restrictions in mind, here is what I did. Hopefully this keep head in proper shape by not banging on the wall.

a) Advanced Find: Very nice tool to quickly visualize if something possible and I was get all the columns required to provide the report from Contacts and its associated accounts. Viola, this was easy, so I thought. Ran the query and came back with, you guessed correctly, the first 5000 records only. OK, onto the next solution. (I know there is way and tool to increase this limit. Not an ideal scenario users running searches and ending up pulling so many records. )

b) Report to the rescue, so I thought. I took the FetchXML from the Advanced Find and fired up BIDS 2008 R2 and started to put together the report. (Check SDK for how to do this). I placed the correct columns from Contacts and Accounts in the report and hit preview and viola, I instantly got the report to run in Preview. Well, Preview was able to pull in the first 500 contacts that matched the criteria. So I created a NEW report in CRM, used the RDL file from BIDs and published it in CRM.  I was able to specify the criteria and clicked on “Run Report”. Well I should hand it to CRM, it did run, I’m guessing that it timed out due to heavy load of 33K records it was pulling for this report. So I was able to taste a small bit of the fruit, but the not the whole yet.

At this point, I’m wracking my brain on how can I pull 60K records from CRM Online and produce this report. If anyone can come up any other ways of producing this report, please do let me know. Maybe I’m doing something wrong.

c)These two articles from Surviving CRM Blog gave me this idea on dealing with ta large set of data. (http://niiranen.eu/crm/2013/06/dynamics-crm-data-visualization-with-geoflow/ and https://www.youtube.com/watch?v=1er6bu9nmlk ).

SOLUTION:

  1. Download and install PowerQuery for Excel 2013. (Update: PowerQuery has become a part of Excel 2013 with sp1).
  2. Export to Excel a small list of records, can be anything within CRM Org as a Dynamic worksheet. CRM will spit out an .xml file.
  3. Open this in Excel 2013. Under the DATA tab, click on Refresh from CRM to fetch the data and also complete authentication. Save the file as an .xlsx file. (This step is very important to our goal).
  4. Create a oData query using XRM Tools solution withont the org. Note: You cann create the query with related records. Using this in feed definition does not bring in the related record fields. oDate_Query
  5. In my case, I had to create two oData queries, one for Contacts and one for Accounts. In the contacts query, i did pull in the parent Customer Id field in order for me to join with Accounts in Excel.
  6. After you have the queries created and tested in the Browser, in the open Excel 2013 Workbook, create a new Sheet and select “From oData Feed” under DATA/GetExternal Data Tab/From Other Sources”.
  7. Paste the oData query into the link section and complete the dialog with default options. Create a New Sheet when fetching the data executing the oData query.
  8. Do the same for accounts as step 7 with query created in step 5.
  9. In order for records in these two sheets called “Contacts” and “Accounts” to be used in PowerQuery, they have to brought in as a Table from PowerQuery ribbon tab.
  10. You will end up creating two more sheets called “Table_Contacts” and “Table_accounts” for each PowerQuery Table that you create.
  11. There is a MERGE option in PowerQuery ribbon, that will first ask you to establish a unique relationship between these two Powerquery Tables. In my case I selected “Table_Contacts.Parentcustomerid” and “Table_Accounts.Account.
  12. Result is a a new Column with an embedded Table in it. Well this is good that it is presenting as such, however I want the embedded Table column to be expanded into its own fields aslongside with fields from Contacts.
  13. Once you complete the above steps, click on “Done” from PowerQuery.
  14. Bang, you have a report of all Contacts with account data for all 33K contacts.

Let us review what we have accomplished. we have created a report in Excel 2013 with master detail relationship for more than 5K or 10K record, which are a limitation in CRM Online. PowerQuery is awesome tool and thanks the SQL server BI teams for building this tool.

NOTE: all of this possible only when you CRM for Outlook client configured to talk to CRM Online or CRM On-prem instance.

Hope this helps.

Advertisements

CRM 2011: Increase export to excel limit (10000) – Different Approach

Installation Type: CRM Online an/or On-Premise.

Here is the solution if you have CRM Online. I know this works. Using Excel 2013(will not work on 2010).
1. Export a very small subset (say 100 records) of data using Web Client(Browser) using Export to Excel functionality.
2. In the Export dialog, choose “Dynamics Worksheet”. When finished, CRM will spit out an xxx.xml file.
3. Save to Desktop and open it within Excel 2013. Under the Data ribbon tab, select “Refresh from CRM”.
4. Once this is done and you see the exact same set as Export criteria, you can save this file as “.xlsx” file.
5. Create a oData query using open source tools to retrieve say contacts modified in last 30 days.
6. Under Data tab in Excel, “Choose a oData feed from ‘From Other Sources’ under ‘Get External Data’ group”.
7. Paste the oData query into the Browse text box and click NEXT, NEXT, OK.
8. Viola. It will be slow if you have lots of data and you might get kicked out during the import. Be careful with this approach since there is no way to cancel the download. If you have large sets of data, you might want to write oData query that will break it down into smaller pieces.

What I like with approach is it is not a change at Organization level. We can provide this as a feature for Power Users who is more interested in analytics using Power BI (PowerPivot/PowerView/PowerQuery). This approach will bypass the 10000 record limit with Export to Excel from CRM. Just now exported almost 33K contacts record with 19 columns of data. Not sure where the limit is for this approach.

Enjoy!
Prabhakar