Set up date mapping on a native OLAP cube

1. Overview

You can connect to an OLAP database, and right away begin dragging measures and hierarchies from its cubes to create metric sets, making use of its abilities to change levels, drill down, and more. There are additional functions that you won't be able to perform on your OLAP data until you set up a date mapping on your OLAP cube's time hierarchies.

A date mapping allows a time dimension hierarchy from your native OLAP cube to be interpreted as dates, allowing you to:

• Connect it to calendar and date filters, and optionally filter OLAP and non-OLAP data together.
• Display it along a date/time axis on a chart instead of a categorical axis.
• Customize the date formatting in visualizations.
• Add period over period comparisons.
• Apply forecasting formulas.

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

Find your OLAP data connector in the Data Connectors folder in the Explore window when you have another file open such as a dashboard or metric set.

Expand the data connector, right-click (or long-tap) the cube and select Set Date Mappings from the menu.

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 folders and select a hierarchy for date mapping.

(If a hierarchy already has a date mapping, it will appear with a small checkmark icon beside it.)

Once you've selected a hierarchy, click Submit (check mark). 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.

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, Fiscal, ISO 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 Fiscal Year Start Month.

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.

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

3.4. Configure Caption Formatting

Click Configure Caption Formatting if you want to customize how member captions should be formatted from what was set up in the cube. 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.

If you want to apply different format settings for certain cultures (languages), select the Culture from the dropdown if already listed, or click Add new culture and enter a language tag (e.g., en-US).

You can then select each culture available from the dropdown and set the Caption Format and Short Caption Format separately for each culture.

You can repeat the above for other OLAP hierarchy levels.

3.5. Conversion

Expand the Conversion section to customize the OLAP hierarchy member property that needs to be converted and add date format strings.

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

• Value
• Name
• Unique Name
• Caption
• Custom Property (described under the sub-section below)

Next, add one or more Format Strings for the conversion. These formats should match the values of the member property selected above.

You can use the Default Format Strings dropdown to select a pre-defined date format, and then click the Add (plus sign) button to add it to the list of format strings.

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

If the format strings should be used with a particular culture when parsing the member values into dates, select that Culture.

3.5.1. Using a custom property for conversion

The Custom Property option for the Converted Member Property setting lets you specify a different property such as the 'key' of the member.

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.

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

3.6. Excluded members

If your OLAP time 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.

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.

3.8. Submit

Finally, click Submit (check mark) to create the date mapping.

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

4. Using a date mapping

Once you have a date mapping in place on an OLAP time hierarchy, you can create a new metric set and make use of the new abilities.

For example, you can drag the hierarchy from the cube to your dashboard and then add a Calendar filter to operate on that hierarchy.

5. See also

• Video: Introduction to OLAP Date Mapping
• Connecting to SSAS
• Connect to OLAP data and apply a formula
• Understanding the Dundas BI data model
• Microsoft Docs: Standard Date and Time Format Strings
• Microsoft Docs: Custom Date and Time Format Strings
• Create a cube perspective
• Create a time dimension
• Adding filters
• Formatting text
• Add a period over period comparison