Forum Discussion
Performance problem with power query code refreshing a dataflow
Damian_CT_Nom is it coming from a a database? if yes, write native DB query to fetch the required data. If it is not coming from any db that offers server side queries, create a power bi dataset by simply fetching the tables through PQ and don't use any PQ transformation but Incremental refresh on it. Once you release it on the sever, it becomes a SSAS db and you can do all sever SSAS sever side transofrmation.
PQ is not a solution for large tables and it is a not a secret anymore. The workaround is to create a ssas db if the data is located in any db whatsover.
Hi smpa01 thank you for your response.
The main table, Export_Vehicles_AVX_Linked, comes from another dataflow so it's a linked entity on this dataflow. Would that count as a DB? In any case, I don't know how to do what you asked, may you please guide me to some web resources so I can investigate how to do that?
Dataflows are not db, but datasets are ssas db. Looks like you have premium capacity as you are using dataflows. Instead of creating a dataflow, create a powerbi dataset and don't use any transformation on it. Only config incremental refresh and publish. Once you release it on the workspace, go to setting->Server Settings->Connection string. Once you grab that, create a pbix dataset/dataflow and use Analysis server connector.
The Connection string gives you something like
Data Source=powerbi://api.powerbi.com/v1.0/myorg/wsp;Initial Catalog=Contoso;
Once in AS connecttor use, Server =powerbi://api.powerbi.com/v1.0/myorg/wsp and Database=Contoso
Now, you can write fully qualified SSAS query for data transformation.
So... Instead of creating a Stagering dataflow getting the data from my sharepoint I should do that in Power BI Desktop, publish the report and then create a transformation DataFlow connecting to the dataset via Analysis server connector. Then figure out how to do the same I did in power query in SQL. Is that it?
Edit: ok smpa01 nevermind... IT has that feature disabled... DataSource.Error: AnalysisServices: XMLA endpoint feature is disabled. Turn on Analyze in Excel feature in PowerBI.com to enable this feature. sadly I can't do that.