Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

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

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.