Overview:
Reporting to the Manager, Data Services, the Data Warehouse Modeler is responsible for data warehouse models that support MPI’s Enterprise Data Warehouse. These models are used to communicate different levels of detail — conceptual, logical and physical. The Data Warehouse Modeler will focus on relational and dimensional modeling for data warehousing, business analytics processes and advanced analytics/data science needs. This role is critical to ensure optimized serving of data from the corporation’s cloud data warehouse to facilitate a wide variety of analytical data usage.
Working with senior management and departmental leaders, the Data Warehouse Modeler is responsible for developing, implementing, overseeing and maintenance of data warehouse models to ensure effective control and use of data assets. The role is also responsible for ongoing coordination and support of associated processes related to data warehouse modeling. The Data Warehouse Modeler consults and works with departments within IT and other business divisions to implement and maintain associated warehouse model documentation.

Responsibilities:

• Creation, design, and maintenance of relational (entity-relationship) and dimensional data warehouse models – also referred to as Normalized Data Stores (NDS) and Dimensional Data Stores (DDS) respectively- the latter of which must be optimized to enable queries for accessing large volumes of data.
• Collaborates with other team members to design and create NDS and DDS.
• Drives and supports documentation of functional requirements for the Enterprise Data Warehouse.
• Coordinates the application of data warehouse modeling techniques (conceptual, logical and physical modeling for data warehousing) to establish, modify or maintain data warehouse models and their associated components: entities, relationships, attributes, domains, facts and dimensions.
• Conducts requirements analysis and design: documents through a combination of descriptive and tabular formatting, detailed designs which enumerates specifications for each table and column of the model, including data types, definitions, data sources and transformation logic.
• Documents business events within transactional source systems and the business entities that describe the business event.
• Works with data warehouse analysts and data transformation specialists for building or modifying data warehouse tables and Extract, Transform and Load (ETL) processes where applicable.
• Builds dimensional schemas which respond to changes in a source system (slowly changing dimensions).
• Builds wide dimensional tables with a large number of attributes to maximize analytic value.
• Builds dimensional models for analytical reporting that can drill across business processes or business subject areas to compare data from different areas and merge query results from more than one star.
• Tests and validates dimensional models through SQL querying to ensure it meets the requirements and needs of consumers, business groups or departments.
• Reviews end-to end processes for the purpose of data warehouse business case development.
• Consolidate two different models, from legacy and modern systems into one single common data model.
• Liaises and coordinates with management to establish data warehouse needs and objectives.
• Directs and provides recommended methodological approaches and research expertise into data warehousing issues and data strategic initiatives.
• Coordinates and provides oversight leading to the completion of data warehousing best practices activities.
• Influences decisions to ensure that the enterprise data warehouse is managed in ways that support the overall goals of the corporation.
• Participates in and engages others in decision making, information sharing, identifying opportunities for improvement and the resolution of data warehousing issues.
• Works with and collaborates with Data Governance Specialists to ensure that the data warehouse metadata artifacts are established, updated, and maintained as needed (includes data dictionary catalogue, business data glossary, system data quality rules, business data validation rules, documentation related to or impacts the data warehouse).
• Recommends and implements methodologies to ensure effective operation of the data warehouse. Ensures data warehouse is performant to meet expectations and demands of enterprise data warehouse consumers and business users through optimized data warehouse modeling.
• Effectively and critically assesses data warehouse service delivery and data warehouse product performance and determines possible courses of action, demonstrating clear understanding of root cause, impacts, and consequences of decisions.

Qualifications:

Education:

• Degree in Computer Science, Information Systems, Business Analytics, or a related discipline.

Experience:

• Five years of progressive data modeling experience, including:

• • Three years of experience in the creation and maintenance of relational and dimensional data warehouse models optimized for queries for accessing large volumes of data from business transactional source systems.

Technical Knowledge and Skills:

• Demonstrated experience building star-schema models to support analytical reporting of data through the modeling of what is measured in the business process and the context in which such business process measurements are evaluated.
• Explains the development of dimensional models in terms of facts and dimensions, processes and grain, additivity and conformance.
• Understands the impact of analytical modeling decisions made, including deviation from best practices when required.
• Demonstrated experience in documenting a dimensional model’s analytic requirements to capture measurement requirements for different types of business processes, description of stars for each process and the associated conformed dimensions.
• Demonstrated experience at looking across processes and beyond individual stars.
• Demonstrated experience at understanding and documenting business processes to support data warehousing design.
• Advanced knowledge in SQL and querying.
• Experience working on cloud (Azure services such as Synapse and Power BI) and Python is an asset.
• Experience with Data modeling tools used to support data warehouse development.
• Experience with master data management, metadata management, data quality management and methodologies around valuation of critical data assets.
• Establishes and maintains effective relationships and communication with business partners.

Employee Benefits:

• Creation, design, and maintenance of relational (entity-relationship) and dimensional data warehouse models – also referred to as Normalized Data Stores (NDS) and Dimensional Data Stores (DDS) respectively- the latter of which must be optimized to enable queries for accessing large volumes of data.
• Collaborates with other team members to design and create NDS and DDS.
• Drives and supports documentation of functional requirements for the Enterprise Data Warehouse.
• Coordinates the application of data warehouse modeling techniques (conceptual, logical and physical modeling for data warehousing) to establish, modify or maintain data warehouse models and their associated components: entities, relationships, attributes, domains, facts and dimensions.
• Conducts requirements analysis and design: documents through a combination of descriptive and tabular formatting, detailed designs which enumerates specifications for each table and column of the model, including data types, definitions, data sources and transformation logic.
• Documents business events within transactional source systems and the business entities that describe the business event.
• Works with data warehouse analysts and data transformation specialists for building or modifying data warehouse tables and Extract, Transform and Load (ETL) processes where applicable.
• Builds dimensional schemas which respond to changes in a source system (slowly changing dimensions).
• Builds wide dimensional tables with a large number of attributes to maximize analytic value.
• Builds dimensional models for analytical reporting that can drill across business processes or business subject areas to compare data from different areas and merge query results from more than one star.
• Tests and validates dimensional models through SQL querying to ensure it meets the requirements and needs of consumers, business groups or departments.
• Reviews end-to end processes for the purpose of data warehouse business case development.
• Consolidate two different models, from legacy and modern systems into one single common data model.
• Liaises and coordinates with management to establish data warehouse needs and objectives.
• Directs and provides recommended methodological approaches and research expertise into data warehousing issues and data strategic initiatives.
• Coordinates and provides oversight leading to the completion of data warehousing best practices activities.
• Influences decisions to ensure that the enterprise data warehouse is managed in ways that support the overall goals of the corporation.
• Participates in and engages others in decision making, information sharing, identifying opportunities for improvement and the resolution of data warehousing issues.
• Works with and collaborates with Data Governance Specialists to ensure that the data warehouse metadata artifacts are established, updated, and maintained as needed (includes data dictionary catalogue, business data glossary, system data quality rules, business data validation rules, documentation related to or impacts the data warehouse).
• Recommends and implements methodologies to ensure effective operation of the data warehouse. Ensures data warehouse is performant to meet expectations and demands of enterprise data warehouse consumers and business users through optimized data warehouse modeling.
• Effectively and critically assesses data warehouse service delivery and data warehouse product performance and determines possible courses of action, demonstrating clear understanding of root cause, impacts, and consequences of decisions