Enterprise Data Warehouse for TVSN Channel

Business Case

Replace a user developed MS Access Data Mart.

The business case:

1. Reduce time to produce Management Reports
2. Handle larger volumes of Data
3. Simplify schema for easier use by End Users
4. Open Data Warehouse to more End Users for Adhoc reporting
5. Reduce reliance on Key Personnel (ie. the user who created the original MS Access DB)
6. Make Data Warehouse Enterprise Wide Capable ie. allow multiple companies to import data into Enterprise Data Warehouse for Corporate Reporting.
7. Global Corporate Product and Customer Referencing.
8. Cater for Corporate Data Volumes
9. Include Time Management System into Data Warehouse

The Return On Investment here is the contingency of Key man reliance and time to produce management reports critical to business decisions.

Split Business Case priorities:

1. Must Have
   1. Migrate from MS Access to Sybase ASE for Enterprise Scalable Solution
   2. For Ease of Use – Redesign using Star Schema Dimensional Modelling
   3. Reduce Reliance on Key man by exposing Data Warehouse to a wider community of users.
2. Would Like to Have
   1. Make Data Warehouse Enterprise Wide Capable. This was achieved by adding company identifiers to applicable Dimension Tables.
   2. Include Time Management System into Data Warehouse
3. Would be Nice to Have
   1. Cater for Corporate Data Volumes – this will be handled by Sybase IQ
4. Future Possibility
   1. Global Corporate Product & Customer Referencing. This requires an optimisation process to cross reference corporate wide customers and products.

Scope:

Figure 1. Must Have Phase 1

Phase 1

Must Have - TVSN Staging on Sybase ASE

• Dimensional Enterprise Data Warehouse (Star Schema)
• Develop Revelation based Extraction Translation Load (ETL) tool
• User Dimensional Enterprise Data Warehouse walkthru
• Map TVSN Legacy Data to Sybase ASE Staging Area
• Export TVSN Legacy Data to Sybase Staging Area
• Users to translate current Access Database reporting to use new Staging Area
• User Acceptance Testing
• Parallel Run
• User Report Writing.

Figure 2. Would Like to Have Phase 2

Phase 2

Would Like to Have - TVSN Dimensional EDW on Sybase IQ

• Include Time Management System Data into Dimensional EDW
• Create Sybase IQ Dimensional EDW
• Populate IQ via MS/SQL staging
• Convert MS/SQL reporting to Sybase IQ
• User Acceptance Testing of IQ.

Figure 3. Would be Nice to Have Phase 3

Phase 3

Would be Nice to Have - Incorporate Direct Group into Dimensional EDW

• Source Foxpro to MS/SQL Database ETL tool
• Enhance Dimensional EDW for Innovations & Homecare Requirements
• Innovation to export data to Innovation MS/SQL staging area
• Homecare to export data to Homecare MS/SQL staging area
• Develop Reporting for Innovations & Homecare
• User Acceptance Testing.

Figure 4. Future Possibility Phase 4

Phase 4

Future Possibility - Direct Group - Global Reference Project

• Analyse Global Product Identifier (APN/UPN)
• Analyse Global Customer Identifier (possible use of DPID)
• Design Global Reference Staging Database
• Enhance Dimensional EDW to cater for Global References
• Create Global Cross Reference Engine
• Run Cross Reference Engine over TVSN, Innovations & Homecare Products and Customers.

Identify Host Data Sources – for Phase 1

Legacy Data from proprietary Revelation Post Relational Database this database is not open and is complex to retrieve data from.

Conceptual Design was based on Object Role Modeling (ORM) using Microsoft® Office Visio® for Enterprise Architect. This product also has the capability of 5NF translation refer figure 6.

Figure 5. Conceptual Data Model – ORM

Figure 6. Logical E/R

Figure 7. Physical Design (PowerDesigner)

Data Mapping

By using Object Role Modeling with Data Mapping we found that we had a more visual view of the mapped relationships.

Figure 8. Data Mapping using ORM

Once this was achieved we then produced an excel spreadsheet of the maps.

Data Migration

Due to the proprietary Revelation Database, we custom built a new ETL tool to perform the Physical mapping and migration from Revelation to Sybase ASE.

Figure 9. Custom built Revelation ETL tool.

User Acceptance Testing is successfully being performed by the original Business Analyst who developed the MS Access Database.