Business — Banking — Management — Marketing & Sales

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.

the relational interdependence between tables of the data warehouse

Main dimensions of the Key Indicators’ data warehouse

Main dimensions of the Key Indicators’ data warehouse

Accounting dimensions of the Key Indicators’ data warehouse

Accounting dimensions of the Key Indicators’ data warehouse

Dimensions “key indicators” of the Key Indicators’ data warehouse

Dimensions “key indicators” of the Key Indicators’ data warehouse

Accounting postings

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.


« ||| »

Window washing in manhattan bigapplewindows.com. Tagged as:

Comments are closed.