Accessing OLAP using ASP.NET (Page 1 of 4 )
This is a quick and easy introduction to OLAP, its architecture, its installation and discusses how to access the OLAP information/data through ASP.NET using VB.NET with a demonstration of a simple web application.
Support files available here.
What is OLAP?
Databases store information about business transactions, plus other data such as employee records. Those types of systems are called online transaction processing (OLTP) databases. OLTP data contains a wealth of information that can help you make informed decisions about your business. The process of analyzing your data for that type of information, and the data that results, are collectively called business intelligence.
You can spend a lot of time and money trying to extract business intelligence information from your database. The time and expense involved in retrieving answers (queried information) from databases means that a lot of business intelligence information often goes unused. The reason: most operational databases are designed to store your data, not to help you analyze it. The solution: an online analytical processing (OLAP) database, a specialized database designed to help you extract business intelligence information from your data in a structured manner.
Microsoft® SQL Server™ 2000 Analysis Services is a high-performance online analytical processing (OLAP) tool for performing data analysis and data mining.
Introduction to OLAP Architecture
The following figure depicts some of the tables and relations that exist in the Northwind sample database provided by Microsoft® SQL Server™ 2000:
The following figure depicts how part of the Northwind database could look when converted to OLAP (Start Schema):
The central table in the schema is the fact table. Fact tables contain numeric data, such as zip codes, and additive data such as the total costs of freight for all beverages.
By themselves, numeric facts do not have much meaning. For instance, the number 206 by itself does not mean much. However, it takes on more meaning if you know that it represents an area code or the number of dishwashers sold yesterday. In a star schema, dimension tables contain the descriptive text that gives meaning to the numbers. Keep in mind that most analyses involve time, which makes time itself a key dimension.
The facts in a dimension are called members. By design, OLAP databases group the related facts in a member into hierarchies whenever the underlying data supports that type of structure. For example, the Time dimension in the preceding figure contains the following hierarchy:
- Year
- Quarter
- Month
- Order Date
Hierarchies use traditional parent/child relationships. For instance, Quarter is a child of Year, Month is a child of Quarter, and so on. If a child contains data that your OLAP system can aggregate, its parent level contains those aggregated sums. Some systems call those aggregated sums rollups. Whenever you drill up or down through your data, you navigate through those hierarchies as shown in the following figure:
The following figure gives an overview on cube (the main multidimensional structure in OLAP).
The above cube shows that it has 3 dimensions (excluding measures) namely, Source, Route and Time. All the 3 dimensions have several members (underneath them) grouped into 2 or more levels together linked in the form of a hierarchy. The measure is nothing but an aggregation of a particular column from the perspective view of dimension(s). According to the above figure, it has two measures i.e., Number of packages and Last transaction.
Note: The above explanation is just a brief introduction to the overall architecture on OLAP. For further information, refer to SQL Server 2000 online documentation or MSDN.
Next: Technical Terms in OLAP >>
More MS SQL Server Articles
More By Jagadish Chaterjee
