Topic overview

This article explains the base dimensions fiscal year, classification, organization and tag. Find out how the base dimensions are used in the OLAP statistics and which fields they contain. The base dimension Company from Comarch ERP Enterprise 2 is no longer used in Comarch ERP Enterprise 4. It is not covered in this article.

The applications relevant to the base dimensions and their fields, actions and procedures are described in detail in separate article.

Definitions of terms

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 analyzes. 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.

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 analyzes. 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.

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.

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.

Financial year

The fiscal years and fiscal periods are saved in the com.cisag.app.olap.obj.FiscalDay business entity. The dimension table uses the same identification as the Day dimension table. To analyze OLAP statistics per fiscal period, link the two dimension tables together, as shown in the following OQL clause:

FROM com.cisag.app.olap.obj.Day day
JOIN com.cisag.app.olap.obj.FiscalDay fiscal ON
     day:id = fiscal:dayId

In a multi-site environment, the financial years are saved for each company. The organisationalUnitId attribute of the dimension table contains the identification of the respective company. This attribute must be linked to the identification of the organization. The following example shows an OQL clause that allows the evaluation of sales statistics per financial period for the company 00000 in a multi-site environment:

FROM com.cisag.app.olap.obj.Sales facts
JOIN com.cisag.app.olap.obj.Day day ON
     facts:dayId = day:id
JOIN com.cisag.app.olap.obj.OrganizationalUnit salesOrg ON
     facts:organizationalUnitId = salesOrg:id
JOIN com.cisag.app.olap.obj.OrganizationalUnit company ON
     salesOrg:financialOrganizationalUnit = company:guid
JOIN com.cisag.app.olap.obj.FiscalDay fiscal ON
    day:id = fiscal:dayId AND
    company:id = fiscal:organizationalUnitId
WHERE company:code = '00000'

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

dayId – the attribute contains the identification of the Day dimension. A calendar day is therefore referenced using the same numerical identification in the two dimension tables Day and Fiscal year.

organisationalUnitId – the attribute contains the identification of the company that uses the financial year. This attribute is used in a multi-site environment to link the Fiscal year dimension table to the company.

fiscalYearNumber – the attribute contains a sequential number to identify the financial year.

fiscalQuarterNumber – the attribute contains a sequential number to identify the quarter across all financial years.

fiscalPeriodNumber – the attribute contains a sequential number to identify the financial period across all financial years.

fiscalWeekNumber – the attribute contains a sequential number to identify the calendar week across all financial years.

fiscalYear – the attribute contains the calendar year as a numerical value.

quarterOfFiscalYear – the attribute contains the quarter within a financial year as a numerical value. The first quarter of the financial year has the value 1 (one); the second quarter has the value 2 (two) and so on.If the fiscal year has more than twelve calendar months, there will be fiscal periods with the same value for the quarter. In this case, you can use the fiscalQuarterNumberattribute for your evaluation.

periodOfFiscalYear – the attribute contains the financial period within a financial year as a numerical value. The first financial period of a financial year has the value 1 (one); the second financial period has the value 2 (two) and so on.

monthOfFiscalYear – the attribute contains the calendar month within a financial year as a numerical value. January has the value 1 (one); February has the value 2 (two) and so on.If the financial year has more than twelve calendar months, there will be fiscal periods with the same value for the calendar month. In this case, you can use the fiscalPeriodNumber attribute for your evaluation.

weekOfFiscalYear – the attribute contains the calendar week within a financial year as a numerical value. The first calendar week of the financial year has the value 1 (one); the second calendar week has the value 2 (two) and so on.If the financial year has more than twelve calendar months, there will be fiscal periods with the same value for the calendar week. In this case, you can use the fiscalWeekNumber attribute for your evaluation.

lastPeriodOfFiscalYear – the attribute contains the value True if the corresponding calendar day of the Day dimension belongs to the last financial period of the financial year.

firstPeriodOfFiscalQuarter – the attribute contains the value True if the corresponding calendar day of the Day dimension belongs to the first financial period of the quarter.

lastPeriodOfFiscalQuarter – the attribute contains the value True if the corresponding calendar day of the Day dimension belongs to the last financial period of the quarter.

lastWeekOfFiscalYea  – the attribute contains the value True if the corresponding calendar day of the Day dimension belongs to the last calendar week of the financial year.

endOfFiscalPeriod – the attribute contains the value True if the corresponding calendar day of the Day dimension is the last day of the financial period.

Classification

In contrast to the OLTP database, all classifications are stored in a single business entity, com.cisag.app.olap.obj.Classification, in the OLAP database. The dimension can be used to analyze sales statistics per sales item classification or customer classification, for example. Classifications are used in the following dimension tables:

• Organization
• Customer
• Sales item
• Sales forecast
• Purchasing item
• Supplier
• Purchasing forecast
• Opportunity
• Marketing partner (Contact)
• Competitor

To analyze OLAP statistics using the classification, link the classification to the dimension table that uses the classification. The following example shows an OQL clause that allows you to analyze the sales statistics for the sales item classifications 100-110-120:

FROM com.cisag.app.olap.obj.Sales facts
JOIN com.cisag.app.olap.obj.SalesItem item ON
     facts:salesItemId = item:id
JOIN com.cisag.app.olap.obj.Classification c1 ON
     item:classificationId1 = c1:id
LEFT OUTER JOIN com.cisag.app.olap.obj.Classification c2 ON
     item:classificationId2 = c2:id
LEFT OUTER JOIN com.cisag.app.olap.obj.Classification c3 ON
     item:classificationId3 = c3:id
WHERE c1:code = '100' AND c2:code = '110' AND c3:code = '120'

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

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

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

type – the attribute contains the functional assignment of the classification. For example, if the value comes from Sales article classification 3, the type attribute has the value SALESITEM_HIERARCHY3. The following values are possible:

• CUSTOMER_HIERARCHY1 to 9
• SALESITEM_HIERARCHY1 to 5
• SUPPLIER_HIERARCHY1 to 5
• PURCHASEITEM_HIERARCHY1 to 5
• MARKETINGPARTNER_HIERARCHY1 to 5
• OPPORTUNITY_HIERARCHY1 to 5

If the classification represents an organization, the type attribute contains one of the following values for a sales organization, purchasing organization or warehouse organisation:

• SALESORG_HIERARCHY</li>
• PURCHASINGORG_HIERARCHY
• INVENTORYORG_HIERARCHY

code – the attribute contains the code of the classification including all higher-level folders. The attribute therefore corresponds to the path attribute in the OLTP database.

description – the attribute contains the name of the classification.

parentId – the attribute contains the identification of the parent folder in the Classification dimension. If there is no parent folder, the attribute contains the numerical value 0 (zero).

parentGuid – the attribute contains the GUID for the parent folder in the Classification dimension. If there is no parent folder, the attribute contains the ZERO-GUID, i.e. a sequence of 32 zeros.

level – the attribute contains the level of the classification as a numerical value. The first level has the value 1 (one), the second level the value 2 (two) and so on.

leaf – if the node is a leaf, the attribute contains the value True; if the node is a folder, the attribute contains the value False.

nodes – the attribute forms an array with the codes of the first ten levels of the classification. You can use the attribute to successively refine the query to include values at the subordinate level (so-called drill-down). If a classification does not include a level, the array contains an empty string.

nodeDescriptions – the attribute forms an array with the names of the first ten levels of the classification. You can use the attribute to successively refine the query to include values at the subordinate level (so-called drill-down). If a classification does not include a level, the array contains an empty string.

nodeGuids – the attribute forms an array with the guids of the first ten levels of the classification. You can use the attribute to successively refine the query to include values at the subordinate level (so-called drill-down). If a classification does not include a level, the array contains the zero value.

Organization

The organizations are stored in the business entity com.cisag.app.olap.obj.OrganizationalUnit. The dimension can be used to evaluate sales statistics per sales organisation or company, for example.

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

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

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

code – the attribute contains the code of the organization without the parent organizations.

description – the attribute contains the name of the organization.

financialOrganisationalUnit – the attribute contains the Guid of the company to which the organization is assigned.

salesClassificationId – if the organization is a sales organization, the attribute contains the identification for the corresponding classification. The classification is of type SALESORG_HIERARCHY and contains the full path of the sales organization in the code attribute.If the organization is not a sales organization, the attribute contains the value 0 (zero).

purchasingClassificationId – if the organization is a purchasing organization, the attribute contains the identification for the corresponding classification. The classification is of type PURCHASINGORG_HIERARCHY and contains the full path of the purchasing organization in the code attribute.
If the organization is not a purchasing organisation, the attribute contains the value 0 (zero).

inventoryClassificationId – if the organization is a warehouse organization, the attribute contains the identification for the corresponding classification. The classification is of type INVENTORYORG_HIERARCHY and contains the full path of the warehouse organization in the code attribute.
If the organization is not a warehouse organization, the attribute contains the value 0 (zero).

financialOrganisation – If the organization is a company, the attribute contains the value True.

Day

The Day dimension table can be used to assign the statistics facts down to the calendar day. You cannot update the dimension table directly using the batch application Update statistics dimensions. Any missing calendar years are automatically added to the dimension table each time the financial years or statistics facts are updated. The first transfer in a new calendar year therefore automatically generates all calendar days from 1 January to 31 December.

The calendar days are saved in the com.cisag.app.olap.obj.Day business entity. The attributes of the dimension table that are relevant for OLAP statistics are explained below:

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

date – the attribute contains the calendar day as a timestamp.
All timestamps are extracted from the OLTP database without a time zone. When transferred to the OLAP database, the client’s time zone is added to the time stamp. If, for example, a customer invoice has the voucher date 01.10.2005 00:00:00 MSK (Moscow standard time), then this voucher appears in the 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 Comarch ERP Enterprise ODBC driver also supplies the date as coordinated universal time (UTC or Universal Time Coordonné) in the virtual attribute date_gmt. This format is based on the zero meridian through Greenwich. Central European Time (CET) therefore corresponds to UTC plus one hour. During summer time, CET is two hours ahead of UTC. Please note that the date_gmt attribute may therefore have a different value to the dayOfMonth attribute if the time zone is not taken into account.
Calendar days prior to 1 January 1970 are not permitted.

yearNumber – the attribute contains a sequential number to identify the calendar year.

quarterNumber – the attribute contains a sequential number to identify the quarter across all calendar years.

monthNumber – the attribute contains a sequential number to identify the financial period across all calendar years.

weekNumber – the attribute contains a sequential number to identify the calendar week across all calendar years.

dayNumber – the attribute contains a sequential number to identify the calendar day across all calendar years.

year – the attribute contains the calendar year as a numerical value.

quarter – the attribute contains the quarter within a calendar year as a numerical value. The first quarter of the year has the value 1 (one); the second quarter has the value 2 (two) and so on.

monthOfYear – the attribute contains the calendar month within a calendar year as a numerical value. January has the value 1 (one); February has the value 2 (two) and so on.

weekOfYear – the attribute contains the calendar week within a calendar year as a numerical value. The first calendar week of a year has the value 1 (one); the second calendar week has the value 2 (two) and so on.

dayOfMonth – the attribute contains the calendar day within a calendar month as a numerical value. The first calendar day of the month has the value 1 (one); the second calendar day has the value 2 (two) and so on.

dayOfWeek – the attribute contains the day of the week within a calendar week as a numerical value. Monday has the value 1 (one); Tuesday has the value 2 (two) and so on.

lastWeekOfYear – the attribute contains the value True if the calendar week is the last week of the calendar year.

endOfMonth – the attribute contains the value True if the calendar day is the last day of the calendar month.