Connect to OLAP data and apply a formula

1. Overview

This walkthrough shows you how to connect to OLAP data (SQL Server Analysis Services), view the data on a dashboard, and apply formulas to data.

Related video: Working with Formulas

2. Connecting to OLAP data

2.1. Create a new data connector (SSAS)

From the main menu, click New, and then click Data Connector to open the the New data connector dialog. This is a scrollable dialog with a number of fields to set depending on the type of data provider chosen.

Click in the File name box to open the Save file dialog. Type in a name for your data connector, and then select the Data Connectors folder under the current project.

Returning to the New data connector dialog, set the data provider to Microsoft SQL Server Analysis Services.

Enter a Server Name and select the Database name.

Data connector settings for SSAS
Data connector settings for SSAS

Optionally, click Test connection to verify the settings you entered.

Click Submit (checkmark) at the bottom to check in the data connector and perform a discovery operation in the background.

2.2. Create a new dashboard and add a measure

From the main menu, click New, click Dashboard, and then select the Blank template option.

Go to the DATA Explorer, locate the SSAS data connector you just created, and expand it to see a list of available native OLAP cubes.

Expand a cube to see a list of available measures and dimensions.

Find the Internet Average Sales Amount measure and drag it onto the dashboard canvas. A table visualization is automatically created, displaying a single measure value because no hierarchies have been added yet.

Drag measure to canvas
Drag measure to canvas

2.3. Add hierarchies

Next, go to the EXPLORE window, expand the Dimensions folder under your native OLAP cube, and find the Date.Calendar hierarchy.

Right-click over the table visualization on the dashboard canvas. From the menu, select Data Binding Panel. Drag the Date.Calendar hierarchy and drop it onto the ROWS field of the panel.

The table visualization automatically updates to show expandable date members along its rows axis.

Drag hierarchy to ROWS
Drag hierarchy to ROWS

Similarly, drag the Customer Geography hierarchy onto the COLUMNS field of the Data Binding panel.

The table visualization updates to additionally show expandable country members along its columns axis.

Drag hierarchy to COLUMNS
Drag hierarchy to COLUMNS

2.4. Switch to View mode

Resize the table visualization a bit larger and then click View from the toolbar so you can start interacting with the dashboard.

For example, click the triangle in the CY 2003 cell to see calendar half year members. Click the triangle in the H2 CY 2003 cell to see calendar quarter members.

Expand hierarchy members in View mode
Expand hierarchy members in View mode

2.5. Re-visualize as a bar chart

While in View mode, right-click over the table visualization, click Re-Visualize, and then click Bar.

The table changes to a bar chart that displays calendar year (date) members along the X axis, and Internet Average Sales Amount measure values on the Y axis. The chart also shows multiple series, where each series (bars of the same color) corresponds to a specific Customer Geography member (e.g. country). You can see this if you hover over a bar to see its corresponding information in a tooltip.

Re-visualize as bar chart
Re-visualize as bar chart

2.6. Drill down on the date hierarchy

Right-click over a CY 2003 bar on the chart.

Drill down
Drill down

From the menu, click Drill Down, and then select Date.Calendar as the hierarchy to drill down on.

Drill down on date hierarchy
Drill down on date hierarchy

The chart now displays date members along the X axis that are one hierarchy level down and which correspond to the CY 2003 member. You can do a right-click now and Drill Up, or switch back to Edit mode. If you switch back to Edit mode, you'll see that the chart remains in the same state it was in before you exited View mode (e.g. in the drilled down state).

After drill down
After drill down

3. Using formulas

In Dundas BI, you apply formulas by writing small script-like expressions, much like you would in a desktop spreadsheet program. A formula expression can be as simple as a constant number value, or involve a formula function call.

3.1. Create another dashboard

For this part of the walkthrough, create a second dashboard.

Go to the EXPLORE window and drag the Internet Sales Amount measure to the dashboard canvas as before. A table visualization is added to the dashboard, showing a single measure value.

Right-click over the table and select Data Binding Panel.

Drag the Date.Calendar hierarchy to the ROWS field of the Data Binding panel. The table visualization is updated with calendar year members along its rows axis.

Select the table visualization, go to the toolbar, click Re-Visualize, and then click Bar from the dropdown menu. The table visualization changes to a bar chart.

Bar chart
Bar chart

3.2. Add a formula to the bar chart

To begin applying a formula to the bar chart, right-click (or long-tap) over the chart and click Data Tools. You can also access Data Tools from the Dashboard Designer toolbar.

Select Data Tools
Select Data Tools

Then click Add Formula.

Select Add Formula
Select Add Formula

Below the toolbar, you'll see a Formula bar appear. This lets you type in a formula expression such as a simple math function and specify its arguments.

For example, to apply a formula that performs an average operation on the bar chart, go to the Formula toolbar and type the letter A.

From the auto-complete menu, select the AVG function.

Select AVG function
Select AVG function

AVG(

If you hover over the function entry, you'll see a tooltip describing the function and its required arguments.

Specifically, the AVG function takes one argument which is a set of data values. To indicate that you want to compute the average of the measure values displayed on the existing bar chart, click any data point on the chart, or type the dollar sign character ($) in the formula toolbar. This opens a menu that lets you choose an applicable argument for the function. For example, select [Measures].[Internet Sales Amount].

Select [Measures].[Internet Sales Amount]
Select [Measures].[Internet Sales Amount]

Make sure there is a closing dollar sign ($) followed by a closing bracket. The entire formula entry now reads:

AVG($[Measures].[Internet Sales Amount]$)

Click the Apply button in the Formula toolbar. If the formula syntax is correct, a checkmark is displayed in the toolbar, and the bar chart is updated to show the corresponding formula series.

Bar chart with formula applied
Bar chart with formula applied

Tip
A formula expression can be just a constant number value which you type in the formula toolbar.

3.3. Re-visualize the formula series

Next, right-click over one of the formula bars on the chart.

Re-visualize formula result
Re-visualize formula result

From the menu, click Re-Visualize, and then click Line. The formula series is now displayed as a horizontal line on the bar chart.

Re-visualize formula result as line chart
Re-visualize formula result as line chart

If you right-click over the chart and click Data Binding Panel, you'll see that the formula result has been added as a second measure which is now part of this metric set. You can remove the formula result if desired by clicking its delete icon on the right.

Formula result added as a second measure
Formula result added as a second measure

3.4. Adding a formula from the Data Binding Panel

You saw earlier how to add a formula via Data Tools. If you have the Data Binding Panel open, you can also add a formula by clicking the click to add link in the MEASURES section.

Click to add a measure
Click to add a measure

You'll see a list of available measures to add to this metric set.

Scroll all the way down to the Calculated Elements section and click <Formula>. The formula bar will appear below the main toolbar as before.

Click <Formula>
Click <Formula>

This option is also available in the Metric Set Designer panel.

3.5. Adding a formula as a new visualization

The previous example showed how to add a formula as a second measure/series on an existing chart (metric set).

But you can also apply a formula on an existing visualization and have the result displayed in a new visualization which is added to the dashboard. This option will leave your original visualization (and metric set) unchanged.

As an example, first create a new dashboard as before. It should have a bar chart (e.g. chart1) that displays an Internet Sales Amount measure against a Date.Calendar hierarchy. Go to the toolbar, click Data Visualization, and then scroll the dropdown menu until you find the Formula Visualization item.

Formula Visualization
Formula Visualization

Click Formula Visualization to see the Formula toolbar. Go to this toolbar and click inside the text box.

Type the letter A. From the autocomplete menu, select the AVG math function.

Now you must enter the input argument for the AVG function. It is not sufficient to specify just the name of the chart as input to the function. You must use "dot" notation to identify the specific metric set displayed by the chart, and also the specific measure from the metric set. The easiest way to do this is click on a data point on the chart. This will set the argument automatically for you. Or, you can type it yourself as follows:

Type a dollar sign character ($). From the menu, select chart1.

Type a period character (.) and then from the menu, select MetricSet1.

Type a period character (.) and then from the menu, select [Measures].[Internet Sales Amount].

Using dot notation for auto-complete
Using dot notation for auto-complete

The formula entry looks like this so far:

AVG($chart1.MetricSet1.[Measures].[Internet Sales Amount]$)

Click Apply. The formula result is displayed as a new table visualization (and metric set) which is added to your dashboard. The underlying metric set is sometimes referred to as a formula metric set.

New visualization is added to dashboard
New visualization is added to dashboard

4. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri