Copy definition of database object avoid to manually enter it in new project.

Steps

1. Connect to project: Demo – Human resource
2. Configuration -> Database -> Human resource -> Right-click -> Copy definition

Then, Edit, to see Connection params and Schema option.

Department is defined as Global tables (code) and 6 other tables are selected for Drilldown.

Create New project consist of selecting a target folder where default XML objects will be created.

Steps:

1. Click on + New project
2. Select a target folder. The folder name will become the project name
3. An empty config is then created
4. Sign in

Create Database object and Paste definition from Demo – Human resource.

Setps:

1. Configuration -> Create -> Database
2. Paste
3. Save -> Create new database into Core > Finish
4. Configuration ->  database. Object is created

Edit project to set default speed dial background.

Steps:

1. Configuration -> Project -> YourProjectName -> Edit
2. Options -> Speed dial background image -> Select Speeddial.jpg
3. Save
4. Close Speed dial Tab. A new one with selected image will be displayed

Schema structure and data  must be analysed to understand what are Code tables and Drill down table.

• Code tables will become Global data and will be loaded in memory at login
• Drill down tables will become dashboards to be customized, with all the JOIN to father tables.

A table can be selected twice, as code and then also for drill down. If no tables are selected, all tables are considered as drill down table.

• In our example, DEPARTMENT is already selected as a Code table, and 6 others tables are already selected as table for Drilldown. Feel free to add other tables, according to new dashboard to be created.

Drag and drop:

• Tables with code to Code table
• Tables with data to Drilldown table

Click-on:

• DB structure to understand it
• DB drilldown to see drilldown starting from top tables
• My drilldown to see drilldown for selected drilldown table
• Info to get a new tab with list of Tables, Columns, Primary keys, Foreign keys
• Key maker to add missing keys
• DB Search to search for text through all fields of all tables

Double-click on:

•  Table name to get a tab with data preview and meta data

Press shift and roll-over:

•  Table name to get a tips with data preview

Create config generate a working configuration, ready to be customized by developers, and then by final users.

A configuration is a set of XML source files, stored in folders. Versioning is controlled by external tool (SVN, Git,..) 

The following main objects are created:

• Dashboard
• Dataset
• Paramset
• Factor
• Query
• ..

Steps:

1. Tools -> DB Schema
2. Create config to generate all objects
3. Exit
4. Login again to load generated config
5. Configuration display generated objects

DB Structure display tables, with attached foreign tables below it. Foreign key pointing to primary key are displayed. Depending on database size and complexity, this tree can be huge. By default we display only one level, but it can be expand.

Click on:

• Add level,  to add one level
• Fold / Unfold to colaps / expand tree

Drag and Drop:

• A node to Drilldown table, to select the whole hierarchy below that node

Shift + roll over :

• To get tips with preview of data

DB drilldown display fathers top tables and related childs tables where to drilldown. Foreign key of childs tables, pointing to the primary key of the father table are displayed. Depending on database size and complexity, this tree can be huge. By default we display only one level, but it can be expand.

Click on:

• Add level,  to add one level
• Fold / Unfold to colaps / expand tree

Drag and Drop:

• A node to Drilldown table, to select the whole hierarchy below that node

Shift + roll over :

• To get tips with preview of data

My Drilldown display fathers top tables and related childs tables where to drilldown. Foreign key of childs tables, pointing to the primary key of the father table are displayed. Depending on database size and complexity, this tree can be huge. By default we display only one level, but it can be expand.

This hierarchy must be validated BEFORE TO CREATE CONFIG. This because Create config will generate all the SQL JOIN, starting from a child, up to top level father table. 

Click on:

• Add level,  to add one level
• Fold / Unfold to colaps / expand tree

Shift + roll over :

• To get tips with preview of data

Data are displayed twice. DB view display data like they are stored. List view display formated data with column name, according to factor definition.

Double-click on:

• Table name to get a new tab with data preview

Click on:

• Data to preview data and formated data
• Columns for layout
• Keys for foreign and drilldown keys
• Tree for drillup and drilldown
• Query for SQL template, with JOIN

Columns provide table layout with all information for each field.

Foreign keys are displayed. Drilldown key to childs tables are summarized.

Drillup display path to father tables. Drilldown display path to child table.

Sql query template is generated, with JOIN to all fathers tables.

Click on:

• Sql source code to modify it
• Run query to execute it

Console is to execute SLQ statement. Key word are highlighted. Copy/Past source code, or enter it manually, then press Exec to run it.

Query result is displayed in a new Query tab.

Log display history of database and Storm message. Log are per user, per login, per day and are stored in folder:

• ..\storm.log

Click on:

• Console to past or enter SQL statement
• Exec to execute SQL statement on database
• Clear to clear log

Query result tab display data twice. DB view displays database fields and records without formating. List view display columns and formated data.

Click on:

• Edit query to manage query object
• Create dashboard to create it, with all related objects

Copy Employee list widget object from Demo – Human resource, and Paste it to new project.

Steps:

1. Sign in to Demo – Human resource
2. Employee dashboard
3. Page Pivot and list
4. Edit of Employee, sorted by Department
5. Copy
6. Back to new project
7. List Edit-> Paste -> Apply
8. Save -> Create new List -> Core -> Finish

Total can be set to SUM for dedicated factor.

Steps:

• Right-click on column header Salary-> Edit
• Total select SUM, or desired function
• Save

Mathematical expression are defined using javaScript.

Reference to factor must be enclosed with: ${FactorID}

To know more about JavaScript:

https://www.w3schools.com/js/default.asp

Steps:

• Right-click on column header Salary -> + Insert
• Enter Alias to be displayed
• Enter expression
• Save as

Add widget for current or slave dataset.

Steps:

• Add widget
• Dataset Employee, or any other slave dataset

Unpin  widget to drag and drop widget positions and size, then Save dashboard layout.

Steps:

1. Right-click on Page name -> Unpine widgets
2. Drag and Drop windows header to move on grid
3. Drag and Drop bottom/right/corner to change size

Click on:

• Cascade widgets
• Tile widgets
• Allign to grid

• Widget toolbar -> Pin
• X to delete a widget
• Save dashboard layout

Add page after current one. Then page sequence can be changed.

Steps:

1. Right-click on Page name -> Add/ Remove page
2.  Drag and Drop Page name to change sequence
3. Double-click on page name to edit it

Query edit  is to customized generate SQL template.

Returned data can be transformed with Script and also transposed (row to columns).

Steps:

1. Dashboard -> Edit -> Properties-> Query,
   or, Configuration -> Query -> Right-click -> Edit
2.  Apply to run query
3.  Open selected macro to edit Replace object, for exemple to activate employee columns
4. Expend macro to replace macro with related source code (if really needed)

Query object are generated by Create config, with the following generic structure:

SELECT

— {% if reduced %}

${ReducedColumnsOfTableA}

— {% else %}

${ColumnsOfTableA}

${ReducedColumnsOfTableJ1}

— {% endif %}

FROM

${SCHEMA}.TableA TableA

LEFT JOIN ${SCHEMA}.TableJ1 TableJ1 ON (TableA.PkColumn = TableJ1.FkToPkColumn

WHERE

${PARAMS}

—

AND ${CONTEXT}

—

LIMIT ${LIMIT}

– – {% if reduced %} … – – {% else %} enclosed code will be executed when we click on a drill down tree node, this to get the number of elements displayed into node. Selecting reduced columns speed-up response time and reduces traffic networks.

– – {% else %} … – – {% endif %} enclosed code will be executed when we click on Load.

LEFT JOIN to father tables are inserted, up to top father table.

${PARAMS} and ${CONTEXT} are reserved keyword to generate the WHERE condition according to load param or selected context.

“- -” are separator to enclose code. Enclosed source code will be activated only if corresponding macro is populated. It can be used both in SELECT or in WHERE condition (see EDIT param)

Check list of your source code:

1. Apply to run query and check for SQL error
2. Tables must be prefix by ${SCHEMA}.YourTable
3. Table must have an alias with the same name, this to be free of schema name in SELECT ..,  in ON (..), and also with Params machinery
4. In macro, select only needed fields, this to speed-up your query
5. Feel free to add ORDER BY, despite that sorting is easily done in List widget

Globals data are populated by queries executed at login. Data stays in memory. Periodical refresh can be defined in project.

Advantage of globals data is to read once static code such as list of currencies, countries, etc.., this to massively speed-up code resolution.

Queries for globals are generated without JOIN neither macro.

Code, language if exist, and text to be displayed must be adjusted in SELECT statement.

Calculation of some globals data may takes time, especially if it does SUM GROUP BY on the DB. To avoid to slow-down login time, such queries will be marked as lazy.

A progress bar of loading globals is displayed during login.

Steps:

1. Global data are select in DB Schema, before to create config
2. Tools -> Globals,
3. Click on a query object to see content
4. Right-click on a query object -> Edit
5. Reset to reload all Globals data

When a foreign key field is pointing to a table selected as globals, create config will generate a new factor to automatically display the corresponding text of the code.

Factor Id and alias will be CodeField_PTR_ForeignTable.

If the code is a business code, Code/text pair factor must be checked, this to make it visible with a Tips when press Shift and roll-over the cell.

Steps:

1. Right-click on Column header -> Edit
2. Enable pointer
3. Datasource globals query mist be selected
4. Link is pair of fields, CodeField1:PK1OfForeignTable,.. 
5. Click on Code/text pair factor, this to have the Tips
6. Expression to display the foreign text field

‘In our example the dataset is populated by only one query.

In SQL  source code, we have a JOIN to EMPLOYEE father table (the employee responsible for the project). As a consequence, we may load projects with project params, but also with employee params. Project and Employee paramset were generated by Create config.

Join to major project is used only to display the major project name. Create config dont generate paramset when we have a join to the same table. If needed it can be added manually.

Dashboard default is selected

Dataset  is populated by one or several query.

Paramset is to build Load window. Usually one paramset per JOIN table.

Join query will be execute after dataset queries. Data will be merged in memory.

Drill down to next level are defined.

Details windows are defined.

Add field is or manually attached a factor to it

Script can be executed for data transformation.

Steps:

1. Right-click on a Query -> Edit.
2. Right-click on a Paramset -> Edit.
3. Drag and Drop a query object to Datasource
4. Drag and Drop a paramset object to Params

The generated param for the SQL bind EMPLOYEE.`EMPNO` was manually improved to display list of employee, with Empl.No, First name and Last name. Multiple selection are allowed, to load selected employeed. 

Steps:

1. Right-click on a param -> Edit
2. Double-click on embeded query source code to adjust it
3. Click on Factor drop down menu to select a Factor to be displayed
4. Preview to see it