Job ID: MI-16649 (90091116)
MI State (must) SSIS/DW Architect with Master Data, Quality of Life DW, DQS and PM experience
Location: Lansing, MI (DTMB)
Duration: 7 months
Skills:
Working experience with the State of Michigan Required 5 Years
Datawarehouse or Data Management experience with the State of Michigan Required 3 Years
The State of Michigan (SOM) is looking for a vendor to provide resources for the Quality of Life DW and Master Data Project. These positions will include:
DATA WAREHOUSE ARCHITECT 3
1) Ability to create write ETL jobs using SSIS, DQS, and other tools.
2) Strong ability to work in a SQL environment.
3) Ability to create scheduler jobs.
4) Ability to model and create reports.
DATABASE ARCHITECT 4
1) Ability to create database design based on customer needs.
2) Strong ability to work in SQL.
3) Strong data architecture principles and expertise.
SOFTWARE SOLUTION ARCHITECT
1) Ability to lead and understand all technical components and integration necessary for successful delivery.
2) Ability to ensure timely delivery of work.
3) Contributes to current state analysis / documentation.
4) Leads Execution Project Planning Activities.
The Master EGLE Interests data warehouse will be implemented through governance of an EGLE IT Core Team, the EGLE Information Management Division, and DTMB Agency Services.
The DWT services are required to augment existing resources and provide leadership and expertise in the creation of a sustainable data warehouse model.
EGLE has a large number of disparate systems that track information on similar, or in some cases the same, site locations throughout Michigan. The goal of this effort is to establish a data warehouse environment that allows EGLE to track high level information and associations on sites, facilities, and locations over time.
The scope of work for DWT resources include, but is not limited to, designing SQL database, building SQL Data Quality Services processes, loading data into the data warehouse and developing reports on the data.
This engagement will include work on the following deliverables:
1) Design SQL Database. EGLE and the corresponding DTMB AS Team use SQL environments and intend to do so for this project as well.
2) Build a load process for a spreadsheet template. Some data sources exist on network drives and may be in Microsoft Access or Excel formats. There is a need to build load functionality for this data, such that any source data in the template will be able to be loaded.
3) Build SQL Data Quality Services (DQS) Process. DQS is envisioned to be a core component of the overall effort. It will be used to help meet the following requirements:
a. Standardize Data. Ensuring data loaded into the master table meets established standards (e.g. Corporation = Corp, …) and has validate or complete data to load.
b. Matching Algorithm. In order to combine data from multiple systems, a process will be developed that scores the likelihood of a match based on factors such as name, address, city, and zip. Records will either be merged, loaded, or identified as needing more action based on this process.
4) Build a mechanism to manually fix data. Data this isn’t merged or loaded may need to be evaluated and updated in order to pass the matching process. A mechanism must be provided to make these data fixes and reprocess the data.
5) Build other transformation and load jobs. EGLE has identified 14 data sources to load data from in this project. It is expected that EGLE/DTMB will extract the data from source systems while the DWT will be responsible for the jobs to load the data. Sources are primarily SQL.
6) Build sequence and scheduler routines. These load jobs are not one time in nature but expected to be updated at least twice a month. Scheduler routines and error or job failure processing must be built.
7) Query and reporting interface. EGLE staff must have the ability to query and report on data. Power BI and other tools may be leverage to provide this ability.
8) Change data mechanism or interface. There must be an ability to update data as errors are found in the Master EGLE Interests data warehouse.
9) Audit / History Tracking. Database design must account for the ability to track changes to data and see history of changes at sites or interests.
The State Project Manager will provide direction to Contractor staff in regard to work effort and priorities.
State will provide telephone access for project-related calls originating from within the State’s office.
State will provide copying equipment for project-related documents.
State will provide access to Facsimile equipment for items that are project related.
State will provide computer hardware and software for resource(s), as deemed necessary
JOB DESCRIPTIONS:
DATA WAREHOUSE ARCHITECT 3
1) Ability to create write ETL jobs using SSIS, DQS, and other tools.
2) Strong ability to work in a SQL environment.
3) Ability to create scheduler jobs.
4) Ability to model and create reports.
DATABASE ARCHITECT 4
1) Ability to create database design based on customer needs.
2) Strong ability to work in SQL.
3) Strong data architecture principles and expertise.
SOFTWARE SOLUTION ARCHITECT
1) Ability to lead and understand all technical components and integration necessary for successful delivery.
2) Ability to ensure timely delivery of work.
3) Contributes to current state analysis / documentation.
4) Leads Execution Project Planning Activities.
Leave a Reply