13 Replies Latest reply: Apr 17, 2014 12:33 AM by Rajat Arora RSS

    hey

      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

        • Re: hey
          Marius Koekemoer

          Hi Sanchit,

           

          Can you please give a bit more details as I am unclear what you are trying to achieve.


          Thanks,
          Marius

          • Re: hey
            Rajat Arora

            please provide a sample data so that I can create a solution and send back to you..

            • Re: hey

              Hi Sanchit,

               

              How do you define the new  "North and South" region? Is that the sum of the north and south data?

              If yes, that you can't add the new region to the same dimension because it is an aggregation.

               

              I think that what you trying to achieve is to transform this:

               

              1.jpg

               

              into this:

               

              2.jpg

               

              That way you can display the data by the "top" region and than drill down to "sub" regions.

               

              In your script you could use something like this:

               

               

              pick(wildmatch(Region, 'North', 'South', 'East', 'West'),'North and South', 'North and South', 'East and West', 'East and West') as [TopRegion]

               

              Cheers,

              Paul

              • Re: hey
                Neil Gulliver

                Hi,

                You will need clarification from the customer exactly what he means by North East etc. Are they asking for it to be a combination of North and East values or something more individual. Or is the input data going to change ?

                • Re: hey
                  Rajat Arora

                  I think you require this, check the application attached if it helps...

                    • Re: Re: hey
                      Rajat Arora

                      or you can simply do this...

                        • Re: hey

                          Hi Rajat,

                           

                          But if the N-S and E-W are the sums of their respective regions than you are mixing different levels of granularity in the same dimension. This will lead to incorrect calculations (unless you use set analysis).

                           

                          Cheers,

                          Paul

                            • Re: hey
                              Rajat Arora

                              what is the requirement exactly then?

                              your customer is asking for two more values N-S and E-W in the dimension Region..what would be their values then..their sum up only or something else?

                              • Re: hey
                                Rajat Arora

                                Hi Paul,

                                 

                                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.

                                  • Re: hey

                                    Hi Rajat,

                                     

                                    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.

                                     

                                    Keep to the Grain in Dimensional Modeling - Kimball Group

                                    Fistful of Flaws - Kimball Group

                                     

                                    "

                                    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

                                     

                                    Cheers,

                                    Paul

                                      • Re: hey
                                        Rajat Arora

                                        Hi Paul,

                                         

                                        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.

                                         

                                        Thanks

                                        Rajat