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 - Definition

Hide navigation
RSS
Modified on Tue, 21 May 2013 04:33 PM Categorized as OLAP, Virtual Cubes
Click to return to: Documentation | Defining Virtual Cubes


Overview

Use the Definition tab of the Virtual Cube Designer to select the elements (⪚ measures, KPIs, dimensions, named sets) from the source cube that you want to use to build analytical dashboards or OLAP-based KPIs/datasets.

The Definition tab of the Virtual Cube Designer.

The Definition tab of the Virtual Cube Designer.


Filtering cube elements

Use the Filter textbox above the treeview to filter the list of cube elements.

Selecting cube elements

Use the treeview on the left to select the cube elements that you want, based on the following rules:

  • Use a single checkmark to select an item and all of its children in the hierarchy.
  • Use a double checkmark to select an item and a subset of its children. In this case, a child item without any checkmarks will be excluded from the virtual cube definition.

Tip: Click the buttons in the top left corner to select, or de-select, all elements in the treeview.

Selecting cube elements.

Selecting cube elements.


Tip: If your cube has a lot of measures and dimensions, use the Filter textbox to narrow the list of elements shown in the treeview.

Note: If the top-level Measures folder is selected, and new measures are subsequently added to the SSAS cube, the new measures will be automatically included in your virtual cube definition the next time you edit the virtual cube. To avoid this default behavior, de-select the top-level Measures folder and explicitly select the measure groups and measures that you want to include in your virtual cube definition.

Manual subcube MDX query

Although the treeview lets you select elements such as dimensions and named sets, it doesn't let you navigate deeper than that in order to choose specific dimension members.

If you want to choose specific members, enter a manual subcube MDX query that returns the desired cellset of data from your source cube. This is useful for restricting the virtual cube to just the subset of data that you are interested in.

Note: A subcube MDX query (if specified) is always applied first, followed by your selections in the treeview. Additionally, due to SSAS restrictions on subcube creation, the MDX query cannot include any calculated measures. If you want to restrict your virtual cube to a specific calculated measure, simply omit it from the MDX query and select only that calculated measure in the treeview.

To use a manual subcube MDX query:

  1. Select the Enable manual subcube query option.
  2. Enter your MDX query in the box provided.
  3. Click Execute and Preview to see the resulting cellset displayed in a grid.

Manual subcube MDX query.

Manual subcube MDX query.


Using a query parameter

In your subcube MDX query, you have the option of embedding query parameters which you can later connect to dashboard parameters (and parameter UI controls) during the dashboard design stage.

The following example shows how to add a query parameter of type Dimension that filters the data based on the Country Name level of a Customer dimension:

  1. Select the Enable manual subcube query option.
  2. Enter your MDX query in the box provided (but without any query parameters for now).
    Enter MDX query.
  3. Click the New Parameter button to define a query parameter:
    • In the first step of the Editing query parameter wizard:
      • Set the Parameter type to Dimension.
      • Enter a name for your parameter.
        Set parameter type.
      • Click Next.
    • In the second step of the wizard:
      • From the grid, select the desired dimension from your source cube.
        Choose dimension.
      • Click Next.
    • In the third step of the wizard:
      • From the grid, select the desired hierarchy/level.
        Choose hierarchy/level.
      • Click Next.
    • In the fourth step of the wizard, choose the default member(s) for your parameter:
      • Select the Allow multiple values selection option if you want to allow multiple default members. If you choose multiple selection, make sure you use the strtoset function in the WHERE clause. Otherwise, use the strtomember function in the WHERE clause for single selection.
      • Use the Filter textbox to search for or narrow the list of members. You have the option of searching the hierarchy or just the current level.
      • Select the default member(s) in the list.
        Choose default members.
      • Click Finish. The wizard closes and the new parameter appears in the Query Parameters grid.
        Query Parameters grid.
  4. Now you can insert the parameter into your query as follows:
    • Click inside the query text box and position the cursor where you want the parameter token to appear.
    • Locate your query parameter in the Query Parameters grid and click its plus sign button in the Actions grid column. This will insert the parameter token into the query text box (where you positioned the cursor).
      Insert parameter.
  5. Click Execute and Preview to see the resulting cellset displayed in a grid.
    Preview cellset.

Note: If you are connecting to SAP, note that stroxxx functions won't work with parameters due to a bug on the SAP side. As a workaround, use a query for a dimension or measure parameter that looks like this:

crossjoin([dim].[hier].[level].[mem], {DD:MyFilter1})

Using a datetime query parameter

The following figure shows an example of using a DateTime query parameter in a subcube MDX query. The example is based on the Adventure Works (DW Standard Edition) cube.

Using a DateTime query parameter.

Using a DateTime query parameter.


Here is the actual text of the query (with the DD:DateFilter parameter already inserted):

SELECT filter([Date].[Fiscal].[Fiscal Year].ALLMEMBERS,instr([Date].[Fiscal].currentmember.unique_name,cstr(year()))) on 0 FROM [Adventure Works]

SSAS actions

SSAS actions are operations that users perform on a SSAS cube, or portion of a cube. Actions are defined on the SSAS side, and appear in the Definition tab of the Virtual Cube Designer. Dundas Dashboard supports the following types of SSAS actions, which can be enabled for use on an analytical dashboard:

  • Drill-through
  • URL
  • Report
  • Row Set

Enabling SSAS actions.

Enabling SSAS actions.


From the Definition tab, select the actions you want to be available to users in a report. While viewing the report in an analytical dashboard, users can then right-click over a selected item and invoke an available action via the context menu:

Accessing actions from the context menu.

Accessing actions from the context menu.


Displaying results of a drill-through action on the selected item.

Displaying results of a drill-through action on the selected item.


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.