Best / Fastest Data Storage with powerBI

[smintos]

Hi, so far we´ve used imported data (into pbix) to work with. Really fast, quite impressive.

Now we´re tackeling the 1GB pbix file size limit and also the lack of refresh option when using power bi embedded and looking for daily fresh data.

In our eyes, the only way is to go with direct query for the long run. There are several databases to choose from, which makes it quite complex. We´ve tried redshift, but did not nearly get the speed / performance our customer looking for.

Is there any way to reproduce the "import" function / in-memory speed?

So, in your eyes, what is the best performance database to go with?

We don´t use tons of data, i would say 10GB is the maximum per client we are working with.

Most important for our customers, is the speed to get the results (this should compare to import mode results).

Thanks for helping out.

[Seth_C_Bauer]

@smintos Direct Query isn't going to be your fastest route, it will be the route for the latest information immediately. The fastest as it relates to end user interaction is Import, or Live Connection to an SQL SSAS Tabular model. Based on your model size, you can use Standard edition in 2016, or there is now a Paas option, but pricing there from my understanding isn't the best at the moment.

[smintos]

Thanks @Seth_C_Bauer, but isn´t the live connection to an SQL SSAS Tabular not just another wording for "Direct Query"?

There is still an underlying SQL Database (which i think is not in-memory)?

Can you describe why this should be result in best performance, thanks!

Do you mean SQL Server Standard 2016?

Do you have a quick guide by your hand how to walkthrough, to setup sql with powerbi?

[Seth_C_Bauer · Accepted solution]

@smintos Many people just intermix the two and call them both Direct Query... I don't because there is a difference in how they work. The live connection doesn't have to transform your DAX queries into SQL and pass those back and forth to the database of origin. It connects directly to the Tabular model and works directly off of the "In Memory" model.

Direct Query - Will transform what you are doing in the model to the language of the database. SQL in this instance. Then hits the DB, and returns the information. This relys on the underlying structure of the DB to return the information quickly, there is more than likely some more complexity to this connection, but you get the idea.

Yes, 2016 SQL Server Standard has the option to use SSAS Tabular with limitations..

The setup of SQL SSAS would be a standard implementation, there is no need to adjust anything specifically for Power BI. You would just connect to the instance. Tabular models would require a Visual Studio license in order to build them though...

You create the model in VS and deploy it to the SQL SSAS Tabular instance.

[smintos]

Thanks Eno for the feedback.

By reading the last news about PowerBI Premium we will stay with Import Mode,

as we can expand the 1GB dataset limit in the near future.

As we need Premium for using PowerBI in Embedded Mode, decissions were quite easy now.