Use Power BI to analyze the CDS data in Azure Data Lake Storage Gen2


In the previous post, we saw how to export CDS data to Azure Data Lake Storage Gen2.

Here we’d see how to write Power BI reports using that data.

Open the Power BI Desktop, and click on Get data

Select Azure > Azure Data Lake Gen 2 and click on connect.

To get the container URL,

Log in to the Azure portal and navigate to the container and click on Properties and copy the URL.

Replace the blob part in the copied URL with dfs

Below is the format of the URL.

https://accountname.dfs.core.windows.net/containername/

replace the account name and the container name.

In case you get the below error

Refer –

https://nishantrana.me/2020/09/07/error-access-to-the-resource-is-forbidden-while-trying-to-connect-to-azure-data-lake-storage-gen2-using-power-bi-desktop/

Select the CDM Folder View (beta)

Expand the CDM folder and select the entity.

In case if you get the below error

Refer

https://nishantrana.me/2020/09/08/error-we-dont-support-the-option-hierarchicalnavigation-parameter-name-hierarchicalnavigation-when-trying-to-load-table-in-power-bi-desktop-using-azure-data-lake-storage-gen-2-cdm-fo/

Once connected we can then create our Power BI report as shown below.

Check the below posts for creating a Power BI report with Dynamics 365 data as the source

https://nishantrana.me/2018/11/24/power-bi-and-microsoft-dynamics-365/

Hope it helps..

Error – We don’t support the option ‘HierarchicalNavigation’. Parameter name: HierarchicalNavigation when trying to load table in Power BI Desktop using Azure Data Lake Storage Gen 2 CDM Folder view (beta)


While trying to connect to a table within Azure Data Lake Storage Gen2 through CDS Folder View

we got the below error

Users have reported this issue with the August 2020 Update of Power BI Desktop.

As suggested in the forums, downgrading to June 2020 Update fixed the issue for us.

Check out Export CDS data to Azure Data Lake Storage Gen2

Hope it helps..

Error – Access to the resource is forbidden while trying to connect to Azure Data Lake Storage Gen2 using Power BI Desktop


While trying to connect to Azure Data Lake Storage Gen2 through Power BI Desktop we got the below error

Came as surprise cause the user was had the owner role assigned to the container

It turned out we need to assign the Storage Blob Data Reader role to the user.

After assigning the role we were able to connect successfully.

Hope it helps..

SSRS and Power BI comparison for Dynamics 365 CE Reporting


Hi,

Was recently working on a comparison between using SSRS and/or Power BI for reporting for Microsoft Dynamics CRM, below are some of the points we came up

SSRS has always been Reporting Tool of choice for Microsoft Dynamics CRM and has been natively integrated with the application.

  • Microsoft Dynamics CRM offers Report Parameters for SSRS that are specific to CRM and can be used while writing custom reports using SSRS.
  • Microsoft Dynamics CRM provides CRM
    Pre-filtering feature using which user can use to create a context-sensitive report. Pre-filtering basically passes the specific record (or selected records) information on which report is to be run and renders the result after pulling the information from those record(s). These reports can be uploaded and run from inside the form or grid (list of records) or from the Reports Area within CRM.
  • Microsoft Dynamics CRM provides Filtered Views against which SSRS Report are written.  Filtered views conform to the Microsoft Dynamics CRM security model so the data shown in an SSRS report using a filtered view would only contain the data the users can view and has access in CRM
  • SSRS Reports adheres to Security Model of Dynamics CRM. They can viewable by the Organization or Individual. Individual reports can be shared with other users within the system through the Sharing feature.
  • SSRS is an ideal solution for something to be printed, for e.g. operation documents like invoices, work order, etc. as it gives fine-grained control over location as well as formatting of each of the report components/controls.
  • SSRS has support for more advanced printing features such as footers, headers, watermarks, and page numbers. We can easily configure the margins and layout of your report to get it exactly the way you want.
  • It is easy to extract data as well as export the SSRS report to a different format like word, excel, CSV, pdf, XML, etc.
  • Delivery reports in multiple formats automatically (Scheduling via Subscriptions).
  • There are some limitations for CRM Online reporting which has mostly to do with limitations of Fetch XML.

Interactivity and Data Exploration is where Power BI wins over SSRS à

  • For interactivity and data exploration we can use parameters, drill-through reports or action links, however, comparing this to Power BI, it is very limited, if we click on a visual, all of the other visuals automatically cross-highlight or cross-filter.
  • Advanced Visualisation support in Power BI.
  • Because of the limited interactivity, SSRS is not ideal for data exploration. We have limited options for slicing and dicing the data. SSRS makes more sense when you know what you want the end result to look like. If you to play around with the data, you are much better off with Power BI.
  • Mobile support is much better for Power BI.
  • Support a large number of data sources.

In short, both the tool can be used and can co-exist as they fulfil a different set of requirements.

Would appreciate if you could provide your thoughts in the comments and some additional points.

Hope it helps..

Power BI and Microsoft Dynamics 365


Listing down the posts on Power BI and Microsoft Dynamics 365 for quick reference


Creating Power BI Report using Power Query (M) Builder plugin of XrmToolBox


Power Query(M) Builder XrmToolBox Plugin developed by Ulrik Carlsson and Mohamed Rasheed makes it very easy to develop Power BI Reports targeting Dynamics 365 CE.

Just listing down the basic steps below for quick reference à

Install the Power Query (M) Builder plugin from XrmToolBox

Click on Load Entities and select the Entity against which we want to write the report. Here we will be writing a report against Case entity (for e.g. Cases by Origin), so let us select the Case entity. For selecting the fields either we can make use of view or search and add the corresponding fields in Selected Fields Section.

Here we have selected the following 3 fields

Click on Update FetchXML button.

This opens the FetchXML tab, where we can select the fields (note we have formatted value field added for Origin OptionSet field) and also select to add record URL or to use all the attributes of the entity.

Here we have selected all the fields and also checked the option “Add Record URL”.

Click on Generate FetchXML, to generate the query.

The generated main query

Now back in Power BI Desktop, open the query editor and create a new blank query with following name and value

Dyn365CEBaseURL https://[org].crm.dynamics.com i.e. URL of the CRM Organization.

We can use the Generate Service URLs button to get this URL from within the plugin.

Similarly create a new blank query as

Get the value from the ServiceRoolURL tab from within the plugin.

Create one more Blank Query and click on Advanced Editor and copy the main query generated earlier and paste it.

Click on Done.

We can see the result generated for us in the Query Editor window

Click on Close and Apply

Back in report editor select the fields or any other visualizations to create the report

To format the URL, select the Link field and go to Modelling tab and in Data Category select Web URL to enable the links.

Thus, we are done with our report. Next steps would be to publish them, add them in dashboard, Embed them within Dynamics CE, Refresh them etc..

Hope it helps..