Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a report published in our premium instance with imported data from a couple sql server dbs. I want to add a PowerBI dataset from the service to that report however I get the following error.
The other thing I noticed is that if I add a PowerBI dataset to a new blank report, I cannot add data from any other sources as those options are deactivated under the "Get Data" menu. I'm reading the documentation titled - "Using DirectQuery for Power BI datasets and Azure Analysis Services (preview)" at https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s... however I'm having a hard time understanding the limitations and considerations and how they pertain to my issue. Do I add a powerbi dataset to a blank report first, change it to DirectQuery, and then add other data sources like sql, excel, etc. to the report? Thank you very much.
Hi @pborah
If you are getting data from a Power BI dataset, the live connection method would be used automatically.
As the picture above says, so you cannot add data from any other sources as those options are deactivated under the "Get Data" menu, if you add a PowerBI dataset to a new blank report.
In this scenario, if you want to add another data source, you can read this article about how to create a composite model over Power BI datasets. https://radacad.com/how-to-import-data-from-a-power-bi-dataset-premium-only
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang did you read the article you posted yourself? It says nothing about creating a composite model. Besides I tried creating one and I'm getting an error. If you read my responses in this thread, you will see that. Generic responses do not help anyone other than cause frustration. It's not like I did not try reading articles before I post in this forum?
This is what happens when I try to connect using xmla endpoint -
Hello pborah
The DirectQuery over PBI dataset and AS feature is still in preview so be cautious but it has been improved since the introduction last year. That said, you should be able to connect to additional data sources after you convert the model to a hybrid/composite model by clicking the "Make changes to this model" link in the status bar. Because connections are made using the XMLA endpoint, any connection to a published tabular model (whether a Power BI dataset or an AAS model) will be treated as an Analysis Services connection. You should be able to use "Get Data" to add more connections, using either DirectQuery or Import mode.
I just walked through the steps in my post that Greg referenced and after adding the two published dataset DQ connections, I imported a table from an Excel file and it all worked as expected. One nice improvement is you can now choose to add selected tables and columns, and duplicate objects are automatically resolved by the designer. I'll try to do a follow-up post in the near future.
Thank you @paul_turley for taking the time to respond. Once I click "Make changes to this model" ->"Add a local model", I get the following error -
I am signed in using my organizational account, and I have acess to the workspace where this data is located, the dataset was configured by the tenant admin. I'm a workspace and data gateway admin. So I do not understand this error.
Hmmmm. I'm not surebut I'd start by eliminating possibilities. Make sure the workspaces are premium. Also double check permissions. Maybe create a simple dataset and then connect to it for testing purposes.
I'm curious to know what you find.
@pborah Here is a good article on it from @paul_turley :A First Look at Gen 2 Composite Models with Live Power BI Datasets | Paul Turley's SQL Server BI Blo...
Thanks @Greg_Deckler , this article is informative but doesn't resolve my issues. As per this article, I should be able to do what I'm trying to do but that isn't the case. Going back to the error screenshot I posted, can you please explain what has it got anything to do with Azure Analysis Services?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |