Is it possible to output to Power BI using DirectQuery?

[NathalieHP]

Hi everyone.

I would like to automate a process from data preparation to data visualisation using Alteryx and Power BI. To do so, I want to create a macro to write using the In-DB tools to Google BigQuery and then create the dataset in Power BI. This is possible using the Output to PowerBI tool, however my constraint is that I want to create this dataset with a DirectQuery connection instead of importing it in PowerBI, which cannot be done currently with the Output to PowerBI tool. Currently, the solution would be to write to BigQuery using Alteryx and then create by hand the DirectQuery connection in PowerBI...

Does anyone know if it is possible to automate a dataset creation in PowerBI using DirectQuery (even outside Alteryx)?
I do not mind if the solution requires coding, I just want to know if this is possible with what is available in PowerBI before trying to embark myself into an impossible journey. 😁

Thank you very much!

[RobertOdera]

Hi, @NathalieHP 

IMHO, no, and it would not be performant (and would "cost" more in Power BI service/ resources).

-Think of the Google Big Query --> Alteryx in-DB --> Power BI Tool as a proxy for using the "import" option (if you were only using Power BI).

-What you're proposing with Direct Query is to drop the Alteryx handshake, i.e., Google Big Query --> Power BI Direct Query --> Power BI Viz

-Maybe create a table in Google Big Query leveraging Alteryx, then Direct Query that table? IDK.

IDK if it is currently impossible, but it is probably counterproductive.

[mceleavey]

HI @NathalieHP ,

that would actually be a long way around a simple problem. Alteryx can connect directly into the back-end dataset for PowerBi and update this automatically on your Azure platform. 

There is a lot of setup on the Azure side of things but once set up you can have these two talking to each other quite happily.

Is there any reason you want to output to Big Query and have the data read from there? That would add extra steps and would be very slow to refresh.

M.

[NathalieHP]

Hi @RobertOdera , @mceleavey ,

Thank you for your responses.
BigQuery and DirectQuery are a must in the company I work for, so leveraging the Azure platform is unfortunately not a possibility. I was wondering if there was a way for us to automate the process of connecting our BigQuery datasets in DirectQuery in PowerBI (hopefully using Alteryx, as the dataset writing to BigQuery is already made with Alteryx) but I do understand why it is not totally relevant to try to do it in Alteryx.
I'm just wondering, if there is a way to manually do it in PowerBI, there must be a way somewhere to do it using an automated process? But this is a question I should probably ask to Microsoft directly 😁

[mceleavey]

Hi @NathalieHP ,

fair enough, although I would certainly question the efficiency of this approach.

Connecting into PowerBI via Alteryx is optimal, so use Alteryx to connect to Big Query, as well as all other sources, then push the data into PowerBI. This also avoids having to build the back end data model in DAX, which is less fun than sticking forks in your eyes.

If you want to do that then yes, ask the Microsoft community and you should get your answers there.

M.