Day over Day - Freezing data?

[cmcreynolds]

Okay - so the inevitable has happened: I've been asked to compare data day over day ("progress"). I at least managed to get my bosses to understand they want "last full day" compared to the "previous full day" instead of today versus yesterday.

I am connecting to Salesforce (in case anyone cares) and those records change day to day - one record can go from "inquiry" to "prospect" - is there a time field? YES! BUT, some are manually entered. (Example, Sarah gets an e-mail on Wednesday saying the application was received last Friday, so the field is populated with Friday's date) Therefore, the date fields aren't really reliable.

So, I was thinking the best solution would be to download the data, freeze it and do the same the following day - then, I'd compare the two. BUT, how can I do this as an ongoing process? Is there a way to "discard" older date freezes? (My boss just wants yesterday compared to the day before - again, "progress")

I don't need code right now, just some conceptual ideas. Maybe I don't use PQ or PP? Just Excel?

Added super bonus - I need to exclude weekends. (So, on Tuesday, I would be looking at data from Monday and Friday).

I think this is going to incorporate VBA or Macros, which I'm very unfamiliar with, so be gentle, kind MrExcel-ers!

Thank you!

 

[Matt Allington]

My starting position on this one is that you seem to have a conflict between your business reporting requirements, your business process and your systems. The simple question I have is "why does the process/system allow the date to be changed if the requirement is to report on the time of data creation"? All too often I see business system/process issues pushed to the reporting system to be "handled". I recommend either preventing the field being changed (possibly not viable for other reasons) or more likely find some other date filed to use instead (I would be surprised if there were not some system date that exists already.

 

[cmcreynolds]

Well, the date I'm looking at is user input in that it's a record that an application has been received. We accept applications on behalf of a client, so our users input into Salesforce when our client received the application, not when something actually hit our data. THEN, we have triggers that change the record based on that user input.

Does that make any more sense? We've implemented Salesforce only a year ago and no one asked me (the analyst) about how thing should be set up, so there are MANY concepts that aren't executed well. (A lot of my posts deal with - "well, here's a mess I have to make sense out of...help?")

 

[Matt Allington]

Ok, got it. So are you saying the issue is that the they can then change the date later? If this is the issue, and if you care about this, then I would suggest you need to extract the data into another storage location prior to importing into power pivot. If it were me, I would have a master table in SQL (or Access if you don't have access to SQL), have a mater table of just the relevant header data (unique ID of the transaction and date minimum). Each time you take an extract, put it in a temp table, append the new records to the master table plus any old records that have changed the date.

This table then can be imported to power pivot as a data table (not a lookup table). From there you can report on the movement in dates.

or

just ignore the changes and take the first extract.

Or

just ignore the original and take the last view as being correct.

 

[cmcreynolds]

Okay, what about this - I can get the data and make a report for that day. Is it conceptually possible to save that report with an appropriate date field and compare my reports day to day?

 

[Matt Allington]

Manually - yes, as long as you keep a separate copy of the report. I doubt that is practicable though. The only viable way I can think of is to store the changing data and modify your reporting to read this data.

 

[cmcreynolds]

yeah, that's what I was afraid of. At what point do I "purge"? I mean, after a couple of months, won't this file get huge?

 

[Matt Allington]

it depends how you do it. If you do it manually, then yes - each refresh will require you to keep a full copy of the previous month file including all data and all reports. However if you do it as I suggested earlier, then you are only keeping the delta changes on the specific records that change. That could be as small as a table that contains the ID and current date. this would not be a problem. But of course it is harder, particularly if you are not familiar with relational databases. It is possible that you could do it with Power Query instead of a RDBMS as long as you keep each monthly extract for the refresh. But in my view this is harder again (just like it is harder to write a letter in Excel instead of Word - you can do it but it is harder).

 

[cmcreynolds]

Since we have individual records, my boss is really wanting changes in the aggregates. So, I would first set up a report that aggregates the various metrics by client, right? Then I would store that in the database instead of the entire pull from Salesforce. (Am I on the right track?) A different boss suggested using Access, but that seems so complicated for such a small set of numbers, but I guess that's the price of simplicity?

 

[Matt Allington]

If you want to refresh the aggregates, then you need the detail. It doesn't matter if you keep the detail history or not, however if you don't keep the detail history, then you can only ever build an aggregate based on the current view of the world. If you are happy with that, then that is a great option - I do this all the time. I tell my customer "This is the current view of the world. It may have been different in the past, but this is the way it is today. If you want to go back and see the way it was in the past then I can do that, but it is more time, more effort, more storage, more cost - it depends what you want."

If you keep only the current view of the world, then I would just reload from SFDC each time. If you want to keep the history and rebuild each time, then I (personally) would create my own local store that keeps the current state as well as any delta history records that you care about. Not a copy of every extract - just a copy of any previous extracts that are different to the latest extract.

This is independent of whether you want to load aggregates or detail. You need the detail to build the aggregates anyway.

I don't think Access is complicated, but if you have never used it I can understand why you may not want to do this. Actually I learnt DB using Access, and then moved to SQL Server on my PC (forced to due to file size). SQL Server takes a bit to get started (not too bad). Once you get going with SQL Server, it is 000's of times better than Access.

But it depends what you want. You simply can't have the flexibly of rebuilding history unless you have access to the changes. If you don't want this (or it is too hard), then you should simply take a new extract of the data each month from SFDC and treat it as "this is the way it is" and forget about the way it was. This is a valid way to use the data and it is probably well within your reach today.