Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |