SSRS 2012 Introduction to SQL Server Reporting Services

Scenarios for Using SQL Server Reporting Services 2012

1. SQL Server Reporting Services 2008 is installed as part of the SQL Server setup. 
2. When Reporting Services is selected in the features selection screen, the Reporting Services Configuration screen provides three options that can be selected to determine how Reporting Services can be accessed. This can be useful if Reporting Services is to be used by internal users, external users or if you wish to integrate Reporting Services with Microsoft Office SharePoint Server.
3. By selecting the Install in Native Mode default configuration option, Reporting Services is installed with all of the default options and is operational once the installation is complete. 
4. This is an ideal choice if the Reporting Services solution is to be used by internal users only and you do not wish to change the default settings.
5. Users will access the reports by typing the URL in Internet Explorer that goes to http://ServerName/reports.
6. The installation allows you to integrate Reporting Services with Microsoft Office SharePoint Server. Assuming the Reporting Services pack is already installed on the SharePoint Server, Reporting Services will be installed in SharePoint integrated mode whereby the user will access the reports from the SharePoint site rather than the Reporting Services home page. Reporting Services can also use the features available to SharePoint Server as well.
7. For external access, it is best practice to select the Install, but do not configure the report server option in the Reporting Services Configuration screen. 
8. In this scenario, you will have to use Reporting Services Configuration Manager to manually set the values for the home page of the Report Server Web site, Report Manager Web site and determine how the Report Server databases are created. 
9. Furthermore, you will have to modify configuration files to secure the traffic that is sent between the Report Server and the external clients.

1. Reporting Services Configuration Manager
2. Report Designer
3. Report Builder
4. Report Manager
5. SQL Server Management Studio
6. Command prompt utilities
7. Use the Report Wizard

1. For post-installation configuration, the Reporting Services Configuration tool provides easier management, particularly for those moving from SQL Server 2000 Reporting Services.
2. Introduced in SQL Server 2005 2008 Reporting Services, the notable change to this version, apart from the look, is the fact that there is only one service account that needs to be configured now that the dependency on IIS is removed. 
3. Previous versions required a Windows service account and a Web service account to be configured. Below is a summary of options:

• Server Node. Provides the ability to stop and start the Reporting Services.
• Service Account. Provides the ability to change the security context under which the Reporting Services run.
• Web Service URL. Configures the URL access to the report server by creating a URL reservation. A URL reservation defines the virtual directory, IP address, TCP port and optionally a SSL certificate. HTTP.sys uses the URL reservation to direct users requests and allow access to the URL.
• Database. Provides the ability to change the location of the report server database and to change the credentials for connecting to the report server database.
• Report Manager. Creates an URL reservation to access the Report Manager Web site.
• E-mail Settings. Provides the ability to integrate with e-mail by defining a server address and an e-mail address that has the ability to send e-mail messages from the report server.
• Execution Account. Provides the ability to specify an account to use against report data sources that do not require credentials or to access remote servers.
• Encryption Keys. Allows the management of the keys that are used to encrypt the sensitive data that is stored in the Reporting Services database. There is the ability to back up and restore the encryption keys, change encryption keys and delete the encrypted content within the database.
• Scale-out Deployment. Provides the ability to manage multiple instances of Reporting Services that share the same ReportServer database such as a clustered instance. SQL Server 2005 Reporting Services referred to scale-out deployment as Initialization.

1. SSRS has introduced a new designer that allows business users to connect to data sources and produce richly formatted reports that provide highly visual information in a user-friendly interface. 
2. The report designer is found within Business Intelligence Development Studio and consists of the following core elements:

• Data pane. Provides the ability to define data sources and data sets. Items from the Data pane can be dragged onto the designer canvass.
• Toolbox. Provides the ability to add data regions and other report items to the designer canvass.
• Report canvass designer. Located in the centre of the Report Designer application, it allows you to develop the report in its entirety.
• Grouping pane. A grouping pane is located at the bottom of the Report Designer. This provides a quick way of managing groups of data in the canvas designer, without the need for entering into dialog boxes.
• Properties pane. A context sensitive pane that changes as items on the designer canvass are selected, providing rich formatting and report body options. The Properties pane provides a vast array of options than can vastly improve the visualisation of the reports

1. Report Builder is an application that empowers power users to create their own ad-hoc reports. 
2. Report Builder is available in two versions:

• Report Builder version 1 is a ClickOnce .NET application that allows the creation of unplanned reports. For power users to use the Report Builder version 1, a report model must be created that provides an abstraction layer to the underlying data in a data source. It is also a prerequisite to be able to use the Report Builder version 1. As a result, the report model has a separate project template in Business Intelligence Development Studio.
• Report Builder version 2 has now been introduced and is not constrained to the requirements of a report model. Report Builder version 2 has the same layout as the report designer that is available in Business Intelligence Development Studio. The application is encased within an Office 2007 application with ribbons to provide shortcut to common tasks. With Report Builder version 2, you can make use of data sources directly, providing a more flexible tool than its previous version.

1. Report Manager is the Web site that is used to host the reports that are deployed to the report server. 
2. End users will browse to a predefined URL that is the home page of Reporting Services.
3. From here, users can browse and export reports.
4. If they have the permission, they may also be able to subscribe to reports so that they can be delivered via e-mail.
5. Report Manager also acts as an administrative Web site for the reports.
6.  Report Server administrators have the same capabilities as the end users, however they can manage report execution, report history and security as well.

1. SQL Server Management Studio provides administrative capabilities for the report server. 
2. Within the Reporting Services server type, a report server administrator can create shared schedules, create system and item-level security roles and view the jobs that are running on the report server.
3. You can also manage the report server databases within the database engine, including backing up the database.

• RSConfig.exe. can be used to perform post installation tasks on the report server. The options available within RSConfig.exe are available within the Reporting Services Configuration Manager.
• RSKeymgmt.exe. allows you to back up and restore the report server encryption keys. This feature is also available within the Reporting Services Configuration Manager. Encryption keys are used to protect the sensitive data that is stored in the report server, such as data source credentials and passwords. This uses a symmetric key to encrypt and decrypt the sensitive data. The report server service is used to create and unlock the key. If you change the identity of the Report Server service or if you migrate the report server to a new computer, the private key of the Report Server service will no longer be able to unlock the symmetric key. To restore access to the symmetric key, the symmetric key must be re-encrypted by using the private key of the new Report Server service identity. Restoring the symmetric key is the process by which the re-encryption occurs.  

• RS Utility. The RS Utility is a script host that you can use to perform scripted operations. Use this tool to run Microsoft Visual Basic scripts that copy data between report server databases, publish reports, create items in a report server database and more.

1. Reporting Services 2008/2012 provides a wizard that will enable you to create simple reports. 
2. On completing the wizard, the report is readily available to be deployed to the report server.
3. Alternatively, you can use the report output from the wizard as the basis to edit the report to provide additional functionality that may not be available in the wizard.
4. The wizard will present you with options to define a data source and data set. You can select either the table or the matrix data region within the wizard. 
5. Therefore, if you wish to create a chart or gauge report, the Report Wizard is not appropriate.
6. Beyond these initial options, you then specify the columns to be included within the data region and define the formatting of the report that is similar to the Report Wizard in Microsoft Access.

1. Data sources - Data sets -Data regions Core SQL Server Reporting Services Components exist that are important for creating reports in SSRS 2012 and 2008. 
2. Here we have described this three main components for making better reports

Best Practices Implementing Packages and Control Flow in SQL Server Integration Services 2008

1. Create an SSIS package that performs a single operation to simplify troubleshooting of packages.
2. Create package templates that contain common SSIS package components.
3. Define data sources within Solution Explorer that need to be available for multiple packages.
4. Create connection managers in an SSIS package so that the connection information is embedded within a package.
5. Identify the Control Flow tasks that are required to complete operations of the package.
6. Identify the Control Flow tasks that will provide error control within a package.
7. Use precedence constraints to control the flow of task execution within a package.
8. Use the Data Profiling task to gain familiarity with new data or identify quality issues with existing data.
9. Use the Bulk Insert task to move data from a text file to a SQL Server table when no error checking or transformations are required.
10. Use the Data Flow task for data loads that requires error checking and transformations.
11. Use the Script task to create custom Control Flow tasks that cannot be met by existing tasks.
12. Use the Execute Package task to control the flow of multiple packages using precedence constraints.
13. Use Sequence containers to group related tasks within a single container that can have package properties set from one object.
14. Use For Loop containers when you know the amount of times the iteration of a loop needs to occur.
15. Use the Foreach Loop when you are required to iterate through objects but do not know how many times the loop should iterate.
16. Use variables as the platform for passing the values between tasks when you are required to pass values from one Control Flow task to another.
17. Ensure that variables are set at the correct scope to operate correctly.

1. ETL 
2. Dataware house
3. OLAP
4. Data Mining
5. Dashboards
6. Scorecards
7. Reporting

• Organizations host Online Transactional Processing (OLTP) systems for holding data that runs the business, such as sales databases or order entry systems. 
• Performing reporting on such systems would be detrimental to the running of the business as this reporting could cause performance problems that at the worst, may prevent a business from earning revenue from the OLTP systems that are set up for that purpose.
•  Some businesses use the same SQL Server system for both transactional and reporting purposes. 
• The more optimal approach is to separate the OLTP systems so that it can be optimised to perform modifications to the data, and create a reporting systems known as decision support systems, so that it can be optimised to read data. 
• You can then use the data from the transactional systems as the basis for populating the decision support system in a controlled manner. 
• When performing this task, you will come across the following terminology 

1. SSIS – SQL Server Integration Services
2. SSAS – SQL Server Analysis Services
3. SSRS- SQL Server Reporting Services
4. Microsoft Office SharePoint
5. Microsoft Excel

1. SSIS is an ETL tool that is responsible for moving data around the business and transforming or changing the data.
2. There are a wide range of transformations that can be performed by SSIS. These can involve changing the format of the data or converting data from one data type to another.
3. You can also perform lookup operation to add data to a destination based on a lookup from a third data source.
4.  For example, based on postal code, look for suburbs from the U.S. Postal Service.
5. While SSIS can be used to populate a data warehouse, it can also be used to perform ad hoc data transfers between different systems.
6.  It can also be used to move objects between different instances of SQL Server. The first interaction that people have with SSIS is when they use the Import/Export wizard.
7. Not only will this provide the ability to define data sources and destination, but you can also perform transformations within the wizard.
8. The wizard also prompts you to save the detail of the wizard as an SSIS package that can be edited later within SSIS. 

1. SSAS provides OLAP databases and data mining capabilities. As the need for data increases for business analysis within an organization, many BI IT professionals are seeing the value that SSAS can bring to providing data analysis in an efficient manner within a BI infrastructure.
2. It also provides the ability to add calculated values to a cube known as calculated members. This use Multidimension Expression (MDX) language to create calculated members that will add value to the cube.
3. MDX can also be used to define KPIs that provide high visualisation of key business metrics. Another component of SSAS is data mining.
4. This is perhaps one of the most underused technologies within the entire SQL Server BI stack.
5. The power to find patterns and trends in the data can help companies make commercial decisions about the future.
6. Excel 2007 now has a data mining add-in that can extend the reach of this technology to the end user.
7. Understanding how data mining works will be an important aspect of Analysis Services as it grows in popularity. 

1. SSRS provides you with the capabilities to design, deploy, manage and deliver end-user reports. The design capabilities is available to all users of the organisation as you can use Business Intelligence Development Studio to create reports.
2.  Report Designer can be made available to the end user to create their own reports.
3.  Report Manager is a Web-based front-end that enables users to view reports and allow report administrators to manage reports. You can also automate the delivery of reports. 

1. Although Microsoft Office SharePoint Services (MOSS) is not a part of the SQL Server BI stack, it would be remiss not to acknowledge the growing importance that MOSS has in delivering BI to the end user.
2.  MOSS can act as a central repository of business information within an organization. This can include the storage of documents and images.
3. You can also set up calendars and newsgroups that an organization can use to exchange information.
4.  You can now integrate SSRS with MOSS during the installation of Reporting Services. This will enable you to host and store reports within MOSS.
5.  Furthermore, it was announced by Microsoft in January 2009, that Microsoft Office Performance Point Server would be integrated into SharePoint Services.
6.  This would provide greater capabilities for providing digital dashboards and scorecards within MOSS. 

1. Microsoft Excel is a popular client tool for BI solutions. You can export SSRS reports to Excel. However, its power is evident in the way that it integrates with SSAS.
2.  You can connect to SSAS cubes by using Excel and create pivot table reports.
3. Furthermore, you can also download a number of Excel add-ins such as the Analysis Services Add-in for Excel and the Data Mining Add-in for Excel.
4.  These add-ins provide more sophisticated integration with Analysis Services than the pivot table alone.

Introduction to SSRS SSAS SSIS in  Business Intelligence in SQL Server 2012 or 2008. 

1. In Order to make good Business Intelligence solutions It is important to understand the key concepts of BI solutions and its various terminologies used in BI Development.
2. Business Intelligence or BI is the delivery of targeted information to the right people at the right time.
3. Today many Organizations are increasingly making use of BI in order to remain competitive within their respective markets and make good products and services.
4. Microsoft SQL Server 2008 has various technologies that facilitates the delivery of information to a wide range of business users within a organisation.
5. All this technologies are embodied in the SQL Server 2008 BI stack using SQL Server Integration Services SSIS, SQL Server Analysis Services SSAS and SQL Server Reporting Services SSRS to deliver BI solutions for any organistion or company

1.SQL Server Integration Services or SSIS

1. It is an Extract Transform and Load tool (ETL) Tool that is responsible for moving data around the business and transforming or changing the data. 
2. Within a business organisation or company , data can be stored on a wide range of systems including Microsoft Access, SQL Server and other third-party relational database management systems. 
3. Typically, within a BI solution, SSIS moves the data to a central database known as a data warehouse while at the same time transforming or cleansing the data, so it is presented in a standardised format and represents one version of the data that is stored within the data warehouse. 
4. SSIS is very important foundation for a BI solution and while the business users will not see or understand the work performed by SSIS, it is responsible for turning the raw data into meaningful information that will eventually be consumed by the user via business reports which are displayed using SSRS. 

2.SQL Server Analysis Services or SSAS 

1. SSAS can provide a single point of access for the organization’s Business Intelligence information. 
2. It provides OLAP (Online Analytical Processing Services) databases and data mining capabilities. 
3. In the context of an OLAP database, SSAS will typically take the data that is stored in the data warehouse and create a multidimensional cube. 
4. The cube not only stores the data from the data warehouse, it can also preaggregate this data and store it permanently within the cube. 
5. This can substantially improve the query performance of both complex business questions and reports that require a large amount of aggregated data. 
6. You can also use the data mining capabilities of SSAS to search for trends and patterns within the data that may not be immediately evident. 

3.SQL Server Reporting Services or SSRS 

1. SSRS is the reporting component of the SQL Server BI stack that provides the front-end reports to business users on which they can make decisions. 
2. You can configure SSRS to query data within an SSAS cube and present the data in a range of reporting formats such as tables, charts and gauges. 
3. SSRS also has the capability to create reports from other data sources such as SQL Server databases or third-party database systems. 
4. Furthermore, you can control how the reports are managed and executed. You can also control how the reports are delivered ensuring that the business users receive the data when they require it. 
5. A BI solution should provide an accurate and consolidated insight into the activities of all areas of a business. 
6. SQL Server 2008 provides the technologies that allows you to create a cost-effective BI solution that is available to all users of the business.
7.  To that end, many BI solutions are managed as projects to ensure that the successful implementation of Business Intelligence within an organization is ensured.