Get data from a Power BI dataset is creating a live connection, which is the recommended way, because you will get the entire model. However, sometimes you want to import the data from the model so that you can add other things to it (like another data source). The ability to have a composite model over Power BI datasets is coming sometime soon. However, the method explained in this blog post will help you just in the meantime.
Power BI dataset live connection
If you are getting data from a Power BI dataset, the live connection method would be used automatically.
Workspace server URL: XMLA endpoint
Another way to connect to the Power BI dataset is to use the XMLA endpoint or the workspace server URL. This is only available if you have premium or embedded capacity.
Find the XMLA endpoint address
To get the XMLA endpoint (or the Power BI dataset’s URL in the workspace) click on the workspace in the Power BI service, and click on settings.
Then you can copy the XMLA endpoint URL that you will find under the premium tab.
Get data from Analysis Services
Instead of using the get data from Power BI service (which creates a live connection automatically), use get data from analysis services
Then paste the XMLA endpoint URL that you have copied from the service as the server address. You can choose the connect to be based on Import.
Enter the credentials, choose Microsoft, and login with the same account you used for the Power BI service (your Power BI account).
Now the navigator window will show you all the datasets under the workspace, you can go under the model and find all tables, fields, and measures and select anything you want from there to import.
Import read before you import the data from a Power BI dataset
Using import for a Power BI dataset (or even analysis services dataset) is not an ideal type of connection. Because when you import, you just get one table, you have to select the fields and measures you want, and all of that comes as one table. Live connection normally gives you a far better experience as you would have all the tables, their relationships, calculations, etc, and you can even create report level measures.
I would just advise the import from the Power BI dataset, only if what you want is not possible through the live connection. and I would recommend limiting the table to not have so many columns and only selected related fields, so it won’t create a cross join scenario.
Video
Related posts:
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.