Microsoft BI Box is a magical box for high-impact query, data integration, reporting and analysis server that provides services to the other components of the Business Intelligence Suite such as Answers, Dashboards, Data Mining, Reporting, and Analytic Applications.
What is BI (Business intelligence)? – In single line “Transform Raw Data into Decisions Making Report”. In computer world Business Intelligence (BI) refers to computer-based techniques used in collecting, analyzing and presenting business data, such as sales revenue by products and/or departments or associated costs and incomes. Generally used for product management, sales, human resource and finance departments.
Currently we have several BI Tools; these tools/technologies provide historical, current, and predictive views of business operations. Common functions of Business Intelligence technologies are reporting, online analytical processing, analytics, data mining, business performance management, benchmarking, and predictive analytics.
An Overview of Business Intelligence and Data Warehouse: Business intelligence (BI) helps enterprises/companies to gain past information from historical data and formulate strategic initiatives for the future growth. The historical data are stored as an electronic repository, which is called a data warehouse.
A data warehouse is a system of records (a business intelligence gathering system) that takes data from a company’s operational databases (Data from Heterogeneous Data Stores) and other data sources and transforms it into a structure conducive to business analysis and reporting. Finally, the data is made available to the end user for analysis, querying, and reporting.
A data warehouse system has organized & optimized storage of historical records gives the business an intelligence storage system to understand the business. Business analysis can be done in reactive mode or predictive mode. Reactive mode business analysis (also known as business analytics) is a function where business analysts and other business users investigate the records and identify patterns and trends, and make business decisions to improve their business processes. Predictive mode analysis (also known as predictive analytics or data mining) is done using mathematical models to predict future trends on the system of records. The general approach to storing business data in a dimensional model and providing quick answers by slicing and dicing the business data is known as On Line Analytical Processing (OLAP). OLAP systems are architected in different ways. The most common types are MOLAP (Multidimensional OLAP), ROLAP (Relational OLAP), and HOLAP (Hybrid OLAP).
Extract, Transform, and Load Systems: Commonly expressed as ETL, extract, transform, and load refers to a set of services that facilitate the extraction, transformation, and loading of the various types of source data (for example, relational, semi-structured, and unstructured) into OLAP cubes or data mining structures. SQL Server 2008 includes a sophisticated set of tools to accomplish the ETL processes associated with the initial loading of data into cubes as well as to process subsequent incremental inserts of data into cubes, updates to data in cubes, and deletions of data from cubes.
Report Processing Systems: Most BI solutions use more than one type of reporting client because of the different needs of the various users who need to interact with the cube data. An important part of planning any BI solution is to carefully consider all possible reporting tools. A common production mistake is to under-represent the various user populations or to clump them together when a more thorough segmentation would reveal very different reporting needs for each population.
MS SQL BI – Core Component
Based on Microsoft SQL Server database platform, Microsoft has developed or acquired technology which has allowed it to offer its clients a full and comprehensive product suite for Business Intelligence software market.
SQL Server started out as a database product but has grown to include additional capabilities that put core BI concepts into action.
SQL Server Database Engine: The core program used to create standard relational databases, including data warehouses and data marts. If you see the above image, Database Engine is the base for all other BI component.
SQL Server Reporting Services (SSRS): Software for creating reports based on Microsoft (and nearly all other) data sources. A web based product, it can deliver interactive report functionality as well as display refreshed reports. The web interface also allows users to subscribe, view and manage their reports.
SQL Server Integration Services (SSIS): Software for connecting to a multitude of data sources, transforming the data into a single useful format, and loading it into a Microsoft SQL Server database — all using the ETL (Extract, Transform, and Load) process.
SQL Server Analysis Services (SSAS): A Microsoft version of Online Analytical Processing that stores massive amounts of data in a special database called a Cube for very quick real-time analysis.
sqlcheatsheet 