Connect to OLAP data and apply a formula

Contents[Hide]

1. Overview

This walkthrough shows you how to connect to OLAP data such as an Analysis Services database, view the data on a dashboard, and apply formulas to data. The dashboard editor is used here, but you can follow similar steps using the metric set editor.

You can work with data from an OLAP database the same way as with data from other data sources, except that cubes have already been completely prepared in the database. These existing cubes are often used directly instead of Dundas BI data cubes, and they contain measures and dimensions or hierarchies rather than tables.

Related video: Formulas

2. Connecting to OLAP data

2.1. Create a new data connector

Create a data connector from the main menu or one of the other ways mentioned in the article Connect to data and view it on a dashboard.

Click in the Name box to type in a file name for your data connector, and set the Data Provider to the type of data source. We chose Microsoft SQL Server Analysis Services in the image below as an example, which is also used for connecting to Azure Analysis Services.

Fill in the fields that appear below, such as Server Name and Database Name, and change any of the other options as needed to successfully access your data source. The example used in this article is the Adventure Works sample database for Analysis Services.

Data connector settings for SSAS
Data connector settings for SSAS

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

Click the submit (checkmark) button at the bottom to check in the data connector and discover the available cubes in the background.

2.2. Create a new dashboard and add a measure

You can now use this data connector in metric sets or in views such as dashboards.

For example, create a new a dashboard from the main menu, selecting the Blank template option.

Go to the Explore window, locate the data connector you just created, and expand it to see a list of its available cubes. Expand a cube to see a list of available measures and dimensions.

Drag measure to canvas
Drag measure to canvas

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.

2.3. Add hierarchies

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

Drag the Date.Calendar hierarchy and drop it onto the label drop a hierarchy under Rows in the Data Analysis Panel that appeared to the left of the table visualization.

Tip
Set up date mapping to take full advantage of dates and time dimensions from OLAP cubes.

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 to Columns in the Data Analysis Panel.

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

Drag hierarchy to Columns
Drag hierarchy to Columns

Note
By default, calculated members in the OLAP dimension will be excluded. To include them, right-click on the dimension in the Explore window, click Utility Hierarchy, and select Enable.

2.4. Switch to View mode

Resize the table visualization a bit larger and then click View in 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 (or long-tap) 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 (or long-tap) a CY 2003 bar on the chart.

Drill down
Drill down

From the context menu that appears, choose 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 Drill Up the same way, 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 expressions, much like you would in a spreadsheet application. A formula expression can be as simple as a constant number value, or it can use math and formula functions.

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. The Data Analysis Panel appears to the side.

Drag the Date.Calendar hierarchy to Rows in the Data Analysis 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, choose Data Tools in the toolbar, or right-click (long-tap) the chart and choose it from the context menu.

Select Data Tools
Select Data Tools

Then choose 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 call to a formula function.

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

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

Select AVG function
Select AVG function

Type an opening parenthesis after AVG like the following:

AVG(

Once the above has been entered into the formula bar, a tooltip popup appears describing the function and its required arguments.

Specifically, the AVG function requires 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 bar. 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. If the formula syntax is correct, a checkmark is displayed in the formula bar, and the bar chart is updated to show the corresponding formula series.

Bar chart with formula applied
Bar chart with formula applied

For more information on the kinds of formulas you can apply, see the article Adding formulas.

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 context menu that appears, 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 Analysis 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 x icon on the right.

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

Tip
When first adding a formula, you can click the output button in the formula bar to choose the chart type ahead of time.

3.4. Adding a formula from the Data Analysis Panel

You saw earlier how to add a formula from the toolbar or context menu. If you have the Data Analysis 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>

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 metric set and visualization 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 until you find the Formula Visualization item.

Formula Visualization
Formula Visualization

Click Formula Visualization to open the formula bar.

Type the letter A. From the auto-complete menu, select the AVG math function.

Now can refer to any or multiple visualizations on the dashboard as inputs to the formula, so the placeholders referring to measures include the visualization and metric set names. The easiest way to enter this is to click on a data point on the chart, 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. This new metric set is called a formula metric set.

New metric set and visualization added to the dashboard
New metric set and visualization added to the dashboard

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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