Data Science Diploma

Data Science Diploma

OVERVIEW

The Data Science Diploma course is designed to give the tools and resources required to transform the deluge of information into useful results by managing and utilizing the resources available at your disposal, the major focus area is Microsoft Excel, Power BI, and SQL.

 

AIM AND OBJECTIVES

This practical business analytics session aims to simulate a typical business analysis project.  This would cover essentials task and deliverables which includes:

  • Understanding the business goal of a particular analysis.

  • Selection and determination of the appropriate analysis methodology.

  • Getting business data to support the analysis, from various systems and sources (ETL).

  • Cleanse and integrate data into a single repository.

  • Analyse data to answer business questions.

  • Make a business recommendation.

  • Build a report/dashboard.

  • Present/publish the report to stakeholders

 

SCOPE

The scope of the Data Science Diploma would cover core business analytics hands-on sessions around customer segmentation, sales analytics, and sales forecast. The session would use a practical business case study of a real company (AHG).

 

DURATION

The session is expected to be 2 months long, 2hrs per session, 16 sessions (Saturday and Sunday).

 

BUSINESS CASE

The case study is to investigate the customer demography of the company and investigate which of the company’s sales channels is generating the most revenue. Finally, carry out sales analysis, and forecasts, and make data-driven business recommendations.

 

BUSINESS ANALYSIS TOOLS

The practical hands-on session would utilize Microsoft Excel, Microsoft SQL Server, and Power BI

 

EXPECTED OUTCOME

Participants would get practical hands-on skills as obtainable in practice, participants would also be presented with another case study problem to solve.

 

OUTLINE

Introduction to Microsoft Excel

 Application of Microsoft Excel

 

 Introduction to Basic Functions

  • Left, Right, And Mid Function

  • Upper, Len, Lower and Proper Function

  • Concatenation Application of Concatenation

 

OTHER FUNCTIONS

  • MAX, MIN, TODAY

  • AVERAGE, SUM, SUMIF, SUMIFS

  • COUNT AND COUNTIFS

  • IF, IFS, IF(OR) AND IF (AND)

  • VLOOK UP AND HLOOKUP

  • INDEX, MATCH

 

CONDITIONAL FORMATTING

  • Highlight Cell Rules

  • Data Bars

  • Icon Set

  • New Rules

 

WEEK 1 ASSESSMENT

Introduction to Data Analysis Using Excel

Analysis Tool Pack

Introduction to Descriptive Statistics

 

Data Presentation (1)

  • Graphs

  • Pie Charts

  • Simple Charts & Combo Charts

 

POWER QUERY

  • Connecting Excel to Web Pages to Auto Update Data Tables

  • Connecting Excel Pivot to Web Pages to Auto Update Pivot Tables

  • Connecting Excel to SQL Server

  • Cleaning Data with Microsoft Excel Power Query

 

PIVOT TABLE

  • Creating aPivot Table

  • Understanding Rows, Columns and Values

  • Calculated Fields in Pivot Table

  • Connecting Multiple Pivot Tables

  • Other Pivot Table Functionalities

 

DASHBOARD

  • Understanding Dashboard Presentation Styles

  • Creating Complex and Interactive Dash Board

  • Understanding How to Use Slicers for Dashboard Insights

 

INTRODUCTION TO DATA ANALYSIS WITH POWER BI

INTRODUCTION TO POWER BI

  • Why Power Bi?

 

INTRODUCTION TO POWER BI INTERPHASE AND WORKFLOW

  • Home Tab

  • Insert Tab

  • Modelling Tab

  • View Tab

 

CONNECTING AND CLEANING DATA

  • ETL

  • Basic Table Transformation

  • Text Tools

  • Number Tools

  • Conditional Column

  • Pivoting And Unpivoting

 

CREATING MODEL

  • What is a Model?

  • Understanding Data Table and Lookup Table

  • Understanding Cardinality

 

CREATING MEASURES USING DAX

  • Sum, SumX

  • Average, Average X

  • Max, Min

  • Distinct Count, Count

  • Time Intelligent Functions

  • Filter

  • Calculate

 

DATA VISUALIZATION AND REPORTS

  • Understanding Different Visualization Tools

  • Selecting The Appropriate Visualization Tool

  • Dashboard Visualization

 

WRITING QUERIES WITH MICROSOFT SQL SERVER

 

INTRODUCTION TO DATABASE

  • What is a Database?

  • Database Management Systems

  • Introduction to SQL

  • Different SQL Servers

 

RELATIONAL DATABASE MANAGEMENT SYSTEM

  • Understanding Relationship

  • Primary Key

  • Foreign Key

 

UNDERSTANDING AND MANIPULATING DATA TYPES

  • Characters

  • Numeric

  • Dates

 

BASIC STATEMENTS

  • SELECT

  • WHERE

  • (=EQUAL <> NOT EQUAL > GREATER THAN < LESS THAN >= GREATER THAN OR EQUAL <= LESS THAN OR EQUAL)

  • (BETWEEN, LIKE, IN)

  • AND, OR, NOT

  • NULL VALUES

  • ORDER BY (ASC & DESC)

 

UNDERSTANDING OTHER FILTERS

  • Top

  • Distinct

 

AGGREGATE FUNCTIONS

  • SUM()

  • AVG()

  • MAX()

  • MIN()

  • GROUP BY

 

DATE MANIPULATIONS

  • YEAR (), MONTH (), DAY (),

  • DATEPART (), DATENAME (), DATEADD, DATEDIFF (),

  • GETDATE ()

 

STRING MANIPULATION

  • UPPER (), LOWER (), LEN ()

  • LEFT (), RIGHT ()

  • SUBSTRING (), CONCAT ()

 

HANDLING CASE EXPRESSION

  • SINGLE CASE

  • MULTIPLE CASE

 

JOINS

  • INNER JOIN

  • OUTER JOIN

  • LEFT, RIGHT AND FULL

 

VIEWS

  • CREAING VIEW

  • MANIPULATING VIEWS

 

UNDERSTANDING DDL

  • CREATE

  • DROP

  • ALTER

 

HOW TO IMPORT DATA INTO SQL

  • EXCEL

  • CSV

 

CONNECTING/ EXPORTING SQL FILE INTO EXTERNAL SOFTWARE

  • EXCEL

  • POWER BI

 

CASE STUDY AND SQL ASSIGNMENT