I am working on a cube to report retail sales that is currently an offline task. Information is received on a lag that is longer than we can wait to act, so I developed factors for each week between the cutoff date and the future date when data is mostly complete and we call it good.
The cube has a product hierarchy (generic dimension), time is 4-4-5 at the week level, and an account dimension to contain the various measures (units, wholesale revenue, cost measures, etc.) as well as my factors. So for example, I have a members called UnitsRaw, UnitsAvg, and UnitsMax. UnitsAvg = UnitsRaw * AvgFactor and represents where we will likely end up when we lock the period in 6 to 8 weeks based on where we are now and how much the number has grown in the past. Max is just one sigma above avg to add perspective.
The problem I am running into is how to input the data efficiently. For a given month, the same factor is used for all products and all weeks within the month. Each week when I update the report the factor shrinks. For instance, at Jan cutoff date I may use a factor of 150% for Jan. When I add more data the next week, the factor might be 140%. How can I use a data entry template to spead a constant into all intersections of product? I’d like to input one cell for January and spread to the weeks as well, but that’s not a huge deal if they can’t coexist.
