List of Courses Included
Online Instructor-led Courses:
- MS SQL
- Tableau Desktop 10
- Power BI
- Informatica Developer & Admin
- Azure Data Factory
- 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.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
- Implementing Correlated Subqueries
- Using EXISTS with a Correlated subquery
- Using Union Query
- Using Grouping Set Query
- Using Rollup
- Using CUBE to generate four grouping sets
- 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
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
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.
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.
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
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
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
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
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.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
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
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
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 –
- Demo of building a Power BI dashboard
- Source data from web
- Publish to cloud
- 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 –
- Connect to a database
- Import data from an excel file
- Connect to SQL Server
- Analysis Service
- Connect to Power Query
- Connect to SQL Azure
- 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 –
- Install Power BI Desktop and configure the settings
- Use Query editor
- Write a power query
- 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 –
- Create a Power Pivot Apply filters
- Use advanced functionalities like date and time functions
- Embed Power Pivot in Power BI Desktop
- 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.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 –
- Create a Power View and a Power Map
- Format and customize visuals
- Deploy Power View on SharePoint and Excel
- Implement top-down and bottom-up analytics
- Create Power View reports, Charts, Scorecards
- Add a custom visual to report
- Authenticate a Power BI web application
- Embed dashboards in applications
- Categorize, filter and sort data using Power View
- Create hierarchies
- Use date hierarchies
- Use business hierarchies
- 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 –
- Write queries using natural language
- Get answers in the form of charts, graphs
- Build ad hoc analytics
- 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 –
- Configure security for dashboard Deploy data gateways
- Aggregate data from multiple data sources
- Schedule data refresh
- Manage groups and row level security, datasets, reports and dashboards
- 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
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
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
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 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
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
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
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
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
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
1.10 Azure Data Lake Storage
- 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
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
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?
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
1. Design an Azure Database for MySQL database using the Azure portal
2. Connect using MySQL Workbench
4.4 Azure Database for PostgreSQL
1. Design an Azure Database for PostgreSQL – Single Server
4.5 Azure Database For MariaDB
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
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
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
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
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
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 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?
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
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.
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.
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.
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.
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.
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
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.
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.
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
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
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
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
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
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
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?
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.
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
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
- 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
- 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
- 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
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.
- 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.
- 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.
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)
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.