Business Intelligence Master’s Program

109,774

This is one of the top Business Intelligence aces programs that will allow you to acquire capability in Business Intelligence. You will chip away at certifiable tasks in Informatica, Tableau, MSBI, Power BI, MS SQL, Data Warehousing and Erwin, Azure Data Factory, SQL DBA, and the sky is the limit from there. In this program, you will cover 9 courses and 32 industry-based undertakings.

Category:

List of Courses Included

Online Instructor-led Courses:

  • MS SQL
  • MSBI
  • Tableau Desktop 10
  • Power BI
  • Informatica Developer & Admin
  • Azure Data Factory

Self-paced Courses:

  • Data Warehousing & Data Modeling
  • Qlik Sense
  • SQL DBA

What will you learn in this Business Intelligence masters online course?

  • Introduction to Business Intelligence
  • Extract, transform, and load steps
  • Working with data discovery tools
  • Creating charts, reports, and graphs
  • Extracting data from multiple data sources
  • Data modeling and analysis
  • Data Warehousing

Who should take up this Business Intelligence masters training?

  • Data Science and Big Data Professionals
  • Software Developers
  • Business Intelligence Professionals
  • Information Architects and Project Managers
  • Those who are aspiring to be a Business Intelligence Architect

What are the prerequisites for taking up this course?

There are no prerequisites for taking up this training program.

Why should you take up this training program?

  • Worldwide Business Intelligence and Analytics market will grow to US$22.8 billion in the next 2 years – Gartner
  • The average salary of an MSBI Professional in the USA is US$107,000 – Indeed

Today, there is an urgent need for Business Intelligence professionals who are well-versed with the front-end and back-end of BI. Due to this, Edutech Skills masters course in BI architecture is created to help you gain complete proficiency in the ETL steps and in the BI reporting techniques. Taking up this training will put you in a different league and will help you grab top jobs.

MS SQL (Live Course)

Module 1 – Introduction to SQL

1.1 Various types of databases
1.2 Introduction to Structured Query Language
1.3 Distinction between client server and file server databases
1.4 Understanding SQL Server Management Studio
1.5 SQL Table basics
1.6 Data types and functions
1.7 Transaction-SQL
1.8 Authentication for Windows
1.9 Data control language
1.10 The identification of the keywords in T-SQL, such as Drop Table

Module 2 – Database Normalization and Entity Relationship Model

2.1 Entity-Relationship Model
2.2 Entity and Entity Set
2.3 Attributes and types of Attributes
2.4 Entity Sets
2.5 Relationship Sets
2.6 Degree of Relationship
2.7 Mapping Cardinalities, One-to-One, One-to-Many, Many-to-one, Many-to-many
2.8 Symbols used in E-R Notation

Module 3 – SQL Operators

3.1 Introduction to relational databases
3.2 Fundamental concepts of relational rows, tables, and columns
3.3 Several operators (such as logical and relational), constraints, domains, indexes, stored procedures, primary and foreign keys
3.4 Understanding group functions
3.5 The unique key

Module 4 – Working with SQL: Join, Tables, and Variables

4.1 Advanced concepts of SQL tables
4.2 SQL functions
4.3 Operators & queries
4.4 Table creation
4.5 Data retrieval from tables
4.6 Combining rows from tables using inner, outer, cross, and self joins
4.7 Deploying operators such as ‘intersect,’ ‘except,’ ‘union,’
4.8 Temporary table creation
4.9 Set operator rules
4.10 Table variables

Module 5 – Deep Dive into SQL Functions

5.1 Understanding SQL functions – what do they do?
5.2 Scalar functions
5.3 Aggregate functions
5.4 Functions that can be used on different datasets, such as numbers, characters, strings, and dates
5.5 Inline SQL functions
5.6 General functions
5.7 Duplicate functions

Module 6 – Working with Subqueries

6.1 Understanding SQL subqueries, their rules
6.2 Statements and operators with which subqueries can be used
6.3 Using the set clause to modify subqueries
6.4 Understanding different types of subqueries, such as where, select, insert, update, delete, etc.
6.5 Methods to create and view subqueries

Module 7 – SQL Views, Functions, and Stored Procedures

7.1 Learning SQL views
7.2 Methods of creating, using, altering, renaming, dropping, and modifying views
7.3 Understanding stored procedures and their key benefits
7.4 Working with stored procedures
7.5 Studying user-defined functions
7.6 Error handling

Module 8 – Deep Dive into User-defined Functions

8.1 User-defined functions
8.2 Types of UDFs, such as scalar
8.3 Inline table value
8.4 Multi-statement table
8.5 Stored procedures and when to deploy them
8.6 What is rank function?
8.7 Triggers, and when to execute triggers?

Module 9 – SQL Optimization and Performance

9.1 SQL Server Management Studio
9.2 Using pivot in MS Excel and MS SQL Server
9.3 Differentiating between Char, Varchar, and NVarchar
9.4 XL path, indexes and their creation
9.5 Records grouping, advantages, searching, sorting, modifying data
9.6 Clustered indexes creation
9.7 Use of indexes to cover queries
9.8 Common table expressions
9.9 Index guidelines

Module 10 – Advanced Topics

10.1 Correlated Subquery, Grouping Sets, Rollup, Cube

Hands-on Exercise

  1. Implementing Correlated Subqueries
  2. Using EXISTS with a Correlated subquery
  3. Using Union Query
  4. Using Grouping Set Query
  5. Using Rollup
  6. Using CUBE to generate four grouping sets
  7. Perform a partial CUBE

Module 11 – Managing Database Concurrency

11.1 Applying transactions
11.2 Using the transaction behavior to identify DML statements
11.3 Learning about implicit and explicit transactions
11.4 Isolation levels management
11.5 Understanding concurrency and locking behavior
11.6 Using memory-optimized tables

Module 12 – Programming Databases Using Transact-SQL

12.1 Creating Transact-SQL queries
12.2 Querying multiple tables using joins
12.3 Implementing functions and aggregating data
12.4 Modifying data
12.5 Determining the results of DDL statements on supplied tables and data
12.6 Constructing DML statements using the output statement
12.7 Querying data using subqueries and APPLY
12.8 Querying data using table expressions
12.9 Grouping and pivoting data using queries
12.10 Querying temporal data and non-relational data
12.11 Constructing recursive table expressions to meet business requirements
12.12 Using windowing functions to group
12.13 Rank the results of a query
12.14 Creating database programmability objects by using T-SQL
12.15 Implementing error handling and transactions
12.16 Implementing transaction control in conjunction with error handling in stored procedures
12.17 Implementing data types and NULL
12.18 Designing and implementing relational database schema
12.19 Designing and implementing indexes
12.20 Learning to compare between indexed and included columns
12.21 Implementing clustered index
12.22 Designing and deploying views
12.23 Column store views

12.24 Explaining foreign key constraints
12.25 Using T-SQL statements
12.26 Usage of Data Manipulation Language (DML)
12.27 Designing the components of stored procedures
12.28 Implementing input and output parameters
12.29 Applying error handling
12.30 Executing control logic in stored procedures
12.31 Designing trigger logic, DDL triggers, etc.
12.32 Accuracy of statistics
12.33 Formulating statistics maintenance tasks
12.34 Dynamic management objects management
12.35 Identifying missing indexes
12.36 Examining and troubleshooting query plans
12.37 Consolidating the overlapping indexes
12.38 The performance management of database instances
12.39 SQL server performance monitoring

Module 13 – Microsoft Courses: Study Material

13.1 Performance Tuning and Optimizing SQL Databases
13.2Querying Data with Transact-SQL

SQL Projects

Writing Complex Subqueries

In this project, you will be working with SQL subqueries and utilizing them in various scenarios. You will learn to use IN or NOT IN, ANY or ALL, EXISTS or NOT EXISTS, and other majorRead More. queries. You will be required to access and manipulate datasets, operate and control statements, execute queries in SQL against databases.

Querying a Large Relational Database

This project is about how to get details about customers by querying the database. You will be working with Table basics and data types, various SQL operators, and SQL functions. The project will require youRead More. to download a database and restore it on the server, query the database for customer details and sales information.

Relational Database Design

In this project, you will learn to convert a relational design that has enlisted within its various users, user roles, user accounts, and their statuses into a table in SQL Server. You will haveRead More.. to define relations/attributes, primary keys, and create respective foreign keys with at least two rows in each of the tables.

Tableau Desktop 10 (Live Course)

Module 1 – Introduction to Data Visualization and Power of Tableau

1.1 What is data visualization?
1.2 Comparison and benefits against reading raw numbers
1.3 Real use cases from various business domains
1.4 Some quick and powerful examples using Tableau without going into the technical details of Tableau
1.5 Installing Tableau
1.6 Tableau interface
1.7 Connecting to DataSource
1.8 Tableau data types
1.9 Data preparation

Module 2 – Architecture of Tableau

2.1 Installation of Tableau Desktop
2.2 Architecture of Tableau
2.3 Interface of Tableau (Layout, Toolbars, Data Pane, Analytics Pane, etc.)
2.4 How to start with Tableau
2.5 The ways to share and export the work done in Tableau

Hands-on Exercise:

1. Play with Tableau desktop
2. Learn about the interface
3. Share and export existing works

Module 3 – Working with Metadata and Data Blending

3.1 Connection to Excel
3.2 Cubes and PDFs
3.3 Management of metadata and extracts
3.4 Data preparation
3.5 Joins (Left, Right, Inner, and Outer) and Union
3.6 Dealing with NULL values, cross-database joining, data extraction, data blending, refresh extraction, incremental extraction, how to build extract, etc.

Hands-on Exercise:

1. Connect to Excel sheet to import data
2. Use metadata and extracts
3. Manage NULL values
4. Clean up data before using
5. Perform the join techniques
6. Execute data blending from multiple sources

Module 4 – Creation of Sets

4.1 Mark, highlight, sort, group, and use sets (creating and editing sets, IN/OUT, sets in hierarchies)
4.2 Constant sets
4.3 Computed sets, bins, etc.

Hands-on Exercise:

1. Use marks to create and edit sets
2. Highlight the desired items
3. Make groups
4. Apply sorting on results
5. Make hierarchies among the created sets

Module 5 – Working with Filters

5.1 Filters (addition and removal)
5.2 Filtering continuous dates, dimensions, and measures
5.3 Interactive filters, marks card, and hierarchies
5.4 How to create folders in Tableau
5.5 Sorting in Tableau
5.6 Types of sorting
5.7 Filtering in Tableau
5.8 Types of filters
5.9 Filtering the order of operations

Hands-on Exercise:

1. Use the data set by date/dimensions/measures to add a filter
2. Use interactive filter to view the data
3. Customize/remove filters to view the result

Module 6 – Organizing Data and Visual Analytics

6.1 Using Formatting Pane to work with menu, fonts, alignments, settings, and copy-paste
6.2 Formatting data using labels and tooltips
6.3 Edit axes and annotations
6.4 K-means cluster analysis
6.5 Trend and reference lines
6.6 Visual analytics in Tableau
6.7 Forecasting, confidence interval, reference lines, and bands

Hands-on Exercise:

1. Apply labels and tooltips to graphs, annotations, edit axes’ attributes
2. Set the reference line
3. Perform k-means cluster analysis on the given dataset

Module 7 – Working with Mapping

7.1 Working on coordinate points
7.2 Plotting longitude and latitude
7.3 Editing unrecognized locations
7.4 Customizing geocoding, polygon maps, WMS: web mapping services
7.5 Working on the background image, including add image
7.6 Plotting points on images and generating coordinates from them
7.7 Map visualization, custom territories, map box, WMS map
7.8 How to create map projects in Tableau
7.9 Creating dual axes maps, and editing locations

Hands-on Exercise:

1. Plot longitude and latitude on a geo map
2. Edit locations on the geo map
3. Custom geocoding
4. Use images of the map and plot points
5. Find coordinates
6. Create a polygon map
7. Use WMS

Module 8 – Working with Calculations and Expressions

8.1 Calculation syntax and functions in Tableau
8.2 Various types of calculations, including Table, String, Date, Aggregate, Logic, and Number
8.3 LOD expressions, including concept and syntax
8.4 Aggregation and replication with LOD expressions
8.5 Nested LOD expressions
8.6 Levels of details: fixed level, lower level, and higher level
8.7 Quick table calculations
8.8 The creation of calculated fields
8.9 Predefined calculations
8.10 How to validate

Module 9 – Working with Parameters

9.1 Creating parameters
9.2 Parameters in calculations
9.3 Using parameters with filters
9.4 Column selection parameters
9.5 Chart selection parameters
9.6 How to use parameters in the filter session
9.7 How to use parameters in calculated fields
9.8 How to use parameters in the reference line

Hands-on Exercise:

1. Creating new parameters to apply on a filter
2. Passing parameters to filters to select columns
3. Passing parameters to filters to select charts

Module 10 – Charts and Graphs

10.1 Dual axes graphs
10.2 Histograms
10.3 Single and dual axes
10.4 Box plot
10.5 Charts: motion, Pareto, funnel, pie, bar, line, bubble, bullet, scatter, and waterfall charts
10.6 Maps: tree and heat maps
10.7 Market basket analysis (MBA)
10.8 Using Show me
10.9 Text table and highlighted table

Hands-on Exercise:

1. Plot a histogram, tree map, heat map, funnel chart, and more using the given dataset
2. Perform market basket analysis (MBA) on the same dataset

Module 11 – Dashboards and Stories

11.1 Building and formatting a dashboard using size, objects, views, filters, and legends
11.2 Best practices for making creative as well as interactive dashboards using the actions
11.3 Creating stories, including the intro of story points
11.4 Creating as well as updating the story points
11.5 Adding catchy visuals in stories
11.6 Adding annotations with descriptions; dashboards and stories
11.7 What is dashboard?
11.8 Highlight actions, URL actions, and filter actions
11.9 Selecting and clearing values
11.10 Best practices to create dashboards
11.11 Dashboard examples; using Tableau workspace and Tableau interface
11.12 Learning about Tableau joins
11.13 Types of joins
11.14 Tableau field types
11.15 Saving as well as publishing data source
11.16 Live vs extract connection
11.17 Various file types

Hands-on Exercise:

1. Create a Tableau dashboard view, include legends, objects, and filters
2. Make the dashboard interactive
3. Use visual effects, annotations, and descriptions to create and edit a story

Module 12 – Tableau Prep

12.1 Introduction to Tableau Prep
12.2 How Tableau Prep helps quickly combine join, shape, and clean data for analysis
12.3 Creation of smart examples with Tableau Prep
12.4 Getting deeper insights into the data with great visual experience
12.5 Making data preparation simpler and accessible
12.6 Integrating Tableau Prep with Tableau analytical workflow
12.7 Understanding the seamless process from data preparation to analysis with Tableau Prep

Module 13 – Integration of Tableau with R

13.1 Introduction to R language
13.2 Applications and use cases of R
13.3 Deploying R on the Tableau platform
13.4 Learning R functions in Tableau

Hands-on Exercise:

1. Deploy R on Tableau
2. Create a line graph using R interface

Tableau Projects Covered

Understanding the global covid-19 mortality rates

Analyze and develop a dashboard to understand the covid-19 global cases. Compare the global confirmed vs. death cases in a world map. Compare the country wise cases using logarithmic axes. Dashboard should display both a log axis chart and a default axis chart in an alternate interactive way. Create a parameter to dynamically view Top N WHO regions based on cumulative new cases and death cases ratio. Dashboard should have a drop down menu to view the WHO region wise data using a bar chart, line chart or a map as per user’s requirement.

Understand the UK bank customer data

Analyze and develop a dashboard to understand the customer data of a UK bank. Create an asymmetric drop down of Region with their respective customer names and their Balances with a gender wise color code. Region wise bar chart which displays the count of customers based on High and low balance. Create a parameter to let the users’ dynamically decide the limit value of balance which categorizes it into high and low. Include interactive filters for Job classifications and Highlighters for Region in the final dashboard.

Understand Financial Data

Create an interactive map to analyze the worldwide sales and profit. Include map layers and map styles to enhance the visualization. Interactive analysis to display the average gross sales of a product under each segment, allowing only one segment data to be displayed at once. Create a motion chart to compare the sales and profit through the years. Annotate the day wise profit line chart to indicate the peaks and also enable drop lines. Add go to URL actions in the final dashboard which directs the user to the respective countries Wikipedia page.

Understand Agriculture Data

Create interactive tree map to display district wise data. Tree maps should have state labels. On hovering on a particular state, the corresponding districts data are to be displayed. Add URL actions, which direct users’ to a Google search page of the selected crop. Web page is to be displayed on the final dashboard. Create a hierarchy of seasons, crop categories and the list of crops under each. Add highlighters for season. One major sheet in the final dashboard should be unaffected by any action applied. Use the view in this major sheet to filter data in the other. Using parameters color code the seasons with high yield and low yield based on its crop categories. Rank the crops based on their yield

Power BI (Live Course)

Module 1 – Introduction to Power BI

1.1 Introduction to Microsoft Power BI
1.2 The key features of Power BI workflow
1.3 Desktop application
1.4 Power BI service
1.5 File data sources
1.6 Sourcing data from web (OData, Azure)
1.7 Building dashboard
1.8 Data visualization
1.9 Publishing to cloud
1.10 DAX data computation
1.11 Row context
1.12 Filter context
1.13 Analytics Pane
1.14 Creating columns and measures
1.15 Data drill down and drill up
1.16 Creating tables
1.17 Binned tables
1.18 Data modeling and relationships
1.19 The Power BI components like Power View, Map, Query, Pivot
1.20 Power Q & A
1.21 Understanding advanced visualization

Hands-on Exercise –

  1. Demo of building a Power BI dashboard
  2. Source data from web
  3. Publish to cloud
  4. Create power tables

Module 2 – Extracting Data

2.1 Learning about Power Query for self-service ETL functionalities
2.2 Introduction to data mashup
2.3 Working with Excel data
2.4 Learning about Power BI Personal Gateway
2.5 Extracting data from files, folders and databases
2.6 Working with Azure SQL database and database source
2.7 Connecting to Analysis Services
2.8 SaaS functionalities of Power BI

Hands-on Exercise –

  1. Connect to a database
  2. Import data from an excel file
  3. Connect to SQL Server
  4. Analysis Service
  5. Connect to Power Query
  6. Connect to SQL Azure
  7. Connect to Hadoop

Module 3 – Power Query for Data Transformation

3.1 Installing Power BI
3.2 The various requirements and configuration settings
3.3 The Power Query
3.4 Introduction to Query Editor
3.5 Data transformation – column, row, text, data type, adding & filling columns and number column, column formatting, transpose table, appending, splitting, formatting data, Pivot and UnPivot, Merge Join, relational operators, date, time calculations, working with M functions, lists, records, tables, data types, and generators
3.6 Filters & Slicers
3.7 Index and Conditional Columns
3.8 Summary Tables
3.9 Writing custom functions and error handling
3.10 M advanced data transformations

Hands-on Exercise –

  1. Install Power BI Desktop and configure the settings
  2. Use Query editor
  3. Write a power query
  4. Transpose a table

Module 4 – Power Pivot for Data Modeling and Data Analysis Expression – DAX Queries

4.1 Introduction to Power Pivot
4.2 Learning about the xVelocity engine
4.3 Advantages of Power Pivot
4.4 Various versions and relationships
4.5 Strongly typed datasets
4.6 Data Analysis Expressions
4.7 Measures, Calculated Members, Row, Filter & Evaluation Context, Context Interactions, Context over Relations, Schema Relations
4.8 Learning about Table, Information, Logical, Text, Iterator, Table, and Time Intelligence Functions
4.9 Cumulative Charts, Calculated Tables, ranking and rank over groups
4.10 Power Pivot advanced functionalities
4.11 Date and time functions
4.12 DAX advanced features
4.13 Embedding Power Pivot in Power BI Desktop

Hands-on Exercise –

  1. Create a Power Pivot Apply filters
  2. Use advanced functionalities like date and time functions
  3. Embed Power Pivot in Power BI Desktop
  4. Create DAX queries for calculate column, tables and measures

Module 5 – Data Visualization with Analytics

5.1 Deep dive into Power BI data visualization
5.2 Understanding Power View and Power Map
5.3 Power BI Desktop visualization
5.4 Formatting and customizing visuals
5.5 Visualization interaction
5.6 SandDance visualization
5.7 Deploying Power View on SharePoint and Excel
5.8 Top down and bottom up analytics
5.9 Comparing volume and value-based analytics
5.10 Working with Power View to create Reports, Charts, Scorecards, and other visually rich formats
5.11 Categorizing, filtering and sorting data using Power View
5.12 Hierarchies
5.13 Mastering the best practices
5.14 Custom Visualization
5.15 Authenticate a Power BI web application
5.16 Embedding dashboards in applications

Hands-on Exercise –

  1. Create a Power View and a Power Map
  2. Format and customize visuals
  3. Deploy Power View on SharePoint and Excel
  4. Implement top-down and bottom-up analytics
  5. Create Power View reports, Charts, Scorecards
  6. Add a custom visual to report
  7. Authenticate a Power BI web application
  8. Embed dashboards in applications
  9. Categorize, filter and sort data using Power View
  10. Create hierarchies
  11. Use date hierarchies
  12. Use business hierarchies
  13. Resolve hierarchy issues

Module 6 – Power Q & A

6.1 Introduction to Power Q & A
6.2 Intuitive tool to answer tough queries using natural language
6.3 Getting answers in the form of charts, graphs and data discovery methodologies
6.4 Ad hoc analytics building
6.5 Power Q & A best practices
6.6 Integrating with SaaS applications

Hands-on Exercise –

  1. Write queries using natural language
  2. Get answers in the form of charts, graphs
  3. Build ad hoc analytics
  4. Pin a tile and a range to dashboard

Module 7 – Power BI Desktop & Administration

7.1 Getting to understand the Power BI Desktop
7.2 Aggregating data from multiple data sources
7.3 How Power Query works in Power BI Desktop environment
7.4 Learning about data modeling and data relationships
7.5 Deploying data gateways
7.6 Scheduling data refresh
7.7 Managing groups and row level security, datasets, reports and dashboards
7.8 Working with calculated measures
7.9 Power Pivot on Power BI Desktop ecosystem
7.10 Mastering data visualization
7.11 Power View on Power BI Desktop
7.12 Creating real world solutions using Power BI

Hands-on Exercise –

  1. Configure security for dashboard Deploy data gateways
  2. Aggregate data from multiple data sources
  3. Schedule data refresh
  4. Manage groups and row level security, datasets, reports and dashboards
  5. Work with calculated measures

Module 8 – Microsoft Course

8.1 Analyzing Data with Power BI

Power BI Projects Covered

Report on Student Survey

There are many stores in which a survey was conducted based on students i.e. How much they are spending on different kinds of purchases like Video games, Indoor games, Toys, Books, Gadgets, etc. You have to create a Power BI Report. You will get hands-on experience on Tabular Visualization, Matrix Visualization, Funnel chart, Pie chart, Scatter plot, Sand dance plot

Case Study 1 – Power BI Desktop, Cloud Service, and End to End Workflow

The case study deals with ways to design a dashboard with a basic set of visualizations and deploy it to Power BI Cloud service. Further, a top-level brief overview of Transport Corp Data is shown using aggregated Key Performance Indicators (KPIs), Trends, Gio Distributions, and Filters.

Case Study 2 – Visualizations, Configuring Extended Properties, and Data Calculations DAX – Introduction

This case study explains the way to design a dashboard and perform calculations by making use of Power BI DAX formulas. The scheduled deliveries of loads are analyzed using correlation across measures. Moreover, Drill Up/Drill Down’s capabilities and reference lines are implemented.

Case Study 3 – Combination Visualizations for Correlated Value Columns

Here, the Dashboard is designed by making use of Power BI DAX formulas to perform calculations. Bucketed Categories are created to represent value measures on the categories axis. Furthermore, a scatter plot is used to identify outliers or outperformers.

Case Study 4 – Data Transformations

The case study involves designing an audit dashboard by making use of Power Query, Query Editor to perform data modeling by applying Data transformations, in turn, by managing relationships.

Case Study 5 – Data Transformations – Contd.

Here, the Dashboard is designed to analyze the trend of admissions into a State University. Query Editor is used to perform data modeling by applying transformations like append data, split data, column formatting, transpose table, pivot/unpivot, fill columns, merge join, conditional columns, index columns, and summary tables.

Informatica Developer & Admin (Live Course)

Data Warehousing and Cleansing Concepts

What is data warehousing, understanding the extract, transform and load processes, what is data aggregation, data scrubbing and data cleansing and the importance of Informatica PowerCenter ETL

Informatica Installation and Configuration

Configuring the Informatica tool and how to install the Informatica operational administration activities and integration services

Hands-on Exercise: Step-by-step process for the installation of Informatica PowerCenter

Working with Active and Passive Transformation

Understanding the difference between active and passive transformations and the highlights of each transformation

Working with Expression Transformation

Learning about expression transformation and connected passive transformation to calculate value on a single row

Hands-on Exercise: Calculate value on a single row using connected passive transformation

Working with Sorter, Sequence Generator and Filter Transformation

Different types of transformations like sorter, sequence generator and filter, the characteristics of each and where they are used

Hands-on Exercise: Transform data using the filter technique, use a sequence generator and use a sorter

Working with Joiner Transformation

Working with joiner transformation to bring data from heterogeneous data sources

Hands-on Exercise: Use joiner transformation to bring data from heterogeneous data sources

Working with Ranking and Union Transformation

Understanding the ranking and union transformation, the characteristics and deployment

Hands-on Exercise: Perform ranking and union transformation

Syntax for Rank and Dense Rank

Learn the rank and dense rank functions and the syntax for them

Hands-on Exercise: Perform rank and dense rank functions

Router Transformation

Understanding how router transformation works and its key features

Hands-on Exercise: Perform router transformation

Source Qualifier Transformation and Mappings

Lookup transformation overview and different types of lookup transformations: connected, unconnected, dynamic and static

Hands-on Exercise: Perform lookup transformations: connected, unconnected, dynamic and static

Slowly Changing Dimension in Informatica

What is SCD, processing in xml, learn how to handle a flat file, list and define various transformations, implement ‘for loop’ in PowerCenter, the concepts of pushdown optimization and partitioning, what is constraint-based loading and what is incremental aggregation

Hands-on Exercise: Load data from a flat file, implement ‘for loop’ in PowerCenter, use pushdown optimization and partitioning, do constraint-based data loading and use incremental aggregation technique to aggregate data

Mapplet and Loading to Multiple Designer

Different types of designers: Mapplet and Worklet, target load plan, loading to multiple targets and linking property

Hands-on Exercise: Create a mapplet and a worklet, plan a target load and load multiple targets

Performance Tuning in Informatica

Objectives of performance tuning, defining performance tuning and learning the sequence for tuning

Hands-on Exercise: Do performance tuning by following different techniques

Repository Manager

Managing repository, Repository Manager: the client tool, functionalities of previous versions and important tasks in Repository Manager

Hands-on Exercise: Manage tasks in Repository Manager

Best Practices in Informatica

Understanding and adopting best practices for managing repository

Workflow Informatica

Common tasks in workflow manager, creating dependencies and the scope of workflow monitor

Hands-on Exercise: Create workflow with dependencies of nodes

Parameters and Variables

Define the variable and parameter in Informatica, parameter files and their scope, the parameter of mapping, worklet and session parameters, workflow and service variables and basic development errors

Hands-on Exercise: Define variables and parameters in functions, use the parameter of mapping, use worklet and session parameters and use workflow and service variables

Error Handling and Recovery in Informatica

Session and workflow log, using debuggers, error-handling framework in Informatica and failover and high availability in Informatica

Hands-on Exercise: Debug development errors, read workflow logs and use the error-handling framework

High Availability and Failover in Informatica

Configurations and mechanisms in recovery and checking health of PowerCenter environment

Hands-on Exercise: Configure recovery options and check health of PowerCenter environment

Working with Different Utilities in Informatica

Using commands: infacmd, pmrep and infasetup and processing of a flat file

Hands-on Exercise: Use commands: infacmd, pmrep and infasetup

Flat File Processing (Advanced Transformations)

Fixed length and delimited, expression transformations: sequence numbers and dynamic targeting using transaction control

Hands-on Exercise: Perform expression transformations: sequence numbers and dynamic targeting using transaction control

Dynamic Targeting

Dynamic target with the use of transaction control and indirect loading

Hands-on Exercise: Use of transaction control with dynamic target and indirect loading

Working with Java Transformations

Importance of Java transformations to extend PowerCenter capabilities, transforming data and active and passive mode

Hands-on Exercise: Use Java transformations to extend PowerCenter capabilities

Unconnected Stored Procedure Usage

Understanding the unconnected stored procedure in Informatica and different scenarios of unconnected stored procedure usage

Hands-on Exercise: Use the unconnected stored procedure in Informatica in different scenarios

Advanced Concepts in SCD

Using SQL transformation (active and passive)

Hands-on Exercise: Use SQL transformation (active and passive)

Incremental Data Loading and Aggregation

Understanding incremental loading and aggregation and comparison between them

Hands-on Exercise: Do incremental loading and aggregation

Constraint-based Loading

Working with database constraints using PowerCenter and understanding constraint-based loading and target load order

Hands-on Exercise: Perform constraint-based loading in a given order

XML Transformation and Active Lookup

Various types of XML transformation in Informatica and configuring a lookup as active

Hands-on Exercise: Perform XML transformation and configure a lookup as active

Profiling in PowerCenter

Understanding what data profiling in Informatica is, its significance in validating content and ensuring quality and structure of data as per business requirements

Hands-on Exercise: Create data profiling in Informatica and validate the content

Workflow Creation and Deletion

Understanding workflow as a group of instructions/commands for integration services and learning how to create and delete workflow in Informatica

Hands-on Exercise: Create and delete workflow in Informatica

Database Connection

Understanding the database connection, creating a new database connection in Informatica and understanding various steps involved

Hands-on Exercise: Create a new database connection in Informatica

Relational Database Tables

Working with relational database tables in Informatica, mapping for loading data from flat files to relational database files

Hands-on Exercise: Create mapping for loading data from flat files to relational database files

LinkedIn Connection

Understanding how to deploy PowerCenter for seamless LinkedIn connectivity with Informatica PowerCenter

Hands-on Exercise: Deploy PowerCenter for seamless LinkedIn connectivity with Informatica PowerCenter

Connection with Sources

Connecting Informatica PowerCenter with various data sources like social media channels such as Facebook, Twitter, etc.

Hands-on Exercise: Connect Informatica PowerCenter with various data sources like social media channels such as Facebook, Twitter, etc.

Pushdown Optimization and Partitioning

Pushdown optimization for load-balancing on the server for better performance and various types of partitioning for optimizing performance

Hands-on Exercise: Optimize using pushdown technique for load-balancing on the server for better performance and create various types of partitioning for optimizing performance

Cache Management

Understanding session cache, the importance of cache creation, implementing session cache and calculating cache requirement

Hands-on Exercise: Implement cache creation and work with session cache

Informatica Projects

Admin Console

For this project, you will be expected to carry out tasks like the creation of users, building roles, forming groups, a collaboration of users, roles, and groups, lock handling, creating sessions, workflow, and worklets.

Deploying Informatica ETL for Business Intelligence

For this project, you will have to access data from multiple sources, manage current and historic data with SCD, import source and target tables, etc. Extract the data and fetch it into staging. It will then go from the operational data store to the enterprise data warehouse, and generate reports and insights.

Deploying the ETL Transactions on Healthcare Data

Systematically load data within a hospital scenario for easy access. You will be extracting data from multiple sources, cleansing data and putting in the right format, and loading the data into the CRDW. You will create CRDW load schedules that are on daily, weekly, and monthly basis.

Case Study 1 – Banking Products Augmentation

This case study is about improving the profits of a bank by customizing the products and adding new products based on customer needs. You will construct a multidimensional model. Deploy a star-join schema, create demographic mini-dimensions, and work with Informatica aggregator transformations.

Case Study 2 – Employee Data Integration

In this case study, you will load a table with employee data using Informatica. You will carry out tasks like creating multiple shared tables, work with the plug-and-play capability of the framework, and code and framework reusability.

Azure Data Factory (Live Course)

Module 01 – Non-Relational Data Stores and Azure Data Lake Storage

1.1 Document data stores
1.2 Columnar data stores
1.3 Key/value data stores
1.4 Graph data stores
1.5 Time series data stores
1.6 Object data stores
1.7 External index
1.8 Why NoSQL or Non-Relational DB?
1.9 When to Choose NoSQL or Non-Relational DB?

  • Best Uses
  • Scenarios

1.10 Azure Data Lake Storage

  • Definition
  • Azure Data Lake-Key Components
  • How it stores data?
  • Azure Data Lake Storage Gen2
  • Why Data Lake?
  • Data Lake Architecture

Module 02 – Data Lake and Azure Cosmos DB

2.1 Data Lake Key Concepts
2.2 Azure Cosmos DB
2.3 Why Azure Cosmos DB?
2.4 Azure Blob Storage
2.5 Why Azure Blob Storage?
2.6 Data Partitioning

  • Horizontal partitioning
  • Vertical partitioning
  • Functional partitioning

2.7 Why Partitioning Data?
2.8 Consistency Levels in AzureCosmos DB

  • Semantics of the five-consistency level

Hands-on:
1. Load Data fromAmazonS3 to ADLS Gen2 with Data Factory
2. Working with Azure Cosmos DB

Module 03 – Relational Data Stores

3.1 Introduction to Relational Data Stores
3.2 Azure SQL Database

  • Deployment Models
  • Service Tiers

Hands-on:
1. Create a Single Database Using Azure Portal
2. Create a managed instance
3. Create an elastic pool

3.3 Why SQL Database Elastic Pool?

Hands-on:
1. Create a SQL virtual machine
2. Configure active geo-replication for Azure SQL Database in the Azure portal and initiate failover.

Module 04 – Why Azure SQL?

4.1 Azure SQL Security Capabilities
4.2 High-Availability and Azure SQL Database

  • Standard Availability Model
  • Premium Availability Model

4.3 Azure Database for MySQL

Hands-on:
1. Design an Azure Database for MySQL database using the Azure portal
2. Connect using MySQL Workbench

4.4 Azure Database for PostgreSQL

Hands-on:
1. Design an Azure Database for PostgreSQL – Single Server

4.5 Azure Database For MariaDB

Hands-on:
1. Create an Azure Database for MariaDB server by using the Azure portal

4.6 What is PolyBase?

  • Why PolyBase?

4.7 What is Azure Synapse Analytics (formerly SQL DW)?

  • SQL Analytics and SQL pool in Azure Synapse
  • Key component of a big data solution
  • SQL Analytics MPP architecture components

Hands-on:
1. Import Data From Blob Storage to Azure Synapse Analytics by Using PolyBase

Module 05 – Azure Batch

5.1 What is Azure Batch?
5.2 Intrinsically Parallel Workloads
5.3 Tightly Coupled Workloads
5.4 Additional Batch Capabilities
5.5 Working of Azure Batch

Hands-on:
1. Run a batch job using Azure Portal
2. Parallel File Processing with Azure Bath using the .NET API
3. Render a Blender Scene using Batch Explorer
4. Parallel R Simulation with Azure Batch

Module 06 – Azure Data Factory

6.1 Flow Process of Data Factory
6.2 Why Azure Data Factory
6.3 Integration Runtime in Azure Data Factory
6.4 Mapping Data Flows

Hands-on:
1. Transform data using Mapping data flows

Module 07 – Azure Data Bricks

7.1 What is Azure Databricks?
7.2 Azure Spark-based Analytics Platform
7.3 Apache Spark in Azure Databricks

Hands-on:
1. Run a Spark Job on Azure Databricks using the Azure portal
2. ETL Operation by using Azure Databricks
3. Stream data into Azure Databricks using Event Hubs

Module 08 – Azure Stream Analytics

8.1 Working of Stream Analytics
8.2 Key capabilities and benefits

Hands-on:
1. Analyse phone call data with stream analytics and visualize results in Power BI dashboard

8.3 Stream Analytics Windowing Functions

  • Tumbling window
  • Hopping Window
  • Sliding Window
  • Session Window

Module 09 – Monitoring & Security

9.1 What is Azure Monitor?

  • Metrics
  • Logs
  • Metrics Vs Logs

9.2 What data does Azure Monitor collect?
9.3 What can you Monitor?

  • Insights and Core Solutions

9.4 Alerts in Azure

  • Flow of Alerts
  • Key Attributes of an Alert Rule
  • What can you set alert on?
  • Manage alerts
  • Alert States
  • How to create an alert?

Hands-on:
1. Create, View, and Manage Metric alerts using Azure Monitor
2. Monitor your Azure Data Factory Pipelines proactively with Alerts

9.5 Azure Security Logging & Auditing

  • Types of Logs in Azure
  • Azure SQL Database Auditing
  • Server-level vs. Database-level Auditing Policy

Hands-on:
1. Azure SQL Database Auditing

Microsoft Azure Data Factory Projects

Create Azure Data Factory to Find the Most Popular YouTube Channel

Fetch the list of videos from the attached dataset of YouTube channel with the highest views and likes to promote advertisements on the channel which has maximum traffic. Through this project, you will acquire a better understanding of Azure Data Factory, Azure Data Lake, Triggers, SQL, Power BI, etc.

 

Working with Azure Data Factory, Data Lake, and Azure SQL

Create an automated solution, using which a company will be able to see a live dashboard of the lead count. You will make use of Power BI Heat maps, Azure SQL instead of On-Premise SQL, and Data Factory to automate the data lifecycle from SQL to the BI tool.
 

Identify the Videos That Get Maximum Traffic in Selected YouTube Channels

In this project, you will be asked to get the real-time list of maximum traffic fetching videos from YouTube channels. Automate the transformation of the real-time video list from YouTube channels on a weekly basis. The traffic can be analyzed on various parameters on a particular day. You can get publicly available data from the YouTube API.
 

Case Study 1 – Non-Relational Data Stores

This case study will check your knowledge of non-relational databases: categories and where to use them. You will be working closely with NoSQL or Non-Relational Database, Azure Data Lake Storage, and its key components.
 

Case Study 2 – Non-Relational Data Stores

In this case study, you will copy data from Azure Blob Storage to Azure Data Lake Storage Gen2. You will also be asked to create an Azure Cosmos DB account and demonstrate adding and removing regions from your Database account. You will be using strategies for partitioning data and semantics of consistency levels in Cosmos DB.
 

Case Study 3 – Relational Data Stores

This case study includes relational databases, deployment models in Azure SQL, creation of an elastic pool, Azure SQL Security Capabilities, importing data from Blob Storage to Azure Synapse Analytics by using PolyBase.
 

Case Study 4 – Azure Batch and Azure Data Factory

This case study will help you understand the working of Azure Batch, the flow process of Data Factory, types of Integration Runtime in Azure Data Factory, and transform data using Mapping Data Flows.
 

Case Study 5 – Azure Data Bricks and Azure Stream Analytics

To conduct this case study, you will be working on ETL Operation by using Azure Databricks, and Stream Analytics Windowing Functions. This case study will make you familiar with the working of Stream Analytics.
 

Case Study 6 – Monitoring & Security

This case study will give you an opportunity to create, view, and manage Metric alerts using Azure Monitor. You will be using Azure SQL Database Auditing. You will also implement Azure Security Logging & Auditing in this case study.

MSBI (Live Course)

MSBI SSIS Course Content

What is BI?

Introduction to Business Intelligence, understanding the concept of Data Modeling, Data Cleaning, learning about Data Analysis, Data Representation, Data Transformation.

ETL Overview

Introduction to ETL, the various steps involved Extract, Transform, Load, using a user’s email ID to read a flat file, extracting the User ID from email ID, loading the data into a database table.

Working with Connection Managers

Introduction to Connection Managers – logical representation of a connection, the various types of Connection Managers – Flat file, database, understanding how to load faster with OLE DB, comparing the performance of OLE DB and ADO.net, learning about Bulk Insert, working with Excel Connection Managers and identifying the problems.

Data Transformations

Learning what is Data Transformation, converting data from one format to another, understanding the concepts of Character Map, Data Column and Copy Column Transformation, import and export column transformation, script and OLEDB Command Transformation, understanding row sampling, aggregate and sort transformation, percentage and row sampling.

Advance Data Transformation

Understanding Pivot and UnPivot Transformation, understanding Audit and Row Count Transformation, working with Split and Join Transformation, studying Lookup and Cache Transformation, Integrating with Azure Analysis Services, elastic nature of MSBI to integrate with the Azure cloud service, scale out deployment option for MSBI, working with cloud-borne data sources and query analysis. Scaling out the SSIS package, deploying for tighter windows, working with larger amount of data sources, SQL Server vNext for enhancing SQL Server features, more choice of development languages and data types both on-premise and in the cloud.

Slowly Changing Dimensions

Understanding data that slowly changes over time, learning the process of how new data is written over old data, best practices.Detail explanation of three types of SCDs –Type1, Type2 and Type3, and their differences.

Overview of Fuzzy Look-up Transformation and Lookup and Term Extraction

Understanding how Fuzzy Lookup Transformation varies from Lookup Transformation, the concept of Fuzzy matching

Concepts of Logging & Configuration

Learning about error rows configuration, package logging, defining package configuration, understanding constraints and event handlers.

MSBI SSRS Course Content

Introduction to SSRS

Get introduced to the SSRS Architecture, components of SSRS Report Building tool, learning about the data flow in different components.

Matrix and Tablix Overview

Understanding the concepts of Matrix and Tablix, working with Text Box, learning about formatting, row/column grouping, understanding sorting, formatting, concepts of Header, Footer, Totals, Subtotals and Page Breaks.

Parameters and Expression

Learning about Parameters, filter and visibility expression, understanding drill-through and drill-down, defining variables, custom code.

Reports and Charts creation

Introduction to various aspects of Bar Chart, Line Chart, Combination Chart, Shape Chart, Sub Reports,Integration of Power Query and M language with SSRS, working with additional data sources in MSBI, rich transformation capabilities addition to MSBI, reusing M functions build for PBIX in SSRS.

Dashboard Building

Learn how to build a Dashboard with Sparklines, Data Bars, Map Charts, Gauge Charts and drilling into reports, the basics of ad hoc reporting.

Data Bar, Sparkline, Indicator, Gauge Chart, Map Chart, Report Drilling, What is Ad hoc reporting?

Reports and Authenticity

Understanding Report Cache, Authorization, Authentication and Report Snapshot, learning about Subscriptions and Site Security.

MSBI SSAS Course Content

Getting started with SSAS

Understanding the concept of multidimensional analysis, understanding SSAS Architecture and benefits, learn what is Cube, working with Tables and OLAP databases, understanding the concept of Data Sources, working with Dimension Wizard, understanding Dimension Structure, Attribute Relationships, flexible and rigid relationship.

Structures and Processes

Learning about Process Dimension, the Process database, creation of Cube, understanding Cube Structure, Cube browsing, defining the various categories, Product Key and Customer Key, Column Naming, processing and deploying a Cube, Report creation with a Cube.

Hands-on Exercise – Create a Cube and name various columns Deploy a cube after applying keys and other rules Create reports with a cube

Type of Database Relationship

Understanding Data Dimensions and its importance, the various relationships, regular, referenced, many to many, fact, working on Data Partitions, and Data Aggregations.

SSAS Cube

Learning about SSAS Cube, the various types of Cubes, the scope of Cube and comparison with Data Warehouse.

Cube: Operations & Limitations

The various operations on Cube, the limitations of OLAP Cubes, the architecture of in-memory analytics and its advantages.

Cube and In-memory Analytics

Deploying cube with existing data warehouse capabilities to get self-service business intelligence, understanding how in-memory analytics works.

Hands-on Exercise – Deploy cube to get self-service business intelligence

Data Source View

Logical model of the schema used by the Cube, components of Cube, understanding Named Queries and Relationships.

Dimensions

An overview of the Dimensions concept, describing the Attributes and Attributes Hierarchies, understanding Key/Value Pairs, Metadata Reload, logical keys and role-based dimensions.

Hands-on Exercise – Create role based dimensions, Use Attributes Hierarchies

Measures & Features of Cube

Understanding the Measure of Cube, analyzing the Measure, exploring the relationship between Measure and Measure Group, Cube features and Dimension usage.

Measures and Features of Cube Cont.

Working with Cube Measures, deploying analytics, understanding the Key Performance Indicators, deploying actions and drill-through actions on data, working on data partitions, aggregations, translations and perspectives.

Hands-on Exercise – Work with Cube Measures, Deploy analytics, Deploy actions and drill-through actions on data, Make data partitions

Working with MDX

Understanding Multidimensional Expressions language, working with MDX queries for data retrieval, working with Clause, Set, Tuple, Filter condition in MDX.

Hands-on Exercise – Apply Clause, Set and filter condition in MDX query to retrieve data

Functions of MDX

Learning about MDX hierarchies, the functions used in MDX, Ancestor, Ascendant and Descendant function, performing data ordering

Hands-on Exercise – Create MDX hierarchies, Perform data ordering in ascending order, in descending order

BI Semantic Model

Designing and publishing a tabular data model, Designing measures relationships, hierarchies, partitions, perspectives, and calculated columns

Hands-on Exercise – Design and publish a tabular data model, Design measures relationships, hierarchies, partitions, perspectives, and calculated columns

Plan and deploy SSAS

Configuring and maintaining SQL Server Analysis Services (SSAS), Non-Union Memory Architecture (NUMA), Monitoring and optimizing performance, SSAS Tabular model with vNext, Excel portability, importing model from Power BI Desktop, importing a Power Pivot model, bidirectional cross-filtering relationship in MSBI.

Hands-on Exercise – Configure and maintain SQL Server Analysis Services (SSAS), Monitor and optimize performance

Microsoft Courses ( Self-Paced Course)

Analyzing Data with SQL Server Reporting Services

MSBI Projects

SSIS

Create a data flow task to extract data from the XLS format and store it into the SQL database. Store the subcategory and category-wise sales in a table of the database. Through this project, you will be able to understand Data Flow, ODBC Set up and Connection Manager, Flat File Connection, and various Transformations.

SSRS

By using a Student Survey data set, try to extract meaningful Insights by creating an SSRS Report to show Tabular Visualization, Matrix Visualization, Funnel chart, Pie chart, Scatter plot, Drill-down, etc. You will have hands-on experience in using Reporting Services, Expression, Parameters, Charts, Reports, Transformations, etc.

SSAS

You will be working deeply in Data Dimensions and Cubes as part of this project. Using Adventure Works DW database, you will have to build a Cube to show the number of products there for each color, the total sales, the amount for each currency, and the number of products there for each product name. You will get familiar with SSAS Perspectives.

Case Study 1 – SSIS

As part of this case study, you will need to create the connection of OLDB and load the data in SQL Server from Excel. You will be working with Transformations and learn how to create constants and events in a package. You will further get to work with Data Flow, Term Extraction, and Lookup.

Case Study 2 – SSRS

The topics that you will be covering in this case study are Reporting Services, Report Creation, Expression and Parameters, Report and Authentication, and Deployment. You will be using data connections, KPI, functions of data bars, etc. You will also learn how to pass the parameter from the main chart to the detail chart (Pie).

Case Study 3 – SSAS

For this case study, you will have to have knowledge of data mart, measures and features of Cube, Data Dimensions and Cubes, KPI’s, perspectives in SSIS and SSAS, etc.

Qlik Sense (Self-paced)

Qlik Sense Introduction and Installation

How does Qlik Sense vary from QlikView, the need for self-service Business Intelligence/Business Analytics tools, Qlik Sense data discovery, intuitive tool for dynamic dashboards and personalized reports and the installation of Qlik Sense and Qlik Sense Desktop

Hands-on Exercise: Install Qlik Sense and Qlik Sense Desktop
 

Qlik Sense Features

Drag-and-drop visualization, Qlik Data indexing engine, data dimensions relationships, connect to multiple data sources, creating your own dashboards, data visualization, visual analytics and the ease of collaboration

Hands-on Exercise: Connect to a database or load data from an Excel file and create a dashboard
 

Qlik Sense Data Model

Understand data modeling, best practices, turning data columns into rows, converting data rows into fields, hierarchical-level data loading, loading new or updated data from database, using a common field to combine data from two tables and handling data inconsistencies

Hands-on Exercise: Turn data columns into rows, convert data rows into fields, load the data in hierarchical level, load new or updated data from database and use a common field to combine data from two tables
 

Creating a Data Model

Qlik Sense data architecture, understanding QVD layer, converting QlikView files to Qlik Sense files and working on synthetic keys and circular references

Hands-on Exercise: Convert QlikView files to Qlik Sense files and resolve synthetic keys and circular references
 

Advanced Data Modeling

Qlik Sense star schema, link table, dimensions table, master calendar, QVD files and optimizing data modeling

Hands-on Exercise: Create a Qlik Sense star schema, create link table, dimensions table, master calendar and QVD files
 

Qlik Sense Enterprise

Qlik Sense enterprise class tools, Qlik Sense custom app, embedding visuals, rapid development, powerful open APIs, enterprise-class architecture, Big Data integration, enterprise security and elastic scaling

 

Qlik Sense Visualization

Learning about Qlik Sense visualization tools, charts and maps creation, rich data storytelling and sharing analysis visually with compelling visualizations

Hands-on Exercise: Create charts and maps, create a story around dataset and share analysis
 

Set Analysis

Understanding set analysis in Qlik Sense, various parts of a set expression like identifiers, operators, modifiers and comparative analysis

Hands-on Exercise: Do Set Analysis in Qlik Sense, use set expression like identifiers, operators, modifiers and comparative analysis
 

Advanced Set Analysis

Learning about set analysis which is a way of defining a set of data values different from normal set, deploying comparison sets and point-in-time analysis

Hands-on Exercise: Deploy comparison sets and perform point-in-time analysis
 

Qlik Sense Charts

Introduction to various charts in Qlik Sense like line chart, bar chart, pie chart, table chart and pivot table chart and the characteristics of various charts

Hands-on Exercise: Plot charts in Qlik Sense like line chart, bar chart, pie chart, table chart and pivot table chart
 

Advanced Charts

Understanding what is a KPI chart, gauge chart, scatter plots chart and map chart/geo map

Hands-on Exercise: Plot a KPI chart, gauge chart, scatter plots chart and map chart/geo map
 

Master Library

Introduction to the Qlik Sense Master Library, its benefits, distinct features and user-friendly applications

Hands-on Exercise: Explore and use Qlik Sense Master Library
 

Qlik Sense Storytelling

Understanding how to do storytelling in Qlik Sense and the creation of storytelling and story playback

Hands-on Exercise: Use the storytelling feature of Qlik Sense, create a story and playback the story
 

Mashups

Understanding mashups in Qlik Sense, creating a single graphical interface from more than one sources, deploying the mashups flowchart, testing of mashups and the various mashup scenarios like simple and normal

Hands-on Exercise: Create a single graphical interface from more than one sources, deploy the mashups flowchart and test mashups
 

Extensions

Understanding the Qlik Sense Extension, working with it, various templates in Qlik Sense Extension, testing of it, making Hello World dynamic and learning how it works and adding a preview image

Hands-on Exercise: Work with Qlik Sense Extension, use a template in Qlik Sense Extension and test it, make Hello World dynamic and add a preview image
 

Security

Various security aspects of Qlik Sense, content security, security rules, various components of security rules and understanding data reductions and dynamic data reductions and the user access workflow

Hands-on Exercise: Create security rules in Qlik Sense and understand data reductions and dynamic data reductions and the user access workflow

What projects will I be working on in this Qlik Sense training?

Project 1

Objective: This project involves working with the Qlik Sense dashboard that displays the sales details whether order-wise, year-wise, customer-wise sales or product-wise sales and so on, doing comparative analysis, rolling six months analysis that should be displaying the trend of sales and placing the worksheets in a user story and publishing.

Project 2

Domain: Data Analytics

Objective: To see the current values of salaries in one column and historical values in another cell in a chart that would contain a bar chart and a trend chart

Project 3

Domain: Healthcare

Objective: Visual Mapping between the vaccination rate and measles outbreak

SQL DBA (Self-paced)

Installation and Configuration
 

  • Plan Installation

Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, auto grow, and monitor growth; manage the technologies that influence SQL architecture (e.g., service broker, full text, scale-out, etc.); design the storage for new databases (drives, filegroups, partitioning, etc.); design the database infrastructure; configure an SQL Server standby database for reporting purposes; Windows-level security and service-level security; core mode installation; benchmark a Server before using it in a production environment (SQLIO, Tests on SQL Instance, etc.); and choose the right hardware
 

  • Installing SQL Server and Related Services

Test connectivity; enable and disable features; install SQL Server database engine and SSIS (but not SSRS and SSAS), and configure an OS disk
 

  • Implementing a Migration Strategy

Restore vs. detach/attach; migrate security; migrate from a previous version; migrate to new hardware, and migrate systems and data from other sources
 

  • Configuring Additional SQL Server Components

Set up and configure all SQL Server components (Engine, AS, RS, and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; and file table
 

  • Manage SQL Server Agent

Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to “Manage SQL Server Agent jobs”

 

Managing Instances and Databases
 

  • Managing and Configuring Databases

Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC
 

  • Configuring SQL Server Instances

Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure Server level settings; configure many databases/instance, many instances/Server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options
 

  • Implementing an SQL Server Clustered Instance

Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node
 

  • Managing SQL Server Instances

Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

 

Optimizing and Troubleshooting
 

  • Identifying and Resolving Concurrency Problems

Examine deadlocking issues using the SQL Server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; and locate and if necessary kill processes that are blocking or claiming all resources
 

  • Collecting, Analyzing, and Troubleshooting Data

Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use XEvents and DMVs; create alerts on critical Server condition; monitor data and Server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; and use the Data Collector tool
 

  • Auditing SQL Server Instances

Implement a security strategy for auditing and controlling the instance; configure an audit; configure Server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; and policy-based management

 

Managing Data
 

  • Configuring and Maintaining a Back-up Strategy

Manage different backup models, including point-in-time recovery; protect customer data even if backup media is lost; perform backup/restore based on proper strategies including backup redundancy; recover from a corrupted drive; manage a multi-TB database; implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore); back up a SQL Server environment; and backup system databases
 

  • Restoring Databases

Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; and page-level restore
 

  • Implementing and Maintaining Indexes

Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; and column store indexes
 

  • Importing and Exporting Data

Transfer data; bulk copy; and bulk insert

Implementing Security
 

  • Managing Logins and Server Roles

Configure Server security; secure the SQL Server using Windows Account / SQL Server accounts, Server roles; create log in accounts; manage access to the Server, SQL Server instance, and databases; create and maintain user-defined Server roles; and manage certificate logins
 

  • Managing Database Security

Configure database security; database level, permissions; protect objects from being modified; auditing; and encryption
 

  • Managing Users and Database Roles

Create access to Server / database with least privilege; manage security roles for users and administrators; create database user accounts; and contained login
 

  • Troubleshooting Security

Manage certificates and keys, and endpoints

 

Implementing High Availability
 

  • Implementing AlwaysOn
    • Implement AlwaysOn availability groups and AlwaysOn failover clustering
       
  • Implementing replication
    • Troubleshoot replication problems and identify appropriate replication strategy

What projects will I be working on in this MS SQL Server DBA training?
 

Project 1: SQL Server Audit

Industry: General

Problem Statement: How to track and log events happening on the database engine
 

Topics: This project is involved with implementing a SQL Server audit that includes creating of the TestDB database, triggering audit events from tables, altering audit, checking, filtering, etc. You will learn to audit a SQL Server instance by tracking and logging the events on the system. You will also work with SQL Server Management and learn about the database-level and server-level auditing.
 

Highlights:

  • SQL Server Management Studio
  • Expanding SQL Server log folder
  • Database and server audit specification

Project 2: Managing SQL Server for a High-tech Company

Industry: Information Technology

Problem Statement: An IT company wants to manage its MS SQL Server Database and gain valuable insights from it.

Topics: In this project, you will be administrating MS SQL Server Database. You will learn about the complete architecture of MS SQL Server. You will also be familiarized with the enterprise edition of SQL Server, various tools of SQL Server, creating and modifying databases, and more.
 

Highlights:

  • Creating a database schema in SQL Server
  • Adding, removing, and moving database files
  • Database backup and recovery

Data warehousing & Data Modeling (Self-paced)

Introduction to Data Warehouse

Introducing Data Warehouse and Business Intelligence, understanding difference between database and data warehouse, working with ETL tools, SQL parsing.
 

Architecture of Data Warehouse

Understanding the Data Warehousing Architecture, system used for Reporting and Business Intelligence, understanding OLAP vs. OLTP, introduction to Cubes.
 

Data Modeling concepts

The various stages from Conceptual Model, Logical Model to Physical Schema, Understanding the Cubes, benefits of Cube, working with OLAP multidimensional Cube, creating Report using a Cube.
 

Data Normalization

Understanding the process of Data Normalization, rules of normalization for first, second and third normal, BCNF, deploying Erwin for generating SQL scripts.
 

Dimension & Fact Table

The main components of Business Intelligence – Dimensions and Fact Tables, understanding the difference between Fact Tables & Dimensions, understanding Slowly Changing Dimensions in Data Warehousing.
 

SQL parsing, Cubes & OLAP

SQL parsing, compilation and optimization, understanding types and scope of cubes, Data Warehousing Vs. Cubes, limitations of Cubes and evolution of in-memory analytics.
 

Erwin Design Layer Architecture (self paced)

Learning the Erwin model, understanding the Design Layer Architecture, data warehouse modeling, creating and designing user defined domains, managing naming and data type standards.
 

Forward & Reverse Engineering (self paced)

Understanding of the forward and reverse engineering, comparison between the two.

What projects will I be working on in this Data Warehouse training?
 

Project 1 – Logical & Physical Data Modeling Using Erwin

Data – Invoice Management (Sales)
 

Topics – This project is involved with creating logical and physical data model using the CA Erwin data modeler design layer architecture. You will learn about the techniques for turning a logical model into a physical design. With this project you will be well-versed in the process of reverse and forward engineering. You will understand both the top down and bottom up design methodology.
 

Project 2– End-to-End implementation of Data Warehouse (Retail Store)

Data –Sales
 

Topics – In this project you will learn about the process of loading data into a data warehouse using the ETL tools. You will learn about the ways to create and deploy the data warehouse. Oracle provides support for multiple physical but only one logical model in the data warehouse. This project will provide you extensive experience in integrating, cleansing, customization and insertion of data in the data warehouse for a retail store.