Different Methods of Storage for Data Files that Feed Power BI Analysis and Reporting

[Anonymous]

Dear Community, 

I have a question about the centralization and storage of different data files that can be retrieved and imported to Power BI for analysis and reporting. 

As a data person of a small consulting firm, I gather and analyse data from different projects, both short and long-term. The data files are of different formats and sizes, but mostly text files, csv files, excel files. They are seperate data files instead of relational database files. Some data are imported to Power BI directly and others will needs further processing with other softwares like SAS or R before being imported to Power BI for further analysis.

We have been using Microsoft 365 Sharepoint Online folder to store the different data tables and Power BI files. As we have more and more data, some with PII, I wonder whether it is best practice to store all these data in Sharepoint both for data security and analytical efficiency. 

We are thinking about using a Asure SQL server as a central storage for all our data. But since our data files are not related data tables, but are standalone data files from different clients/projects, and we are not building relational databases out of these files, so I wonder whether using SQL server is the best efficient way. To convert and import these data into SQL will take much time. There is also the extra time writing SQL codes extracting the data from SQL to Power BI. 

I would appreciate your advice on what solutions should be the best in our situation. Should we use a SQL server, or non-SQL server, or any other data storage that can both safeguard data integrity as well as efficiency for data analysis and reporting.         

Thanks.

[FarhanJeelani · Accepted solution]

Given your situation, here’s a tailored recommendation:

1. Data Storage and Security:
Since you’re handling data with sensitive information, like Personally Identifiable Information (PII), security and compliance are critical. Azure SQL Server can provide excellent data security, including encryption, access controls, and compliance with privacy regulations. However, moving non-relational, file-based data into SQL can add significant overhead for transformation and may not be the most efficient choice given that your data files are typically standalone and project-based, not relational.

An alternative you might consider is Azure Data Lake Storage. Data Lake Storage is a highly scalable solution optimized for big data analytics and supports a range of file types (like CSV, text, Excel), which aligns well with the formats you're working with. Additionally, Data Lake integrates well with Microsoft tools, including Power BI and Azure Synapse Analytics, allowing you to load and process data more seamlessly.

2. Data Accessibility and Integration:
With Azure Data Lake, you can connect Power BI directly to your data files without needing to convert everything into a relational database. If some files require further processing in tools like SAS or R, Data Lake allows you to organize your files by project or client and provides straightforward access. Additionally, Data Lake integrates with Azure Data Factory, which can help you automate and orchestrate data processing workflows if your data requires transformations or needs to be moved into a more structured format periodically.

3. Centralization and Efficiency:
For teams working on diverse projects, Azure Data Lake or SharePoint Online (where you’re currently storing data) both offer flexible, scalable storage without the need to transform files into a relational format. SharePoint is an excellent option for small-scale file storage, but it can face performance limitations with large volumes of data or larger file sizes. If the data volume and file size grow, Data Lake’s performance will likely meet your needs better and make it easier to scale over time.

4. Additional Considerations:
- Cost: Data Lake tends to be more cost-effective for large volumes of unstructured data, as you only pay for the storage used and any data transactions.
- Data Lake Analytics: If your data analysis needs grow, Azure Data Lake integrates with Azure Synapse Analytics, which can run powerful queries and perform complex analysis on your data without moving it.
- Governance: Azure Data Lake offers robust governance and security features, allowing you to control access at a fine-grained level, essential when dealing with PII.

Recommendation Summary:
Given your data is standalone, non-relational, and needs security, scalability, and easy access for Power BI, Azure Data Lake Storage is likely a more efficient and flexible choice than Azure SQL Server. Data Lake aligns well with your data's format, provides seamless Power BI integration, and offers robust security and scalability, ideal for a growing consulting firm with varied, project-based data requirements.

Please mark this as solution if it helps you. Appreciate Kudos.

[Anonymous]

Thanks @FarhanJeelani  for your detailed reply. I will read some documentation about Data Lake and see how it works.