i have a field name Region which has north,south,east,west...my customer is asking that i should give him a functionality of north and South,East and West.......so that the region has 6 fields i.e, north,south,east,west,North and South,East and West
what you can do then is extract out one more table out of the main table having distinct region and the sum of their values, then the new table will have only 4 regions and their values as in this example. Then you can apply the same thing on that new table.
the level of granularity is decided by you only. How you want to see and represent, you create a model for that purpose only. If the client wants to see the North-South summed up data, then you have to create like this one.
I must respectfully disagree with you. We are not talking here about deciding the level of granularity but about mixing the levels of granularity. Mixing granularity is always a bad idea. People inevitably sum the higher grain measures producing incorrect reporting. If a client ask a simple question - what is the total sum of sales for all regions? You can't simply do a sum here without excluding the aggregated regions first. The developer is just making life really hard for himself or herself in my opinion.
If this is what clients wants and the consultant can't see anything wrong with this request than I would be worried.
When developing fact tables, aggregated data is NOT the place to start. To avoid “mixed granularity” woes including bad and overlapping data, stick to rich, expressive, atomic-level data that’s closely connected to the original source and collection process." - Ralph Kimball
I am not saying that I disagree with you. I totally agree with you, mixing up is never a good idea. But here I am not dealing with Sanchit's client, else I would have suggested them something else that what could be a better option for them to analyze. But here what I am doing is just helping him with the script, just to get the desired result. I think you can understand this now.
