Set up date mapping on a native OLAP cube


1. Overview

With Dundas BI, you can connect to an OLAP cube and right away begin dragging measures and hierarchies from the native cube to your dashboard canvas. Switch to View mode and you can drill down or up the native hierarchies. However, there are several other functions you won't be able to perform on your OLAP data until you set up a date mapping on your native OLAP cube.

A date mapping connects a hierarchy from your native OLAP cube to a Dundas BI time hierarchy format. Once you have a date mapping in place, you'll be able to:

  • Use calendar and other types of hierarchy filters in Dundas BI on your OLAP data.
  • Use a datetime scale on a chart axis instead of a categorical scale when displaying OLAP data.
  • Apply forecasting formulas to OLAP data.

Date mapping essentially allows you to prepare OLAP data so that SSBI and other users can work with data seamlessly in Dundas BI without having to be concerned about the type of data source, such as OLAP versus relational.

Related video: Introduction to OLAP Date Mapping

2. Setting up date mapping

2.1. Connect to an OLAP data source

In order to set up date mapping, you need to connect to an OLAP data source which gives you access to a native OLAP cube. See the first part of Connect to OLAP data and apply a formula for the steps.

2.2. Set up date mapping on your native cube

Next, go to Explore to find your OLAP data connector and expand it to see your native cube.

Right-click over the cube and click Set Up Date Mappings from the menu.

Click Set Up Date Mappings
Click Set Up Date Mappings

The Date Mappings dialog is displayed.

2.3. Select a native hierarchy

The Date Mappings dialog shows you the available dimensions from your native cube.

Expand the treeview items and select a hierarchy for date mapping.

Note that if a hierarchy in the treeview already has a date mapping, it will appear with a small checkmark icon beside it.

Select a hierarchy for adding a date mapping
Select a hierarchy for adding a date mapping

Once you've selected a hierarchy, click Submit. This opens the Define Date Mapping dialog.

3. Define the date mapping

The Define Date Mapping dialog is where you configure the details of the mapping.

Define Date Mapping
Define Date Mapping

3.1. Select a time dimension provider

The first step is to select a time dimension provider. This is the target calendar system you want to map to.

Dundas BI supports different types of calendar systems including: Gregorian, FiscalISO 8601 (week numbers), and Reporting (Marketing). The default is Gregorian.

Depending on the provider chosen, you can set additional properties which are similar to the options available when creating a new time dimension. For example, for Fiscal or Reporting providers, you'll likely want to set the the Fiscal Year Start Month.

Fiscal provider properties
Fiscal provider properties

3.2. Auto detect values

The Auto Detect Values option tells Dundas BI to automatically detect a start date for the mapping.

Uncheck this option if you want to set the start date manually yourself.

3.3. Automatically matched date mapping

Dundas BI will automatically attempt to map each OLAP hierarchy level with a corresponding time dimension hierarchy level. For example, in the figure below, the OLAP Calendar Semester level is matched to the Gregorian Half Year level.

But for some hierarchy levels, you will need to configure the mapping yourself. These levels have a red 'minus' icon beside them. For example, in the figure below, the OLAP Date level could not be matched automatically.

OLAP Date level needs to be matched manually
OLAP Date level needs to be matched manually

To configure the mapping manually, select the mapping with the red minus icon, and then select the target level to map to (using the dropdown list).

Map OLAP Date level to Gregorian Day level
Map OLAP Date level to Gregorian Day level

3.4. Configure Caption Formatting

Click Configure Caption Formatting to set up how level captions should be formatted for one or more cultures in the system. The options here are similar to the Formatting options available when you edit a time dimension.

In the Setup Caption Formatting dialog, select an OLAP hierarchy level (e.g., Date).

Enter a regular caption format such as MMM/d/yyyy for the Date level.

Enter a short caption format such as dd for the Date level.

Select the language culture or click Add new culture and enter the culture you want (e.g., en-US).

Configure caption formatting
Configure caption formatting

Repeat the above for other OLAP hierarchy levels.

3.5. Conversion

Expand the Conversion section to specify the OLAP hierarchy member property that needs to be converted, add date format strings, and choose the culture for the conversion.

Conversion settings
Conversion settings

The first step is to choose the Converted Member Property from these options:

  • Value
  • Name
  • Unique Name
  • Caption
  • Custom Property

Next, add one or more Format Strings for the conversion. You can use the Default Format Strings dropdown to select a pre-defined date format, and then click the plus sign button to add it to the list of format strings.

Alternatively, enter a Custom Format String and then click the plus sign button to add it to the list.

3.5.1. Using a custom property for conversion

The Custom Property option lets you specify a custom property for conversion such as the 'key' of the dimension.

For example, the following dialog from SQL Server Analysis Services shows there is a member property named KEY0 with a value 20071001 which indicates the corresponding date format string should be yyyyMMdd.

Member Properties dialog from SSAS
Member Properties dialog from SSAS

Returning to the Conversion section in Dundas BI, set the Custom Property to KEY0 and then add yyyyMMdd as a format string.

Specifying a custom property for conversion
Specifying a custom property for conversion

3.6. Excluded members

If your OLAP hierarchy has empty or unknown members, auto-detection of the start date may not work properly. In this case, use the Excluded Members section to exclude empty, unknown, or other members from the date mapping.

Expand the Excluded Members section, and then select the Exclude Unknown/Empty members checkbox to hide members with empty captions, for example.

To exclude specific members by name, type a unique name in the text box, and then click the Add (plus sign) button to add it to the exclusion list. Instead of a unique name, you can also add an MDX expression.

Excluded members
Excluded members

3.7. Preview data

Use the Preview data section to check the mapping/conversion for each OLAP hierarchy level.

Select the OLAP hierarchy level using the Level dropdown list. The table will be updated with the mappings of OLAP property values to target time values.

Preview data
Preview data

3.8. Submit

Finally, click Submit to create the date mapping.

Now if you go to the Date Mappings dialog and locate your OLAP hierarchy, you'll see that it has a checkmark icon, which indicates it has a date mapping.

Checkmark indicates it has a date mapping
Checkmark indicates it has a date mapping

4. Using a date mapping

Once you have a date mapping in place on an OLAP hierarchy, you can drag that hierarchy from the OLAP data connector (or cube perspective) to your dashboard and then add a Calendar filter control to operate on that hierarchy.

Calendar filtering on a date mapped OLAP hierarchy
Calendar filtering on a date mapped OLAP hierarchy

5. See also


Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri