Error in calculating Twelve Months to date using Time Intelligence

Hi,

I have used Time intelligence of AS2005 to calculate YTD and Twelve Months to date. YTD works pretty well, but there is an error in parsing the code that AS2005 generated when it created time intelligence for calculating Twelve Months to date.

The time dimension contains 2 hierarchies, Full Year (QTR- Month) and YTD hierarchy(Year - Qtr - Month) . Here is the code it generated for 12 months to date.

Scope( { [Measures].[Quote Count] } )

( [Total Year FY].[YTD Hierarchy Total Year FY Calculations 2].[Twelve Months to Date],

[Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members) =

Aggregate(

{ [Total Year FY].[YTD Hierarchy Total Year FY Calculations 2].DefaultMember } *

{ ParallelPeriod(

[Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName], 11,

[Total Year FY].[YTD Hierarchy].CurrentMember

) : [Total Year FY].[YTD Hierarchy].CurrentMember } )

End Scope

This is the error message

MdxScript(ApplicantHORT) (130, 8) The level '[YTD Hierarchy]' object was not found in the cube when the string, [Total Year FY].[YTD Hierarchy].[YTD Hierarchy], was parsed.

Any ideas how to fix this problem?

Thanks

Srinivas

Hi Srinivas,

If you refer to this SQL Server Magazine article, the MDX Script generated for Time Intelligence conforms to certain patterns; so I suspect that your [YTD Hierarchy] doesn't quite fit:

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=46157

>>
Analysis Services 2005 Brings You Automated Time Intelligence
The Business Intelligence Wizard makes time analysis a snap
Mosha Pasumansky,
Robert Zare
InstantDoc #46157
June 2005

...

As Listing 3 shows, best practice uses attribute hierarchies on the left side of the assignment. In Analysis Services 2005, the cube space is defined entirely by attributes, so the space to which calculations apply is best described by the attribute hierarchies contained therein. Conversely, specifying the scope for user-defined hierarchies can result in inadvertently over-restricting the calculation scope. The Year to Date calculation illustrates this best practice through the use of the Fiscal Year attribute hierarchy, which excludes the All member (where the calculation doesn't apply). This approach includes all other attributes in the hierarchy, regardless of whether or not they are All members, which is precisely what we want.

Next, note that the right side of the assignment uses multilevel user hierarchies (rather than attribute hierarchies), which lets you use hierarchy-friendly MDX functions such as ParallelPeriod and PeriodsToDate.

...

( [Date].[Fiscal Date Calculations].[Twelve Month Moving Average],
[Date].[Month Name].[Month Name].Members ) =
Avg(
{
ParallelPeriod(
[Date].[Fiscal].[Month],
11,
[Date].[Fiscal].CurrentMember
) : [Date].[Fiscal].CurrentMember
},
[Date].[Fiscal Date Calculations].DefaultMember
) ;
>>

If you compare the above listing with your MDX Script, then [Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members should be members of the Month level of a Month attribute hierarchy, which should be incorporated in your user-defined [YTD Hierarchy]. Similarly, [Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName] should be the Month level of [YTD Hierarchy].

|||

Thanks Deepak,

It worked when i changed

[Total Year FY].[YTD Hierarchy].[YTD Hierarchy].Members to

[Total Year FY].[Months].[Months].Members and

[Total Year FY].[YTD Hierarchy].[?MonthLevelUniqueName] to

[Total Year FY].[YTD Hierarchy].[Months]

your post was really helpful.