Dimensions with complex hierarchies ?

Please advise...

I have a dimension hierarchy that each member could have multiple parents and therefore the out-of-box ParentKey support in dimension would not be sufficient.

Based on Mr. Kimball recommendation ( http://www.dbmsmag.com/9809d05.html ) I tried to use a linked helper dimension that has only ParentKey and ChildKey. However I am having difficulty utilizing it in the cube browser. The similary user experience we have with the out-of-box hierarchy is missing in this solution.

I am wondering if anyone in this forum has done something similar or know a better way of doing it.

Appreciate any help or comment.

Best Regards,

This is not natively supported by AS, but you can make it work as follow:

Create a duplicate member with same member name but different member key: ie: Member 1 (Key=1) and Member 1 (Key=2)

The original member is the one linked to your fact data Member 1 (Key = 1)

Associate a custom member formula to the shadow member (Key = 2): Member formula = Dimname.hierarchyname.$[1]

You’re set.

|||

In AS2K5, you created this via a many-to-many dimension. The "linked helper dimension" in Kimball terminology is implemented as a measure group.

_-_-_ Dave

sql

Dimensions with complex hierarchies ?

Please advise...

I have a dimension hierarchy that each member could have multiple parents and therefore the out-of-box ParentKey support in dimension would not be sufficient.

Based on Mr. Kimball recommendation ( http://www.dbmsmag.com/9809d05.html ) I tried to use a linked helper dimension that has only ParentKey and ChildKey. However I am having difficulty utilizing it in the cube browser. The similary user experience we have with the out-of-box hierarchy is missing in this solution.

I am wondering if anyone in this forum has done something similar or know a better way of doing it.

Appreciate any help or comment.

Best Regards,

This is not natively supported by AS, but you can make it work as follow:

Create a duplicate member with same member name but different member key: ie: Member 1 (Key=1) and Member 1 (Key=2)

The original member is the one linked to your fact data Member 1 (Key = 1)

Associate a custom member formula to the shadow member (Key = 2): Member formula = Dimname.hierarchyname.$[1]

You’re set.

|||

In AS2K5, you created this via a many-to-many dimension. The "linked helper dimension" in Kimball terminology is implemented as a measure group.

_-_-_ Dave

Dimension processing taking too long

A regular dimension with some hierarchies is defined.

StorageMode:Molap

ProcessingGroup:ByAttribute

ProcessingMode:Regular

then on processing this dimension individually, why is a "select distinct" from the relational table done for each attribute of the dimension separately and finally a select distinct <all attribs> is done in one query.

What is the purpose of this ? this slows down processing for a large dimension table.

This is not done for rolap dimensions though.

Is there a way to speed up processing for Molap dimensions.

Also what is the significance of the ProcessingGroup property for a dimension?

Regards

mat

Processing by table seems to process all dimensions from that table at the AS server while by attribute gets each unique set of attributes from the database and then indexes them etc. According to a post I've just read there may be an issue with processing by table.|||

Try modifying EnableTableGrouping server property.

See description in http://msdn2.microsoft.com/en-us/library/ms174469.aspx

EnableTableGrouping

A Boolean property that specifies whether table grouping is enabled. If True, when processing dimensions, entire dimension tables are queried at once, as opposed to separate queries for each attribute.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Dimension processing taking too long

A regular dimension with some hierarchies is defined.

StorageMode:Molap

ProcessingGroup:ByAttribute

ProcessingMode:Regular

then on processing this dimension individually, why is a "select distinct" from the relational table done for each attribute of the dimension separately and finally a select distinct <all attribs> is done in one query.

What is the purpose of this ? this slows down processing for a large dimension table.

This is not done for rolap dimensions though.

Is there a way to speed up processing for Molap dimensions.

Also what is the significance of the ProcessingGroup property for a dimension?

Regards

mat

Processing by table seems to process all dimensions from that table at the AS server while by attribute gets each unique set of attributes from the database and then indexes them etc. According to a post I've just read there may be an issue with processing by table.|||

Try modifying EnableTableGrouping server property.

See description in http://msdn2.microsoft.com/en-us/library/ms174469.aspx

EnableTableGrouping

A Boolean property that specifies whether table grouping is enabled. If True, when processing dimensions, entire dimension tables are queried at once, as opposed to separate queries for each attribute.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

Dimension Hierarchies

When a new hierarchy in created in a dimension, all of the attributes are listed as related to the lowest level of the hierarchy.

I have heard that this restricts SSAS to only creating aggregations at the leaf and all levels for that hierarchy until you re-distibute the attribute relationships. This will then allow SSAS to create aggregations at levels higher than the leaf level.

What I mean by this is that when you have both Year, Month and Day in a hierarchy, you can have Year and Month under Day (not allowing aggregations at something other than the leaf level) or you can put Year under Month and now that will allow aggs on all levels of the hierarchy.

Can someone confirm this?

What are the implications of not defining the relationships - what are the benefits of doing it?

Thanks

Mark

http://mgarner.wordpress.com

Hi Mark,

The "Project REAL: Analysis Services Technical Drilldown" paper seems to confirm what you've surmised:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Best Practice: Spend time with your dimension design to capture the attribute relationships within the dimensions.

Important: You must define attribute relationships if you want to design effective aggregates, if you want effective run-time calculations from the formula engine, or if you want valid results in MDX time functions.

In the hierarchy-based nature of SQL Server 2000 Analysis Services (which only supports natural hierarchies), aggregates are designed around hierarchies. In SQL Server 2005 Analysis Services, aggregates are combinations of attributes. User-defined hierarchies are not used. The Storage Design Wizard uses attribute relationships to determine when combinations of attribute rollups will be useful (and thus aggregates will be designed for those attributes). Without relationships, one attribute is as significant as any other attribute, so the Storage Design Wizard simply ignores the attribute and uses the ALL level for the dimension. Thus if you want to design effective aggregates, you must define attribute relationships. Without them the system still returns the proper number, but values must be calculated at run time and aggregates are not useful.

>>

There are more detailed explanations of AS 2005 aggregations elsewhere, such as in the "Designing Aggregations" sections of Teo Lachev's book on AS 2005, and in the PASS 2005 session: " Understanding Analysis Services 2005 Aggregations from Every Angle" (if you have access to the archive).

|||

Deepak,

Thanks for the info. That doc was great. I knew of some of the Project REAL documentation, but not that part.

Thanks for the help.

Mark

http://mgarner.wordpress.com

DImension depends on other dimesion

I have a parent child hierarchy in a table.

Due to some requirements we need to create the same hierarchies for different years with different primary keys.

i have data for 2005 and 2006

when i create a new parent chile dimension I am getting all 2005 and 2006 hierarchies.

I have one more dimension where I list only years.

Now I want to fileter the first diemsion depending on second dimension.

I tried "depends on Dimension" property.

it is not allowing me to save the dimesion at all.

It is saying invalid parent child relationship

if i remove the "depends on Dimension" then every thing is working fine except filtering.

I need filtering of One dimesion from other.

Pleas help!!!!

Thanks

Pasupula

How to use "depends on Dimension" property for parent child relation dimension|||

This paper on Many-Many dimensional modelling in AS 2005 by Marco Russo may help you - page 73 discusses how to deal with multiple parent-child hierarchies. There is a Hierarchy attribute, which selects a specific hierarchy:

http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/7/Default.aspx

>>

...

Multiple Hierarchies

Parent-child dimensions are a useful feature of Analysis Services. They can be used to model hierarchical and fast changing dimensions like sales or employee organizations. A limitation of this feature is that you can define only one parent-child hierarchy in a dimension. In the real world, this may be an issue. For example, in the middle of a company reorganization, someone may need to analyze alternatively the present with the eyes of the past (actual data for previous organization hierarchy) and the past with the eyes of the present (past data for actual organization hierarchy).

...

>>

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.