Intelligent cubes are multi-dimensional, in-memory copies of data that can be queried and accessed by many different documents and reports. Basically, the cube is allocated on the Intelligence Server machine and uses either memory or disk to store the definition and perform fast data retrieval.
We can treat it as a database where data is pre-cached and quickly accessed. To improve the report execution performance and response time, it's good to run the report with significant wait time and high usage against intelligent cubes. We cannot include custom groups, consolidations, derived metrics, prompts, or view filters in an intelligent cube.
The steps to create an intelligent cube are as follows:
- Navigate to the folder where you want to create an intelligent cube; the best practice is to create it under Public Objects | Reports.
- Select the report from which you want to build an intelligent cube. Edit and enter the report editor.
- Select Data | Intelligent Cube Options | Convert to Intelligent Cube.
- Click Save and Close, and enter an intelligent cube name.
This gives us a cube, but it has not been processed so it cannot be used for reporting. To make it usable, right-click on a cube and click Run. Once execution completes, it shows an Execution complete screen, giving us the provision to create reports out of a cube.
We can publish intelligent cubes as follows:
- Publish manually:
- Browse to the intelligent cube that needs to be published.
- Right-click the intelligent cube and click Run.
- Publish with schedule:
- Select Administration | Configuration Managers | Subscriptions.
- Right-click and select Subscription creation wizard.
- On the welcome page, click Next.
- On the specify characteristics screen, select the project that contains the intelligent cube and select Cube refresh as the delivery type. Click Next.
- On the Choose Report/Document screen, browse to the intelligent cube to be published and click the right arrow to move it to the selected objects pane. Click Next.
- On the choose recipient screen, select Schedule or Run Subscription Manually. Click Next.
- On the Specify Subscription Properties screen, select Expire Subscription if subscription should expire on and the recipient email id who receives mail after the intelligent cube runs. Click Next.
- The summary page is displayed; review the information and click Finish creating a subscription.
- Alternate way to publish an intelligent cube:
- Right-click the intelligent cube and select Schedule Delivery To | Refresh Cube.
- In Refresh Cube Subscription Editor enter the Name, Schedule, Delivery Notification and click OK.
Based on the schedule selected, the intelligent cube will be refreshed.
Republishing an intelligent cube using the preceding options will reload data from the data warehouse causing existing data to be overwritten. To avoid this, we can use the incremental refresh intelligent cube option.
Incremental refresh type options are:
- Update: Add new data if not in a cube, or else just update where applicable
- Insert: Add new data if available
- Delete: Based on the filter or report definition, data is deleted from the cube
- Update only: No new data is added, it is just updated where applicable
Types of object used for incremental fetching:
- Filter: This is used for incremental refresh; data returned by a filter is compared to an existing cube data
- Report: This is a template for an intelligent cube, and the cube is populated based on the report's data
To create an incremental refresh:
- Right-click on an intelligent cube and select Define Incremental Refresh Report.
- Select the options from the following screen for incremental refresh:
An example:
Let's say we have an intelligent cube which stores weekly sales data, so we would like an intelligent cube to update weekly sales data by the end of the week. We can use incremental refresh so that the data for one week is added without affecting the existing data.
