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.