MS SQL Server & BI
Overview
Curriculum
Features
Overview
Microsoft SQL Server and is a relational database management system with the primary function of storing and retrieving data and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services.
Curriculum
- Database and Data Modeling
- Data Modeling using ERWIN
- ER and Relational Model
- Normalization and Demoralization
- Data Relationship
- Management and Administration
- Installation of SQL Server 2005/2008
- Quick introduction to the tools
- Creating/Altering databases and transaction logs
- Server/ Database configuration
- Backup and recovery
- Managing Tables with DDL
- Creating schemas
- Managing schemas
- Referencing schemas versus using the default schema
- Hiding schemas with synonyms
- Building tables
- Selecting appropriate SQL Server data types
- Constructing tables with CREATE TABLE
- Adding constraints
- Enforcing uniqueness using PRIMARY KEY and UNIQUE constraints
- Validating relationships using FOREIGN KEY
- Retrieving Data with Transact-SQL Stored Procedures
- Batch and stored procedure processing
- Minimizing network traffic using batches and procedures
- Stored procedure compilation and execution
- Using scalar functions
- Selecting data Using SELECT
- Developing stored procedures that extract data from multiple servers
- Retrieving data from two or more table using INNER JOIN,OUTER JOIN and CROSS
- JOIN
- Executing dynamic queries using OPENROWSET and OPENQUERY
- Combining tabular results with UNION, INTERSECT and EXCEPT
- Executing remote procedures
- Sub-query
- Producing cross tabulations using PIVOT
- CTE
- Declaring variables and parameters
- Creating and utilizing local variables
- Passing input and output parameters
- Interrogating global variables
- Calling built-in scalar functions
- Converting data using CAST and CONVERT
- Ordering data with ranking functions
- Text Manipulation
- Substring, Charindex, LEFT, LTRIM ……………
- Modifying data
- Inserting, updating and deleting data
- Ensuring data consistency with transactions and distributed transactions
- Managing concurrency with isolation levels
- SQL Server locking fundamentals
- Avoiding blocking problems with read-committed snapshot isolation
- Managing locks using hints
- Programming procedural statements
- Implementing conditions with IF…ELSE
- Looping with WHILE and GOTO
- Creating code blocks with BEGIN…END
- Debugging with PRINT
- Returning data using RETURN
- Debugging T-SQL in Management Studio
- Handling errors
- Communicating problems to the client with RAISERROR
- Intercepting errors with TRY…CATCH
- Dealing with open transactions when an exception occurs
- Producing server-side result sets
- Building and using temporary tables
- Processing rows on the server with a cursor
- Taking advantage of table variables
- Developing Views, Functions and Triggers
- Storing queries on the server
- Concealing complexity with views
- Solving business problems using multistatement table-valued functions
- Creating user-defined functions
- Calculating values with scalar functions
- Processing multiple rows returned from a table-valued function
- Taking advantage of schema binding
- Formulating triggers
- INSTEAD OF vs. AFTER triggers
- Detecting row changes using the inserted and deleted tables
- Tracking metadata changes with DDL triggers
- Auditing user access using a LOGON trigger
- Tracking data changes with the OUTPUT clause
- SQL Server Integration Services (SSIS)
- SSIS Architecture and Tools
- Extract, Transform and Load (ETL) capabilities of SSIS
- Configuring connection managers
- Adding data flow tasks to packages
- Transforming with the Data Flow Task
- Reviewing progress with data viewers
- Copying, moving and deleting files
- Sending messages through mail
- Error Handling, Logging and Transactions
- SQL Server Reporting Services (SSRS)
- SQL Server Reporting Services (SSRS) Architecture
- Creating basic reports
- Grouping and sorting data
- Building parameters into reports
- Deploying and Delivering Reports
- Creating reports with Report Builder
- SQL Server Analysis Services (SSAS)
- Building and Modifying an OLAP Cube
- Choosing between ROLAP, MOLAP and HOLAP
- Customizing Properties of Measures and Attributes.
- Creating KPI and Calculated Measures
- Action, Translation and Perspectives
- Creating Partition and Aggr egations in cube for improved performance
Features
Real Life Case Studies
Projects modeled on select use cases with implementation of diverse technology concepts
Assignments
All guided classes and courses are mandatorily followed by useful practical assignments
24x7 Expert Support
Every technical query is resolved on demand with readily available expert assistance
Instructor-led Sessions
Technical session conducted under the guidance of qualified and certified educationists
Related Course
Pega
Overview Curriculum Features Overview Our Pega
Pharmacovigilance
Overview Curriculum Features Overview Pharmacovigilance, also
Dev-Ops
Overview Curriculum Features Overview DevOps training
