416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Virtual Cube Designer - Date Mapping

Hide navigation
RSS
Modified on Mon, 11 Nov 2013 03:10 PM Categorized as OLAP, Virtual Cubes
Click to return to: Documentation | Defining Virtual Cubes


Overview

Use the Date Mapping tab of the Virtual Cube Designer to define one or more date mappings, which map dimension hierarchies from your source cube to a date format that is similar to Dundas Dashboard time dimensions.

Tip: Watch the how-to video: Setup Date Mapping

Date mappings are necessary if you want to:

  • Use calendar-style parameter controls including date tokens (such as YTD or MTD) on a performance dashboard to filter OLAP-based KPIs/datasets.
  • Use calendar controls in a report and analytical dashboard.
  • Use a datetime scale for a chart instead of a categorical scale.
  • Use date offset parameters to compare the same measure over two different time periods.

The Date Mapping tab of the Virtual Cube Designer.

The Date Mapping tab of the Virtual Cube Designer.


Date mapping allows use of the calendar parameter control type on a performance dashboard.

Date mapping allows use of the calendar parameter control type
on a performance dashboard.


You can also use the calendar (range) parameter control type if your OLAP KPI filter is set to allow range selections. This lets you choose a start and end date.

You can also use the calendar (range) parameter control type if your OLAP KPI filter
is set to allow range selections. This lets you choose a start and end date.




Adding a date mapping

The following example shows how to create a date mapping for the Date.Calendar hierarchy from the AdventureWorks (DW Standard Edition) cube:

  1. In the Date Mapping tab, click the New Date Mapping button. The Editing Date Mapping wizard is displayed.
    Click New Date Mapping button.
  2. In the first step of the wizard, select a dimension from the source cube, then click Next.
    Select a dimension.
  3. In the second step of the wizard, select a hierarchy from the list, then click Next.
    Select a hierarchy.
  4. In the third step of the wizard:
    • Choose the Level type that matches each of the hierarchy levels from the source cube.
      Match level types.
    • Next, you must choose a start date for the mapping:
      • If you want to determine the start date automatically, click Setup to configure auto detection, and then select the Auto detect values check box. If the detection is successful, the Start date selection control will display the start date and become read-only.
        Auto Detection setup.
      • Auto-detection of the start date may not work if your OLAP dimension has empty or unknown members. In this case, click Members to exclude to exclude such members from the date mapping.
      • If you want to set the start date manually, uncheck the Auto detect values checkbox and determine the start date yourself based on your cube. By default, the wizard sets the start date to the current date, which will likely be incorrect. One way to determine the start date yourself is to use the Member caption value for the Date hierarchy level as shown below.
        Choose start date.
    • Select the First day of week.
    • Choose the Calendar type (e.g. Gregorian, ISO, or Fiscal). If the type is Fiscal, you have the option of using the Calendar year + 1 naming convention.
    • In the Preview area, select a level using the dropdown to preview the corresponding date mapping. Level preview lets you preview members with the corresponding mapping information so you can verify that the conversion works. The Preview grid displays a Date column which shows the mapped dates. The Sequence column shows numbers used for forecast formulas. There is also a conversion status column which uses a checkmark to indicate successful conversion. If conversion is unsuccessful, you will have the opportunity to set up the conversion for that specific member.
      Preview the date mapping.
  5. Click Finish. The Editing Date Mapping wizard closes and the new date mapping appears in the grid in the Date Mapping tab.
    A date mapping is added to the grid.
  6. Click Save in the toolbar to save your virtual cube definition.

Auto detection setup

The Auto Detection Setup dialog lets you choose the member property that will be converted, and configure the conversion process for that property:

  1. Select the Member property to be converted. You can choose among the following properties:
    • MemberValue
    • MemberCaption
    • MemberName
    • MemberUniqueName
  2. Enter a standard or custom date/time format string which will be used to parse the member property values. Click the arrow next to the New button to select a predefined format string, such as MMMM d, yyyy, and then click New.
  3. Select the corresponding culture for conversion.
  4. Select format styles for handling white space during the conversion. You can choose among the following styles:
    • AllowInnerWhite
    • AllowLeadingWhite
    • AllowTrailingWhite
    • AllowWhiteSpaces
  5. Click Convert to test the conversion. If the conversion is successful, you will see a checkmark beside the Convert button. (Note that this dialog is tall; you may need to set your browser window to full screen to see the Convert button.)
  6. Click OK to complete the setup.

Auto detection setup.

Auto detection setup.


Members to exclude

Auto-detection of the start date may not work if your OLAP dimension has empty or unknown members.

To exclude empty/unknown or other members from the date mapping:

  1. Click Members to exclude. The Exclude members dialog is displayed.
  2. Select the Hide Unknown/Empty checkbox to hide members with empty captions, for example.
  3. Click New to specify the unique name of a member to exclude. You can also enter an MDX expression.
  4. Click OK.

Members to exclude from the date mapping.

Members to exclude from the date mapping.


Using a date mapping

Once you have a date mapping defined for a dimension hierarchy, you can use it in a report as follows:

  1. Create a new report for your virtual cube.
  2. Drag a measure to the Measures axis.
  3. Drag the hierarchy, which you previously mapped to dates, to the Categorical axis.
  4. Right-click over the categorical dimension to display its context menu.
  5. From the context menu, you will see the Calendar option.

Calendar option is now available.

Calendar option is now available.


The Calendar menu option brings up the Edit Dates dialog, which presents calendar controls for filtering the data.

The Edit Dates dialog.

The Edit Dates dialog.


You will also be able to access this Calendar functionality when you view the report in an analytical dashboard.

Related topics


Click to return to: Documentation | Defining Virtual Cubes

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.