Using Ado Md and Office Web Components to Generate Thin Client Charts and Pivottables from Olap Cubes

Only available on StudyMode
  • Topic: Microsoft SQL Server, Microsoft Analysis Services, Online analytical processing
  • Pages : 5 (1733 words )
  • Download(s) : 872
  • Published : July 15, 2009
Open Document
Text Preview
Using ADO MD and Office Web Components to Generate Thin Client Charts and PivotTables from OLAP Cubes When faced with a problem of browser incompatibility for embedded components, most programmers are eager to roll up their sleeves and begin implementing server side components with similar functionality. This article is an effort to describe a way of processing the Chart and PivotTable Components on the server side to present a thin client interface for OLAP cubes. I will discuss techniques that connect Office Web Components (OWC) to an OLAP server and issue simple MDX queries via ADO MD (ActiveX Data Objects for Multidimensional Data) to generate Charts and PivotTables that can be exported as images embedded in a HTML page. Sample Application

We shall be using the following technologies in our sample application: Active Server Pages
Microsoft OLAP
Office Web Components
ADO MD and MDX
The above screen-shot is from the sample application that I will use to illustrate the various techniques throughout this article. This application uses a multidimensional data cube called Sales on an MS OLAP server to do the data analysis of a fictitious toy manufacturing company called Fun School. The Sales cube is made up of three Dimensions, Product, Customers, Time, and three Measures, Sale, Cost and Quantity. We will fetch data from the OLAP cube to generate a PivotTable view and a graph in a web server's memory and then generate graphical images based on the PivotTable's view using the OWC components for a browser based data analysis. For those of you who are new to the OLAP and multidimensional database analysis concepts, the following couple of sections will give you a brief overview of these technologies. Since an in-depth discussion on these technologies is beyond the scope of this article, it is recommended to check out the URLs mentioned at end of this article for further reference. About MS OLAP

Microsoft Online Analytical Processing (OLAP) services is based on and closely coupled to the relational databases, and at the same time, Microsoft OLAP Services is actually a multidimensional information system, where all information is modeled in terms of OLAP structures and not relational structures. These OLAP structures come in very handy because many important analyses are difficult or sometimes impossible to phrase in SQL using the tabular structure. Even when information is presented in relational terms, an understanding of OLAP structure is helpful for multidimensional analysis and querying. The Microsoft SQL Server OLAP Services Architecture includes server side components such as the online analytical processing Server for building cubes. The client also contains some components, the most important of which is the PivotTable Service.This client-side component allows the user to access multidimensional data. PivotTable Service connects to the OLAP Server on the server, which makes calls to the multidimensional data, the data is then returned to the client and the PivotTable Service allows the user to browse the data. Cubes

A cube defines a set of related dimensions that form an n–dimensional grid. The data point at the intersection of dimensions is where the information is, in the cube structure. This is usually a numeric value that describes the measure of the intersecting dimensions. Each point in this grid is uniquely identified by a set of coordinates whose values come from the component dimensions. The cube is the fundamental metadata object in OLE DB for OLAP, on which ADO MD is built. Dimensions

A dimension in a cube structure can be defined as the set of values for an individual axis in the cube. It identifies what the axis is trying to represent. Members
Each discrete value in a dimension is called the Member. A member represents what you are trying to evaluate. Cells
The intersection point of two or more dimensions forms a cell that stores the...
tracking img