Best database type for Power BI?

[Anonymous]

Hi,

We have been fetching data from BigQuery into Power BI. Even though this works most of the time, we are seeing some issues.

Out of curiosity I was wondering if anyone want to share what database they think works best? Because we have a datawarehouse, we could go with having a semantic layer that is pushed to a MS SQL database and this has been a thought, but are there any other databases types anyone can recommend that works very well? I would imagine MS SQL + Power BI has to be a great combo seeing as both are MS products 🙂

Best,

Ali A

[artemus · Accepted solution]

The other two require additional software to be installed for Power BI desktop to connect

• Not is doesn't. For Analysis services, it is native, more so than MS SQL, seeing as if you use any other data source, the data will get imported into Power Bi's internal instance of Analysis services
• For Azure Data Explorer, you don't need any additional instalitions

Azure Data explorer short info:

• Supports large data sets and fast ingestion
• Does not support update to existing data in database
• Delete capabilities are limited. Model is that data inserted has an age and is elgadible to be deleted in bulk when the retention of it is up (can be set by the administrator)
• Supports append

Azure Data explorer in Power BI:

• Supports query folding (about on par compared to SQL).
• Can use tables, external tables, and functions that have 0 arguments (views)
• Does not support more complex functions (I'm working on adding this)
• Is implemented in Power Query

Azure SQL in Power BI:

• Supports query folding
• Can use tables, functions, stored procs
• Is implemented in c#

Analysis Services:

• Expected use is direct query, you won't get much benifit if you use import
• Supports using measures defined in the server.

[edhans]

SQL Server and Azure SQL are phenominal for Power BI, especially when you get into the advantages of Query Folding that Power Query provides. But other relational databases provide this too, like Oracle, Amazon Redshift, etc.

I'd start by looking at databases that best fit your overall need. If it is only for Power BI (or the Power Platform in general), then it makes it easier, but if you are trying to satisfy demands of different platforms, you are invariably going to have to make some sacrifices on some platforms to get benefits on others.

[Anonymous]

Thanks for the reply @edhans 

What we have right now is a dwh that is in BigQuery and that works wonder for what we are trying to do there. It's being used for more generic ETL right now, but will eventually be used for also BigQuery ML so we are happy with that.

What is lagging right now is that the connector moving data from BigQuery to Power BI service doesn't seem very stable so a thought is to have the following layers in BQ:

Raw

Stage

Standard

Business

Semantic

and create one more called "Visualisation" that is in a MS SQL server which is what Power BI will read from. We are using Matillion as DWH tool and what this layer would provide is models that combine fact tables from semantic where there have been done where clauses and inner joins on dimensions to remove unused dimensional values based on concatenated fact tables...

I am hoping that can give us much more lean models with just the data we need + a much tighter integration between the visualisation tool and database it reads from without removing the horsepower BigQuery provides for ML...

Sounds like you support the notion of SQL Server + Power BI = Fireworks 🙂

Does what I describe above raise any conserns you can think of?

Thanks again.

// 

Ali A

[artemus]

The main thing to note is that PowerBi runs its own version of SQL Analysis Services internally (unless you do a direct query to  SQL Analysis Services, in which case it uses that). So, generally if you want high performance you can use that. If you really want the best performance, you will need to set up premium capacity in Azure, and load your data using incremental refresh (using dataflows) or another kind of push model to get past the 10GB limit on data size.

You could also try Azure Data Explorer. It has a very mature Power Bi connector, which allows direct query (https://azure.microsoft.com/en-us/services/data-explorer/)

[Anonymous]

Hi @artemus,

Ok...I will look into Azure Data Explorer. Right now we are looking to stay within the Google Cloud Plattform "sphere" and I know that you can create a relational database there that is either MySQL, MSSQL or PostgreSQL and I was thinking that MSSQL might be the best option here.

Any thoughts between those 3 types which has a better integration in terms of connector and stability?

Best,

Ali A

[edhans]

MS SQL will be best among those three. The other two require additional software to be installed for Power BI desktop to connect. Just one more link in the chain that could cause an issue at some point. MS SQL connectivity is native.

[artemus · Accepted solution]

The other two require additional software to be installed for Power BI desktop to connect

• Not is doesn't. For Analysis services, it is native, more so than MS SQL, seeing as if you use any other data source, the data will get imported into Power Bi's internal instance of Analysis services
• For Azure Data Explorer, you don't need any additional instalitions

Azure Data explorer short info:

• Supports large data sets and fast ingestion
• Does not support update to existing data in database
• Delete capabilities are limited. Model is that data inserted has an age and is elgadible to be deleted in bulk when the retention of it is up (can be set by the administrator)
• Supports append

Azure Data explorer in Power BI:

• Supports query folding (about on par compared to SQL).
• Can use tables, external tables, and functions that have 0 arguments (views)
• Does not support more complex functions (I'm working on adding this)
• Is implemented in Power Query

Azure SQL in Power BI:

• Supports query folding
• Can use tables, functions, stored procs
• Is implemented in c#

Analysis Services:

• Expected use is direct query, you won't get much benifit if you use import
• Supports using measures defined in the server.

[edhans · Accepted solution]

---

@artemus wrote:

The other two require additional software to be installed for Power BI desktop to connect

• Not is doesn't. For Analysis services, it is native, more so than MS SQL, seeing as if you use any other data source, the data will get imported into Power Bi's internal instance of Analysis services
• For Azure Data Explorer, you don't need any additional instalitions

@artemus- he was asking about the best among 3 possibilities he has narrowed it down to: MS SQL, PostgreSQL, and MySQL. The latter two require additional software for the desktop to work.

I know SSAS is supported and a great source for Power BI, but I'd personally rather have SQL than SSAS because SQL can be further modified. With SSAS, you are limited to the structure that the DBA set up in SSAS for you. You cannot add additional data or transform it in another way to suit your needs. It is one of the least flexible methods of creating reports. But, it has its uses, and is supported natively.