XL2000: Error Accessing Data in Offline OLAP Cube

In Microsoft Excel, when you use an Online Analytical Processing (OLAP) cube definition file (.oqy) to return data defined in an OLAP cube, you receive error messages similar to the following:

This problem occurs when field names for dimensions or levels defined in the OLAP cube definition file contain any of the following characters: These characters are not valid in field names for dimensions or levels in the OLAP definition file, although these are valid in Structured Query Language (SQL) queries.

To correct this problem, edit the .oqy file and rename the fields so that the names for the dimensions and levels do not include any of the characters mentioned in the "Cause" section.

To rename fields in the OLAP definition file, follow these steps:

1. Start Microsoft Query. If you do not have Microsoft Query installed, start the Setup program and install it.
2. Open the .oqy file into Microsoft Query.
   
   This starts the OLAP Cube Wizard.
3. Click Next to go to Step 2.
4. In Step 2, right-click the field name that you need to rename. On the shortcut menu, click Rename. Remove the invalid characters or rename the field.
   
   Note that the field names you type here will appear in the PivotTable report in Microsoft Excel and do not have to be the same name as the fields in the source database.
5. Click Next to go to Step 3.
6. Choose any of the options in Step 3 to create the offline OLAP cube file. Click Help for more information about these options.
7. Click Finish. In the Save As dialog box, click Save to save the .oqy file.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

You can create an OLAP definition file (.oqy) to store the query definition for an OLAP database. You can open the .oqy file in Microsoft Excel to automatically create the PivotTable report.

To create the .oqy file, follow these steps in Microsoft Query:

1. Create a query in Microsoft Query and then click Create OLAP Cube on the File menu to start the OLAP Cube Wizard.
2. Follow the instructions in Step 1 to create summary fields. Click Next to go to Step 2.
3. In Step 2, follow the instructions to create dimensions for your cube. Click Next to go to Step 3.
4. Select one of the three options in Step 3 to create your offline OLAP cube. Click Finish.
5. In the Save As dialog box, type a name for your .oqy file and click Save.

For more information about creating OLAP cubes, click Microsoft Query Help on the Help menu, type About OLAP queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.