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.

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.

3.2. Auto detect values

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

Note

In some cases, Dundas BI will be unable to automatically detect the start date and show an error. In such a case, uncheck the Auto Detect Values option and manually specify a start date.

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.

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.

3.5. Conversion

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.

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.

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.