Open ODS Views – Referential Integrity setting when mapping with InfoObject

See more in our hbite Open ODS Views

This post is to explain the referential integrity setting in the OpenODSView.

Create an openODSView of type facts based on HANA table as shown below

Create a query on this OpenODSView and see the data

Now create a Product infoobject with product name as navigation attribute

load data to this infoobject

Now in OpenODSView, associate this infoobject with the field Product ID

specify product name as navigation attribute

Check the checkbox for referential integrity for product id field.

Activate your OpenODSView again.

Add a record in fact table sales with a product id which is not present in master data of infoobject product

Execute your query on fact openODSView.

What do you observe?

Due to referential integrity option enabled, the new record with product id which is not present in master data is not shown.

This is because system performs referential join between fact table and product master table.

Only those records are shown from fact table for which master data exists in product table.

Now again to to your fact OpenODSView and deselect the option for referential integrity for product id field.

Save and activate your openODSView.

Execute the query again.

What do you see?

The record which is not present in master data is also shown

This is because system performs left outer join between tables fact and product master and show all the records from the fact table if referential integrity option is not set.

Note: Pay attention that once records are shown in report for which there is no master data, the same records are inserted in the master data table of the infoobject

These records are inserted during query execution because SIDs are created during query execution.

So be careful when referential integrity is not set for fields, extra records will get generated in the BW core infoobjects associated with the field wherever system finds no master data for a field in a transaction record.

See more in our hbite Open ODS Views