Introducing Business Intelligence in Microsoft SQL Server 2008

Course 10056:
Introducing Business Intelligence in Microsoft SQL Server 2008

BI is to deliver the targeted info to right people at right time.Companies use BI to remain competitive within their respective market.
MS uses SQL Server 2008 BI uses SQL server,integration,analysis and reporting services.
SSIS transforms the data and moves it to a central data warehouse. It is responsible for turning the raw data into meaningful information that will eventually be consumed by the user via business reports.
SQL Server Analysis Services or 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.
SQL Server Reporting Services or 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.

ETL
ETL stands for Extract, Transform and Load. Data is extracted from source systems such as OLTP databases and loaded into a destination database such as a decision support system or a data warehouse. During the movement of this data, you can optionally transform the data. Data transformations allow you to change the format or structure of the data. SSIS is an ETL tool that is provided by SQL Server 2008.
In typical ETL solutions using SSIS, staging tables may be used to break down the movement of data from source systems into the data warehouse. Staging tables acts as useful intermediary storage locations in that data can be moved out of the source systems and straight into the staging tables to reduce the time and resources that are placed upon the source system. From the staging table, you can then transform the data as it is loaded into the data warehouse without impacting the resources on the source systems.


OLAP:
OLAP refers to the multidimensional analysis of data. SSAS consumes information from a data warehouse to store data within an OLAP database that is stored in cubes. Cubes can store detailed data, however, its power is to create preaggregated data that is persistent within the cube. You can arrange the aggregated data so that it can be intersected by dimensions that provide contextual information for the aggregated data. Dimension can include contextual information about customers, employees or orders. This allows analysis to be performed far more efficiently than could be performed against the same data in a transactional system.
OLAP databases can store one or more cubes within it.


Data Mining:
Data mining provides an exciting aspect to SSAS in that it can use mathematical algorithms to analyse the data in either a cube or a relational table. This analysis can involve trend analysis, data classification or clustering and sequence analysis. Data mining allows you to explore your data and find out patterns that may have not been immediately evident.

Dashboards and Scorecards:
Key performance indicators (KPIs) are a feature of SSAS and are stored in an OLAP database but can be exposed through client tools such as SSRS, Microsoft Excel and Microsoft Office SharePoint Services. The ability to provide key business metrics through these visual indicators is a very powerful feature that enables you to build scorecards and digital dashboards through the client tools. Storing the KPIs on SSAS helps you to manage the KPIs centrally.





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.