Time Intelligence Wizard script shortcomings
Basically, the script generated by the Time Intelligence Wizard only works for one level of a hierarchy.I would like to know the best way to correct this. In my case several time dimensions are present - this means a short and simple answer is preferable as many scripts must be updated.
I will list a couple scenarios using AdventureWorks and their proposed solution below, I welcome comments, especially how I could combine the multiple scripts in the solution for scenario 2:
Scenario 1 - Year to Date
The BIDS generated script is below:
Create Member CurrentCube.[Ship Date].[Fiscal Ship Date Calculations].
[Year to Date] AS "NA" ;
Scope(
{
[Measures].[Internet Sales Amount]
}
);
( [Ship Date].[Fiscal Ship Date Calculations].[Year to Date],
[Ship Date].[Fiscal Year].[Fiscal Year].Members ) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Year],
[Ship Date].[Fiscal].CurrentMember
)
) ;
End Scope ;
This only worked at the year level, a solution is below but it only works because year is the top level of the hierarchy.
Create Member CurrentCube.[Ship Date].[Fiscal Ship Date Calculations].
[Year to Date] AS "NA" ;
Scope(
{
[Measures].[Internet Sales Amount]
}
);
( [Ship Date].[Fiscal Ship Date Calculations].[Year to Date],
[Ship Date].[Fiscal].Members ) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Year],
[Ship Date].[Fiscal].CurrentMember
)
) ;
End Scope ;
Scenario 2 - Quarter to Date
The BIDS generated code is very much like that for scenario 1.The solution demanded 3 scripts though as follows:
( [Ship Date].[Fiscal Ship Date Calculations].[Quarter to Date],
[Ship Date].[Fiscal].[Fiscal Quarter].Members) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Quarter],
[Ship Date].[Fiscal].CurrentMember
)) ;
( [Ship Date].[Fiscal Ship Date Calculations].[Quarter to Date],
[Ship Date].[Fiscal].[Date].Members) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Quarter],
[Ship Date].[Fiscal].CurrentMember
)) ;
( [Ship Date].[Fiscal Ship Date Calculations].[Quarter to Date],
[Ship Date].[Fiscal].[Month].Members) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Quarter],
[Ship Date].[Fiscal].CurrentMember
)
) ;
I had tried several ways to combine/simplify these scripts.
Failed Solution 1(ErrorThe Fiscal hierarchy is used more than once in the Crossjoin function.):
( [Ship Date].[Fiscal Ship Date Calculations].[Quarter to Date],
[Ship Date].[Fiscal].[Date].Members,
[Ship Date].[Fiscal].[Month].Members,
[Ship Date].[Fiscal].[Fiscal Quarter].Members) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Quarter],
[Ship Date].[Fiscal].CurrentMember
)) ;
Failed Solution 2(ErrorAn arbitrary shape of the sets is not allowed in the current context.):
( [Ship Date].[Fiscal Ship Date Calculations].[Quarter to Date],
Descendants([Ship Date].[Fiscal].[Fiscal Quarter].Members, , SELF_AND_AFTER)) =
Aggregate(
{ [Ship Date].[Fiscal Ship Date Calculations].DefaultMember } *
PeriodsToDate(
[Ship Date].[Fiscal].[Fiscal Quarter],
[Ship Date].[Fiscal].CurrentMember
)) ;
Can the three scripts be combined?What is the best solution?I want to develop some fairly generic scripts that can be placed at the end of the calculations of a cube to implement many calculated measures that were implemented utilizing a shell dimension in AS2000.I desire to make these scripts as simple as possible.Any help is appreciated.
Thanks,
Dan
