Enabling Drillthrough in Dynamic Reports

The OLAP cubes in the Data Warehouse contain aggregations of multidimensional data. When viewing the aggregated data in a dynamic report, you might want to see the underlying detailed records that contributed to that aggregate. The ability to view the underlying records is known as "drillthrough." Drillthrough only applies to OLAP-based reports, and drillthrough is operational only if the cube on which a report is based is enabled for drillthrough.

The following procedures describe how to enable drillthrough for cubes in the Data Warehouse. You can enable drillthough for a regular cube (a cube that is based on tables and has its own aggregations) and for a virtual cube (a logical cube that is based on one or more regular cubes or linked cubes).

Note

• You can use the CSDW_MakePrimaryProductCategoryInfo stored procedure to map each product to its primary parent category, and thus reduce the number of items that appear in the details of a drillthrough report. For information, see Using Drillthrough to View Product Sales or Shopping Basket Events.

To enable drillthrough for a regular cube

To enable drillthrough for a virtual cube

To enable drillthrough for a regular cube

1. Click Start, point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.
2. On the Tree tab, expand the Analysis Servers node, expand the node for the server on which the Data Warehouse is running, and then expand the Data Warehouse node, for example Retail_dw.
3. Expand the Cubes node.
4. Right-click the cube on which you want to enable drillthrough, and then click Edit.
5. In the Cube Editor window, on the Tools menu, click Drillthrough Options.
6. In the Cube Drillthrough Options dialog box, select the Enable Drillthrough check box.
7. On the Columns tab, select the column names from which you want drillthrough data to be available, and then click OK.
8. In the Cube Editor window, on the File menu, click Save.
9. On the File menu, click Exit.
10. In the Analysis Manager window, right-click the same cube that you modified above, and then click Manage Roles.
11. In the Cube Role Manager dialog box, click Edit.
12. On the Options tab, select the Allow drillthrough check box.
13. Click OK.
14. In the Cube Role Manager dialog box, click Close.
15. In the Analysis Manager window, on the Console menu, click Exit.

To enable drillthrough for a virtual cube

1. Enable drillthrough for at least one of the base cubes of the virtual cube by following the procedure To enable drillthrough for a regular cube.
2. Click Start, point to Programs, point to Microsoft SQL Server, point to Analysis Services, and then click Analysis Manager.
3. On the Tree tab, expand the Analysis Servers node, expand the node for the server on which the Data Warehouse is running, and then expand the Data Warehouse node, for example Retail_dw.
4. Expand the Cubes node.
5. Right-click the virtual cube on which you wish to enable drillthrough, and click Edit.
6. In the Cube Editor window, on the View menu, click Properties.
7. On the Advanced tab, set Enable Drillthrough to True.
8. In the Cube Editor window, on the File menu, click Save.
9. On the File menu, click Exit.
10. In the Analysis Manager window, right-click the same cube that you modified above, and click Manage Roles.
11. In the Cube Role Manager dialog box, click Edit.
12. On the Options tab, select the Allow drillthrough check box.
13. Click OK.
14. In the Cube Role Manager dialog box, click Close.
15. In the Analysis Manager window, on the Console menu, click Exit.

See Also

Viewing Dynamic Reports

Features of Dynamic and Static Reports

Copyright © 2005 Microsoft Corporation.
All rights reserved.