header-logo.png
hd14

Overview

Course Description

Business Intelligence Architect master's course lets you gain proficiency in Business Intelligence. You will work on real-world projects in Informatica, Tableau, MSBI, Power BI, MS SQL, Data warehousing and Erwin, Azure Data Factory, SQL DBA and more.

Course Content

MS SQL 

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 Data Anomalies
2.2 Update Anomalies
2.3 Insertion Anomalies
2.4 Deletion Anomalies
2.5 Types of Dependencies
2.6 Functional Dependency
2.7 Fully functional dependency
2.8 Partial functional dependency
2.9 Transitive functional dependency
2.10 Multi-valued functional dependency
2.11 Decomposition of tables
2.12 Lossy decomposition
2.13 Lossless decomposition
2.14 What is Normalization?
2.15 First Normal Form
2.16 Second Normal Form
2.17 Third Normal Form
2.18 Boyce-Codd Normal Form(BCNF)
2.19 Fourth Normal Form
2.20 Entity-Relationship Model
2.21 Entity and Entity Set
2.22 Attributes and types of Attributes
2.23 Entity Sets
2.24 Relationship Sets
2.25 Degree of Relationship
2.26 Mapping Cardinalities, One-to-One, One-to-Many, Many-to-one, Many-to-many
2.27 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 - Managing Data with Transact-SQL

10.1 Creating Transact-SQL queries
10.2 Querying multiple tables using joins
10.3 Implementing functions and aggregating data
10.4 Modifying data
10.5 Determining the results of DDL statements on supplied tables and data
10.6 Constructing DML statements using the output statement

Module 11 - Querying Data with Advanced Transact-SQL Components

11.1 Querying data using subqueries and APPLY
11.2 Querying data using table expressions
11.3 Grouping and pivoting data using queries
11.4 Querying temporal data and non-relational data
11.5 Constructing recursive table expressions to meet business requirements
11.6 Using windowing functions to group
11.7 Rank the results of a query

Module 12 - Programming Databases Using Transact-SQL

12.1 Creating database programmability objects by using T-SQL
12.2 Implementing error handling and transactions
12.3 Implementing transaction control in conjunction with error handling in stored procedures
12.4 Implementing data types and NULL

Module 13 - Designing and Implementing Database Objects

13.1 Designing and implementing relational database schema
13.2 Designing and implementing indexes
13.3 Learning to compare between indexed and included columns
13.4 Implementing clustered index
13.5 Designing and deploying views
13.6 Column store views

Module 14 - Implementing Programmability Objects

14.1 Explaining foreign key constraints
14.2 Using T-SQL statements
14.3 Usage of Data Manipulation Language (DML)
14.4 Designing the components of stored procedures
14.5 Implementing input and output parameters
14.6 Applying error handling
14.7 Executing control logic in stored procedures
14.8 Designing trigger logic, DDL triggers, etc.

Module 15 - Managing Database Concurrency

15.1 Applying transactions
15.2 Using the transaction behavior to identify DML statements
15.3 Learning about implicit and explicit transactions
15.4 Isolation levels management
15.5 Understanding concurrency and locking behavior
15.6 Using memory-optimized tables

Module 16 - Optimizing Database Objects

16.1 Accuracy of statistics
16.2 Formulating statistics maintenance tasks
16.3 Dynamic management objects management
16.4 Identifying missing indexes
16.5 Examining and troubleshooting query plans
16.6 Consolidating the overlapping indexes
16.7 The performance management of database instances
16.8 SQL server performance monitoring

Module 17 - Advanced Topics

17.1 Correlated Subquery, Grouping Sets, Rollup, Cube

Module 18 - Microsoft Courses: Study Material

18.1 Performance Tuning and Optimizing SQL Databases
18.2 Querying Data with Transact-SQL

 

Tableau Desktop 10

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

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.

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.

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

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

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

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

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

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

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 and Hadoop

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
13.5 The integration of Tableau with Hadoop

 

Power BI

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 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

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

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

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

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

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

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

Module 8 - Microsoft Course

8.1 Analyzing Data with Power BI

 

Informatica Developer & Admin

Module 1 -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

Module 2-Informatica Installation and Configuration

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

Module 3-Working with Active and Passive Transformation

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

Module 4 -Working with Expression Transformation

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

Module 5 -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

Module 6 -Working with Joiner Transformation

Working with joiner transformation to bring data from heterogeneous data sources

Module 7-Working with Ranking and Union Transformation

Understanding the ranking and union transformation, the characteristics and deployment

Module 8-Syntax for Rank and Dense Rank

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

Module 9 -Router Transformation

Understanding how router transformation works and its key features

Module 10 -Source Qualifier Transformation and Mappings

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

Module 11 -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

Module 12 -Mapplet and Loading to Multiple Designer

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

Module 13 -Performance Tuning in Informatica

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

Module 14 -Repository Manager

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

Module 15- Best Practices in Informatica

Understanding and adopting best practices for managing repository

Module 16-Workflow Informatica

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

Module 17-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

Module 18 -Error Handling and Recovery in Informatica

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

Module 19 -High Availability and Failover in Informatica

Configurations and mechanisms in recovery and checking health of PowerCenter environment

Module 20 -Working with Different Utilities in Informatica

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

Module 21 - Flat File Processing (Advanced Transformations)

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

Module 22 -Dynamic Targeting

Dynamic target with the use of transaction control and indirect loading

Module 23- Working with Java Transformations

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

Module 24-Unconnected Stored Procedure Usage

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

Module 25 -Advanced Concepts in SCD

Using SQL transformation (active and passive)

Module 26 - Incremental Data Loading and Aggregation

Understanding incremental loading and aggregation and comparison between them

Module 27- Constraint-based Loading

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

Module 28 -XML Transformation and Active Lookup

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

Module 29 - 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

Module 30 -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

Module 31- Database Connection

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

Module 32 -Relational Database Tables

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

Module 33 -LinkedIn Connection

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

Module 34 - Connection with Sources

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

Module 35 - Pushdown Optimization and Partitioning

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

Module 36 – Cache Management

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

 

Azure Data Factory

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

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?

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

4.4 Azure Database for PostgreSQL

4.5 Azure Database For MariaDB

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

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

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

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

Module 08 - Azure Stream Analytics

8.1 Working of Stream Analytics
8.2 Key capabilities and benefits

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?

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?

9.5 Azure Security Logging & Auditing

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

 

Qlik Sense 

Module 1 - 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

Module 2- 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

Module 3- 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

Module 4 -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

Module 5 - Advanced Data Modeling

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

Module 6- 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

Module 7- Qlik Sense Visualization

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

Module 8 -Set Analysis

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

Module 9 - 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

Module 10 - 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

Module 11- Advanced Charts

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

Module 12 -Master Library

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

Module 13- Qlik Sense Storytelling

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

Module 14 - 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

Module 15 - 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

Module 16- 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

 

SQL DBA 

Module 1 - 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, autogrow, 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 filetable

  • 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”

Module 2 - 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

Module 3 - 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

Module 4 - 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 back up 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

Module 4 - 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

Module 5 - Implementing High Availability

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


 

Data warehousing & Data Modeling

Module 1- Introduction to Data Warehouse

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

Module 2 - Architecture of Data Warehouse

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

Module 3 -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.

Module 4- Data Normalization

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

Module 5 -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.

Module 6 - 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.

Module 7- Erwin Design Layer Architecture 

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

Module 8 - Forward & Reverse Engineering 

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

Student feedback

10 Reviews

  • 8
  • 0
  • 0
  • 0
  • 0

5

out of 5

Course Rating

review1.png

Karan Gautam

Good course

Session was amazing and trainer was also also good. Cleared all the concepts. Great quality of the course content and excellent pace displayed! I'm glad I found this course! Highly Recommend!!!


review1.png

Mansi Saxena

Amazing

Thank you for this course. I got to know so much knowledge from this Master's in Business Intelligence and Architect Program. Course material covered all the important topics.


review1.png

Milli Sharma

Well constructed course

I'm delighted to have attended sessions on SparkAcademy, the trainer explained the concepts making sure that everyone can understand the concepts, every minute thing is also discussed really well in the course.


review1.png

Vanshikha Rawat

Great Course

Training staff is amazing and taught all concepts crystal clear, would love to join SparkAcademy again, they provide appreciable training, loved it.


review1.png

Prince Singh

Cleared all doubts

All sessions were worth it, my doubts are all solved, trainer took the sessions really well. Overall it was great training


review1.png

Mahima Srivastava

Great Course

A good course for beginners as well as experts. Here in this all the basic concepts were well explained. And it was much detail oriented with important concepts.


review1.png

Pooja Verma

Helpful content

SparkAcademy is one of the best solutions to learn online. I enrolled in the Master's in Business Intelligence Architect program and it really helped me in my learning.


review1.png

Tamanna Rathi

Clear concepts

This course was very good & informative, content was comprehensive and elaborate. The trainer was very knowledgeable and answered all queries too.


review1.png

Monica Kaur

Good training skills

the trainer's knowledge and excellent communication skills while delivering sessions was commendable.. He taught everything really efficiently.


review1.png

Hardik Upadhyay

Great training

SparkAcademy provides really great course content. Loved it, the way topics were delivered was really great!


Add Reviews & Rate

  • What is it like to Course?

Related Courses

t1.jpg
Business Intelligence Courses
Preview Course

Power BI Certification Course
t1.jpg
Business Intelligence Courses
Preview Course

Tableau Training and Certification Course

    Course Features

    • MS SQL
    • Tableau Desktop 10
    • Power BI
    • Informatica Developer & Admin
    • Azure Data Factory
    • Data Warehousing & Data Modeling
    • Qlik Sense
    • SQL DBA