Creating Intelligent Cubes is as easy as creating reports.
You need the Use Intelligent Cube Editor privilege to create Intelligent Cubes. This privilege is part of OLAP Services.
After you have created your Intelligent Cube, you can publish its set of data to be shared by multiple reports. For more information on publishing an Intelligent Cube, see Publishing Intelligent Cubes.
Make sure you review the following prerequisites before you create an Intelligent Cube in MicroStrategy. They can save you time and make your Intelligent Cubes more effective for your reporting environment:
While creating Intelligent Cubes, bear in mind that Intelligent Cubes can deplete Intelligence Server's system resources. Create Intelligent Cubes for logical subsets of your data, rather than using them as a reflection of your entire data warehouse. For information on managing the size of Intelligent Cubes, see Managing Intelligent Cubes in the System Administration Help.
- Define your business query: Before you can determine what objects to place on an Intelligent Cube, you need to know what data you want to make available for reports to access directly. To define your Intelligent Cube, make sure you consider the following questions:
- What subset of business queries does the Intelligent Cube need to provide data for? Intelligent Cubes allow you to create sets of data that can support multiple reports that answer variations to similar business queries.
- Do you have reports that currently access your data warehouse that could benefit from accessing an Intelligent Cube instead? To support this scenario, you can use dynamic sourcing to connect these reports to Intelligent Cubes that you create. You can also use MicroStrategy Cube Advisor to create Intelligent Cubes that these reports can access. For information on using Cube Advisor to support dynamic sourcing, see Using Cube Advisor to support dynamic sourcing.
- Look for existing Intelligent Cubes: Before you create an Intelligent Cube, search through MicroStrategy to see whether a similar Intelligent Cube already exists that can serve the same purpose as the Intelligent Cube that you intend to create. This can not only save you time, it can help you avoid unnecessary duplication in your MicroStrategy project. You can search a project for Intelligent Cubes, or you can view Intelligent Cubes created for your projects in the Intelligent Cube Monitor. For information on using the Intelligent Cube Monitor, see the System Administration Help .
- Review the feature support for Intelligent Cubes: The MicroStrategy features that are available for Intelligent Cubes differ from those available for reports.
Intelligent Cubes act as a set of data for other reports to connect to and use OLAP Services to report, analyze, and display data. Since Intelligent Cubes function simply as a set of data, Intelligent Cubes do not need to contain objects that modify the display of data. The display of data is handled by the reports that access the Intelligent Cube.
For these reasons, the following objects and features cannot be included in Intelligent Cubes in the same ways that they can be included for reports:
- Consolidations and custom groups: Consolidations and custom groups cannot be included in Intelligent Cubes or in reports that access Intelligent Cubes. However, you can include derived elements in reports that access Intelligent Cubes. Derived elements combine the analysis features of consolidations and custom groups, while executing against the Intelligent Cube rather than the data warehouse. For information on derived elements, see Derived Elements.
- OLAP Services features: View filters, derived metrics, and derived elements cannot be included in Intelligent Cubes, but these features are the main analysis tools for reports that access Intelligent Cubes. For an overview of OLAP Services features and how these can be created in reports, see About MicroStrategy OLAP Services.
- Transformation shortcut metrics: These metrics can be created on reports using a similar workflow as derived metrics that are created as shortcut metrics. For information about the difference between transformation shortcut metrics and shortcut metrics that are also derived metrics, see Using rank and percent-to-total metric analysis . As an alternative, you can create transformation metrics using the Metric Editor. Metrics that use transformations in this way can be included in Intelligent Cubes. To create transformation metrics using the Metric Editor, see the Advanced Reporting Help.
- Prompts: Prompts cannot be included in Intelligent Cubes. However, reports that access Intelligent Cubes can use prompts that access only data included in the Intelligent Cube. This saves processing time by ensuring that re-execution against the data warehouse is not necessary.
Building an Intelligent Cube is similar to building a report. You can add data for your Intelligent Cube by including objects such as attributes, metrics, filters, and so on as report objects or report filters. For a list of objects and features that cannot be included in the definition of Intelligent Cubes, see above.
Since Intelligent Cubes are used simply to share a set of data, no data or report results are displayed when you execute an Intelligent Cube. However, executing an Intelligent Cube publishes the Intelligent Cube, which can then be accessed as a set of data for multiple reports (see Publishing Intelligent Cubes).
- You need the Use Intelligent Cube Editor privilege to create Intelligent Cubes. This privilege is part of OLAP Services.
To build an Intelligent Cube
In Developer, from the File menu select New, and then Intelligent Cube. The New Intelligent Cube dialog box opens.
If the New Intelligent Cube dialog box does not open, from the Tools menu, select Developer Preferences. Expand the Object Templates category, select General, and from the Show templates for the following objects list, select Report. Click OK to accept your changes, and then repeat the previous step to open the New Grid dialog box.
- Select Empty Intelligent Cube and click OK. The Report Editor opens.
- Add objects such as attributes, metrics, and so on for the Intelligent Cube, the same way you would add report objects.
Create a filter for the Intelligent Cube as needed.
If you create a filter on an Intelligent Cube, any data that is restricted from the Intelligent Cube is not available for any reports that connect to the Intelligent Cube. While this can help reduce the size of the Intelligent Cube, it also reduces the amount of data available in the Intelligent Cube.
- Click Save and close to save the Intelligent Cube and close the Report Editor.
- To publish an Intelligent Cube, see Publishing Intelligent Cubes.
Building Intelligent Cubes from MDX data sources, or using Freeform SQL
You can create Intelligent Cubes with data from an MDX data source, such as SAP BW, Hyperion Essbase, or Microsoft Analysis Services. To create an Intelligent Cube based on MDX data in Developer, open the File menu, select New, and select Intelligent Cube. In the New Intelligent Cube dialog box, select the MDX Sources tab. For instructions on configuring MDX sources, refer to the MDX Cube Reporting Help.
You can also create Intelligent Cubes by directly querying your data using SQL. You can use Freeform SQL to write your own SQL statements, or Query Builder to create a query using a graphical interface. These options are available in the New Intelligent Cube dialog box, under the ODBC Sources tab. For information on using Freeform SQL and Query Builder to connect to ODBC data sources, see the Advanced Reporting Help.
If you have an existing report that contains a set of objects you want to use in an Intelligent Cube, you can convert the report into an Intelligent Cube.
Creating an Intelligent Cube by converting a report allows you to view the set of data you are including in your Intelligent Cube. This is an easy way to share a report's set of data among multiple reports.
Intelligent Cubes do not display report results in the same way as reports can. Instead of building an Intelligent Cube from the start, you can build a report; execute it; view the report data in grid, graph, or grid and graph mode; and then convert the report to an Intelligent Cube. With this method you can verify that you have the set of data you want, rather than just the required report objects, before you publish it as an Intelligent Cube.
When you convert a report to an Intelligent Cube, some parts of the report are not included in the resulting Intelligent Cube. Intelligent Cubes are not used for the same display and analysis purposes as a report. Intelligent Cubes simply act as a sharable set of data. Therefore, when a report is converted into an Intelligent Cube, some of the display and analysis features are no longer necessary.
The procedure below explains the high-level steps for converting a report to an Intelligent Cube. The procedure assumes you have already created a report.
To convert a report to an Intelligent Cube
- Right-click the report and select Edit. The Report Editor opens.
- Choose Data > Intelligent Cube Options > Convert to Intelligent Cube.
- If the report contains objects that cannot be included in the Intelligent Cube, one of the following messages is displayed:
- If the report includes OLAP Services features such as view filters, derived metrics, or dynamic aggregation, you are prompted to automatically remove these features as part of the conversion process. Click Yes to have these features automatically removed so that the report can be converted into an Intelligent Cube.
- If the report includes features such as consolidations, custom groups, or prompts, a warning message is displayed that explains that these objects cannot be included in the Intelligent Cube. Click OK to close the warning message and then manually remove the objects from the report. You can then attempt to convert the report to an Intelligent Cube again.
- After the conversion process is completed successfully, save the Intelligent Cube.
- You must publish an Intelligent Cube to make it available for multiple reports to access and report on its set of data. To publish an Intelligent Cube, see Publishing Intelligent Cubes.
While you are creating or modifying an Intelligent Cube, you can schedule when an Intelligent Cube should be re-executed against the data warehouse to update its data. For example, you can schedule to re-execute and republish an Intelligent Cube when a database load occurs.
The procedure below describes the steps to create a subscription that schedules the re-execution and republication of a single Intelligent Cube. You can also schedule multiple Intelligent Cubes to be updated using a single subscription, which is described in Publishing Intelligent Cubes using a schedule.
- An Intelligent Cube has been created.
- You have created the schedule to subscribe the publication of an Intelligent Cube to. For information on creating schedules, see the System Administration Help.
To schedule an Intelligent Cube to be updated
- In Developer, browse to an Intelligent Cube.
- Right-click the Intelligent Cube, choose Schedule Delivery To > Refresh Cube. The Subscription Editor opens.
- Select a schedule from the Schedule drop-down list.
- Select other schedule options as required, and click OK.
Once the schedule is triggered the Intelligent Cube is re-executed against the data warehouse and published to the Intelligent Cube Monitor.