cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashishrj
Power Participant
Power Participant

Data Storage in Power BI Datamarts

Hi folks,

 

I have recently explored the capability around Power BI datamarts and have a query. 

Assume I have an Excel file containing few thousands records (assume data for May 2022) and I loaded into the datamart within Power BI. Suppose next month that same file will have new set of data ( for June 2022) and I don't want loose the old data and keep intact along with the new set of data i.e. June 2022. So, ideally the datamart should consists of current month and the previous months data for computing historical data analysis using some trends.

How can I achieve this using Power BI datamarts in the online service ? I hope this should be achievable else the purpose of datamart would defeat. Thank you

1 ACCEPTED SOLUTION
otravers
Super User
Super User

It sounds like your June 2022 Excel file won't have data from May 2022, correct? In that case load from a folder with all your monthly Excel files (May 2022.xlsx, June 2022.xlsx etc.) to build up your data history.

 

In case what you want is to preserve the state of the data as it was in May 2022 then have the June 2022 version of those same rows, this is called snapshotting and is possible with regular dataflows using the underlying Azure Data Lake Storage Gen 2 folder, but on the other hand the dataflows generated in datamarts and saving to SQL don't generate snapshots.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
More tips and guidance in my Power BI architecture and development blog

View solution in original post

4 REPLIES 4
otravers
Super User
Super User

It sounds like your June 2022 Excel file won't have data from May 2022, correct? In that case load from a folder with all your monthly Excel files (May 2022.xlsx, June 2022.xlsx etc.) to build up your data history.

 

In case what you want is to preserve the state of the data as it was in May 2022 then have the June 2022 version of those same rows, this is called snapshotting and is possible with regular dataflows using the underlying Azure Data Lake Storage Gen 2 folder, but on the other hand the dataflows generated in datamarts and saving to SQL don't generate snapshots.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
More tips and guidance in my Power BI architecture and development blog

Hey thanks @otravers that's what I though of connecting to folders just wanted to double check if I am on the right path. Don't really want to create a snapshot at this point of time.

tackytechtom
Super User
Super User

Hi @ashishrj ,

 

Is it incremental refresh you are looking for? 🙂 Then this one might help you:

Get started with datamarts (preview) - Power BI | Microsoft Docs

 

"To set up incremental refresh for a datamart, select the table for which you want to set up incremental refresh for in the datamart editor. In the Table tools ribbon, select the Incremental refresh icon, and a right pane appears enabling you to configure incremental refresh for the selected table."

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@tackytechtom not really looking for incremental refreshes. As there will be new set of files coming every month and wanted to preserve historic data. So, the folder option I thought and as suggested by @otravers as well should suffice

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors