search

TabularEditor / TabularEditor

This is the code repository and issue tracker for Tabular Editor 2.X (free, open-source version). This repository is being maintained by Daniel Otykier.
https://tabulareditor.com
MIT License
880 stars 215 forks source link

Dual Storage Mode Model deployed to Power BI Premium #753

Open julmoser opened 3 years ago

julmoser commented 3 years ago

I currently have a Power BI Desktop Report that uses data sources in Import and in Direct Query (all data is on an Azure SQL DB) and gets published to the Power BI Service. I would like to use Tabular Editor to version the model side of the Report and deploy it to the Power BI Service using DevOps, then connecting to the Dataset from Power BI Desktop.

I've tried to connect to the Azure SQL DB from Tabular Editor, but I cannot seem to change the mode from Import to Direct Query. Is it possible at all?

otykier commented 3 years ago

I would like to use Tabular Editor to version the model side of the Report and deploy it to the Power BI Service using DevOps, then connecting to the Dataset from Power BI Desktop.

This has nothing to do with changing data sources from Import to DirectQuery. Basically, all you need to do is publish your report to the service, then enable the XMLA read/write endpoint. Once you have the endpoint enabled, you can connect Tabular Editor directly to the dataset in the service, save the model metadata to source control / DevOps, make changes and deploy them back to service using Tabular Editor.

But if you actually intended to change a partition from Import to DirectQuery mode, then be aware that this is not supported if you're using Tabular Editor against a model hosted in Power BI Desktop. In this case, Tabular Editor will hide the partition mode property by default, but this can be changed under File > Preferences > Enable unsupported Power BI features. However, if you're working with a model against the Power BI Premium XMLA (read/write) endpoint, then all modelling operations should be supported, allowing you to change the storage mode on each partition:

image

otykier commented 3 years ago

Hi @julmoser. Did my reply answer your question? If not, can you please clarify what the issue is? Thanks!

julmoser commented 3 years ago

Hi @otykier ,sorry for my late reply. Thanks for your description. I was able to change the mode from direct query to import and deploy it to the Power BI service. The model was initially created with Tabular Editor. However after doing so I can't seem to refresh the dataset anymore: image

This happens only when I open the model directly from the Power BI Service and deploy directly to it with a mixed model (import and direct query) or all tables are imported OR If I save the model to a folder and then deploy to the Power BI Service when I change the mode from direct query to import but NOT when I change them from import to direct query

Addtional info: We need an on prem data gateway because we have the data in a Azure SQL DB within a VNET.

Any idea what could be behind the error?