Direct Query with SQL Server 2017 Standard Edition

[IoanCosmin]

The company I am currently working for purchased a new server that has SQL Server Standard Edition 14.0.1000.169 installed. My goal is to create some tabular models and connect to them in Direct Query mode using Power BI. I never had any problems in the past using SSAS in Azure but am I a bit confused now.

Here's what I had in mind:

• create a tabular model in SSDT (DirectQuery Model: On)
• deploy it to SSAS and set Default Mode to DirectQuery
• connect to the model using Power BI: Connect live.

Issues:

• Getting the following error when trying to deploy the tabular model: "Cannot deploy metadata. Reason: Failed to save modifications to the server. Error returned: 'This edition of SQL Server Analysis Services does not support DirectQuery mode."
• Tried to change the Default mode of another model to DirectQuery in SSMS: 'This edition of SQL Server Analysis Services does not support DirectQuery mode.'

Goal:

• Get live data from SQL Server in my Power BI reports

After doing some reading it seems like only the Enterprise edition supports direct query. Unfortunately, upgrading to Enterprise is not an option for now.

I see that the On-Premised Gateway supports the Standard edition. https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem and it just confuses me.

Given the current setup, what are my best options? What thing that comes to mind is to create a tabular model in Import mode, create a SQL job in SSIS to refresh the cube every few minutes and setup a direct query connection in Power BI. Can someone please clarify my confusion and offer some advice on the best approach here? Is there a workaround? I am sorry if I sound like a disoriented begginer.

Thank you,

C

[GilbertQ · Accepted solution]

Hi there,

You are indeed correct that using DirectQuery from SSAS tabular is an Enterprise only feature.

And as you rightly suggested the best would be to create the SSAS Tabular as an imported dataset, and then have it refreshing every few minutes. And have the Power BI reports using a Live Connection to the SSAS Tabular cube. And all this would go via the On-Premise Data Gateway.

The only other option is to connect your Power BI report directly to your SQL Server source. But then that would require that not only can your SQL Server handle the load, but that the dataset is optimized for Power BI queries.

[IoanCosmin]

Hello Guavaq,

Thank you so much for your insight! I appreciate your input, it's clear to me what needs to be done. Both solutions sound good to me, I'll test them out.

Have a great one,

C

P.S: If anyone else has other ideas, please chime in.