One important aspect of SAP BODS is that it enables us to create template tables in the desired data store within the tool itself and enables us to convert the template table into permanent physical table in the same data store. Template table plays a vital role in development and testing phases.
We often use template tables as staging tables. Template tables are typically used to store the data in an optimized way. There are number of use cases for using template table in table comparison such as:
1. Filtering target table based on source field: Filtering the data in comparison target table based on the source field is not possible in default table comparison filter method. In such scenario, we can filter target table based on source field and load the filtered data into template table which will be used as a comparison table in table comparison.
2. Using functions in table comparison filter: In some scenario, we use pushdown_sql (), in () etc., within the WHERE condition to filter the data. Such functions do not work as advertised within the default table comparison filter. In such cases, we will implement those functions in the transform level and load the target table data into a template table for comparison.
Table comparison typically looks up the target table with the input records and decides whether the input record is an insert, update or normal mode operation. Based on the mode, it will alter the target table. By default, Data Services doesn’t directly allow us to use the template table inside a table comparison, but this can be achieved through a workaround which we will see in detail below. Let’s jump into it.
- CUST_DIM (Physical Table) – Source Table
- CUST_TEMP (Template Table) – Target Table
- In the below scenario, I want to compare the records coming from CUST_DIM (physical table) with the template table CUST_TEMP using table comparison method.
- Click the table comparison and choose the datastore where your template is residing in the area highlighted in yellow.
- In my case, I kept my template table inside the datastore ‘SQL_SRT’ and I choose the same.
- Once you have chosen your datastore, you won’t find your template table in the list because data service will show the list of physical tables only. So, to get around this, you can choose any one of the physical table of your choice.
- In my case, I chosen MATCH_INFO physical table as I don’t find my template table CUST_TEMP.
- This table can be any table, but make sure you have at least a dummy physical table in your data store for this
- Once you chosen the physical table from the list, you will see the fields available in the physical table in the right side of the table comparison.
- In my case, MATCH_INFO physical table fields listed in the right side of the table comparison. But I need the fields of template table CUST_TEMP. We will see how to do that in the next step.
- Just remove the physical table name MATCH_INFO and replace the template table name CUST_TEMP. That’s it.
- Just come out of your table comparison after template table name change and get into table comparison again. Yes, you will see all the fields in the template table in the right side of the table comparison.
- In my case, I see all the fields in the template table CUST_TEMP in the right side of the table comparison.
- Now, you can drag your template table fields into primary key columns and compare columns in the table comparison transform as per your wish.
Got questions? Click here to get in touch.