OLAP: Purchasing statistics

Topic overview

This article explains the purchasing statistics in the OLAP data bank. The purchasing item statistics comprise two statistics. The purchase value statistics show the sales per supplier and item based on the supplier invoice items. purchasing item costs are calculated on the basis of the corresponding purchase order line items and shown for the respective item. The cost value statistics show the turnover achieved per supplier and item based on the supplier invoice line items. Procurement costs are taken from the supplier invoice line items and allocated to the originating item. Depending on the assignment in the Customizing application, either the invoicing party, the delivery partner or the supplier is used as the supplier.

This article explains how the star schemas of the respective purchasing statistics are structured and which dimensions and fields they contain. The applications relevant to the purchasing statistics and their fields, actions and procedures are described in detail in separate articles.

Definitions of terms

Cash purchase values

The cash purchase value is calculated from the net amount (gross amount minus discounts plus surcharges) minus cash discount. The cash discount percentage in the first payment interval of the corresponding payment terms is used for the calculation. The cash purchase value is calculated per item and displayed as the total of the entire order in the order header. The following formula is used: Cash purchase value = gross amount – discounts + surcharges – cash discount

Acquisition values

The acquisition value comprises the costs, including surcharges, discounts and procurement costs, that are incurred for the purchase of an item. Payment terms are not taken into account. The acquisition value is calculated based on the costs according to the supplier invoice.

Procurement costs

The procurement costs are incurred for the delivery of an item. They are a percentage value or an absolute amount that increases the cost price of an item.

Gross amount

The gross amount is the product of price * quantity. The amount determined in this way is displayed for an order line item. In the order header, the gross amount is displayed as the total of all order line items. It does not include discounts, surcharges or other costs.

Data warehouse

The data warehouse collates business-relevant data from various data sources. The data is summarised into meaningful information and stored in a separate OLAP database in fact and dimension tables. This information is then available for analyses. In Comarch ERP Enterprise, master and transaction data from the Sales and Purchasing frameworks, for example, are transferred to the OLAP database used as a data warehouse.

Dimension tables

The dimension tables in a data warehouse contain qualitative data that classify the quantitative data in the fact table. Examples of dimensions are tag, sales item, customer, etc. The data in dimension tables changes comparatively rarely and contains relatively few data records compared to the fact table.

Supplier invoices

The supplier invoice contains a list of the amounts to be paid for items delivered or still to be delivered. The supplier invoice is an external voucher that can also be a credit note.

Purchase value

The purchase value corresponds to the net amount of an item from a supplier invoice. Neither cash discounts nor surcharges and discounts or similar are added to or deducted from the amount from the supplier invoice.

Cost values

The cost value is made up of the gross amount, discounts and surcharges, then reduced by the cash discount for the first payment interval, if applicable, and increased by the pro rata procurement costs. The following formula is used: Cost value = cash purchase value *(1 + purchase costs (in %) / 100) + purchase costs. Note: Cash purchase value = gross amount – discounts + surcharges – cash discount.

Fact tables

The fact table is the actual centrepiece of a data warehouse. It contains the quantitative data of the data warehouse that has arisen from the operational processes, such as sales, quantities, etc. These key figures serve as the data basis for the analyses. In addition to the key figures, the dimension tables also contain references to the dimensions in order to classify the data. Compared to the dimension tables, the fact table contains larger amounts of data and additional data records are added with each update.

Classifications

A classification represents the superordinate and subordinate relationship between objects. It determines the ranking in the system. A classification can consist of one or more levels. Classifications consist of interdependent nodes. The higher-level node is called the folder. The node below the last folder is called a leaf.

Net amount

The net amount is the sum of the calculated gross amount (price * quantity) minus discounts granted and plus surcharges. The net amount is calculated per item and displayed as the total of the entire order in the order header.

Online Analytical Processing (OLAP)

Online Analytical Processing (OLAP) is a method for rapid analysis of business events on an aggregated, dense level. Instead of primarily considering a single business event, the process deals with a set of similar or connected business events. Various aggregation levels can be used as required. OLAP is a software technology to analyze large data sets through rapid, consistent, interactive access to different views of the data. The data are prepared in multidimensional views, intended to reflect the structure of the company being analyzed from the user’s point of view. In the ERP system, the data for OLAP are taken from the OLTP database, which contains the current master and transaction data. These OLAP data are then saved in a separate OLAP database in the star topology fact and dimension tables. This OLAP database is used as a data warehouse.

Online Analytical Processing database (OLAP database)

An online analytical processing (OLAP) database provides the necessary data structures for OLAP. In contrast to OLTP databases, very large amounts of data are processed in a transaction using denormalized schemas.

Discounts

The discount is a reduction on the agreed price. The discount can be expressed as a percentage or as an absolute amount.

Cash discount

A cash discount is a percentage price reduction granted as agreed in payment terms. The reduction may be scaled as follows: 5 % discount if paid within 1 week, 3 % discount if paid within 4 weeks. Cash discount is always calculated on the total value of an invoice. For determining the cost value, only the first cash discount percentage rate of the applicable payment terms is considered. The economic significance of a cash discount is that it is the price for credit (prefinancing) granted to the recipient of a merchandise delivery. Beside the interest and administration expense, the cash discount that is added as a surcharge to the cash price contains a premium for the credit risk. It provides the purchaser with an incentive to pay cash.

Star topology

The star topology is a data model for relational databases. Usually a data warehouse is structured according to a star topology. A simple star topology consists of a central fact table and several dimension tables connected to the fact table via keys. In the model, a fact table is represented in the middle with the dimension tables arranged around it. This graphical representation resembles a star shape.

Statistics

Statistics concerns numerical recording, analysis, and representation of mass phenomena. Facts from the past are collected, summarized, and evaluated using specific methods. Analysis of these data has the purpose of discovering deviations and errors in order to develop means of improvement.

Surcharge

A surcharge is related to and increases the agreed price, if it is a positive value. The surcharge can be expressed as a percentage or an absolute amount.

Dimension tables

In addition to the base dimensions, the purchase value statistics include the following dimensions:

The dimensions are explained individually in the following chapters. Please refer to the article for a description of the base dimensions OLAP: Base dimensions.

Note
The dimensions SupplierGroup, PurchaseItemGroup, PurchaseOrderClassification from Comarch ERP Enterprise 2 are no longer used in Comarch ERP Enterprise 4. They are not described further in this article.

Supplier

The suppliers are saved in the business entitycom.cisag.app.olap.obj.Supplier. The purchase value statistics can be analyzed by supplier using the dimension table.

The attributes of the dimension table that are relevant for OLAP statistics are explained below:

id – the attribute contains the numerical identification of the supplier. The identification is used in the OLAP database to uniquely reference the supplier.

guid – the attribute contains the Guid for the corresponding partner in the OLTP database.

number – the attribute contains the partner number of the supplier as a character string.

name – the attribute contains the name of the supplier. The value corresponds to the name attribute of the com.cisag.app.general.obj.Partner business entity. If the name consists of several lines, these are separated by a line break (LF).

abcCode – the attribute contains the ABC code of the supplier as a character string. The attribute can contain the values A, B or C.

address – the attribute contains the supplier’s address. The address is made up of the following information:

  • City (city)
  • Country code (countryCode)
  • Country (countryDesc)
  • District (district)
  • P.O. box (poBox)
  • P.O. box city(poBoxCity)
  • P.O. box code (poBoxPostalCode)
  • Postal code (postalCode)
  • Region code (regionCode)
  • Region (regionDesc)
  • Street (street)

If the address is not stored for a pseudo supplier, the attribute contains an empty character string. If the address consists of several lines, these are separated by a line break (LF).

If you also want to analyze the purchase value statistics for pseudo-suppliers by region, for example, you should enter a pseudo-supplier for each region in the OLTP database and store the country and region in the partner master data.

classificationId1 – the attribute contains the identification of supplier classification 1 (SUPPLIER_HIERARCHY1) in the Classification dimension table. If no supplier classification 1 is stored for the supplier, the attribute contains the value 0 (zero).

classificationId2 – the attribute contains the identification of supplier classification 2 (SUPPLIER_HIERARCHY2) in the Classification dimension table. If no supplier classification 2 is stored for the supplier, the attribute contains the value 0 (zero).

classificationId3 – the attribute contains the identification of supplier classification 3 (SUPPLIER_HIERARCHY3) in the Classification dimension table. If no supplier classification 3 is stored for the supplier, the attribute contains the value 0 (zero).

classificationId4 – the attribute contains the identification of supplier classification 4 (SUPPLIER_HIERARCHY4) in the Classification dimension table. If no supplier classification 4 is stored for the supplier, the attribute contains the value 0 (zero).

classificationId5 – the attribute contains the identification of supplier classification 5 (SUPPLIER_HIERARCHY5) in the Classification dimension table. If no supplier classification 5 is stored for the supplier, the attribute contains the value 0 (zero).

Purchasing item

Purchasing items are saved in the com.cisag.app.olap.obj.PurchaseItem business entity. The dimension table can be used to analyze the purchase value statistics by purchasing item.

The attributes of the dimension table that are relevant for OLAP statistics are explained below:

id – the attribute contains the numerical identification of the purchasing item. The identification is used in the OLAP database to uniquely reference the purchasing item.

guid – the attribute contains the Guid for the corresponding item in the OLTP database.

number – the attribute contains the item number of the purchasing item as a character string.

guid – the attribute contains the Guid for the corresponding item in the OLTP database.

referenceNumber – if the purchasing item is a variant item, the attribute contains the item number of the reference item. If the purchasing item is not a variant item, the attribute contains the item number from the number attribute.

description – the attribute contains the short name of the purchasing item.

uom – the attribute contains the base unit of the purchasing item as a character string.

abcCode – the attribute contains the ABC code of the purchasing item as a character string. The attribute can contain the values A, B or C.

classificationId1 – the attribute contains the identification of purchasing item classification 1 (PURCHASEITEM_HIERARCHY1) in the Classification dimension table. If no purchasing item 1 is stored for the purchasing item, the attribute contains the value 0 (zero).

classificationId2 – the attribute contains the identification of purchasing item classification 2 (PURCHASEITEM_HIERARCHY2) in the Classification dimension table. If no purchasing item classification 2 is stored for the purchasing item, the attribute contains the value 0 (zero).

classificationId3 – the attribute contains the identification of purchasing item classification 3 (PURCHASEITEM_HIERARCHY3) in the Classification dimension table. If no purchasing item classification 3 is stored for the purchasing item, the attribute contains the value 0 (zero).

classificationId4 – the attribute contains the identification of purchasing item classification 4 (PURCHASEITEM_HIERARCHY4) in the Classification dimension table. If no purchasing item classification 4 is stored for the purchasing item, the attribute contains the value 0 (zero).

classificationId5 – the attribute contains the identification of purchasing item classification 5 (PURCHASEITEM_HIERARCHY5) in the Classification dimension table. If no purchasing item classification 5 is stored for the purchasing item, the attribute contains the value 0 (zero).

Supplier invoice type

Note
This dimension is no longer used in Comarch ERP Enterprise 4. To ensure a “smooth” migration, the supplier invoice types can be transferred from a Comarch ERP Enterprise R2 system to Comarch ERP Enterprise 4. For this purpose, the supplierInvoiceType attribute in the com.cisag.app.olap.obj.Parameters business entity can be assigned the value True as part of the migration.

The supplier invoice types are saved in the com.cisag.app.olap.obj.SupplierInvoiceType business entity. The dimension table can be used, for example, to analyze the purchase value statistics by supplier invoice type.

The attributes of the dimension table that are relevant for OLAP statistics are explained below:

id – the attribute contains the numerical identification of the supplier invoice type. The identification is used in the OLAP database to uniquely reference the supplier invoice type.

guid – the attribute contains the guid for the corresponding supplier invoice type in the OLTP database.

code – the attribute contains the code of the supplier invoice type.

description – the attribute contains the name of the supplier invoice type.

Fact tables

The OLAP database includes the following purchasing item statistics:

The cost value statistics are explained individually below.

Purchase value statistics

The purchase value statistics show the sales per supplier and item based on the supplier invoice line items. Procurement costs are calculated on the basis of the corresponding purchase order line items and shown for each item. This enables a clear assessment of the turnover generated with the individual suppliers. Depending on the assignment in the Customizing application, either the invoicing party, the delivery partner or the supplier is used as the Supplier.

The purchase value statistics are based on the supplier invoice line items posted and transferred to the OLAP database (business object: com.cisag.app.sales.obj.SupplierInvoiceDetail). Only supplier invoices (and their line items) that fulfil the following conditions are taken into account:

  • The attribute statisticRelevant has the value true for standard, discount in kind, set component and external production main line item categories,
  • the internalNumber attribute has a value greater than zero (0) for posted supplier invoices.

The following attributes of the supplier invoice line items are used to calculate the key figures, among other things:

  • quantity (attribute: quantity)
  • net weight (attribute: netWeight)
  • net amount (attribute: netAmountDomestic)

The purchase value statistics also access the corresponding supplier invoice line items in order to calculate the imputed cost values.

The purchase value statistics are saved in the fact table com.cisag.app.olap.obj.PurchaseValue. As of Comarch ERP Enterprise 4, the purchase value statistics are cumulated using the following attributes:

  • day
  • organisation (purchasing organisation)
  • purchasing article
  • supplier

The following figure shows the key figures saved in the fact table and the relationships to the dimension tables:

The dimensions Supplier (Supplier), Purchasing item (PurchaseItem) and Organization (OrganizationalUnit) have further relationships to the Classification dimension. For the sake of clarity, these relationships are not shown in the diagram. For a description of how the classifications are used, please refer to the article OLAP: Base dimensions.

The attributes of the fact table relevant to the purchase value statistics are explained below:

guid – the attribute contains the primary key of the fact table. It is required for data import, among other things, and has no equivalent to a business object in the OLTP database.

dayId – the attribute contains the identification for the Day dimension. It corresponds to the voucher date of the supplier invoice.

organisationalUnitId – the attribute contains the identification of the purchasing organisation for which the supplier invoice was posted (see also BO supplier invoice com.cisag.app.purchasing.obj. SupplierInvoice:invoiceRecipient).

purchaseItemId – the attribute contains the identification of the purchasing item.

supplierId – the attribute contains the dentification of the supplier. Regardless of the assignment in the Customizing application, the invoicing party is transferred to the purchase value statistics.

supplierInvoiceTypeId – the attribute contains the identification of the supplier invoice type. The supplier invoice type is no longer used as of Comarch ERP Enterprise 4. To ensure a smooth migration, the supplier invoice types from a Comarch ERP Enterprise R2 system can be transferred to Comarch ERP Enterprise 4. For this purpose, the supplierInvoiceType attribute in the com.cisag.app.olap.obj.Parameters business entity can be assigned the value True as part of the migration.

quantity – the attribute contains the quantities in the base unit of the purchasing item from the quantity attribute of the supplier invoice line items. For additional cost items, the quantity is zero (0).

weight – the attribute contains the net weights in the system unit of the “Mass” type from the netWeight attribute of the supplier invoice line items. For additional cost line items, the net weight is zero (0).

purchaseValue – the attribute contains the purchase values from the supplier invoice line items in the first local currency. These are the net amounts without taking into account any taxes or discounts. Any additional costs are assigned to the respective billing item. This makes it possible to analyze the sales made with the individual suppliers in the purchase value statistics.

purchaseCosts – the attribute contains the procurement costs from the associated purchasing order line items in the first local currency. The procurement costs and the purchase value together make up the acquisition value. The following formula is used to calculate the procurement costs:

Procurement costs =
Net value * (procurement costs (in %) / 100) +
Procurement costs (in currency unit)

The net value is calculated as follows:

Net value =
gross amount - discounts + surcharges

All values are based on the corresponding attributes in the purchasing order line items. If these attributes are not filled, the procurement costs are calculated as zero (0). As the actual procurement costs incurred according to the supplier invoice or additional cost invoice are not taken into account, the procurement costs in the purchase value statistics are estimated values.

Neither the purchase value statistics nor the cost value statistics take into account any cash discounts. This makes it easier to compare the procurement costs in the two statistics.

inventoryValue – the attribute contains the valuation of the supplier invoice line items in the first local currency. The transfer price 1 is used for the valuation.

Transfer price_1 * quantity

The transfer price 1 applies at the time of transfer to the OLAP statistics and not at the time of posting the supplier invoice. For additional cost line items, the valuation is zero (0)

standardValue – the attribute contains the default values in the first local currency of the supplier invoice line items. The default value represents a supplier-neutral price. It is calculated during the transfer from the standard price list stored in the Customizing application. No price definitions for supplier or supplier classifications are taken into account in the calculation. If no standard price can be determined from the standard price list, the attribute contains the value zero (0).

Cost value statistics

The cost value statistics show the sales achieved per supplier and item based on the supplier invoice line items. procurement costs are taken from the supplier invoice iline tems and allocated to the “originating” item. This ensures an optimal comparison of suppliers, taking into account the actual procurement costs incurred. Depending on the assignment in the Customizing application, either the invoicing party, the delivery partner or the supplier is used as the supplier.

The cost value statistics are based on the line items of the suppler invoices and additional cost invoices (business object: com.cisag.app.sales.obj.SupplierInvoiceDetail) that have been posted and transferred to the OLAP database. Only supplier invoices (and their linw items) that fulfil the following conditions are taken into account:

  • the attribute statisticRelevant has the value truefor standard, discount in kind, set component and external production main line item categories,
  • the internalNumber attribute has a value greater than zero (0) for posted supplier invoices.

The following attributes of the supplier invoice line items are used to calculate the key figures, among other things:

  • quantity (attribute: quantity)
  • net weight (attribute: netWeight)
  • net amount (attribute: netAmountDomestic)

The cost value statistics are saved in the fact table com.cisag.app.olap.obj.PurchaseCosts. As of Comarch ERP Enterprise 4, the cost value statistics are cumulated using the following attributes:

  • day
  • organisation (purchasing organisation)
  • purchasing item
  • supplier

The following diagram shows the key figures saved in the fact table and the relationships to the dimension tables:

The dimensions Supplier (Supplier), Purchasing item (PurchaseItem) and Organisation (OrganizationalUnit) have further relationships to the Classification dimension. For the sake of clarity, these relationships are not shown in the diagram. For a description of how the classifications are used, please refer to the article OLAP: Base dimensions.

The attributes of the fact table relevant to the cost value statistics are explained below:

guid – the attribute contains the primary key of the fact table. It is required for data import, among other things, and has no equivalent to a business object in the OLTP database.

dayId – the attribute contains the identification for the Day dimension. It corresponds to the voucher date of the supplier invoice.

organisationalUnitId – the attribute contains the identification of the purchasing organisation for which the supplier invoice was posted (see also the supplier invoice business object com.cisag.app.purchasing.obj. SupplierInvoice:invoiceRecipient).

purchaseItemId – the attribute contains the identification of the purchasing item.

supplierId – the attribute contains the identification for the supplier. Depending on the assignment in the Customizing application, either the invoicing party, delivery partner or supplier is transferred.

supplierInvoiceTypeId – the attribute contains the identification for the supplier invoice type. The supplier invoice type is no longer used as of Comarch ERP Enterprise 4. To ensure a smooth migration, the supplier invoice types from Comarch ERP Enterprise R2 system can be transferred to Comarch ERP Enterprise 4. For this purpose, the supplierInvoiceType attribute in the com.cisag.app.olap.obj.Parameters business entity can be assigned the value True as part of the migration.

quantity – the attribute contains the quantities in the base unit of the purchasing item from the quantity attribute of the supplier invoice line items.

weight – the attribute contains the net weights in the system unit of the Mass type from the netWeight attribute of the supplier invoice line items.

purchaseValue – the attribute contains the purchase values from the supplier invoice line items in the first local currency. These are the net amounts without taking into account any taxes or discounts in kind. Any additional costs are assigned to the originating purchasing item and shown in the purchaseCosts attribute. This ensures an optimal comparison of suppliers in the cost value statistics, taking into account the actual procurement costs incurred. It is not possible to analyze the individual billing items.

purchaseCosts – the attribute contains the procurement costs from the corresponding supplier invoice line items in the first local currency. These are the net amounts without taking into account any taxes or cash discounts. The procurement costs and the purchase value together make up the acquisition value.

The procurement costs are either recognized as additional cost line items of an supplier invoice or in a separate additional cost calculation. The procurement costs are allocated to the originating purchasing item. This makes it possible to analyze the individual purchasing items in the cost value statistics, taking into account the actual procurement costs incurred.

inventoryValue – the attribute contains the valuation of the supplier invoice line items in the first local currency. The transfer price 1 is used for the valuation.

Transfer price_1 * quantity

The transfer price 1 applies at the time of transfer to the OLAP statistics and not at the time of posting the supplier invoice.

standardValue – the attribute contains the default values in the first local currency of the supplier invoice line items. The default value represents a supplier-neutral price. It is calculated during the transfer from the standard price list stored in the Customizing application. No price definitions for supplier or supplier classifications are taken into account in the calculation. If no standard price can be determined from the standard price list, the attribute contains the value zero (0).

Czy ten artykuł był pomocny?

Wyślij opnie