Set up date mapping on a native OLAP cube
- Setting up date mapping
- Define the date mapping
- Using a date mapping
- See also
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
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.
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.
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.
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 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 (using the dropdown list).
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).
Repeat the above for other OLAP hierarchy levels.
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.
The first step is to choose the Converted Member Property from these options:
- Unique Name
- 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.
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 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.
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.
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.
5. See also
- Video: Introduction to OLAP Date Mapping
- Create a cube perspective
- Understanding the Dundas BI Data Model
- Standard Date and Time Format Strings
- Custom Date and Time Format Strings
- Create a time dimension