"тнιѕ вℓσg ¢συℓ∂ ѕανє уσυя мσηєу ιƒ тιмє = мσηєу" - ∂.мαηנαℓу

Saturday 12 December 2015

How to Extract Dynamics CRM Fields to an Excel Sheet

This post is about how to extract Dynamics CRM fields into a Microsoft Excel sheet.  We might need this info for data migration / integration tasks. For instance mapping of Dynamics CRM fields against an old system from which we need to migrate  / integrate data.

Xrm Tool box provides a tool with this feature. Download link is given below.

http://www.xrmtoolbox.com/download.html


1.After download, extract the zip file to a custom folder.


2.  Open the XrmToolBox.exe. Choose Metadata Document Generator


3. Next step is to connect to the organization. So click yes.


4. Create a new connection by clicking on the New Connection button.



5. Create the connection with your CRM server / Online version details.


6. Once connected, Xrm tool box loads all the entities. Now choose your entity or all entities as per your requirement.  Please note the highlighted buttons. Click on Generate document button after choosing the options.


7. Thats it really. You could find your excel document with chosen attributes in the chosen folder.

Thanks to Xrm Tool Box !


Sunday 6 December 2015

Power Map

Power map is a 3-D visualization tool for Excel. Power map is based on Microsoft Bing maps. As the name implies its a powerful visualization map. Power map is more powerful when its used in conjunction with Power BI. Lets take a look.

Power map could detect the following data ( geolocation data)

• Latitude/Longitude (formatted as decimal)
• Street Address
• City
• County
• State / Province
• Zip Code / Postal Code

• Country/Region

Version used for this post:
Microsoft Office Professional Plus 2013
Power BI was activated from the office 365 login in order to utilize Power BI features.
Microsoft Excel -> File-> Options->Add-ins->Manage option set-> COM Add-ins->Go->Select Microsoft Power Map-> OK

"If you have a subscription for Microsoft Office 365 ProPlus, you have access to Power Map for Excel as part of the self-service business intelligence tools. Whenever any new Power Map features and performance enhancements are released, you'll get them as part of your subscription plan."
(Ref:https://support.office.com/en-us/Article/Power-Map-for-Excel-82d65bd7-70c9-48a3-8356-6b0e82472d74 )

We are considering 2 samples in order to understand power maps.

I)  Common scenario in Microsoft Dynamics CRM-  To plot the number of opportunities from certain area ( For instance Based on post code or City ). Its a simple scenario without using Power BI features.

II) Ref : https://eriksvensen.wordpress.com/2015/07/06/visualize-the-danish-mobile-network-history-coverage-using-powerquery-and-powermap-powerbi/

A sample mentioned in Erik Svensen's blog - Danish mobile network history and coverage. 

This sample is an amazing scenario.Full Credit: Erik Svensen with reference to his blog. Tak Erik Svensen !. This is a scenario using power BI features.

Scenario I: Plot the number of Opportunities based on the post code.

We have a table with postcodes ( UK  post code)  and number of opportunities in Excel as shown below.

1. Select the whole table first and then Insert tab-> Map-> Launch Power map





2. Create a new tour




3. Choose Geography and then Geography and map level as shown below. Select Zip against post code. And click next.




4. Drag the Number of Opportunities field to the Height as shown below.



5.  Use the tilt down / up , rotate right / left arrows. Also zoom in / out could be used to have better view.




6. Map label was one of favourites feature.




7. Heat map view. Heat map button is highlighted.





Scenario II

Ref : https://eriksvensen.wordpress.com/2015/07/06/visualize-the-danish-mobile-network-history-coverage-using-powerquery-and-powermap-powerbi/

A sample mentioned in Erik Svensen's blog - Danish mobile network history and coverage. 

This sample is an amazing scenario.Full Credit: Erik Svensen with reference to his blog. Tak Erik Svensen !. This is a scenario using power query- power BI features.

URL for Danish mobile network with reference to Erik's blog - http://mastedatabasen.dk/Master/antenner.json?maxantal=1000 ( In this sample we are selecting only 1000 records)

1. Power BI- Power Query is very useful for bringing data. Please note the tab Power query and the button from Web. Enter the provided url and press ok.



2. Records would be loaded as 'Record'. Click on the To Table button



3. Default values are ok in our case. Press ok



4. Next step is a bit tricky. Please note the highlighted tiny button.




5.  Lets learn Danish language : ) . We are not selecting all fields but only the necessary ones in this sample. Postnummer is post code. vejnavn is street name. Kommune is municipality. We could choose whichever fields we like. As i mentioned at the beginning Power map can detect certain data like post code ( see the full list at the beginning of this post)




6. Expand columns as shown below. Then you could  see the real data.



7. After expansion, you could see data like below. Once we have proper data, click on the close and load button.




8. Next step is to launch power map from the Insert tab. Then click new tour.




9. Select Column1.postnummer.nr  column and then select zip code against it as shown below. Click next.




10. For this sample height is not important. You could play around with it if you prefer. Its not chosen in the below picture.  You could use the tilt down / up , rotate right / left arrows. Also zoom in / out could be used to have better view.



11. Please note the buttons Capture screen ( once you click this button you could paste in paint or word etc just like screen shot, Create video etc. Here is a capture screen sample. Users could create video for presentations using the create video. Power map is very useful in Geomarketing.