Change directquery to import error?

[agd50]

Why can't i change this to import? (it's locked)

[OwenAuger]

I have seen similar errors in the past when querying Analysis Services as a data source.

As I understand it, Analysis Services Tabular has to materialize the result set in memory, which has the possibility of exceeding the memory limit of the database.

To at least diagnose the problem, could you try a query with a smaller result set, for example by applying some filters or constructing some smaller DAX Query?

It is generally preferable to find a way to query the underlying the data source rather than Analysis Services as an intermediate source, but I can understand that there might be good reasons you want to do this.

[OwenAuger]

On the face of it that's odd. Normally DirectQuery can be changed to Import but not vice versa.

I couldn't find anything in the documentation to explain this behaviour.

• What is the current DirectQuery source?
• Could you provide more detail on the data source for this table and other tables in the model?
• Are all tables currently DirectQuery?
• Is there anything notable/unusual about the current data model?

[agd50]

Thanks for the assistance. I connected to a live dataset (live connection type) and then turned it into a directquery and then I am trying to turn this into a Import.

The source: Well unfortunately I don't have much details as I didn't create the data/

I can confirm that all of the tables are directquery.

Data source type: SQL Server Analysis Services

Can't tell anything strange - it's a star scheme with a calendar and measures table

[OwenAuger · Accepted solution]

You're welcome 🙂
Ah - that explains it then.

When you use DirectQuery with Analysis Services or Power BI datasets within a Composite Model (as described here), the tables from the "remote datasets" cannot be switched to Import mode.

The idea of connecting to datasets in this way is that you are connecting to a "model" rather than individual tables, inheriting all features of the remote model, and the tables remain remote and are not imported into the local model.

If you wanted to import a table from an Analysis Services dataset, you would need to instead use the Analysis Services connector and select Import (Get Data > Analysis Services and select Import).

This should work with any Analysis Services dataset, but would only work with Power BI datasets with XMLA endpoints enabled (requiring Premium).

However it is generally considered better to connect to the underlying data source than importing from Analysis Services, but there may be specific reasons you need to do it this way.

Regards,

Owen

[agd50]

Thanks, I tried it and got this error message. 

Any idea what this means?