Microsoft Power BI is extremely popular. And for good reason: the tool is easy to get started with, the visualizations are simple to create, and even someone without a technical background can independently gain a lot of insight from the data. Does this mean Power BI is also suitable for use directly within a cloud application like D365 Business Central?
Power BI
Beyond its user-friendliness, there’s another reason behind the scenes why Power BI is so popular. Like a true chameleon, the tool adapts seamlessly to how the user wants to use it. For example, you can use the Power BI dashboard on its own to create a dashboard based on data you’ve imported and modeled, and then send the PBIX file to someone else. That person can open the file in Power BI Desktop and view your visualizations. Simple and effective—though it’s important to note that anyone with Power BI Desktop can modify such a document, including the data and modeling. For the creator, Power BI looks like this (when you’ve selected the Modeling pane):
This standalone approach is useful for personal dashboards, one-off experiments, quick tests, and simple analyses, but it’s not suitable for a secure, corporate environment with multiple departments, users, and governance requirements regarding the definitions of the metrics. Power BI offers a smart solution for this: the ability to keep data and modeling outside the tool, so that only the visualization remains within the dashboard definition. In IT terms, this means that Power BI can elegantly switch from a fat client to a thin client.
Semantic layer
This application is called ‘Live-connect,’ which allows you to connect to a Microsoft Analysis Services (Tabular) model. This separates the visualization in Power BI from the semantic layer. Such a semantic layer—in this case, a Tabular model—closely resembles the way you model in Power BI Desktop, and people with modeling experience in Power BI will need very little time to transfer the logic they used in Power BI to a ‘Tabular Model.’ Power BI then looks like this to the builder, where you can see that the data (namely the tables) are not available and that you can only ‘view’ the model.
If you click on "Tabular Editor," the Tabular Editor application will open, and you can easily develop and manage your model there.
The benefits of a tabular model like this are numerous; below is a list of the most important ones:
- Performance: Because the calculations take place in Analysis Service, which runs on a server and not on your own PC
- Scalable: Data import in Power BI Desktop is limited by the capacity of your own PC; with a Tabular model, however, the process runs on a server that you can scale as needed. Laptops with a lot of memory are then no longer necessary.
- Single point of truth: Instead of each dashboard requiring its own modeling, there is now a single location where definitions are stored.
- Security: The same dashboard can now be accessed by different people, but thanks to row-level security that you can define in the Tabular Model, only the data that each user is authorized to see is displayed.
- Management and Development Speed: A tabular model is a metadata definition that can be quickly updated. Instead of having to update the modeling for many separate Power BI dashboards, you now only need to make a change in one place, and that change will be reflected in all the dashboards that use it.
- Self-service convenience for a dashboard builder: Since the data import and modeling have already been handled, the developer can focus on building the dashboard instead of having to spend a lot of time on data loading and modeling as well.
There are certainly more advantages to list, but that would be going too far for now. It is important, however, to touch on one issue regarding the use of Power BI Desktop with connectors to (cloud) applications such as D365 Business Central. These are often OData or REST APIs. Here, too, the limitations of using Power BI as a standalone solution quickly become apparent.
Performance
Performance can really be an issue if, when you open Power BI, the refresh process starts and dozens of API calls have to be processed first, and perhaps 100,000 records need to be retrieved. Many of these connectors that connect directly to a source application allow you to query entities, but you still need to model how everything is related. This requires the dashboard builder to have extensive knowledge of the source application, which limits the number of people in an organization who can build dashboards based on it.
In addition, anyone who opens such a Power BI dashboard uses the security settings configured by the person who initially set up that connection in the dashboard, and in practice, this is often done using personal credentials instead of a service account. We frequently encounter complaints that a dashboard is no longer working, which is often due to the fact that the user account used for a particular connection is no longer active.
If you ensure that the data from such a connector is loaded into the Tabular Model, these issues will disappear. For example, data can be loaded into the Tabular Model once a day, rather than every time the dashboard is refreshed. Google Analytics is a good example where real-time user refreshes quickly cause problems because Google only allows a certain number of requests per day. For example, if you’re the 3e If the person updating the dashboard does so, it's possible that no more data will be returned because the API no longer allows it—since you've already exceeded the maximum number of allowed calls.
Conclusion
Our advice on how to best use Power BI depends on your specific use case. Are you the only one viewing the dashboards, and is the dataset small? In that case, using Power BI without a semantic layer (Tabular Model) isn’t a problem—and is actually convenient—because aside from installing Power BI Desktop, no additional software is required. However, you will need technical knowledge of the source(s) you’re using in your dashboard.
But… as soon as the dashboards need to be viewed by others, the volume of data increases, and security becomes a priority, it becomes difficult to ignore all the benefits that a Tabular Model offers.
We see customers who start using Power BI without a Tabular Model quickly run into problems, and eventually they have to switch to an architecture where the Tabular Model sits between the source data (applications) and the Power BI dashboards.
So, are you a fan of Power BI and want to start using it for business intelligence in your company? Then start right away with a Tabular Model as the source for your dashboards, and resist the temptation to quickly pull in some data yourself using a connector. That might seem quick and easy at first, until you’re asked to roll it out live across the entire organization and you run into the problems mentioned above.
