DATABASE: COPY

Copy database definition.

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.

PROJECT: NEW

Create new project

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

DATABASE: PASTE

Paste database definition. 

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

 

PROJECT: SPEED DIAL IMAGE

Set speed dial background image.

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

Understanding database stucture, selecting Code and Drill down tables, to create a config.

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

 

SCHEMA: CREATE CONFIG

Create a config.

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

     

    SCHEMA: DB STRUCTURE

    Tables with attached foreign tables.   

    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

    SCHEMA: DB DRILLDOWN

    Tables with possible drilldown. Its a father – childS hierarchy.

    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

    SCHEMA: MY DRILL DOWN

    FatherS top tables, with possible childS table where to drilldown,  for selected tables only.

    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

    TABLE: DATA

    Display data preview and meta 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

    TABLE: COLUMNS

    Columns layout.   

    Columns provide table layout with all information for each field.

    TABLE: KEYS

    Keys.   

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

    TABLE: TREE

    Drillup and drilldown tree.   

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

    TABLE: QUERY

    Query template with JOIN to fathers tables.   

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

    Click on:

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

      SYSTEM: CONSOLE / LOG

      Console is to run SQL statement. Log display database and Storm message.  

      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: DB VIEW / LIST

        Query tab result display loaded data  

        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

          WIDGET: COPY / PAST

          Copy / Past a widget from Demo to new project

          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

          FACTOR: TOTAL SUM

          Total SUM on factor.  

          Total can be set to SUM for dedicated factor.

          Steps:

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

            FACTOR: EXPRESSION

            JavaScript mathematical expression.  

            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

              DASHBOARD: ADD WIDGET

              Add widget for current dataset.  

              Add widget for current or slave dataset.

              Steps:

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

               

                DASHBOARD: UNPIN WIDGET

                Unpin widget to set size and position.  

                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

                DASHBOARD: ADD / REMOVE PAGE

                Add a new page.  

                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: SQL / MACRO

                Query editor to customize SQL source code. 

                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: SQL TEMPLATE

                Create config generate query template with JOIN up to top table (father table). 

                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

                Globals data are loaded in memory, usually to speed-up code résultion. 

                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

                FACTOR: CODE RESOLUTION

                Code resolution is to automatically replace a code with the corresponding text stored in a foreign table. 

                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

                DATASET

                Dataset is populated by one or several queries.

                ‘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

                PARAMSET

                Paramset are a list of load param for an underlying table.

                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