Tabular Model - Sales

Contents

Usage/Purpose

Main analysis scenarios are:

Fact Tables

Model Table DW Source view (SQL Server) BI Access View Referenced Information Source Recommended Access Type
CURRENCY RATES FACT_CURRENCY_RATES_TMFACT_CURRENCY_RATES_BI FACT_CURRENCY_RATESOn Line
CUSTOMER INVOICEFACT_CUSTOMER_INVOICE_TM FACT_CUSTOMER_INVOICE_BIFACT_CUSTOMER_INVOICEData Mart
CUSTOMER ORDER CHARGEFACT_CUSTOMER_ORDER_CHARGE_TM FACT_CUSTOMER_ORDER_CHARGE_BIFACT_CUSTOMER_ORDER_CHARGE Data Mart
CUSTOMER ORDER LINEFACT_CUSTOMER_ORDER_LINE_TM FACT_CUSTOMER_ORDER_LINE_BIFACT_CUSTOMER_ORDER_LINE Data Mart
RETURN MATERIAL CHARGEFACT_RETURN_MATERIAL_CHARGE_TM FACT_RETURN_MATERIAL_CHARGE_BIFACT_RETURN_MATERIAL_CHARGE Data Mart
RETURN MATERIAL LINEFACT_RETURN_MATERIAL_LINE FACT_RETURN_MATERIAL_LINEFACT_RETURN_MATERIAL_LINE Data Mart

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

FACT_CUSTOMER_INVOICE
Customer Invoices information source is based on IFS\Customer Invoices. This information source can be used to design reports and queries about customers and customer invoices. The customer invoice amount and tax amount in transaction currency and accounting currency together with open amount for invoices can be analyzed in different dimensions such as company, customer, invoice type, and the code string.

FACT_CUSTOMER_ORDER_CHARGE
Customer Order charge information source is based on IFS\Customer Order Charges. The main purpose of this information source is to present data based on the charges connected to the customer orders to support high level summarizations and analyzes to evaluate sales including its charges.

FACT_CUSTOMER_ORDER_LINE
Customer Order Line information source is based on IFS\Customer Order Lines. The main purpose of this information source is to present data based on the customer order lines to support high level summarizations and analyzes to evaluate sales, for instance, using the Count Perfect Order metrics.

FACT_RETURN_MATERIAL_CHARGE
Return Material Charge Information Source is based on IFS\Return Material Authorization. The main purpose of this information source is to present data based on the credit charges connected to the return material authorizations to support high level summarizations and analyzes to evaluate sales including its returns and charges.

FACT_RETURN_MATERIAL_LINE
Return Material Charge Line Source is based on IFS\Return Material Authorization. The main purpose of this information source is to present data based on the return material authorization lines to support high level summarizations and analyzes to evaluate sales including returns.

Dimension Tables

Model Table DW Source view (SQL Server) BI Access View Referenced Information Source Recommended Access Type
ACCOUNTING PERIOD DIM_ACCOUNTING_PERIOD_BIDIM_ACCOUNTING_PERIOD_BI DIM_ACCOUNTING_PERIODOn Line
ACTIVITYDIM_ACTIVITY_BIDIM_ACTIVITY_BI DIM_ACTIVITYOn Line
CAMPAIGNDIM_CAMPAIGN_BIDIM_CAMPAIGN_BI DIM_CAMPAIGNOn Line
COL ACTUAL DEL DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL CREATED DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
COL FIRST ACT SHIP DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL LAST ACT SHIP DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL PLANNED DEL DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL PLANNED DUE DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL PLANNED SHIP DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL PRICE EFF DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
COL PROMISED DEL DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COL WANTED DEL DATEDIM_BI_TIME_BI DIM_BI_TIME_BIDIM_BI_TIMEOn Line
COMPANYDIM_COMPANY_BIDIM_COMPANY_BI DIM_COMPANYOn Line
COMPANY CUSTOMERDIM_COMPANY_CUSTOMER_BI DIM_COMPANY_CUSTOMER_BIDIM_COMPANY_CUSTOMEROn Line
CONDITION CODEDIM_CONDITION_CODE_BI DIM_CONDITION_CODE_BIDIM_CONDITION_CODEOn Line
CONFIGURATION SPECDIM_CONFIGURATION_SPEC_BI DIM_CONFIGURATION_SPEC_BIDIM_CONFIGURATION_SPECOn Line
CREDIT APPROVERDIM_ORDER_COORDINATOR_BI DIM_ORDER_COORDINATOR_BIDIM_ORDER_COORDINATOROn Line
CUST ORDER TYPEDIM_CUST_ORDER_TYPE_BI DIM_CUST_ORDER_TYPE_BIDIM_CUST_ORDER_TYPEOn Line
CUSTOMERDIM_CUSTOMER_BIDIM_CUSTOMER_BI DIM_CUSTOMEROn Line
CUSTOMER ADDRESSDIM_CUSTOMER_INFO_ADDRESS_SALES_TM DIM_CUSTOMER_INFO_ADDRESS_BIDIM_CUSTOMER_INFO_ADDRESS On Line
CUSTOMER INVOICE TYPEDIM_CUSTOMER_INVOICE_TYPE_BI DIM_CUSTOMER_INVOICE_TYPE_BIDIM_CUSTOMER_INVOICE_TYPE On Line
DELIVERY TYPEDIM_DELIVERY_TYPE_BI DIM_DELIVERY_TYPE_BIDIM_DELIVERY_TYPEOn Line
INVENTORY PARTDIM_INVENTORY_PART_TM DIM_INVENTORY_PART_BIDIM_INVENTORY_PARTOn Line
INVOICE DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
INVOICE DUE DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
ISO UNITDIM_ISO_UNIT_TMDIM_ISO_UNIT_BI DIM_ISO_UNITOn Line
ORDER CANCEL REASONDIM_ORDER_CANCEL_REASON_bi DIM_ORDER_CANCEL_REASON_BIDIM_ORDER_CANCEL_REASON On Line
ORDER CURRENCYDIM_CURRENCY_CODE_BI DIM_CURRENCY_CODE_BIDIM_CURRENCY_CODEOn Line
ORDER DELIVERY TERMDIM_ORDER_DELIVERY_TERM_BI DIM_ORDER_DELIVERY_TERM_BIDIM_ORDER_DELIVERY_TERM On Line
PROJECT DIM_PROJECT_BIDIM_PROJECT_BIDIM_PROJECTOn Line
REPORTING CURRENCYDIM_REPORTING_CURRENCY_TM DIM_CURRENCY_RATE_TYPE_BIDIM_CURRENCY_RATE_TYPEOn Line
REPORTING DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
REPORTING PERIODDIM_REPORTING_PERIOD__TM DIM_RDP_COMPANY_PERIOD_BIDIM_RDP_COMPANY_PERIODOn Line
RETURN APPROVERDIM_ORDER_COORDINATOR_BI DIM_ORDER_COORDINATOR_BIDIM_ORDER_COORDINATOROn Line
RETURN MATERIAL REASONDIM_RETURN_MATERIAL_REASON_BI DIM_RETURN_MATERIAL_REASON_BIDIM_RETURN_MATERIAL_REASON On Line
RM REQUESTED DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
RMC ENTERED DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
RML RETURNED DATEDIM_BI_TIME_BIDIM_BI_TIME_BI DIM_BI_TIMEOn Line
SALES CHARGE GROUPDIM_SALES_CHARGE_GROUP_BI DIM_SALES_CHARGE_GROUP_BIDIM_SALES_CHARGE_GROUPOn Line
SALES CHARGE TYPEDIM_SALES_CHARGE_TYPE_BI DIM_SALES_CHARGE_TYPE_BIDIM_SALES_CHARGE_TYPEOn Line
SALES DISTRICTDIM_SALES_DISTRICT_BI DIM_SALES_DISTRICT_BIDIM_SALES_DISTRICTOn Line
SALES MARKETDIM_SALES_MARKET_BI DIM_SALES_MARKET_BIDIM_SALES_MARKETOn Line
SALES PARTDIM_SALES_PART_BIDIM_SALES_PART_BI DIM_SALES_PARTOn Line
SALES PERSONDIM_SALES_PERSON_BI DIM_SALES_PERSON_BIDIM_SALES_PERSONOn Line
SALES PROMOTION DEALDIM_SALES_PROMOTION_DEAL_BI DIM_SALES_PROMOTION_DEAL_BIDIM_SALES_PROMOTION_DEAL On Line
SALES REGIONDIM_SALES_REGION_BIDIM_SALES_REGION_BI DIM_SALES_REGIONOn Line
SHIP VIADIM_MPCCOM_SHIP_VIA_BI DIM_MPCCOM_SHIP_VIA_BIDIM_MPCCOM_SHIP_VIAOn Line
SITEDIM_SITE_BIDIM_SITE_BIDIM_SITE On Line
SUPPLIER DIM_SUPPLIER_BIDIM_SUPPLIER_BIDIM_SUPPLIER On Line
VOUCHER TYPEDIM_VOUCHER_TYPE_BIDIM_VOUCHER_TYPE_BI DIM_VOUCHER_TYPEOn Line

Configurations

The Sales 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 Sales 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 = 'Sales'.

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)
CUSTOMER INVOICE (DIM_ACCOUNTING_PERIOD_ID) - ACCOUNTING PERIOD (ID)
CUSTOMER INVOICE (DIM_COMPANY_CUSTOMER_ID) - COMPANY CUSTOMER (ID)
CUSTOMER INVOICE (DIM_COMPANY_ID) - COMPANY (ID)
CUSTOMER INVOICE (DIM_CURRENCY_CODE_ID) - ORDER CURRENCY (ID)
CUSTOMER INVOICE (DIM_CUSTOMER_ID) - CUSTOMER (ID)
CUSTOMER INVOICE (DIM_CUST_INV_TYPE_ID) - CUSTOMER INVOICE TYPE (ID)
CUSTOMER INVOICE (DIM_DUE_DATE_ID) - INVOICE DUE DATE (ID)
CUSTOMER INVOICE (DIM_INVOICE_DATE_ID) - INVOICE DATE (ID)
CUSTOMER INVOICE (DIM_PROJECT_ACTIVITY_ID) - ACTIVITY (ID)
CUSTOMER INVOICE (DIM_PROJECT_ID) - PROJECT (ID)
CUSTOMER INVOICE (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)
CUSTOMER INVOICE (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)
CUSTOMER ORDER CHARGE (DIM_ACTIVITY_ID) - ACTIVITY (ID)
CUSTOMER ORDER CHARGE (DIM_CAMPAIGN_ID) - CAMPAIGN (ID)
CUSTOMER ORDER CHARGE (DIM_COMPANY_ID) - COMPANY (ID)
CUSTOMER ORDER CHARGE (DIM_CURRENCY_CODE_ID) - ORDER CURRENCY (ID)
CUSTOMER ORDER CHARGE (DIM_CUSTOMER_ID) - CUSTOMER (ID)
CUSTOMER ORDER CHARGE (DIM_CUSTOMER_ORDER_TYPE_ID) - CUST ORDER TYPE (ID)
CUSTOMER ORDER CHARGE (DIM_DELIVERY_TYPE_ID) - DELIVERY TYPE (ID)
CUSTOMER ORDER CHARGE (DIM_ISO_UNIT_IS_CSID) - ISO UNIT (CODE_NEW)
CUSTOMER ORDER CHARGE (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)
CUSTOMER ORDER CHARGE (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)
CUSTOMER ORDER CHARGE (DIM_SALES_CHARGE_GROUP_ID) - SALES CHARGE GROUP (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_CHARGE_TYPE_ID) - SALES CHARGE TYPE (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_DISTRICT_ID) - SALES DISTRICT (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_MARKET_ID) - SALES MARKET (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_PART_ID) - SALES PART (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_PERSON_ID) - SALES PERSON (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_PROMOTION_DEAL_ID) - SALES PROMOTION DEAL (ID)
CUSTOMER ORDER CHARGE (DIM_SALES_REGION_ID) - SALES REGION (ID)
CUSTOMER ORDER CHARGE (DIM_SITE_ID) - SITE (ID)
CUSTOMER ORDER LINE (CUSTOMER) - CUSTOMER ADDRESS (ID)
CUSTOMER ORDER LINE (DIM_ACTIVITY_ID) - ACTIVITY (ID)
CUSTOMER ORDER LINE (DIM_COL_ACTUAL_DEL_DATE_ID) - COL ACTUAL DEL DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_CREATED_DATE_ID) - COL CREATED DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_FIRST_ACT_SHIP_DATE_ID) - COL FIRST ACT SHIP DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_LAST_ACT_SHIP_DATE_ID) - COL LAST ACT SHIP DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_PLANNED_DEL_DATE_ID) - COL PLANNED DEL DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_PLANNED_DUE_DATE_ID) - COL PLANNED DUE DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_PLANNED_SHIP_DATE_ID) - COL PLANNED SHIP DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_PRICE_EFF_DATE_ID) - COL PRICE EFF DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_PROMISED_DEL_DATE_ID) - COL PROMISED DEL DATE (ID)
CUSTOMER ORDER LINE (DIM_COL_WANTED_DEL_DATE_ID) - COL WANTED DEL DATE (ID)
CUSTOMER ORDER LINE (DIM_COMPANY_ID) - COMPANY (ID)
CUSTOMER ORDER LINE (DIM_CURRENCY_CODE_ID) - ORDER CURRENCY (ID)
CUSTOMER ORDER LINE (DIM_CUSTOMER_ID) - CUSTOMER (ID)
CUSTOMER ORDER LINE (DIM_CUST_ORDER_TYPE_ID) - CUST ORDER TYPE (ID)
CUSTOMER ORDER LINE (DIM_INVENTORY_PART_ID) - INVENTORY PART (ID)
CUSTOMER ORDER LINE (DIM_MPCCOM_SHIP_VIA_ID) - SHIP VIA (ID)
CUSTOMER ORDER LINE (DIM_ORDER_CANCEL_REASON_ID) - ORDER CANCEL REASON (ID)
CUSTOMER ORDER LINE (DIM_ORDER_DELIVERY_TERM_ID) - ORDER DELIVERY TERM (ID)
CUSTOMER ORDER LINE (DIM_PROJECT_ID) - PROJECT (ID)
CUSTOMER ORDER LINE (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)
CUSTOMER ORDER LINE (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)
CUSTOMER ORDER LINE (DIM_SALES_DISTRICT_ID) - SALES DISTRICT (ID)
CUSTOMER ORDER LINE (DIM_SALES_MARKET_ID) - SALES MARKET (ID)
CUSTOMER ORDER LINE (DIM_SALES_PART_ID) - SALES PART (ID)
CUSTOMER ORDER LINE (DIM_SALES_PERSON_ID) - SALES PERSON (ID)
CUSTOMER ORDER LINE (DIM_SALES_REGION_ID) - SALES REGION (ID)
CUSTOMER ORDER LINE (DIM_SITE_ID) - SITE (ID)
CUSTOMER ORDER LINE (DIM_SUPPLIER_ID) - SUPPLIER (ID)
RETURN MATERIAL CHARGE (DIM_ACTIVITY_ID) - ACTIVITY (ID)
RETURN MATERIAL CHARGE (DIM_COMPANY_ID) - COMPANY (ID)
RETURN MATERIAL CHARGE (DIM_CREDIT_APPROVER_ID) - CREDIT APPROVER (ID)
RETURN MATERIAL CHARGE (DIM_CURRENCY_CODE_ID) - ORDER CURRENCY (ID)
RETURN MATERIAL CHARGE (DIM_CUSTOMER_ID) - CUSTOMER (ID)
RETURN MATERIAL CHARGE (DIM_DELIVERY_TYPE_ID) - DELIVERY TYPE (ID)
RETURN MATERIAL CHARGE (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)
RETURN MATERIAL CHARGE (DIM_RETURN_APPROVER_ID) - RETURN APPROVER (ID)
RETURN MATERIAL CHARGE (DIM_RMC_DATE_ENTERED_ID) - RMC ENTERED DATE (ID)
RETURN MATERIAL CHARGE (DIM_RML_DATE_RETURNED_ID) - RML RETURNED DATE (ID)
RETURN MATERIAL CHARGE (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)
RETURN MATERIAL CHARGE (DIM_SALES_CHARGE_GROUP_ID) - SALES CHARGE GROUP (ID)
RETURN MATERIAL CHARGE (DIM_SALES_CHARGE_TYPE_ID) - SALES CHARGE TYPE (ID)
RETURN MATERIAL CHARGE (DIM_SITE_ID) - SITE (ID)
RETURN MATERIAL LINE (DIM_ACTIVITY_ID) - ACTIVITY (ID)
RETURN MATERIAL LINE (DIM_COMPANY_ID) - COMPANY (ID)
RETURN MATERIAL LINE (DIM_CONDITION_CODE_ID) - CONDITION CODE (ID)
RETURN MATERIAL LINE (DIM_CONFIGURATION_SPEC_ID) - CONFIGURATION SPEC (ID)
RETURN MATERIAL LINE (DIM_CREDIT_APPROVER_ID) - CREDIT APPROVER (ID)
RETURN MATERIAL LINE (DIM_CURRENCY_CODE_ID) - ORDER CURRENCY (ID)
RETURN MATERIAL LINE (DIM_CUSTOMER_ID) - CUSTOMER (ID)
RETURN MATERIAL LINE (DIM_DELIVERY_TYPE_ID) - DELIVERY TYPE (ID)
RETURN MATERIAL LINE (DIM_REPORTING_DATE_ID) - REPORTING DATE (ID)
RETURN MATERIAL LINE (DIM_RETURN_APPROVER_ID) - RETURN APPROVER (ID)
RETURN MATERIAL LINE (DIM_RETURN_MATERIAL_REASON_ID) - RETURN MATERIAL REASON (ID)
RETURN MATERIAL LINE (DIM_RML_DATE_RETURNED_ID) - RML RETURNED DATE (ID)
RETURN MATERIAL LINE (DIM_RM_DATE_REQUESTED_ID) - RM REQUESTED DATE (ID)
RETURN MATERIAL LINE (DIM_RPD_COMPANY_PERIOD_ID) - REPORTING PERIOD (Company-Date)
RETURN MATERIAL LINE (DIM_SALES_PART_ID) - SALES PART (ID)
RETURN MATERIAL LINE (DIM_SITE_ID) - SITE (ID)