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.
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.
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 |
|