To help make business intelligence more accessible to all levels of customers, Microsoft has seriously committed itself to forming a complete business intelligence platform: SQL Server 2005 business intelligence
Almost every SQL Server application has some level of reporting. It may form something as simple as a query or database view providing the total number of new orders or Web site hits. Yet, many companies' business intelligence only happens in Microsoft Office or static reports, which are printed out on a regular basis and require one poor soul to convert them into something sharable.
If you know SQL Server 2000, you know the names of some Microsoft business intelligence technologies. But be warned -- they are the same in
name only. The latest release of the BI platform can be characterized as the first that is enterprise class. The SQL Server product has made strides in making the analytical side of the product more scalable. With server-side processing for Analysis Services, the customer can expand the reach of reporting and move from looking at the business in a rear-view mirror to being able to keep a finger on the pulse of daily activity.
In SQL Server 2005, business intelligence and reporting applications are given a whole new look and feel. There's a new Visual Studio-like development tool called the Business Intelligence Development Studio, which companies can use to do everything from create Analysis Services cubes to report packs and data mining applications. Moreover, developers will enjoy using the new BI features natively as part of the Visual Studio product (you will find database projects and solutions as part of the product).
Let's take a look at exactly what's changed from SQL Server 2000 to 2005.
|Component||Analysis Services 2000||Analysis Services 2005|
|Calculation||Server and client side||Server side|
|Caching||Server and client side||Server side|
|Schema||Star schema||Data source view (DSV) flexible schema mapping|
|Metadata||Repository in Access or SQL Server||SQL Server database, also called Analysis Services database|
|Data sources||Limited sources||DSV provides flexible data source mapping|
Comparison of Analysis Services components
From an architectural point of view, Analysis Services is a client/server application that uses a specialized Web services protocol: XML for Analysis. By using Web services, you gain greater scalability, richer metadata delivery and a convenient, client-agnostic transport model. You could use a Linux client or even a Pocket PC as a client. The combination of Web services, proactive caching and the introduction of the Unified Dimensional Model (UDM) creates an interesting story for consideration. Let's take a look at the key new concepts for Analysis Services.
New, key concepts for Analysis Services
Unified Dimensional Model (UDM):
UDM is Microsoft's term for the technology that bridges the gap between relational and data warehousing approaches to reporting. The UDM is a series of XML schemas and mappings that relate all business intelligence information into a single version of what some call the truth. XML schemas and objects are all part of the XML for Analysis specification.
Data source view (DSV):
Central to the success of the UDM is the ability to create a unified model of data; the data source view provides a semantic layer. It creates an XML file that contains the relevant data source information in a highly flexible manner. You can rename table attributes with user-friendly names and not affect the source. Moreover, you can create custom calculations without changing the source data. In fact, using the Business Intelligence wizard, you can automatically generate logical inferences about the facts and dimensions in a data source based on the cardinality found in databases. Data sources can even reference other objects, such as the SQL Server Integration Services (SSIS) package. Don't confuse the DSV with a cube. They are not the same.
A cache is built out of the data and aggregated data upon first invocation; the cache answers subsequent calls for the data. Because calculations are cached, the performance and scalability of analytic applications increase. When designing a UDM, you set the acceptable latency level. For systems with significant user exploration, proactive caching provides a means of obtaining greater performance. Proactive caching also has an effect on Analysis Services Data Storage.
Key performance indicator (KPI):
Although it isn't a Microsoft-only term, KPI is a new technology that relies heavily on Multidimensional Expression (MDX) to create a site-oriented facility for checking the status of business success factors. For instance, in a call center you might use call waiting time as a measure of business performance. By having a reporting model that allows management to quickly scan the performance of call centers, you can make changes during business hours quickly and competitively. If you are using Microsoft Business Scorecard Manager 2005, you will love the tight integration with Analysis Services KPIs.
The most interesting and compelling feature of Analysis Services 2005 is the UDM. Microsoft has moved away from requiring a common data warehouse schema, meaning star or snowflake. Traditional analytical applications using OLAP databases offered excellent query performance, analytical richness and, for those versed in analytical applications, an easy-to-use model.
OLAP databases suffered from a couple of handicaps: Data was often old, and the cube couldn't be reprocessed quickly enough. Additionally, complex schemas were difficult to deal with and, from a management perspective, these databases represented another resource commitment on an already constrained IT infrastructure. The goal of the UDM is simply to overcome these limitations and bring to bear the best of both relational and OLAP reporting.
About the author: Eric Brown is a senior consultant in the Business Intelligence national practice for Quilogy Inc. He works on emerging technologies and SQL Server 2005. He was previously a member of the SQL Server Product Team at Microsoft, for which he ran Yukon readiness during a three-year stint. He has presented at Microsoft TechEd and he has written several papers as well as a column in SQL Server Magazine. He has also authored his own SQL Server 2005 book, available now: SQL Server 2005 Distilled. To ask Brown your own business intelligence questions, click here.
This was first published in June 2007