Project Description
A Specialized I&IT Consultant is required to develop BI solutions using Microsoft Power BI for varying business needs, following all applicable GO-ITS standards and industry best practices while adhering to the existing technological solution framework. The successful candidate will work with various technology teams to translate business needs and work through the analysis and the development phases.
General Skills:
- Architect IT systems, infrastructure, and software solutions to meet business needs and technical requirements.
- Create and maintain detailed architecture diagrams and technical documentation.
- Provide technical support and participate in the development, package creation, code merge, testing and release activities.
- Develop and manage solutions using Power BI, Oracle, MS-SQL, Visual Studio, SSIS, Attunity, ODAC, .NET framework, GIT and SQL-based reporting tools.
- Support application architecture-related activities, documentation, and provide general IT assistance.
Desirable Skills:
• Knowledge and understanding of Information Management principles, concepts, policies and practices
• Ability to translate business and data requirements into specifications sufficient for designing appropriate technology solutions
• Experience with a wide range of methods, tools and techniques for business analysis such as: business functional modelling, business event modelling, business process mapping
• Experience in structured, object oriented and agile methodologies for user requirements
• Knowledge and understanding of Accessibility for Ontarians with Disability Act (AODA) and related regulations and standards
• Provides a comprehensive range of I&IT business analysis services for clients with diverse business and technological needs.
• Experience in the use of Computer Aided Software Engineering (CASE) tools to document conceptual requirements and scope
• Awareness of emerging I&IT trends and technologies
• Prepares and advises on business analysis policies, processes, best practices and standards to promote a comprehensive and consistent business analysis practice within the organization.
Technical Skills:
Power BI:
- Power BI Desktop: Skills in designing and creating reports, dashboards, and interactive visualizations in Power BI.
- Power Query (M Language): Experience in using Power Query for data transformation, ETL tasks, and data cleaning.
- DAX (Data Analysis Expressions): Strong knowledge of DAX for writing measures, calculated columns, and advanced calculations in Power BI.
- Power BI Service: Understanding of publishing reports, setting up dashboards, and sharing reports within Power BI Service.
- Power BI Gateway: Knowledge in configuring and managing on-premises data gateways for live data connections.
- Power BI APIs: Experience in automating tasks and integrations using Power BI REST APIs.
- Power BI Security: Skills in setting up row-level security (RLS) and managing user permissions.
MS SQL Server:
- T-SQL: Proficiency in writing T-SQL queries, stored procedures, and functions for data manipulation and retrieval.
- SQL Server Management Studio (SSMS): Expertise in using SSMS for database management and query execution.
- SQL Server Integration Services (SSIS): Experience in building ETL solutions using SSIS.
- SQL Server Reporting Services (SSRS): Knowledge of creating, deploying, and managing reports with SSRS.
- SQL Server Analysis Services (SSAS): Understanding of building OLAP cubes and data models with SSAS for data analysis.
- SQL Server Performance Tuning: Skills in optimizing queries, indexing, and database performance.
- SQL Server Security: Knowledge in setting up roles, permissions, and managing database security.
Oracle:
- PL/SQL: Proficiency in PL/SQL for writing stored procedures, functions, and triggers.
- SQL: Strong SQL skills for querying, optimizing queries, and working with Oracle databases.
- Oracle Database Administration: Basic skills in administering an Oracle database, including backup, restoration, and performance tuning.
- Oracle Data Modeling: Familiarity with database design and data modeling concepts using Oracle.
- Oracle E-Business Suite/Oracle BI: Experience with Oracle BI tools if applicable.
- Oracle Performance Tuning: Knowledge of optimizing Oracle database performance, including indexing, query optimization, and identifying bottlenecks.
Data:
- Data Warehousing: Understanding of data warehousing concepts, including star and snowflake schemas, and ETL processes.
- Data Modeling: Ability to design efficient data models for reporting and analysis.
- Version Control: Familiarity with version control tools like Git for managing code changes and collaboration.
Soft Skills:
• A team player with a track record for meeting deadlines
• Problem Solving: Analytical and troubleshooting skills to resolve technical issues related to data and reports.
• Communication: Strong communication skills to collaborate with business stakeholders, understand requirements, and translate them into technical solutions.
Job Description
A Specialized I&IT Consultant is required to develop BI solutions using both Cognos and Microsoft Power BI for varying business needs, following all applicable GO-ITS standards and industry best practices while adhering to the existing technological solution framework. The successful candidate will work with various technology teams to translate business needs and work through the analysis and the development phases.
Responsibilities:
- Works in partnership with clients, advising them on information technology in order to meet their business objectives or overcome problems, works to improve structure and efficiency of an organization’s I&IT systems.
- Provides subject matter expertise in their field and highly expert technical assistance.
- Acts as the technical advisor/expert on all aspects of a specific deliverable
- Anticipates and resolves problems to ensure that the deliverables are completed within budget, to the highest quality, meeting or exceeding expectations
- Develops processes and procedures for implementing deliverables
- Prepares work plan, effort estimates, reports and presentations including options and recommendations, etc.
- Works with clients to determine the technical scope of the project and to determine requirements
- Defines software, hardware and network requirements
- Analyzes I&IT requirements giving independent and objective advice on the use of I&IT
- Designs, tests, installs and monitors new systems and develops solutions and implementation of new systems
- Familiar with change-management principles and methodology
- Knowledge and understanding of Information Management principles, concepts, policies and practices
General Skills:
- Ensure seamless integration of new and existing systems, applications, and platforms.
- Evaluate and recommend technology stacks, platforms, and tools to support business goals.
- Design systems with a focus on security, compliance, and data privacy standards.
- Work closely with stakeholders such as business units, development teams, IT infrastructure teams to ensure alignment with project goals.
- Ensure solutions are scalable, reliable, and can be optimized for future growth.
- Provide technical support and participate in the development, package creation, code merge, testing and release activities.
- Primary interface between technical teams (Dev, QA) and business stakeholders, focusing on requirements clarification and UAT.
- Develop and manage solutions using Power BI, Cognos, Oracle, MS-SQL, Visual Studio, SSIS, Attunity, ODAC, .NET framework, GIT and SQL-based reporting tools.
Desirable Skills:
• Knowledge and understanding of Information Management principles, concepts, policies and practices
• Ability to translate business and data requirements into specifications sufficient for designing appropriate technology solutions
• Experience with a wide range of methods, tools and techniques for business analysis such as: business functional modelling, business event modelling, business process mapping
• Experience in structured, object oriented and agile methodologies for user requirements
• Knowledge and understanding of Accessibility for Ontarians with Disability Act (AODA) and related regulations and standards
• Provides a comprehensive range of I&IT business analysis services for clients with diverse business and technological needs.
• Experience in the use of Computer Aided Software Engineering (CASE) tools to document conceptual requirements and scope
• Awareness of emerging I&IT trends and technologies
• Prepares and advises on business analysis policies, processes, best practices and standards to promote a comprehensive and consistent business analysis practice within the organization.
Knowledge Transfer
- Knowledge Transfer should be done to designated technical cluster FTE.
- Knowledge transfer must be completed two weeks prior to the consultant leaving the ministry.
- All deliverables, including design/supporting/release/training documents must be checked into designated version control repositories (for example, SharePoint, DevOps).
- All final documents and working drafts related to project requirements or business analysis or solution design or solution development or QA testing plans or Architectural artefacts and other working related documents are must be stored on designated project SharePoint folder and Azure DevOps.
- Project manager and designated FTE must be regularly informed in writing (by email) of where documentation has been stored and must be provided a minimum of one walk-through of all documentation as part of the final knowledge transfer activities.
- Knowledge will be transferred through 1 on 1 sessions, emails, document updates and document review with the team.
Deliverables / Responsibilities
Experience and Skill Set Requirements
Technical Experience - 50%
Experience:
The ideal candidate should possess 5+ years of experience as a I&IT Consultant, with a focus on the following project environments:
- Required Skills (3-4 years): Power BI, MS SQL Server, SSIS, Informatica ETL, Cognos Development, Data Migration & Transformation
- Preferred Skills (2-3 years): Oracle, Visual Studio, .NET Framework, Java, Cloud Services, Data Warehousing, Advanced Data Analytics, Version Control
• Extensive experience designing and implementing BI solutions for both on-premise and cloud environments, ensuring scalability, performance, and maintainability.
• Led technology upgrade and migration projects to Microsoft BI Suite, determining which components to migrate, rebuild, or optimize.
• Over 3+ years of hands-on experience in ETL development and data warehousing, including large-scale data migrations and daily operational data loads.
• Deep knowledge of the Microsoft technology stack, including Azure Data Factory, SSIS, SSAS, SSRS, Power BI, and supporting tools like Visual Studio and SQL Server Management Studio.
• Experienced with data governance, role-based access control (Azure AD), and working with multiple database platforms including Oracle and MSSQL.
Power BI Expertise:
• Hands-on experience with Power BI Server (On-Prem) and Power BI Service (Cloud), including report deployment, administration, and publishing.
• Skilled in designing interactive dashboards, reports, and visualizations that meet business user requirements.
• Implemented row-level security, automated data refresh scheduling, and performance optimization for enterprise datasets.
• Knowledge of integrating Power BI with other MS BI tools and Azure services for end-to-end reporting solutions.
Data Modeling & ETL:
• Expertise in designing and developing semantic models, dimensional models, and data warehouse architectures aligned with analytics needs.
• Strong hands-on experience with ETL development using SSIS and Azure Data Factory, including debugging transformations, mappings, and unit testing.
• Skilled in data ingestion, staging, cleansing, and integration from multiple sources to support reporting and analytics.
• Experience with data modeling tools such as PowerDesigner and Erwin Data Modeler for creating and maintaining accurate and efficient data models.
Release Pipeline Automation & Scripting:
• Hands-on experience automating ETL and Power BI deployments using CI/CD pipelines in Azure DevOps, GitHub Actions, or similar platforms.
• Proficient in scripting (PowerShell, Python, or command-line tools) to automate report publishing, ETL job execution, and environment promotion.
• Experienced in implementing automated testing, validation, and rollback mechanisms for smooth production deployments.
• Strong understanding of integrating version control into release workflows to maintain consistent environment and version management.
Release Management & Operations Support:
• Experience creating comprehensive release documentation (Release Book) and coordinating with Release and Operations teams.
• Capable of providing technical support, troubleshooting production issues, and implementing quick fixes in live environments.
• Knowledge of production deployment processes, version control, change management, and environment governance.
• Able to review and improve existing technical documentation, data dictionaries, and source-to-target mappings, providing guidance to development teams.
Documentation:
• Experienced in creating and maintaining comprehensive technical documentation, including data dictionaries, source-to-target mappings, and data flow diagrams.
• Skilled in documenting data models, ETL processes, and BI solution designs to ensure clarity, standardization, and maintainability.
• Provides guidance and feedback to teams on improving existing documentation and aligning it with organizational data governance standards.
• Able to produce clear release and deployment documentation (Release Books) for technical and operational stakeholders.
• Ensures documentation supports troubleshooting, auditing, and compliance requirements across BI and data warehouse environments.
Core Skills - 25%
- Long term vision: be able to work with stakeholders to define technical roadmap for current applications, and provide recommendation
- Working knowledge of systems design and development, and related methods, techniques, practices and standards enable you to analyze and define clients' business and information technology requirements pertaining to data flow and data integration; provide recommendation to business
- Demonstrates Analytical and problem-solving skills to assess clients' business priorities and needs; to evaluate business and technical design alternatives
- Demonstrates ability to guide the architecture leveraging Azure cloud services, including effective standard use of Microsoft Stack tools and services critical for the integrity, quality and pace of delivery.
- Working knowledge of technology, data, databases, applications, and networking techniques, tools and practices enable you to lead the design and development of infrastructure related projects.
Project Experience and Techniques – 10%
- Demonstrates lead design experience with technology upgrade/migration projects, and shows acumen in being able to evaluate and determine the best path going forward
- Demonstrates experience leading discussions with stakeholders to elicit requirements, developing/consulting on options and solutions, and facilitating the resolution of I&IT and Business issues. Ability to drill down on business pain points and determine the right solution, rather than delivering only the item that’s being requested by stakeholders
- Demonstrates development and design experience with SDLC processes, Agile and Waterfall methodologies
Communication Skills -10%
- Demonstrated experience in solving problems analytically, and making sound decisions based on
- Experience in negotiating using both verbal and written communication skills and should have worked in a team-based environment with a proven record of meeting deadlines
- Strong stakeholder engagement, facilitation, negotiation, and consensus building
Public Sector Experience: 5%
- Previous public sector experience in an organization of equivalent size or complexity in Public Service
- Demonstrates experience designing and developing applications that comply with GOITS standards and general processes would be an asset;