Connect to OLAP data and apply a formula
- Connecting to OLAP data
- Using formulas
- See also
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.
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.
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.
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.
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.
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.
2.6. Drill down on the date hierarchy
Right-click over a CY 2003 bar on the chart.
From the menu, click Drill Down, and then select Date.Calendar as the hierarchy to drill down on.
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).
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.
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.
Then click 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.
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].
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.
3.3. Re-visualize the formula series
Next, right-click over one of the formula bars on the chart.
From the menu, click Re-Visualize, and then click Line. The formula series is now displayed as a horizontal line on the bar 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.
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.
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.
This option is also available in the Metric Set Designer panel.
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.
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].
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.