This Is How Data Is Stored In Power BI

Knowing where data is stored helps you stay on top of your data’s security. Data tools make analyzing data so much more simple, but layer in the complexity of making sure it all stays secure, and bam! You’re here. I get it.

Data used within Power BI Service is secured in a back end via the Azure platform with a gateway for access. Power BI Desktop stores the information as indicated – within the file if Import or remains only in the data source if DirectQuery or Connect Live is chosen.

Power BI has some great information on their security documentation, but this post turns it into something digestible. Ya know. Technical documentation.

Power BI Data Connection Options

There are 2 primary ways to connect to data – Import or Direct Query. Live Connection is a 3rd option to be aware of, as well. You have some pros and cons of each way, but we’ll stick to how each way relates to storing data in Power BI in this post.

Where is Data Stored if Import is Chosen in Power BI?

Selecting Import when accessing data is essentially taking a copy & loading it directly into Power BI as the name suggests. It lives inside of the actual Power BI .pbix file being created. As it now is part of the file, no further authentication takes place beyond access administration when sharing results through Power BI service. Row-level security can be used for additional data security needs after the import. That said, the data still lives within the file.

Where is Data Stored if Direct Query is Chosen in Power BI?

Connecting to your data in Power BI through DirectQuery leaves the data inside of the data source that you open a connection to. Only the elements like column names and formats (schema) is stored within the .pbix file you’re creating. Anytime a user interacts with the data, it refreshes the query along with any authentication required.

For a bit more flavor on how this is stored,Power BI gives you the benefit of using SSAS (SQL Server Analysis Services) when you connect to data sources like Excel. It’s just a local instance stored on your personal computer. You can even find the files with this info in your Program Files if you want to dig a bit while the file is in use. Power BI just serves as a connection platform for your visualizations to wherever you store the data. That said, this connection disappears when you close Power BI to keep your information secure.

Where is Live Connect Data Stored in Power BI?

Connect Live option only appears if your data source is Analysis Services (SSAS) and shows up as Connect Live in that dialog box. Connect Live is not the same as the direct query option as this option stores NOTHING in Power BI. Those column names and such are all stored inside of the SSAS platform instead as this is what Power BI is built on. Very subtle difference here.

Power BI Service Data Storage Details

Power BI Service is the web component of the software and is split into 2 parts. A front end and a back end. Yup. That’s the easy version. Microsoft calls each component a cluster and refers to the front end as a Web Front End. The back end is referred to as a Back-End cluster.

Your data is protected by being housed in that back end component. Essentially, Power BI uses a gateway as a way to control access to your data. So there is a secure mechanism to protect your data.

That said, if someone logs in, they have the opportunity to share the information. Power BI authenticates users, but what your users do with the information doesn’t have an additional layer of protection.

Anytime this data is moved from the back to the front-end, it is encrypted with the https protocols for security during the movement.