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 database and right away begin dragging measures and hierarchies from its cubes to create metric sets, and making use of its abilities right away to change levels, drill down, and more. There are additional functions Dundas BI provides for its time hierarchies 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 by Dundas BI. Once you have a date mapping on your OLAP time hierarchy, you'll be able 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
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 editing a file, or in the Open dialog for data connectors in the main menu.
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.
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 from the dropdown list.
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.
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:
- Unique Name
- 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.
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
- 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