Help with Historical Trend Reporting

[elinsner]

Hi, my company has asked me to look at creating historical trend reports in Power BI so I recently took a training class to get me started.  In the meantime my manager setup our SFDC data to go Amazon Redshift daily.  After getting that data into Power BI I'm looking at creating a daily report on our pipeline change (as a test), I realized that only delta changes were sent to Redshift after the first download.  My question to this group is, can I use the delta data to create a historical trending report?  If so, how?

Thanks in advance,

Erik

[v-yuezhe-msft]

@elinsner,

Could you please elaborate the “delta change”? After you import data from Amazon Redshift to Power BI and create reports,  you are able to schedule refresh for the dataset in Power BI Service. This way, once data is changed in your data source, the data in Power BI report will be automatically updated based on refresh schedule.

Regards,
Lydia

[elinsner]

Lydia (@v-yuezhe-msft),

When I say delta change, I'm referring to the data moving from our CRM to Redshift.  After the initial download of CRM data, Redshift is setup to only download records that change.  My question is whether or not Power BI can use that type of data to do historical reporting, or if a full download needs to be done everyday.  We are using Redshift because we don't want our historical data to be overwritten as we want to do month-to-month and week-to-week type trending analysis.

Thanks,

Erik

[v-yuezhe-msft]

@elinsner,

In Power BI, do you connect to CRM or Redshift?

Regards,
Lydia

[elinsner]

@v-yuezhe-msft, we currently have Power BI connected to Redshift.  We thought we had to do this in order to do historical reporting.  When we connected directly to CRM we were not able to figure out how to store historical data.

Thanks,

Erik

[v-yuezhe-msft]

@elinsner,

As long as the data is changed in Redshift and you click the refresh button in Power BI Desktop, the report data will be overwrited by new data. You would need to export data from the report regularly in order to save historical data.

Regards,
Lydia

[elinsner]

@v-yuezhe-msft, data from CRM is exported to Redshift everyday, but only the changes that occur and not every record.  Are you saying that if I click the refresh button on Power BI Desktop that it can process the changes with the other data?

Thanks,

Erik

[v-yuezhe-msft]

@elinsner，

When you click Refresh button in Power BI Desktop, it will bring the latest data of your source(Redshift) to report.

Regards,
Lydia

[elinsner]

@v-yuezhe-msft How do I prevent that update from overwriting my data?  If it overwrites the data I won't have any history.

[v-yuezhe-msft]

@elinsner,

Please take a look at the following similar thread.
http://community.powerbi.com/t5/Desktop/store-historical-data-from-Power-BI-Online/td-p/127700

Regards,
Lydia

[elinsner]

Thank you Lydia.  I was hoping the solution would be simpler than copying my data daily from Power BI to a CSV file somewhere else then re-importing the data.  I think I may have to use a 3rd party database to download daily data to, then connect it to Power BI to create the trending metrics I need.