This course covers SSIS, SSRS and SSAS from Basics to Advanced Levels
SSIS- SQL Server Integration Services
SSAS - SQL Server Analysis Services
SSRS- SQL Server Reporting Services
SSIS:
SSIS makes it easy to create robust and complex solutions for extracting, Transforming and Loading the data.
These will help you to design, create, deploy and manage packages that address everyday business requirements.
SSIS can be used for data migration as well as data integration purpose.
Sections in SSIS Designer:
Control Flow:
Work flow of task execution / order of tasks execution.
Controlling the flow of SSIS package execution.
Data Flow: ETL
It contains extracting the data from one more sources with the help of source adapters, performing transformations and loading the data into one more destinations with the help of destination adapters.
Event Handlers:
It allows you to implement error handling and debugging of control flow tasks in SSIS packages.
Package Explorer:
It allows you to browse the package contents. Package contains tasks, precedence constraints, log providers, variables, event handlers and connection managers.
Parameters:
Parameters are the replacement for configurations that are in the current version of SSIS. It allows you to assign values to properties within packages at the time of package execution.
Connection Manager:
It maintains all connections used by the different tasks and adapters in the package.
A connection manager is a logical representation of a connection
SSRS:
It’s a reporting platform; it’s used to generate the reports from any data source (any RDBMS).Provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports
Report:
Report is nothing but a text based file to present the business data.
Report file extension .rdl( Report definition language)
It’s a XML based file.
We have three types of reports.
• Enterprise reports
• Ad-hoc reports.
• Embedded reports.
Enterprise reports:
Complete business standards and rules followed.
Formatting and other requirements collected.
Report developers will prepare these reports with complexity.
Shared among the business users in the enterprise.
Standard data analysis and decision making purpose.
Ad-hoc reports:
No business standard and no rules followed.
No formatted and other requirements.
Non-technical business managers will prepare these reports.
Not shared among business users in the enterprise.
Ad-hoc data analysis purpose.
Embedded Reports:
Embedding the report to any third party application such as Java,.Net etc..
Data Source:
Data source means a connection string to a particular db system. In ssrs we have two types of data sources.
1. Ordinary Data source
2. Shared Data Source
Ordinary Data source:
Data source connection string maintain in particular report. It’s not reusable in the product.
Shared Data Source:
Data source connection string maintains in connection file (rds).
It can be reusable for multiple reports in the application.
In SSAS and SSIS2008 we have only shared Data source.
Dataset:
Dataset means in-memory result set structure prepared based on query or stored procedure.
Dataset populated with actual rows at the time of report execution.
A report requires at least one dataset.
A report can use multiple datasets.
SSAS :
SSAS full form is SQL Server Analysis Services.
Multi-dimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data.
SSAS is an analysis service platform, which can be used to create and manage the analysis database.
SSAS Contains Preaggregated data , internally creates analysis database, and once the analysis database is ready, it can be used for many purposes
It has 2 variants Multidimensional and Tabular.
One or more cubes can be presented in the analysis database.
