Suggestions for how to store data?

[glen_sunw]

I'm setting up reports in Power BI.  One of the data sources cannot be accessed with any exisiting connector.  We plan to build a tool that will process the data we need.  The question is now... where should we store the data?  Datamarts sounded like the right idea a couple of years ago.  Is that still what you would recommend?

[v-kaiyue-msft]

Hi @glen_sunw ,

Thank you @AnalyticsWizard  for your replies and allow me to provide another insight:

Based on your scenario, Data Mart in Power BI is indeed a very suitable solution for your needs. Data Mart is a self-service analytics solution that enables users to store and browse data loaded into a fully managed database. Data Mart provides a simple and optionally code-free experience for bringing in data from different data sources, extracting, transforming and loading (ETL) the data using Power Query, and then loading it into a fully hosted and unoptimised or optimised Azure SQL database.

Data Mart is a fully managed database for storing and browsing data in both relational and fully managed Azure SQL DBs. Data Mart provides SQL support, a code-free visual query designer, row-level security (RLS), and automatic semantic model generation for each data mart. You can perform ad hoc analyses and create reports on the Web.

More details can be found in the documentation: Introduction to datamarts - Power BI | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticsWizard · Accepted solution]

@glen_sunw 

Creating a data storage solution for Power BI reporting depends on several factors, including the volume of data, the complexity of your data operations, and your specific business requirements. Here are some contemporary options for storing your data:

1. Azure Data Lake: An excellent choice for storing large amounts of structured and unstructured data. It's highly scalable and works well with Azure Data Factory for ETL processes and Power BI for analytics.

2. Azure SQL Database or Azure Synapse Analytics: These are cloud-based databases that can serve as a data mart or data warehouse. They offer robust querying capabilities, are highly scalable, and integrate well with Power BI.

3. SQL Server: If you prefer or require an on-premises solution, SQL Server can serve as a traditional data mart and can be a good choice if you already have SQL Server infrastructure in place.

4. Dataflows in Power BI: Dataflows allow you to prepare and store data within the Power BI environment. They are designed to transform, clean, and enrich data that can then be used by multiple reports and dashboards.

5. Cosmos DB: For globally distributed applications and analytics, Cosmos DB can be a good choice. It's designed to handle massive amounts of data and provides fast, global access with multiple data models and APIs.

6. SharePoint Lists or OneDrive: For smaller datasets or less frequent reporting needs, storing Excel files or CSVs in SharePoint or OneDrive might suffice. This is often used for simpler or manual data entry processes.

7. Common Data Service (CDS) / Dataverse: If you're using Microsoft's Power Platform, the Dataverse stores and manages data used by business applications. It's secure and integrates well with Power BI and other Microsoft services.

Data marts are still a valid concept, especially for departmental reporting or specific subject areas. The technology underlying data marts has evolved, though, and cloud services like those provided by Azure offer more scalable and flexible architectures compared to traditional on-premises solutions.

The choice among these options will be dictated by the specific needs of your business case, such as the need for real-time reporting, data privacy requirements, and the existing technology stack of your organization. It's also wise to consider future scalability and potential integration with other systems.

Whatever storage you choose, ensure it has robust support for connectivity with Power BI. Since you're planning to build a tool to process the data, it's also important that the storage solution has a well-supported API or SDK that your tool can interface with effectively.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍