Data_Warehouse_Architecture_Presentation

Document Sample
Data_Warehouse_Architecture_Presentation Powered By Docstoc
					              Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer




                                 Page 0
                 Data Warehouse Architecture

                        DMS People First! IT/Data Team:

•Michael Johnston (850)488-3923
DMS People First! IT/Data Team Manager
michael.johnston@dms.myflorida.com
•Brenda Hough (850)488-2277
HR Consultant
brenda.hough@dms.myflorida.com
•Mike Davis (850)487-3230
HR Consultant
•mike.davis@dms.myflorida.com
•Tim Vause (850)921-9740
HR Consultant
•tim.vause@dms.myflorida.com



                                         Page 1
               Data Warehouse Architecture


                           AGENDA
1.   Introductions

2. Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer



                                  Page 2
                        Data Warehouse Architecture
Input Sources
  •PeopleFirst System
        •Wage Req. Detail Info.       •Organization Data
        •Employee\Appointment Data    •Position Data
                                      •Benefits Data
  •BOSP (Salary Detail Information)                          PeopleFirst
  •COPES Historical Data                                                     COPES
                                           BOSP                             Historical
      •Employee Transaction
      •Position Transaction
      •Leave Balance                                                              Training
  •Time Direct Historical Data      Time Direct                                    Direct
  •Training Direct Historical Data
  •DSGI Historical Data
Output                               DSGI
  •File Interfaces
        •HR File Interfaces                                Data Warehouse
        •Benefit File Interfaces
       •Payroll Interfaces
  •COGNOS Impromptu
  •Reports Web Portal
      •Oracle Views
      •Standard Reports                        File                          Reports
                                                             COGNOS
                                            Interfaces                      Web Portal

                                               Page 3
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3. Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer




                                  Page 4
                  Data Warehouse Architecture


       DWSOF All
        Active                                Organization Data
                                              Flair Account Data


               ORG WK
                                              Position Data        Class Data
Organization            Position              Pay Grade Data       Broadband Data
  Active                 Active               Pay Band Data        Building Facilities Data


               POS WK
                                              Appointment Data     Basic Pay Data
                                              W4-W5 Data

Appointment EMPLOYEE Employee
  Active       WK     Active                  Employee Data




                                     Page 5
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4. Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer



                                  Page 6
           Data Warehouse Architecture


                                           Position Rate Information
                             POS Rate          •Organization Code
                                               •Position Number



Position
 Active                                    Fund Code      Other Cost
                                           Fund Type      Accumulator
                           Position Fund       •Organization Code
                               Source          •Position Number




                                           Knowledge\Skills Ability
                                               •OLO Code
                               KSA             •Position Number



                               Page 7
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5. Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer



                                  Page 8
              Data Warehouse Architecture

Appointment
   Active




                                                                Emp Wage Req
              Recurring Pay
                              FMLA Event        Time Sheet         Detail
               Deductions
                                                                   File 1


              One-Time Pay    Appointment
                                               Time Balance
               Deductions      Equipment



                                              Time Evaluation
               Pay Additive   Substitutions                     Salary Detail
                                                 Messages


              Personal Work   Supplimental
                Schedule       Pay Deduct

                                  Page 9
              Data Warehouse Architecture
               PAYMENT AND DEDUCTIONS
Appointment
   Active
                                           Key

              Recurring Pay         •APPT_WK
               Deductions           •WAGE_TYPE
                                    •START_DATE


              Supplimental           •APPT_WK
               Pay Deduct            •WAGE_TYPE
                                     •START_DATE

                                    •APPT_WK
              One-Time Pay
                                    •WAGE_TYPE
               Deductions
                                    •ORIGIN_DATE

                                     •APPT_WK
                                     •WAGE_TYPE
              Pay Additive           •BEGIN_DATE
                                     •END DATE

                                 Page 10
              Data Warehouse Architecture
              TIME MANAGEMENT
Appointment
   Active                                     Key
                                           •APPT_WK
                          Time Sheet       •ATTENDANCE_TYPE
                                           •WORK_DATE
                                           •APPT_WK
                                           •BAL_TYPE
                         Time Balance      •BAL_EFFECTIVE_DATE



                                           •APPT_WK
                         Personal Work     •PWS_DATE
                           Schedule        •DAILY_WORK_SCHEDULE_CLASS

                                           •APPT_WK
                                           •START_DATE
                        Oncall Schedule




                                 Page 11
              Data Warehouse Architecture
              PAYROLL INTERFACE DATA
Appointment
   Active
                                               Key
                                            •APPT_WK
                                            •PAY_PERIOD_BEG_DATE
                        Emp Wage Req        •PAY_PERIOD_END_DATE
                           Detail
                           File 1




                                            •APPT_WK
                                            •BEG PAY PER DATE
                         Salary Detail      •END PAY PER DATE
                                            •RUN TYPE




                                  Page 12
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6. Employee Data

7.   Security/Access

8.   Question And Answer



                                  Page 13
           Data Warehouse Architecture
                                                    Key
Employee
 Active                                     •EMPLOYEE_WK
                                            •ADDRESS_TYPE
                    Address                 •CARE_OF

                                            •EMPLOYEE_WK
                                            •CERTIFICATE_TYPE
                  Certification

                                            •EMPLOYEE_WK
                                            •MAJOR_CODE
                   Education                •EDUCATION_LEVEL


                                            •EMPLOYEE_WK
                                            •LANGUAGE_CD
                    Language


                                            •EMPLOYEE_WK
                                            •LICENSE_NUMBER
                    License

                                  Page 14
               Data Warehouse Architecture


1.   Introductions
                            AGENDA

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7. Security/Access

8.   Question And Answer




                                  Page 15
                                  Data Warehouse Architecture


                     USR_SECURITY_TABLE
     USER_WK   USER_ACTIVE_DATE   USER_ACTIVE_   USER_ID   ORG_WK   ACCESS_FLAG   CONF_IND   EMP_WK   EXTERNAL_
                                  FLAG                                                                USER_FLAG
        1        ’01-JUN-2003’           Y       123456    NULL         SA           Y        3511        N

        2        ’01-JUN-2003’           Y       123457      12         PO           N        3515        N

        3        ’01-JUN-2003’           Y       123457      16         PO           N        3515        N

        4        ’01-JUN-2003’           Y       123458      11         PA           Y        3521        Y

1.      USER_ID is the Employee User Sign-on ID.
2.      ORG_WK links the security to the Authorized Agency or Organization
3.      ACCESS_FLAG determines the level of security access
        •     PA – Privileged Access at the Agency Level
        •     PO – Privileged Access at the Organization Level
        •     SA – Privileged Access to any Agency Statewide
4.      Confidential_Ind provides access to Confidential / Sworn Employee data
5.      EMP_WK links the security record to the Employee table for maintenance
6.      External_User_Flag is used to bypass Employee maintenance




                                                              Page 16
                                   Data Warehouse User ID Securtiy Process Flow
                                  Data Warehouse Architecture


                                                                 1                 HRM




             Agency Security
           Coordinator/Employee               3


                                                                                     2
                     4




                People First                                                    Data Warehouse
                                                                 5              USR_SECURITY
                                                                                    Access
                  Log In




1.   Agency Security Coordinator Requests DW Access for Employee
2.   HRM Adds\Changes\Deletes User to DW
3.   HRM communicates changes to Agency Employee via. email
4.   Employee logs into People First System
5.   Remote Function Call to DW after successful login on People First.




                                                                      Page 17
             Data Warehouse Architecture




Convergys Single Sign-on Tie-In

                                                People First/DW UNIX Servers
PeopleFirst Login                                                No
                    Remote Function Call
                                                Process to
Enter User ID                                   update or
                     Confirm Update                                    Is User ID
and Password                                  insert User ID
                                                                      Valid in PF ?
                                              and password
                                                                       Yes


                                           UserID and Password    User ID and Password




                                                   SAP                   DW
                                                 Database              Database




                                      Internal Network




                                           Page 18
                          Data Warehouse Architecture

DATA WAREHOUSE ACCESS / ENCRYPTION

TNSNAMES.ORA file addition (change all tnsnames.ora files on desktop):

PSOFDW.world =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ???.???.??.??)(PORT = 1599))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = psofdw)
   )
 )

SQLNET.ORA Required File added to the NETWORK/ADMIN folder:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

SQLNET.ENCRYPTION_CLIENT = ACCEPTED
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256,RC4_128,RC4_56,RC4_40)
SQLNET.CRYPTO_SEED = "Z#aY1bX%2cW3d@V4eU5!fT6gS7&hR8iQ9*jP0kO~lNmMnLo?KpJq;IrHsG:tFuEvDwCxByAz"




                                                       Page 19
                               Data Warehouse Architecture

         Data Warehouse Methods and Procedures for Change Management

                                                                                              Data Warehouse
                                    1                        HRM                  2                Staff




      Agency Report
        Developer




                                             7




                                                                    4
                                                                                                    3




          Data                                                                                Data Warehouse
        Warehouse                  6              Work Assignment    5                             Staff




1.   Agency Report Developer requests changes to existing view or new view
2.   HRM verifies request and forwards request to Convergys Data Warehouse Team
3.   Data Warehouse Presents change to the Convergys Change Management Team
4.   Convergys Change Management Team meets on weekly basis to establish Priority/Timeline/Estimate/Due Date
5.   Work Assigned to Convergys Data Warehouse Team and projected completion date relayed to HRM
6.   Changes made to Data Warehouse
7.   Data Warehouse team communicates changes to HRM, HRM Modifies Impromptu Catalog




                                                                        Page 20
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8.   Question And Answer




                                  Page 21
               Data Warehouse Architecture


                            AGENDA
1.   Introductions

2.   Input\Output Sources

3.   Four Major Subject Areas

4.   Position Data

5.   Appointment Data

6.   Employee Data

7.   Security/Access

8. Question And Answer



                                  Page 22
               Data Warehouse Architecture


          Data Warehouse Architecture
1. Data Model

2.   Data Driven “Not” Column Driven

3.   Transaction Data

4.   Historical Data




                                  Page 23
                  Data Warehouse Architecture


       DWSOF All
        Active                                 Organization Data
                                               Flair Account Data


               ORG WK
                                               Position Data        Class Data
Organization            Position               Pay Grade Data       Broadband Data
  Active                 Active                Pay Band Data        Building Facilities Data


               POS WK
                                               Appointment Data     Basic Pay Data
                                               W4-W5 Data

Appointment EMPLOYEE Employee
  Active       WK     Active                   Employee Data




                                     Page 24
                                      Data Warehouse Architecture
  ADDRESS                    EDUCATION                                                             POSITION FUND
                                                          LICENSEACTIVE
   ACTIVE                     ACTIVE                                                               SOURCEACTIVE




                                                                 EMPLOYEE
                                                                   W K
EMPLOYEE   CERTIFICATION       EMPLOYEE        LANGUAGE                                 POSRATE
                                                                                                         POS_WK         KSA ACTIVE
  W K        ACTIVE               WK             ACTIVE                                  ACTIVE



             EMPLOYEE                      EMPLOYEE                                                                         POS_NUM
                                                                                         POS_WK
                WK                            WK                                                                            OLO_CODE
                                                          DWSOF
                                                           ALL
                                                          ACTIVE


            APPT_WK                  APPT_WK                                             APPT_WK                  APPT_WK                  APPT_WK                 APPT_WK

                                                             APPT_WK


 APPT_WK                 APPT_WK                                                                     APPT_WK                   APPT_WK                  APPT_WK
                                               APPT_WK
                                                                            APPT_WK
                                                                                                                                                                              APPT_WK

            ON CALL
                                   FMLAEVENT             RECURRINGPAY                 F01OTHER REQ             F01WAGEREQ                 F13 SALARY              TIMESHEET
           SCHEDULE
                                     ACTIVE              DEDUCTACTIVE                 DETAILACTIVE             DETAILACTIVE              DETAILACTIVE               ACTIVE
            ACTIVE




PERSONAL
                                                                                                   SUPPLIMENTAL                                          TIME
  WORK                PLANNED WORK          ONETIMEPAY                 SUBSTITUTIONS                                        TIMEBALANCE
                                                                                                    PAY DEDUCT                                        EVALUATION
SCHEDULE                TIMEACTIVE         DEDUCTACTIVE                   ACTIVE                                               ACTIVE
                                                                                                      ACTIVE                                         MESSAGESACT
 ACTIVE

                                                                                                                                                                       ABSENCE
                                                                                                                                                                     QUOTAACTIVE




                                                                                      Page 25
               Data Warehouse Architecture


                       Data Warehouse Architecture
1.   Data Model

2.   COPESView to Data Warehouse Mapping Report (IMR)

3. Data Driven “Not” Column Driven

4.   Transaction Data

5.   Historical Data




                                      Page 26
           Data Warehouse Architecture

COPESView Recurring Payment Data in T_APPOINTMENT
CJIP              TRAINER                   LEADWORKER
130.00            83.04                     94.92

Data Warehouse Recurring Payment Data
CJIP                      130.00
TRAINER                   83.04
LEADWORKER                94.92
NEW WAGE TYPE             99.99

•NO TABLE CHANGES
•NO SQL CHANGES
•NO IMR CHANGES




                                  Page 27
               Data Warehouse Architecture


                       Data Warehouse Architecture
1.   Data Model

2.   COPESView to Data Warehouse Mapping Report (IMR)

3.   Reference Report (IMR)

4.   Payroll Data

5.   Data Driven “Not” Column Driven

6. Transaction Data

7.   Historical Data




                                      Page 28
               Data Warehouse Architecture


                    Data Warehouse Architecture
1.   Data Model

2.   COPESView to Data Warehouse Mapping Report (IMR)

3.   Reference Report (IMR)

4.   Payroll Data

5.   Data Driven “Not” Column Driven

6.   Transaction Data

7. Historical Data




                                   Page 29

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:35
posted:8/19/2012
language:Latin
pages:30