SQL Server - SSIS, SSRS, SSAS - Asked By rama chandran on 20-Apr-11 12:59 AM

Hi Friends,
         i dont know about these 3 concepts. please explain in interview point of view. urgent.

1) what is meant by SSIS? what is the use of it?
2) what is meant by SSRS? what is the use of it?
3) what is meant by SSAS? what is the use of it?


Riley K replied to rama chandran on 20-Apr-11 01:08 AM
SSIS

Microsoft SQL Server 2005 Integration Services (SSIS) is a platform for building high performance data integration solutions, including the extraction, transformation, and loading (ETL) of packages for data warehousing. SSIS is the new name assigned to the component formerly branded as Data Transformation Services (DTS).

SSIS (SQL Server Integration Services) is an upgrade of DTS (DataTransformation Services), which is a feature of the previous version ofSQL Server. SSIS packages can be created in BIDS (Business IntelligenceDevelopment Studio). These can be used to merge data from heterogeneousdata sources into SQL Server. They can also be used to populate datawarehouses, to clean and standardize data, and to automateadministrative tasks.


For More insight look into this document http://

SSRS

It creates and manages Web-enabled reporting services. It is used to create and generate reports, to retrieve data from various data sources, and to publish reports in various formats.

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. You can publish reports, schedule report processing, or access reports on-demand. Reporting Services also enables you to create ad hoc reports based on predefined models, and to interactively explore data within the model. You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. Reporting Services provides the key to your business data.

SSAS

SQL Server Analysis Services (SSAS) enables BI (Business Intelligence) workers to work on multi-dimensional data in SQL Server which can be gathered from different kinds of resources like flat files to relational databases.

SSAS (SQL Server Analysis Services) has the ability to analyze data grouped and aggregated into different formats and views like the faces of a cube.

A SSAS application adds the value of data analysis and represents the data in the format of OLAP cubes, OLAP reporting or data mining features.
Nikhil Mahajan replied to rama chandran on 20-Apr-11 01:13 AM
hi,,
the dufference b/w SSAS,SSIS,SSRS is as below 

SSAS--SQL Server 2005 Analysis Services 
SSIS--SQL Server 2005 Integration Services
SSRS--SQL Server 2005 Reporting Services


SSIS - SQL Server Integration Services, It is a Data Warehousing Tool, Developed by MicroSoft.

SSIS is One of the key features introduced in SQL Server 2005 is the new DTS (Data Transaction Services) platform. 

This product does bring in a new perspective and new thinking the way we used to work with DTS. Most of the DTS architecture has undergone dramatic changes. In this article I will walk through an step-by-step easy uploading of a given datafile into SQL Server. This new version of DTS does go beyond the ETL (Extract, Transform, Load) tools definition. You can orchestrate your code and create an workflow with the same. DTS in Yukon is more manageable, usable and more mature from its previous version. 
rama chandran replied to Riley K on 20-Apr-11 01:39 AM
Thanks for ur description.  can u explain me in a real time scenario. that is give me one example and explain the 3 concepts with this example. so that i can easily able to understand.
Jitendra Faye replied to rama chandran on 20-Apr-11 04:45 AM
SSIS stands for SQL Server Integration Services.  It is the new http://www.tek-tips.com/faqs.cfm?fid=6242# transformation standard for SQL Server 2005 and has replaced the old SQL Server Data Transformation Services.  Integration Services has been literally re-written from the ground up, so it is not the same thing as DTS for SQL Server 2000 and earlier.

 http://msdn.microsoft.com/en-us/sqlserver/bb671393.aspx


SSRS stands for SQL Server Reporting Services. It creates and manages Web-enabled reporting services. It is used to create and generate reports, to retrieve data from various data sources, and to publish reports in various formats.

http://www.accelebrate.com/sql_training/ssrs_tutorial.htm

SQL Server Analysis Services (SSAS) enables BI (Business Intelligence) workers to work on multi-dimensional data in SQL Server which can be gathered from different kinds of resources like flat files to relational databases.

SSAS (SQL Server Analysis Services) has the ability to analyze data grouped and aggregated into different formats and views like the faces of a cube.

A SSAS application adds the value of data analysis and represents the data in the format of OLAP cubes, OLAP reporting or data mining features.

Ravi S replied to rama chandran on 20-Apr-11 05:30 AM
HI

SSRS
Use the http://msdn.microsoft.com/en-us/library/ms156305(v=sql.90).aspx tool to specify service accounts, create or upgrade the report server database, modify the connection properties, set virtual directories, manage encryption keys, and configure the report server for unattended report processing and e-mail report delivery

To start the Reporting Services Configuration tool

  1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and click Reporting Services Configuration.

    The Report Server Installation Instance Selection dialog box appears so that you can select the report server instance you want to configure.

  2. In Machine Name, specify the name of the computer on which the report server instance is installed. The name of the local computer is specified by default, but you can also type the name of a remote SQL Server instance.

    If you specify a remote computer, click Find to establish a connection. The report server must be configured for remote administration in advance. For more information, see http://msdn.microsoft.com/en-us/library/ms365170(v=sql.90).aspx.

  3. In Instance Name, choose the SQL Server 2005 Reporting Services instance that you want to configure. Only SQL Server 2005 report server instances appear in the list. You cannot configure earlier versions of Reporting Services.

  4. Click Connect.

  5. To verify that you launched the tool, compare your results to the following image:

    Reporting Services Configuration Startup page

http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-1/

SSAS

SQL Server Analysis Services (SSAS) enables BI (Business Intelligence) workers to work on multi-dimensional data in SQL Server which can be gathered from different kinds of resources like flat files to relational databases.

SSAS (SQL Server Analysis Services) has the ability to analyze data grouped and aggregated into different formats and views like the faces of a cube.

A SSAS application adds the value of data analysis and represents the data in the format of OLAP cubes, OLAP reporting or data mining features.
http://clay.lenharts.net/blog/2008/04/07/ssas-beginners-guide-storage-structure/
http://www.accelebrate.com/sql_training/ssas_tutorial.htm#understanding
http://www.scribd.com/doc/35052450/SSAS-Tutorial

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software which can be used to perform a broad range of data migration tasks.This is the fastest ETL tool available now.

SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions.

Use SSIS to import one cell of an Excel file into SQL Server by following the link blow

http://www.mssqltips.com/tip.asp?tip=1930
http://www.mssqltips.com/tip.asp?tip=1930

Hope this helps you

div v replied to rama chandran on 21-Apr-11 01:26 AM
Reporting Services has a quite complex architecture. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including SOAP and WMI interfaces.

Figure 18-1 shows a simplified diagram of the main Reporting Services components that we'll be using in this chapter.

Figure 18-1: Report Server architecture

Figure 18-1: Report Server architecture

In this chapter you'll learn about these components:

  • Report Server is the core engine that drives Reporting Services.
  • Report Manager is a Web-based administrative interface for Reporting Services.
  • Report Designer is a developer tool for building complex reports.
  • Report Builder is a simplified end-user tool for building reports.
  • The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.
  • eporting Services includes two tools for creating reports:
    • Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface.
    • Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model before end users can create reports.

    We'll start our tour of Reporting Services with Report Designer. Report Designer runs inside the Business Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can also use the design tools to modify a report created with the wizard.

    Using the Report Wizard

    The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report Wizard walks you through the process in step-by-step fashion. You can make the following choices in the wizard:

    • The data source to use
    • The query to use to retrieve data
    • Whether to use a tabular or matrix layout for the report
    • How to group the retrieved data
    • What visual style to use
    • Where to deploy the finished report

    Try It!

    To create a simple report using the Report Wizard, follow these steps:

    1. Launch Business Intelligence Development Studio.
    2. Select File > New >Project.
    3. Select the Business Intelligence Projects project type.
    4. Select the Report Server Project Wizard template.
    5. Name the new report ProductReport1 and pick a convenient location to save it in.
    6. Click OK.
    7. Read the first page of the Report Wizard and click Next.
    8. Name the new data source AdventureWorksDS.
    9. Click the Edit button.
    10. Log on to your test server.
    11. Select the AdventureWorks database.
    12. Click OK.
    13. Click the Credentials button.
    14. Select Use Windows Authentication.
    15. Click OK.
    16. Check the Make This a Shared Data Source checkbox. This will make this particular data source available to other Reporting Services applications in the future.
    17. Click Next.
    18. Click the Query Builder button.
    19. If the full query designer interface does not display by default, click the query designer toolbar button at the far left end of the toolbar. Figure 18-2 shows the full query designer interface.
    SSAS
    • What Is Microsoft Business Intelligence?
      • Define Business Intelligence
      • Understand the Cube Structure
      • Deploy and View a Sample Cube
      • View a Cube by using Excel
      • View a Cube by using SQL Reporting Services
    • OLAP Modeling
      • Understand Basic OLAP Modeling (star schema)
      • Understand Dimensional Modeling (stars and snowflakes)
      • Understand Measure (fact) and Cube Modeling
      • Model with SQL Server Business Intelligence Development Studio (BIDS)
    • Using SSAS in BIDS
      • Understand the Development Environment
      • Create Data Sources
      • Create Data Source Views
      • Create Cubes by using the Cube Wizard
    • Intermediate SSAS
      • Learn how to Create Key Performance Indicators (KPIs)
      • Discover how to Create Perspectives
      • See how to Create Translations for Cubes and Dimensions
      • Review the three SSAS Action Object Types: Regular, Drillthrough, and Reporting
    • Advanced SSAS
      • Work with Multiple Fact Tables and the Dimension Usage Subtab in BIDS
      • Explore Advanced Dimension Types
      • Learn how to use the Business Intelligence Wizard
      • Understand Writeback in Dimensions
      • Review Semi-Additive Measures in OLAP Cubes
    • Cube Storage and Aggregation
      • View Aggregation Designs
      • Customize Aggregation Designs
      • Implement Proactive Caching
      • Use Relational and SSAS Partitions
      • Customize Cube and Dimension Processing
    • Introduction to MDX Queries
      • Understand Basic MDX Syntax
      • Use the MDX Query Editor in SSMS
      • Understand Common MDX Functions and Tasks
      • Review MDX Functions New to SSAS 2008
    • MDX Expressions
      • Understand the Calculation Subtab
      • Learn how to add Calculated Members
      • Explore how to add MDX Script Commands
      • Discover how to add .NET Assemblies
    • Introduction to Data Mining
      • Understand Data Mining Concepts
      • Review the Algorithms that SSAS Includes
      • Consider Data Mining Clients
      • Understand Mining Structure Processing
    • SSAS Administration
      • Implement SSAS Security
      • Implement XMLA Scripts in SSMS
      • Deploy and Synchronize Databases
      • Understand SSAS Database Backup and Restore
    • Advanced Administration and Optimization
      • Implement SSIS to Manage SSAS Databases
      • Explore Clustering
      • Explore Scalability Options
      • Understand Performance Tuning and Optimization
    • Introduction to SSAS Clients
      • Design Reports using Reporting Services
      • Design Reports by using Report Builder
      • Implement Excel 2007 Pivot Tables and Charts
      • Use Excel 2007 as a Data Mining Client
      • Review Microsoft Office SharePoint Server 2007
    SSIS
    • Use BIDS (Business Intelligence Development Studio)
    • Orchestrate control flow and data flow
    • Work with variables and configurations
    • Debug your flows and handle errors
    • Set up sophisticated logging
    • Extend SSIS through custom code
    • Deploy and manage SSIS packages