Connecting Power BI for Business Central Data

In this chapter, you’ll use the skills you learned in Chapter 2 on connecting to Business Central and out-of-the-box APIs to connect data to Power BI. In addition, we will walk through connecting Power BI to an Excel file. Finally, we will review setting up parameters in the report to connect to different databases and companies, along with basic best practices in data modeling. In this chapter, we will use Power BI Desktop and publish it to a workspace and Business Central.

Lastly, we will review different visuals and give hints on which visuals to use for what story you are trying to tell. It is essential to have clean data and excellent data modeling skills, and you will want your report to tell a story and make sense to the report consumers. Knowing your audience is critical in this process.

In this chapter, we’re going to cover the following main topics:

• Enabling Power BI reports within Business Central
...

Technical requirements

If you didn’t install Power BI Desktop in Chapter 2, you will want to have it installed before going through the exercises in this chapter. You can download Power BI Desktop at https://powerbi.microsoft.com/en-us/downloads/.

This chapter’s code examples can be found on GitHub: https://github.com/PacktPublishing/Extending-Business-Central-with-the-Power-Platform.

Exercise for creating a Power BI report and dashboard

K&S Solutions requires a Power BI report to manage Customer Credit Limits effectively. This chapter will focus on creating a comprehensive Customer Credit Limit report and a corresponding dashboard. The report that you’ll generate in this chapter will be utilized in Chapter 10 to integrate a Power Apps app, enabling users to review customer activities, modify Credit Limits, and block customers when necessary:

Figure 9.1 – Power BI data sources

We have the report layouts and lots...

Enabling Power BI reports within Business Central

In this section, we will cover setting up Business Central with Power BI demo reports and having them available for the user on the Business Central home page:

1. Log in to Business Central and, on your Home page, scroll down to the Power BI Report section. Generally, this can be found on the bottom right:

Figure 9.2 – Business Central home page

2. Select Upload demo reports for this page; the following screen will inform you that the demo reports to Power BI will run in the background. If you already have reports in your workspace, which means this isn’t the first time you have loaded reports in this section, a message box will appear for you to select the reports instead. This is the first time it has been run, so it will run in the background. Select refresh or hit F5; the report will appear once it has finished downloading. Once it has been refreshed, it will show you Top 5...

Connecting to data in Power BI using out-of-the-box APIs

In Power BI, you can connect to different data sources and use Power Query to create relationships and model data. So many data source options include files, databases, power platforms, Azure, and online services. There being so many options to connect data is one of the reasons Power BI is such a powerful tool. This section will focus on two options: Online Service and File. We will use Business Central to bring data into Power BI in our examples. If you need a refresher on accessing the Business Central API, refer to Chapter 2; with so much to cover in this chapter, I will get right to it. Here are the steps for connecting to data in Power BI:

1. Open Power BI Desktop and ensure you are logged in under the user that you have access to in Business Central. Select Get data on the home page ribbon | Business Central under Online Services and select Connect. Once the navigator screen is available, you will see Business Central...

Reviewing different visuals and publishing reports

When creating Power BI reports, you’ll want to consider a few key things. First, is the data in the report accurate and up to date? In the previous section, we covered connecting to data and data modeling; in this section, we will review the report layout and different visualizations. It is just as crucial as data modeling when you are designing your report that you tell the story and know what story you are telling. If the user opens the report and must spend lots of time figuring out what the visuals are saying, or if it isn’t clear, the user will not use it. We will dive into user adoption later in this book but always keep the user in mind when building your visuals. As you probably already know, I like to use color, but I am cautious with the color I use in my dashboard reports. Still, have fun with this section; even if you are not that creative, you can use many different sites to get inspiration, and I will add...

Summary

In summary, this chapter was packed with all things Power BI, including enabling Power BI reports in Business Central, connecting to the Business Central API and Excel files, best practices for data modeling, reviewing different visuals, and publishing reports in Power BI services. I hope that if you haven’t already started working in Power BI, this chapter has given you some inspiration to create a few reports and design a dashboard. If you have already started creating Power BI reports, we hope this chapter gave you some tips and tricks.

In the next chapter, we will combine all the functionality and Power Platform tools we have covered in the book. You will learn how Business Central and Power Platform are better together.

Further reading

To learn more about the topics that were covered in this chapter, take a look at the following resources:

• Workspaces in Power BI: https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-new-workspaces
• Visualization types in Power BI: https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-types-for-reports-and-q-and-a
• Introduction to dashboards for Power BI designers: https://learn.microsoft.com/en-us/power-bi/create-reports/service-dashboards#Dashboard%20basics
• Create and manage relationships in Power BI Desktop: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
• I use this website to check colors that I am using to see how they will look if the person is color blind: https://colorkit.org/color-blindness#:~:text=How%20to%20use%20Color%20Blindness%20tool%201%201.,of%20the%20HEX%20Codes%20of%20the%20generated%20colors