Query Dataflow with SQL Server (SSMS)

[cassidy]

You read that right, I don't want a Dataflow to query a SQL Server, but rather a SQL Server to query a Dataflow.

Why?  The Dataflow is using a Google Analytics API and since it performs well and parses the data, I then want to query that Dataflow and store it in a SQL Table on a daily basis.

Anyone ever done that?  Is it possible?

Thanks

[Nimai123]

Hello @cassidy 

I understood why you want to try it but I don't think so Power BI has an API which can help you achieve your desired goal.

All links that can help you with Dataflows

https://radacad.com/category/dataflow

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-serv...

https://docs.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-features-limitations

Similar thread as per your requirement which also states it is not possible

https://community.powerbi.com/t5/Service/Dataflow-amp-SSMS/td-p/815339

IF YOU LIKE MY ANSWER PLEASE ACCEPT IT AS A SOLUTION AND GIVE KUDOS

Regards,

Nimai

[cassidy]

Thanks for the reply @Nimai123 .

I've learned that you can connect to a Dataset in SSMS, so I'm trying to map that to a SQL table.  My GA Dataflows would load into a GA Dataset, and then try and pull it from there.  So far it does not like mapping something coming from Analysis Services.

Another not so fun option, I've built a Flow that converts the JSON to CSV.  Trying to save that CSV to a folder and have SSMS pick it up.

I'll post a solution if I get something reliable.

[Nimai123]

Hello @cassidy 

The second option could be a better one!

Sure do let me know!

Regards.

[cassidy · Accepted solution]

@Nimai123 

I was able to come up with a solution, though a little more excessive than I wanted.

1.  Create Dataflow to connect to Google Analytics using the API.
2.  Create Dataset from Dataflow(s).
3.  Create a Paginated report using Power BI Report Builder.  Using new Dataset, pull in the columns of Data from GA.
4.  Using the REST API functions & Power Automate, Export the Paginated report to CSV  ( this blog post was extremely helpful https://blog.crossjoin.co.uk/2020/04/27/calling-the-power-bi-export-api-from-power-automate-part-2-c...)
5.  Create procedure to pick up CSV file and insert into SQL DB.

Once it's in the SQL DB, it can be brought back into PowerBI if needed.