Cube - Sales

Contents

Usage/Purpose

Main analysis scenarios are:

To see which dimensions that can be used with each fact in, for example Excel pivot analysis, see section Fact and Dimension Connections below.

 

Overview Facts

Cube Fact (DSV name) Measure Group Referenced Information Source  BI Access View Access Type
FACT_CUSTOMER_INVOICE CUSTOMER INVOICE FACT_CUSTOMER_INVOICE FACT_CUSTOMER_INVOICE_BI On Line
FACT_CUSTOMER_ORDER_CHARGE CUSTOMER ORDER CHARGE FACT_CUSTOMER_ORDER_CHARGE FACT_CUSTOMER_ORDER_CHARGE_BI On Line
FACT_CUSTOMER_ORDER_LINE CUSTOMER ORDER LINE FACT_CUSTOMER_ORDER_LINE FACT_CUSTOMER_ORDER_LINE_BI On Line
FACT_RETURN_MATERIAL_CHARGE RETURN MATERIAL CHARGE FACT_RETURN_MATERIAL_CHARGE FACT_RETURN_MATERIAL_CHARGE_BI On Line
FACT_RETURN_MATERIAL_LINE RETURN MATERIAL LINE FACT_RETURN_MATERIAL_LINE FACT_RETURN_MATERIAL_LINE_BI On Line
FACT_REPORTING_CURRENCY_RATES CURRENCY RATE FACT_CURRENCY_RATES FACT_CURRENCY_RATES_BI_OPT On Line

Each measure in the facts is described in Chapter Measures. Corresponding Information Sources are described below. Detalled infomation about Information Sources can be found in IFS Online Documentation.

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.

Overview Dimensions

Cube Dimension Note
ACCOUNT  
ACCOUNTING PERIOD  
ACTIVITY  
CAMPAIN  
CODE B  
CODE C  
CODE D  
CODE E  
CODE F  
CODE G  
CODE H  
CODE I  
CODE J  
COL ACTUAL DEL DATE  
COL CREATED DATE  
COL FIRST ACT SHIP DATE  
COL LAST ACT SHIP DATE  
COL PLANNED DEL DATE  
COL PLANNED DUE DATE  
COL PLANNED SHIP DATE  
COL PRICE EFF DATE  
COL PROMISED DEL DATE  
COL WANTED DEL DATE  
COMPANY CUSTOMER  
COMPANY  
COMPANY REPORTING PERIOD  
CONDITION CODE  
CONFIGURATION SPEC  
CREDIT APPROVER  
CUST ORDER TYPE  
CUSTOMER INVOICE TYPE  
CUSTOMER INVOICE Cube specific dimension (based on a Fact)
CUSTOMER ORDER CHARGE Cube specific dimension (based on a Fact)
CUSTOMER ORDER LINE Cube specific dimension (based on a Fact)
CUSTOMER  
DELIVERY TYPE  
INVENTORY PART  
INVOICE DATE  
INVOICE DUE DATE  
ISO UNIT  
ORDER CANCEL REASON  
ORDER CURRENCY  
ORDER DELIVERY TERM  
PROJECT  
REPORTING CURRENCY  
REPORTING DATE  
RETURN APPROVER  
RETURN MATERIAL CHARGE Cube specific dimension (based on a Fact)
RETURN MATERIAL LINE Cube specific dimension (based on a Fact)
RETURN MATERIAL REASON  
RM REQUESTED DATE  
RMC ENTERED DATE  
RML RETURNED DATE  
SALES CHARGE GROUP  
SALES CHARGE TYPE  
SALES DISTRICT  
SALES MARKET  
SALES PART  
SALES PERSON  
SALES PROMOTION DEAL  
SALES REGION  
SHIP VIA  
SITE  
SUPPLIER  
VOUCHER TYPE  

Configurations

The Sales cube 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 cube 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 ETL process, using Source = 'Sales'.

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

Fact and Dimension Connections

The following picture shows how different facts in the cube are connected to the available dimensions.




 

Measures

About Measures

Aggregation of measures is based on SUM if not explicitly noted.

This sections describes the basic measures in the cube. These are either measures defined in the core Information Source or in the ETL process. The columns have the following meaning:

 

Measure
(RC = Reporting Currency)		 Measure Group Core Measure ETL Measure Cube Measure Measure Expression Reporting Currency Measure Aggr. Method Note
Qty Sales CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM BUY_QTY_DUY in core fact
Qty Assigned CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Picked CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Short CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Shipped CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Shipdiff CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Returned CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Qty Invoiced CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Net Amount in Base Curr CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Net Amount in Order Curr CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Total Cost in Base Curr CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Total Order Line Discount CUSTOMER ORDER LINE YES NO NO NO NO SUM  
Count CO Line CUSTOMER ORDER LINE YES NO NO NO NO SUM Count of order lines that are not cancelled.
Count Early Order Lines CUSTOMER ORDER LINE YES NO NO NO NO SUM wanted_delivery_date > real_ship_date
Count Late Order Lines CUSTOMER ORDER LINE YES NO NO NO NO SUM wanted_delivery_date < real_ship_date
Count On Time Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM planned_ship_date = real_ship_date
Count Incomplete Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM buy_qty_due <> qty_shipped
Count Delivered Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM real_ship_date IS NOT NULL
Count Delivered Not Invoiced CUSTOMER ORDER LINE YES NO NO NO NO SUM if real_ship_date IS NOT NULL and not Partially Delivered or real_ship_date IS NULL and no Invoiced
Count Delivered as Requested CUSTOMER ORDER LINE YES NO NO NO NO SUM if real_ship_date IS NOT NULL and (real_ship_date+delivery_leadtime) < wanted_delivery_date
Count Delivered as Promised CUSTOMER ORDER LINE YES NO NO NO NO SUM if real_ship_date IS NOT NULL and (real_ship_date+delivery_leadtime) < promised_delivery_date)
Count Delivered as Planned CUSTOMER ORDER LINE YES NO NO NO NO SUM if real_ship_date IS NOT NULL and (real_ship_date+delivery_leadtime) < planed_delivery_date)
Count Perfect Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM  buy_qty_due=qty_shipped and planned_ship_date=real_ship_date
Count Agreement Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM Order Lines with price source AGREEMENT
Count PriceList Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM Order Lines with price source PRICELIST
Count BasePrice Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM Order Lines with price source BASE
Count Campaign Order Line CUSTOMER ORDER LINE YES NO NO NO NO SUM Order Lines with price source CAMPAIGN
Sum Ship Days Early CUSTOMER ORDER LINE YES NO NO NO NO SUM if planned_ship_date > real_ship_date then (planned_ship_date- real_ship_date) else 0
Sum Ship Days Late CUSTOMER ORDER LINE YES NO NO NO NO SUM if planned_ship_date < real_ship_date  then(real_ship_date-planned_ship_date) else 0
Sum Ship Days Off Time CUSTOMER ORDER LINE YES NO NO NO NO SUM ABS of (planned_ship_date-real_ship_date)
Net Amount CO Line RC CUSTOMER ORDER LINE NO YES NO YES YES SUM Net amount on CO line in reporting currency
Total Cost CO Line RC CUSTOMER ORDER LINE NO YES NO YES YES SUM Total cost on CO line in reporting currency
Total Order Line Discount CO Line RC CUSTOMER ORDER LINE NO YES NO YES YES SUM Total order line discount on CO line in reporting currency
Net Amount in Base Curr CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM base_charge_ amount * charged_qty
Net Charge Amount Order Curr CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM charge_ amount * charged_qty
Charge Cost CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM charge_cost
Qty Charged CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM charged_qty
Qty Invoiced CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM qty_invoices
Qty Returned CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM qty_returned
Sales Qty CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM get buy qty due
Qty Inv CO Charge CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM get revised qty due
Count Charge Lines Total CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM charged_qty > 0
Count Charge Line Returns CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM if charged_qty <> 0 and qty_returned <> 0
Total Charge Amount Base Curr CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM get total base charged amount
Total Charge Amount Order Curr CUSTOMER ORDER_CHARGE YES NO NO NO NO SUM get total charged amount
Net Amount CO Charge RC CUSTOMER ORDER_CHARGE NO YES NO YES YES SUM Net amount CO Charge in reporting currency
FACT CUSTOMER ORDER CHARGE Count CUSTOMER ORDER_CHARGE NO NO YES NO NO COUNT Count of rows
Total Charge Amount RC CUSTOMER ORDER_CHARGE NO YES NO YES YES SUM Total charge amount in reporting currency
Total Cost in Base Curr CO Charge CUSTOMER ORDER_CHARGE NO YES NO NO NO SUM Using procedure CUSTOMER_ORDER_CHARGE_API.Get_Charge_Cost in stage step
Total Cost CO Charge RC CUSTOMER ORDER_CHARGE NO YES NO NO YES SUM Using procedure CUSTOMER_ORDER_CHARGE_API.Get_Charge_Cost in stage step
Qty Received RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_received
Inv Qty Received RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_received_inv_uom
Qty To Return RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_to_return
Inv Qty To Return RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_to_return_inv_uom
Inv Qty Scrapped RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_scrapped
Qty Returned Inv RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM qty_returned_inv
Net Amount in Base Curr RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM get line total base price
Net Amount in Order Curr RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM get line total price
Count RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM count order line (RMA line)
Count Returns to Inventory RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM if qty_returned_inv > 0
Count Returns to Scrap Curr RMA Line RETURN MATERIAL LINE YES NO NO NO NO SUM ig qty_scrapped > 0
Net Amount RMA Line RC RETURN MATERIAL LINE NO YES NO YES YES SUM Net amount RMA line in reporting currency
Total Cost in Base Curr RMA Line RETURN MATERIAL LINE NO YES NO NO NO SUM Using procedure CUSTOMER_ORDER_LINE_API.Get_Cost in Stage step
Total Cost  RMA Line RC RETURN MATERIAL LINE NO YES NO YES YES SUM Using procedure CUSTOMER_ORDER_LINE_API.Get_Cost in Stage step
Qty Charged RMA Line RETURN MATERIAL CHARGE YES NO NO NO NO SUM charged_qty
Qty Credited RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM credited_qty
Charge Cost RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM charge_cost
Net Amount in Order Curr RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM get total charged amount
Net Amount in Base Curr RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM get total base charge amount
Count RMA Charge Lines RETURN MATERIAL CHARGE YES NO NO NO NO SUM count_charge_line
Count Credited Charge Lines RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM rows with state 'Credited'
Count Denied Charge Lines RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM rows with state 'Denied'
Count Planned Charge Lines RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM rows with state 'Planned'
Count Released Charge Lines RMA Charge RETURN MATERIAL CHARGE YES NO NO NO NO SUM rows with state 'Released'
Net Amount RMA Charge RC RETURN MATERIAL CHARGE NO YES NO YES YES SUM Net amount RMA charge in reporting currency
Total Cost in Base Curr RMA Charge RETURN MATERIAL CHARGE NO YES NO NO NO SUM Using procedure RETURN_MATERIAL_CHARGE_API.Get_Charge_Cost in Stage step
Total Cost RMA Charge RC RETURN MATERIAL CHARGE NO YES NO YES YES SUM Using procedure RETURN_MATERIAL_CHARGE_API.Get_Charge_Cost in Stage step
Gross Amount DOM Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM net_dom_amount + vat_dom_amount
Gross Amount Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM net_curr_amount + vat_curr_amount
Net Amount DOM Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM net_dom_amount
Net Amount in Base Curr Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM net_curr_amount
VAT Amount DOM Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM vat_dom_amount
VAT Amount Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM vat_amount
Open Amount DOM Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM sum dom_amount - cleared_som_amount
Open Amount Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM sum curr_amount - cleared_curr_amount
Disc Amount Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM sum curr_amount from invoice discounts
Deduction Amount Cust Inv CUSTOMER INVOICE YES NO NO NO NO SUM  
Net Amount Cust Inv RC CUSTOMER INVOICE NO YES NO YES YES SUM net amount
Count Cust Inv CUSTOMER INVOICE NO NO YES NO NO COUNT Count of rows
Rep Currency Rate CURRENCY RATE YES YES NO NO NO SUM Currency rate used to calculate amounts to reporting currency.
The core measure is modified in ETL

 

Calculated Measures

Calculated measures are in the cube calculated with respect to other measures. The table below defines the general meaning/definition of each calculated measure.

Measure Meaning
Qty Not Invoiced CO Line Qty Sales CO Line - Qty Invoiced CO Line
Perfect Orderlines % Count Perfect Order Line / Count CO Line
Orderlines on Time % Count On Time Order Line / Count CO Line
Incomplete Orderlines % Count Incomplete Order Line / Count CO Line
Complete Orderlines % (Count Co Line - Count Incomplete Order Line) / Count CO Line
Profit Net Amount in Base Curr CO Line - Total Cost in Base Curr CO Line
Not Invoiced Base Curr CO Line (Qty Sales CO Line - Qty Invoiced CO Line) * (Net Amount in Base Curr CO Line / Qty Sales CO Line)
Not Invoiced CO Line RC (Qty Sales CO Line - Qty Invoiced CO Line) * (Net Amount CO Line RC / Qty Sales CO Line)
Net Amount in Base Curr Net Amount in Base Curr CO Line + Net Amount in Base Curr CO Charge - Net Amount in Base Curr RMA Line - Net Amount in Base Curr RMA Charge - Net Amount in Base Curr CO Credit
Net Amount RC Net Amount CO Line RC + Net Amount CO Charge RC - Net Amount RMA Line RC - Net Amount RMA Charge RC - Net Amount CO Credit RC
Total Cost in Base Curr Total Cost in Base Curr CO Line + Total Cost in Base Curr CO Charge - Total Cost in Base Curr RMA Line - Total Cost in Base Curr RMA Charge
Total Cost RC Total Cost CO Line RC + Total Cost CO Charge RC- Total Cost RMA Line RC - Total Cost RMA Charge RC
Profit Margin in Base Curr Net Amount in Base Curr - Total Cost in Base Curr
Profit Margin RC Net Amount RC - Total Cost RC
Profit Margin % (Net Amount in Base Curr - Total Cost in Base Curr)/Net Amount in Base Curr
Avg Ship Days Early Sum Ship Days Early / Count Order Lines
Avg Ship Days Late Sum Ship Days Late / Count Order Lines
Avg Ship Days Off Time Sum Ship Days Off Time / (Count Early Order Lines + Count Late Order Lines)
Count Complete Order Line sum(Count CO Line) - sum(Count Incomplete Order Line)

 

Time Based Measures

Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of  the different time measures. These measures need to be used with a specific time dimension defined in the table below.

Measure Meaning
YTD Year to Date
Aggregated value for current month in current  year. E.g. for period 201210 Ytd represents sum of periods 201201-201210.
Based on [REPORTING_DATE].[RepDateMFHy]
R12 Rolling 12 (Completed) Months
For a given period, the sum all months from one year back up to the previous month. E.g. Rolling 12 for 201210 is the sum of months 201110 - 201209
Based on [REPORTING_DATE].[RepDateMFHy]
PY Previous Year
The value of the corresponding month one year back. E.g. for month 201210, the value of month 201110.
Based on [REPORTING_DATE].[RepDateMFHy]
YTD PY Year to Date Previous Year
Aggregated value for corresponding month one year back. E.g. for month 201210 YTD Previous Year represents sum of months 201101-201110.
Based on [REPORTING_DATE].[RepDateMFHy]
R12 PY Rolling 12 (Completed) Months Previous Year
For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 201210 the sum of month 201010-201109
Based on [REPORTING_DATE].[RepDateMFHy]

 

Included Example Reports and Dashboards

This section lists dashboards and reports in the example content for the BI Analysis Package that is using this cube:

UI Name Type
Sales Overview Excel Dashboard
Sales Details Excel Dashboard
Sales Customer Shipping Precision Report
Net Amount and Not Invoiced Per Catalog Group APC - Analysis Panel Component
Profit Last 12 Month APC - Analysis Panel Component
Sales Per Catalog Group in Base Currency APC - Analysis Panel Component
Shipping Precision - Number of Order Lines APC - Analysis Panel Component
Shipping Variation By Site APC - Analysis Panel Component

Required Components

The BI specific and also related core components listed in this section are required, i.e. they must along with other dependent components, be part of the customer installation to successfully use this cube.

Note that only directly used components are listed. Each such component can have its own list of dependent components. Please refer to the Component Deployment Profile File for each component to get the full picture

Component Type Component Name Product
BISEDI BI BI Services Dimensions Business Intelligence
APTOOL BI Common scripts and utilities, BI Anapac Business Intelligence
APPSDI BI Application Services Analysis Package Dimension Business Intelligence
ENTEDI BI Enterprise Analysis Package Dimensions Business Intelligence
ACCRDI BI Accounting Rules Analysis Package Dimensions Business Intelligence
ACCRFT BI Accounting Rules Analysis Package Fact Business Intelligence
INVODI BI Invoicing Analysis Package Dimensions Business Intelligence
INVOFT BI Invoicing Analysis Package Facts Business Intelligence
PARTDI BI Part Catalog Analysis Package Dimensions Business Intelligence
MPCCDI BI Gen Inventory and Distr BI Anapac Dimensions Business Intelligence
CFGCDI BI Configuration Characteristic BI Anapac Dimensions Business Intelligence
INVEDI BI Inventory Analysis Package Dimensions Business Intelligence
PROJDI BI Project Analysis Package Dimensions Business Intelligence
ORDEDI BI Customer Orders Analysis Package Dimensions Business Intelligence
ORDEFT BI Customer Orders Analysis Package Facts Business Intelligence
SLANAC BI Sales Analysis Cube Business Intelligence
ENTERP CORE Enterprise Financials
ACCRUL CORE Accounting Rules Financials
INVOIC CORE Invoicing Financials
PARTCA CORE Part Catalog Supply Chain
MPCCOM CORE General Inventory and Distribution Supply Chain
ORDER CORE Customer Order Supply Chain
INVENT CORE Inventory Supply Chain
PROJECT CORE Project Projects
CFGCHR CORE Configuration Characteristic Manufacturing

Note: BI Component SLANAC