56 Enabling Smarter Government with Analytics to Streamline Social Services
Figure 3-19 is a depiction of the SSIRS architecture that shows the component
products used and the conceptual data flow. These components are further
described in the following section.
Figure 3-19 SSIRS architecture
3.3.1 Data sources
In this section we discuss the particular data requirements for SSIRS, which
included a specific set of data sources.
The SSIRS team chose to include client information from the following sources
into SSIRS for analysis and reporting:
򐂰 CIS is a copy of CalWIN, the 18-county TANF consortium system to
determine eligibility and track the delivery of CalWORKs, Medi-Cal, CalFresh,
General Assistance benefits, and other related programs.
򐂰 CWS/CMS is statewide system used by the Department of Social Services
Division for Children and Families to track referrals, placement, and other
child-protective services.
Social Services Integrated Reporting System (SSIRS)
Conceptual Architecture
SQL
ITD
Or ac le
Server
(A)
Email
SQL
Ac ce ss
DB
Mai l
EAS
SQLware
RPT
Staging
-------- +
-------- +
-------- +
-------- +
CRON
C-Shell
Bourne
Korn
Data
Warehouse
DB2
EA S
COGNO S
ve rs a_o ds c mip s _od s
smart_ods
Relation al
DB
F
D
D
D
D D
Star
Schema
BI User
DW
Da sh Bo ar d
CMI PS
Monthl y CD
CalWIN
Oracle
Dai ly F TP
Month ly DVD
Fi l es
CWS
DB2 –Z/OS
Dail y
Partial
Fil es
Versaform
Access DB
Da ily
DOS
Smart - ES
SQL Server
Name St d
Address Std
Entity
Matching
Data Quality
Relation sh ip
Matchi ng
Landing
Zo ne
For
Ex tr a c t
files
CALWIN
DATA
CWS/CMS
DATA
Ver saform
DATA
Sm a rt
DATA
CMIPS
DATA
CMIPS
DATA
DW_ID
SYS_ID
CALWI N/
CIS
DATA
DW_ID
SYS_ID
CWS/CMS
DATA
DW_ID
SYS_ID
Versaform
DATA
DW_ID
SYS_ID
Sm a r t
DATA
DW_ID
SY S _I D
Rep ort
Consumers
Sc ri pts
:
Cl ean Up
Tr a ns for m
Prepa ration
Reorganize
Build
Database
.
Build
TableSpaces
Bui ld Tables
Bui ld Indexes
Bu ild Vi ews
Buil d O bjects
Load Tables
Monitor t he
Database
Mail
3 DVDs
Unzi pped
into 180 gb
of month ly
data
10 Reports
Pow er User s
Powe r Report
Generators
WTW WPR
(Ex em pl a r )
[CalWIN]
SSA
Shared
Drive
ssadw devadm
ETL S oftware:
TALE ND / IBM
IIS
ETL Software
:
TALEND / IBM
IIS
EAS
db
Dat a Extract Phase:
ETL #0
Data Cleansing
Source System
F TP Dai ly Del ta Lo g Fil es
“LA Solution
ssadwdevcog
ETL #1
Talend
ET L #3
BI Me tri c
Studio
Re po r t St ud i o
Cognos
Cu be
ssadw devadm
ssadw devadm
ssadwdevadm
.
ssadwdevcog
Cognos
Packa ge
Cognos
DB
BI
Transformer
Framework
Manager
WT W
Ca sel o a d
[C alWIN]
WTW
Performance
[C alWIN]
GA Caseload
[CalW IN]
GA/FSET
M an a ge me nt
[C alW IN]
SSA C lien t
JOverview
[All syste ms ]
Balderas Eligibilit y
[C alW IN]
I HSS Applic a nts
Smaa rt
[ CMIPS, Smart
Out of Placement
CalW IN P ay me nt s
[CWS, CalWIN]
Pr ob ation acro ss
CW S/CI S
[C alW IN, CW S,
Versaform]
Transform into LMF
EAS Ingest
Alamed a Cou nty
Social Services
Agency
ETL #2 DB 2 L o ad
(large Tables)
Talend other tables
Non-PII Dat ass adwdevadm
UNI_DW_ID
Append
Ori ginating
Sy s t em
ID
An d s o o n
DV D
DV D
Daily FTP to S SABC ORACLE S er ver
Monthly-Around the 1
st
3 DVDs 180gb refresh (14)
Quar terly- FATP o n to an ITD Server
5 16 ta bl e s i n Or ac l e ( us i ng 10 7 ta bl e s with 67 v i ew s)
Key 541Tables
1 Day L atency for Daily FTP
Load
cis_ ods
cws_ods
Key
ETL#4
W he n, Wh er e, Wh o pr ep ar es th e dat a, wh o re ce iv es
th e data , h ow much dat a i s there, fo r ma t of the d ata ,
is the data kept, how long and by whom
Framework
Manager
Framewo rk
Ma n a g e r
Re port
PII Data

Get Enabling Smarter Government with Analytics to Streamline Social Services now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.