Can I create 1 dimension including a Hierarchy and Parent/Child members?

• mjksoft

  Old Hand

  Points: 344

  More actions

  July 15, 2007 at 7:51 am

  #173455

  Dear all;;

  How can I create a dimension containing a hierarchy(Levels) and the last level is a parent child ?

  I'll make it very clear with the following table example :-

   

  Country             City                   Branch                          Parent_Branch  

  01                     01                     001                              

  01                     01                     002                               001

  01                     02                     003                               001

  01                     02                     004                               002

  ….

   

  My Expected output form the Geographical Dimension as following

   

  Country(Hier. Level1)

   01

              City(Hier. Level2)

              01        

                          Branches(Hier. Level3 and in the same time a first level of the Parent/Child architect)

                          001

                                      002

                                                  004

                                      003

  And so on..

   

  Is that applicable or not with SQL 2005 Business Intelligent / Analysis Service?

   

  Thanks and appreciate your help .                      

   

  MjkSoft

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  July 15, 2007 at 9:42 am

  #719520

  I don't know if Analysis Service will do it or not... but you can use a recursive CTE in 2005 to produce the result set you need.  I don't have 2005 so I haven't had to use it nor look for it... I think if you search this site for "CTE Hierarchy", you might turn something up.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)