Power BI – Nishant Rana's Weblog

Create Power BI Report to analyze Dataverse / Dynamics 365 data in Azure Data Lake

To begin with, here we have already configured the Azure Synapse Link /Export to Data Lake service and have configured Account, Contact and User table for the sync.

Check posts on Azure Synapse Link/ Export to Data Lake service:

https://nishantrana.me/2020/12/10/posts-on-azure-data-lake/

Let us now create the Power BI report on the exported data –>

Click in Get Data and select Azure >> Azure Data Lake Gen 2 connector and click on connect.

Navigate to the storage account and the container within it and click on Container properties.

Copy the URL

Replace the blob part https://saazuredatalakecrm.blob.core.windows.net/dataverse-pmaurya105-unqdc8ed1c1df824188bbe2225de96f0

with the dfs

https://saazuredatalakecrm.dfs.core.windows.net/dataverse-pmaurya105-unqdc8ed1c1df824188bbe2225de96f0

Enter the URL and select CDM Folder View

In case if you get the “Access to the resource is forbidden” error.

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/

Here we have selected the Contact, Account, and System User table.

Click on Load.

Here we’d create a basic report that shows Contact’s full name, email, along with Account Name (left join with Account) and Owning User Full Name (left join with System user)

Let us choose the appropriate columns from the contact table.

fullname, emailaddress1, owninguser, ownerid_entitytype, parentcustomerid, parentcutsomerid_entitytype, parentcustomeridname

Now to get the full name of the owning user and the Account’s name, let us left join the contact table with the system user and account table.

Select Merge Queries as New in the Power Query Editor.

Merge Contact with Account

Merge with System User

Expand the account column and select the name field for the merged query.

Similarly, select fullname for the systemuser.

Close and apply the updates.

Below we have our report ready.

Save and publish the report.

More posts on Power BI – https://nishantrana.me/2018/11/24/power-bi-and-microsoft-dynamics-365/

Hope it helps..

Advertisements

Fixed – Error – “Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server” – while trying to use Dataverse connector in Power BI

While trying to use Dataverse connector inside Power BI Desktop

we got the below error

Details: “Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)”

Later we realized we were not entering the environment details in the correct format as expected

i.e.

[orgname].[crm].dynamics.com

We were entering it incorrectly below with the https

Entering URL in correct format fixed the issue for us.

In case if TDS endpoint it not enabled –

we will get the below error

i.e. Microsoft SQL: TDS Protocol endpoint is disabled for this organization.

Check more posts on Power BI

https://nishantrana.me/category/power-bi/

Hope it helps..

Advertisements

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,

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/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/

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/

Posts on Azure Data Lake

Hope it helps..

Advertisements

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

Posts on Azure Data Lake

Hope it helps..

Advertisements

Fixe – 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.

Posts on Azure Data Lake

Hope it helps..

Advertisements

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.