IBM DB2 Warehouse Cubing Services known issues: Calculated members are invalid at run time

Technote (troubleshooting)


There might be an instance where you define a calculated member that is originally valid, but becomes not valid at run time.


The following scenario describes an instance where calculated members become invalid:

    1. Import the XMLR3 metadata of CVSample into project explorer.

    2. Drill-down to the Fiscal Time hierarchy under the Time dimension in the Sales Model.

    3. Right-click the hierarchy and add a new MDX Calculated Member.

    4. In the Data frame, click the Fiscal Quarter level.

    5. Click the + and click on the Fiscal Quarter level again.

    6. Add Qtr1 and Qtr 2 to the brackets. For example:

    [Fiscal Time].[Fiscal Quarter].[Qtr 1] + [Fiscal Time].[Fiscal Quarter].[Qtr 2]

    7. Click OK to create the query.

    8. Navigate to the MDX Calculated Member folder under fiscal time.

    9. Rename the calculated member to [Half Year].

    10. In the Properties tab, add a parent value of [Time].[2004].

    11. Create a new cube for the Sales Model. Name it CalcMbrTranslateDefect.

    12. Add the cube measure Profit to the new cube.

    13. Add cube dimensions (Market, Product, Time).

    14. For the time cube dimension, add the cube hierarchy Fiscal Time.
      Note: If you analyze the model you will see no errors.

    15. Deploy this metadata to the Database Explorer to a CVSample database.

    16. From the Database Explorer, navigate to Cube Models > Sales Model > Time >Time > Hierarchies. You can now see the Fiscal Time, Fiscal Time (CalcMbrTranslateDefect), and Fiscal Time (General Sales) hierarchies.

Important: Each of these hierarchies include the MDX calculated member [Half Year] and use the expression [Fiscal Time].[Fiscal Quarter].[Qtr 1] + [Fiscal Time].[Fiscal Quarter].[Qtr 2] .The expression here is not valid for each of these hierachies.

Diagnosing the problem

You can determine that this expressions is not valid by examing the calculated members. The Fiscal Time (CalcMbrTranslateDefect) level should be [Fiscal Quarter (CalcMbrTranlateDefect)] and not [Fiscal Quarter].

If you deploy this cube to a cube server, the cube will fail to start because of the invalid calcaulated member definition.

In this scenario, the General Sales cube will also fail if you deploy it to the cube server because it is also based on the Fiscal Time hierarchy.

Resolving the problem

To correct this issue, complete the following steps:

1. Export the metadata out of the database explorer.
2. Re-import the metadata into project explorer.
3. Modify the expression in each of the cubes that use the modified hierarchy.
4. Redeploy the the _________

Document information

More support for:

DB2 Cube Views

Software version:


Operating system(s):

AIX, Linux, Solaris, Windows

Reference #:


Modified date:


Translate my page

Content navigation