Adding tables

As your business processes may have a data model that requires more tables than the tables available for the default app templates, you can add new tables using Data transformations. You can use these additional tables in your process app, and enrich your dashboards with filters and KPIs created on these tables to add extra value to your business processes.

Important:

Always make sure your data model adheres the requirements. See Data model requirements.

Note:

Only models that are materialized as table can be added to the data model.

If you create a new process app, all models will be materialized as table. For existing process apps, the models in the 1_input folder are materialized as view.

Adding a new table

Follow these steps to create a new output table using Data transformations.

Note:

Make sure the data needed for your new table is available in your app. See Loading data.

Go to the Data transformations editor.
Locate the models section and add a new .sql file for the table in the transformations that you want to expose to your process app.
Run the query for the new file. You can check the data for the new table in the Preview panel.
Select Data model to view the data model for your process app.
Select + Add table. The Add table dialog is displayed.
Select the table that you added in step 2 from the Select table list.
Select the Primary key for the new table, and select Done.

The Edit table panel is displayed.

A notification is displayed that your data model does not meet the requirements.

Connecting the table

Follow these steps to create a relation to connect the new table to the data model.

Note:

Make sure to adhere the Data model requirements when adding a relation.

In the Edit table panel, select + Add new to create a new relation.
Select the field that is used a the Primary key to join the tables.
Select the Foreign key and select Apply.

Composite keys

Currently, relations in the data model are made from one field to the other. However, your data model might require composite keys, i.e. keys that are comprised from multiple fields. In this case, do the following:

Go to the Data transformations editor.
Locate the table that requires a composite key.
Add a field combining all composite fields in the select statement. See the example below.
```
Select 
  {{ pm_utils.concat('"Field_A"', '"Field_B"') }} as CompositeKey,
     table1.*
from table1Select 
  {{ pm_utils.concat('"Field_A"', '"Field_B"') }} as CompositeKey,
     table1.*
from table1
```
Select Run file to populate the table.
Repeat steps 3. and 4. for each table that requires a composite key.
Go to the Data model.
Add a relation between the tables for which you defined a composite key and select the field you defined as CompositeKey from the Key list for each table.

Making the new table available for use in dashboards

Select Save in the Data model editor to save the changes to the data model.
Select Apply to dashboards to run the transformations and make new the table available for use in dashboards.

Note:
This may take several minutes. Only if the run finishes successfully, the new table will show up in the Data Manager.
Select Edit dashboards and open the Data Manager.
A message is displayed indicating that the new table is added.
Select Confirm to continue.
Open Data Manager.