D365 F&O - Connect Power BI to D365 Finance and Operations Using OData

 Dynamics 365 Finance and Operations comes out of the box with over 600 standard reports and inquiries , These reports and inquiries may meet the majority of reporting requirements but if you want to take your data and analytics to the next level you will need Power BI. 

(you can find the how to Create D365 F&O Data Entity post).

Power BI can open up a whole new world of analytics and insights.

If you have never used Power BI or have never connected Power BI to Dynamics 365 Finance and Operations, it can be a bit tricky to get connected and start building reports. In this post I will take you through step by step how to connect Power BI to Dynamics 365 Finance and Operations, build your first report, and publish it to your Power BI workspace.

Connect Power BI to Dynamics 365 Finance and Operations

After you have downloaded the Power BI Desktop, launch the Power BI Desktop application. In the ribbon click the “Get data” button and click “More…”.

Click the “Online services” tab, click the “Dynamics 365 (online)” option and click “Connect”.

In the Web API URL paste in your Dynamics 365 Finance and Operations environment URL. Make sure that at the end of the URL you add “/data”. Then click “Ok”.

The next step is to authenticate. Click the “Organization account” tab and click the “Sign in” button. Sign in with the same username and password you use to sign into Dynamics 365 Finance and Operations. Then click the “Connect” button.

Once you have authenticated you will need to select the data entities you will need to build you Power BI reports. To do this you can search for the entities you need and select each one by clicking the checkbox. You can also preview the data in the pane to the right. Once you have selected all the data entities you need click the “Load” button. This will begin loading the data into Power BI (Data Entities).

Once the data loads you will see the tables appear on the right hand side of the form.

Build Your Power BI Report

Next, we need to define the relationships between the data. Typically, a star schema is used with one fact table related to multiple dimension tables. In this case my fact table will be the sales lines and the dimensions I need will reside on the sales order header table. On the left side of the form click the “Model” button.

To create the relationship, you can drag the related field which is the primary key to the related field in the other table. In this case the key is the sales order number which links the sale order header records to the sales order lines records.

Next click on the visual you want to create. Then drag each field you want to add to the visual to the “Axis”, “Values”, or legend sections depending on the type of visual you are using.

Publish Your Power BI Report

When you are ready to publish the report, you can click File > Publish > Publish to Power BI. Select the workspace you want to publish to and click the “Ok” button.

Now you can go back to powerbi.com and view your published Power BI report with the Dynamics 365 Finance and Operations data. If you have a Power BI Pro license you can also share your reports with others.

Takeaways

Power BI is an extremely powerful tool for data analytics and insights. This article was intended to help you connect Power BI to Dynamics 365 Finance and Operations, so you can start to use all the tools available in the Power BI feature set. If you are not familiar with the star schema approach to data modeling, I would recommend learning that approach. Also, there are many resources online that cover some of the more basic and advanced features of Power BI that can help you accelerate your learning.

D365 F&O Enable Free Text Invoice Corrections

 To correct a free text invoice that has already been posted, open the posted free text invoice

Making use of this functionality can save you some time if you heavily use free text invoices and regularly have to correct them.

To Enable this function You need to go to System Administration >> Setup >> License configuration

Enable ( Free Text Invoice Correction ) under General Ledger 

To solve This and Enable Edit in this Form You need to enable Maintenance Mode.

1. This form is read-only unless the system is in the maintenance mode. Maintenance mode can be enabled in this environment by running maintenance job from LCS, or using Deployment.Setup tool locally

You can now turn maintenance mode on and off directly through Lifecycle Services (LCS) on your sandbox and production environments. Refer to the following steps to do this:

1. Go to the environment details page and on the Maintain menu, click Enable Maintenance Mode.

1. In the slider, set Turn maintenance mode on for the environment and select Confirm.
2. A servicing operation will begin and your system will go into maintenance mode.
3. On completion, the environment state will be In Maintenance. At this point, only the system administrator will have access to the environment.
4. After you are done making system-wide changes, you can turn off maintenance mode by clicking Disable Maintenance Mode under the Maintain menu.
5. This will start a servicing operation that takes your environment out of maintenance mode. You can see the progress of the operation in the environment details page.
6. After this is complete, your environment goes back to the Deployed state. Now all users can sign in to the environment.
7. You can check the environment history page to see when the maintenance mode was turned on or turned off. To get to the environment history page, select History and Environment changes on the environment details page.

Turning maintenance mode on and off for your sandbox and production environment is very similar to a servicing operation. If turning maintenance mode on or off fails, you will see options such as Resume, Rollback, and Abort. You also have the option to download the logs to troubleshoot why the operation failed.

D365 F&O Error No report data table with name exists in report schema for data provider DP.

 When I run the Report in D365 , I get the error ' No report data table with name exists in report schema for data provider DP.'

Checked the report and it has only one dataset and the Tmp table exists. How can this error be resolved?

Solution : just restart your report server service from the services console and the error will be resolved.