Microsoft Excel includes client software that allows you to work with data from On-Line Analytical Processing (OLAP) databases by creating and interacting with PivotTable and PivotChart reports. This topic explains what you need to do to set up data sources, which are stored sets of information that Excel uses to access the OLAP source data for your reports.
This topic provides reference information about:
Software and components you need
Microsoft Query To set up OLAP data sources for Excel, you use Microsoft Query, a general-purpose tool for setting up connections to external databases and creating queries to retrieve data. Microsoft Query is an optional Microsoft Office component.
An OLAP provider You also need one of the following OLAP providers:
Server databases and cube files The Excel OLAP client software supports connections to two types of OLAP data sources. If a database on an OLAP server is available on your network, you can retrieve source data from it directly. If you have an offline cube file containing OLAP data or a cube definition file, you can connect to that file and retrieve source data from it.
Return to top
To connect to OLAP data, you create a data source in Microsoft Query. A data source supplies the information necessary for Excel to connect to the OLAP database or file, including its name and location, the driver to use, and any additional information the database requires.
How an OLAP data source works A data source gives you access to all data in the OLAP database or offline cube file, and as a result, you do not have to construct a query in Microsoft Query to select the data. After you have created an OLAP data source, you can base reports on it, but you can only return the OLAP data to Excel in the form of a PivotTable or PivotChart report, not as an external data range on a worksheet.
Ways to create one You can create a data source while you are using the PivotTable and PivotChart Wizard, or you can create a data source in Microsoft Query and then use the data source when you create reports in Excel. You can specify a server database or an offline cube file when you create the data source. If you create a data source to connect directly to an offline cube file, this data source might not give you access to the original server database from which the offline cube file was created. Contact the person who supplied you with the offline cube file for information about the original source and the data it contains.
To run Microsoft Query so that you can create a data source, point to Get External Data on the Data menu, and then click New Database Query. For the procedure to create an OLAP data source, see Microsoft Query Help.
Return to top
For users who are familiar with database management and access, Excel provides several ways to create and work with your own OLAP cubes. To create your own cubes, you must have an OLAP provider, such as Microsoft SQL Server OLAP Services, that supports this capability.
Offline cube files from OLAP data From a PivotTable or PivotChart report that gets its external data from an OLAP server database, you can create your own offline cube (.cub) file containing a subset of the data from the server (or all of the data, if your disk space is sufficient). When stored on your local disk, a .cub file allows you to continue working with data when you're disconnected from the network or the server is unavailable. After you create the .cub file, you can update it when the server database changes, and you can switch the report between connection to the file and the server database.
You don't have to create a separate data source to use the offline cube file, but if you save it on a shared network location, other users can create data sources to access the file and base reports on it. Some third-party OLAP providers might not support creating offline cube files, and in that case, the commands to create the files are unavailable.
Cubes created from relational databases You can also create your own OLAP cube or offline cube file from data that you query from a relational database, such as Microsoft SQL Server. Creating an OLAP cube can make access to large amounts of relational data more efficient and can help organize the data for use in reports. You do not need an OLAP server product to create and use this type of cube.
To create this type of cube, you first set up a data source for the relational database, create a query in Microsoft Query to retrieve the data, and then run the OLAP Cube Wizard in Microsoft Query to define a structure for the data and, optionally, save an offline cube (.cub) file. After you finish this process, you have a cube definition (.oqy) file that you can open in Microsoft Excel to create a PivotTable report. You do not have to set up a separate data source for the OLAP cube.
Return to top