BI and Microsoft SQL
Prerequisites:
Before attending this course, students must have:
· Basic knowledge of SQL Server
1. BI Introduction
ü Measurement, Metrics and Analysis
ü Fact
ü Dimensions
ü Dimension Modeling
ü ETL
ü Simple Pivot
ü Cubes introduction
ü Analytical reporting
2. Dimension Modeling with Microsoft Tools
ü Logical modeling using Visio
o Identifying Facts and Dimensions
o Shared dimension
o Time dimension
o Star Schema
o Snow flake Schema
ü Dimension Types (Type 1, Type 2, Type 3)
ü Typical BI Architecture
ü Deploying the model into Ms SQL
ü Managing change
ü ErWin Introduction
3. ETL with Microsoft Tools
ü Native ETL in MS SQL
o BCP
o Bulk Insert
o OPENQUERY
o OPENROWSET
o OPENDATABASE
ü SQL Server Integration Services (SSIS)
o SQL Server Integration Services Basics
o Exploring the need for migrating diverse data
o SSIS Architecture
o Preparing for Installation
o Utilizing basic SSIS objects
· Configuring connection managers
· Adding data flow tasks to packages
· Reviewing progress with data viewers
· Assembling tasks to perform complex data migrations
ü Operating system level tasks
o Copying, moving and deleting files
o Transferring files with the FTP task
o Reading system information with WMI query language (WQL)
ü Communicating with external sources
o Sending messages through mail
o Detecting system events with WMI
ü Processing XML
ü Extending Capabilities with Scripting
o Writing expressions
o Making properties dynamic with variables
o Building expressions in Expression Builder
o Script Task
o Extending functionality with the Script Task
o Debugging, breakpoints, watches
ü Transforming with the Data Flow Task
o Performing transforms on columns
o Converting and calculating columns
o Transforming with Character Map
o Combining and splitting data
o Merge, Union, Conditional Split
o Multicasting and converting data
o Aggregate, sort, audit and look up data
ü Manipulating row sets and BLOB data
ü Reading and writing binary files
ü Importing and exporting BLOB data
§ Redirecting error rows
§ Performing database operations
§ Executing a SQL task
§ Bulk inserting data from text files
§ Error Handling, Logging and Transactions
§ Organizing package work flow
ü Defining success, failure, completion and expression precedence constraints
§ Adapting solutions with package configurations
§ Auditing package execution results
§ Managing and securing packages
ü Storing packages in Package Store and msdb
§ Encrypting packages with passwords and user keys
4. Cubes with Microsoft Tools
ü Simple Pivot using Excel
ü SQL Server Analysis Services (SSAS)
o SQL Server Business Intelligence Development Studio
o Adding Data Source
o Adding Data view
o Adding Dimension
o Create the cube
· Measures
· Calculated members
· Actions
· KPI
· Partition
· Perspectives
· Translations
ü MDX introduction.
ü XMLA introduction.
5. Analytical reporting with Microsoft Tools
ü Simple report using Excel
ü SQL reporting Services (SSRS)
o Reporting Services Architecture
o SQL Server Business Intelligence Development Studio
o Building a report
· Simple Report
· Calculations and Formatting
· Grouping and Sorting
· Creating Drill-Down Reports
· Report Parameters
· Multi-Valued Parameters
· Creating Matrix Reports and Charts
· Managing Reporting Services
· Reporting Services Security
ü Introduction to ProClarity
ü Introduction to PPS
6. Introduction to Data Mining
At Course Completion
After completing this course, students will be able to:
· Explain the elements of SQL Server Business Intelligence.
· Input and output data, script to branch control, and configure and deploy packages.
· Create an account and work with measures.
· Scale Analysis Services.
· Use Client Reporting Tools and the web service.
· Create custom OLAP clients.
· Create queries using Analytical MDX.
· Create and execute a package using Integration Services.
|
