AS 2005 Business Intelligence Wizard - rerunning to add time calculations

In Analysis Services 2005, once you’ve used the Business Intelligence Wizard in the BI Development Studio to add time calculations to a cube, how do you come back later and add additional standard time calculations based on the named calculation that was already created in the cube’s data source view by the prior run of the Wizard?Kendal,

Assuming I understand you correctly, you want to add additional custom time calculations after running the Time Intelligence wizard...

The wizard creates a calculated column in your time dimension table within the DSV. It then adds a new attribute hierarchy to your time dimension based on this new attribute. This serves as an "holding point" for the calculations. It then creates the calculations you selected in the calculations script for the cube, using the following steps:

- it starts the calcuations by adding new members to the attribute hierarchy with a default value of "NA"
- the script then uses a "scope" statement to scope the calculations to the measures you selected in the wizard
- each of the calculations you selected by the wizard are then implemented by using an MDX expression to define the values of the new calculated member across the time hierarchy the calculations apply to
- finally, an "end scope" statement closes things out

If you review the calculation script for the cube, you should see this at the end of the script after running the Time Intelligence wizard.

To add new custom calculations to the same attribute hierarchy created by the wizard, you can just add new calculated members to the hierarchy and add the custom MDX expressions needed. To add a new member, simply add a new "Create Member" statement before the "scope" statement, using the same syntax and construction as the ones added by the wizard. Then, add a new calculation after the "scope" statement that specifies the MDX expression that should be used to calculate values for the new calculated member. Use a syntax similar to that used for the existing calculations.

You should note that any calculated members that you add in this manner will be restricted to the same scope as the ones added by the wizard -- meaning the calculated member will only return values for the same list of members selected in the wizard. You can use variations of this technique with your own scope statements to change this.

Hope this helps and is understandable :-)

Dave Fackler
|||It does help. I had been thinking of the wizard as more of a development tool that would allow me to iteratively refine my cube. However, at this point in time, it serves more to codify best practices and generate examples of how to implement certain features in the context of the cube.

As is, it certainly is a big benefit.

Thanks for the clarification.|||

Hi Dave

I have tried to 'add time intelligence' but when I browse the result I only get something in 'current time'. In 'year to date' I only get the value 'N/A'

On my calculations I have nothing in my 'parent hierarchies' . On the parent hierarchies i got an error

the specified hierarchy [Time].[Year - Quarter - Month - Date Time Calculations] does not exist int the cube.

Scope(

{

[Measures].[Invoice Net Sales Amt]

}

)

( [Time].[Year - Quarter - Month - Date Time Calculations].[Quarter to Date],

[Time].[Quarter].[Quarter].Members ) =

Aggregate(

{ [Time].[Year - Quarter - Month - Date Time Calculations].DefaultMember } *

PeriodsToDate(

[Time].[Year - Quarter - Month - Date].[QuarterName],

[Time].[Year - Quarter - Month - Date].CurrentMember

)

)

End Scope

|||

Some of the calculations produced by the Time Intelligence Wizard, including the Year to Date ones, don't work - see
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry

Hopefully this will be fixed in SP1.

Chris

|||

I'm running SP1 on Standard Edition. I'm also using the code (below) that's recommended as a workaround but still get the same problem. NAs are displayed when I try to browse the data for this measure / calculation combo. All I've done is put the code into my calculations script on Adventure Works. Current Date (which I assume is a hidden calculation) works.

Is there any other issue that could be causing this or is there something I've missed? Any help on this would be much appreciated.

Create Member

CurrentCube.[Date].[Fiscal Date Calculations].[Year to Date]

As "NA" ;

Scope(

{

[Measures].[Internet Sales Amount],

[Measures].[Reseller Sales Amount]

}

);

( [Date].[Fiscal Date Calculations].[Year to Date],

[Date].[Fiscal Year].[Fiscal Year].Members ) =

Aggregate(

{ [Date].[Fiscal Date Calculations].DefaultMember } *

PeriodsToDate(

[Date].[Fiscal].[Fiscal Year],

[Date].[Fiscal].CurrentMember

)

) ;