Data warehousing – Day 1 Empowering DWH & BI Ecosystem

Data warehousing – Day 1 Empowering DWH & BI Ecosystem Sensitivity: Internal

Course Being Offered – Course Outline Week 1 DWH Essentials & SQL Week 2 DWH Architecture & Design Data Analytics Ecosystem Introduction to DWH OLTP vs OLAP Databases VS RDBMS DWH Soft Architecture & layers Implementations of DWH Exercise on DWH Importance Introduction to Teradata Introduction to Teradata HW Architecture Data Science vs Business Intelligence Overview DWH & BI Certifications DWH Project Distribution on Retail Industry DWH Planning Quiz 1 A Job Oriented Course on Enterprise Datawarehouse & Business Intelligence Week 3 DWH Project (ETL Development & Data Quality) Hands on SQL basics Hands on SQL Advanced Teradata Architecture Data Modeling (Conceptual, Logical and Physical Data Model) Normalization Data Quality & Automation DWH Operations ETL ELT/ELTL/Data Lake Retentions, Compressions Modern Data Warehouse vs Traditional DWH Project Distribution on Retail Industry Exercise on Conceptual Data Model SLJM (ETL Framework Overview) Exercise on SLJM Hands on Logical Data Model Hands on Physical data model (Staging layers) Implementing Data Quality in ETL development Queries/Processes Monitoring through TD Viewpoint Quiz 2 Week 4 DWH Project (Automations & Performance Tuning) Sensitivity: Internal Hands on physical Data Model (Foundation layer) Hands on Physical Data Model (Aggregate layer) Building Reconciliation Mechanism across layers of DWH ETL Automations using SLJM Performance Tuning Statistics Viewpoint Query Monitoring, Health Monitoring, Workload Management, Query Spotlight Learning importance of Explain Plan & query Performance Optimization

Course Being Offered – Course Outline Week 5 BI Modeling Week 6 Power BI Business Intelligence & its importance Hands on Business Analysis (Ad-hoc Reporting) hours OLAP - Dimensional Modelling Fundamentals Dimensional Modelling Design with industrial use case Design Steps – Dimensional Modeling (Hands on) Implementation – Dimensional Modeling (Hands on) Slowly Changing Dimensions (SCDs) with use cases ROLAP VS MOLAP Design and implementation of Dimensional Model on Retail Store Data (Assignment) Quiz 3 A Job Oriented Course on Enterprise Datawarehouse & Business Intelligence Week 7 Advanced Power BI & Tableau Basics How to Connect & Import Data from multiple Data sources Reshaping and Transforming Data in Query Editor Data Enrichment (New business Fields) Data Modelling Understanding Cardinalities Building Interactive Visualizations on previously implemented Dimensional Model Animated Visualization Implementation Roll-up/Roll-Down Capabilities Custom visualization in Power BI Introduction to Power BI Services Scheduling Automated Reports Refresh Creating Dashboards & Natural Language Processing in Power BI Services Sharing Dashboards All across Organization Mobile Dashboard Design Introduction to DAX Language Creating DAX Measures Evaluating DAX Measures Leverage Calculate Functions functionality Power Function/ Divide Function MTD, QTD and YTD Date Calculations Business Use Case implementation in Power BI (Assignment) Connecting with Different Data Sources in Tableau Data preparation with Tableau Live Vs Extract Data Source Filters Basic Report Creation Understanding of Rows and Columns Leveraging the Use of Marks Labels to enrich information in Reports Visualization best practices with real world examples Grouping fields in Tableau Interactive Filters Quiz 4 Sensitivity: Internal Week 8 Advanced Tableau Types of filters Advanced Filter Calculations Enhancing user interactivity thorough parameters Pages Maps in Tableau Importing custom geocoding in Tableau Visualize your data on map through spatial files Building a Dashboard Leveraging the use of Interactivity in Dashboards through Actions Designing and implementation of dashboard Designing of dashboard for mobile & Tablets Extensions Enriching information by creating Calculated Fields Calculation Syntax Date/Logic/String Calculations Advance Calculations (LODs) LODs real world Use cases Visual analytics Pareto Chart Business Use Case implementation in Tableau (Assignment)

Introduction Name Education/University Profession/Experience-Years Current Company Expectations from Course Sensitivity: Internal

Data is the new oil. Now data is as essential as water for humans Sensitivity: Internal

What is Sensitivity: Internal Information in raw or unorganized form (such as alphabets, numbers or symbols) A single piece of information A qualitative or quantitative set of values Facts & statistics collected together for reference or analysis

Sources of Data Machines People Organizatio ns Sensitivity: Internal

What is a Data Analytics Data Raw Fact No Context Just Numbers and Text Sensitivity: Internal Information Insights Value Added to Data

INFORMATION? Data (Price,Date) 6.34,1 6.45,2 6.39,3 6.62,4 6.57,5 6.64,6 6.71,7 6.82,8 7.12,9 7.06,10 Information is: Prices are growing Prices are growing with the rate of 10% In week on week comparison we observed higher rise in recent week Data analytics is a process of inspecting, cleansing, transforming, and modeling data with the goal of discovering useful information, informing conclusions, and supporting decision-making Sensitivity: Internal

What is a Database Sensitivity: Internal

Guess a Database Which all of us use almost everyday Sensitivity: Internal

Some Famous DBMSs http://db-engines.com/en/ranking Sensitivity: Internal

How many of us Know about Data Warehouse Sensitivity: Internal

Data warehousing is the process of constructing and using a data warehouse. A data warehouse is constructed by integrating data from multiple heterogeneous sources that support analytical reporting, structured and/or ad hoc queries, and decision making. Data warehousing involves data cleaning, data integration, and data consolidations. Sensitivity: Internal

Characteristics of DWH Subject Oriented Integrated Time Variant Non Volatile Sensitivity: Internal

Sensitivity: Internal

Organization Sales Recharg e Call Center Billing Custom er MFS Sensitivity: Internal

Sales Recharge Call Centre Billing CB Customer Pro MFS Sensitivity: Internal Data Warehous e

New Sales Customers from Chakwal usually recharge their account with 100 Rupees Scratch Card One Window for Organizational Information (BI & DWH) Subject Oriented Sensitivity: Internal MSISDN Level Integrated Time Variant

High Value Customers from Lahore only opting for a Daily Data Bundle on weekends from 11 AM to 5 PM One Window for Organizational Information (BI & DWH) Subject Oriented Sensitivity: Internal MSISDN Level Integrated Time Variant

Easy Paisa Users usually recharge their account after receiving money One Window for Organizational Information (BI & DWH) Subject Oriented Sensitivity: Internal MSISDN Level Integrated Time Variant

360 Degree Profiling of Customer [Decision Making] [Data Warehouse] Sensitivity: Internal

Why Government of Pakistan Needs a Data warehouse Sensitivity: Internal

5 Ws of DWH & BI Ecosystem Advance Analytics Business Intelligence Data Integration Sensitivity: Internal What we have to do on Acti SUGGEST NECESSARY ACTION Ac ti o n Why Happened s Ana lytic ytic s Anal What's the Impact When Happened What Happened Dat a Visu aliz atio n Rep orti ng Fou nd ati o n STATISTICAL ANALYSIS , DATA MINING , PREDICTION on zati i l a Visu DASHBOARDS FOR PERFORMANCE MANAGEMENT INFORMATION DELIVERY ting r o Rep da t n u a Fo t a D ion DATA MANAGEMENT DATA INTEGRATION METADATA

Owner of a Business always want to know about ? Which Whichare areour our lowest/highest margin lowest/highest margin customers customers? Who Whoare aremy mycustomers customers and what products and what products are arethey theybuying? buying? What Whatisisthe themost most effective distribution effective distribution channel? channel? What Whatproduct productpromprom-otions have the biggest -otions have the biggest impact impacton onrevenue? revenue? Which Whichcustomers customers are most are mostlikely likelyto togo go to the competition to the competition? What Whatimpact impactwill will new products/services new products/services have haveon onrevenue revenue and margins? and margins? Sensitivity: Internal

Exercise 1 Why your organization need a Data Warehouse Identify Data sources Identify Integration Points Figure out 5 different questions which your company will be able to answer after data warehouse Sensitivity: Internal

Exercise 2 Why Tehzeeb Bakers don't need a Data warehouse but yet they can generate a lot of value out of data Why? & How ? Sensitivity: Internal

OLTP vs OLAP Online Transactional Processing vs Online Analytical Processing Sensitivity: Internal

OLTP vs OLAP Sensitivity: Internal

Data warehouse is all about Decision Making Sensitivity: Internal

Q&A Sensitivity: Internal