Ms. Dynamics CRM: Power Query

Friday, 23 May 2014

Connect to CRM 2013 OnPremise as OData Feed Service Excel Power Query Without Windows Credential (Join Domain)

In My Previous Post, I was talking about Power Query for generating the CRM Data. The data source can be various, from SQL Server, Online Search, Public Data, Web, OData, etc.

Now, I am talking about connect to CRM Data using OData Feed Request.

Here, are some good blogs explaining about connect to CRM Data, either Online or OnPremise, good for reference and inspiration.

*Online
http://niiranen.eu/crm/2013/12/connecting-crm-online-odata-feed-excel-2013-power-query/

Using CRM Online and Office 365 Authentication

*OnPremise
http://www.mindthecloud.net/?p=1020

Using CRM OnPremise and Windows Authentication.

If you have OnPremise Data, you also can access using SQL Server Authentication, it is easier.

But, right now, I want to access to CRM Data (OnPremise), that I cannot access to the SQL Database, and now my Windows Credential is not supported, since my PC is not joining Domain. I also don’t have the Office 365 Authentication.

Then, I try to access my CRM OData Feed :

Then, I click OK, it will prompt and challenge you for inputting correct Credential as I expected, since I don’t have default Credential for accessing this link.

Then, try to input the Credential

1. Using Anonymous

Result : The user was not authorized

2. Using Windows

Result : The user was not authorized

3. Try using Basic, also cannot.

Result : The user was not authorized

4. Using Web API

Result : I don’t know what value to be inputted as Key, so cannot be authorized.

5. Using Marketplace key

Result : I don’t know what value to be inputted as Key, so cannot be authorized.

6, Using Organizational account

Result : since this is not CRM Online and not support the Office 365 Authentication, so cannot be authorized.

I was thinking about this, it should be Basic can afford this one, but in this case, I cannot use the Basic Authentication, I don’t know why.

Then, I suddenly found a workaround by remembering some old-fashioned way, there is to use the Windows Credential Manager.

To fix this :
1. Go to Windows Credential Manager
It is located at: “Control Panel\All Control Panel Items\Credential Manager”
Or you can just type it in the Search Box of Windows.

2. Then Add a Windows Credential

3. Type in the Information you usually input to access your CRM through Browser.

Entry your Server URL (without port), let’s say you want to access :

http://mycrmserver:5555/Contoso/XRMServices/2011/OrganizationData.svc/
or
http://175.111.222.18:5555/Contoso/XRMServices/2011/OrganizationData.svc/

Just type the mycrmserver or 175.111.222.18

4. OK and Save, here is the summary.

Back to Your excel, and try connect using Windows Credential, and it will work.

You also can filter the Query using URL:
http://mycrmserver:5555/Contoso/XRMServices/2011/OrganizationData.svc/ContactSet

Hope it helps!

Using Excel Power Query Online Search to Get Tons of CRM Data

I was attending a SQL Server 2014 Launching in Malaysia. Most of them were talking about PowerBI and SQL on the Cloud. Then, one of the best feature I can realize is the capability of Power Query to Query using external data source from Online Search.

It means that Power Query has capability to extract Public Data into directly to the Worksheet Table, without letting the user open the browser, then getting the data, copy one by one to the Excel table records. Wikipedia is one of the greatest source, they provide us the raw data.

Then, I was thinking to utilize this capability to get many data to our CRM demo purpose or Live purpose. When I had a presales activity and demo, what I did at the time is getting the raw data from Google then transform it into the Excel Table. Well, it is not efficient, since the data format from that Web is not in Excel or .CSV, so that I had to copy the data one by one to build a Excel Dataset to be converted as .csv file to be imported to CRM Database. Preparing data for demo sometimes is requiring more effort than developing it Smile

Okay, then luckily, I found this method and I will explain to you.

So, my requirements are:
1. Get the Country List around the world, together with the ISO Code as Country Code
2. Get the State or Province List of Indonesia

What you and I need are:Excel 2013 or older version with Power Query.

You can download the Power Query add-on from this URL :
http://www.microsoft.com/en-my/download/details.aspx?id=39379

And here is the prerequisite for Power Query

System Requirements

Supported Operating Systems:
Windows 7, Windows 8, Windows Server 2008 R2, Windows Server 2012, Windows Vista

Windows Vista (requires .NET 3.5 SP1)
Windows Server 2008 (requires .NET 3.5 SP1)
Windows 7
Windows 8
Windows 8.1

Microsoft Office 2010 Professional Plus with Software Assurance
Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone

Then here is the link about Power Query :

http://office.microsoft.com/en-001/excel-help/introduction-to-microsoft-power-query-for-excel-HA104003940.aspx

Then, here is the Magic.

1. First, go to Excel
2. Go to Power Query and Search Online
3. Type what I want to search and get the result

4. Double Click and I get the Data

I can manage the Column as well to adjust my CRM Fields

5. Then I do the same thing for my next requirement.

Let’s say you have multiple data source, then you can use Merge as well

If I don’t use this feature, I have to go to the Public Data, for example Wikipedia to get the data and then copy paste to our Excel file.

And beside that, I also can go to specific web URL and specific Database as source as well, based on the Authentication, of course, since some of them are not public data.

From Web :