On Accessing Data From An OLAP Server Using MS Excel - Accessing Data from MS Excel Pivot Table and Pivot Chart Wizard
(Page 4 of 6 )
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.
Next: Assembling Report Items >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
