Create an OLAP query file to connect to an OLAP database - Help and How-to

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Printer-Friendly Version

Create an OLAP query file to connect to an OLAP database

Applies to: Microsoft Office Excel 2003

You can also use Office database connection (.odc) files to connect to OLAP databases.

Create a new data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) that uses the name you want for the OLAP query (.oqy) file as the name of the data source.
How?

1. Under the Data menu, point to Import External Data, and then click New Database Query.

2. In the Choose Data Source dialog box, on the OLAP Cubes tab, click New Data Source, and then click OK.

3. Choose a name for the data source, select an OLAP provider (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.), and click Connect.

4. Complete the Multi-Dimensional Connection wizard.
In the Choose Data Source dialog box, click Options on the OLAP Cubes tab to view the name of the folder where the .oqy file was created. The default location is your user profile folder under Application Data\Microsoft\Queries.

Notes

When you use the Open command on the Microsoft Excel File menu to open an .oqy file, Excel displays a worksheet with a blank PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) for the OLAP data. You can use this report to display the data you want to see, and then save it in an Excel workbook file.
You can also use the OLAP Cube Wizard in Microsoft Query to create an .oqy file that connects to a cube file or defines a cube (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.) that is created from queried data. For more information, see Microsoft Query Help.