Organization Levels View

The Organization Levels view is a helper view used for representing the organizational hierarchy captured in the YFS_ORGANIZATION table. This view should be customized to reflect your organization hierarchy before the view is created.

The top of the organization corresponds to ORGANIZATION_KEY_0 that is the ORGANIZATION_KEY column from the YFS_ORGANIZATION table, next level is ORGANIZATION_KEY_1. The last level is the one where your transactional data is stored. This level must correspond to ORGANIZATION_KEY. For each parent organization, use (uncomment) 1 ORGANIZATION_KEY_n starting from 0. So, if you have 3 levels in the organization, your view SQL will look as shown in the following example.

Figure 1. Organization Levels View SQL
Create Or Replace View YNA_ORGANIZATION_LEVELS_VW ( 
 ORGANIZATION_KEY_0,  
 ORGANIZATION_KEY_1,  
-- ORGANIZATION_KEY_2,  
-- ORGANIZATION_KEY_3,  
-- The last key must be called ORGANIZATION_KEY. This is the level at which the 
transaction detail (Orders) is stored.
-- Transformer uses this column name in the Level input sources to link to the 
Transaction input sources.
 ORGANIZATION_KEY )  
As Select 
O.ORGANIZATION_KEY,  
O1.ORGANIZATION_KEY,  
O2.ORGANIZATION_KEY -- The Organization level at which data is stored
--,  O3.ORGANIZATION_KEY,  
--O4.ORGANIZATION_KEY,  
From 
YFS_ORGANIZATION O 
, YFS_ORGANIZATION O1
, YFS_ORGANIZATION O2
--, YFS_ORGANIZATION O3
--, YFS_ORGANIZATION O4
Where 
(O.PARENT_ORGANIZATION_CODE Is Null Or O.PARENT_ORGANIZATION_CODE = ' ') 
And O1.PARENT_ORGANIZATION_CODE (+) = O.ORGANIZATION_CODE
And O2.PARENT_ORGANIZATION_CODE (+) = O1.ORGANIZATION_CODE
--And O3.PARENT_ORGANIZATION_CODE (+) = O2.ORGANIZATION_CODE
--And O4.PARENT_ORGANIZATION_CODE (+) = O3.ORGANIZATION_CODE

;

Each ORGANIZATION_KEY_n in this view can be used as a level in the organization hierarchy dimension in cubes that have data at the lowest ORGANIZATION_KEY level and also contain the ORGANIZATION_KEY in the IQD.

Note: If the transactional data is stored at different levels within the organization, the data will not rollup correctly and you may get “orphan” categories in transformer.
Note: Since the organization hierarchy depth is defined at view creation time, this dimension works for multiple organizations only if each has the same number of levels. You may have to create dummy hierarchies to achieve this. Otherwise, a given view (with n levels) will only display part of the hierarchy for organizations with more than n levels. It is recommended that you either create dummy hierarchies, or, create a view with the minimum number of levels that are present in all the organizations in your data.
Table 1. Organization Levels View
View Description Interpreted Columns SQL Filename

YNA_ORGANIZATION_

LEVELS_VW

This view exposes the data in the YFS_ORGANIZATION table. It is a helper view used for representing the organizational hierarchy captured in the YFS_ORGANIZATION table. This view should be customized to reflect your organization hierarchy before the view is created.

None
CONFIGURATION/
Organization_Levels.sql