Get started with Power BI for Microsoft Business Central
Get started with Power BI
Getting started with showing Business Central data in Power BI can be daunting, but at the end of this guide you will be ready to visualize any KPI your team needs. The guide walks you through these critical steps:
Before we jump into it, let's outline an important distinction.
Report and dashboard are two words that are often intermixed within Power BI but they are two distinctly different things. A Power BI Dashboard is flexible grouping of various reports. It allows you to mix and match individual reports into a single page view.
Power BI Reports are multi-page visualizations of metrics and data. Easiest way to go is to begin designing reports, and then compile dashboards as needed.
Getting started with Power BI is a straight forward process. Once you have successfully connected to your Microsoft Dynamics 365 Business Central to Power BI, the connection details will be saved in the report designer making it easy to reuse going forward.
Purchasing a Power BI Pro license is the easiest first step in the process. It is just $9.99 per month and can be purchased through a self-service on Microsoft's Power BI page.
You are also able to get a free 60 day trial to test before purchasing.
The Power BI Report Designer is the desktop tool for connecting to your data sources, configuring your data, and designing the report components.
These capabilities are also available through the Power BI online portal, however we typically find it easier to use the desktop designer.
The Power BI report designer can be downloaded here.
Once you've installed Power BI Desktop, we recommend signing into your Microsoft account that is associated with your license.
To connect Power BI to your Microsoft Business Central account, first click on the Get Data button in the toolbar. Second, search for Business Central in the data source pop-up.
Finally, select Dynamics 365 Business Central and click connect.
Now that you are connected to your Business Central instance, you will need to select the environment you want to draw the data from. You can see in the below example that we have two environments, a sandbox and production.
The choice for which to use depends on your circumstances, but you will be able to switch them at any time.
Selecting the environment is as simple as clicking on the expand drop down from the list above.
Once the environment has been expanded, you will see a list of companies available within that environment.
Again, you will need to select the one that is appropriate for the report you are planning on designing. You can then expand that company and see the full list of tables available to import.
Select the tables you want and click the Load or Transform Data buttons.
Now your data will be loaded into your Power BI report, and ready for you to get started!
Here are a few tips that we believe will be helpful as you get started with using Power BI with Business Central:
When you first open Power BI Desktop you are greeted with a blank slate. Creating a great report helps when you are have a template to serve as a starting point.
We recommend spending some time searching for a free Power BI template to use.
When you've selected the tables to import, we recommend hitting the Transform Data button instead of load. This will open the Power BI Query Editor and allows you to add transformation steps to the query.
Some important ones transformation steps can include:
There is a standard setting that is enabled when you install Power BI Desktop that handles relationships between tables. This can be very helpful for associating primary keys and related columns between tables.
However, this feature can sometimes create relationships which you are not interested in using and will create problems if you do not know it exists.
Review your relationships tab and verify that only the relationships you're interested in are enabled.
A great habit to get into is to create date tables and relationships to it from your primary date field. By doing this you can properly display time-series data without missing dates, months, and other issues.
You can create the initial date field by using: Date = CALENDAR (DATE(2012,1,1),DATE(2025,1,1)) and then mark that table as a date table.
Other important date table columns include:
Creating the relationship from your primary date field to the date table is done in the relationships tab. An example of this would be the posting date of a document.
Once your report is ready to be used, you are able to publish it to your account workspace.
Workspaces allow you to segment dashboards, reports, and data sources into the groupings that you need.
Once published, you will be able to open it in Power BI online and share the report with anyone else who needs access.
Go from 0-100 with Power BI and Business Central Data. Click here to review.
