SoftPro Tetral  From the production data to the decision-support system - Medika d.d.    Solutions  
Case Studies
From the production data to the decision-support system

Beside the production system for sales and purchasing processes, Medika actively used its reporting subsystem which provided end users with data in form of reports, as a support for control of business processes and actions. In order to provide end users with crucial data and information as quickly as possible, Medika d.d. decided to implement a Data Warehouse system based on Microsoft SQL Server 2000. Implementation also included Microsoft SQL Server Analysis Services for data analysis purpose as well as SoftPro Manager 2000 application as an end-user platform.

Medika logo
Situation

Present production system, developed according to needs of Medika d.d., was based on Recital 4GL & RDBMS applications. Applications have been developed for years with one primary aim to ensure daily functioning of information system. According to that information system’s concept, gathering information required for the business decision process took minimal attention. Reporting system, in organizational terms, did not exist. Reports have been made according to users’ requests on a daily basis. Reports often had to incorporate data from previous time periods, so, in order to make data access optimal even on a large set of transactions, a data aggregation file system based on monthly decades and months in year was invented.

Although that system satisfied end users’ requests for basic daily reporting, soon it was quite obvious that it is inadequate for any complex reporting and analysis. Primary that came across when the quantity of data in the production system considerably increased, as well as the quantity of information requiring a course of action to get some results.

Particularly that came across on reports and data analysis that encompassed longer time periods. In that case, preparation and creation of report or analysis took several hours. These cases often required for an additional development of special application components and supporting data files - meaning considerable IT resources involvement too.

Graphical interface was additional problem which, according to the fact that all applications worked in character mode, was impossible to implement. One part of the data delivered through the reporting system has been transferred to Excel requiring additional manipulation.

While the quantity of data progressively increased, users also demanded for the new reports beside the standard ones insisting on additional data analysis as well. Moreover, users wanted faster information access, faster report development and a possibility for interactive analysis.

Solution

Present system, based on Recital 4GL & RDBMS, was unable to provide such a solution, so a decision to implement a Data Warehouse system emerged in order to facilitate a Business Intelligence system development. The implementation incorporated Microsoft SQL Server 2000 as a platform and SoftPro Manager 2000 as an end-user application. DataWarehouse system implementation completely took place in compliance to the Microsoft Data Warehouse Framework specification.

After the production system’s daily processing (Recital 4GL + HP UX), data is copied to a shared directory on a Data Warehouse server. Directory mapping was performed through Microsoft Windows Service for UNIX 2.0. Furthermore, data is transformed to the Data Warehouse database, where additional processing as well as reports and analysis preparation takes place. The complete transformation process was implemented with Microsoft Data Transformation Service (MS DTS) which plays execution as well as control part of the whole process. A set of 75.000 transactions from the production system processes in one transformation cycle.

Central Data Warehouse database is a relational database implemented on SQL Server 2000, holds some 25.000.000 transactions and consists of structures covering the following business processes: sales, purchase and warehouse inventory managing.

Data Warehouse serves as a reporting database as well as for an analytical database development. All analytical database implementation took place with Microsoft Analysis Service 2000 (MS SQL Server OLAP Service).

Covering the wide range of user needs, from simple reporting to more complex analysis, Manager 2000 Enterprise Edition was the choice of an end-user platform implementation.

Benefits

Using SQL Server and Manager 2000, Medika d.d. succeeded in implementation of system that significantly contributed the overall quality of IT support to business decision making process.

Briefly, here are the most important benefits and advantages of the new system:

  • Interactive use of the reporting system
    Users can interactively (on-line) use their reporting system through a set of predefined reports
  • Interactive data analysis
    Analytical databases provide interactive complex data analysis of a Data warehouse
  • Significant decrease in resources required for preparation and development of reports as well as data analysis
  • Single data warehouse
    All production system data is saved in unique structures in a central Data Warehouse database

Using provided functionality, managers have the ability to rapidly access information making business decisions even faster and more efficient.

Copyright © 1993 - 2017 Business Intelligence Website. All Rights Reserved.  SoftPro Tetral