Tabular Model - Procurement

Usage/Purpose
Fact Tables
Dimension Tables
Configurations
Limitations
Relationships

Usage/Purpose

Main analysis scenarios are:

Procurement analysis over time. Cost, volumes, suppliers, purchase groups etc
Vendor delivery precision over time
Charges
Spend Analysis

Fact Tables

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
CURRENCY RATES	FACT_CURRENCY_RATES_TM	FACT_CURRENCY_RATES_BI	FACT_CURRENCY_RATES	On Line
PURCHASE ORDER CHARGE	FACT_PURCHASE_ORDER_CHARGE_TM	FACT_PURCHASE_ORDER_CHARGE_BI	FACT_PURCHASE_ORDER_CHARGE	Data Mart
PURCHASE ORDER LINE	FACT_PURCHASE_ORDER_LINE_TM	FACT_PURCHASE_ORDER_LINE_BI	FACT_PURCHASE_ORDER_LINE	Data Mart
SPEND ANALYSIS	FACT_SPEND_ANALYSIS_TM	FACT_SPEND_ANALYSIS_BI	FACT_SPEND_ANALYSIS	Data Mart

Corresponding Information Sources are described below. Detailed information about Information Sources can be found in About Information Sources.

FACT_PURCHASE_ORDER_LINE
Purchase Order Line information source is based on IFS\Purchase Orders. This information source can be used to design reports and queries about suppliers and purchase order lines. Main focus is on purchase amounts and delivery performance.

Purchase Order Analysis
Supplier Performance in terms of time and quality

FACT_PURCHASE_ORDER_CHARGE
Purchase Order Line information source is based on IFS\Purchase Charges. This information source can be used to design reports and queries about charges connected to purchase orders.

Analysis on charges per project
Analysis on charges per supplier and/or part

FACT_SPEND ANALYSIS
Spend Analysis information source is based on IFS\Spend Analysis. This information source can be used to design reports and queries about spend amounts based on purchase orders, supplier invoices and maverick spend.

Analysis on spend per category
Analysis on spend per source type (purchase order, agreements, references)

Dimension Tables

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
ACCOUNT	DIM_ACCOUNT_TM	DIM_ACCOUNT_BI	DIM_ACCOUNT	On Line
ACTIVITY	DIM_ACTIVITY_BI	DIM_ACTIVITY_BI	DIM_ACTIVITY	On Line
CAT ASSORTMENT	DIM_PROC_CAT_ASSORTMENT_BI	DIM_PROC_CAT_ASSORTMENT_BI	DIM_PROC_CAT_ASSORTMENT	On Line
CAT ASSORTMENT NODE	DIM_PROC_CAT_ASSORTMNT_NODE_TM	DIM_PROC_CAT_ASSORTMNT_NODE_BI	DIM_PROC_CAT_ASSORTMNT_NODE	On Line
CODE B	DIM_CODE_B_TM	DIM_CODE_B_BI	DIM_CODE_B	On Line
CODE C	DIM_CODE_C_TM	DIM_CODE_C_BI	DIM_CODE_C	On Line
CODE D	DIM_CODE_D_TM	DIM_CODE_D_BI	DIM_CODE_D	On Line
CODE E	DIM_CODE_E_TM	DIM_CODE_E_BI	DIM_CODE_E	On Line
CODE F	DIM_CODE_F_TM	DIM_CODE_F_BI	DIM_CODE_F	On Line
CODE G	DIM_CODE_G_TM	DIM_CODE_G_BI	DIM_CODE_G	On Line
CODE H	DIM_CODE_H_TM	DIM_CODE_H_BI	DIM_CODE_H	On Line
CODE I	DIM_CODE_I_TM	DIM_CODE_I_BI	DIM_CODE_I	On Line
CODE J	DIM_CODE_J_TM	DIM_CODE_J_BI	DIM_CODE_J	On Line
COMPANY	DIM_COMPANY_BI	DIM_COMPANY_BI	DIM_COMPANY	On Line
CONDITION CODE	DIM_CONDITION_CODE_BI	DIM_CONDITION_CODE_BI	DIM_CONDITION_CODE	On Line
CREATED DATE	DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME	On Line
EXTERNAL SERVICE TYPE	DIM_EXTERNAL_SERVICE_TYPE_BI	DIM_EXTERNAL_SERVICE_TYPE_BI	DIM_EXTERNAL_SERVICE_TYPE	On Line
FORWARDER	DIM_FORWARDER_BI	DIM_FORWARDER_BI	DIM_FORWARDER	On Line
INSPECTION CODE	DIM_INSPECTION_CODE_BI	DIM_INSPECTION_CODE_BI	DIM_INSPECTION_CODE	On Line
INTERNAL DESTINATION	DIM_INTERNAL_DESTINATION_BI	DIM_INTERNAL_DESTINATION_BI	DIM_INTERNAL_DESTINATION	On Line
INVENTORY LOCATION	DIM_INVENTORY_LOCATION_BI	DIM_INVENTORY_LOCATION_BI	DIM_INVENTORY_LOCATION	On Line
INVENTORY PART	DIM_INVENTORY_PART_TM	DIM_INVENTORY_PART_BI	DIM_INVENTORY_PART	On Line
ISO UNIT	DIM_ISO_UNIT_TM	DIM_ISO_UNIT_BI	DIM_ISO_UNIT	On Line
ORDER CANCEL REASON	DIM_ORDER_CANCEL_REASON_bi	DIM_ORDER_CANCEL_REASON_BI	DIM_ORDER_CANCEL_REASON	On Line
ORDER COORDINATOR	DIM_ORDER_COORDINATOR_BI	DIM_ORDER_COORDINATOR_BI	DIM_ORDER_COORDINATOR	On Line
ORDER DELIVERY TERM	DIM_ORDER_DELIVERY_TERM_BI	DIM_ORDER_DELIVERY_TERM_BI	DIM_ORDER_DELIVERY_TERM	On Line
PROJECT	DIM_PROJECT_BI	DIM_PROJECT_BI	DIM_PROJECT	On Line
PURCHASE BUYER	DIM_PURCHASE_BUYER_BI	DIM_PURCHASE_BUYER_BI	DIM_PURCHASE_BUYER	On Line
PURCHASE CHARGE TYPE	DIM_PURCHASE_CHARGE_TYPE_BI	DIM_PURCHASE_CHARGE_TYPE_BI	DIM_PURCHASE_CHARGE_TYPE	On Line
PURCHASE GROUP	DIM_PURCHASE_GROUP_BI	DIM_PURCHASE_GROUP_BI	DIM_PURCHASE_GROUP	On Line
PURCHASE PART	DIM_PURCHASE_PART_BI	DIM_PURCHASE_PART_BI	DIM_PURCHASE_PART	On Line
QUALITY CONTROL ANALYST	DIM_QUALITY_CTRL_ANALYST_BI	DIM_QUALITY_CTRL_ANALYST_BI	DIM_QUALITY_CTRL_ANALYST	On Line
REPORTING CURRENCY	DIM_REPORTING_CURRENCY_TM	DIM_CURRENCY_RATE_TYPE_BI	DIM_CURRENCY_RATE_TYPE	On Line
REPORTING DATE	DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME	On Line
REPORTING PERIOD	DIM_REPORTING_PERIOD__TM	DIM_RDP_COMPANY_PERIOD_BI	DIM_RDP_COMPANY_PERIOD	On Line
REQUISITIONER	DIM_REQUISITIONER_BI	DIM_REQUISITIONER_BI	DIM_REQUISITIONER	On Line
SALES REPRESENTATIVE	DIM_BUSINESS_REP_BI	DIM_BUSINESS_REP_BI	DIM_BUSINESS_REP	On Line
SHIP VIA	DIM_MPCCOM_SHIP_VIA_BI	DIM_MPCCOM_SHIP_VIA_BI	DIM_MPCCOM_SHIP_VIA	On Line
SITE	DIM_SITE_BI	DIM_SITE_BI	DIM_SITE	On Line
SUB PROJECT	DIM_SUB_PROJECT_TM	DIM_SUB_PROJECT_BI	DIM_SUB_PROJECT	On Line
SUPPLIER	DIM_SUPPLIER_BI	DIM_SUPPLIER_BI	DIM_SUPPLIER	On Line
SUPPLIER ASSORTMENT	DIM_SUPPLIER_ASSORTMENT_BI	DIM_SUPPLIER_ASSORTMENT_BI	DIM_SUPPLIER_ASSORTMENT	On Line
SUPPLIER STAT GROUP	DIM_SUPPLIER_STAT_GROUP_BI	DIM_SUPPLIER_STAT_GROUP_BI	DIM_SUPPLIER_STAT_GROUP	On Line
TECHNICAL COORDINATOR	DIM_TECHNICAL_COORDINATOR_BI	DIM_TECHNICAL_COORDINATOR_BI	DIM_TECHNICAL_COORDINATOR	On Line
WANTED DELIVERY DATE	DIM_BI_TIME_BI	DIM_BI_TIME_BI	DIM_BI_TIME	On Line

Configurations

The Procurement model supports calculation of base currency amounts to a reporting currency. To make this work it is necessary to create/select a currency rate type in one company as the rate source. The relevant configuration lookup must also be defined.

Please refer technical documentation for more information.

Limitations

The Procurement model is using the Company Reporting Period dimension, which means that it is necessary to create a specific Reporting Period Definition to be used by this cube. The created/selected identity must also be defined in the lookup configuration for the Tabular Model, using Source = 'Procurement'.

Please refer technical documentation for more information.

Relationships

The following table lists the relationships between tables and columns in the model.

Relationship: Table (column) - Table (column)

CURRENCY RATES (Dim_Reporting_Currency_ID) - REPORTING CURRENCY (ID)

CURRENCY RATES (Dim_Time_ID) - REPORTING DATE (ID)

PURCHASE ORDER CHARGE (Dim_Activity_Id) - ACTIVITY (ID)

PURCHASE ORDER CHARGE (DIM_CHARGE_TYPE_ID) - PURCHASE CHARGE TYPE (ID)

PURCHASE ORDER CHARGE (DIM_COMPANY_ID) - COMPANY (ID)

PURCHASE ORDER CHARGE (DIM_PO_ORDER_DATE_ID) - CREATED DATE (ID)

PURCHASE ORDER CHARGE (Dim_Project_Id) - PROJECT (ID)

PURCHASE ORDER CHARGE (Dim_Reporting_Date_ID) - REPORTING DATE (ID)

PURCHASE ORDER CHARGE (Dim_Reporting_Period_ID) - REPORTING PERIOD (Company-Date)

PURCHASE ORDER CHARGE (DIM_SITE_ID) - SITE (ID)

PURCHASE ORDER CHARGE (DIM_SUB_PROJECT_ID) - SUB PROJECT (ID)

PURCHASE ORDER LINE (DIM_ACTIVITY_ID) - ACTIVITY (ID)

PURCHASE ORDER LINE (DIM_COMPANY_ID) - COMPANY (ID)

PURCHASE ORDER LINE (DIM_CONDITION_CODE_ID) - CONDITION CODE (ID)

PURCHASE ORDER LINE (DIM_EXTERNAL_SERVICE_TYPE_ID) - EXTERNAL SERVICE TYPE (ID)

PURCHASE ORDER LINE (DIM_FORWARDER_ID) - FORWARDER (ID)

PURCHASE ORDER LINE (DIM_INSPECTION_CODE_ID) - INSPECTION CODE (ID)

PURCHASE ORDER LINE (DIM_INTERNAL_DESTINATION_ID) - INTERNAL DESTINATION (ID)

PURCHASE ORDER LINE (DIM_INVENTORY_LOCATION_ID) - INVENTORY LOCATION (ID)

PURCHASE ORDER LINE (Dim_Inventory_Part_ID) - INVENTORY PART (ID)

PURCHASE ORDER LINE (DIM_MPCCOM_SHIP_VIA_ID) - SHIP VIA (ID)

PURCHASE ORDER LINE (DIM_ORDER_CANCEL_REASON_ID) - ORDER CANCEL REASON (ID)

PURCHASE ORDER LINE (DIM_ORDER_COORDINATOR_ID) - ORDER COORDINATOR (ID)

PURCHASE ORDER LINE (DIM_ORDER_DELIVERY_TERM_ID) - ORDER DELIVERY TERM (ID)

PURCHASE ORDER LINE (DIM_POL_WANTED_DEL_DATE_ID) - WANTED DELIVERY DATE (ID)

PURCHASE ORDER LINE (DIM_PO_DATE_ENTERED_ID) - CREATED DATE (ID)

PURCHASE ORDER LINE (DIM_PROJECT_ID) - PROJECT (ID)

PURCHASE ORDER LINE (DIM_PURCHASE_BUYER_ID) - PURCHASE BUYER (ID)

PURCHASE ORDER LINE (DIM_PURCHASE_GROUP_ID) - PURCHASE GROUP (ID)

PURCHASE ORDER LINE (DIM_PURCHASE_PART_ID) - PURCHASE PART (ID)

PURCHASE ORDER LINE (DIM_QUALITY_CTRL_ANALYST_ID) - QUALITY CONTROL ANALYST (ID)

PURCHASE ORDER LINE (Dim_Reporting_Date_ID) - REPORTING DATE (ID)

PURCHASE ORDER LINE (Dim_Reporting_Period_ID) - REPORTING PERIOD (Company-Date)

PURCHASE ORDER LINE (DIM_REQUISITIONER_ID) - REQUISITIONER (ID)

PURCHASE ORDER LINE (DIM_SITE_ID) - SITE (ID)

PURCHASE ORDER LINE (DIM_SUB_PROJECT_ID) - SUB PROJECT (ID)

PURCHASE ORDER LINE (DIM_SUPPLIER_ID) - SUPPLIER (ID)

PURCHASE ORDER LINE (DIM_SUPPLIER_STAT_GROUP_ID) - SUPPLIER STAT GROUP (ID)

PURCHASE ORDER LINE (DIM_TECHNICAL_COORDINATOR_ID) - TECHNICAL COORDINATOR (ID)

SPEND ANALYSIS (DIM_ACCOUNT_ID) - ACCOUNT (ID)

SPEND ANALYSIS (DIM_ACTIVITY_ID) - ACTIVITY (ID)

SPEND ANALYSIS (DIM_CATEGORY_ASSORTMENT_ID) - CAT ASSORTMENT (ID)

SPEND ANALYSIS (DIM_CATEGORY_ASSORTMNT_NODE_ID) - CAT ASSORTMENT NODE (ID)

SPEND ANALYSIS (DIM_CHARGE_TYPE_ID) - PURCHASE CHARGE TYPE (ID)

SPEND ANALYSIS (DIM_CODE_B_ID) - CODE B (ID)

SPEND ANALYSIS (DIM_CODE_C_ID) - CODE C (ID)

SPEND ANALYSIS (DIM_CODE_D_ID) - CODE D (ID)

SPEND ANALYSIS (DIM_CODE_E_ID) - CODE E (ID)

SPEND ANALYSIS (DIM_CODE_F_ID) - CODE F (ID)

SPEND ANALYSIS (DIM_CODE_G_ID) - CODE G (ID)

SPEND ANALYSIS (DIM_CODE_H_ID) - CODE H (ID)

SPEND ANALYSIS (DIM_CODE_I_ID) - CODE I (ID)

SPEND ANALYSIS (DIM_CODE_J_ID) - CODE J (ID)

SPEND ANALYSIS (DIM_COMPANY_ID) - COMPANY (ID)

SPEND ANALYSIS (DIM_EXTERNAL_SERVICE_TYPE_ID) - EXTERNAL SERVICE TYPE (ID)

SPEND ANALYSIS (DIM_MAIN_REPRESENTATIVE_ID) - SALES REPRESENTATIVE (ID)

SPEND ANALYSIS (DIM_PROJECT_ID) - PROJECT (ID)

SPEND ANALYSIS (DIM_PURCHASE_BUYER_ID) - PURCHASE BUYER (ID)

SPEND ANALYSIS (DIM_PURCHASE_GROUP_ID) - PURCHASE GROUP (ID)

SPEND ANALYSIS (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)

SPEND ANALYSIS (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)

SPEND ANALYSIS (DIM_SITE_ID) - SITE (ID)

SPEND ANALYSIS (DIM_SUPPLIER_ASSORTMENT_ID) - SUPPLIER ASSORTMENT (ID)

SPEND ANALYSIS (DIM_SUPPLIER_ID) - SUPPLIER (ID)