Data Model/Tables, Views and Stored Procedures

The Data Model/Tables, Data Model/Views and Data Model/Stored Procedures pages allow user to

• manage tables, views, their columns and relationships

• add and remove calculated fields

• manage stored procedures, their parameters, output columns and relationships

• assign filter values for stored procedure parameters

• mark stored procedures as dynamic

Note:

Stored Procedures are not supported in AWS Redshift databases.

List tables, views or stored procedures

1. In browser, log in to AgilePoint NX as a user with Data Model permission.

2. Click Settings, then Data Setup then Data Model in the left menu.

3. Select the Setting Level: either System or a specific tenant.

4. Click Tables, Views or Stored Procedures in the Middle Panel. (Fig. 23)

   

   Fig. 23 Data Model - Tables, Views and Stored Procedures
   

5. Visible tables, views, or stored procedures from all connections will be displayed in the upper data source grid, and columns of the currently selected one will be displayed in the lower Column Grid. (Fig. 24)

   

   Fig. 24 Data Model - Data Source Grid and Column Grid
   

6. By default, only the first page with 10 items are displayed. To see more, either use the next page icon

   

   Fig. 25 Data Model - Next Page
   

   or select a larger number in Items per page box.

   

   Fig. 26 Data Model - Items per page
   

Search for tables, views or stored procedures

The Search box at the top allows user to search for specific tables, views or stored procedures.

1. 

   Fig. 27 Data Model - Search Tables or Views by element

   Select a specific element to search for in the dropdown on the left of the Search box. Default is All. (Fig. 27)

2. Type a partial name and click the search icon (🔍).

3. Only the matching tables, views or stored procedures will be displayed.
   

Assign a category to a table, view or stored procedure

1. Select a name in the Category dropdown to assign it to the table, view or stored procedure.

2. If the category name is not yet in the list, user can add it by typing the name in and press Enter. (Fig. 28)

   

   Fig. 28 Data Model - Functions
   

3. Continue to assign category to more tables, views or stored procedures in the same page.

4. Click Save button at the top, then click OK in the confirmation pop-up.

   Note

   User must save the assignments before moving to another page or another group in the Middle Panel.

See also

Advanced Settings/Add data source categories.

Assign an alias to a table, view or stored procedure

1. Enter an alias into the Database Source Alias box to assign it to the table, view or stored procedure.

2. Continue to assign alias to more tables, views or stored procedures in the same page.

   The alias can contain any characters except for “[” and “]”.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note

   User must save the assignments before moving to another page or another group in the Middle Panel.

Note

Within a category, the aliases cannot be duplicated. In Fig. 29, Alias_1 is duplicated because the data sources are in the same Category2, and Alias_2 is valid because the data sources are in different categories.

Fig. 29 Data Model - Duplicated Aliases in Category

Manage relationships for a table, view or stored procedure

1. Click the relationhips icon at the end of each line to open the Relationships dialog.

2. See Data Model/Relationships and Schema for how to manage relationships.

Assign an alias to a column in table, view or stored procedure

1. In the Column Grid, enter an alias into the Column Alias box to assign it to the column.

2. Continue to assign alias to more columns in the same page.

   The alias can contain any characters except for “[” and “]”.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note

   User must save the assignments before moving to another page or another group in the Middle Panel.

Note

Within a table or view, the aliases cannot be duplicated. (Fig. 30)

Fig. 30 Data Model - Duplicated Column Alias

Select visible or not for a column in table, view or stored procedure

A visible column will be included in any field selection control in report.

1. Untick the Visible check-box to exclude the column from field selection controls in report, or leave it checked to include.

2. Continue for more columns in the same page.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note

   User must save the changes before moving to another page or another group in the Middle Panel.

Select filterable or not for a column in table, view or stored procedure

A filterable (and visible) column will be included in any filter in report.

Note

A not visible column will be excluded from any filter in report no matter it is filterable or not.

1. Untick the Filterable check-box to exclude the column from filters in report, or leave it checked to include.

2. Continue for more columns in the same page.

3. Click Save button at the top, then click OK in the confirmation pop-up.

   Note

   User must save the changes before moving to another page or another group in the Middle Panel.

Update Filter Value for a table/view field or a stored procedure parameter

This feature are only available for tables/views’ fields from version 2.14.0

1. Click the icon in Filter Value box.

   Note: For Store Procedures, Filter Value icon only appears for parameters.

2. Select either Filter Lookup Key - Value or User Defined Filter Value

   • Example to set parameter @OrderID to look up from NorthwindA.dbo.Orders.OrderID, displaying column ShipName to end-user. (Fig. 31)

     

     Fig. 31 Data Model - Filter Lookup Key - Value
     

   • Example to set parameter @OrderID to look up from a list of 3 values: the value of Tenant ID and 2 fixed values NewValueA and NewValueB. (Fig. 32)

     

     Fig. 32 Data Model - User Defined Filter Value
     

3. 

   Fig. 33 Data Model - Selected User Defined Filter Value

   The selected filter value will appear in the Filter Value box. (Fig. 33)
   

4. Click Save button at the top, then click OK in the confirmation pop-up.

Add a calculated field to table or view

Calculated field

Calculated field is a virtual field calculated from an expression that can use other fields in the same table or view. A calculated field is created to simplify the select queries by hiding the detailed formula, and the formula can be replaced without any change in existing queries. A calculated field will also avoid data redundancy since it is re-calculated in each query.

For example, from a student test score, the grade can be calculated by this formula:

WHEN score >= 0 AND score < 7 THEN 'GOOD'
WHEN score >= 7 AND score < 9 THEN 'EXCELLENT'
WHEN score >= 9               THEN 'OUTSTANDING'

If defined as a calculated field, the grade can later use another formula without changing any queries.

Note

Calculated fields cannot be used in relationships between tables or views.

See also

Computed Columns

1. Select the table or view in the upper data source grid.

2. Click Add Field button on top of the Column Grid to open Add Calculated Field pop-up.

3. Enter the field name into Column Name box.

   The field name must be unique in that table or view. (Fig. 34)

   

   Fig. 34 Data Model - Calculated Field Name
   

4. Enter the definition for the calculated field into Expression box.

   Click the bulb icon (💡) to see the list of available fields, functions and operators. Then click a field, function or operator to insert it into the cursor position. (Fig. 35)

   

   Fig. 35 Data Model - Calculated Field Available List
   

5. Depending on the formula, a suitable data type is suggested in Data Type drop-down.

6. Click Preview button to see a sample result of the formula.

7. Click OK to accept the formula and close the pop-up. (Fig. 36)

8. Click Save at the top.

   

   Fig. 36 Data Model - FullName Calculated Field
   

See also

• Analytics Center Data Types

• Analytics Center Operators

• Analytics Center Functions

Remove a calculated field from table or view

1. Select the table or view in the upper data source grid.

2. Click the remove icon (X) at the end of the calculated field.

   The remove icon is only enabled for calculated fields. (Fig. 37)

   

   Fig. 37 Data Model - Remove Calculated Field
   

3. Click OK in the confirmation pop-up.

   If the calculated field has been used in any report, user will have to confirm that these reports will not be viewable anymore.

4. Click Save at the top.

Set stored procedure as dynamic

For stored procedures with any input parameter, they can be set as dynamic.

Dynamic stored procedure

A stored procedure with one (or more) input parameter might have different output schemas based on the value of that input parameter. The reports have 2 options to handle this situation:

• Dynamic: The schema is only determined at report design time by a user-supplied input value.

  The schema is empty in Data Model.

• Non-dynamic: The schema is assumed to remain consistent regardless of the input value.

  System can try getting that schema for Data Model by executing with null input parameters. In case it cannot because any parameter requires NOT NULL, user will be prompted for a proper input value.

  The exact rule for NOT NULL input parameters:

  1. If Filter Value has been defined for a parameter, the first value in that list will be used as input value.

  2. If not, then null will be used as input value.

Non-dynamic should be the default value since in practice, well-coded stored procedures should return a consistent schema.

1. 

   Fig. 38 Data Model - Set stored procedure as dynamic confirmation

   Tick the Dynamic check-box to set a stored procedure as dynamic.

2. Click OK in the confirmation pop-up. (Fig. 38)

3. Click Save button at the top, then click OK in the confirmation pop-up.

4. The stored procedure will be set as dynamic and its schema will be removed from the lower Column Grid. The Execute button is also disabled.
   

Set stored procedure as non-dynamic

1. Untick the Dynamic check-box to set a stored procedure as non-dynamic.

2. Click Save button at the top, then click OK in the confirmation pop-up.

3. The stored procedure will be set as non-dynamic. The Execute button is also enabled for user to get the schema.

Execute a non-dynamic stored procedure to get the schema

1. Click the Execute button above the lower Column Grid.

2. System tries running the stored procedure to get the schema.

3. The schema will be populated into the lower Column Grid.

4. Click Save button at the top, then click OK in the confirmation pop-up.

The action will fail if one of the parameters requires not null and Filter Value has not been defined.

In this case, please update the Filter Value section.