I just found out that my dataset is a lot messier than I expected and I was wondering if anyone here had some advice. I have sales data that is divided into regions (5 big breaks on a national level and then 4-6 breaks within each big national level break, the second break is what I am calling a region. The breakdown after that is all of the stores that sell within a region.), but I just found out that what is included in the regions varries over time.
In the last 3 years, they have removed 3 regions and then moved their sales to other regions (Imagine taking all of the sales for the twin cities and then deciding that on Feb 1 they should be switched from being recored as"Twin Cities" to half of them being recorded "Madison" and the other half being linked to "Chicago").
Also it is very common (not exactly sure how common yet, working on figuring that out) for an indivudal store (the breakdown after region) to move from one region to another (so on Feb 1 my store moved from being recored as selling in the twin cities to selling in Chicago).
Naturally this is information is not contained in my data set (no one things of the statistics guy when they make data deicisions), so I have a cube that currently has no link to this information. In an access database I have access to individual store level data that is a list of their monthly region association (so for my store it would say "twin cities" in each cell until Feb 1 2012 and then switch to "Madison").
I have two ideas to approach this. The first is to go through the access database, identify the stores that have been consistent in their region definition and remove all other stores, then join that with the data I have already (when my IT guy gets me access to the information in SSMS). This amounts to deleting any store that has ever switched region from the data. The problem I see here is it is going to remove what I imagine to be a ton of data and the data is going to be removed more heavily from some regions than others, since some regions have more movement than others (and we still have the 3 complete region removals in the way).
The other thing I am considering (not sure if it is legit) is to go through and add in a dummy variable that is a 1 if there was a region change and include it in my model. This does not address multiple changes, but if I include that information in my model it should deal with some of it, yes?
I would like to come up with a solution that does not involve gutting my data set, which is what I am leaning towards now. It might be that a store level shift can just be ignored, since they are somewhat common and are not that huge of a shift, but deleting a region and moving it seems like it would just be too large to ignore. Thank you for your suggestions.
Update: Looks like our data team already has a solution implemented, see @peter and my comments below for the solution.