Master Data Analytics & BI for Data-Driven Decision Making

Excel Functions

• Basic Excel Function
• Mathematical Functions
• Logical Functions
• Text Functions
• Statistical Functional
• Relation Based Functions
• Financial Functions
• Date-Time Functions

Advance Paste Special Techniques

• Paste Formulas, Paste Formats
• Transpose Tables
• Paste Validations

Pivot Tables & Pivot Chart – Advance

• Creating Simple & Classic Pivot table
• Basic and Advanced Value Field Setting
• Calculated Field & Calculated Items
• Grouping based on numbers and Dates

Advanced Level Lookup Formulas

• Vlookup with Match, Hlookup with Match.
• Lookup from the Left,Mid & Right Side of Data. (Lookup, Index and Match)
• Vlookup & Hlookup with Multiple Text Functions.
• Creating Smooth user Interface Using lookup

Magic With Array Formulas:

• What are the Array Formulas, Use of the Array Formulas?
• Advanced Use of formulas with Array &Lookup functions.

Advanced Conditional Formatting and Filtering

• Apply Auto-Formatting & Conditional Formatting with Custom Data Formats and Validation
• Create custom conditional formatting rules
• Create conditional formatting rules that use formulas
• Manage conditional formatting rules

Define Named Ranges and Objects

• Manage Name cells & Name data ranges
• Name tables and Manage named ranges and objects.

Referencing Formulas

• Absolute Referencing/ Relative Cell Referencing
• Consolidating Data - With or Without Links
• Trace the Precedents and Dependents

Adding Charts,Graphics to Worksheet

• Work with Chart Styles,Layouts, Add Labels,Axis Options,Chart Title, Legends, Data-Labels
• Insert & Modifying Pictures, Group and Ungroup Data

Collaboration With Internal Or External Worksheets

• Manage Document Properties, Sharing a Workbook, Track Changes, Accept/Reject Changes,
• Mark as Final , Setting Printing Area with Margins, Headers and Footers & Scaling.
• AutoRecover Save Options, Save As Web Page & Protecting Workbooks

Formatting, Data linking and Proofing

• Object Linking and Embedding & Hyper linking data, within sheet / workbook
• Linking & Updating links between workbooks & application
• Creating / Working with an Automatic / Manual Outline

Importing & Exporting Data

• Importing & Exporting Data from Database / Text Files / Web
• Changing External Data Range

Protecting Excel- Excel Security

• File Level Protection
• Workbook, Worksheet Protection

Preparation of the Dashboards

• Different kinds of advanced-level dashboards
• Use formulas like OFFSET, MATCH, SUMIF, SUMIFS & many more to prepare the dashboards
• Use of Data Validation in charting
• Use of Sparkline in your sheet, interactive sparklines
• Working with 2-axis and 3-axis charts

Excel Dashboard - Planning & Processing

• Planning a Dashboard
• Adding Dynamic Contents to Dashboard
• Adding Tables and Charts to Dashboard

Slicers and Charts

• Using SLICERS, Filter data with Slicers
• Various Charts i.e. Bar Charts / Pie Charts / Line Charts
• Manage Primary and Secondary Axis

Complex Charting

• Being able to choose the appropriate type of chart for every occasion
• Being able to integrate many charts into one
• Utilize tools like conditional formatting charts and in-cell charts.
• Being able to create dynamic and interactive charts

Data Reporting with Pivot Tables

• Evaluate vast volumes of data with the help of Pivot tables and Data Reporting
• Relationships, multiple-table pivots, grouping, slicers, measures (Power Pivot)

Worksheet / Workbook Operations Using Macro

• Merge & Split Worksheets using Macro & VBA filters
• Worksheet copiers
• Merge multiple excel files into one-sheet

Pivot Table and Handling Of Errors In Excel

• Split Pivot from the Multiple Source of Data using Multiple Field Setting in Pivot Table.
• Split Pivot from the Multiple Data Ranges & Name Range.
• Creating Groups, Insert additional Calculated Field in Pivot Table.

Start-up with Pivot Charts With Slicer

• Insert slicer in Pivot Table and Pivot charts.
• Discussion of Errors and handling of multiple Errors & Use of Tool “Error Checking”.
• Removing Duplicates.
• Trace Precedents, Trace Dependents.

Data Analysis & Data Reporting

• Managing Extracts ManagingMetadata Visua lAn.a lytics
• Data Granularity using Marks Card
• Highlighting
• Sorting. Filtering. Grouping

Visual Analytics

• Graphical Visualization
• Forecasting Clustering Trend Lines.
• Reference Lines. Parameters

Dashboard & Stories

• Introduct on to Dashboard. Creatinga Dashboard Layout. DesigningDashboard for Devices.
• Dashboard Interaction - Using Action.
• Introduct on to Story Point.

Mapping & Publishing Report

• Introduct on to Maps.
• Editing Unrecognized Locations. Custom Geocoding.
• Polygon Maps.
• Web Mapping Services . Background Images
• Sharing & Publishing Reports to BI Teams or Clients.

Introduction to MySQL

• What is a database and why do we need SQL?
• Create a link to Excel spreadsheet with MySQL
• Client/Server Concepts
• Database models & ER Model

Database and Database Objects

• Import/Export
• Tools for Import/Export Data
• Restore database in SQL
• A Data Generating Procedure

Data Definition using SQL

• Databases
• Data Types
• Tables
• Primary Keys
• Null values and Not Null Values
• Auto Increment
• Basics Queries
• Removing Duplicates
• Data Filters Using Operators
• Data Sorting
• Constraints and Indexes
• Views

Basic Data Manipulation using SQL

• Recurring SQL Constructs
• Adding data / Modifying data / Removing data / Searching data

Advanced Data Manipulation using SQL

• Expressions for Grouping and Aggregate Functions
• Joining Tables

Create Query

• Use the WHERE clause
• Conditional statements
• Multiple conditions with Comparison Operators
• Logic Values
• Null Values
• Compare Column Values
• Advanced Queries
• Distinct Values

Joins

• Inner Join
• Left Join
• Full Outer Join
• Filtering Grouped Data
• Summarize Group Data
• Pivot and Unpivot Operators

User Defined Functions

• Functions vs Procedures
• User-Defined Functions

MySQL Admin

• Install and configure MySQL Server and client programs
• Recognize the key components of the MySQL architecture
• Secure your server
• Improve query performance
• Understand the benefits of MySQL Cloud

Installing & Configuring MySQL

• Install and configure MySQL Server and client programs
• Recognize the key components of the MySQL architecture
• Secure your server
• Improve query performance
• Understand the benefits of MySQL Cloud

Working with Different Data Sources in Power BI

• Importing from relational data sources and from databases
• Importing from multi-dimensional data sources
• Importing flat files from Excel and SQL Server
• Importing Reports and data feeds from other data sources

Transforming & Enhancing the Data Model in Power Query Editor

• Understanding Power Query Editor
• Understanding Power Query elements & Viewing published models
• Performing Row & Column operations
• Understanding table & Create relationships between tables
• Understanding data refresh Mode & Working with relationships Module

Analyzing Data & Implementing Dashboards

• Understanding Power BI data analysis
• Understanding Pivot Table, Power Pivot Field List & Pivot Chart in Power BI

Creating Reports & M Language Functions

• Data Operation and Manage Functions Using Parameters with M Language
• Understanding Slicers & Check Data quality
• Handling metadata issues and Handling Errors
• Working with dates, hierarchies, and perspectives

Data Analysis Expressions (DAX)

• Understanding Data Analysis Expressions
• Understanding row and filter context for Data Modeling
• Creating and Applying DAX Expressions

Implementing Measures, Calculated Columns, and Tables

• Understanding measures & Understanding measure functions
• Implementing measures and KPIs

Working with Report View User Interface

• Fields Pane, Visualizations pane, Ribbon, Views, Pages Tab, Canvas Visual
• Interactions - Interaction Type (Filter, Highlight, None)
• Visual Interactions Default Behavior, Changing the Interaction

Basic & Advanced Visualizations

• Visualizing Data, Visualization types
• Create and Format Charts: Bar, Pie, Donut Charts, Scatter Charts
• Create and Format Table Visual, Matrix Visualization
• Analyzing Data with Power View and Power Map

Power BI Service

• Power BI Service Introduction, SIGN IN to Power BI Service Account
• Publishing Reports & Dataset to the Power BI service
• Import / Getting the Report to PBI Service & My Workspace

Interacting Dashboards Models

• Pin Visuals and Pin LIVE Report Pages to Dashboard
• Advantages of Dashboards, Interacting & Formatting with Dashboards
• Formatting Dashboard
• Sharing Dashboard with SharePoint Publishing

Working with Data Analysis Expression

• data structures, Control & Conditional Statements
• DAX concepts using objects and methods
• Exception handling, Implementation of Pre-defined functions
• DAX Expressions with date-time concepts

Data Filter & Data Slicing

• String, Lists, Tuple, Dictionaries
• Str Data Type | Indexing | Slicing | Built-in functions
• Append - Extend - Insert - | Update and Delete Operations
• Storing and Accessing the data in Operations

Introduction to Data Analysis in POWER BI - Understanding and Preprocessing

• Knowledge domains of Data Analysis
• Understanding structured and unstructured data
• Data Analysis process, Dataset generation, Importing Dataset: Importing and Exporting Data
• Basic Insights from Dataset
• Cleaning and Preparing the Data: Identify and Handle Missing Values

Data Cleaning

• Types of Missing Values
• Imputing missing values with the business logic in a real-time scenario
• Finding out & Handling outliers from the data
• Introduction to Replace and Drop level Function
• Data Cleaning on Big Dataset & Databases

Data Processing and Visualization

• Data Formatting & Conditional Formatting
• Exploratory Data Analysis
• Filtering and hierarchical indexing
• Data Visualization: Basic Visualization Tools, Specialized Visualization Tools

Creating and Plotting Visualization

• Understanding and creating N-dimensional arrays
• Basic indexing and slicing, Boolean indexing, Fancy indexing
• Universal functions & File input and output with arrays
• Data-Processing using arrays

Bookmarking

• Applications of Probability in Real Time Databases
• Use of Static & Normal bookmarking
• Understanding of Referential Statistics Data

Publishing and sharing Data on Web Power BI App

• Data Web scraping, Combining and merging data sets, Reshaping and pivoting
• Publishing data online or Offline

Dashboard Model Development and Evaluation

• Introduction to machine learning - Supervised and Unsupervised Learning
• Model development using Linear Regression, Report Visualization
• Prediction and Decision Making Dynamic Dashboard
• Model Evaluation: Over-fitting, Under-fitting and Model Selection
• Pin Dashboard & Generate Final Data view with Dataset