Stephan Freudl's picture

Motivation

Even MashZone Enterprise Edition reads a max number of 10,000 lines per data source. The rationale for this is MashZone’s intention:

  • MashZone is about dashboarding based on values aggregated previously
  • Number crunching should be delegated to back ends

This post explains how to interact with back ends to receive the correct 10k lines, here a using a database. Results of user interactions will be used to re-query a database.

Tobias already blogged on this topic, he discribed how to query web services that way.

Overview

The example is based on Oracle XE sample schema HR. Christian explains how to activate it and hook up MashZone against. Today two tables are of interest

  1. Table Employees which lists employees’ names and a department number (foreign key) each employee is assigned to.
  2. Table Departments which consists of a unique department number (primary key) and departments names.

Assuming there are many employees this sample used the department as a filter. The sample is structured in three parts

  • List departments
  • Use department’s name/number to query for employees assigned to it
  • List employees filtered by department numberFiltering is done outside MashZone, i.e. it is delegated to the database.

List departments

In the first step create an empty MashApp and a data feed to list all departments. The box is filled by this statement:

select department_id, department_name from departments;

Based on the two column data feed bind it to a selection box.
The box contains in fact two columns

  1. Visible one with names
  2. Invisible column containingthe department number

Therefore after a box entry is selected two attributes are returned.

Use department’s name/number

Next insert a label to echo user selections. The images illustrate the data assignment of the label

  • To echo select use selection option
  • Select the widget to echo the selection from
  • Select one of the two columns, i.e.attributes returned

In a similar way the department numberwill be used to query for employees.

A little background on data feeds:

  • Data feeds can hold single value variables
  • User selections can be bound to variables – at runtime
  • Variable values can be sent to data sources

In this example the selected number will be used to compose a SQL statement at runtime.

List employees filtered by department number

In the last step use a table to list employees. The data feed behind it executes a query including a variable – department id:

select first_name, last_name from employees where department_id = X;

Basically the feed consists of data source, individual value text concatenation, number to string conversion and the user input on the far right hand side.

Data feeds including variables show them in data assignment mode below their columns:

Binding user selections works almost like filtering.
Select the department list and the id is selected automatically since the only one of type number.

Done

Each box selection triggers another SQL query so filtering is handled by the back end.
The entire sample works with RESTful web services, ARIS PPM and webMethods Optimize almost identically!

Please find a slide version of this artivle and the MashApp to reproduce it on your own attached.