On Accessing Data From An OLAP Server Using MS Excel

The main objective of this article is to show a step-by-step procedure for accessing data for an SQL 2000 Analysis server from Microsoft Excel. Data access can also be accomplished by VB, Visual Studio .NET 2003 IDE, and by running Multidimensional Expressions (MDX) queries against an Analysis server. Before getting into the nuts and bolts of this a few clarifications are in order as to the location and origin of the data.

Analysis Server Data

The Database

The data to be accessed is stored in the NewOlap database on the "Nechost" linked server. The "NewOlap" database data is derived from an OLTP datatbase, Northwind, on the SQL 2000 Server. The "Nechost" database and "Nechost" Analysis Server are on a Windows 2000 workstation.

The Cube: MyFirstCube

This cube was fashioned from the "Order-Details" table on the Northwind database as the Measures table, and the "Orders" and "Products" tables as the Dimensions table. The next picture shows this star schema in the "Cube Editor" window.

The Meta Data

Meta data is data about data. It really summarizes what is seen in the "Cube Editor." Additionally it also shows a Calculated Member. When data is accessed, this calculation need not be executed; the value already exists.

The Data in the Cube

This picture shows the "global picture" of the data, encompassing all Orders, all Products and all customers.

Right clicking on the Contact title allows you to drill down to the Region-based breakdown for the Contact title, as shown in the next two pictures.

You can drill down further by right clicking on the region to show measures based on Country as shown in the next picture, and so on. If you want to drill up, right click on the level you are in and pick drill up. If you see a + sign, it means you can drill down, and if it is a - sign you can drill up to a higher level.

Accessing Data from MS Excel Pivot Table and Pivot Chart Wizard

The MS Excel program used here is the MS Excel 2002 Sp1. Create a blank workbook (JExcelOlap.xls) which comes with three default worksheets saved to the default location (C:\Documents and Settings\computer user\MyDocuments). For the menu item, click on Data, then click on Pivot Table and Pivot Chart Report as shown in the next picture. In Excel 97, there was just one item titled Pivot Table Report.

This opens up the three step, Pivot Table and Pivot Chart Wizard as shown. Here you can make a choice as to the type of report you want to make, a pivot table or a pivot chart. Also you can indicate your choice as to what kind of data source you want to access. For now, just pick External Data Source and click Next. This changes your visual interface, as shown in the picture after the next one.

Clicking on Next will take you to step two of the pivot wizard as shown, where you are required to get data. Notice the information that No data fields have been retrieved.

Clicking on Get Data... opens up the dialogue where you need to choose the data source as shown in the next image. Here you have three choices, databases, queries, and Olap Cubes. In the "OLAP Cubes" tab, you may find a cube that someone might have already accessed, as in this case, and an additional list item for <New Data Source>. Click on this list item.

This brings up the next screen where you provide a name (your choice)for your data source, and the OLAP provider for the database you're trying to access. Click on Connect...,which leads you to the Multi-Dimensional Connection.

Here you can indicate the Olap server to which you are connecting in the indicated text box. Click on Next.

Now you need to choose the database as shown in this picture. The data from the NewOlap database forms the source. Choose this and click Finish.

Now the definition of the Data Source is complete, as shown in the next two pictures. There is only one cube, MyFirstCube, and this is automatically chosen.

You are back again to step two of the wizard, but notice the added information that Data Fields have been retrieved. Click on Next.

You have now reached step three of the Pivot Table and Pivot Chart Wizard.

Assembling Report Items

When you click Finish, you will be presented with place holder regions of the data starting from the third row; a Pivot Table menu bar; and a pivot table field list. You may click on the icons, MyCustomer, MyOrder, and so forth, and drag and drop them onto the place holder regions. The Pivot Filed List has all the items from MyFirstCube: the Dimensions and the Measures.

MyOrder and MyProducts items have been dropped onto the "Page Fields," while MyCustomers is dropped onto the Row fields. Only two of the four Measures, namely, Unit Price and Quantity, have been dropped into the "Data" place holder region, as shown in this picture. The picture shows a highly consolidated view of the data, for all products, all orders, and all customers.

Customizing Reporting Parameters

From the consolidated view shown above, it is possible to view data for specific values of the products, the orders, and so forth. In the next picture, from all products, only the "1" is chosen. You may click on the + sign to expand the items and - sign to collapse.

In this picture from all orders only those that start with the letter "A" are chosen.

The data for these parameters will change, as shown in this picture.

Formatting a Report and Creating a Chart

The Pivot Table Menu bar has a number of options: formatting a report, making chart, making changes to the layout of the data, and so forth. These items can be accessed from the Pivot Table Menu Bar.

For example, if formatting a report is required, clicking on the Format Report menu bar item brings up this next screen, from which various styles of reports can be chosen.

Choosing the style, second column, second row, produces this report for the selected data shown in an earlier picture.

For this picture, all orders from customers beginning with letters "A" and "B" were chosen. The data now represents the changed conditions as shown in this picture.

The data can be turned into a "chart" as well, by clicking on the Pivot Table Menu bar item Chart Wizard. Voila! You have a preformatted bar chart.

If changes are made to the "Cube," it will be necessary to "process" the cube. One of the options is to "refresh" the data. This is carried out by right clicking MyFirstCube, then clicking on Process... from teh drop down menu. Depending on the amount of data to be processed, the time to complete the processing varies. Mercifully, for this example, it is rather short, as seen in the next picture.

Summary and Conclusions

Data on an OLAP server can be accessed very easily from an Excel worksheet. The wizard driven report creation process is well automated. The report created has all the drilldown/drillup capabilities. Using the layout button in step three of the Pivot Table and Pivot Chart wizard, it should be possible to rapidly change the layout of the report, as it is supported by drag-and-drop features.

blog comments powered by Disqus