Power BI with Dynamics 365 CE – Publishing Power BI Report to the web

In the previous post, we learned how to create a simple Power BI Report using Power BI Desktop

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-creating-report/

Now let us publish the report to Power BI Service from within the Power BI Desktop.

To do so, select the Publish button

Publish window will ask us to save the report file (if not saved) before we could publish it.

Here we are publishing it to the default “My Workspace” app workspace within Power BI Service.

Publishing the report from Power BI Desktop publishes both the report and dataset associated to it in the Power BI Service. We can think of DataSet as the Database.

After successful publish we can see the Report and Dataset being added inside the Power BI Service (https://app.powerbi.com)

From with Power BI Service, we have the option of Publishing the report on Web so that it could be accessed by anyone having that URL.

To do so,

Select File
à
Publish to Web to publish it.

The Embed dialog box pops up as shown below with all the information.

Clicking on Create Embed Code will open the confirmation dialog box, that shows the potential risk associated with sharing the information over the internet.

Here, clicking on Publish generates the link for the report and also the HTML code which can be used for embedding it within an IFrame.

Within Dynamics 365 CE, we can use this public URL to show it inside the IFrame component of the Dashboard.

The report within Dynamics 365 CE’s Dashboard.

Next, we will see how to create Dashboard from the reports created and how to share them with other users and how to use the Power BI Dashboard and Power BI Tiles feature of Dynamics 365.

Hope it helps.

Power BI with Dynamics 365 CE – Creating Power BI Report

To begin with, let us first create a Dynamics 365 Trial.

https://trials.dynamics.com/

After our CRM trial is setup up successfully, we will install the OOB Sample Data which we would be using for creating the Power BI report.

Next, we need to create a free trial for the above user to use Power BI Service.

Go to Power BI site, and click on Start Free to get started.

Use the same user account (trial) that was used to set up the Dynamics 365 trial.

https://powerbi.microsoft.com/en-us

Note: If we already have an organization account with Power BI License we can use it to log in to Power BI else can associate a free trial to the same organization account instead of creating a new one. The gmail, hotmail, yahoo etc personal account are not allowed. https://app.powerbi.com/.

Download the Power BI Desktop as well as click on Try Free.

Power BI Desktop is basically a feature rich windows application that allows us to get data from various services online or on-premise and develop Dashboard and Reports efficiently.

The alternate way to download is through Microsoft Downloads siteà

https://www.microsoft.com/en-us/download/details.aspx?id=45331

After installing it and signing in and click on Get Data ribbon button to connect to the Dynamics CRM Online.

Select Dynamics 365 Online as the Online Services to connect to.

Specify the Web API URL of the instance.

Copy it from Customization à Developer Resources

Select the Organization Account and Sign in to the CRM Instance.

In the Navigator window, we have searched Lead entity and clicked on Edit to select the columns that we need for our sample report.

In the Query Editor window, click on “Choose Columns“, we have selected the Revenue, Created On, Subject and Owning
User field to be used for the report.

Now to get the Full Name of the system user for Owning User, expand that column, and select Full Name as the value to be displayed as shown below.

Click on Close and Apply to apply the changes to the query and to go back to the main editor.

Back in the main editor, we can see the fields added.

Now from the Visualizations section pick any of chart type.

Here we have picked a Stacked Bar Chart to start with. We can drag the fields and specify Area, Legend, and Value and also use the Filters section to generate the report as shown below.

The report after adding the fields à

Similarly, we can add our filter in the FILTER section to filter the records further as shown below

We can also add multiple charts in the report by selecting it from the Visualizations section.

For e.g. here we have used a Clustered Column chart to see all the leads based on Owner and Created On Date.

This completes the creation of simple reports which uses Dynamics 365 Online as Data Source.

In the next post, we’d publish this report to Power BI Service and display it within Dynamics 365 CE.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-publishing-power-bi-report-to-the-web/

Hope it helps..

Maximum File Size Exceeded while using Export to Excel in Dynamics 365 CE

We got this error while trying to export around 70K data through export to excel in Dynamics 365 CE.

Log file gave us detail of the error i.e. Excel file exceeded size lime of 4 MB.

One workaround is to export small set of data (page wise) by applying some order criteria (either in the Grid or through Advanced Find)

Or better use the wonderful Export to Excel tool in XrmToolBox

https://dreamingincrm.com/2015/05/30/new-xrmtoolbox-plugin-export-to-excel/

It took few mins to export the records in excel and the file size was close to 19 MB.

Hope it helps..

Update/Upsert operation is not supported in non-transaction mode error in Dynamics 365 for Sales Connector of Informatica Cloud

Few weeks back while working on a migration project in which we were using Informatica Cloud’s Dynamics 365 for Sales connector, we used to get the below error while trying to perform the Upsert operation in Batch.

“In CRM WRITER_1_*_1> CCI_WRITER_1000 [ERROR] Update/Upsert operation is not supported in non-transaction mode. “

For this we had raised a support ticket with Informatica. The response we got was that this feature will be available in the new IICS Platform.

So recently we migrated to IICS from ICS and were glad to see this feature available i.e. “performing Upsert operation in Batch“.

Few other enhancements in the connector includes

Get all the details here

https://kb.informatica.com/proddocs/Product%20Documentation/6/CDI_Spring2018_ConnectorReleaseNotes_en.pdf

Hope it helps..

When does the Plugin registered on SetState message fires in Dynamics 365?

Hi,

Recently we were writing a plugin that should fire when a record is activated.

In this kind of scenario, it is suggested\recommended to register plugin in both SetState and SetStateDynamicEntity message.

https://community.dynamics.com/crm/f/117/t/184070

https://blogs.msdn.microsoft.com/crm/2008/03/07/developing-plug-ins-for-crm-4-0/

Interestingly we had the following observations

  • The Plugin fires only for SetStateDynamicEntity message.
  • The Plugin fires even if the SetState message step is disabled or not registered.
  • Only SetStateDynamicEntity message is fired when the record is Activated or Deactivated. For incident entity also, it was the only message fired on Reactivating the record. The plugin didn’t trigger in case of Resolve or Cancel Case.
  • Only SetStateDynamicEntity message is fired, even if we are updating the state of the record using SetStateRequest of SDK.
  • As SetStateRequest message is deprecated, if we use UpdateRequest to update the state of the record, the plugin that is registered on SetState and SetStateDynamicEntity will not fire. It will trigger only if registered for Update message.

Plugin Trace log records :-

We are yet to figure out when exactly the SetState will be fired. Please share your thoughts and experience around the same.

Hope it helps..

My Interview at MSDynamicsWorld.com

Check out my interview at MSDynamicsWorld.com

https://msdynamicsworld.com/story/dynamics-crm/microsoft-dynamics-profile-nishant-rana-goes-man-series-cricket-mvp

2018-06-16_0941

Thanks MSDynamicsWorld.com and Linda Rosencrance.

Fixed – Invalid Export Business Process Not Activated error while exporting solution in Dynamics 365

While exporting a solution from our DEV, we got the below exception.

As the messages suggests to fix it we need to activate the process (which will create the corresponding entity for the BPF and add the entity in the solution). Or if already created simply add that entity if missing in the solution.

So we Activated the BPF.

Added the BPF specific entity.

Which then allowed us to export the solution.

Hope it helps..

PLAN PROPERLY before using Back and restore and Copy instances in Dynamics 365

Update – 25 June 2018 – Took nearly 20 days to get both the instances up.

Update – 12 June 2018Support Engineer has been not been able to resolve either of the support ticket which were raised last Thursday 7th June. One instance is not opening and the other instance is not restored with the backup we had requested. They are unable to find the backup till now.

Recently we tried copying our DEV to one of the TEST instances. The idea was we were upgrading the click dimensions solution in our DEV so we wanted to make sure everything works fine after the upgrade and in case it didn’t we could then again copy it back from the TEST.

We had faced back and restore issues (instance failed) in the past so decided to go the Copy instance way this time.

However, we got the Instance Failed error on the TEST instance to which we were copying DEV to.

Interestingly the Edit, Reset, Delete etc option were still available and we could open the TEST instance (the original, as the copy of DEV had failed) without any issues.

Then we thought of giving a try again and started copy from DEV to TEST again. This time again it failed. And with no option of Edit, Reset, delete etc. and the Instance was also not opening.

The error à

Along with that we also tried Restoring the Backup in one of the other TEST instances, that also expectedly failed.

So basically, we ended up with 2 unusable instances.

A few weeks back also we faced the same with our Dev environment and the only way out was to raise the support ticket with Microsoft. That time it took 5 days for DEV to be restored. Imagine the development activity that came to halt.

Last year also we had faced this issue in Production and had to work with the support team for 16 – 20 hours continuously to get it restored.

So, if we have some critical activity planned like Production Deployment and we are counting on Back and Restore or Copy an instance to rescue us in case anything goes wrong after production deployment, we need to plan for this Instance Failure as well while restoring or copying.

Basically, be ready to raise a support ticket with the highest severity to restore things back, which could take hours or in worst case days and do all the production deployment over the weekend or have enough downtime allocated.

Hope it helps..

Using Lookup Transformation and Cache Transformation in SSIS for Dynamics 365 (using Kingswaysoft)

Recently while working in data migration project, we had a requirement to insert a record in CRM from SQL Database and assign it to the team insert.

For e.g.

We had a Transaction Record that had a Brand lookup in it i.e. n – 1 relationship.

And the Brand record had a lookup of Team in it again n – 1. So basically, while inserting the transaction record we wanted to assign the record to the team of the corresponding brand in it.

So, for this, we had to set the owner id and owner id type field while inserting the record.

And for owner id i.e. team GUID we had to do a lookup on the Brand entity.

To achieve so, we did the following step

Added 2 Data flow task in the Control Flow, one for Caching the Brand entity’s detail and other for using the fetching data from SQL DB, Lookup Transformation task and inserting the data into CRM.

For the first data flow task,

We added the Dynamics CRM Source and fetched the Brand Name and Team Lookup’ GUID field.

name field which will be used for mapping and teamid field whose value we need from it.

Then we added a Cache Transform task with following details,

Now for our second data task flow,

First, we added the OLE DB Source to fetch data from the source SQL View, having Brand (string) column in it.

Then we added the Lookup Transformation task with following properties,

Setting Connection to Cache Connection Manager defined earlier.

Now the most important part, the columns properties. Here we defined the mapping between the Brand field of the SQL View and name field of the Brand Entity of CRM and selected the teamid field as the lookup column whose value we would like to fetch as shown below.

Then we added Derived Column task to add one more column for specifying ownerid type field to be used in CRM Destination Component while mapping. We defined the field type as string and hardcoded the value as “team”.

And as the final step mapped the above fields to the corresponding ownerid and owneridtype field in CRM Destination Component for Create operation.

For ownerid, we selected the below option as we were setting the GUID of the team.

On running the package, we can see the transaction records getting created and properly assigned to the corresponding team of the brand.

The transaction record with Team set as Owner.

Brand record with Team lookup in it.

Hope it helps..

Operation on clone solution failed error during solution import in Dynamics 365

We’d get this error if we are trying to import a solution whose upgrade solution is already there in the target system.

So, either we need to Clone the Solution to remove the upgraded solution or we need to delete the Upgrade Solution. Deleting the Upgrade solution will only delete the solution it will not delete the components as it will already be there in the main solution whose upgrade has been imported.


Hope it helps..