CAPITOLO 3 PROGETTAZIONE DEL DW
4.5 OBIEE MODEL
4.5.2 BUSINESS MODEL
La parte del lavoro riguardante la costruzione del modello OBIEE ha seguito lo schema descritto nel paragrafo precedente ed è stato realizzato mediante l’utilizzo di Oracle BI
Administration Tool. Di seguito saranno descritti i passaggi realizzati mediante l’ausilio
di immagini dimostrative del lavoro svolto.
PHYSICAL LAYER
La parte iniziale del lavoro è stata quella di importare i metadati delle tabelle interessate dalle analisi sui Kpi. Nel dettaglio le tabelle in questione sono le Fact Table e le Dimension Table realizzate mediante il lavoro di ETL discusso nella sezione precedente. Fase fondamentale in seguito all’importazione delle tabelle è quella di creazione degli Alias e dei join fisici che hanno portato alla creazione dei seguenti Schemi.
Figura 65 Physical Join Monosource Figura 64 Physical join Net Cash Flow
98
Nelle immagini raffigurate è presente un join (in rosso nell’immagine sopra) fittizio con la dimension table ‘DW_PURCHASE_POV_OBI’. Tale tabella è una tabella manuale creata sul Db e utile per gestire la visualizzazione dei dati sulla Dashboard e all’interno del quale sono presenti solo due record:
• Acual • YTD
L’utilizzo di questa tabella permette di vedere i dati inerenti alle date selezionate(Actual), oppure di visualizzare i dati Year To Date(YTD). Il Join fittizio è stato realizzato mettendo come condizione di join 1=1.
BUSINESS MODEL AND MAPPING
Terminata la fase dedicata al Physical layer è stato costruito il modello logico. All’interno del layer di riferimento sono state create le fact table logiche, le dimension table logiche, le gerarchie e sono state create le misure per i KPI analizzati. Per ogni singolo KPI verranno rappresentate le misure realizzate, le dimensioni a cui è legato le modalità di calcolo delle misure stesse e le gerarchie create. Per la realizzazione della seguente documentazione è stato utilizzato Indyco, un Tool di modellazione sviluppato da Iconsulting.
TOTAL PRICE SAVING
99
GLOSSARIO DELLE MISURE
Name Description Formula Source table Source field
Total Price Saving Point of View
CASE WHEN "DWPUR- PurchaseKPI"."Dim Point of View"."Point of View" = 'YTD' THEN "DWPUR- PurchaseKPI"."Fact Total Price Saving"."Total Price Saving Point of View (YTD)" ELSE "DWPUR-
PurchaseKPI"."Fact Total Price Saving"."Total Price Saving Point of View (Actual)" END Total Price Saving Point of View (Actual) Differenza tra initial price and final price (reference price) * total actual quantity F_PURCHASE_SAVING PRICE_SAVING_ACTUAL Total Price Saving Point of View (YTD)
ToDate( Total Price Saving Point of View (Actual) (in Total Price Saving), Year)
Total Price Saving Point of View vs
Total Price Saving Point of View (in Total Price Saving) / Total Price Saving Target (in Total Price Saving)
100
Name Description Formula Source table Source field
Target Total Price Saving PY
CASE WHEN Point of View (in Total Price Saving) = 'YTD' THEN Total Price Saving PY (YTD) (in Total Price Saving) ELSE Total Price Saving PY (Actual) (in Total Price Saving) END Total Price Saving PY (Actual)
Ago( Total Price Saving Point of View (Actual) (in Total Price Saving), Year, 1)
Total Price Saving PY (YTD)
ToDate( Total Price Saving PY (Actual) (in Total Price Saving) , Year)
Total Price Saving Target
CASE WHEN Point of View (in Total Price Saving) = 'YTD' THEN Total Price Saving Target (YTD) (in Total Price Saving) ELSE Total Price Saving Target (Actual) (in Total Price Saving) END Total Price Saving Target (Actual) F_PURCHASE_SAVING PRICE_SAVING_TARGET Total Price Saving Target (YTD)
ToDate(Total Price Saving Target (Actual) (in Total Price Saving) , Year)
ADDITIVITY MATRIX
Plant Purchasing Area Business Unit Year/Month Point of View
Total Price Saving Point of View (Actual) Sum Sum Sum Sum Sum Total Price Saving Target (Actual) Sum Sum Sum Sum Sum Total Price Saving PY (Actual) Sum Sum Sum Sum Sum Total Price Saving Point of View (YTD) Sum Sum Sum Sum Sum Total Price Saving Target (YTD) Sum Sum Sum Sum Sum Total Price Saving PY (YTD) Sum Sum Sum Sum Sum Total Price Saving Point of View Sum Sum Sum Sum Sum Total Price Saving Target Sum Sum Sum Sum Sum
101 Plant Purchasing Area Business Unit Year/Month Point of View
Total Price Saving PY Sum Sum Sum Sum Sum Total Price Saving Point of View vs Target
GERARCHIE 1. Business Unit
Dimensional attributes
Name Description Sample values Source table Source field Notes
Business Unit DIM_MATERIAL_PURCHASE BI_OBU
2. Plant
Dimensional attributes
Name Description Sample values Source table Source field Notes
Plant DIM_WAREHOUSE_PURCHASE PLANT_NAME Company Code 534 DIM_WAREHOUSE_PURCHASE COMPANY_NAME Country DIM_WAREHOUSE_PURCHASE CNT_DESCRIPTION Region DIM_WAREHOUSE_PURCHASE REGION
102
Name Description Sample values Source table Source field Notes
Company
3. Point of View
Dimensional attributes
Name Description Sample values Source table Source field Notes Point of View DW_PURCHASE_POV_OBI
4. Purchasing Area
Dimensional attributes
Name Description Sample values Source table Source field Notes
Purchasing Area DW_PURCHASING_AREA PURCHASING_AREA
5. Year/Month
Dimensional attributes
Name Description Sample values Source table Source field Notes
103
Year JANUARY DIM_TIME YEAR_CODE
NET CASH FLOW
GLOSSARIO DELLE MISURE
Name Description Formula Source table Source field
Net Cash Flow Point of View
CASE WHEN Point of View (in Net Cash Flow) = 'YTD' THEN Net Cash Flow Point of View (YTD) (in Net Cash Flow) ELSE Net Cash Flow Point of View (Actual) (in Net Cash Flow) END Net Cash Flow Point of View (Actual) F_PURCHASE_CASHFLOW NET_CASH_FLOW_ACTUAL Net Cash Flow Point of View (YTD)
ToDate( Net Cash Flow Point of View (Actual) (in Net Cash Flow) , Year)
Net Cash Flow Point of View vs Target
Net Cash Flow Point of View (in Net Cash Flow) / Net Cash Flow Target (in Net Cash Flow) Net Cash
Flow PY
CASE WHEN Point of View (in Net Cash Flow) = 'YTD' THEN Net Cash Flow PY (YTD) (in Net Cash Flow) ELSE Net Cash Flow PY (Actual) (in Net Cash Flow) END
104
Name Description Formula Source table Source field
Net Cash Flow PY (Actual)
Ago( Net Cash Flow Point of View (Actual) (in Net Cash Flow), Year, 1) Net Cash
Flow PY (YTD)
ToDate( Net Cash Flow PY (Actual) (in Net Cash Flow), Year)
Net Cash Flow Target
CASE WHEN Point of View (in Net Cash Flow) = 'YTD' THEN Net Cash Flow Target (YTD) (in Net Cash Flow) ELSE Net Cash Flow Target (YTD) (in Net Cash Flow) END Net Cash Flow Target (Actual) F_PURCHASE_CASHFLOW NET_CASH_FLOW_TARGET Net Cash Flow Target (YTD)
ToDate( Net Cash Flow Target (Actual) (in Net Cash Flow), Year)
ADDITIVITY MATRIX
Country Purchasing Area Year/Month Point of View
Net Cash Flow Point of View (Actual) Sum Sum Sum Sum Net Cash Flow Target (Actual) Sum Sum Sum Sum Net Cash Flow PY (Actual) Sum Sum Sum Sum Net Cash Flow Point of View (YTD) Sum Sum Sum Sum Net Cash Flow Target (YTD) Sum Sum Sum Sum Net Cash Flow PY (YTD) Sum Sum Sum Sum Net Cash Flow Point of View Sum Sum Sum Sum Net Cash Flow Target Sum Sum Sum Sum Net Cash Flow PY Sum Sum Sum Sum Net Cash Flow Point of View vs Target
GERARCHIE 1. Country
105 Dimensional attributes
Name Description Sample values Source table Source field Notes
Country DIM_WAREHOUSE_PURCHASE CNT_DESCRIPTION Region DIM_WAREHOUSE_PURCHASE REGION
2. Point of View
Dimensional attributes
Name Description Sample values Source table Source field Notes
Point of View DW_PURCHASE_POV_OBI
3. Purchasing Area
Dimensional attributes
Name Description Sample values Source table Source field Notes
Purchasing Area DW_PURCHASING_AREA PURCHASING_AREA
4. Year/Month
106
Name Description Sample values Source table Source field Notes
Year/Month DIM_PURCH_TIME_MONTH MONTH_CODE Year JANUARY DIM_TIME YEAR_CODE
MONOSOURCE RAW MATERIAL
GLOSSARIO DELLE MISURE
Name Description Formula Source table Source field
MonoSource RM 1 Point of View F_PURCHASE_MONOSOUCE_A F_PURCHASE_MONOSOUCE_1_1X MonoSource RM 1 PY Ago( MonoSource RM 1 Point of View (in MonoSource Product Supplier RM Actual), Year, 1) MonoSource RM 2 Point of View F_PURCHASE_MONOSOUCE_A F_PURCHASE_MONOSOUCE_2 MonoSource RM 2 PY Ago( MonoSource RM 2 Point of View (in Figura 71 DFM Monosource
107
Name Description Formula Source table Source field
MonoSource Product Supplier RM Actual) , Year, 1) MonoSource RM 2x Point of View F_PURCHASE_MONOSOUCE_A F_PURCHASE_MONOSOUCE_2X MonoSource RM 2x PY Ago( MonoSource RM 2x Point of View (in MonoSource Product Supplier RM Actual) , Year, 1) MonoSource RM 4 Point of View F_PURCHASE_MONOSOUCE_A F_PURCHASE_MONOSOUCE_4_4X MonoSource RM 4 PY Ago( MonoSource RM 4 Point of View (in MonoSource Product Supplier RM Actual) , Year, 1) ADDITIVITY MATRIX
Purchasing Area Plant Supplier Local Code Material Year Point of View
MonoSource RM 1 Point of View Avg Avg Avg Avg Avg Avg
MonoSource RM 2 Point of View Avg Avg Avg Avg Avg Avg
MonoSource RM 2x Point of View Avg Avg Avg Avg Avg Avg
MonoSource RM 4 Point of View Avg Avg Avg Avg Avg Avg
MonoSource RM 1 PY Avg Avg Avg Avg Avg Avg
MonoSource RM 2 PY Avg Avg Avg Avg Avg Avg
MonoSource RM 2x PY Avg Avg Avg Avg Avg Avg
MonoSource RM 4 PY Avg Avg Avg Avg Avg Avg
GERARCHIE 1. Material
108 Dimensional attributes
Name Description Sample values Source table Source field Notes
Material DIM_MATERIAL_PURCHASE MAT_DESCRIPTION Material Family DIM_MATERIAL_PURCHASE MATERIAL_FAMILY_DESC Business Unit DIM_MATERIAL_PURCHASE BI_OBU
2. Plant
Dimensional attributes
Name Description Sample values Source table Source field Notes
Plant DIM_WAREHOUSE_PURCHASE PLANT_NAME Company Code 534 DIM_WAREHOUSE_PURCHASE COMPANY_NAME Country DIM_WAREHOUSE_PURCHASE CNT_DESCRIPTION Region DIM_WAREHOUSE_PURCHASE REGION
109 Descriptive attributes
Name Description Sample values Source table Source field Notes
Company
3. Point of View
Dimensional attributes
Name Description Sample values Source table Source field Notes
Point of View DW_PURCHASE_POV_OBI
4. Purchasing Area
Dimensional attributes
Name Description Sample values Source table Source field Notes
Purchasing Area DW_PURCHASING_AREA PURCHASING_AREA
5. Supplier Local Code
Dimensional attributes
Name Description Sample values Source table Source field Notes
Supplier Local Code DIM_MIXED_SUPPLIER S_NAME Supplier Pan Code
Supplier Super Pan Code Supplier Code
110 Dimensional attributes
Name Description Sample values Source table Source field Notes
Year JANUARY DIM_TIME YEAR_CODE
ACTIVE SUPPLIER
Figura 72 DFM Active Supplier
GLOSSARIO DELLE MISURE
Name Description Formula Source table Source field
# of Active Suppliers (Rolling 12 Months) Point of View
PeriodRolling (Fact # of Active Suppliers.# of Active Suppliers Point of View , -11, 0)
# of Active Suppliers (Rolling 12 Months) Point of View - PAN
PeriodRolling(Fact # of Active Suppliers.# of Active Suppliers Point of View - PAN , -11, 0)
# of Active Suppliers (Rolling 12 Months) PY
PeriodRolling(Fact # of Active Suppliers.# of Active Suppliers Point of View - PAN , -23, -12)
# of Active Suppliers (Rolling 12 Months) PY - PAN
PeriodRolling(Fact # of Active Suppliers.# of Active Suppliers Point of View , -23, - 12)
# of Active Suppliers Point of View
count(distinct case when
DWPUR_PK_F_PURCHASE_ACTIVES UPP_A.ACTIVE_FLAG = 1 then DWPUR_PK_DIM_MIXED_SUPPLIER. F_PURCHASE_ACTIVE SUPP_A ACTIVE_FLA G
111
Name Description Formula Source table Source field
S_CODE else NULL end ) # of Active Suppliers
Point of View - PAN
count(distinct case when
DWPUR_PK_F_PURCHASE_ACTIVES UPP_A.ACTIVE_FLAG = 1 then DIM_MIXED_SUPPLIER.SUPP_PAN else NULL end )
F_PURCHASE_ACTIVE SUPP_A ACTIVE_FLA G # of Active Suppliers PY
Ago(Fact # of Active Suppliers.# of Active Suppliers Point of View", Dim Time.Year , 1)
# of Active Suppliers PY - PAN
Ago(Fact # of Active Suppliers.# of Active Suppliers Point of View - PAN , DWPUR-PurchaseKPI.Dim Time.Year, 1) ADDITIVITY MATRIX Purchasing Area Year/Month Point of View
Plant Source Supplier
# of Active Suppliers Point of View Count Count Count Count Count Count # of Active Suppliers (Rolling 12 Months) Point of
View
Count Count Count Count Count Count
# of Active Suppliers PY Count Count Count Count Count Count # of Active Suppliers (Rolling 12 Months) PY Count Count Count Count Count Count # of Active Suppliers Point of View - PAN Count Count Count Count Count Count # of Active Suppliers (Rolling 12 Months) Point of
View - PAN
Count Count Count Count Count Count
# of Active Suppliers PY - PAN Count Count Count Count Count Count # of Active Suppliers (Rolling 12 Months) PY - PAN Count Count Count Count Count Count
GERARCHIE 1. Purchasing Area
Dimensional attributes
Name Description Sample values Source table Source field Notes
Purchasing Area DW_PURCHASING_AREA PURCHASING_AREA
112 Dimensional attributes
Name Description Sample values Source table Source field Notes
Year/Month DIM_PURCH_TIME_MONTH MONTH_CODE Year JANUARY DIM_TIME YEAR_CODE
3. Point of View
Dimensional attributes
Name Description Sample values Source table Source field Notes
Point of View DW_PURCHASE_POV_OBI
4. Plant
Dimensional attributes
Name Description Sample values Source table Source field Notes
Plant DIM_WAREHOUSE_PURCHASE PLANT_NAME Company Code 534 DIM_WAREHOUSE_PURCHASE COMPANY_NAME Country DIM_WAREHOUSE_PURCHASE CNT_DESCRIPTION Region DIM_WAREHOUSE_PURCHASE REGION
113 Descriptive attributes
Name Description Sample values Source table Source field Notes
Company
5. Source
Dimensional attributes
Name Description Sample values Source table Source field Notes
Source F_PURCHASE_ACTIVESUPP_A MM/FI
7. Spot/Specialista
Dimensional attributes
Name Description Sample values Source table Source field Notes
Supplier Local Code DIM_MIXED_SUPPLIER S_CODE Spot Specialista DIM_MIXED_SUPPLIER ZVEN_TYPE
ACTIVE SUPPLIER
114
GLOSSARIO DELLE MISURE
Name Description Formula Source table Source field
% Call-Off Point of View
CASE WHEN Dim Point of View.Point of View = 'YTD' THEN Fact % Call-Off Orders Actual.% Call-Off Point of View (YTD) ELSE Fact % Call-Off Orders Actual.% Call-Off Point of View (Actual) END % Call-Off
Point of View (Actual)
Fact % Call-Off Orders Actual.Call-Off Quantity / Fact % Call-Off Orders Actual.Orders Quantity % Call-Off
Point of View (YTD)
ToDate(Fact % Call-Off Orders Actual.% Call-Off Point of View (Actual) , Dim Time.Year )
% Call-Off Point of View EUR
CASE WHEN Dim Point of View.Point of View = 'YTD' THEN Fact % Call-Off Orders Actual.% Call-Off Point of View EUR (YTD) ELSE Fact % Call-Off Orders Actual.% Call-Off Point of View EUR (Actual) END
% Call-Off Point of View EUR (Actual)
Fact % Call-Off Orders Actual.Spending Call-Off (EUR) / Fact % Call-Off Orders Actual.Spending (EUR) % Call-Off Point of View EUR (YTD) ToDate(Fact % Call-Off Orders Actual.% Call-Off Point of View EUR (Actual), Dim Time.Year)
% Call-Off Point of View Local
CASE Dim Point of View.Point of View = 'YTD' Fact % Call-Off Orders Actual.% Call-Off Point of View Local (YTD) ELSE Fact % Call-Off Orders Actual.% Call-Off Point of View Local (Actual) END % Call-Off
Point of View Local (Actual)
Fact % Call-Off Orders Actual.Spending Call-Off (Local Currency) / Fact % Call-Off Orders Actual.Spending (Local Currency) % Call-Off Point of View Local (YTD) ToDate(Fact % Call-Off Orders Actual.% Call-Off Point of View Local (Actual) , Dim Time.Year)
115
Name Description Formula Source table Source field
% Call-Off Point of View USD
CASE WHEN Dim Point of View.Point of View = 'YTD' Fact % Call-Off Orders Actual.% Call-Off Point of View USD (YTD) ELSE Fact % Call-Off Orders Actual.% Call-Off Point of View USD (Actual) END % Call-Off
Point of View USD (Actual)
Fact % Call-Off Orders Actual.Spending Call-Off (USD) / Fact % Call-Off Orders Actual.Spending (USD) % Call-Off Point of View USD (YTD) ToDate(Fact % Call-Off Orders Actual.% Call-Off Point of View USD (Actual), Dim Time.Year)
% Call-Off PY CASE Dim Point of View.Point of View = 'YTD' THEN Fact % Call-Off Orders Actual.% Call-Off PY (YTD) ELSE Fact % Call- Off Orders Actual.% Call-Off PY (Actual)END
% Call-Off PY (Actual)
Ago(Fact % Call-Off Orders Actual.% Call-Off Point of View (Actual), Dim Time.Year , 1) % Call-Off PY
(YTD)
ToDate(Fact % Call-Off Orders Actual.% Call-Off PY (Actual) , Dim Time.Year) % Call-Off PY
EUR
ToDate(Fact % Call-Off Orders Actual.% Call-Off PY (Actual) , Dim Time.Year) % Call-Off PY
EUR (Actual)
Ago(Fact % Call-Off Orders Actual.% Call-Off Point of View EUR (Actual) , Dim Time.Year , 1)
% Call-Off PY EUR (YTD)
ToDate(Fact % Call-Off Orders Actual.% Call-Off PY EUR (Actual) ,Dim Time.Year ) % Call-Off PY
Local
CASE Dim Point of View.Point of View= 'YTD' THEN Fact % Call-Off Orders Actual.% Call-Off PY Local (YTD) ELSE Fact % Call-Off Orders Actual.% Call-Off PY Local (Actual) END
% Call-Off PY Local (Actual)
Ago(Fact % Call-Off Orders Actual.% Call-Off Point of View Local (Actual) , Dim
116
Name Description Formula Source table Source field
Time.Year , 1) % Call-Off PY
Local (YTD)
ToDate(Fact % Call-Off Orders Actual.% Call-Off PY Local (Actual) ,Dim Time.Year ) % Call-Off PY
USD
CASE WHEN Dim Point of View.Point of View= 'YTD' Fact % Call-Off Orders Actual.% Call-Off PY USD (YTD) ELSE Fact % Call- Off Orders Actual.% Call-Off PY USD (Actual) END % Call-Off PY
USD (Actual)
Ago(Fact % Call-Off Orders Actual.% Call-Off Point of View USD (Actual) , Dim Time.Year , 1)
% Call-Off PY USD (YTD)
ToDate(Fact % Call-Off Orders Actual.% Call-Off PY USD (Actual) , DWPUR- PurchaseKPI.Dim Time.Year )
Call-Off Quantity
F_PURCHASE_CALLOFF_A QTY_CALLOFF
Orders Quantity F_PURCHASE_CALLOFF_A QTY_ORDERS Spending (EUR) F_PURCHASE_CALLOFF_A SPENDING_EUR Spending (Local
Currency)
F_PURCHASE_CALLOFF_A SPENDING_LC
Spending (USD) F_PURCHASE_CALLOFF_A SPENDING_USD Spending Call- Off (EUR) F_PURCHASE_CALLOFF_A CALLOFF_SPENDING_EUR Spending Call- Off (Local Currency) F_PURCHASE_CALLOFF_A CALLOFF_SPENDING_LC Spending Call- Off (USD) F_PURCHASE_CALLOFF_A CALLOFF_SPENDING_USD ADDITIVITY MATRIX
Purchasing Area Year Point of View Plant Material Spot/Specialista
Spending (EUR) Sum Sum Sum Sum Sum Sum
Spending (Local Currency) Sum Sum Sum Sum Sum Sum
Spending (USD) Sum Sum Sum Sum Sum Sum Spending Call-Off (EUR) Sum Sum Sum Sum Sum Sum
117
Purchasing Area Year Point of View Plant Material Spot/Specialista
Spending Call-Off (Local Currency) Sum Sum Sum Sum Sum Sum Spending Call-Off (USD) Sum Sum Sum Sum Sum Sum Call-Off Quantity Sum Sum Sum Sum Sum Sum % Call-Off Point of View (Actual)
% Call-Off Point of View (YTD) % Call-Off Point of View
% Call-Off Point of View EUR (Actual) % Call-Off Point of View EUR (YTD) % Call-Off Point of View EUR % Call-Off Point of View USD (Actual) % Call-Off Point of View USD (YTD) % Call-Off Point of View USD % Call-Off Point of View Local (Actual) % Call-Off Point of View Local (YTD) % Call-Off Point of View Local % Call-Off PY (Actual) % Call-Off PY (YTD) % Call-Off PY
% Call-Off PY EUR (Actual) % Call-Off PY EUR (YTD) % Call-Off PY EUR % Call-Off PY USD (Actual) % Call-Off PY USD (YTD) % Call-Off PY USD % Call-Off PY Local (Actual) % Call-Off PY Local (YTD) % Call-Off PY Local
Orders Quantity Sum Sum Sum Sum Sum Sum
GERARCHIE 1. Material
118 Dimensional attributes
Name Description Sample values Source table Source field Notes
Material DIM_MATERIAL_PURCHASE MAT_DESCRIPTION
Business Unit DIM_MATERIAL_PURCHASE BI_OBU
2. Plant
Dimensional attributes
Name Description Sample values Source table Source field Notes
Plant DIM_WAREHOUSE_PURCHASE PLANT_NAME Company Code 534 DIM_WAREHOUSE_PURCHASE COMPANY_NAME Country DIM_WAREHOUSE_PURCHASE CNT_DESCRIPTION Region DIM_WAREHOUSE_PURCHASE REGION
Descriptive attributes
Name Description Sample values Source table Source field Notes
Company
119 Dimensional attributes
Name Description Sample values Source table Source field Notes
Purchasing Area DW_PURCHASING_AREA PURCHASING_AREA
4. Point of View
Dimensional attributes
Name Description Sample values Source table Source field Notes