Query Power BI Service dataset into Dataflow

[mbbozzuto]

I have a dataset model running in the Power BI service.  I want to run a DAX query against this dataset to get a table result into a dataflow/datamart.

I have been trying to use the SQL Server connector in the datamart power query and using the connection string provided by the Power BI dataset settings, but it keeps giving an error.  (I have been trying different combinations of the string in the Server and Database fields - it says only Server is a required value but it needs a value entered into the Database field to initiate the connection).

I know this dataset is accessible from externally from my location because I can connect to it from Tabular Editor and DAX studio.

Is this possible in Datamart / Data Flow?

[lbendlin · Accepted solution]

If you really must, use the Analysis Services connector with a custom DAX query.

However this is a design red flag. A dataflow should get its data from the original data source, not from a secondary source like a semantic model.

[mbbozzuto]

@lbendlin that works - the "Azure Analysis Services" connector succesfully ran.

In my use case, I am more interested in using these data in a Data Mart where I am combining data from our primary dataset (itself built on Data Flows from our original data source) with other ad hoc data required for a specific tailored analysis.  All of our measures and dimensions are standardized in this primary Dataset so they are consistent across the org (I assume you would agree with this approach).

In this Data Mart analysis, I just need a specific set of measures filtered/sliced a certain way for this analysis.  The other data I am combining with these data from our Dataset does not qualify to be integrated into our Dataset (which is what I would need to do to be able to model against them and visualize in a report).  A Data Mart provides a potential solution for this.

[lbendlin]

Rumor has it that datamarts are on the way out (in favor of the new Fabric offerings).  Personally I never liked them as they were just glorified dataflows with an Azure SQL instance slapped on in front.  A solution looking for a problem (in my opinon).

Glad you got it working.

[mbbozzuto]

Interesting.  As I have been exploring Datamart, I actually like the potential, particularly with this capability to combine different ad hoc data with data/measures from our standard semantic model dataset.  The SQL component is a great feature but the shortcoming of it was that it couldn't be used to create new tables or views that could then be incorporated into the Datamart model - I prefer to do transformations in SQL rather than M/Power Query.  Limiting SQL to only data exploration seems like a half-way solution.

Overall, I wish Microsoft would build capabilities for the long-run and stick with them rather than constantly introducing/removing new features - it is great to add new features and tools, but it doesn't help if the end result is that it becomes impossible to build our internal processes and reports off of them because we don't know which features/tools are permanent and which will be removed next year.