Connecting Power BI to database management system (i.e SQL Server, Snowflake, MS Access)

[Anonymous]

Hello everyone!

I'm a newbie when it comes to Power BI and I'm hoping if you could help me with this question.

My current way to import data is: pull data from database (i.e. MS Access) -> save as excel file in OneDrive -> import excel file in power BI -> Manipulate data (i.e. change formatting) using Query Edit

However, I found this way soooooo time consuming so I thought about connecting the database directly with Power BI.

I know that when I use my current way in Power BI, the original excel file saved in my OneDrive will not be changed after I did Query Edit (and I DON'Tt want to change the original file). I did some research and learnt that if I connect database (MS Access, Snowflake, SQL Server) directly to Power BI, the tables created in the database will appear in 'Fields' in Power BI.

My questions are (taking MS Access as an example):

1. After connecting Power BI with MS Access, if I use Query Edit to modify the data, will I be modifying the data in the MS Access? (I DON'T want the original data in the database to be modified so I wanted to double check to ensure it will not be modified)

2. Since there are a number of tables I created in MS Access, can I only select the ones I need and add them to the 'Fields' section in Power BI?

3. This question related to Snowflake. If I wanna connect Snowflake with Power BI, when creating dashboards, will the dashboards be created in Power BI or Snowflake interface? I noticed there's something called Power BI Built-In for snowflake so I'm confused.

Thank you for your help!

[Burningsuit · Accepted solution]

Hi @Anonymous 

Yes, you're right that connecting your database directly to Power BI desktop is much more efficient than downloading excel files and loading them into Power BI.

Taking your questions in order:

1) The original data in the database is untouched. In Power Query what you are doing is building a filter, that transforms the data, changing it, filtering it etc, as it is imported into the Data Model, you then have a transformed copy of the data in the Data Model that Power BI reports on.

2)Yes in Power Query you get a navigator that shows you the Tables (and Views etc. ) in the Database, and you can select which ones to import.

3)Snowflake data looks like any other database, the same as Access, SQL databases etc. So once you've imported your Snowflake data into a Power BI data model, you use Power BI to create Reports and Dashboards based on that.

see : Maximizing Power BI with Snowflake - Snowflake BlogMaximizing Power BI with Snowflake - Snowflake Blog

Hope this helps

Stuart

[Anonymous]

Hello,

I am following up on your question -- as I am in the same boat as you were years ago. 

I have been worried about connected Power BI to my Access file but I understand that transforming the data in Power BI does not affect the original file. 

Did you ever figure out how to create a create a refresh? 

[Burningsuit · Accepted solution]

Hi @Anonymous 

Yes, you're right that connecting your database directly to Power BI desktop is much more efficient than downloading excel files and loading them into Power BI.

Taking your questions in order:

1) The original data in the database is untouched. In Power Query what you are doing is building a filter, that transforms the data, changing it, filtering it etc, as it is imported into the Data Model, you then have a transformed copy of the data in the Data Model that Power BI reports on.

2)Yes in Power Query you get a navigator that shows you the Tables (and Views etc. ) in the Database, and you can select which ones to import.

3)Snowflake data looks like any other database, the same as Access, SQL databases etc. So once you've imported your Snowflake data into a Power BI data model, you use Power BI to create Reports and Dashboards based on that.

see : Maximizing Power BI with Snowflake - Snowflake BlogMaximizing Power BI with Snowflake - Snowflake Blog

Hope this helps

Stuart

[Anonymous]

Hi Stuart,

Thank you very much for taking your precious time to answer my question! 

May I ask some follow up questions please?

1. I learnt that Power BI has its own cloud and if I understand correctly, the transformed copy of the data in the data model is saved in Power BI's cloud? Please correct me if I'm wrong. I'd like to know where the data is stored by Power BI.

2. Regarding data privacy. Since the data is sensitive and confidential, is it safe to be stored in the place where Power BI store it? Will anyone outside my company be able to access the data, reports, dashboards without my permission? (I DON'T want anyone outside of my company access those info).

Thank you!

[Burningsuit · Accepted solution]

Hi @Anonymous 

1) Yes, the data is stored in your tenant on the Microsoft Cloud. It is possible to run an on-premise version of Power BI, but the licencing and infrastructure costs mean this is not a common scenario.

2) the data is stored in the Microsoft cloud that is  ISO 27001, ISO 27018, EU Model Clauses, HIPAA BAA, and UK G-Cloud. compliant.  Some very large companies and governments trust the Microsoft cloud, you can read much more detail here :Security | Microsoft Power BI

Importantly, you can control where your data is hosted, who has access to it and what level of access.

No-one will be able to access any of your data, dashboards or reports without your permission.

Security is a big subject, read the documents and  reach out to your Microsoft Dealer or Microsoft contact and I'm sure that'll help

Stuart

[Anonymous]

Thank you Stuart for your quick reply and kind help! Really appreciate it!