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:

must-have-phase-1

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.

would-like-to-have-phase-2

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.

would-be-nice-to-have-phase-3

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.

future-possibility-phase-4

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.

conceptual-data-model

Figure 5. Conceptual Data Model – ORM

logical-e-r

Figure 6. Logical E/R

physical-design

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.

data-mapping-using-orm-figure-8

Figure 8. Data Mapping using ORM

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

ORYX_MEMBER_NOTES_MAS Name Table
0*1 MEMBERSHIP_NUMBER
0*2 SEQ
1 NOTE_DATE NOTE_DATETIME EMPO_CUSTOMER_NOTE_TRN
2 NOTED_BY ALIAS_ID_NAME EMPO_CONTACT_REF
3 NOTES NOTE_DETAIL EMPO_CUSTOMER_NOTE_TRN
4 ORDER_NUMBER ORDER_NUMBER EMPO_SALE_ORDER_TRN
5 SEQ_KEY SEQ_KEY EMPO_CUSTOMER_NOTE_TRN
6 CALLCENTRE CALL_CENTRE_CODE_ID EMPO_CUSTOMER_NOTE_TRN
7 NOTE_NO NOTE_NO EMPO_CUSTOMER_NOTE_TRN
8 DUMMY_PROMPT
9 KEYWORD KEYWORD EMPO_CUSTOMER_NOTE_TRN
10 END_DATE END_DATETIME EMPO_CUSTOMER_NOTE_TRN
11 ACTIVE ACTIVE_FLAG EMPO_CUSTOMER_NOTE_TRN

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.

custom-built-revelation-etl-tool

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.

Our Partners

We have a number of high quality partners, who we combine with to create fantastic services for our valued clients:

Sybase Revelation Software SGI

more...

Our Clients

We have established relationships with many notable and important clients:

TVSN NRMA Powerforce

more...