Show AllMicrosoft Query allows you to create and work with data from On-Line Analytical Processing (OLAP) databases by setting up data sources, creating OLAP cubes from relational data, and saving OLAP cube definition files. This topic explains how to use Microsoft Query to prepare OLAP data for use in Microsoft Excel PivotTable reports and PivotChart reports.
This topic provides information about the following:
Creating an OLAP cube from records in a query
OLAP is a way to organize large business databases. OLAP data is organized by a database administrator to fit the way users analyze and manage it, so less time and effort is needed to create your reports.
Levels of detail OLAP databases organize data by level of detail, using the same kinds of categories that you use when you analyze the data. For example, a database of worldwide sales information for a company's products might have separate fields identifying the country, region, city, and site of each sale. In an OLAP database, this geographical information could be organized from lowest to highest level of detail, like this:
Levels in a geography dimension
This organization makes it easy both to display high-level sales summaries, such as sales totals across an entire country or region, and to display the details for sites where sales are particularly strong or weak. In a Microsoft Excel PivotTable or PivotChart report, you can display summarized data at different levels and, when you view the levels that contain greater detail, display only the items of interest.
Dimensions and cubes A set of levels that encompasses one aspect of the data, such as geographic areas, is called a dimension. Similarly, information about when sales were made can be organized in a time dimension with levels for year, quarter, month, and day. OLAP databases are called cubes because they combine several dimensions, such as time, geography, and products, with summarized data, such as sales or inventory figures. An OLAP database calculates a summarized value for every intersection of every level of every dimension, such as the total sales for each day, month, and year of each site, region, and country and each product and product line.
Return to top
OLAP providers To connect to an OLAP database, you need an OLAP provider that supports that particular type of OLAP database. Microsoft Office includes OLAP provider software for the Microsoft OLAP server product, Microsoft SQL Server OLAP Services. For other products that provide OLAP data and services, you need to install additional drivers and client software. For information about using a third-party OLAP provider, consult your system administrator or the vendor for your OLAP product. Learn about installing OLAP providers.
Data sources OLAP client software includes a data source driver for your OLAP database. Office includes a driver, named Microsoft OLE DB Provider for OLAP Services, to access databases that were created by using Microsoft SQL Server OLAP Services. When you create cubes or offline cube files from within Microsoft Query, you also create data sources to access these cubes.
When you run Microsoft Query to connect to an OLAP database or offline cube file, you set up a new data source just as you do for any other kind of data, except that you use the OLAP Cubes tab in the Choose Data Source dialog box to name the data source, specify the data source driver for your OLAP provider, and connect to the database. How to set up a data source.
Returning data to Excel After you set up an OLAP data source and select it for use, you don't create a query to select tables and fields as you do for other kinds of data. The data source gives you access to all data in the OLAP cube. The data fields are immediately returned to Excel as a PivotTable report.
Return to top
By using Microsoft Query and the OLAP client software that's provided with Microsoft Office, you can 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 accessing 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 OLAP cube.
Retrieving the data for the cube To create your own cube, you first set up a data source for the relational database, create a query to retrieve the data, and then run the OLAP Cube Wizard to define a cube structure for the queried data and, optionally, save an offline cube file.
Creating a cube that's built in memory In the OLAP Cube Wizard, you can create a temporary cube that's rebuilt in memory whenever you open a report that's based on the cube. See Help in the OLAP Cube Wizard for information about when to create this type of cube. Learn about running the wizard to create an OLAP cube.
Creating an offline cube file The OLAP Cube Wizard also lets you create an offline cube (.cub) file that you can store on your local disk or a network share. When stored on your local disk, you can use a .cub file to continue working with the data when you're disconnected from the network or the server is unavailable. If you store the file on a network share, you and other users can set up data sources to access the offline cube file and base reports on it. Learn about running the wizard to create an offline cube file.
Return to top
OLAP data sources When you use the OLAP Cubes tab of the Choose Data Source dialog box to set up a data source, Microsoft Query saves an .oqy file in your user profile folder under Application Data\Microsoft\Queries. In addition to specifying the data source when you create a PivotTable or PivotChart report in Excel, you can open the .oqy file directly from Excel to create a PivotTable report. If the .oqy file connects to an OLAP server database or to an offline cube file on a network share, you can make the .oqy file available to other users, who can then open it to create reports without having to set up their own data sources.
OLAP Cube Wizard cubes After the final step in the OLAP Cube Wizard, you are prompted to save a cube definition file with the extension .oqy. This file contains the information that Excel needs to either build the cube in memory or connect to an offline cube file, if you created one. Instead of returning the records in your query to Microsoft Excel, you can open this file in Microsoft Excel to create a PivotTable report. You do not have to set up a separate data source for the OLAP cube, although you can set up a separate data source for an offline cube file if you want to connect directly to the cube file in order to create reports.
Saving the .oqy file is not the same as saving a copy of the query for the relational database records that you used to create the OLAP cube. If you want to keep a copy of that query so that you can add data to your cube later on, you must save the query in a database query (.dqy) file.
Return to top