Attribute Relationships, Related Attributes, Uniqueness

I haven't see a clear example of how to setup the following for efficient aggregations:

Example 1:

If I have a Hierarchy like:

Year->Month->Day

Its clear how to setup the attribute relationships as long as the Month members are all unique. Can I somehow include an Attribute like WeekendIndicator? Weekend certainly isn't unique, but it would seem there should be a way to roll this up and include it in the attribute relationships.

Example 2:

In many of my dimensions, I have Hierarchies whose Name member is not unique, but there is an Id value that is unique. I want to build the relationships using the Ids since I know the aggregations will be good...the problem is, that a program querying the data would use the IDs, but a person writing a report would want to use the Name attributes. Is there a way to link the two together so that the non-unique name members are tied to the Id fields. The User Defined Hierarchy define on names works fine from a UI perspective, but I want to make sure I have aggregations as well.

For example, if I have a Campaign dimension, and users frequently want to navigate these for rollups:

Account -> MarketingProgram->MarketingCampaign

The fact is an Order

The Program and Campaign names can be anything, but their IDs are enforced to be unique. How do I manage these together?

Example 3:

And finally, if I want to do rollups with the following types of attributes

AccountStatus->AccountCategory->Account

I can't build an attribute relationship for this since Account Category does not imply Account Status since all accounts of a certain category are not guaranteed to be within an Account Status. How do I efficiently build aggregations for this type of hierarchy? The User Defined Hierarchy allows the customer to navigate it, but the aggregations don't allow it.

Thanks for any insight!

Example 1

Create a new attribute WeekeendIndicator and use it to filter the time dimension.

Example 2

One way to resolve this by adding the name and id in a view or Data Source View as a calculated value, e.g.
Market Development Fund (1)
Market Development Fund (2)
Market Development Fund (3)

Example 3

If the hierarchy is enforced (or a strong hierarchy) such as the time dimension, then aggregations can roll up.

Hope this helps.

|||

That's a solution , but not quite what I want to do.

Example 1 -> Is there a way to pre-aggregate this information with attribute relationships?

Example 2 -> I know I can generate unique names, but is there a way to "pair" attributes together so I can leverage the Ids for the aggregation but see the names elsewhere

Example 3 -> The issue is the hierarchy is not enforced, so is there no option for attribute relationships of any kind, is there another way I should model this?

Attribute Relationships in Project Real

Hi,

I'm just trying to dig a little bit deeper into attribute relationships... The documentation of Project Real is a quite interesting read... So if you have a look at http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx (the technical drilldown for Analysis Services) or even better http://www.microsoft.com/technet/images/prodtechnol/sql/2005/Images/realastd02_big.gif you find an example how to construct attribute relationships.

But here's my problem: do I have a problem in understanding attribute relationships or doesn't also Microsoft know how to deal with them? Why are Fiscal Year/Qtr/Period listed as attribute relationships for Day? This doesn't make sense to me...

Thomas,

If you know the value of Day (the date) then - by defintion - you will also know the value of Fiscal Year/Qtr/Period, since any given date can only belong to one of these. For instance, with a fiscal year starting july 1 and ending june 30, november 11, 2005 will belong to

Fiscal Year: 2005

Fiscal Quarter: 2

Fiscal Period: 5

Also notice that all the attributes in any dimension will always (directly or indirectly) be related to the key attribute in the dimension (and in this case the key attribute is Day)...

|||

Michael,

I aggree 100%... My question is: Why did Microsoft define i.e. the Fiscal Quarter as a direkt attribute relationship to the day? This should be an indirect relationship through Period as with the calendar dimension (through month)... This would make aggregations on the fiscal hirarchy impossible...

Thanks,

|||

You are right Thomas... The Fiscal Qtr and Fiscal Year attributes are actually redundantly related to the Day attribute. I do not think it means anything though, since the relationships between Fiscal Year, Fiscal Qtr, Fiscal Period have been specified (Fiscal Year is related to Fiscal Qtr is related to Fiscal Period is related to Day).

Now I wonder, however, how Fiscal Period can be related to Fiscal Week, since knowing a week doesn't automatically mean that you know a month (I am assuming that Fiscal Period is actually just a month).

|||Well, that was something I was wondering about, too! In the document they state that with confidence... However I don't understand that, too! A week can belong to two months, everything else doesn't make much sense... I only could think about something like "week of 01-February", so this week belongs to "February", regardless of January, 31 is part of that week or not...|||

Sorry. I left them that way to demonstrate how NOT to specify attribute relationships. The way that the Calendar attributes are setup is the correct way. The guiding best practice that I use is that if an attributes are both directly and indirectly related, then you should drop the direct relationships. Thus for the Fiscal attributes, day should be directly related to period, which is directly related to qtr which is direct related to year. The direct relationships between (day and qtr) and (day and year) should be dropped -- like they are with Calendar.

Hope that helps.

_-_-_ Dave

|||

Dave,

that makes sense... Any comments on the weeks, too?

Thanks,

|||

You have to have a direct relationship to weeks because it is the bottom of the natural hierarchy (immeditately above the key attribute).

_-_-_ Dave

|||Well... I disagree. A week can span two months, which is why I think a relationship cannot be defined between Fiscal Period (Month) and Fiscal Week. |||Doesn't that depend on how an orgination's fiscal month is defined - in some cases, the number of weeks per fiscal month is varied (like 4-4-5) in order to prevent weeks from spanning fiscal month boundaries?|||

Deepak is right. If you look at the dimension, B&N has a very unique way of defining fiscal weeks. It isn't the same as calendar weeks.

_-_-_ Dave

Attribute Relationships in Project Real

Hi,

I'm just trying to dig a little bit deeper into attribute relationships... The documentation of Project Real is a quite interesting read... So if you have a look at http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx (the technical drilldown for Analysis Services) or even better http://www.microsoft.com/technet/images/prodtechnol/sql/2005/Images/realastd02_big.gif you find an example how to construct attribute relationships.

But here's my problem: do I have a problem in understanding attribute relationships or doesn't also Microsoft know how to deal with them? Why are Fiscal Year/Qtr/Period listed as attribute relationships for Day? This doesn't make sense to me...

Thomas,

If you know the value of Day (the date) then - by defintion - you will also know the value of Fiscal Year/Qtr/Period, since any given date can only belong to one of these. For instance, with a fiscal year starting july 1 and ending june 30, november 11, 2005 will belong to

Fiscal Year: 2005

Fiscal Quarter: 2

Fiscal Period: 5

Also notice that all the attributes in any dimension will always (directly or indirectly) be related to the key attribute in the dimension (and in this case the key attribute is Day)...

|||

Michael,

I aggree 100%... My question is: Why did Microsoft define i.e. the Fiscal Quarter as a direkt attribute relationship to the day? This should be an indirect relationship through Period as with the calendar dimension (through month)... This would make aggregations on the fiscal hirarchy impossible...

Thanks,

|||

You are right Thomas... The Fiscal Qtr and Fiscal Year attributes are actually redundantly related to the Day attribute. I do not think it means anything though, since the relationships between Fiscal Year, Fiscal Qtr, Fiscal Period have been specified (Fiscal Year is related to Fiscal Qtr is related to Fiscal Period is related to Day).

Now I wonder, however, how Fiscal Period can be related to Fiscal Week, since knowing a week doesn't automatically mean that you know a month (I am assuming that Fiscal Period is actually just a month).

|||Well, that was something I was wondering about, too! In the document they state that with confidence... However I don't understand that, too! A week can belong to two months, everything else doesn't make much sense... I only could think about something like "week of 01-February", so this week belongs to "February", regardless of January, 31 is part of that week or not...|||

Sorry. I left them that way to demonstrate how NOT to specify attribute relationships. The way that the Calendar attributes are setup is the correct way. The guiding best practice that I use is that if an attributes are both directly and indirectly related, then you should drop the direct relationships. Thus for the Fiscal attributes, day should be directly related to period, which is directly related to qtr which is direct related to year. The direct relationships between (day and qtr) and (day and year) should be dropped -- like they are with Calendar.

Hope that helps.

_-_-_ Dave

|||

Dave,

that makes sense... Any comments on the weeks, too?

Thanks,

|||

You have to have a direct relationship to weeks because it is the bottom of the natural hierarchy (immeditately above the key attribute).

_-_-_ Dave

|||Well... I disagree. A week can span two months, which is why I think a relationship cannot be defined between Fiscal Period (Month) and Fiscal Week. |||Doesn't that depend on how an orgination's fiscal month is defined - in some cases, the number of weeks per fiscal month is varied (like 4-4-5) in order to prevent weeks from spanning fiscal month boundaries?|||

Deepak is right. If you look at the dimension, B&N has a very unique way of defining fiscal weeks. It isn't the same as calendar weeks.

_-_-_ Dave

Attribute relationships

I am having trouble understanding SQL Server 2005 Analysis Services dimension terminology. In AS 2000, you defined relationships with hierarchies...just drag and drop levels. In AS 2005, you drag and drop hierarhy relationships, but you can also define attribute relationships. What is the difference between defining a dimension like:

Hierarchy
Level 0
Level 1
Level 2
Level 3

Generated Attribute 23
Level 0
Level 1
Level 2
Level 3

VS.

Hierarchy
Level 0
Level 1
attribute relationship to Level 0
Level 2
attribute relationship to Level 1
Level 3
attribute relatiohship to Level 2

Generated Attribute 23
Level 3

Both designs seem to accomplish the same thing. But there must be some kind of differences. I've read the other thread on attribute definition, but I was hoping for a layman's explanation.

You touched one of the most fundamental questions in AS 2005.

You should look at what is called "Natural hierarchy" and you definitely should be using it in your dimension.

Not sure if what you have as a second example is Natural hierarchy. (Not clear what is the key of your dimension) But it looks close.

Some past posts on the same matter:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=360657&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=377519&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193477&SiteID=1

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

|||

Thanks for the reply and the link to those treads. I've seem some of those treads before, but they never made sense before. I've since re-read the Project Real Analysis Services Technical Drilldown article and I believe I understand now. In my example above, the second option is the prefered design. Level 0 is the top, Level 1 roll ups up to Level 0, Level 2 rolls up to Level 1, etc. It was confusing because you drag the table fields onto the Hierarchy and Level area just like AS 2000. When you do this, AS 2005 creates a Key attribute that contains all of the levels. It is my understanding now that if the hierarchy are Natural and not Reporting Hierarchies, it is best to define the attribute relationship in the Hierarchy pane and remove the attribute from the attribute key definition. Thanks again.

Eric

attribute relationship modelling by BIDS

Hi,

litle question about modelling attribute relationships on dimensions with >=50 Attributes?

As far as I know it is only possible in the treeview of BIDS to model attribute relationships, but what if I have already 50 Attributes and want to drag an attribute to an attribute thats already as much down that i cannot see it - if you drag an attribute the view does not scroll down like excel or some other appliation while you are at the boarder of the view.

Is there any other way do add some attribute relationships? (not code, not programming)

Hannes

You can right click on an attribute in the tree view and then choose "copy". Then you can go down to the attribute that you want to relate the first attribute to and "Paste" on the "<new attribute relationship>" drop zone.

HTH,

Steve