Profile picture for user Stephan Freudl

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 number Filtering 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 containing the 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 number will 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.

 
by Peter Turbucz
Posted on Mon, 11/29/2010 - 14:10

Dear Stephan!

 

Thanks for your post, it looks very useful and it is very well described! :)

I have a question about database connections. In many cases the login password can change (security reasons, developer and customer can use different login, etc..). If we create many database connector datafeed, then the password change can be very difficult, and needs to know, how exactly datafeeds work. It cannot be a task for customers IT administrator.

Is it possible to give authetication paramteres (user name, password) only once, on the administration/database connections form?

I think it should work, user id and password can be contained by the connection string. 

 

I tried it, Mashzone accepted my connstring, no error appeared, but the authentication was still neccessary, in a popup :(

Thanks in advance,

Peti

 

 

 

0
by Stephan Freudl Author
Posted on Tue, 11/30/2010 - 08:14

Hi,

as you already indicated there are two ways to store credentials for DB access:

  1. As part of the JDBC-URL - the password will be visible (and possibly transferred) in plaintext, every data feed reuses that credential.
  2. As part of the data feed  - password is not visible and each feed might specify a different credential

If you invoke Test database connection option within the administrative interface a user name password prompt comes up. If you already entered credentials in the URL you may leave it blank to verify your settings.

Regards

Stephan

0
by vijay kumar
Posted on Thu, 04/26/2012 - 16:45

is it possible to achieve the same feature using a date range as an input rather a particular field being common in two tables.

 

A simple query where the data is fetched based on the date range eg: 2012-04-05 12:12:12 to 2012-04-05 13:13:00

Users are more interested to know if they can fetch information on date ranges from a single table

eg: select count (*) from table where date_range >='2012-04-05 12:12:12'  and date_range<='2012-04-05 13:13:00'

where the date range comes as a user input..

Thanks,

Vijay

0
by Stephan Freudl Author
Posted on Fri, 04/27/2012 - 08:35

Yes, I think that's possible, also. You may utilize two of MashZone's user input fields or the date picker with multiselection enabled.

Based on this you are able to have two variables inside the data feed, representing the interval's upper and lower limit.

0
by vijay kumar
Posted on Fri, 04/27/2012 - 10:55

In reply to by sstein

Thanks Stephan.

can you please share a sample  on the date range stuff as an input query.

 

Thanks,

Vijay.

 

 

 

0
by vijay kumar
Posted on Fri, 04/27/2012 - 15:33

In reply to by sstein

hi Stephan,

 

I have tried the way you had asked me for using the user input fields as Date and then converting to the date pattern to feed the query.

I assume there should be a way to pass the values i used in user input in the data feed.from the mashapp.

However i have not been able to create the front end mash app to pass the values to the data feed dynamically.

your inputs on it will be really useful.

 

Thanks,

Vijay.

0
by Stephan Freudl Author
Posted on Thu, 05/24/2012 - 09:30

That's similar to filtering of values. Assuming there is a data feed having a user input operator:

A very basic MashApp to utilize this feed could look like this:

There is a text field to enter values and a label to print the result. Lets connect the feed to the result label.

As the data feed being used contains a variable we see it below the list of columns. Now we need to bind the variable to a UI widget, i.e. the form field. Its done using a gesture which is comparable to filtering.

As there is a variable to compare with MashZone enables us to choose between filtering or user inputs. The second option has already been selected. Accept and run the MashApp.

0
by Luis Delgado
Posted on Thu, 06/14/2012 - 18:35

Great post, let me try your solution!!!

0
by Claudio Hernandez
Posted on Mon, 10/29/2012 - 15:44

Hello Mr Stephan Freudl,

I've a question ¿how  can i work if my DB have more than 10.000 row?. I'm think in SAP DB with more than 300.000 row. So, is posibile to create a correct KPI with out consider all data?.

Thanks

0

Featured achievement

Rookie
Say hello to the ARIS Community! Personalize your community experience by following forums or tags, liking a post or uploading a profile picture.
Recent Unlocks

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock