Get data from SQL Server

[Anonymous]

Hi Everyone,

I'm so confusing... after looked many articles.

I need some help, here's my questions :

1. May I use the Power BI service to get data from the SQL server? or do I need to import the SQL Server data to Power BI Desktop and publish the data to Power BI? 

2. If I have to import the SQL Server data to Power BI Desktop, the database size limit is depend on whether I have subscribed to Power BI Premium right?

Thank you

Yuisyuan

[Anonymous]

Hi,

Thank you guys reply!

so, when I install a gateway, I can through Power BI to get data from SQL server?

or do I have to link the SQL server on the Power BI desktop and then publish it to Power BI?

Thank you

yuisyuan

[Burningsuit]

Hi @Anonymous 

Basically, when you connect to a SQL datbase in Power BI you have two options.

Import mode. In this mode the data from the SQL database is imported into the Datamodel, and can thn be accessed by Reports and visualisations. The maximum size of the Datamodel varies with Licence, initially with a Pro licence the maximum size is 1GB. However the data imported into the Datamodel is compressed, based on the number of repetitions in each column. So in many cases this 1GB can represent many thousands, indeed millions of records. If you do need a larger Datamodel you can use Premium Per User licences, with a max size of 100GB or Premium Capacity with a max size of 400GB.

When the data changes, you need to configure Refresh to re-import data into the datamodel, this can be done manually, or automatically at scheduled time during the day.

Direct Query Mode. In this mode no data is imported into the Datamodel. Rather, Power BI sends SQL queries to read data from the Database each time it is needed. This gets real-time data into Power BI (no need to configure Refresh)  and the amaount of data is not limited as it is in the Datamodel. However Power BI generates at least one SQL query for each visualisation on the report, and these are run each time the report is opened, or a visualisation is clicked on or a page selected etc. This can creat a great deal of work for the SQL database, and unless the Server is configured to cope with this extra load it may result in slow Reports and slow the SQL server, which may impact other applications that use the database.

You can find more information here : Dataset modes in the Power BI service - Power BI | Microsoft Docs

Hope this helps

Stuart

[itchyeyeballs]

If its an on-premise SQL server you will need to install a Gateway to enable the service to connect to it

https://powerbi.microsoft.com/en-us/gateway/