Can we query a parent child hierarchy in a cube from any Level?

Question

Can we query a parent child hierarchy in a cube from any Level?

ilovedata

Old Hand

Points: 377
More actions
September 1, 2015 at 9:39 pm

#300159

I am using an Adventure works Cube with DimEmployee dimension. When I look at the data through browser, I can see as below.
-->All
---->Ken, Sanchez
------->Laura, Norman
------->Debbie, Borah
---------->Shawn, Mike
---------->Lacey, Rhonda
Now I want to know if there is a way I can pull data from middle of this hierarchy like,
------->Debbie, Borah
---------->Shawn, Mike
---------->Lacey, Rhonda
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19127 More actions · Answer 1

Are you looking at the data in Excel, or did you write an MDX query? It is possible to only show certain levels within that hierarchy, but it's not clear from your question if you're looking for the MDX or exactly what method you need to implement this with...

ilovedata Old Hand Points: 377 More actions · Answer 2

Hi Martin, sorry for not making it clear. I am trying to see if it's possible through MDX or if you can tell me the steps in excel also it would be fine. I would highly prefer MDX though. I am searching for an answer of such but didn't find any over the internet.

Thanks in advance

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

ilovedata (9/1/2015)
I am using an Adventure works Cube with DimEmployee dimension. When I look at the data through browser, I can see as below.
-->All
---->Ken, Sanchez
------->Laura, Norman
------->Debbie, Borah
---------->Shawn, Mike
---------->Lacey, Rhonda
Now I want to know if there is a way I can pull data from middle of this hierarchy like,
------->Debbie, Borah
---------->Shawn, Mike
---------->Lacey, Rhonda
Thanks in advance.

Quick suggestion, use the Descendants function

😎

Martin Schoombee SSCoach Points: 19127 More actions · Answer 4

Also look at the Levels function, which allows you to return a specific level within a hierarchy. Combined with the Descendants function and assuming you know which levels you need to return, this should be good.

ilovedata Old Hand Points: 377 More actions · Answer 5

Thanks for the guidance, Once I am able to get the answer I will mark it as solution. it will be really helpful if you have a sample query by any chance.

Martin Schoombee SSCoach Points: 19127 More actions · Answer 6

ilovedata (9/10/2015)
Thanks for the guidance, Once I am able to get the answer I will mark it as solution. it will be really helpful if you have a sample query by any chance.

The best reference would be the official MDX documentation on MSDN, which also contains sample queries. Without knowing the structure of your cube and exactly what you're trying to do, we cannot really offer anything that will be closer than that.