How to decide the grain when there are two different metrics, Forecast and Actual, at two different levels. Say,for a retail chain Forecast is done at State level whereas Actuals are received at Store for every item sold. So grain of Forecast is State and grain of Actual is Item. In a traditional data mart, we would create two fact tables with their respective grains.
For Endeca, what if I go with just one record type, say "Sales",and bring down and assign Forecast value to every Item sold? So all items sold in all Stores in that State will have the same forecast value. I can use ARB function and group by State to get the right forecast value. The reason I want to do this is to make sure that my dashboard won't break when a Store level filter, say Store Manager, is selected.Will this work or do I need to create two record types, "Forecast" and "Actual"?
Appreciate your thoughts on this approach.