Business Intelligence Training Syllabus

1 SQL Server Management Studio (SSMS)

2 Data Visualization with Power BI

3 SQL Server Analysis Services (SSAS) Tabular Model

4 SQL Server Integration Services (SSIS)


Week 1
  • Introduction
  • Microsoft Excel and Access Review

Week 2
  • Installation of Microsoft SQL Server Management Studio Tools
  • Attaching and Restoring Databases
  • Creating Database Objects and building relationships between objects.
  • Understanding the different datatypes in SSMS

Week 3
  • Selecting and Grouping Data
  • Constraints
  • Limiting and Sorting Data
  • Combining Data from Multiple Sources - Joins

Week 4
  • System Functions
  • Working with Variables
  • User Defined Functions
  • Introduction to Performance Tuning and Indexing

Week 5
  • Implementation and Best Practices for Temporary Data Structure
  • Subqueries and Common Table Expression (CTE)
  • Creating and Managing Views
Developing and Implementing Stored Procedures


Week 6
  • SQL Review

Week 7-11 Data Visualization and Business Intelligence with MS Power BI
Week 7
  • General Introduction and Overview
  • Desktop, Service and Mobile
  • Desktop; Author, Publish and Share
  • Power Query, Power Pivot, Power Map
  • Report view, data view, model view

Week 8
  • Connecting data: various connectors; specifically, Flat file, Excel, SQL Server & SSAS
  • Connection mode : import vs direct Quercy
  • Query Editor - ETL Framework
  • Data types, remove columns, remove rows, remove duplicates, make first row header
  • Replace values, filter, fill down, pivot, unpivot, split column, add column, group by , etc
  • Naming conventions
  • Applied steps

Week 9
  • Introduction to advance editor
  • Introduction to append and merge
  • Relationships – the soul of data modelling
  • Single vs bi -directional filtering
  • Cardinality – 1-1,1-m, m-m, m-1
  • Data and Look up tables – star schema model

Week 10
  • DAX - Data Analysis Expressions – The formula Language
  • Calculated columns and Measures
  • Creating a measures table
  • Contexts in Power BI – Evaluation, filter and row contexts
  • Calculate – the most popular and important Dax
  • Calculate () all
  • Aggregates and iterators
  • Building measures on measures (measure branching)

Week 11
  • Visuals – column, bar, stacked charts, line charts, tree map, maps, Cards, Tables, matrix and slicers
  • Edit interactions and sync slicers
  • Tooltips
  • Themes and custom visuals
  • Time intelligence – calendar table, PY, same period last year
  • Publish
  • Power BI service – apps & workspaces
  • Datasets, reports and dashboard
  • Row level security
  • Gateway and refreshes
  • Different ways to publish
  • Subscriptions
  • O&A and quick insights
  • Connecting to Analysis services.
  • Best practices

Week 12 - 17

Week 12
SSIS- ETL
  • Extract, Transformation and Load Basics
  • Variables & Expressions

Week 13

SSIS- ETL

  • Data Warehouse Project - Loading Staging Tables

Week 14
SSIS- ETL
  • Data Warehouse Project - Loading Dimension Tables


Week 15 -17
SSIS- ETL
  • Data Warehouse Project - Loading Fact Tables and Dimensions

Week 18 - 20
  • Building Analytical platform with SSAS tabular Model
  • Dax Expression and Measures
  • Deployment
Health & Medical Informatics

​Next Class Start Date : Saturday March 14th 2020

We will use the following platforms
1. Database platforms: Microsoft SQL Server and SAP Sybase SQL
2. Databases: EHR, PMS, RxNorm
3. Hospital Information System: Medisoft Patient Accounting
4. Tools to install: SQL Management Studio, Advantage Data Architect, Power BI, Mirth Connect

Instructors:
1. Pancratius Mukeh
2. Herbert Lifange
3. Ms. Marie-Noel Mukum
4. Dr. Bakia A.
5. Blaise Ngatcha
6. Joseph Ngum
​
Administrator
1. Verlene Vargas

Training Curriculum
  • Week 1 – 5: Understanding Healthcare Data and Data Architecture
​
​Week 1
  • Introduction to Health Informatics, Health Information System, Health Information Exchange and Health IT by Dr. Kay
  • Introduction to Electronic Health Record Software. Example Medisoft EHR by Pancratius Mukeh
  • Architecture of HIS: Entity Relation Diagram of EHR & Pharmacy IS by Mukeh

Week 2
  • Installation of Microsoft SQL Server Management Studio Tools
  • Attaching and Restoring Databases
  • Creating Database Objects and building relationships between objects.
  • Understanding the different datatypes in SSMS

Week 3
  • Selecting and Grouping Data
  • Constraints
  • Limiting and Sorting Data
  • Combining Data from Multiple Sources - Joins

Week 4
  • System Functions
  • Working with Variables
  • User Defined Functions
  • Introduction to Performance Tuning and Indexing

Week 5
  • Implementation and Best Practices for Temporary Data Structure
  • Subqueries and Common Table Expression (CTE)
  • Creating and Managing Views
Developing and Implementing Stored Procedures

Week 6
  • Clinical Decision Support
  • Medicare 2019 Part C & D Display Measure
  • Meaningful Use
  • Week 7-11 Healthcare Data Visualization and Business Intelligence with MS Power BI

Week 7
  • General Introduction and Overview
  • Desktop, Service and Mobile
  • Desktop; Author, Publish and Share
  • Power Query, Power Pivot, Power Map
  • Report view, data view, model view

Week 8
  • Connecting data: various connectors; specifically, Flat file, Excel, SQL Server & SSAS
  • Connection mode : import vs direct Query
  • Query Editor - ETL Framework
  • Data types, remove columns, remove rows, remove duplicates, make first row header
  • Replace values, filter, fill down, pivot, unpivot, split column, add column, group by , etc
  • Naming conventions
  • Applied steps

Week 9
  • Introduction to advance editor
  • Introduction to append and merge
  • Relationships – the soul of data modelling
  • Single vs bi -directional filtering
  • Cardinality – 1-1,1-m, m-m, m-1
  • Data and Look up tables – star schema model

Week 10
  • Dax! Data Analysis Expressions – The formula Language
  • Calculated columns and Measures
  • Creating a measures table
  • Contexts in Power BI – Evaluation, filter and row contexts
  • Calculate – the most popular and important Dax
  • Calculate () all
  • Aggregates and iterators
  • Building measures on measures (measure branching)

Week 11
  • Visuals – column, bar, stacked charts, line charts, tree map, maps, Cards, Tables, matrix and slicers
  • Edit interactions and sync slicers
  • Tooltips
  • Themes and custom visuals
  • Time intelligence – calendar table, PY, same period last year
  • Publish
  • Power BI service – apps & workspaces
  • Datasets, reports and dashboard
  • Row level security
  • Gateway and refreshes
  • Different ways to publish
  • Subscriptions
  • O&A and quick insights
  • Connecting to Analysis services.
  • Best practices

Week 12- Week 16 Health Level 7 (HL7)

Week 12
  • Concept of Health Informatics, Health Information System, Health Information Exchange and Health IT by Dr. Kay
  • Careers in Health Informatics

Week 13
  • What is HL7?
  • Brief History of HL7
  • Why is it important?
  • Brief history of HL7
  • HL7 Version Standards and Implementation Guides

Week 14
  • HL7 Data Types
  • Categories of Data Types
  • Entity Identifier
  • Hierarchy Designator
  • Message Type

Week 15
  • HL7 Structure and Message Types

Week 16
  • Vocabulary Mapping and Coding Set

  • Week 17– 21 Healthcare Integration Engine using Mirth Connect
  • Implementing and Using Integration Engines