OLAP Preparation and Application
Table of contents
- 1 Introduction
- 2 IDL Konsis Adjustments
- 2.1 Reports, Positions and Accounts
- 2.2 Charts of Positions (POSDFN)
- 2.3 Positions (POSDFN)
- 2.4 Position and Account Allocations (POSKTO)
- 2.5 Report Ident (REPDEF)
- 2.6 Report Line Descriptions (REPDEF)
- 2.7 MIS Parameter (MISPAR)
- 3 MIS Parameter
- 4 Create MIS Data Tables
- 5 Monetary Tables in the MIS Evaluation Data Sets of IDL Konsis
- 5.1 Database Table K810 / K850
- 5.2 Database Table K811 / K851
- 5.3 Database Table K812 / K852
- 5.4 Database Table K813 / K853
- 6 Performance Problems
Nearly all hard- and software designations mentioned in this documentation are registered trademarks or should be understood in this way.
1 Introduction
1.1 Two Steps of Transition
IDL Konsis is based on a relational database. The database tables are interrelated with each other by several technical and functional relations, that are evaluated by special application functions. Thus the analysis and reporting facilities are limited.
On the other hand many users demand a flexible and individual presentation of the data defined in IDL Konsis like offered by analysis and reporting tools (called OLAP tools in the following) like IDL.DESIGNER or IDL Cockpit. These tools are based on multi-dimensional databases (called OLAP databases in the following) as provided by several producers. OLAP databases require data in a structure, that consists of a defined number of dimensions as well as a hierarchy of objects within these dimensions.
Thus for usage of IDL Konsis data within OLAP tools the data have to be lead over from the relational database into the OLAP database. Within this transition the data have to be widely separated from IDL Konsis specific functional dependencies. Therefore the transition consists of two separated steps:
- Preparation of data for further processing out of the relational database: This step is performed by functions of IDL Konsis. The data required by the OLAP database are suitably converted und provided in special tables (K8xx) of the relational database. This functionality is described in this document.
- Take-over of the data of the special tables of the relational database into the OLAP database: A transformation tool like IDL Importer e.g. is required for this step. However, this step depends on the OLAP database system as well as on the desired dimension model, especially if data from other sources shall be integrated in the OLAP database. Therefore IDL cannot provide a standard procedure for this purpose. Rather the take-over has to be individually configured on the user's site.
As an alternative to this procedure the transformation of data for IDL.DESIGNER is performed in step 1 with aid of special additional database views (IDL Datamart) without usage of the database tables K8xx.
2 IDL Konsis Adjustments
Possibly you have to make modifications on the IDL Konsis data, before you can take them over to an OLAP database. Please check, which adjustments are required for you. E.g. you don't have to reduce the number of report positions to single-line positions, if you already work with single-line positions in your corresponding report. Please read the following description, before you start with modifications.
2.1 Reports, Positions and Accounts
Accounts and positions represent a hierarchical dimension in the OLAP database. The top node of this hierarchy is the report. Typically there are separate reports for balance sheet and profit & loss, which refer to a common chart of positions yet.
These data have to be copied and adjusted, if the following preconditions are not satisfied:
- Single-line positions:
- The description of positions is limited to 70 characters in IDL Konsis. However, for allowance of longer position descriptions you can distribute the text to several positions, where all but the last position are designated by the line type 'T' as text positions, that are not capable of allocation of accounts and amounts. Such pure text positions are not supported by the OLAP systems.
2.2 Charts of Positions (POSDFN)
If you require a new chart of positions for the transition of b/s and p&l data into the OLAP database, you best copy the existing master data record for a chart of positions and modify its description. Within copying the chart the assigned positions are copied along with their position numbers.
2.3 Positions (POSDFN)
After copying to the new chart of positions as well as at subsequent usage of an existing chart of positions the positions have to be modified as follows:
- Descriptions of positions, which in fact represent the description of a real position, but which are extended over several lines (e.g. positions "BA080" to "BA120"), have to be summarised to the description of one real position (e.g. position "BA120" in figure 4). ATTENTION: The position with allocated accounts has to be selected for the summarisation.
- All positions with remainders of the original description have to be deleted, if you had created a new chart of positions. However, when revising an existing chart of positions the positions usually cannot be deleted, as long as there exist references on them. Then you should designate these positions as deactivated by suitable descriptions and setting of the valid-until-date.
Example:
| Position | Position description |
|---|---|
| BA080 | 1. Franchises, trademarks, |
| BA090 | marks, patents, licenses |
| BA100 | and similar rights |
| BA110 | and licenses to such |
| BA120 | rights |
| Position | Position description |
|---|---|
| BA120 | 1. Franchises, trademarks, patents & similar rights and licenses |
2.4 Position and Account Allocations (POSKTO)
If you have created a new chart of positions, then you have to copy the allocations of accounts from the former chart of positions for b/s + p&l (e.g. "BILGUV") to the new chart of accounts (e.g. "MILGUV").
Since IDL.KONSIS currently does not offer a copy function the easiest way is to export the allocations into a text file, modify the chart of position key in the text file via <Find and Replace> and then import this file with the application "Import".
2.5 Report Ident (REPDEF)
If you have created a new chart of positions or your report is not suitable for OLAP transition due to other reasons, then you have to create a new report master record for the balance sheet and for profit & loss. You best copy the respective existing report master record of your previous reports and modify its description with aid of the application "Report definition" (REPDEF). The report line descriptions defined for this report are copied along with the report ident.
2.6 Report Line Descriptions (REPDEF)
The copied report line descriptions refer to the original chart of positions (e.g. "BILGUV" for the balance sheet). They have to be modified to refer to the new chart of positions (e.g. "MGBBIL"). For this purpose you should
- export the existing report line descriptions into a text file,
- delete these report line descriptions (do not forget Save!),
- modify the text file created by the export with an editor, i.e. substitute the chart of positions by "find and replace" (e.g. from "BILGUV" to "MILGUV"),
- delete the lines that refer to the omitted text positions, and
- import the modified text file via the IMPT application.
Even if you want to continue to work with your original report definition the positions have to be allocated to the respective super-ordinate positions (e.g. position BA120 has to be allocated to the super-ordinate position BA070) for a unique determination of the hierarchy of the OLAP database, and the report lines for the positions no longer required have to be deleted.
Please note that there exist two parallel allocation possibilities: one for the IDL.KONSIS reporting and another for the MIS preparation and OLAP transmission. To distinguish these views the application "Report definition" contains a second registry tab "OLAP" with the tree display of the OLAP structure beneath the usual view "Tree".
The context menu of this table contains the menu item "Set standard allocation". This function generates the probably desired entries for the OLAP structure of the complete report. Only if the structure shall be defined different from this standard a manual intervention (shifting the report lines in this view per drag and drop) is required.
These adjustments have to be performed for the p&l report description as well as for the b/s report description.
2.7 MIS Parameter (MISPAR)
After providing the IDL Konsis adjustments you can create an MIS parameter record (application "MIS preparation/parameters" (MISPAR)) and then charge the MIS database tables using the application "Create MIS data tables" (out of the action menu of application MISPAR). You find a detailed description in the following chapters.
3 MIS Parameter
The preparation of the MIS evaluation data in the IDL Konsis database requires additional information (parameters) to select and configure data from the IDL Konsis database tables. This information is stored as an MIS parameter.
3.1 List "MIS Preparation/Parameters" (MISPAR)
After invocation the application "MIS preparation/parameters" (MISPAR) displays all defined MIS parameter records. In principle you can define several parameter records, which are distinguished by an up to 6-digit MIS parameter key and an allocated description.
However, please mind, that the MIS preparation tables can contain data for only one MIS parameter at one time. Thus the data preparation for one parameter always overwrites the data of the previous preparation, even if processed for another parameter. Therefore organisational discipline is required at application of the preparation function. Therefore in practice in most cases the definition of only one MIS parameter is sufficient.
The fifth column of the list table displays the "active flag". It designates the active MIS parameter with value 'A' (only one MIS parameter can be active). All other MIS parameters are designated with 'D' (deactivated). The MIS preparation can be processed only for the active MIS parameter.
Except for the active flag the table displays the key, the short name, the version, the particular parameter values as well as user and timestamp of the last modification of the MIS parameter. The parameter values are documented for the wizard.
Beside the call of the wizard via one of the actions "Create MIS parameter" (star icon), "Change MIS parameter" (pencil icon) or "Copy MIS parameter" the following actions are supported:
- Create MIS data tables
- Calling the application Create MIS data tables: This action is only allowed for the active MIS parameter, which therefore has to be selected before. This action is not permitted for MIS parameters of version '04' (IDL Datamart).
- Display log-file
- Display of the log-file of the last MIS data preparation: This action is independent from selected lines and thus works as a global action. The last protocol is overwritten by the next call of "Create MIS data tables".
3.2 Wizard "MIS Preparation/Parameter"
The wizard for MIS parameters can be invoked only from the table for MIS preparation/parameters. It consists of seven Pages.
Page 1: Description
The first page is titled "Description" just like in other wizards. Restrictions due to validity are not provided for MIS parameters. Following entry fields are contained:
- MIS Parameter ID
- Key for identification of the MIS parameter in IDL Konsis. The key has a maximum of 6 characters with arbitrary values. It cannot be modified by the action "Change".
- Description
- Arbitrary text with a maximum of 70 characters in the language preselected in the header of the MIS parameter table; required entry
- Short name
- Arbitrary text with a maximum of 10 characters in the language preselected in the header of the MIS parameter table for display in the list application
Page 2: Parameter
This page comprehends all mandatory entries and other essential settings.
- Version
- The version number ('01', '02', '03' or '04') distinguishes several variants of the prepared data. In many cases the entry of the following parameters depends on the selected version. You find a detailed description in chapter Create MIS data tables. For the versions '01' to '03' in principal only parameter entries on level of the group (e.g. group chart of accounts, group fact) are admitted for creation of MIS evaluation data. Parameter entries on the level of company data are rejected.
- Activated
- An MIS parameter record can be activated or deactivated. You can create a new MIS evaluation data set only for an MIS parameter designated as Active. Only one parameter record can be Active at one time. If you activate one parameter record, the previously activated parameter record will be deactivated simultaneously.
- Period of data selection
- Entry of a space of time (from period until period) for selection of data. If data shall be selected for only one period then the same period has to be entered in both entry fields.
- Parameters for group structures
- You define with these entries, which group, which fact and which period are to be used for the detection of the group structure (hierarchy of groups and sub-groups as well as companies allocated to these groups) represented in the OLAP cube.
- Cube languages
- Here you determine, in which language(s) the descriptions, e.g. for a position or an account number, shall be prepared. Of course, the corresponding descriptions have to exist in IDL Konsis. At least one language has to be entered and up to 3 languages may be specified.
- Chart of accounts
- Group chart of accounts, required entry for the versions '01' to '03'
- Fact
- At least one fact has to be entered. This entry provides for assuring this requirement for the versions '01' to '03'.
- Clearing of positions and accounts
- Only with version '02' enterable: After creation of the MIS data sets all prepared entries are deleted from the dimension "report position/account number", where no balances and no postings exist for, for the purpose of reduction of the data volume in the OLAP cube.
- Commitment key
- Only with versions '01' to '03' enterable: The entry 'J' designates, that the entered parameter can be applied, i.e. the application Create MIS data tables can be processed for this parameter. However, 'N' designates, that this parameter is currently processed or the processing had been aborted due to an error. In the latter case you find further information in the log-file. This flag prevents, that two users create MIS data tables simultaneously and then obstruct each other.
Page 3: Groups
In Addition to the group entered on page 2 more groups can be specified, e.g. report sub-groups. These groups can be selected in the box "Groups" and then supplemented in the list of allocated groups by clicking the '<' button with the mouse.
With version '01' you can enter a maximum of only four groups, with versions '02' and '03' a maximum of eight and with version '04' an arbitrary number. Each entered group/sub-group involves all subordinate sub-groups automatically. An entered group structure therefore must not be contained in another entered group structure.
Page 4: Facts
In Addition to the fact entered on page 2 more facts can be specified. These facts can be selected in the box "Facts" and then supplemented in the list of allocated facts by clicking the '<' button with the mouse.
With version '01' you can enter a maximum of only 3 facts, e.g. for actual data, planned data and forecast data. With versions '02' and '03' a maximum of eight facts can be entered and with version '04' an arbitrary number.
Page 5: Allocation of periods to facts
This page is activated only in version '04'. Here you have the possibility to restrict the facts allocated on the previous page to certain spaces of time, e.g. facts for actual data to the closed periods and fact for plan data to the future facts.
A box displays the allocated facts. For each fact the period begin and end are displayed. In the beginning these entries are preselected with the space of time entered on page 2. Entries exceeding this interval are not allowed.
Page 6: Reports
With version '01' you can enter a maximum of only 4 reports, with versions '02' and '03' a maximum of 8 report idents and with version '04' an arbitrary number. Without entry of a report the accounts represent a flat dimension without any hierarchy.
The allocation is performed by selecting elements in the box "Reports" and then supplementing them in the list of allocated reports by clicking the '<' button with the mouse.
Only reports with report type = 'E' (b/s + p&l reports) are admitted here for the versions '01' and '02'. With versions '03' and '04' there is no restriction for report types.
Page 7: Multi-language descriptions
Similar to many other wizards the final page allows for the maintenance of descriptions in all activated languages.
4 Create MIS Data Tables
The application "Create MIS data tables" creates data sets for transition to an OLAP database out of the IDL Konsis data tables with respect to the parameters defined with the application MIS preparation/parameter. The processing of these data has to be performed in a further step by special programs (e.g. IDL Importer) and lead over to an OLAP database for analysis and reporting with an OLAP tool (e.g. IDL Cockpit).
The application "Create MIS data tables" can be invoked only by the list application MIS preparation/parameters and only for the versions '01' to '03. MIS parameters of version '04' exclusively serve for IDL Datamart. Here the data for the OLAP cube are read from the IDL.KONSIS database tables with the aid of special database views.
The creation of MIS data sets is performed in several steps, which are shortly listed below. All processing steps as well as occurring errors are logged. You can display the log-file using the action menu item "Display log-file" of the table MIS preparation/parameters.
Advices:
- Depending on the data volume the program may be active for a longer space of time. The current program activity is always visualised in terms of a progress display on the screen.
- Allocations "report position / account" (table K827) and "report position / report position" (table K828) already existing in the MIS data sets are always deleted and newly created.
The single steps of processing are:
- Determination and storage of the group structures
- Determination and storage of the companies
- Determination and storage of the accounts
- Determination and storage of the report structures and report positions/accounts
- Determination and storage of the business units, controlling objects, consolidation functions, posting keys and currency codes
- Determination and storage of the account balances and consolidation postings
- Determination and storage of the Intercompany account balances and shareholdings/participations
- Determination and storage of the controlling balances and consolidation postings with controlling objects
- Determination and storage of the development transactions and consolidation postings with posting keys
- Refresh of the texts, e.g. account descriptions
- Refresh of the dimensions
The database tables charged with the MIS evaluation datasets depend on the version number defined in the MIS parameter:
- Version '01'
- writes the selected monetary data (balances and postings) into the database tables K810 to K813. The fact is not a key in these tables. Thus the number of facts is limited. Version '01' is not updated any more with respect for compatibility to elder interfaces and thus is not emphasized for new interfaces.
- Version '02'
- writes the selected monetary data into the database tables K850 to K853. The fact is a key attribute of these tables. This version generally should be used.
- Version '03'
- writes the selected monetary data into the database tables K850 to K853 like version '02'. However, the master and structure data (e.g. account numbers, report positions) are no more provided in the corresponding K8xx-tables (exception: companies and group structure). Since the allocation of companies to groups is not evaluated the monetary data of all companies are selected and stored. For the same reason no quotas are considered. If applicable this has to be concerned in subsequent processes (e.g. IDL.IMPORTER). This version may be more performant. However, the definition of the transition function requires detailed knowledge of the IDL Konsis data model and should be introduced only by IDL consultants.
5 Monetary Tables in the MIS Evaluation Data Sets of IDL Konsis
The monetary data (balances, details, and consolidation postings) selected for transition to an OLAP database are prepared in database tables described below. If you performed a data preparation with an MIS parameter record with version '01', then you find these data in the database tables K810 to K813, but at preparation with a MIS parameter record with version '02' or version '03' you find these data in the database tables K850 to K853.
5.1 Database Table K810 / K850
These tables contain the account balances and the consolidation postings.
| Column name | Column description | Explanation |
|---|---|---|
| K810_SAL_IST | balance current data | account balance from 1st fact MIS parameter |
| K810_SAL_IST_Q | balance current data quoted | If the company is allocated to a group/sub-group, then this field contains the quoted account balance at consolidation type 'Q' and the total account balance at consolidation type 'V'. For a company consolidated at equity (consolidation type 'E') this field remains empty even if account balances exist. |
| K810_BUC_IST | postings current data | consolidation postings from 1st fact MIS parameter |
| K810_SAL_VOR | balance forecast data | account balance from 2nd fact MIS parameter |
| K810_SAL_VOR_Q | balance forecast data quoted | If the company is allocated to a group/sub-group, then this field contains the quoted account balance at consolidation type 'Q' and the total account balance at consolidation type 'V'. For a company consolidated at equity (consolidation type 'E') this field remains empty even if account balances exist. |
| K810_BUC_VOR | postings forecast data | consolidation postings from 2nd fact MIS parameter |
| K810_SAL_PLA | balance planned data | account balance from 3rd fact MIS parameter |
| K810_SAL_PLA_Q | balance planned data quoted | If the company is allocated to a group/sub-group, then this field contains the quoted account balance at consolidation type 'Q' and the total account balance at consolidation type 'V'. For a company consolidated at equity (consolidation type 'E') this field remains empty even if account balances exist. |
| K810_BUC_PLA | postings planned data | consolidation postings from 3rd fact MIS parameter |
| Column name | Column description | Explanation |
|---|---|---|
| K850_SALDO | balance | account balance |
| K850_BUCH | postings | consolidation postings |
5.2 Database Table K811 / K851
These tables contain the details of the account balances separated for intercompanies, i.e. the shareholding transactions and the intercompany account balances.
| Column name | Column description | Explanation |
|---|---|---|
| K811_SAL_IST_A1 | balance current data detail 1 | shareholding transactions and intercompany account balances from 1st fact MIS parameter |
| K811_SAL_VOR_A1 | balance forecast data detail 1 | shareholding transactions and intercompany account balances from 2nd fact MIS parameter |
| K811_SAL_PLA_A1 | balance planned data detail 1 | shareholding transactions and intercompany account balances from 3rd fact MIS parameter |
| Column name | Column description | Explanation |
|---|---|---|
| K811_SALDO_A1 | balance detail 1 | shareholding transactions and intercompany account balances |
5.3 Database Table K812 / K852
These tables contain the details of the account balances separated for controlling objects, i.e. the controlling balances and the consolidation postings with controlling objects from the table CNSPST.
| Column name | Column description | Explanation |
|---|---|---|
| K812_SAL_IST_A2 | balance current data detail 2 | controlling balances from 1st fact MIS parameter |
| K812_SAL_VOR_A2 | balance forecast data detail 2 | controlling balances from 2nd fact MIS parameter |
| K812_SAL_PLA_A2 | balance planned data detail 2 | controlling balances from 3rd fact MIS parameter |
| K812_BUC_IST_A2 | postings current data detail 2 | consolidation postings with controlling objects from 1st fact MIS parameter |
| K812_BUC_VOR_A2 | postings forecast data detail 2 | consolidation postings with controlling objects from 2nd fact MIS parameter |
| K812_BUC_PLA_A2 | postings planned data detail 2 | consolidation postings with controlling objects from 3rd fact MIS parameter |
| Column name | Column description | Explanation |
|---|---|---|
| K852_SALDO_A2 | balance detail 2 | controlling balance |
| K852_BUCH_A2 | postings detail 2 | consolidation postings with controlling objects |
5.4 Database Table K813 / K853
These tables contain the details of the account balances separated for transaction development columns and posting keys, i.e. the fixed asset transactions, capital transactions, provision transactions and other development transactions of the company financial statement as well as the consolidation postings on development accounts with specification of a posting key.
| Column name | Column description | Explanation |
|---|---|---|
| K813_SAL_IST_A3 | balance current data detail 3 | development transactions from 1st fact MIS parameter |
| K813_SAL_VOR_A3 | balance forecast data detail 3 | development transactions from 2nd fact MIS parameter |
| K813_SAL_PLA_A3 | balance planned data detail 3 | development transactions from 3rd fact MIS parameter |
| K813_BUC_IST_A3 | postings current data detail 3 | group transactions/consolidation postings from 1st fact MIS parameter |
| K813_BUC_VOR_A3 | postings forecast data detail 3 | group transactions/consolidation postings from 2nd fact MIS parameter |
| K813_BUC_PLA_A3 | postings planned data detail 3 | group transactions/consolidation postings from 3rd fact MIS parameter |
| Column name | Column description | Explanation |
|---|---|---|
| K853_SALDO_A3 | balance detail 3 | development transactions |
| K853_BUCH_A3 | postings detail 3 | group transactions/consolidation postings |
6 Performance Problems
6.1 Bad Performance at Creation of the OLAP Cube
Problem: The select statements on the K8xx tables with joins to other tables take very long time at creation of an OLAP cube.
Solution: You should execute the following command on the concerned database tables:
ANALYZE TABLE tab-no ESTIMATE STATISTICS
This command provides for filling the data dictionary with information used by the Cost Based Optimizer at creation of the execution plan. If no statistical information is available for one of the concerned database tables then the Rule Based Optimizer is applied.