How to specify drillthrough options for a linked cube

Analysis Services

Analysis Services

How to specify drillthrough options for a linked cube

To specify drillthrough options for a linked cube based on a regular cube

  1. On the publishing server hosting the source cube of the linked cube, in the Analysis Manager tree pane, under the database that contains the source cube, expand the Cubes folder.

  2. Right-click the source cube, and then click Edit.

  3. On the Tools menu, click Drillthrough Options. (If this command is not available, the cube structure is not valid. You can try to determine the problem by clicking Validate Cube Structure on the Tools menu.)

  4. In the Drillthrough Options dialog box, perform one of the following actions:
    • To enable drillthrough, select the Enable drillthrough check box. Go to Step 5.

    • To disable drillthrough, clear the Enable drillthrough check box. Go to Step 7.
  5. In the Columns tab, select the columns to be displayed when the end user attempts to drill through from the linked cube on the subscribing server.

    Important  If you select a column to which a cube role is denied access and then grant the cube role the ability to drill through, you create a security exposure. (You can deny access to a column by using dimension security or cell security. For more information, see Dimension Security and Cell Security.) When the end users in the cube role drill through, they access the denied column.

  6. (Optional.) In the Filter tab, specify a filter (WHERE clause expression) to limit the result set returned by drillthrough. Do not include the keyword WHERE. For example, to limit the result set to fourth-quarter data, specify:
    "shipment_facts"."quarter"=4
    

    For more information, see Drillthrough Filters.

  7. Click OK.

  8. In Cube Editor, on the File menu, click Save.

  9. (Optional.) On the subscribing server, browse the linked cube from Analysis Manager to test the drillthrough settings for the linked cube.

To specify drillthrough options for a linked cube based on a virtual cube

  1. On the publishing server hosting the virtual cube that is the source cube of the linked cube, in the Analysis Manager tree pane, under the database that contains the virtual cube, expand the Cubes folder.

  2. Right-click a source cube of the virtual cube, and then click Edit.

  3. On the Tools menu, click Drillthrough Options. (If this command is not available, the cube structure is not valid. You can determine the problem by clicking Validate Cube Structure on the Tools menu.)

  4. In the Drillthrough Options dialog box, select the Enable drillthrough check box.

  5. In the Columns tab, select the columns to be displayed when the end user drills through the linked cube on the subscribing server.

    Important  If you select a column to which a cube role is denied access and then grant the cube role the ability to drill through, you create a security exposure. (You can deny access to a column by using dimension security or cell security. For more information, see Dimension Security and Cell Security.) When the end users in the cube role drill through, they access the denied column.

  6. (Optional.) In the Filter tab, specify a filter (WHERE clause expression) to limit the result set returned by drillthrough. Do not include the keyword WHERE. For example, to limit the result set to fourth-quarter data, specify:
    "shipment_facts"."quarter"=4
    

    For more information, see Drillthrough Filters.

  7. (Optional.) If you do not want drillthrough to be enabled for the source cube on the publishing server after selecting the display columns, clear the Enable drillthrough check box. This option provides users the ability to drill through to the selected columns through the virtual cube on the publishing server and the linked cube on the subscribing server but not through the current source cube.

  8. Click OK.

  9. In Cube Editor, on the File menu, click Save.

  10. (Optional.) If there is more than one source cube on the publishing server, select the name of the next source cube from the Cube box within Cube Editor. Repeat Steps 3 through 9 as necessary for each source cube.

  11. Close Cube Editor.

  12. On the publishing server in the Analysis Manager tree pane, right-click the virtual cube that is the source cube for the linked cube to enable drillthrough, and then click Edit.

  13. If the properties pane is not expanded in Virtual Cube Editor, expand it by clicking Properties beneath the tree pane.

  14. Click the Advanced tab of the properties pane, and then click the value next to the Enable Drillthrough property.

  15. Click True to enable drillthrough for the virtual cube.

  16. In Virtual Cube Editor, on the File menu, click Save.

  17. (Optional.) On the subscribing server, browse the linked cube from Analysis Manager to test the drillthrough settings for the linked cube.