Forum Discussion
Best database type for Power BI?
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.
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.
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
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.
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.
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.