Skip to main content

OLAP Queries

You can add an OLAP data query in CDM against an Oracle EPM, Oracle Essbase, IBM® Cognos® TM1®, or Microsoft™ Analysis Services data sources.

You can use the Add OLAP Data Query wizard to add data queries using predefined dimension/member view, by selecting specific dimensions and members, or by using a multidimensional expression script. If you manually select dimensions and members, you can specify which dimensions represent the columns and rows of the data query.

You must have already created a data source that points to the OLAP database and specifies the database type. See Add an OLAP Data Source.

To create an OLAP data query against an Oracle EPM data source, you need to configure the EMPConfiguration key in the CDM.config file properly.

Add an OLAP Data Query Using Custom Data

You can add an OLAP data query that contains selected dimensions and members.

  1. In CDM, do either of the following to open the Data Queries tab in the work area:
    • Click the File tab, then navigate to Administration > External Data > Data Queries.
    • Click the Home tab, then in the Navigation Pane, click Administration and double-click Data Queries under External Data.
  2. Click Home > Add > OLAP Query, or right-click any existing query in the query list and click Add > OLAP Query. The Add OLAP Data Query wizard displays.
  3. Enter a query name in the Name field.
  4. Select an OLAP data source from the OLAP Data Source drop-down list.
  5. For a TM1® OLAP database, select None in the View drop-down list. The dimensions available display in the Page Options box. Dimensions with an associated member display in the <dimension>.<member> format. Dimensions that do not have an associated member are highlighted.
  6. When creating the query against a data source other than Oracle EPM, you can add or modify a member for a dimension before adding that dimension as a column or row. You can add only one member using this method.
    1. Double-click a dimension in the Page Options box.
    2. In the Select a Dimension Member window, expand the member hierarchy to locate the appropriate member. You can select Show Aliases at the bottom to display the alias names of the dimensions instead of the actual names.
    3. To search for a member, type the word to search in the search field.

      The search text can be:

      • Oracle Essbase - member name or the MEMBER_ALIAS property.
      • Cognos® TM1 - member name, or the MEMBER_CAPTION or MEMBER_ALIAS property.
      • Microsoft™ Analysis Service - member name or the MEMBER_CAPTION property.

      A list of members containing the search text opens. The search results are ordered hierarchically, as are found in the dimension tree. You are able to select a member node from the list. If selecting row or column members and you want to include children, you can right-click the node and choose the appropriate option: Children, IChildren, Descendants, IDescendants, and Bottom Level Descendants.

    4. Select a member and click OK.
  7. In the Page Options box of the Add OLAP Data Query wizard, use the arrow keys or drag dimensions to assign rows and columns in the query, and point of views if you selected an Oracle EPM data source for the query. You can assign only one dimension to the Rows/Columns box for an Oracle EPM-based query. You must select a default member in the Page Options box. Ensure that a leaf-level member is selected if the query is write-back enabled. A leaf-level member is a member of a hierarchy that has no children.
  8. If needed, add or modify the members that are associated with a dimension you selected for the query.
    1. Double-click a dimension in the Columns or Rows box, or a dimension in the Point of Views box if the query is based on an Oracle EPM data source.
    2. In the Edit Dimension Specifications window, expand the member hierarchy to locate the appropriate members. You can also search for a member using the method described earlier. You can only search by member alias for an Oracle EPM data source.
    3. Click Add MDX Script to add a multidimensional expression (MDX) script to the query, as needed.
    4. Use the tools in the Current member specifications box as you need.
      • Change the dimension/member relationship to child or descendant by clicking Change Relationship.
      • Change the order of the members with the Move up and Move down controls.

      Important: In CDM, the default parent-child order returned is parents before their children. In Financial Statement Reporting (FSR), the parent-child order returned is children before their parents. If you migrate your data from Financial Statement Reporting (FSR), version 6.6.1 to CDM, a special flag is set to retain the same order result as Financial Statement Reporting. If you would like to edit your query to change the order after migration, delete and replace the query and then relink your formulas.

    5. Specify the following dimension options if available:
      • Show Aliases - Display the alias names of the dimensions instead of the actual names. For IBM® Cognos® TM1 queries, this option might not update the user interface to display the alternative alias.
      • Join asymmetrically with next dimension - Specify whether to group the results that are based on the parent members.
    6. Click OK to return to the Add OLAP Data Query wizard.
  9. In the Additional Properties section, specify the query properties.
    1. In the Description field, enter an optional description for the OLAP data query.
    2. To specify how missing or zero values are displayed, select Display Missing Data As, then type the string or value to indicate how you want to display zero or missing data, for example, Zero, Nil, and 0.
    3. When you selected a data source other than Oracle EPM for the query, specify the following:
      1. To allow a user to edit the data in the query and write the data back to the OLAP cube, select Write-Back Enabled.
        Note:
        • If you have selected Display Missing Data As, you cannot write back to the OLAP database. The Display Missing Data As option is mutually exclusive with the Write-Back Enabled option.
        • To write data back to an OLAP data source, all dimension members in the data query must be leaf-level members. A leaf-level member is a member of a hierarchy that has no children.
      2. To suppress rows containing only null values, select Suppress Rows With Null Values.
      3. To suppress rows containing only 0 (zero) values, select Suppress Rows With Zero Values.
      4. To display row headers in the results table of an OLAP query, select Display Dimension Row Headers. The headers enable you use a pivot table in an Excel worksheet to analyze data that is inserted into the object. You do not have to do a manual update if the query result changes.
    4. When you selected an Oracle EPM data source for the query, specify the following:
      1. Set the data delimiter as Comma, Tab, or your preferred one. You cannot use alphabets, $, minus (-), or underscore (_) as the delimiter.
      2. Specify whether to export Smart List values as Labels or Names.
      3. Specify whether to include dense dynamic members.
      4. Specify whether to apply decimals. If Yes, specify the number of decimal places to include in numeric output (valid values are 1 to 16).
  10. Click Next to preview the OLAP query results.
  11. Click Next to associate reports with this query. You can select Make This Query Global to make the query available to all reports.
  12. Click Finish.

Add an OLAP Data Query Using Predefined Views

You can add an OLAP data query with predefined views in CDM against an IBM® Cognos® TM1® data source. The specific database views must have already been created in a third-party tool, such as IBM Cognos® TM1 Architect.

  1. With the Data Queries tab open in the work area in CDM, click Home > Add > OLAP Query, or right-click any existing query in the query list and click Add > OLAP Query.
  2. In the Add OLAP Data Query wizard, enter a query name in the Name field.
  3. Select an OLAP data source from the OLAP Data Source drop-down list.
  4. Select a predefined view from the View drop-down list. The data query will filter based on the Cognos TM1 view. If the view is modified outside on the Cognos TM1 cube level, the data query in CDM will be updated appropriately.
  5. In the Additional Properties section, specify properties of the query. You can select Show Attribute if you want to display dimension members according to the value the member has in the attribute instead of the member name.
  6. Click Next to preview the OLAP query results.
  7. Click Next to associate reports with this query. You can select Make This Query Global to make the query available to all reports.
  8. Click Finish.

Add an OLAP Data Query Using Multidimensional Expression Scripts

You can add an OLAP data query by using an multidimensional expression (MDX) script in CDM.

  1. With the Data Queries tab open in the work area in CDM, click Home > Add > OLAP Query, or right-click any existing query in the query list and click Add > OLAP Query.
  2. In the Add OLAP Data Query wizard, enter a query name in the Name field.
  3. Select an OLAP data source from the OLAP Data Source drop-down list.
  4. Select Use Script.
  5. Add a multidimensional expression (MDX) script to the query. You can use query variables in the script by typing them using the appropriate syntax.

    For example, {CurYear} can be created to represent the year of the reporting period. Data from this query applies a filter based on the year to ensure that data is updated based on different periods of the reporting cycle.

    You can use only Select type scripts, for example:

    Select {[Time].[Quarter 1]} on Rows, {[Year].[Planning Years]} on Columns from [ABCDemo] where ([Scenario].[Actual], [All Products].[No Product], [Accounts].[Balance Sheet], [Office Products Inc].[No Company] )

    Dimension member selection can be done on rows and columns. Dimension member selections after the where keyword are the page options.

  6. In the Additional Properties section, specify properties of the query. You can select Show Attribute if you want to display dimension members according to the value the member has in the attribute instead of the member name.
  7. Click Next to preview the OLAP query results.
  8. Click Next to associate reports with this query. You can select Make This Query Global to make the query available to all reports.
  9. Click Finish.

Was this article helpful?

We're sorry to hear that.