
Content	Filters	Formulas	Data Sources

Building a Basic Dashboard

Sunday, March 09 2008 - Learning Center

In this tutorial, you will learn how to build a fully functional, interactive dashboard that combines various widgets and formulas.

Before you start...

Download transactions.xls

The following tutorial can be applied on any database server. But for the purpose of demonstration, we have extracted a subset of the data and moved it into Excel.

Note:

If you never connected to data in Prism before, please take a look at the Connect to Data tutorial before you continue.

Also, in order to understand the basics of creating and manipulating widgets and formulas, take a look at the following tutorials:

Working with Pivots

Working with multi-dimensional formulas

Jump To:

Step 1: Import transactional data into Prism

Step 2: Create a new Sales formula

Step 3: Analyze Sales over time

Step 4: Apply Time Filters with the Date Range Picker

Step 5: Apply Product Categories Filter with the Drop Down Selector Widget

Step 6: Modify Selector Widget to support multi-selection of members

Step 1: Import transactional data into Prism

The first step is to import transactional data (for the sake of our tutorial, those records are stored in the attached Excel file) into Prism.

To Do That:

1. From the File menu, click on the Create new Data Source... menu item.
2. Use the wizard to connect to transactions.xls. If you need help on using the wizard, go here.
Step 2: Create a new sales formula Just like many transactional sources that combine multiple tables into one view for analysis, one requires to combine multiple fields in order to create new fields that represent measures. For example, our data source contains sales transactions, but doesn't really have a Sales field. If we wish to get the sales figure per transaction, or per any other member on any given dimension, we must first create a new formula that represents Sales in terms of Unit price and Quantity. To do that: 1. Right click over the Measures dimension from the Data Browser and select the "New..." menu item
2. Type in the SUM function
3. Then, drag the two numeric dimensions: Unit Price and Quantity, from the Data Browser into the Formula Editor
Note: Prism allows us to use both measures and numeric dimensions inside formulas. We use measures when we wish to aggregate the values over any given members, and we use numeric dimensions when we want to calculate something on a row by row basis. In our example, the unit Price and quantity dimensions could have been measures as well, but this would have aggregated Unit Price and Quantity inside the SUM() function, resulting wrong values. Instead, we will multiply numeric dimensions to ensure no aggregation is done inside the SUM(), but only on the SUM() as a whole.
Here is the final result of dropping and multiplying the two numeric dimensions:
4. Press [Enter] and type in the name Sales in the Add New Formula dialog
As a result, Prism will add the newly created formula into the local repository for future usage:
3. From the Data Browser, expand the Required Date (Calendar) dimension and drag the Months level on the Axis panel, inside the Widget's Data Layout
Step 3: Analyze sales over time Now that we have our new Sales formula, we can start putting it into use by visualizing Sales over time inside a chart widget. First, lets insert a new Chart widget...
To do that: 1. Click on the New Widget toolbar item 2. From the sub menu, select Area Chart Widget
4. From the Data Browser, expand the Measures dimension, then the Custom Measures folder, and drag the Sales formula on the Measures panel, inside the Widget's Data Layout 5. Press on the Update button to refresh the Chart Widget
Tip: Move the mouse cursor over a formula inside the Data Browser to see the actual syntax behind the formula:
Now that the chart is populated with data, we can easily view the history of our sales transactions
Step 4: Apply Time Filters with the Date Range Picker This step involves the addition of a Date Range Picker widget which can filter other widgets by letting the end-user select specific date ranges. Lets start by dropping a Date Range Picker widget over the chart. To do that:
1. Click on the Widgets button (located at the bottom-left corner of the Prism environment) to switch to the Widgets view.
2. Inside the Widgets view, expand the Selection widgets category, then drag & drop the Date Range Picker on top of the chart.
3. Switch back to the Data Browser, by pressing on the Data Browser button.
4. From the Data Browser, select the Required Date dimension and drop it on the empty Date Range Picker Widget
* After the drop, the Date Range Picker Widget gets filled with dates:
Now all there's left to do is to connect the Date Range Picker widget as background to the Chart widget. Explanation: By doing that, we ensure that every selection in the Date Range Picker widget will automatically reflect the data in the chart widget. To Do That:
5. Move the mouse cursor over the Chart widget, and click on the Apply background from other Widgets button.
6. Click on the background button to open the background menu. Explanation: The background menu displays all widgets (in the current sheet) that can be used as background to the current Chart widget.
Tip: moving the mouse cursor over each menu item will highlight the widget that will be used as background. In our example, moving the mouse cursor over the Years menu item will highlight the Date Range Picker as follows:
6. Inside the popup menu, select the Years item which represents the Date Range Picker, then click on the Apply button to confirm the background selection
As we can see, the chart got updated with the specific dates. We can play with the Date Range Picker and see how different dates reflect the chart: \
Step 5: Apply Product Categories Filters with Drop Down Selection Widget In the previous step ,we added a date filter to our chart, in this step, we are going to apply an additional filter that will allow us to analyze specific product categories. To do that:
1. From the Data Browser, drag & drop the Categories dimension onto the sheet.
2. Rearrange the newly added Drop Down Selector widget to the right of the Date Range Picker.
3. Connect the Drop Down Selector widget to the Chart by following the same steps you did to connect the Data Range Picker widget.
That's it, we now have two selection widgets providing background to our Chart widget, and end-users can play with the widgets to filter specific time ranges and specific product categories.
Step 6: Modify Selector Widget to support multi-selection of members To allow multiple member selection in a Drop Down Selector widget, we must set the Multi-selection property. To do that: 1. Right click on the Drop Down Selector widget 2. Select the Preferences menu 3. Inside the Preferences menu, select Multi Selection
That's it, we can now select multiple members and see how it affects our sales values.