ENABLED BUSINESS SOLUTIONS

Enabled Business Solutions

Need Help ?




Courses Delivered

Course Outline

MODULE 1 : Installing and Configuring MS SQL Server SSAS

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the data warehouse server. Participants will setup a new installation of analysis services from scratch and change server properties based on the use case.

The module covers new features in SSAS and typical architecture of MS SQL Server Business Intelligence projects.

MODULE 2 : Design a data warehouse solution

This module covers the basics of OLAP cube design. It introduces concepts like Dimensions and Facts, Kimball Dimensional modelling and Star and snowflake schema. In this module, participants will review best practices for data warehouse design.

We introduce the process of incremental loads and staging table upload best practices, the basic architecture of Analysis services and the different storage models like MOLAP, ROLAP and HOLAP.

MODULE 3 : Working with Datasource and Datasource views

Datasource and Datasource views act as the foundation for a scalable cube design. In the module, we cover how to fetch data from heterogeneous sources. How to accept change without considerable rewrite and how to enrich the cube from source data are also covered in this module.

Improving cube processing and indexing strategies are also covered in this module.

MODULE 4 : Create a Cube

The Module covers the first step in creating a MOLAP cube by using the Datasource and views from the previous module. It covers the different steps involved in setting a basic cube that contains only default entities and needs to be refined by participants in remaining modules. It covers how to create a new cube from scratch as well as importing and modifying an existing cube. Data validation and error checking will also be covered in this module.

MODULE 5 : Dimensions- Hierarchies , Mapping, Processing

The dimensions module covers the best practices in working with cube and defining appropriate dimensions. It covers in depth the different types of Mappings between the dimension and the facts as well as the creation of hierarchies. The module then goes on to cover the different ways the dimensions can be processed. The module ends with coverage on how to handle special dimensions like bridge tables and late arriving dimensions.

MODULE 6 : Measures- Fact less tables, Calculated Measures

Facts play a critical role in the accuracy of the data from the BI project. Arriving at the correct fact, understanding semi-additive measure and fact-less fact table are covered in this module. Enriching the cube with measure groups and calculated measure are also covered in this module.

MODULE 7 : Calculations , MDX Basics

In this module, we cover the basic syntax of MDX queries and introduce some common keywords used when querying the cube. We also cover how to create calculated measures, Named Sets and scripts in this module. The module then proceeds to cover some common calculations using the lag, lead, rank, ancestor, parent and child syntax for MDX queries. The participants will then be querying different datasets that will be run against the cube and validate the same.

MODULE 8 : Partitions, Perspectives , Translations

Here we continue to explore the different tabs available within visual studio when creating a cube. We explore the use and benefits of Partitions in cube processing, performance and data management. The module also covers the implementation of Perspectives as a means to abstract the cube by business segment and how to enforce translations that show cube metadata in regional language format by the client geography.

MODULE 9 : Actions , Aggregations , KPIs

This module covers the design of aggregates to improve performance and explains the advantages and disadvantages of using them. It also covers the implementation of usage based optimization. The module then proceeds to explain the use of actions and covers Reporting actions and drill through actions. This will conclude with the introduction of Key performance indicators or KPIs and how they are implemented within the cube and viewed within Excel or Reporting Services.

MODULE 10 : MDX

Here the participants are introduced to additional MDX querying concepts and a new set of functions to query and create more complicated reports. The participants are familiarized with common functions used in accounting, reporting, cube navigation and custom coding that are required as part of a BI project. We introduce the date dimension and navigate a time series using Time-based functions as well.

MODULE 11 : Securing the Cube, permissions

Securing the package will conclude the developer aspect of the cube with how to implement permissions at dimensions, Facts and rows within SQL Server Analysis Services. Users will create different permissions for a variety of business roles and understand how to layer the security setup when working with the cube.

MODULE 12 : Deploying the cube

Developers and DBAs will learn how to prepare a cube for fresh deployment as well as incremental deployments in this module. Developers will go over the different ways that the cube can be deployed to the server and the advantages and disadvantages of each.

MODULE 13 : Modify and Automate Cube maintenance

Participants are introduced to best practices when working with Cube and how to ensure the cube is always in good health. Modify Server properties to keep up with changing business needs as well as automating cube data load and processing; routine maintenance of the environment, are covered in this module.

MODULE 14 : Administer the cube, Backups , Profiling , Partitions

This Module cover concepts like warm caching, proactive caching, Taking backups, securing backups, managing and maintaining partitioning etc. It introduces the DBA to XMLA or XML for Analysis services which are used by administrators to manage SSAS installations. Additionally, it covers common mistakes made by DBA when working SSAS.

MODULE 15 : Performance Tuning the Cube

This module covers common performance issues that are encountered in SSAS. It explains how to improve cube processing times and query performance for ad hoc queries.

MODULE 16 : Introduction to mining structures

The last module shows participants how to extend SSAS to perform data mining and forecasting using a few of the inbuilt Mining algorithms available within SQL Server Analysis Services. It covers the basic tenants of data mining and how it can be used in different business scenarios such as market basket analysis etc.

Course Outline

MODULE 1 : Installing and Configuring MS SQL Server SSRS

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the datawarehouse server. Participants will setup a new installation of analysis services from scratch and change server properties based on the use case.

The module covers new features in SSRS and typical architecture of MS SQL Server Business Intelligence projects.

MODULE 2 : Datasets , Tabular and Matrix Reports

This module covers the creation and implementation of Datasets and their use within Tabular and Matrix Reports. It covers fetching data from OLTP and OLAP systems and common features used to create reports. The module will give the participants hands on experience creating basic reports which acts as the foundation for the remaining module.

MODULE 3 : Drill Down, Drill Through and Sub Reports

Participants are guided on the different types of reports and when and where to use them in this module. They will also be introduced to creating groups and nested hierarchies in this module. The module then moves on to cover embedding reports within each other. In this module, the participants will create dashboards and navigation structures to guide users within the site.

MODULE 4 : Dynamic Reports and List Reports

In this module, the participants are introduced to linked datasets and how they are used to create dependant parameters. Users are shown how to create parameters at the report level to dynamically change the behaviour of the report as needed. The module also covers the use of list control to create brochure and catalogue type reports as well.

MODULE 5 : Working with Charts

This module will cover in extensive detail the use of the different charting controls available within SSRS. It will explain the use and behaviour of each control and the pros and cons of using them within reports. Participants will be shown examples of how choosing the correct chart control increases the readability of the report.

MODULE 6 : Deploying Reports

With a basic set of report created, participants are guided on how to deploy reports to SSRS servers in this module. We cover the three different methods available and explain the advantages and disadvantages of them. We cover automated deployments and how to deploy reports in a scaled out environment.

MODULE 7 : Performance Tuning Reports

This module covers how to improve load time and rendering times for reports. It helps the participants to go over basic steps of troubleshooting performance issues and explains where to correct the problems. It also introduces tools available to the participants in order to improve execution times.

MODULE 8 : Best Practices for building Reports

Reports are all about presentation and in this module we explain key design concepts that participants can use to ensure reports meet the business need and are able to answer a variety of user queries efficiently. We cover creating reports that are consistent and reusable. The module will also cover how to make reports scalable as well as best practices around security and performance.

MODULE 9 : Working with Maps

This module explains how to work with the unique chart control called Map, we explain when it can be used and how to use it to replace older tabular reports. We also explain how to use this control to depict GPS data or regional sales data in new ways. The Map module will also explain how to work with Geometry and Geography data types within MS SQL Server.

MODULE 10 : Working with Report Manager

In this module, we explain how to implement security within Reporting Services and the use of the SSRS API to work with Report Server. The module then explains the different features available in Report Manager to administer Reporting Services. We cover Server properties and Report properties in this module.

MODULE 11 : Working with Report Builder

In this module, we explain how to modify existing reports or build new reports within the Reporting Services URL using the Report Builder functionality. We cover how to use the same features as available in Visual Studio and user workspaces in this Module.

MODULE 12 : Integrating SSRS with ASP.Net

In this module, we cover how to embed SSRS Reports into visual studio aspx pages using Report viewer control and explain how to programmatically control SSRS Report within the website. We explain how to pass values to hidden parameters and the use of RDLC reports.

MODULE 13 : Scripting within Reports

In this module, we explain how to use scripting languages to improve the usability or functionality of the reports within report manger. This includes customizing the report for different layout hiding and displaying report parts, implementing complicated calculations that are not readily available within the expressions of SSRS.

MODULE 14 : Subscriptions , Snapshots and Caches

In the module, we cover topics like creating Snapshots to improve report performance. We explain the advantages and disadvantages of the same. We also cover creation of scheduled and data driven subscriptions and common business cases where they are implemented. We then cover the use of caches to speed up data fetch and fine tune report execution times.

Course Outline

MODULE 1 : What is ETL?

This module covers the installation, minimum software and hardware requirements, disk and memory sizing of the SQL Server integration services. Participants will setup a new installation of integration services from scratch and change server properties based on the use case.

The module covers new features in SSIS and typical architecture of MS SQL Server Business Intelligence projects. It establishes the basic principles that govern ETL processes.

MODULE 2 : Navigating Visual Studio

This module familiarizes the participants with the Visual Studio interface used for building and working with SSIS packages. It shows users where they can find the different options that are required for creating packages. The participants will learn how to create a package from scratch or modify an existing package. This module covers new features available in the version of SQL Server being used as well as best practices on how to code and build an MSBI solution.

MODULE 3 : Control Flow tasks

This module introduces the participants to some of the common control flow tasks they will encounter in SSIS and how to use and configure them. Tasks such as Execute SQL, Bulk Insert, Execute Process task etc. are covered in this module. Participants are shown how to achieve different program flows using the same tasks or subset of tasks. New components such as Change Data capture are also introduced in this module.

MODULE 4 : Advanced Control Flow tasks

In this module, the participants are introduced to linked datasets and how they are used to create dependant parameters. Users are shown how to create parameters at the report level to dynamically change the behaviour of the report as needed. The module also covers the use of list control to create brochure and catalogue type reports as well.

MODULE 5 : Data Flow tasks

This module introduces working with data in SSIS. It explains the common tasks used to transform and enrich the data while moving from a source to destination. Participants will be shown to create summary tables perform joins and other standard ETL operations like sorting, converting data and performing calculations on the data.

MODULE 6 : Advanced Data flow tasks

Extending on what was covered in the previous module this section covers advanced Data flow tasks such as pivot, unpivot, Import and export data as well as DQS cleansing and CDC splitter. It also will explain redirect rows feature for handling issues with the data and best practices implementing data flow task for fast ETL of very large number of rows. We also cover all the different data sources and destinations.

MODULE 7 : Debugging SSIS Packages

Debugging the SSIS package is a very important skill set and this module explains the different options available for troubleshooting and fixes logic issues as well as functionality of the package. We cover breakpoint at the package level and task level as well as control flow error handling and failure constraints. We introduce debugging code in SSMS and Visual Studio as well as watch and locals ad monitoring performance counters associated with SSIS.

MODULE 8 : Performance Tuning SSIS Packages

In this module, we show participants how to fine tune the ETL process and some tips and tricks for speeding up the ETL process. This module covers best practices when loading very large datasets as well as efficient ways to implement SSIS packages that are reusable and fault tolerant. Activities like batching data, memory management and threading are covered in this module. We also cover some fundamentals in T-SQL coding to ensure faster data manipulation using SSIS.

MODULE 9 : Deploying SSIS Packages and Scheduling

With the SSIS package ready we cover deployment options and their behavior on High availability systems in this module. We cover the pros and cons of the different deployment options as well as the deployment manifest file and its use in SSIS.

MODULE 10 : Securing the package

With the package deployed we cover how to ensure the package and the connection strings and other metadata within it are secure from misuse. We cover encrypting the package as well as permissions issues when scheduling the package using SQL Server Agent including the concept of credentials and proxies within MS SQL Server. We also cover common issues when working with cloud based servers in Windows azure.

MODULE 11 : Package Store

As part of deploying packages in SQL 2012 and above, we introduce the participants to the package store functionality and explain the architecture of the feature. We cover the benefits and disadvantages of the package store with respect to packages. We explain the difference between the package deployment model and Project deployment model.

MODULE 12 : Error handling SSIS packages

In this module, we recap some core concepts and explain how errors can be handled and managed in the Control, Data, and precedence tasks as needed. We cover how to perform conditional logic based on a different stage of the package and the use of checkpoint files etc.

MODULE 13 : Datawarehouse tasks

In this module, we introduce the participants to some additional tasks used when working with SQL Server Analysis Services such as the SSAS processing task as well as Slowly Changing Dimensions tasks. We also show participants how to use Analysis Services Execute DDL task to create new partitions etc.

MODULE 14 : Administration tasks

Here we cover in detail some of the more commonly used Control flow tasks used by DBAs as part of regular maintenance for the server. These tools are useful for developers to be able to automate a lot of activities that are performed on Dev servers as well. We cover shrinking, backups, rebuild indexed and more as well as alerts.

MODULE 15 : Parameters and Package Configuration

We prepare the package for deployment using Parameters and package Configuration files in this module. We explain the best practices and pros and cons of each of these tasks. Parameters and their knowledge are essential for the DBA and Developer to ensure a smooth deployment and we explain how to create generic package configuration which can be seamlessly used in DEV, TEST and production environments.

MODULE 16 : Data Quality Services and Master Data Services

Data quality Services and master data Services are additional features introduced or extended in the latest version of MS SQL Server. In this module, we cover a few common scenarios where they can be used to streamline the business process along with a quick demo.

Course Outline

MODULE 1 : Installing SQL Server , Baseline , Hardware sizing

In this module we cover aspects of SQL Server Database administration such as Installing SQL Server and configuring it to best utilize the hardware, we also cover base line of the hardware and implementing best practices such as TempDB optimization, PBM, RAID Configurations and Disk and Memory Sizing for database servers. We will also cover aspects such as different architectures followed when designing a database. Using SQLIOSIM etc will also be covered in this module.

MODULE 2 : Performance Monitoring Tools

In this module we explore the different tools available to monitor performance of the sql server instance.

SQL Server Activity Monitor, PerfMon, Data Collectors, RML, PAL, Profiler, DMVs, Extended Events, DRC

MODULE 3 : SQL Internals

In this module we cover a quick introduction to SQL Server internals and the main components and behaviours of SQL OS with regard to IO, CPU and MEMORY, we explain the roles played by the execution engine and the storage engine as well as the way they affect performance based on recovery model, isolation levels and server settings.

MODULE 4 : Fixing CPU issues

In this module we cover common CPU issues faced by DBAs and Developers when working with SQL Server, we explore how to identify the correct root cause using the tools mentioned in module 2 and explore how to fix the root cause. Some key topics that will be covered in this module include.

Parallelism, Hyper Threading and Multi Core, NUMA, Wait and Queues, Scheduler, Thread Counts, indexes, Resource Governor, Server Side trace etc.

MODULE 5 : Fixing Memory Issues

In this Module we cover memory architecture of SQL server, covering in detail the processes and way memory is allocated and de-allocated, we explore the buffer pool as well as the difference between 32 and 64 bit architecture. We work with DMVs used to identify memory pressure and performance counter used to measure memory utilization. We explore the use of indexes in improving memory utilization and some database design tips and tricks that can improve memory footprint such as filtered indexes, compression, partitioning, in memory structures, columnstore indexes, rewriting the query etc.

MODULE 6 : Fixing IO Issues

In this module we cover the most common root cause of SQL Server performance issues, the Disk IO. We explore files and file groups , the internals of mdf and ldf file management , VLFs , Auto growth and Shrink , impact of TDE on disk IO, Partitioning, Storage Engine Internals , Locking and Blocking, Latches , Trouble shooting tempdb bottlenecks, improving write throughput , performance counters to measure disk latency and more.

MODULE 7 : Locks and Latches

In this module we explore the execution engine and learn how to understand the execution plan we cover locking, blocking and deadlocks. In addition we explore isolation levels and recovery models impacts on data durability, consistency and performance. We will also explore row versioning, snapshots and locking hints.

MODULE 8 : Indexing

In this module we cover internals of indexes, B-Trees , how to identify the right index for the job, how to implement indexed views, reusing indexes, working with statistics, the different types of indexes can when they are most useful, fragmentation in indexes , Scalar UDF , CLR assemblies , Plan Guides and Hints, Joins and Join Algorithms.

MODULE 9 : Everything else

In this module we cover some other not so common ways to approach performance issues, we explore how developers and DBA can achieve performance benefits by using hints , rewriting the query , identify problem queries , Parameter sniffing , SQL plan recompile , dynamic queries , VAS , other common performance issues , QnA .

Course Outline

MODULE 1 : Installing and Configuring MSSQL 2014

This module will cover a high level outline of the new features in MS SQL Server 2014 and the installation and configuration of SQL Server instance.

  • Installing SQL Server
  • Pre and post install checklist
  • Best Practices when installing SQL server
  • Hardware Sizing SQL Server
  • New Features list in SQL 2014
  • Deprecated Features list in SQL 2014
  • Configuring Server Properties
  • Migrations and Upgrades of SQL Server

MODULE 2 : Improving Loads Times and Read Performance

In this module we explore some of the new features in SQL Server that can be used to improve concurrency, read and write performance. We explore features that were newly introduced and compare their performance with older features and evaluate the advantages and disadvantages of each.

  • Delayed Durability
  • Optimistic Concurrency
  • Non Clustered Column Store Indexes
  • Clustered Column Store Indexes
  • Online Partition Rebuild
  • Indexing Strategies

MODULE 3 : Introduction to Always ON and Availability groups

In this Module we cover the implementation and configuration of Always ON Availability Groups. We explore the prerequisites and key steps involved in setting up the HADR Solution and compare it with other solutions currently available.

  • Setting Up Always ON
  • Configuring the database for AlwaysON
  • Setting up Listener
  • Creating the Availability Groups
  • Performing Failover
  • Cloud AG
  • Monitoring the AG

MODULE 4 : Other New features in SQL 2014

In this module we explore some additional features available in MS SQL Server such as Buffer pool Extensions and Resource Governor Enhancements and Managed Lock priority

  • Architecture of Buffer Pool Extensions
  • Improving performance using SSD
  • Resource Governor setup to throttle IO
  • Implementing Managed Lock priority and its impact during Index Rebuild and Partition Switch
  • Cardinality Estimates
  • Cloud Backups
  • Cloud Database files
  • Filestream management
  • Azure deployments

MODULE 5 : Implementing In-Memory OLTP

In this module we cover the key concepts around In-memory OLTP. We explore the trends that resulted in the creation of in Memory OLTP database platforms and how it impacts the current OLTP landscape. We explore the impact of this feature with regard to memory.

  • Advantages and Disadvantages of In-Memory OLTP
  • When to use In-Memory OLTP
  • Implementing In-Memory OLTP
  • Comparing performance between In-Memory OLTP and traditional tables

MODULE 6 : More on In-Memory OLTP

In this module we explore the architecture of In-Memory OLTP and how it impacts other features of MS SQL Server. We cover how indexes behave in In-Memory OLTP as opposed to other traditional Indexes and also the storage and Durability aspects of In-Memory OLTP.

  • Indexes on In-Memory tables
  • Durability in In-Memory tables
  • Storage models in In-Memory tables
  • Natively Complied Procedures in SQL Server
  • Monitoring and trouble shooting In-Memory OLTP

Course Outline

MODULE 1 : Setting up Azure AD and granting permissions

This module will cover a creating a Azure AD and adding users and granting permissions within Azure. We also cover the layout and common features within the old and new portal.

  • Adding Domain
  • Adding Users
  • Setting up DNS
  • Granting Permissions
  • Setting up Multifactor Authentication

MODULE 2 : Setting up and configuring Azure Virtual Machines

In this module we cover the different service tiers for virtual machines and how to decide which configuration is best suited to a task

  • Pricing VMs
  • Deploying Read made images vs Custom Images
  • Scaling and configuring VMs
  • Troubleshooting VMs
  • Deploying to VM
  • Assiging NIC cards and static IPs for Virtual machines

MODULE 3 : Understanding Azure pricing model

In this Module we cover how to monitor and manage costs within Azure by taking advantage of different features or services within the Azure stack. We cover the pricing model in detail.

  • Understanding the Azure Pricing model
  • Working with the pricing calculator
  • Streamlining costs by effectively managing resources
  • Tips to manage Azure using PowerShell

MODULE 4 : Exploring the new azure portal

The new azure portal introduces a number of new services and we walk thru the most common services that will be used as part of deploying IT infrastructure.

  • Exploring new features in Azure Portal
  • Migrating to V12 database using Azure Portal
  • Navigating the New portal
  • Common features in New portal
  • Assigning admins
  • Transfering subscriptions
  • Resource Groups

MODULE 5 : Working with Azure databases

In this module we cover working with traditional relational database like MS SQL Server which are provided within Azure as PaaS

  • Working with Azure database
  • Understanding limitations and design practices for Azure database
  • Deciding when to use Azure DB
  • Deploying databases to Azure DB
  • Migrating databases to Azure DB
  • Troubleshooting Azure database
  • Securing Azure database
  • HADR for Azure database

MODULE 6 : Working with Web and Worker Roles

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • What is a web role?
  • What is a worker role?
  • Configuring a web roles & worker role
  • Setting up IIS
  • Debugging Code in web role

MODULE 7 : Setting up and configuring Azure Automation

In this module we cover how to automate and schedule routine administrations tasks within Windows Azure.

  • What is Automation?
  • Difference between Automation and Scheduler
  • Using PowerShell scripts
  • Runbooks
  • Testing Automation

MODULE 8 : Working with BLOBs, Queues, Tables and Files

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • Using Files
  • Using Queues
  • Using Tables
  • Using BLOBS
  • Scalability and limits of Azure Storage media
  • Securing Azure Storage

MODULE 8 : Working with BLOBs, Queues, Tables and Files

In this module we explore the differences between a web role and a worker role. we cover the aspects needs to deploy websites and web services into Azure.

  • Using Files
  • Using Queues
  • Using Tables
  • Using BLOBS
  • Scalability and limits of Azure Storage media
  • Securing Azure Storage

MODULE 9 : Deploying an application to Azure

We combine everything we have learned into this module to create and deploy a secure azure website with a database backend.

  • Deploying a website
  • Deploying a Database
  • Linked resources
  • TP deployment
  • Publish profile
  • Web Matrix
  • Azure Data Sync

MODULE 10 : Monitoring Azure applications

In this module we cover how to monitor the entire Azue stack and setup alerts for performance and cost.

  • Azure Management console
  • Setting up alerts
  • Different types of Alerts
  • Elastic Scaling

MODULE 11 : Scaling Azure Applications with traffic manager

In this module we explain traffic management using Azure services to allow routing of user connections effectively so that scaled VMs or databases are used evenly.

  • Using traffic manager
  • Different modes of traffic manager
  • Scalability achieved using traffic manager
  • Load balancing traffic manager
  • Round Robin

MODULE 12 : Setting up Virtual networks and VPNs

In this module we establish a hybrid cloud environment where secure data transfer can happen rom cloud to on premise and vice versa.

  • Creating credentials
  • Certificates
  • Configuring firewalls and VPNs

MODULE 13 : Additional Services

In this module we cover other services that might be useful in atypical IT stacks

  • Azure CDN
  • Azure Search
  • Document DB
  • Visual Studio
  • Remote App