How to run PQ import query against dataset in NON-premium workspace

[dbeavon3]

I have a basic question about the "analysis services" connector.

We have used this connector many times in to retrieve data via PQ from a PBI dataset living in premium capacity.

You simply use the "connection string" from the server settings:
powerbi://api.powerbi.com/v1.0/myorg/MY-COOL-WORKSPACE-SalesHistoryData01

However I can't figure out how to retrieve data when using a NON-premium workspace as a data source for PQ. 

It looks like these shared capacity (non-premium) workspaces will support MDX queries, which is the basic building block for PQ imports.  It appears that I can run my MDX queries using the "Analyze in Excel" functionality.  But in Power BI I have NOT been able to do a similar thing via PQ.  (I can only use directquery or live connect when reporting against a NON-premium workspace)

What do I need to do in order to extract data from a dataset in a NON-premium workspace, and send it to another another PBI dataset (or report or dataflow).  There has to be a way to harvest this data.  It is especially important requirement the sake of PBI developers who are constantly killing our P1 capacity.  Their PQ queries via the connector are very problematic while they are still a work-in-progress.

[dbeavon3]

Hi again @Anonymous

I revisited the assumption that the source data for import queries has to be coming from P1 capacity.  

At first glance it seems like it should be possible to get AnalysisServices data from “shared capacity” as well.  The reason it seems possible is because Excel pivot tables can connect to these shared-capacity-workspaces, and get data via MDX queries. 

That is conceptually the same thing that we need to accomplish in Power BI. 

After experimenting today, I was able to use the “AnalysisServices” connector to retrieve data from a shared capacity workspace.  The steps to get this working aren’t very intuitive but here is what I did:

• First publish a normal “dataset” to a shared capacity workspace (aka. dataset “A” living in shared capacity). 
  This is the dataset that will take the heaviest CPU workload from a developer.  It will be used while the data exploration and report development work is underway.
  
  
• Create a new PBI report for premium that will connect to the shared capacity dataset using a “live” connection (aka report “B” living in premium is connecting to dataset “A”)
• Add new data table to the report “B”, thereby giving it a local model, and converting much of the source data to directquery.
• Publish report “B” to premium capacity.
• Refresh report “B” after setting your credentials.
• Get the connection string for report “B” from server settings
  
  
  
  
• Create a third report/dataset “C” which is the final deliverable from the PBI developer, and will eventually be deployed into production and scheduled overnight. 
  In here we will use the AnalysisServices connector to import any data we need from report “B”, including any of the directquery components that would come to us from the dataset “A” by way of the intermediate report “B”.
• Once the report “C” is finally finished and needs to be  deployed into production, then the AnalysisServices connector will be adjusted to point at the “real” source of data in production (rather than passing thru the A -> B pre-production artifacts).

This approach is obviously a little messy, but would allow a PBI developer to offload some of the CPU to a shared-capacity dataset, rather than swamping the P1 during the business day.

This approach is not my preference.  A better option would probably involve ask developers to use DAX statements rather than using the visual MDX designer in PQ.  I think those DAX queries would have better execution plans than MDX, and would use less "front-end CPU" (especially in the case of a simple one-to-one import from another PBI dataset.)

Has anyone else ever tried this?  Is Microsoft likely to disable this capability when they see my solution? 😉

[dbeavon3]

Hi @Anonymous 

Have you ever tried?

How come Microsoft allows Excel to run MDX queries via XMLA endpoint against the "shared" capacity?  But they won't allow Power BI to do the same thing!

It seems like there is some sort of implementation inconsistency. 

It seems likely that a professional software company would be able to get around the limitation.  It seems like software could be designed that would "proxy" the queries from the PBI desktop to a shared-capacity-dataset ...  in a way that impersonates Excel.

If customers were able to get this working, then do you think it would it violate any license agreement?  It seems to me that a pro developer should have legitimate access to their shared-capacity-workspace for the sake of MDX queries.  (Whether those queries come from Excel *or* from the PBI desktop)

FYI, the "shared" capacity is a critical tool for a PBI pro developer.  It the capacity that allows the developer to unburden the "premium" capacity.  Otherwise our PBI developers are constantly impacting production users, because they swamp the four "front-end P1 v-cores" with rogue queries thru-out the middle of the business day!

[Anonymous]

Hi @dbeavon3 ,

Sorry, to my knowledge, Power BI Premium, Premium Per User, and Power BI Embedded workspaces use an XMLA endpoint to support open-platform connectivity from Microsoft and third-party client applications and tools.

 For more details, you could read related document:  Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dbeavon3]

Hi @v-binbinyu-msft 

Have you ever tried?