Datawarehouse

DATA WAREHOUSING CASE STUDY
Correlating Data Across the Business
Client’s Problem:

Client’s Requirement:

∙ Data is not consolidated and its structure
not suitable to user requirements
∙ Reporting process is slow
∙ Essential business data can‘t be extracted
from the current computing structure
∙ Report distribution is handled manually

∙
∙
∙
∙
∙

Reports to be generated automatically
Reports distribution via web interface
Simplified generation of new reports
Source data to be ingested from client‘s
MagnaQuest ERP system
Minimal demand on current systems

Solution:
Following detailed scrutiny of the requirements, OpenSys proposed and implemented a classic
three layer data warehouse (DWH), utilizing CloverETL as an ETL and data transformation tool.
Data Warehouse Facts:
∙ Based on Microsoft SQL Server 2005 with integrated Reporting Services
∙ Data extracted from MagnaQuest ERP system (based on Oracle 10g Enterprise Edition)
∙ Filled once a day during off-peak hours from stage area (one-to-one copy of source system)
∙ Consists of 4 fact tables and 12 dimensional tables containing data for 21 various reports
CloverETL Performance Facts:
∙ 17 transformation graphs (typical operations include sort, look-up, and join)
∙ Overall duration of single day feed approximately 30 minutes, 3.75 million records / 700 MB

Data Analysis and Reporting Tool

Major international satellite TV
service provider with over
100,000 subscribers in more than
20 countries came to Javlin Inc.
with following problem:
As the number of subscribers grew
and the choice of consumer
packages increased, obtaining
detailed marketing reports became
nearly impossible. Manually created
static reports weren’t sufficient for
major marketing decisions. This fact
resulted in reduced operational
activity and slowed down decision-making process. Moreover, the
company’s computing structure
proved incapable of handling even
regular reporting requirements, let...