CODEWITHSUNDEEP
powerbiazure-analysis-servicesdirectquery
Bryan Rock
Bryan Rock

Reputation: 632

Error adding local model for Power BI dataset

I'm having some trouble using DirectQuery for Power BI datasets and Analysis Services: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-services

I would think I meet all the prerequisites. "Allow XMLA Endpoints and Analyze in Excel with on-premises datasets" is enabled for my tenant:

enter image description here

I've also enabled DirectQuery for PBI datasets and AS in my source dataset, which has been published to a PPU workspace:

enter image description here

I can create a live connection to the dataset no problem. The trouble comes in when I try to create a local model.

enter image description here

enter image description here

Can anyone help me understand what I'm missing here? I seem to only get this error for this specific dataset.

Update 1: The workspace is now on a premium capacity. Everything else is the same.

Update 2: I ended up saving a copy of the .pbix file and republishing. For some reason, that seemed to do the trick. I'm still curious why this error occurred and whether there was an easier way. The new dataset and all dependent reports now have new ID's, which means I had to replace all my hyperlinks, which was a bit of a pain.

Upvotes: 2

Views: 1568

Answers (1)

trenton-ftw
trenton-ftw

Reputation: 972

As a general rule, if you can't connect to your workspace's XMLA endpoint and browse the dataset from a tool like SSMS, then you are going to experience unexpected issues in other tooling related to the dataset's model, like the Direct Query functionality.

My experience is that the server side schema (TOM) validation is not very good, leading to often, in my opinion, improperly handled client side errors when client libraries attempt to parse the server side schema into TOM objects. At best, it results in very vague error messages, and at its worst it just causes crashing.

As far as what your model's issue is, I am not aware of a single method of isolating it using any existing tooling. I typically just deploy the dataset to a dev environment and simply begin removing items in an attempt to isolate the issue. I would imagine that you could pretty simply write an Tabular Editor Advanced Script to parse the objects in the dataset into the TOM libraries it provides and quickly identify the issue.

Just a reminder, tools like Tabular Editor are fantatistic, but since it allows you to save directly to the server side schema, you should be very cautious. Per Tabular Editor's own documentation, you should never be connecting directly to a deployed production dataset. Tabular Editor itself performs some schema validation, but it itself operates using a TOM wrapper (for the most part) and there is really only so much validation a tool like that can do without writing a ton of additional code for ensuring your schema is correct for the given compatibility level and other libraries like AMO that you might be connecting to your schema as well.

Upvotes: 0

Related Questions