• SQL Server 2012 Installation
  • Service Accounts & Use, Authentication Modes & Usage, Instance Configurations
  • SQL Server Features & Purpose
  • Using Management Studio (SSMS)
  • Configuration Tools & SQLCMD
  • Conventions & Collation
  • SQL Database Architecture
  • Database Creation using GUI
  • Database Creation using T-SQL scripts
  • DB Design using Files and File Groups
  • File locations and Size parameters
  • Log files and placement
  • Database Structure modifications
    • SQL Server Database Tables
    • Table creation using T-SQL Scripts
    • Naming Conventions for Columns
    • Single Row and Multi-Row Inserts
    • Table Aliases
    • Column Aliases & Usage
    • Table creation using Schemas
    • Basic INSERT
    • UPDATE
    • DELETE
    • SELECT queries and Schemas
    • Use of WHERE, IN and BETWEEN
    • Variants of SELECT statement
    • ORDER BY
    • GROUPING
    • HAVING
    • ROWCOUNT and CUBE Functions
    • Table creation using Constraints
    • NULL and IDENTITY properties
    • UNIQUE KEY Constraint and NOT NULL
    • PRIMARY KEY Constraint & Usage
    • CHECK and DEFAULT Constraints
    • Naming Composite Primary Keys
    • Disabling Constraints & Other Options
    • Benefits of Views in SQL Database
    • Views on Tables and Views
    • SCHEMA BINDING and ENCRYPTION
    • Issues with Views and ALTER TABLE
    • Common System Views and Metadata
    • Common Dynamic Management views
    • Working with JOINS inside views
    • Need for Indexes & Usage?
    • Indexing Table & View Columns
    • Index SCAN and SEEK
    • INCLUDED Indexes & Usage
    • Materializing Views (storage level)
    • Composite Indexed Columns & Keys
    • Indexes and Table Constraints
    • Primary Keys & Non Clustered Indexes
    • Why to use Stored Procedures?
    • Types of Stored Procedures
    • Use of Variables and parameters
    • SCHEMABINDING and ENCRYPTION
    • INPUT and OUTPUT parameters
    • System level Stored Procedures
    • Dynamic SQL and parameterization

    This course will teach you the fundamentals of using the Azure AD service to manage the users and the authentication process for your applications hosted on Azure.

    • Scalar Valued Functions
    • Types of Table Valued Functions
    • SCHEMABINDING and ENCRYPTION
    • System Functions and usage
    • Date Functions
    • Time Functions
    • String and Operational Functions
    • ROW_COUNT
    • GROUPING Functions
    • Why to use Triggers?
    • DML Triggers and Performance impact
    • INSERTED and DELETED memory tables
    • Data Audit operations & Sampling
    • Database Triggers and Server Triggers
    • Bulk Operations with Triggers
    • ACID Properties and Scope
    • EXPLICIT Transaction types
    • IMPLICIT Transactions and options
    • AUTOCOMMIT Transaction and usage
    • SAVEPOINT and Query Blocking
    • Complex SPs with Transactions
    • Linked Server and DTC operations

    SSIS(SQL SERVER INTEGRATION SERVICES)

    • Version tracking
    • Control flow package parts
    • Preparing to implement control flow package parts
    • Creating a single task control flow package parts
    • Handling variables, parameters and package parts
    • Configuring and testing a package parts
    • Working with multiple tasks in a package part
    • Using package parts from another project and handling changes
    • Deploying packages with control flow packaging parts
    • Updated excel connection manager
    • Updated analysis services components
    • Reviewing tabular model partitioning concepts
    • Configuring dynamic tabular partitioning processing
    • Configuring the partition processing destination
    • Updated xml task
    • Hadoop control flow components
    • Preparing data for HDFS and configuring the HDFS task
    • Configuring Hive tasks
    • Configuring Pig tasks
    • Configuring the Hadoop Connection Manager
    • Executing a package with Hadoop Control Flow components
    • Add-in connectors introduction
    • Hdfs data flow components introduction
    • OData support introduction
    • Data Feed publishing introduction and demo
    • Balanced Data Distributor performance analysis
    • Buffer sizing
    • Prepare for buffer sizing testing
    • Buffer size tuning and performance analysis
    • Always on availability groups and SSIS
    • Preparing Databases for an availability group
    • Configuring databases and SSISBD for an availability group
    • Always encrypted
    • Setting up an always encrypted table
    • Loading data into an encrypted column
    • Using parameterizes execute sql task with always encrypted table
    • Extracting data from an encrypted column
    • Integration services feature pack for azure
    • Azure storage connection manager
    • Azure subscription connection manager
    • Azure resource manager connection manager
    • Azure HDInsight connection manager
    • Azure data lake connection manager
    • Azure blob upload task
    • DW upload tasks
    • Creating a SQL DW
    • Using the SQL DW Upload Task
    • HDInsight Cluster Tasks & Big Data Processing in Azure
    • Executing and troubleshooting HdInsight Tasks.
    • Data flow diagnostics
    • Data flow diagnostics in SSDT
    • Data flow diagnostics in SSMS
    • Incremental Deployment
    • Package Loggig
    • Runtime Lineage and custom log levels
    • DiagnosticEx and other Logging Results
    • Managing Log security

    SQL SERVER REPORTING SERVICES(SSRS)

    Creating ssrs project

    Creating a line item listing report with sql server data tools

    • Introducing the line item report
    • Using the wizard
    • Working with the generated report
    • Introducing the manual report generation
    • Manually adding a Data Source
    • Manually adding a Dataset
    • Manually adding a table to the Report
    • Manually adding grouping
    • Manually adding Totals
    • Manually sorting the Detail Data
    • Introduction to Formating Report
    • Creating a New Report from an existing one
    • Formatting Numeric Values
    • Formatting the Printed pages
    • Resizing columns in the table
    • Creating a group header
    • Formatting Text
    • Repeating Column Headers on Every Page
    • Creating page headers
    • Creating page footers
    • Formatting tips
    • Introducing the Matrix
    • Setting up the Data Source
    • Designing the Query
    • Setting up the matrix
    • Sorting months correctly
    • Wrapping up the matrix-+
    • Introducing charts
    • Adding the chart report
    • Creating the analysis service data source
    • Creating the dataset
    • Adding the chart
    • Fixing the horizontal axis
    • Fixing the vertical axis
    • Formatting the title and legend
    • Introduction
    • Creating the Report, Data source and dataset
    • Adding the chart
    • Adding the table
    • Wrapping up the report
    • Introduction to parameters
    • Buying group parameter-Adding a parameter with drop down values
    • Year Parameter – A Second parameter – Default values
    • Minimum total excluding tax parameter-Allowing the user to enter values for a parameter
    • Working with shared datasets
    • Deploying reports from sql sever data tools
    • Uploading reports from the file system

    SQL Server Analysis Services(SSAS)

    • Introduction to SSAS 2012 & 2014
    • Multidimensional Mode Configuration
    • Tabular Mode Configuration, In-Memory
    • Testing SSAS Workspace Servers in SSDT
    • Understanding Real-world Databases
    • Need for OLAP Databases and Mining
    • Need for Cubes and Faster Reports
    • Identifying OLAP / Analysis Entities
    • Kimball and Inmon Methods - BI Design
    • SSAS Usage and Target Access Methods
    • Column Storage, Row Storage Options
    • Data Source and Data Source Views
    • Computed Columns and Attributes
    • Building Relationships For Cubes
    • Identifying Facts and Dimensions
    • Identifying Measurable Aggregates
    • Identifying Attributes and Hierarchies
    • Deciding Cube (MOLAP) Schema
    • Named Queries and Relations in DSV
    • Database Dimensions from DSV Entities
    • Cube Dimensions from DSV Relations
    • Cube & Database Dimensions – Issues
    • Creating Multi-Dimensional Entities
    • Attributes and Key Member Names
    • Cube Dimension Templates and Options
    • Applying Time Based Dimensions
    • Dimension Hierarchies and Limitations
    • Cube Attribute Relations and Uses
    • Composite Attribute Keys in OLAP Cubes
    • Building Attribute Levels and Hierarchies
    • Duplicate Attribute Key Errors
    • Common Deployment & Security Errors
    • Deployment Warnings in Real-World
    • Cube Design - Regular Templates
    • Cube Measures and Dimension Keys
    • Additive and Semi-Additive Measures
    • Cube Customization Options and MDX
    • Calculations and MDX Members
    • MDX Scripts for Member Calculations
    • Cube Calculation Sets and MDX Scripts
    • BI Enhancements and MDX in MOLAP
    • Time-Based Calculations with MDX
    • Role Playing Dimensions and MDX
    • Fact and Factless Dimensions
    • Referenced Dimensions & Usage
    • Conformed Dimensions & Usage
    • Degenerate Dimensions & Usage
    • Junk & Hierarchical Dimensions
    • Dimension Usage and Granularity
    • Building Fact Less Dimensions
    • Static and Dynamic KPIs with MDX
    • Parent and Child KPIs with MDX
    • Defining KPIs and Goals using MDX
    • Perspectives using MDX Expressions
    • SSAS Actions with MDX and XMLA
    • Local and Remote Cube Partitions
    • Storage Modes - MOLAP, ROLAP, HOLAP
    • Aggregations and Proactive Caching
    • Tuning Cube Partition Processing
    • Row Estimations and Aggregations
    • Assigning Aggregations, Scripts
    • Usage-Based Optimization (UBO)
    • MDX Functions & Conditional JOINS
    • Complex MDX Queries & FILTERS
    • CROSSAPPLY versus MDX JOINS
    • CROSSAPPLY and MDX Query Filters
    • Cube and Dimension Writebacks
    • MDX with Roleplaying Dimensions
    • MDX Transactions & Limitations
    • Need for Data Mining Structures
    • Data Mining Algorithms and Rules
    • DMX Queries and Forecast Reports
    • Data Mining Scope and DMX Ranks
    • Entity Identification Procedures
    • Workspace Configuration Settings
    • In-Memory Processing Options
    • Advance Cube Design Scenarios
    • Aggregated Measures with KPIs
    • Partitioned Dimensions & Usage
    • Conditional MDX Scripts & Options
    • Hierarchical MDX in Tabular Mode
    • MDX Filters and Joins in Tabular Mode
    • Deployment Options and Pre-requisites
    • Cube Deployment Process and Tools
    • SSAS Deployment Wizard Tool & Errors
    • Storage Locations - Cube & Partitions
    • Creating OLAP Databases, Scripts
    • Processing Options and Schedules
    • Managing Deployment Process (OLAP)
    • SSAS Database and Cube/Mining Audits
    • MDX Query Log Tables in DB Engine
    • Flight Recorder Options and Sampling
    • Managing SSAS Log Providers
    • OLAP & Tabular Database Backups
    • OLAP Compressions and Encryptions
    • Backups, Restores and Passwords
    • Scheduling Backups and Jobs
    • OLAP DB Synchronizations and XMLA
    • Partitions - Splits and Merge Options
    • Managing Partitions & Aggregations
    • Need for Optimization and Audits
    • Designing Aggregations using UBO
    • Aggregations For Optimization Levels
    • Audit Query Filters and Reuse Options
    • OLAP Restore Options and XMLA
    • Cube Analysis with Excel Tables
    • Excel Pivots and Refresh Options
    • Configuring ODC (Office) Connections
    • Prediction Functions and Analysis
    • DMX and MDX Queries Interfaces
    • SSAS Security Roles and Scope
    • Object Level Access and Dynamic MDX
    • Item Security and DAX Expressions
    • Dynamic Cube Security with MDX
    • OLAP Security Audits and Query Logs
    • OLAP Management with ETL/DWH
    • MDX and OLAP Operations with ETL
    • Scheduling SSAS Operations (XMLA)
    • Comparing SSAS 2012 & SSAS 2014