Hierarchy Parent-Child

Hi.

I have a parent-child relationship in a table, by making use of an ID, and Parent ID.

Firstly how do I manually change, or setup this hierarchy.

Secondly, how do I do a rollup, along this hierarchy, sothat I can retrieve the aggregate values, using MDX?

I thank you in advance for your feedback.

Hi. Sounds like you should start with the basics in Analysis Services about parent-child hierarchies. Here's a link to MSDN on-line which explains how parent-child hierarchies work in SSAS 2005. The subsequent links at the bottom of the page can guide you to more detail on creating the parent-child hierarchy in BI Studio.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agdimensions_5ab7.asp

Your second question, "how do I do a rollup, along this hierarchy, so that I can retrieve the aggregate values, using MDX?". There is a simple answer to your question. The answer is "nothing". The normal aggregation behavior of Analysis Services will aggregate the measures up your parent-child hierarchy. If you have a measure such as 'sales' and it has an aggregtion type of SUM, then the 'sales' values are naturally summed as you roll up the parent-child hierarchy. There is no action required on your part unless you want to change the SUM aggregation of the 'sales' measure.

Hope this helps - PaulG

|||

Hi. After a more thorough look, I determined that BI Studio seem to have sucessfully determined the hierarchies.

I can say this, based on the 'levels', that is shown under the parent view. The problem that I now have, is that for two tables, of exactly the same structure, BI is showing me the ID's of one table, and the 'name' values for another. I

have already tried to set the 'NameColumn' property of the parent id, as well as the source id, but it does not seem to work. Is there any place, where I can get good articles, on how to change these hierarchies. Or send a sample to someone?

The reason why I feel that my hierachy isnt rolling up, is the following. When I run the MDX query:

select {[Measures].[People]}on columns,{[FAFA Location].[FAFA Location].[Africa]} on rows

I get 'null', although if I do a level mdx, with 'africa' included, I get a rollup value back.

Regards.

|||

Hi. For your first problem, getting a "name" to display instead of the ID, you need to manage the properties of the child attribute which is usually the primary key for your dimension. Set the NameColumn property of child attribute to the "name" column from your parent-child table.

Second problem, getting null when querying, I can't say what the problem is without debugging your model. The most common problem is that the dimension is NOT linked properly to the fact data. What is the result when you execute a query against leaf-level descendant members of [Africa] in the dimension (I'm assuming that [Africa] is a non-leaf level member)?

Hope this Helps. Paul Goldy