The ETL Process - defining BI Names and Natural Keys

SAI360 uses a separate data warehouse database for reporting. The building or the data warehouse database is done by an automated ETL process. The ETL process does the following:

Creates the structure for tables, fields and views required for SAI360 reporting in the data warehouse
Transferring the data from the SAI360 database to the SAI360 data warehouse database
Automatically generating the cube schemas.

The names of the tables, fields and views in the SAI360 data warehouse database will have meaningful names as opposed to ids.

Overview of the ETL Process (for more details please contact your Account Manager or our Support Team).

STEP	ACTION	OUTCOME
1	To add Component(s) to Data warehouse database, Navigate to Designer Configuration Management BI Components. Click on Components to ADD to data warehouse to select the Components to add to Data Warehouse. OR Navigate to Designer Component Management, open up the Component you wish to add. Click on BI Configuration tab and tick Use this component in BI Reporting Click the Run button on the top right corner.
2	Review and modify BI Names and Natural Keys	BI Names for Components, Fields and Relationships, as well as Natural Keys are maintained in the Component Management Tool. The names defined here will determine the table, field and view names in the Data Warehouse. This step is very important when you are about to build the Data Warehouse database. Refer to the next section on the good practices for making changes to the details.
3	Run a Publish BI from Designer System Publish BI. (Run Publish BI anytime BI details have been modified)	Generates the below files in the server: ETL.xml Cube_Schema.xml AdvancedCube_Schema.xml
4	Run the ImportETL.bat Batch File.	This loads or updates the ETL definition into the repository. This is required for the first time you run the ETL .
5	Using the Pentaho Data Integration Tool (Spoon), run the ETL	This will create or modify the datawarehouse database structure AND load the data into the datawarehouse database.
6	Test your datawarehouse database to ensure it is ready for use.	Check the following: All selected components have a matching rt_ view The number of records in the view matches the number of records in the SAI360 component If all your tests pass, the datawarehouse is ready for use to create reports. To get started building reports using the Report Designer refer to this section.
7	In the User Console Connect to the Data Warehouse database Import the automatically generated cube schema	Cube schemas allow Analysis Reports in the User Console to be built.

GENERAL RULES – When renaming BI names for component, field and relationship names:

Do not use any Oracle or SQL reserved words. Refer to the following lists for all reserved words
- For Oracle - http://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm
- For MS SQL - https://msdn.microsoft.com/en-us/library/ms189822.aspx
Ensure the limitation of 30 characters are adhered to for Oracle

The Natural Key

The Natural Key is used in the Dimension and Fact Tables ONLY, which are used to build Cubes. It is auto-generated by the system, but can be updated in the BI Section of the Component Management Tool.

The Natural Key is a way to uniquely identify the records in a dimension. This is particularly important when viewing data in the User Console’s Analysis reports. For example if they are looking at Incidents by Department (Department being a dimension) then the name you see in the department column is what is defined as you Natural Key for Department component:

BI Report - example for natural key

Natural keys are automatically picked by the system as follows:

If any field have the following in the name of the field "name, title, code, reference, number and id", then these will be picked. Depending on the order of processing of the fields, the first one the system finds with one of those words in the name it will pick it.
It will automatically exclude any fields with "sys, legacy, xx, zz, notused, not_used' in the name.

The Natural Key can be changed BUT the following rules must be followed:

Choose a field that is mandatory.
Choose a field which will uniquely identify a record.

What if you cannot find a valid field for the Natural Key?

If the component will NEVER be used for a dimension, you can leave it as BMS_ID (the system will automatically use this key if no appropriate field can be found).
If the component will be used as a dimension, then create an appropriate field (you must adhere to the rules mentioned above) and use this field.

Changes to the SAI360 database structure (after data warehouse database has been configured and built)

IMPORTANT:

Before making the below changes in an existing system, read up about the potential consequences.

Renaming a component, field or relationship in SAI360
- The renamed component, field or relationship will be added as a new component, field or relationship in the data warehouse database. This ensures that existing reports continue to work.
- Any existing reports will need to be updated to use the new component, field or relationship as the ETL will populate data in the new component, field or relationship and stop populating data in the old component, field or relationship.
Removing a component from the data warehouse or deleting a field or relationship
- Components, fields or relationships are never automatically deleted in the Data Warehouse database by the ETL process. It will need to be done manually using scripts or any SQL tools.