RQ09983 - Solutions Designer - Senior

Responsibilities: 

Undertakes the design of hosting technology solutions based on the client's service specifications, standards, policies, best practices and cost models, in order to meet client application business requirement and project/initiative deliverables. The consultant will provide services under the direction of the Senior Technical Manager and Project Manager.

Key deliverables to be performed by the consultants include, but are not limited to, the following:

• Participating in the analysis, design, development, and unit testing of all the project’s ETL components i.e. Mappings, Workflows, Data Mart.
  


• Develop ETL routines and scripts based on submitted designs.
  


• Developing, testing, and deploying full code package for the solution while maintaining code in version control tooling.
  


• Participating in project design and code walkthrough.
  


• Experience optimizing database performance during and after migration, including indexing, query optimization, partitioning, and database configuration for SQL Server.
  


• Provide input in creation of change requests.
  


• Extensive experience working with both Oracle and SQL Server databases, including knowledge of Oracle SQL, PL/SQL, T\-SQL, and SQL Server\-specific features.
  


• Provides expertise and assistance to cluster functional areas and external service providers in delivering effective technology solutions to clients.
  


• Submit deliverables for the cluster/ministry approval.
  


• Support the analysis of existing business processes, design and recommend process improvements to effectively harness the benefits of using the application(s).
  


• Work closely with the director, managers, and staff on issues and business needs.
  


• Work collaboratively with other groups to ensure optimal integration.
  


• Comply with the Ontario government and the cluster/ministry’s guidelines, standards and procedures such as: architecture/ technology standards and best practices conflict of interest and confidentiality guidelines security procedures and practices Ontario Government.
  


• I&IT Directive, operational policy on the I&IT project gateway process; and other applicable guidelines, standards and procedures.
  


• All General Skills outlined in the “Task\-Based I&IT Services” Agreement Solution Designer, Senior.
  


• Demonstrated development and solution design experience in an environment where Oracle, Informatica and Cognos are used together.
  


• Strong understanding of data modeling, relational database design, and experience with schema conversion between Oracle and SQL Server, including differences in data types, constraints, indexes, stored procedures, and views.
  


• Demonstrated experience developing the Application Code package using MS Visual Source Safe (VSS), Team Foundation Server (TFS) and documenting code changes.
  


• Demonstrated experience in working with and analyzing large and diverse data sets, data visualization, KPI development and data profiling to identify relationships and themes.
  


• Demonstrated experience manipulating and analyzing complex, high\-volume data from structured and unstructured sources.
  


• Demonstrated experience creating detailed documentation for the tools that are developed, such as business requirements, system requirements and technical requirements.
  


• Demonstrated experience working with both the business users and IT development teams to ensure business requirements are properly reflected in the system design and technical specifications.
  


• Demonstrated experience eliciting requirements, developing/consulting on options and solutions, facilitating the resolution of I&IT and Business issues, and providing effective guidance to Ministry clients.
  


• Demonstrated development and solution design experience with Oracle 11.x and 12.x databases.
  


• Experience converting Oracle PL/SQL stored procedures, functions, packages, and triggers to equivalent T\-SQL code in SQL Server, with a strong understanding of differences in syntax and functionality.
  


• Demonstrated experience designing technology solutions and working on projects within a multi\-client/partner environment, involving multiple highly inter\-dependent applications and/ or data sources.
  


• Demonstrated experience developing star schema multi\-dimensional models and documenting detailed design models.
  


• Demonstrated analytical, problem\-solving, decision\-making skills, interpersonal and negotiation skills.
  


• Demonstrated experience performing system testing and unit testing, and conducting SIT, SAT and UAT Testing.
  


• Demonstrated development and design experience with SDLC processes, Agile and Waterfall methodologies.
  


• Demonstrated written and oral communication skills, as well as presentation skills to technical and non\-technical audiences.
  


• Proficiency with ETL tools such as SQL Server Integration Services (SSIS), Informatica, Talend, or custom migration scripts to facilitate the extraction, transformation, and loading of data from Oracle to SQL Server.
  


• Demonstrate experience in managing deployment processes and hand\-off, documentation and KT to operation and other teams.
  

Requirements

Experience and Skill Set Requirements:

Must Have:

• Demonstrates experience designing, developing, and implementing Informatica ETL solutions.
  


• Demonstrates development and solution design experience in an environment where Oracle, Informatica and Cognos are used together for data warehousing and business intelligence.
  


• Experience with data transfer technologies such as Microsoft MQ, SSIS, SSRS, SSAS.
  


• Demonstrates Experience using PowerDesigner to review data models, as well as make changes and generate DDL scripts.
  

Skill Set Requirements:

Technical Experience:

• Demonstrates experience designing, developing, and implementing Informatica ETL solutions.
  


• Demonstrates development and solution design experience in an environment where Oracle, Informatica and Cognos are used together for data warehousing and business intelligence.
  


• Demonstrates experience developing the Application Code package using MS Visual Source Safe (VSS), Team Foundation Server (TFS) and documenting code changes.
  


• Demonstrates working experience in debugging mappings, transformations, mapplets and source qualifier ETL components.
  


• Demonstrates development and solution design experience with Oracle 11.x and 12.x databases.
  


• Demonstrates experience constructing complex query statements and enhancing performance of SQL Queries.
  


• Demonstrates experience designing and developing Unix shell scripts.
  


• Demonstrates Experience using PowerDesigner to review data models, as well as make changes and generate DDL scripts.
  


• Demonstrate experience designing, developing and implementing Reports, Dashboards and other Business Intelligence solutions using Cognos 10 or higher and Microsoft Power BI.
  


• Experience with data transfer technologies such as Microsoft MQ, SSIS, SSRS, SSAS.
  


• Experience in leading development and modelling of applications, specifically hierarchical data model applications.
  


• Working knowledge of cloud infrastructure and ETL tooling in the cloud such as Azure and Databricks is an asset.
  

Core Skills:

• Demonstrates development and solution design experience with technology, data, databases, statistics, applications, and networking techniques, tools and practices that enables the candidate to lead the design and development of business intelligence projects.
  


• Demonstrates experience in working with and analyzing large and diverse data sets, data visualization, KPI development and data profiling to identify relationships and themes.
  


• Demonstrates experience manipulating and analyzing complex, high\-volume data from structured and unstructured sources.
  


• Demonstrates experience creating detailed documentation for the tools that are developed, such as business requirements, system requirements and technical requirements.
  


• Demonstrated experience developing star schema multi\-dimensional models and documenting detailed design models.
  


• Demonstrates experience with advanced data visualization tools and techniques, designing high quality interfaces to present information in a meaningful way.
  


• Troubleshooting of production issues and creating fixes for any unplanned deployments.
  


• Defect investigation, resolution and assignment to team members using defect/issue.
  


• management tools, e. g. HP ALM or HP Service Manager.
  


• Demonstrated experience in coordinating and planning for the modernization of large, complex multi\-platform, multitier information technology systems.
  


• Proven experience using a variety of methodologies to carry\-out the software development lifecycle (e.g., Agile, waterfall, etc.).
  


• Experience with Azure DevOps.
  


• Experience with AODA/WCAG compliance.
  

Project Experience and Techniques:

• Demonstrates development and design experience with SDLC processes, Agile and Waterfall methodologies.
  


• Demonstrates experience conducting system testing and unit testing, and conducting SIT, SAT and UAT Testing.
  


• Demonstrates experience communicating regarding day\-to\-day tasks and issue tracking, reporting and facilitating resolution of issues and risks for all project activities.
  


• Demonstrates experience gathering and consolidating input into changes required to business and/or system requirements, change requests, project artefacts, as well as leading requirement gathering sessions.
  

General Skills:

• Demonstrates experience problem solving and decision making.
  


• Demonstrates experience communicating clearly in both written and verbal formats.
  


• Demonstrates experience working with and leading multiple teams representing various areas in delivering the project.
  


• Demonstrates ability to work collaboratively, with the ability to coordinate multiple projects with competing priorities and a track record for meeting strict deadlines.
  


• Demonstrates experience working collaboratively with other team members and/or groups and leading a team to ensure optimal solution integration.
  


• Demonstrates experience leading a team and with providing technical advice and guidance.
  


• Demonstrates experience working with both the business users and IT development teams to ensure business requirements are properly reflected in the system design and technical specifications.
  

Organization Experience:

• Previous public sector experience in an organization of equivalent size.