Introduction to SSRS SSAS SSIS in Business Intelligence in SQL Server 2012 or 2008.
- 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.
- Business Intelligence or BI is the delivery of targeted information to the right people at the right time.
- Today many Organizations are increasingly making use of BI in order to remain competitive within their respective markets and make good products and services.
- Microsoft SQL Server 2008 has various technologies that facilitates the delivery of information to a wide range of business users within a organisation.
- 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
- 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.
- 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.
- 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.
- 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
- SSAS can provide a single point of access for the organization’s Business Intelligence information.
- It provides OLAP (Online Analytical Processing Services) databases and data mining capabilities.
- 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.
- The cube not only stores the data from the data warehouse, it can also preaggregate this data and store it permanently within the cube.
- This can substantially improve the query performance of both complex business questions and reports that require a large amount of aggregated data.
- 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
- 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.
- 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.
- SSRS also has the capability to create reports from other data sources such as SQL Server databases or third-party database systems.
- 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.
- A BI solution should provide an accurate and consolidated insight into the activities of all areas of a business.
- 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.
- To that end, many BI solutions are managed as projects to ensure that the successful implementation of Business Intelligence within an organization is ensured.