How to use real database in Power BI and make it refresh dynamicaly?

Question

I'm new to Power BI (Free Version) and I have been asked to develop a report system which generates report from an excel sheet, the reports work good for only the data I have collected.

but my question is how to connect to the data immediately from SQL server without the need to convert it to excel and then import it in power BI, I also want the data to be refreshed dynamically.

One of the solutions I tried is to add new dataset but I get the following message:

Refresh can't be scheduled because the data set doesn't contain any data model connections, or is a worksheet or linked table. To schedule refresh, the data must be loaded into the data model.

I have looked for many solutions but none has worked.

am I missing a concept? thank you

Answer 1

If this data is stored in a SQL SERVER table it is a pretty straight forward process.

When you create a new power bi report (.Pbix) you should see a prompt asking you if you want to "Get Data". You would select the 'SQL Server Database' option - See the image below:

Then, you will be asked to enter the Server and Database name, and to specify either 'Import' or 'Direct Query' mode. If you choose 'Import' the data will be refreshed every time you access the report or upon 'Refresh' within a report session. If you choose the latter, the connection will always be live i.e. any changes to the data in your database will be reflected in the report.

Once you get passed this window, you will be asked to either specify credentials or use a windows authentication to access the database and server. After that you can either specify a query to pull in some data or you can select from a list of tables.

I hope this helps!!