Design of data warehouse structure for key indicators
Category: Information Systems
When designing the structure for the Key Indicators data warehouse we used PLATINUM software products belonging to the ERwin 3.5.2 and Model Mart family which is used to design transactional databases and data warehouses, generate and maintain databases, design logical and physical structure of DBs, create enterprise models, dimensional models and IDEF1x models as well as information and technical models. These software products support the following databases:
CA-Clipper, CA-OpenIngres, DB2 for MVS and DB2 for OS/390, DB2/400, dBASE, FoxPro, HiRDB, Informix, InterBase, Microsoft Access, Microsoft SQL Server, Oracle, Paradox, Rdb, Red Brick Warehouse, SAS, SQL Anywhere, SQLBase, Sybase and Teradata.
Data storage topology “Snow Flake” was used during creation of the structure of the data warehouse for key indicators.
The data warehouse structure for Key Indicators was created and tested at two physical levels:
— Microsoft SQL Server;
— Oracle.
Two files are attached to this document. To open them you, will have to install PLATINUM ERwin 3.5.2):
| File | Comments |
| KIOracle.ER1 | Data warehouse structure, Oracle. Physical level of data storage |
| KISQLServer.ER1 | Data warehouse structure, MS SQL Server. Physical level of data storage |
These files contain comments on tables, fields and other remarks.
More information on the structure of the data warehouse for Key Indicators can be found in KI DB structure.
Graphs on the next pages explicitly illustrate the relational interdependence between tables of the data warehouse.
Main dimensions of the Key Indicators’ data warehouse
Accounting dimensions of the Key Indicators’ data warehouse
Dimensions “key indicators” of the Key Indicators’ data warehouse
Accounting postings
Meanings of indicators’ value
It is worth mentioning that the warehouse structure enables:
Change, addition, replacement and deletion of any key indicator and their groups (all tables D_xxxxx and D_xxxxx_Unions, S_Units) as frequently as required
Keeping or changing of the tree-like structure for all dimensions (up to the fifth sublevel in the “Snow Flake” topology and unlimited number of sublevels when using reference to parent via Parent_Id in every dimension table)
Setting of limits for key indicators for every specific date
Analysis of key indicators in terms of the following dimensions:
Time
Key indicator
Region
Client
Product
And with the following attributes:
Quantity
Amount
Value
Automatic provision of integrity of data in the Key Indicators data warehouse.
In the structure of the Key Indicators data warehouse, the following facilities are reserved:
Dimensions and groups in the accounting sphere:
Chart of Accounts
Subjects of analytical accounting.
Financial postings as a basis for calculating most non-production key indicators in terms of the following dimensions:
Time
Debtors
Five subjects for analysis on debit side
Creditors
Five subjects for analysis on credit side
Currency of transaction
And with the following attributes:
Amount
Quotation of a transaction currency.
TACIS IT experts installed Microsoft SQLServer 7.0 + ServicePack 3.0 and Personal Oracle 8i on a computer provided by the company. Using this SQL server, IT experts from TACIS created a Key Indicator data warehouse.