cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Data Snapshots

I am trying to figure out how to go about taking a snapshot of some data. We keep a daily count of our active companies, but things are constantly changing in our database so the numbers always change. For instance, today I can get the exact active client count for 2/8/2018 which is 4,309. If I open up my PBI report in a few weeks, or even days, and want to look back to see what the active client count was on 2/8/2018, it would no longer read 4,309, but some other random number that could either be higher or lower since the data is constantly changing in our SQL database. Is there a way that each day I can take a snapshot of the date and client count on that day and have it stored in some additional table to reference? We are attempting to record customer retention and I just want the numbers as accurate as possible. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-fengyz-msft Frequent Visitor
Frequent Visitor

Re: Data Snapshots

@mrainey

 

Power BI does not have incremental load at present. This means that if you try to timestamp something at the time of query load, you will get updated values for the timestamp when it is refreshed.

 

Three options for your reference:

 

1. Storing and using information from a dynamic data source using PBI desktop

 

2. Use R script: auto export of data

 

3. Create a Trigger on source table to insert rows to your DataChangeLog table as soon as the data updated from source table in your database. Colde looks like below:

 

create trigger triggername On sourcetable

for update

as

insert into destinationtable (column) select column from TriggerTest1

Go

 

You can vote the idea: Allow for point-in-time snapshots for reports/dataset

 

Regards,

Pirlo Zhang 

4 REPLIES 4
Highlighted
v-fengyz-msft Frequent Visitor
Frequent Visitor

Re: Data Snapshots

@mrainey

 

Power BI does not have incremental load at present. This means that if you try to timestamp something at the time of query load, you will get updated values for the timestamp when it is refreshed.

 

Three options for your reference:

 

1. Storing and using information from a dynamic data source using PBI desktop

 

2. Use R script: auto export of data

 

3. Create a Trigger on source table to insert rows to your DataChangeLog table as soon as the data updated from source table in your database. Colde looks like below:

 

create trigger triggername On sourcetable

for update

as

insert into destinationtable (column) select column from TriggerTest1

Go

 

You can vote the idea: Allow for point-in-time snapshots for reports/dataset

 

Regards,

Pirlo Zhang 

Omar1 Frequent Visitor
Frequent Visitor

Re: Data Snapshots

I'd like to follow your example for option 3.

 

Can you please show step by step directions for how to add like you would do it using example tables?

Recsant Frequent Visitor
Frequent Visitor

Re: Data Snapshots

@v-fengyz-msft
I would also love to know more about the create trigger. I'm assuming it's M written into the advanced editor. Is there any documentation you could direct us too.

Recsant Frequent Visitor
Frequent Visitor

Re: Data Snapshots

@v-fengyz-msft
I would also love to know more about the create trigger. I'm assuming it's M written into the advanced editor. Is there any documentation you could direct us too.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 350 members 3,506 guests
Please welcome our newest community members: