Question to ytd()

Hi,

Ihave a Time-Dimension with 2 hierarchies: (a) Year - Semester - Quarter - Month and (b) Year -Week.

Than I have a calculated member in my Cube like:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(ytd(), [Measures].[Sales Volume KG]),
FORMAT_STRING = "#,#.00",
VISIBLE = 1 ;

When I browse the cube and I use the (a) hierarchy on the colums, all works fine; I get the accumulated sums over Semesters, Quarters, Month. When I use the (b) hierarchy, in each cell I get the normal week-sum and not the accumulated. What's my mistake?

Thanks
Hans

Not sure whether you've read the "Time calculations in UDM: Parallel Period" entry in Mosha's blog, but it discusses approaches to designing time calculations which work with multiple time hierarchies. In your specific case, YTD() may always be using hierarchy (a) by default, since you didn't specify a parameter. And, as explained in the blog entry: "If Time dimension has hierarchies which share some attributes, then it might not be possible to say by which hierarchy user browsed". So, my guess is that, when a week is selected on hierarchy (b), the current member of hierachy (a) becomes the corresponding year, in which case YTD() returns a set with that single member.

If you post more info on the relationships and shared attributes between the 2 time hierarchies, maybe someone here can give a good solution?

|||

Hi Deepak,

Thanks for the reply. My hierarchies have the following relationships (from top down; the indent-part are the attribute relationships):

Name: [Date - Year]
- Year
- Semester
- SemesterDescription
- Year
- Quarter
- QuarterDescription
- Semester
-Month
- MonthDescription
- Quarter

Name: [Date - Week]
- Year
- Week
- WeekDescription
- Year

The only shared attribute between the 2 hierarchies is "Year". The [Date] Dimension is a "hand-made" Table (with fields for FullAlternateDate, Year, Semester, ...) and not the built-in on of AS2005.

Hope this helps for finding an answer.

Thanks
Hans

|||

Hi Hans,

Am I correct in assuming that the key attribute of the dimension is [Date], though it isn't explicitly shown in any hierarchy - and that [Date] is also the granularity attribute for the measure group(s) in question?

|||

Hi Deepak,

The key attribute of the Dimension is [TimeKey] which is a runnung number (1 ... 9497) from 01.01.2000 until 31.12.2025 and the TimeKey ist the granulatiy attribute. You are correct.

The DimTime is handmade (by me with a small Microsoft Access Function) and has the following fields:

TimeKey (PrimaryKey), FullDateAlternateKey, DayNumberOfWeek, GermanDayNameOfWeek, EnglishDayNameOfWeek, SpanishDayNameOfWeek, FrenchDayNameOfWeek, DayNumberOfMonth, DayNumberOfYear, WeekNumberOfYear, GermanMonthName, EnglishMonthName, SpanishMonthName, FrenchMonthName, MonthNumberOfYear, CalendarQuarter, CalendarYear, CalendarSemester, FiscalQuarter, FiscalYear, FiscalSemester

Example 1	01.01.2000 00:00:00	7	Samstag	Saturday	Sábado	Samedi	1	1	52	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1
2	02.01.2000 00:00:00	1	Sonntag	Sunday	Domingo	Dimanche	2	2	1	J?nner	January	Enero	Janvier	1	1	2000	1	1	2000	1

Here is the Hierarchy with the original Names and not the abbreviations:

Name: [Date - Year]
- CalendarYear
- CalendarSemester
- SemesterDescription
- CalendarYear
- CalendarQuarter
- QuarterDescription
- CalendarSemester
- MonthNumberOfYear
- MonthDescription
- CalendarQuarter

Name: [Date - Week]
- CalendarYear
- WeekNumberOfYear
- WeekDescription
- CalendarYear

Thanks for your help!

Hans

|||

Hi Hans,

Based on the info for the [Date] dimension above, maybe something like this will work:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Volume KG (kum.)]
AS Sum(YTD([Date].[Date - Year].CurrentMember) *

YTD([Date].[Date - Week].CurrentMember),

[Measures].[Sales Volume KG])

|||

Hello Deepak,

Thanks a lot, this works. But please, can you tell me the background, why this crossjoin work? Is it, because if the current hierarchy is Weeks, the other hierarchy is "Null" and reverse?

Thanks
Hans

|||Hans, on the contrary, from what I can tell, when you select a week in [Date - Week], a year member is selected in [Date - Year], because the year attribute is shared between the 2 hierarchies. However, this works with YTD() calculations, because YTD(year member) returns that year member. You could confirm this by adding calculated measures to the query like [HierYearName]: [Date].[Date - Year].CurrentMember.Name and [HierWeekName]: [Date].[Date - Week].CurrentMember.Name.

Question on user-defined hierarchies

Hi, all,

Just suddenly dont see a big deal of creating user-defined hierarchies. As user-defined hierarchies are used for navigation purpose only. But you wont complete all possible hierarchies where users may want to browse? What I mean is users can alwyas feel it more flexible to browse the cube data by simply drag and drop any attributes into any way of levels they want to see. Does this make more sense?

Just a bit confused about the big deal of user-defined hierarchy and looking forward to hearing from you shortly for your guidance and advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

Well, it is pretty much a matter of taste whether you create user hierarchies or you let users use individual attribute hierarchies. However while user hierarchies are less flexible in terms of querying they are arguably more user-friendly, they make defining several types of common calculation much easier (for example if you have a user hierarchy on your Time dimension going Year->Quarter->Month it's very easy to work out what Year any particular Month is in; it's a bit more complex just using attribute hierarchies) and I've heard there are performance benefits to using user hierarchies although I've never noticed any significant different myself.

Chris

|||

Hi, Chris,

Thanks a lot for your advanced ideas.

With best regards,

Yours sincerely,

Question on Multiple Parent-Child Hierarchies

Hi all,

I have a business scenario which is shown in the below hierachy which has 4 levels. Typically the fact table will hold data at the Project manager Level i.e Level 4. I want to implement a solution in SSAS 2005 such that it can create multiple parent child hierarchy

Can you please provide me with a data model whcih can do this?

I am aware of Many to Many dimensions but not sure how it can be implemented in SSAS 2005 based on the person logging into the cube i.e it will have an Employee Table with LOGIN ID.

Scenario 1:

Mgr1 can execute projects under Business CIO1 and Business CIO2, i.e. cross hierarchy is also possible.

Scenario 2:

A CIO at level 2 i.e. CIO1 can have horizontal level access of CIO2 also even though members of CIO2 are not reporting to CIO1.

CEO

/ \

/ \

/ \

CIO1 CIO2

/ \

/ \

/ \

Business CIO1 Business CIO2

/ \

/ \

/ \

Mgr1 Mgr2

Please treat this as urgent as i need to implement this as soon as possible for the security design

Regards

Sai

I do not fully understand the diagram you present in your question. Are you saying you have a member who may have multiple parents?

Regarding parent-child hierarchies in general, you can have only 1 per dimension. This is enough for most users. If you have a fixed number of levels and data associated with just the leaf-level of the hierarchy, you can implement your solution as a standard hierarchy. If you truly have two parent-child hierarchies, my advice would be to implement two dimensions, each with a single parent-child hierarchy.

B.

|||

Hi Bryan,

Yes am saying about a member having multiple parents. Can you please let me know how this can be achieved and based on the login ID I need to filter the members and fact data. Do you have a solution or any data model which can support this.

Regards

Sai Krishna

|||

If a member is part of independent parent-child hiearchies, you would need to implement two dimensions. Each dimension would house one of the parent-child hierarchies.

If a member may have multiple parents and those parents may have multiple parents and there is no logical/topical separation between these relationships, I'm not sure of a way to proceed. This kind of relationship is referred to as a network relationship and I do not believe SSAS supports this.

B.

question on cube hierarchies

Hi, all experts here,

Thank you for your kind attention.

I am having a question on cube hierarchies creation. Is it necessary to create two different hierarchies for the hierarchies as below?

.................................................................................

Year-Quarter-Month-Day

Year-Month-Day

..................................................................................

Should we create two different user-defined hierarchies for the above hierarchies? As one of the hierarchy is actually included in another one. Therefore I am wondering if we need to create two different ones for them or just create the one with more levels.

Hope my question is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello Helen. I recommend to create the one with all levels.

Do not forget the attribute relations between the levels in the user hierarchy.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

Question on attribute relationships and hierarchies on SQL Server 2005 AS Cubes

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the attribute relationships and hierarchies in the cubes. It is a must to create the attribute relationships for each levels in any hierarchies in order to let the UDM to get the correct aggregates over different hierarchies? (e.g. within hierarchy H, levels are a-b-c-d. Do we have to create the attribute relationships for all the levels?, e.g. we have to create the attribute relationship between a and b, b and c, c and d? ). Hope my question is clear for your help and I am looking forward to hearing from you shortly.

Thanks a lot.

With best regards,

Yours sincerely,

Hello. Correct! Your cubes will benefit in performance if you add attribute relations. The way you have described this is correct but you will only need to create the in your natural or user hierarchies(the middle pane in the dimension editor).

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot.

With best regards,

Yours sincerely,