When to use Navigational Attribute in SAP BW
Navigational attributes are discussed a lot of times in BW. They have an advantage as these attributes can be used for navigation in a query. However there are lot many more advantages of navigational attributes. I will discuss today one such scenario where navigational attributes has saved a week’s time.
The Scenario is like this-
There are 2 characteristics used in a cube. One characteristic is an attribute of another and both the info objects are included in the cube.
Info object Is Attribute of
Both country and region are included in the cube as separate characteristics and country is an attribute of Region. Hence the master data of REGION info object will look like below-
Now suppose the fact and dimension tables in the cube store a wrong combination of the region and country value-
Here the second and third entries in the fact table contain wrong combination of the region and country values.
Let’s suppose that according to business, the master data is correct but the entries in the fact table are not correct. This can happen if the source system has updated the master data but forgot to delete and reload the transaction data.
To make it more clear, in the source, this arises in the case when there are many bw systems being used in business and each one of them act as data silos with their own set of master data than the global system. (A business or company with lot of acquisitions)
Hence, now the target cube is having wrong combination of region and country values. These were uploaded as it is from the source which can be another local BW system.
The correction of these values can be done either by deleting complete data from the source and reloading with correct master data and transaction data. This may take a lot of time as data volume can be huge and this operation will be involving initialization loads. Then after source data correction, a reload is required in the target cube also since the dimension tables need to be recreated.
Here, the utilization of navigation attribute can be done to save time.
You need to change the region info object. In Region info object, the attribute country is set as navigation and given a unique name like Country [NAV] to distinguish it from the Country info object already present in the target cube.
Now you just have to change the front end query where you have to include the Country [NAV] info object from the attributes of Region instead of using the Country info object.
This will show the correct data in the query even though the data in the cube is wrong. Hence, the navigational attribute saved the time by avoiding the data reloads in the source and target.
The Query data will look like below-