Topic overview

For some years now, the topic of data warehousing has become increasingly important for companies. This reflects the changing role of information technologies in companies: in the era of the Internet and e-business, information and knowledge have become important competitive factors in a global competition. However, despite the large amount of internal and external data that is important for a company, the information required for decision-making is often only available to a limited extent. The data is distributed across the entire organisation, poorly available and inadequately prepared in terms of content and format.

Data Warehouse and Online Analytical Processing (OLAP) are closely related concepts in business informatics. Data from various operational systems is stored in a data warehouse. A relational, usually denormalised (redundant) and simplified database schema (star schema) enables powerful evaluations. A data warehouse therefore provides a consistent information basis for decision-making within the company. The term Online Analytical Processing (OLAP) covers technologies, methods and applications that enable the analysis of large data sets through fast, interactive data access. It is not the individual business transaction that is of interest here; rather, business transactions with similar characteristics are analysed cumulatively and multidimensionally. Due to its characteristics, OLAP is particularly suitable as a planning and control instrument for controlling. OLAP provides quick and flexible answers to questions such as “How has customer behaviour changed as a result of the market launch of the new product?”.

This article explains the data warehouse and statistics functions. Find out, for example, the difference between OLAP and OLTP statistics and what options the system offers for obtaining decision-relevant information from your databases.

Definitions of terms

Data Warehouse

The data warehouse brings together 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 the ERP system, master and transaction data from the sales and procurement 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.

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 turnover, quantities, etc. These key figures serve as the data basis for the data warehouse. 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.

Online Analytical Processing (OLAP)

Online Analytical Processing (OLAP) is a method for analysing business transactions in real time at an aggregated, condensed level. The primary object of analysis is not the individual business transaction, but a set of similar or related business transactions. Different aggregation levels can be used as required. This software technology enables large volumes of data to be analysed through fast, consistent, interactive access to different views of the data. The data is prepared in multidimensional views that reflect the structures of the analysed company from the user’s perspective. In the ERP system, the data relevant for OLAP is extracted from the OLTP database, which contains the current master and transaction data. This OLAP data is then stored in a separate OLAP database in fact and dimension tables according to the Star schema. 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 denormalised schemas.

Online Transaction Processing (OLTP)

Online Transaction Processing (OLTP) is a method of centralised online processing of operational data. With this method, the orders and therefore transactions of several simultaneously accessing (competing) users, or more precisely clients, are processed one after the other on a server. During this time, the clients maintain their ‘connection’ to the database and wait for execution.

Online transaction processing database (OLTP database)

Master and transaction data for the business applications are managed in an online transaction processing (OLTP) database. In addition, behaviours are stored that are referred to as ‘customising’. In a productive system, the OLTP database is subject to the highest user load. It is therefore specially designed to be able to serve a large number of users who process a shared database in parallel.

Star schema

The star schema is a data model for relational databases. A data warehouse is usually structured according to the star schema. A simple star schema consists of a central fact table and several dimension tables that are linked to the fact table via keys. In the model, a fact table is shown in the centre and the dimension tables are distributed around it. This graphical representation corresponds to the shape of a star.

Statistics

Statistics deals with the numerical recording, investigation and visualisation of mass phenomena. Facts from the past are collected, collated and analysed using specific methods. The purpose of analysing this data is to identify deviations and errors and to develop opportunities for improvement.

OLAP statistics

Each OLTP database can be assigned exactly one OLAP database, which serves as a data warehouse for the OLAP. In turn, the OLAP database can only receive operational data from one OLTP database.

The OLAP database contains star schemas for the following statistics:

• Sales statistics
• Purchasing statistics:
  • Purchase value statistics
  • Cost value statistics
• Relationship management statistics:
  • Opportunity statistics
  • Opportunity per competitor
  • Opportunity per sales representative
• Packaging disposal statistics:
  • Packaging consumption
  • Packaging consumption per item and customer

Each star schema is linked to several dimensions that further qualify the facts. Both the star schemas and the dimensions are located in the namespace.

Only the financial years are stored in the OLAP database on a company-specific basis. In order to be able to analyse the data of different organisations cumulatively, all other dimensions only contain the business entities at client level. Which organisation is the responsible organisation of a business entity is not taken into account. If, for example, a sales organisation has entered a sales item classification that differs from the definition at client level, this cannot be used for the evaluation of the OLAP statistics. The facts, on the other hand, are stored in the OLAP database with a link to the Organisation dimension on an organisation-specific basis. The content language of the client is used for both the dimensions and the facts.

The facts are always based on a document or line item in the OLTP database. Payment information or any write-offs of open items in financial accounting are not taken into account. The following document types are relevant for the facts:

Customer invoices

The customer invoice items posted and transferred to the OLAP statistics form the basis of the OLAP sales statistics and the OLAP packaging statistics. In the OLAP sales statistics, only customer invoice items with the value True in the estatisticRelevant attribute are taken into account. For the OLAP packaging statistics, the attribute packagingRelevant must be true.

Supplier invoices

The supplier invoice items posted and transferred to the OLAP statistics form the basis of the OLAP purchasing statistics. Only the supplier invoices (and their items) that fulfil the following conditions are taken into account:

• the statisticsRelevant attribute has the value True and
• the internalNumber attribute has a value greater than zero (0).

Purchase orders

The OLAP purchasing value statistics calculate the procurement costs based on the purchase order items linked to the supplier invoice items.

Opportunities

The sales prospects transferred to the OLAP statistics form the basis of the OLAP sales prospect statistics. The following objects serve as a buffer for the transfer:

• Business Entity cisag.app.crm.obj.OpportunityHistory
• Dependent cisag.app.crm.obj.OpportunityHistoryDetail

The business entity summarises all changes to the opportunity on a calendar day.

The facts are saved cumulatively by key. If, for example, a customer has purchased the same item several times and the outgoing invoices generated all have the same document date, then only the total of the outgoing invoice items is saved in the OLAP database. Cumulation also means that it is not possible to drill down to the line item.

All amounts are transferred in the first local currency (group currency). This enables a consolidated evaluation in a multi-site system, even if not all companies use the same lead currency. Dates are always extracted from the OLTP database without a time zone. Saving to the OLAP database takes place with the client’s time zone. If, for example, a customer invoice has the document date 01.10.2005 00:00:00 MSK (Moscow standard time), then this document appears in the OLAP statistics with the invoice date 01.10.2005 00:00:00 CET if the Central European time zone is stored for the client. This symbolic view of the date enables a time zone-independent view and provides the same query results regardless of the viewer’s time zone.

The OLAP statistics are located in the namespace com.cisag.app.olap.obj. They are described in the following documentation:

• OLAP: Sales statistics
• OLAP: Purchasing statistics
• OLAP: Packaging statistics
• OLAP: Opportunity statistics

The dimensions are described together with the respective fact tables. The article OLAP: Basic Dimensions provides a description of the basic dimensions, i.e. those dimensions that are used by several fact tables, such as the classifications and the time dimension.

OLTP statistics

In addition to the proven star schemas of the OLAP database, which can be analysed with external analysis tools, you are offered statistical analyses in the applications Items (in the views Sales, Purchasing and Planning), Partners (in the views Customer, Supplier and in the Base view for representatives) and Storage locations on a dedicated Statistics tab. The results are displayed both as a table and in graphical form.

The OLTP statistics in the Partners and Items applications show the facts generated by the organisation selected in the selection field for organisations and the underlying organisations. In the Storage locations application, however, the OLTP statistics are not restricted by location. A user who is authorised to open the Storage locations application can also query the OLTP storage location statistics for all storage location-managed storage locations. A description of the individual input and display fields can be found in this article:

• Items, Sales view
• Partner, Customer view
• Storage locations.

The analyses are based on simple star schemas in the namespace com.cisag.app.statistic.obj in the OLTP database. The OLTP statistics therefore do not directly access the business entities on which the OLTP statistics are based. Even if the fact tables are in the OLTP database, the OLTP statistics reflect the status at the time of the last transfer to the OLTP statistics.

The following OLTP statistics are available:

• Sales item statistics
  • Sales item annual statistics
  • Item customer monthly statistics
  • Item customer classification monthly statistics
• Customer statistics
  • Customer annual statistics
  • Customer item monthly statistics
  • Customer item classification monthly statistics
• Purchasing item statistics
  • Purchasing item annual statistics
  • Item supplier monthly statistics
  • Item supplier classification monthly statistics
• Supplier statistics
  • Supplier annual statistics
  • Supplier item monthly statistics
  • Supplier item classification monthly statistics
• Turnover statistics (planning)
  • Turnover annual statistics
• Storage location statistics
  • Storage location access annual statistics

To support material planning, the Planning view of the Items application also offers the key figures for range, movement and turnover frequency. Further information on the key figures can be found in the article Key figures in the inventory turnover statistics.

OLTP statistics are always based on a voucher or voucher item in the OLTP database. Payment information or any write-offs of open items in financial accounting are not taken into account. The following voucher types are relevant for the OLTP statistics:

• Customer invoices

The customer invoice items posted and transferred to the OLTP statistics form the basis of the OLTP sales statistics. Customer invoice items with the value True in the statisticRelevant attribute are only taken into account.

• Supplier invoices

The supplier invoice items posted and transferred to the OLTP statistics form the basis of the OLTP purchasing statistics. Only the supplier invoices (and their items) that fulfil the following conditions are taken into account:

• • the statisticsRelevant attribute has the value true and
  • the internalNumber attribute has a value greater than zero (0).
• Purchase orders

If the Purchasing value statistics type is defined for the purchasing statistics in the Customizing application, the OLTP purchasing statistics calculate the procurement costs based on the purchase order items linked to the supplier invoice items.

• Inventory postings

The inventory postings successfully processed by an inventory management server form the basis of the OLTP turnover statistics and the OLTP storage location statistics. The OLTP turnover statistics only take into account inventory postings of inventory items that have the Planning usage. If the planning data is missing for the posting location (organisation), the inventory posting is not taken into account. In addition, it must be specified for the inventory item in the Planning view of the Items application that it is to be taken into account by the statistics.

In the Items and Partners applications, you can specify whether the item or partner is included or excluded from the respective OLTP statistics. The inclusion or exclusion can be specified at up to three levels:

1. Business Entity
2. Statistics classification
3. Customizing

To determine whether an item is included in the sales item annual statistics, for example, the item master data is first checked in the Items application: If the Inclusion/exclusion field in the Sales view of the item has the value Inclusion, then the item is included. However, if the value is Exclusion, it is not. If the field has the value From inheritance, the sales item statistics classification stored in the Classification input field is checked in the same way. If the value From inheritance is also stored there, then the setting stored in the Customizing application applies.

A voucher line item is transferred to the annual statistics if the corresponding business entity is included in the statistics. If, for example, a customer with the value Exclusion has purchased an item with the value Inclusion, the customer invoice item is transferred to the sales item annual statistics, but not to the customer annual statistics. In the turnover statistics, the inventory item must also have the usage Planning for the inventory posting to be transferred. With the storage location statistics, on the other hand, all relevant inventory postings are always transferred, regardless of whether the inventory item is included or excluded.

For monthly statistics, both the partner and the item must be taken into account for a line item to be transferred. In contrast, for monthly statistics that use a statistics classification as a secondary business entity, only the primary business entity is used for the check. If, for example, a customer with the value Exclusion has purchased an item with the value Inclusion, the initial invoice item is transferred to the item customer classification monthly statistics and is not transferred to the customer item classification monthly statistics. The transfer is completely independent of the respective secondary statistics classification.

If you subsequently change the values for inclusion and exclusion, this has no effect on the existing facts in the fact tables. The change will only take effect with the next update of the facts and all new line items added since the last transfer will be transferred to the OLTP statistics. If you would also like to transfer older, already transferred line items to the OLTP statistics at a later date, you must run the batch application Recompile statistics facts.

The OLTP statistics are located in the namespace com.cisag.app.statistic.obj. They are described in the following documentation:

• OLTP: Sales statistics
• OLTP: Purchasing statistics
• OLTP: Turnover statistics
• OLTP: Storage location statistics

Applications and functions

The data warehouse and statistics functions are integrated into the business processes so that they are available at the relevant points. This has made it possible to dispense with the presentation of data warehouse and statistics as a separate framework. The applications available in earlier versions can be found in the following locations:

• Customizing application

In the Customizing application, you can make client-specific settings for the OLAP and OLTP statistics using the Data warehouse and statistics function. As the details for the packaging statistics are stored per company, they are located under a separate Packaging disposal function under the main Base function. For the same reason, the standard price listings required by the sales and purchasing statistics are located under the Prices function below the main Sales or Purchasing functions.

Further information can be found in the articles Customizing: ERP Functions and Customizing: Data warehouse and statistics.

• Batch application Update statistics dimensions

During execution, the statistics dimensions of the OLAP statistics are compared with the corresponding business entities in the OLTP database. Updating the statistics dimensions has no effect on the OLTP statistics.

This batch application is only visible when the relevant OLTP database is actively connected to an OLAP database.

• Batch application Update statistics facts

During execution, the business entities on which the statistics are based are extracted, prepared and transferred to the statistics. The batch application updates both the OLAP and OLTP statistics.

• Batch application Recompile statistics facts

The selected facts in the OLAP and OLTP statistics are first deleted during execution. The counters relevant for the update are then reset and the statistics facts are updated. The reorganization of the statistics facts has no effect on the existing statistics dimensions.

By storing a series pattern in the processing order, the statistics facts and statistics dimensions can be updated repeatedly, e.g. at a certain time of day. For a description of the actions and parameters of the batch applications, please refer to the direct article for the respective application.

In the dialog window for running the batch applications, select the relevant statistics. The update always takes place for both the OLAP and OLTP statistics. The refresh is carried out either for the OLAP statistics, the OLTP statistics or both. For a description of the actions and parameters of the batch applications, please refer to the direct article of the respective application.

Process

The following illustration shows the procedure for updating the statistics facts and statistics dimensions using the sales statistics (OLAP) and the customer annual statistics (OLTP):

The respective OLAP and OLTP statistics are updated asynchronously. The business entities defined as facts are extracted from the OLTP database, processed and saved in the OLAP statistics as star schemas and also transferred to the OLTP statistics in accordance with the settings for inclusion and exclusion. If the facts to be transferred relate to business entities that are not present in the OLAP dimension tables, the missing data records are automatically added to the OLAP dimension tables. To be able to analyze the OLAP statistics, you therefore do not need to explicitly update the statistics dimensions. However, if you want to determine all customers for whom no customer invoices have been generated, for example, you will need to update the dimensions. All relevant facts are always transferred to the OLAP statistics. It is not possible to exclude certain partners or items from the OLAP statistics, as is the case with OLTP statistics.

Customer invoices are given a unique sequence number in the system when they are created. For supplier invoices, the sequence number is not assigned until the posting. These numbers also determine the sequence in which the invoice items are transferred to the OLAP and OLTP statistics. When updating, the sequence number of the last invoice transferred is saved. The next update of the statistics facts continues with the invoice with the next highest sequence number. This ensures a complete and unique transfer of the invoice items. The data records in the sales prospect history are also given an internal sequence number when they are created. The inventory postings are transferred according to the internalProcessingNumber attribute.

During the transfer, the data in the fact tables is cumulated by key. If, for example, a customer has purchased the same item several times and the customer invoices generated all have the same voucher date, then only the total of the customer invoice items is transferred to the OLAP statistics. In the OLTP statistics, all fact tables are cumulated by calendar month. On the one hand, this cumulation means smaller data volumes, but it also means that it is not possible to drill down to the voucher line item.

The OLAP dimension tables are updated in the same way as the fact tables. If a business entity exists in several time-dependent versions, the version valid at the time of the update is transferred.

The Partners, Items and Storage locations applications display the OLTP statistics graphically. The system does not provide any applications for analysing OLAP statistics. You analyse the OLAP statistics using external applications. The ERP system ODBC driver supports external access to the statistical data using, for example, the products Cognos^® and Crystal Reports^® . A description of the ERP system ODBC driver can be found in the “ODBC data sources” documentation. Of course, it is also possible to analyse OLTP statistics using external applications. This makes it possible, for example, to create a report based on the monthly customer-sales-article statistics, which lists the ten best-selling articles per customer and calendar month.

The reorganization of business entities in the OLTP database has no effect on the facts and dimensions already transferred in the OLAP statistics. If, for example, an already reorganized item is entered again in the OLTP database, this leads to the creation of a new data record in the dimension table when the statistics dimensions are next updated. The OLAP statistics then contain two sales items with the same item number but with different identifications (attribute: id). If you now analyse the sales achieved for each item number, the sales of the two sales items are displayed together. If you analyze the sales according to the unique identification, the sales of the two sales items are displayed separately. As the OLTP statistics are directly linked to the business entities Partner and Item, the reorganization of the sales item in the example above leads to the deletion of all facts for the item in the OLTP statistics.

Data import and export

You can use the Import data application to transfer facts to the following fact tables of the OLAP statistics:

• Sales statistics
• Purchase value statistics
• Cost value statistics
• Sales forecasts
• Purchasing forecasts

The sales and purchasing forecasts can be both imported and exported. The other fact tables can only be imported. Procedures for importing the fact tables can be found in these articles:

• Import cost value statistics into OLAP database
• Import purchase value statistics into OLAP database
• Import purchasing forecasts into OLAP database
• Import sales forecasts into OLAP database
• Import sales statistics into OLAP database

Further information on the Import data application can be found in the Import data article.

Authorizations

Authorizations can be assigned both with the help of authorization roles and by assigning an organisation. The authorisation concept can be found in the technical article Authorizations.

Special capabilities

There are no special capabilities for the applications.

Every user who is authorized to open the Partners, Items or Storage locations applications can also call up the OLTP statistics under the Statistics tab.

To be able to analyse the OLAP statistics, you need both the ability to use the ERP system ODBC driver and the corresponding authorizations on the OLAP database.

In order to update statistics facts and statistics dimensions and to recompile the statistical facts, you need the ability to run batch applications.

Organizational assignments

An organizational assignment is not required for the applications.

Special features

The selected OLTP database must be connected to an OLAP database for the batch application Update statistics dimensions to be visible.

Authorizations for business partners

The applications are not released for business partners.