Microsoft KB Archive/912136

Knowledge Base

You may experience problems after you run the Business Intelligence Wizard to add time calculations in SQL Server 2005 Analysis Services

Article ID: 912136

Article Last Modified on 4/5/2006

---

APPLIES TO

• Microsoft SQL Server 2005 Analysis Services

---

SYMPTOMS

After you run the Business Intelligence Wizard to add time calculations in Microsoft SQL Server 2005 Analysis Services, you may experience the following problems.

Problem 1

The parent hierarchy where you put the time calculations does not appear in the form view of the Calculations tab in Cube Designer.

Problem 2

The time calculations that the Business Intelligence Wizard adds are generated on only a narrow scope of the hierarchy instead of on the whole hierarchy.

CAUSE

Problem 1

This problem occurs because the code to create the calculated member contains a carriage return. This carriage return appears in the middle of the unique name of the calculated member. The Multidimensional Expressions (MDX) parser does not behave as expected in this scenario. However, this issue concerns only the user interface. The validity of the calculated member is not affected.

Note The Business Intelligence Wizard adds the calculated member to the cube when the Define time intelligence option is selected. Depending on the scenario, the Business Intelligence Wizard could add more than one calculated member.

Problem 2

This problem occurs because of an issue in the MDX code of the calculated member that performs the calculation. A scope is missing in the MDX code.

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089 How to obtain the latest service pack for SQL Server 2005

WORKAROUND

Note These workarounds are based on the problem scenarios that are described in the "More information" section.

Steps that apply to both problems

The following steps are required to work around both problems. Follow these steps first.

1. In SQL Server Business Intelligence Development Studio, open the Adventure Works DW Standard Edition sample project.
2. In Solution Explorer, expand Cubes, and then double-click Adventure Works.cube to show Cube Designer.
3. In Cube Designer, click the Calculations tab.
4. In the Script Organizer pane, click the [Year to Date] command.
5. On the toolbar at the top of the Calculations tab, click Script View to open the Calculation Expressions pane.

Steps that apply to problem 1

1. In the Calculation Expressions pane on the Calculations tab, notice the following lines of code.

   Create Member CurrentCube.[Date].[Fiscal Date Calculations].
   [Year to Date] AS "NA";

2. Delete the carriage return at the end of the first line of code. The code now appears as follows.

   Create Member CurrentCube.[Date].[Fiscal Date Calculations].[Year to Date] AS "NA" ;

3. Press CTRL+S to save the change.

Steps that apply to problem 2

1. In the Calculation Expressions pane on the Calculations tab, notice the following lines of code.

   Scope(
          {
            [Measures].[Internet 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
                )
     ) ;
   
   End Scope ;

2. Add an extra scope on members of the key attribute of the cube dimension where the time calculations are applied. Use the following format.

   [<SelectedCubeDimension>].[<SelectedCubeDimensionKeyAttribute>].Members

   Note <SelectedCubeDimension> is a placeholder for the cube dimension where the time calculations are applied. <SelectedCubeDimensionKeyAttribute> is a placeholder for the key attribute of that cube dimension.
   
   In this case, the extra line of code to be added will look like the following.

   [Date].[Date].Members

   The following example shows the complete lines of code after you have added the extra line of code.

   Scope(
          {
            [Measures].[Internet Sales Amount]
          }
   ); 
   
   ( [Date].[Fiscal Date Calculations].[Year to Date], 
   [Date].[Fiscal Year].[Fiscal Year].Members,
   [Date].[Date].Members ) = 
   
     Aggregate(
                { [Date].[Fiscal Date Calculations].DefaultMember } *
                PeriodsToDate(
                               [Date].[Fiscal].[Fiscal Year],
                               [Date].[Fiscal].CurrentMember
                )
     ) ;
   
   End Scope ;

3. Deploy the project to Microsoft SQL Server 2005.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft SQL Server 2005 Service Pack 1.

MORE INFORMATION

Steps to reproduce the problems

Steps that apply to both problems

The following steps are required to reproduce both problems. Follow these steps first.

1. In SQL Server Business Intelligence Development Studio, open the Adventure Works DW Standard Edition sample project. This sample project is located in the following folder:

   SQLInstallDrive:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Standard

   Note SQLInstallDrive is a placeholder for the drive where you installed SQL Server 2005.
2. In Solution Explorer, expand Cubes, right-click Adventure Works.cube, and then click Add Business Intelligence to start the Business Intelligence Wizard.
3. Click Next.
4. On the Choose Enhancement page, select Define time intelligence in the Available enhancements list, and then click Next.
5. On the Choose Target Hierarchy and Calculations page, select Date\Fiscal in the Use the following hierarchy to analyze time calculations list, and then select the Year to Date item in the Available time calculations list. Click Next.
6. On the Define Scope of Calculations page, select the Internet Sales Amount item in the Available measures list, and then click Next.
7. On the Completing the Wizard page, click Finish.
8. Deploy the project to SQL Server 2005.

Steps to reproduce problem 1

1. After the deployment, click the Calculations tab in Cube Designer.
2. In the Script Organizer pane, select the item that has [Year to Date] in the Command column.
3. In the right pane, expand Parent Properties.

Notice that the Parent hierarchy box is blank.

Steps to reproduce problem 2

1. After the deployment, open SQL Server Management Studio, and then connect to the instance of SQL Server 2005 Analysis Services.
2. In Object Explorer, expand Databases, and then click Adventure Works DW Standard Edition.
3. On the toolbar, click New Query to open a new query editor window.

4. Type the following query in the query editor, and then press F5 to run the query.

   SELECT
   [Measures].[Internet Sales Amount]
   *
   [Date].[Fiscal Date Calculations].ALLMEMBERS
   ON 0,
   [Date].[Fiscal].MEMBERS
   ON 1
   FROM [Adventure Works]

Notice that the [Year to Date] time calculation shows only the values of the year level of the target hierarchy. Levels under the year level contain the value of NA.

REFERENCES

For more information about how to install the Adventure Works DW sample databases, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

Keywords: kbtshoot kbqfe kbprb kbexpertiseadvanced kbsql2005as kbsql2005bi kbsql2005olapsp1fix kbsql2005sp1fix KB912136

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.