Export (0) Print
Expand All

Create and process an OLAP cube

Dynamics AX 4.0

An OLAP query returns data from multiple tables in a database. Because the query returns data from multiple tables, the query is said to be multidimensional. The multidimensional nature of the query is typically represented as a cube--called an OLAP cube. OLAP cubes must be configured, transferred to the Microsoft SQL Server Analysis Services server, and processed before they can be used by client applications. Make sure that you have completed the OLAP setup processes described in Set up OLAP reporting before beginning the procedures in this topic. For an introduction to OLAP cubes and OLAP concepts, see Introduction to OLAP reporting.

This topic includes the following procedures that show how to:

  • Import Microsoft Dynamics AX standard cube definitions.

  • Create a new cube definition.

  • Create and process a cube instance.

ImportantImportant

The process of creating and editing cube definitions, as described in this topic, must be completed with the help of business decision makers in your organization or company. Business decision makers will tell you what kind of data that they want to process in an OLAP cube, which will determine how you create the cube. You might also need the help of a developer or partner to create new queries in Application Object Tree (AOT). Queries determine which data sources the cube should process. For more information about how to create queries in AOT, see the Microsoft Dynamics AX software development kit.


Microsoft Dynamics AX includes standard cube definitions, or cubes that have already been constructed. You can import and view standard cube definitions in the Microsoft Dynamics AX. Standard cube definitions, when they are processed, return data about your customer revenue, inventory transactions, ledger transactions, and sales margins (to name some). Some of the standard cube definitions might work for your organization without changes; that is, you can import the definitions, process them, and analyze the data to obtain meaningful results that will be of value to the business decision makers. However, other standard cube definitions require editing to make them suitable for your organization. If there are no standard cube definitions that meet the needs of your organization, you can create your own cube definitions, as described later in this topic.

To import standard cube definitions

  1. From a Microsoft Dynamics AX client, click > > > > .

  2. Click Import/Export and select .

  3. In the dialog box, enter the following path in the field:%systemdrive%:\Microsoft Dynamics AX client\data\OLAPSampleData.def

  4. Click OK to exit the dialog box.

If the import was successful, you will see several cube definitions listed in the form. Again, these cubes, when transferred and processed, will query the database and return report data that can be viewed in a PivotTable. If you import cube definitions from another source, you might need to construct an entity called a data warehouse, which Microsoft Dynamics AX does not require as long as you create cubes in Microsoft Dynamics AX or use the standard cube definitions.

If you have not created and processed a cube before now, you should see the procedure in this topic called "To Create a Cube Instance". By creating and processing a cube with a standard cube definition, you can learn about the process and quickly view an OLAP report in a PivotTable. As soon as you are familiar with the process, you will be ready to create your own cube definitions.

To create a new cube definition

  1. From a Microsoft Dynamics AX client, click > > > > .

  2. On the tab, press CTRL+N to add a new cube definition.

  3. Enter a name in the field. If you are using SQL Server Analysis Services 2000, cubes must be named using Latin characters. SQL Server Analysis Services 2000 cannot process cubes named with characters other than Latin characters.

  4. Select a query from the drop-down list. If you want to use a new query, you must first add the query to Application Object Tree (AOT). For more information, see the Microsoft Dynamics AX software development kit.

  5. Enter a description of this cube in the field.

  6. Select a module in the drop-down list. The cube definition and the module should correspond. For example, if you are creating a cube that processes sales data, select the module.

General tab

  1. Click the tab.

  2. Select a key in the drop-down list (optional).

  3. Select a key in the drop-down list (optional, but strongly recommended for securing access).

Measures tab

  1. Click the tab.

  2. Enter a name in the field.

  3. Select a field from the drop-down list.

  4. Select a calculation function for the measure in the box.

  5. Select a field in the box if the measure represents an amount that will be given in a foreign currency.

  6. To add measures, press CTRL+N.

Dimensions tab

  1. Click the tab.

  2. Enter a name in the field, for example, Customer or Product or Time.

  3. Select or in the drop-down list.

NoteNote

Most cubes use more than one dimension (view the standard cube definitions to see an example). To add dimensions, press CTRL+N.

The item selected on the tab determines the options that you see on the Levels tab. That is, levels options for dimensions differ from options for dimensions. You must add at least one level to each dimension to process the cube definition—this means that you must select one dimension on the tab and then click the tab to set levels options for that dimension. Next, you must return to the tab and select a new dimension until you have set level options for all the dimensions.


  1. To add a level for a dimension, do the following:

    1. Press CTRL+N to create a new level.

    2. Enter a name in the field. The name cannot begin with a number.

    3. Select a data source in the drop-down list.

    4. Select a field in the drop-down list.

    5. To add levels, press CTRL+N.

    6. To analyze a measure across an aggregate of all the dimension levels, select the box.

  2. To add a level for a dimension, do the following:

    1. Select a field in the drop-down list.

    2. Select or .

    3. In the list, press CTRL+N and select the appropriate level from the drop-down list.

    4. To add levels, press CTRL+N.

    5. To analyze a measure across an aggregate of all the dimension levels, select the box.

Calculated member tab

  1. Click the tab.

  2. Define a calculation to perform on a measure or dimension by doing the following:

    1. Click .

    2. Type the name of the calculated member in the field.

    3. Select or a specific dimension on which to perform the calculation in the box.

    4. Expand the tree and double-click a measure or dimension level to add it to the box.

    5. Expand the tree and double-click a function to add it to the box.

    6. Modify the expression, as needed, and then click OK.

ImportantImportant

If you see the following error in the Infolog, you created a level in the wrong dimension which caused an orphaned table: "Error 0x8004004F: A table was detected in the schema that is not joined to any other table." Verify all levels on the tab. You might need to delete levels to correct this problem.


Now that you have created a cube definition, you are ready to create a cube instance, transfer that instance to the Analysis Services server, and process the cube.

To create and process a cube instance

Before you can process a cube, you must copy it and move the copy to the Analysis Services Server. The copy of the cube is called a cube instance. Described another way, a cube instance is a representation of a cube definition that is stored on an OLAP server. You can have several instances of the same cube and can use these instances to make versions of cubes in multiple currencies or from multiple time periods.

ImportantImportant

If you modify and transfer an existing cube, all cube security settings (roles and permissions) are lost and must be recreated using SQL Server Analysis Services administration.


  1. From a Microsoft Dynamics AX client, click > > > .

  2. On the tab, click . Microsoft Dynamics AX creates instances for all cubes listed in the .

  3. On the tab, change the identification and parameters details as necessary.

NoteNote

Microsoft Dynamics AX generates a table that contains time information based on the and times. Each day in the period exists as a separate entry, which can make for a very large time table. For performance reasons, enter the precise time period for which you want report data.


  1. On the tab, select the cubes that you want to transfer to the Analysis Services server for processing, and then click .

NoteNote

If the name of your OLAP database contains a symbol, you might be unable to transfer the OLAP cube to the Analysis Services server. If the cube did not transfer, verify that the name of the OLAP database does not contain a symbol.


  1. In the Batch, enter the desired processing details and then click OK. The Infolog shows the transfer progress.

ImportantImportant

Consider enabling automatic batch processing for OLAP cube instances. If a cube instance is not processed regularly, business decision makers might be looking at outdated data.


  1. Select the cubes you want to process, and then click . The form appears. Verify the details and then click OK. The Infolog provides status about cube processing. If the operation is complete successfully, close the dialog to save changes. If the operation fails, review the details in the Infolog. If you need help troubleshooting the operation, see Checklist for troubleshooting OLAP reporting.

For more information about how to view cube data in a PivotTable report, see View OLAP reports.

Show:
© 2014 Microsoft