Overview of Business Intelligence (BI)
Overview of Business Intelligence (BI)
By : Kasim Wirama, MCDBA, MVP SQL Server
This is introduction article to Business Intelligence in SQL Server 2005. Microsoft began including its BI products from SQL Server 7.0 release. Business Intelligence new features and exciting tools are provided in SQL Server 2005 such as Analysis Service, OLTP database engine itself, reporting services, data mining, and SQL Server Integration Services as ETL toolset that unites from heterogeneous data sources into centralized data source so that all departments in an organization get consistent information insight on their enterprise data asset.
What is BI actually? From Microsoft vision to SQL Server 2005, it is a way of storing and presenting key information (not data) to your organisation, about question or information enquires to accurately and timely manner. BI can present data into information insight based on past data and forecast data correctly in future. BI also presents reports in many kinds of report formats such as windows application, web application and BI clients tools (excel and reporting services).
Typical BI configuration involves staging server between OLTP systems and BI system. Staging server can any OLTP database, but SQL Server is preferred options because it is optimized with use of Analysis Services and Integration Services. Microsoft BI implements Ralph Kimball OLAP approach (http://www.ralphkimball.com), namely denormalization to provide fast query result.
Here are Microsoft BI tools :
· SQL Server 2005
· SQL Server Analysis Service (SSAS)
· SQL Server Reporting Service (SSRS)
· SQL Server Integration Service (SSIS)
· Data mining
· Excel
· Microsoft Office SharePoint Server 2007 or SharePoint Portal Server 2003.
· Visio for OLAP modelling.
· Performance Point Server 2007 (PPS 2007) that integrates ProClarity and Business ScoreCard Manager.
BI uses some query languages different from OLTP query language, for SSAS it uses MDX (multi dimensional expression), DMX (data mining extensions) for data mining, and XMLA (XML for Analysis) for SSAS administrative related tasks.
You can begin exploration with SSAS by opening SSAS sample project in VS 2005 located in c:\program files\microsoft sql server\90\tools\samples\Adventure works analysis projects\enterprise\adventure works dw.sln. you can deploy it with right clicking to the project. once it gets deployed, you can use pivot table and chart on Excel to do some analysis as end-user analysis query tool, or you can use VS 2005/BIDS for doing some testing with browsing in Browse tab in selected cube.
Some business problems that BI can address is
- query responds slowly
- disparate data sources.
- invalid/inconsistent report data among departments in an organization.
- too much data for analysing pattern to get conclusion quickly.
Microsoft SQL Server 2005 versions affect available features in BI. These features are available only in Enterprise edition such as proactive caching, data mining tuning, writeback cube, text mining support and advanced SSIS transformations.