Drill-through is often used to limit the level of details that can be accessed directly in a report. This is done to ensure the returned result set is not big and could cause the OLAP server coming to a standstill. An example could be InvoiceID. We can create our cube so the end user cannot drag and drop the InvoiceID. Instead the user can execute a drill-through action returning the information in a tabular form.
This article will describe how to enable drillthrough (drilldown) in the Jet Data Manager.
- In SQL Server BIDS open the OLAP database
Type in server and database name and hit OK.
- In the Solution Explorer open the cube where the drill-through will be applied
- Click "New Drillthrough Action" on the Actions tab
- Set up the drill-through action
Property Description Name This is the name the front-end users will see. Measure group members Select <All> if the action should apply for all measure groups. Condition (Optional) Add a condition to limit the scope of the action. The action will not be available in the front-end if the condition is not met. This is optional. Drillthrough Columns Select the columns to be displayed when the drill-through action is executed. Only dimensions and measure in the cube are available. If more details are needed from the underlying fact table this has to be added as a dimension to the cube. The dimensions could be made invisible to prevent the user to use it for drag and drop purposes. For further information on how to hide a cube dimension please see: Hiding a cube dimension MaximumRows If the returned result set needs to be limited the MaximunRows property in the Properties window can set set. This is property should be set if large result set could be returned by mistake.
- Save the changes
Once the drill-through action has been setup the project needs to be saved. Hit CTRL-S to save the changes
- In SQL Server Management Studio connect to the OLAP database
- Script the cube
- Locate and copy the Actions tag
- Create an OLAP Script Action in the JDM project
- Paste the script into the script window
Add <InsertEnd Node="Cube"> </InsertEnd> tags and paste the script between the two tags
Modify the <Actions> tag in the beginning of the script to: <Actions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
To avoid breaking the code in case of changes in the names of objects it is best practice to set up parameters for all referenced cube objects in the script. For details about parameters please see: Stored Procedures, User Defined Functions, and Script Actions
- Add the script to the cube
- Deploy and execute the cube or the whole project.
- Open Excel (or other front-end) and connect to the cube