SQL Query Data Set

Selecting the query option requires the BI developer to write an SQL statement to retrieve data.

Remember that the SQL dialect depends on the data source that has been chosen. The SQL text must be written in the Query text area. Below a SQL query example.

Listing 8 SQL query example

SELECT p.media_type as MEDIA, sum(s.store_sales) as SALES
FROM sales_fact_1998 s
JOIN promotion p on s.promotion_id=p.promotion_id
GROUP BY p.media_type

It is also possible to dynamically change the original text of the query at runtime. This can be done by defining a script (Groovy or JavaScript) and associating it to the query. Click on the Edit Script section (see next figure) to open the script editor and write your script. The base query is bounded to the execution context of the script (variable query) together with its parameters (variable parameters) and all the profile attributes of the user that executes the dataset (variable attributes).

Fig. 66 Script editing for dataset.

In the script below we use JavaScript to dynamically modify the FROM clause of the original query according to the value of the parameter year selected at runtime by the user. You can notice that the variable query, provided by Knowage, contains the text of the SQL query.

Listing 9 Example of a script in a Query dataset

if(isValid('year')) {
          if( parameters.get('year') == 1997 ) {
        query = query.replace('FROM sales_fact_1998', 'FROM sales_fact_1997');
    } else {
        query = query; // do nothing
    }
  }

In the above example we use the function isValid to test whether the parameter year contains a null value or not.