Brian Cowhig


Perhaps my understanding of the UDM is incorrect, but I was under the
impression that, when a member of a dimension is selected, the corresponding
member of another dimension should be automatically associated.

To illustrate: I have a table of users, and each user has an account
creation date; there is an Account Creation Date time dimension based on
that, as well as a Users dimension.

When I filter my query (see below) based on an individual user, I expected
the current member of the Account Creation Date dimension to be the date of
that user's account creation; instead, the All member is returned:

WITH MEMBER [Measures].[Creation Date] AS
[User Account Creation Date].[Calendar Year - Month - Day].CurrentMember.Name
SELECT {[Measures].[Creation Date]} ON COLUMNS
FROM [Sales Cube]
WHERE ([Users].[User ID].[2355320])

Is my understanding of UDM functionality incorrect, or is there a flaw in my
MDX or in the implementation of the cube

This is driving me crazy because it is fundamental to some significant
metrics required of this cube, so any assistance will be greatly appreciated.

Thanks in advance,

-- Brian Cowhig





Re: Newbie Question: Correlating members across dimensions

Thomas Ivarsson


Hello Brian! The behaviour you have experienced is how Analysis Services works since the first release in 1999. There is no dependence between dimensions in a cube by default.

All dimensions point to the All-level as default.

My guess is that you expect the behaviour that you have in an inner join in the SQL world.

If you query a data mart /star schema with TSQL in reporting services you will se the behaviour you have expected.

HTH

Thomas Ivarsson






Re: Newbie Question: Correlating members across dimensions

Vladimir Chtepa

Hi Brian,

please, could you depict the report you want

There are relation in the UDM, and you can use them in MDX, but another way as in SQL.






Re: Newbie Question: Correlating members across dimensions

Darren Gosbell

Brian, as Thomas has said, dimensions are independant and there are no inherant relationships between them. Something like the following might work, which should show non-empty creation dates for the current user (this depends a bit on which measure is the default one)

SELECT {} ON COLUMNS,
NON EMPTY [User Account Creation Date].[Calendar Year - Month - Day].Members ON ROWS

FROM [Sales Cube]
WHERE ([Users].[User ID].[2355320])

Where you may have gotten confused is that attributes within a dimension can set the context (provided that their attributes are correctly related). So if you select "August 2007" at the month level, then "2007" becomes the current year level. Following this example, if your [Users] dimenions had attributes of [User ID] and [Creation Date], then something like the following might work.

WITH MEMBER [Measures].[Creation Date] AS
[Users].[User Account Creation Date].CurrentMember.Name
SELECT {[Measures].[Creation Date]} ON COLUMNS
FROM [Sales Cube]
WHERE ([Users].[User ID].[2355320])






Re: Newbie Question: Correlating members across dimensions

Brian Cowhig

Thanks, Thomas. I'm familiar with dimensional behaviour in pre-2005 Analysis Services, but with the UDM I'd hoped that dimensions would be correlated where applicable (e.g. when they are based on the same table).

At least now that I know this to be a limitation of AS rather than an error in my implementation, I can focus my efforts on working around the limitation.

I appreciate your time in responding to my post,

-- Brian






Re: Newbie Question: Correlating members across dimensions

Brian Cowhig

Hi, Vladimir. My data mart has a fact table consisting of user-generated events, which are linked to the user dimension by user ID (and also have their own date dimension based on event date). I need to count the number of events per user within two weeks of the user creation date. So, for a given user, I first need to determine the user creation date, then correlate that with the event date and count events in the two-week time range.

I appreciate your interest in helping me with this issue.

Thanks,

-- Brian






Re: Newbie Question: Correlating members across dimensions

Brian Cowhig

Hi, Darren. Thanks for your input.

I understand both of your suggestions and I agree that they are viable. I'll consider them as I work towards a solution for the metrics that I require. Another approach that I'm evaluating is to add in the user account creation date as a user member property, then filter the event date dimension on that value.

Once again, my thanks to all of you who have taken the time to provide your input and suggestions regarding my questions! It's greatly appreciated.

Sincerely,

-- Brian






Re: Newbie Question: Correlating members across dimensions

Thomas Ivarsson

Hello Brian. Thank you for the comment.

My answere was based on the idea that that you had assumed correlation between different dimensions by the fact table.

If you have a customer dimension table and build a customer dimension with one or several natural hierarchies and attribute hierarchies you will have an autoexist behaviour in the way you have described in your first post.

But this behaviour doest not exist outside of a dimension.

The difference between AS2000 and AS2005 regarding dimensions is that each hierarchy in AS2000 is a dimension but in SSAS2005 they are one dimension and several user hierarchies.

Kind Regards

Thomas Ivarsson





Re: Newbie Question: Correlating members across dimensions

Darren Gosbell

I understand both of your suggestions and I agree that they are viable. I'll consider them as I work towards a solution for the metrics that I require. Another approach that I'm evaluating is to add in the user account creation date as a user member property, then filter the event date dimension on that value.

Member properties would be implemented as additional attributes in AS 2005, so this is really the same approach.

The main reason you would use member properties to access these attributes is if analyzing and aggregating an attribute on their own made no sense. Something like a customer phone number, you might never analyze in isolation and you would always want to see associated with a given customer. In this case you would disable the attribute hierarchy and just access it as a member property.

In the case of the creation date, there might be cases where you want to build a year - month -day or other date related hierarchy to analyze your measures by. In which case leaving the attribute hierarchy enabled would be preferrable.

-- from your response to Thomas

At least now that I know this to be a limitation of AS rather than an error in my implementation, I can focus my efforts on working around the limitation.

Actually this is more of an implementation error than an AS limitation. Individual dimensions are designed to be independant and not to imply specific correlations. If attributes are specifically related in either a one to one or one to many relationship they should normally be modelled within the one dimension, this is how you would get the correlation and implied setting of context that you are after.






Re: Newbie Question: Correlating members across dimensions

Brian Cowhig

Hi, Darren. Now that I've been working on this implementation a few more days, I think I see what you mean about the implementation error. Rather than trying to correlate one dimension with another (via filtering on a member property), incorporating both into a dimensional hierarchy seems to be the best approach (e.g. users organized by account creation date). Although I think I can get the filtering by attribute value query to work, it looks like it will perform very poorly, so implementing the dimensional hierarchy approach should make the query both simpler and more efficient.

Thanks again for contributing your time and expertise,

-- Brian