Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simond
New Member

Get data from SQL database

I will be hiring someone to create several reports using BI in the near future.  I know this must be a basic questions but I couldn't find the answer quickly and easily...

 

When someone uses BI to create reports and connects with a SQL database are they able to change /edit any data in the tables on the server?  Is there a risk that they could change any information contained in the program that uses the SQL database?  Or, are they only able to "get" the data and create a report but not change the existing tables residing on our server?

 

Hope I asked that correctly,

 

Thanks,

 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Power BI cannot change any data in any source system (SQL , CSV, Excel etc.) so you only need to provide read access.

 

It could potentially place a lock on tables while it is reading data, which may interfere with other read/write operations going on with the table being read from.  So in the unlikely event this becomes a problem, you may want to consider some staging/output tables that are used only by Power BI.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

Power BI cannot change any data in any source system (SQL , CSV, Excel etc.) so you only need to provide read access.

 

It could potentially place a lock on tables while it is reading data, which may interfere with other read/write operations going on with the table being read from.  So in the unlikely event this becomes a problem, you may want to consider some staging/output tables that are used only by Power BI.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi Phil, you said thatPower BI cannot change any data in any source system but that it could place a lock on tables while it is reading data. Do you know where I could find any more documentation on this? Is it COULD or IT WILL - I just in a search for a confirmation of the lock on tables which Power BI is reading from.

 

Any answer will be highly appreciated!

 

Thanks,

Kristina

Actually, you can use a SQL-script to write data back to SQL-server. But because this has a certain live of it's own, it's basically not used in production. But if it gets into the wrong hands, people can actually mess up your data:

https://blog.crossjoin.co.uk/2013/12/09/updating-data-in-sql-server-with-power-query/

So you need to limit the user access rights at server-level in order to prevent data modification.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

itchyeyeballs
Impactful Individual
Impactful Individual

PBI itself cant change data in your database but you will need to be careful how you provide access.

 

The simplest thing would be to create a read only user in the database and make sure that user only has access to the relevant tables.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.