Check the date or time field in the source database If you don't see the levels you expect in the date or time dimensions in source data from the OLAP Cube Wizard in Microsoft Query, check the date or time field from the underlying relational database that supplied the data for the cube. The database may store dates and times as text instead of a date or time format that the wizard can recognize. If you suspect this is the case, consult the database administrator for the relational database to verify and correct the date or time format for the field.
Check the top level of the dimension When you add date and time fields as lower levels of a dimension, the OLAP Cube Wizard does not automatically break the data out into year/quarter/month/week and hour/minute/second levels. The wizard does this only when you add the date or time field as the top level of a new dimension. If the date or time field is not the top level, modify the cube by opening the .oqy file in Microsoft Query, or contact the person who created the cube to make these changes.
The summary function I want is missing
In the OLAP Cube Wizard, the only summary functions available for data fields are Sum, Count, Min, and Max.
Create the report directly from the database records If you can simplify and reduce your query to where your system can handle the amount of data it returns, try returning the data directly to Microsoft Excel from Microsoft Query without creating a cube. When you create a PivotTable or PivotChart report directly from records in a database, you have access to the full set of PivotTable summary functions (Sum, Count, Average, Max, Min, Product, Count Nums, StdDev, StdDevp, Var, and Varp).
Consider setting up an OLAP server for the database The Microsoft OLAP server product, Microsoft SQL Server OLAP Services, lets you set up a wider range of summary fields than the OLAP client software included in Microsoft Office.
Make sure the original database is available To edit an OLAP cube, you must have access to the original server database that supplied the cube data. Check to make sure the database hasn't been renamed or moved. Make sure the server is available and you can connect to it.
Check for changes to the original database If you used the OLAP Cube Wizard to create the cube from a relational database, tables in the database might have been renamed or deleted so that the cube can no longer locate the original data. Connect to the database in Microsoft Query, and check for any changes to the organization, or schema, of the database. If the database has changed, you'll need to create a new cube.
A 'Data has been lost' message appears
A field you included in the cube is no longer available in the source database.
Check for changes to the original database If the cube is an offline cube file that was created from an OLAP server database, reconnect a report to the server database and check the fields available in the PivotTable Field List window. If the field is no longer available from the server, you can use the Offline OLAP command on the PivotTable menu to create a new offline cube file.
If you created the cube in Microsoft Query, use Query to open the .dqy query file that you used to create the cube, or if you did not keep a .dqy file, create a new query connecting to the original relational database. Check what fields are available. For full instructions, see Help in Microsoft Query. If fields have been removed from the database, you can create a new cube.
Saving the cube file is taking a long time
If you included a large subset of the OLAP data in the cube file, the file may be time consuming to create.
- To cancel saving the file, click Stop in the Create Cube File – Progress dialog box.
- To reduce the size of the file, eliminate data that you don't need to view in your report. In step 2 of the Offline Cube Wizard, select only the dimensions and levels within each dimension that you need to see. In step 3, select only the measures that you're using as data fields in the report, and in each dimension eliminate any items you don't need.
I run out of disk space while saving a cube
OLAP databases are designed to manage very large amounts of detailed data, and as a result, the server database might occupy a much larger amount of disk storage than your local hard disk provides. If you specify a large subset of this data for your offline cube file, you might run out of space.
Free up disk space or find another disk Try deleting files you don't need from your disk before saving the cube file. Or try saving the file on a network drive.
Include less data in the offline cube file Consider how you can minimize the amount of data in the file and still have what you need for your PivotTable or PivotChart report. Try the following:
- Eliminate dimensions In step 2 of the Offline Cube Wizard, select only the dimensions that you actually have displayed as fields in your PivotTable or PivotChart report.
- Eliminate levels of detail Click the
box next to each selected dimension in step 2 of the wizard, and clear the check boxes for levels lower than those displayed in your report. - Eliminate data fields In step 3 of the wizard, click the box next to Measures, and select only the data fields you're using in the report.
- Eliminate items of data Click the box next to each dimension in step 3, and clear the check boxes for items you don't need to see in the report.
Stay connected to the server If you've eliminated all possible data and still cannot save an offline cube file, you'll need to continue using the connection to the server OLAP database to interact with your report.
Using OLAP cubes
Data is missing from my report after I create a cube file
When you selected the contents for the offline cube file, you might have left out some of the data for the fields used in the report. As a result, when you change the report to display different data, data you expected to see isn't available from the offline cube file.
Use the Offline OLAP command on the PivotTable menu to reconnect the report to the server database, and make sure the report displays the data you want to see. Then edit the offline cube file, making sure you include all dimensions, data fields, and levels of detail used in the report.
My report is using fields from the query instead of my cube
If the PivotTable Field List window is showing you the fields from the relational database that you queried to create the cube, you returned the data from your query to Microsoft Excel instead of opening the .oqy file created by the OLAP Cube Wizard. This file stores the cube definition, and if you saved a .cub offline cube file, provides access to that file. If you did not specify a new location for the .oqy file, the file was saved in either my documents\my data sources or winnt\profiles\your user name\my data sources, depending on your version of the Windows operating system.
To base a report on your new cube, click Open on the Excel File menu, click Query Files in the Files of type list, and then locate and double-click the .oqy file. If you want a PivotChart report, click the PivotTable report that's created when you open the .oqy file, and then click Chart Wizard
on the PivotTable toolbar.
Data I know is in the database is missing from my cube
Cubes created from OLAP server databases or relational databases don't necessarily include all of the data in the original database. Only the data that you select in the Offline Cube Wizard or OLAP Cube Wizard is included in the cube.
Wait for data retrieval to complete When you change your PivotTable or PivotChart report to display different data, new data is retrieved from the cube. When you refresh the report, new data is retrieved from the original database and the cube is completely reconstructed. This process might take a while.
Check with the person who created the cube If you got the cube from someone else, and the PivotTable Field List window is missing fields that you need or levels of detail that you wanted are unavailable, ask the creator of the cube to change it so that it includes additional data.
Change the contents of an offline cube file If you created the offline cube file in Microsoft Excel from an OLAP server database, use the Offline OLAP command on the PivotTable menu to change the file. Make sure you include all dimensions, data fields, and levels of detail used in the report.
Check the contents of a cube that was created in Query You cannot add data to cubes created with the OLAP Cube Wizard in Microsoft Query, but you can change how the cube is organized and delete data from the cube. If the cube is missing fields from the original relational database, you can create a new cube in Query to include those fields. In Query, open the .dqy file that you used to query the data for the OLAP cube, or create a new query if you did not keep a .dqy file. Add to the query any additional fields that you want in the cube, and then use the Create OLAP Cube command on the Query File menu create a new cube. For full instructions, see Help in Microsoft Query.
New data doesn't appear in my report when I refresh
The offline cube file, or the cube created in Microsoft Query, might not be able to connect with the original server database to retrieve new data.
Make sure the original database is available Check that the original server database that supplied the data for the cube hasn't been renamed or moved. Make sure the server is available and you can connect to it.
Make sure new data is available Check with the database administrator to determine whether the database has been updated in the areas included in your report.
Make sure the database organization hasn't changed If an OLAP server cube has been rebuilt, or a relational database supplying data to a cube has been reorganized, you might need to reorganize your report or create a new offline cube file or OLAP Cube Wizard cube to access the changed data. Contact the database administrator to find out about changes to the database.
Microsoft Excel can't find my offline cube file
The .cub might have been renamed or moved.
Make sure you have the file If someone else gave you the .oqy file you're opening, make sure you also have access to the .cub file.
Browse for the file If you're trying to connect to the offline cube file from the Offline OLAP Settings dialog box, click Browse, and locate the .cub file.
Reconnect to the original database If you cannot locate the file, you might be able to reconnect the report to the original OLAP server database. Click the report, click Offline OLAP on the PivotTable menu, and then click On-line OLAP. You can then create a new offline cube file.
Note For further help with problems with OLAP Cube Wizard cubes, see Help in Microsoft Query.