Dou need to create Power BI reports for Business Central Saas that require data from one or multiple companies from your tenant, then you can use this template:

blog/MultiEnvironment at master · srenders/blog (github.com)

 

When you run the pbit file (template), you are asked to login with your office 365 BC credentials:

Next a popup asks which environment to connect to:

Next you can open the Transform Data (Power Query Editor):

and it will show a list of all api’s and web services you can connect to:

As you can see this query is not loaded into the data model. The purpose is that you reference it, to add a new query (api, web service) to the data model:

Here I referenced the _apis query and filtered it on the accounts query.

Then you expand the Data.1 column, to get the fields/columns:

Then you can select which columns you need, check the data types and continue to load other data.

 

As you can see, each query has an Environment and Company column:

When ready, load the tables to the data model and create your star/snowflake model.

 

Remember, you will need to add key columns to every table you load, containing Environment+Company+Pk, to be able to create relations between the tables, so you don’t link accounts from different companies/environments.

You could even make the Environment a parameter, to convert it into a multi-environment and multi-company template.

How cool is that? 🙂

 

You can download it on my github:


Discover more from think about IT

Subscribe to get the latest posts sent to your email.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation