Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Best database type for Power BI?

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 d...
Need Help
Tips and Tricks
  • artemus's avatar
    artemus
    6 years ago

    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's avatar
    edhans
    6 years ago
    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.

     

edhans's avatar
edhans
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      artemus
      Microsoft Employee
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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