Datasources

A datasource retrieves data from the database and allows it to be exposed to users via front end configuration items such as Content Areas and Dashboards.

Datasources are used extensively throughout the system such as in Dashboards, Reports, Landing Pages, Entity Pages, Content Areas and more.

New datasources can be created on the datasource configuration page

1. Config Workbench: Navigate to the config workbench from the menu.

2. Datasources: In the Data and Reporting section, click on Datasources.

3. New Datasource: Click on the plus button in the top-right of the datasources page.  This will open the datasource editor.

4. Datasource Information: Input general information such as the name, scope and refresh rate for the new datasource.  Name and scope are mandatory.

5. Datasource Query: Input a query for the datasource.  It is not mandatory for creation.

6. Datasource History: When you save the new datasource it will create the first history record automatically.

7. Save: Click the save button on the datasource editor to finish creating the new datasource.

8. Cancel: Clicking on cancel will discard the new datasource.

Existing datasources can be edited by re-opening them in the datasource editor.

If you edit a datasource it will affect any areas in the tool that is using that datasource.

1. Config Workbench: Navigate to the config workbench from the menu.

2. Datasources: In the Data and Reporting section, click on Datasources.

3. Open Datasource: Search through the list to located the datasource you want to edit.  Open it from the list by clicking the open button.

4. Edit Datasource: Make your updates and adjustments to the datasource in the datasource editor.

5. Save: Click on the save button in the datasource editor to commit any changes.

6. Cancel: Clicking on cancel will discard any unsaved changes to the datasource.

The information tab contains fields for general attributes related to the datasource

1. Name: The name of the datasource as shown in the selection lists and datasource configuration page.

2. Scope Name: The token identifying this datasource in configuration.

3. System Entity: Marks if the datasource is considered a system entity which can only be edited by Polarisoft R&D.

4. Grouping Column Name: The query result column that the result set should be grouped by.  Can be left blank.

5. Created On: When the datasource was created and by who.

6. Last Updated: When the datasource was last updated and by who.

7. Refresh Rate: How often the datasource should refresh its result set.

The refresh rate will cause the datasource result set to periodically update based on the setting chosen:

1. Always: The datasource is always refreshed when loaded.

2. Time Interval: The datasource will automatically refresh itself based on the set time interval.

3. Never: The datasource is never refreshed.

The query tab is where you can write the SQL to retrieve information from the database

1. Query: The SQL query to retrieve data from the database.

2. Query Filters: Filters that can be applied to the datasource by appending further SQL to the end of the query. Query Filters appear in the content area definition as selectable filters when the datasource is selected.

3. Token Mapping: The Token Mapping connects the SQL queries' result column alias with a recognised token parameter.

4. ERD: Opens the entity relationship diagram documentation.

5. Test: Allows you to test run the query directly from the datasource editor.

Example Code Block - Datasource Query

   SELECT                                                               

      id                                                                

      , project_name                                                    

      , start_date                                                      

      , end_date                                                        

   FROM                                                                 

      projects                                                          

   ORDER BY                                                             

      project_name                                                      

Filters allow tokens to be defined in the SQL query which can modify and focus the data retrieved from the database.

Filters can be configured for the datasource yet made optional.  In this scenario the filter query needs to be written in a specific format to handle blank inputs.

All query filters will appear as editable options in front end configuration for the entity, i.e. dashboard or content area, in which the datasource is attached.

Filters have the following attributes:

1. Prompt: The name of the filter as it appears in front end configuration.

2. Token: The token that will represent the filter value and is used by the datasource query.  The token is referenced by the query in the format of [P.FILTER_TOKEN_NAME]

3. Validation: Valid input for the filter can be controlled through validations.

4. Required: Specify if the filter must have input before the datasource can be run.

Example Code Block - Standard Filter

   SELECT                                                               

      id                                                                

      , project_name                                                    

      , start_date                                                      

      , end_date                                                        

   FROM                                                                 

      projects                                                          

   WHERE                                                                

      start_date = '[P.START_DATE]'                                     

   ORDER BY                                                             

      project_name                                                      

1. START_DATE: The token used for the start date filter. The query will always try to resolve the token in this format.

Example Code Block - Optional Filter

   SELECT                                                               

      id                                                                

      , project_name                                                    

      , start_date                                                      

      , end_date                                                        

   FROM                                                                 

      projects                                                          

   WHERE                                                                

      start_date = '[P.START_DATE]'                                     

      /*[TOKEN] AND end_date = '[P.END_DATE]' [/TOKEN]*/                

   ORDER BY                                                             

      project_name                                                      

1. END_DATE: The token used for the end date filter. Query statements between /*[TOKEN] ... [/TOKEN]*/ will only be executed if the token within it is successfully resolved into a value by the datasource engine.

The token map is how the datasource query result is exposed to the front end configuration.  Each query result column can be mapped to a token, and that token can then be referenced to display data.

When adding token mappings the following attributes are:

1. Token: The token that can be referenced in front end configuration.

2. Column Name: The alias of the column returned by the datasource query.

3. Remove: Clicking the bin icon next to each token will remove the mapping, if the datasource is saved.

Example Code Block - Using Token Maping in the Content Area Definition

   <div class='landing_title page_title'>Hi, [P.DS_SCOPE.TOKEN_NAME]</div>    

1. [P.DS_SCOPE.TOKEN_NAME]: The format used to let the system know it is referencing a datasource token.  The datasource must be available to the entity.

2. DS_SCOPE: The scope name of the datasource, set in the information tab of the datasource editor.

3. TOKEN_NAME: The token used in the token mapping, set in the query tab of the datasource editor.