Reviewing and creating recommended Intelligent Cubes

After you have completed the prerequisites for using Cube Advisor, you can begin using Cube Advisor to create Intelligent Cubes.

The steps below show you how to use Cube Advisor to create Intelligent Cubes to support dynamic sourcing for the reports in your project.

Prerequisites

You must complete the prerequisites described in Prerequisites for using Cube Advisor before using Cube Advisor to recommend and create Intelligent Cubes to support a dynamic sourcing strategy.
In order to use Cube Advisor, you must log in to a project using a MicroStrategy user account with the following privileges:
- Privileges required to execute reports and documents.
- Privileges required to create folders in a MicroStrategy project.
- Privileges required to create Intelligent Cubes.

You can use Integrity Manager to perform a before and after test to identify which reports actually connect to a suggested Intelligent Cube and also to test data integrity.

To review and create recommended Intelligent Cubes using Cube Advisor

In Windows choose Start > Programs > MicroStrategy Tools > Cube Advisor. Cube Advisor opens.
You can analyze a new set of reports using Cube Advisor or use the results of a previous Cube Advisor analysis:

To use Cube Advisor to analyze a new set of reports, choose Tools > Options. The Options dialog box opens. Continue to step 3 in this procedure and complete all of the steps provided.
If you have previously used Cube Advisor to create Intelligent Cubes to support dynamic sourcing for the reports in your project, you can use the results file of this analysis. Choose File > Open. In the Open dialog box, select the Cube Advisor results file to use and click Open.

The results of a Cube Advisor analysis are stored in the MicroStrategy common files folder (the default is

C:\Program Files\Common Files\MicroStrategy). The name of the file is in the format ProjectName.details.txt. For example, analyzing the MicroStrategy Tutorial project creates a Cube Advisor results file named MicroStrategy Tutorial.details.txt.

Click ... (browse) to supply the following files to support Cube Advisor and define the following options:
- Metric Level File: The metric levels log file is one of the files that can be used to track the use of dynamic sourcing. Cube Advisor uses this log file to help recommend and create Intelligent Cubes to support dynamic sourcing.
  
  If you install and use Cube Advisor on the same machine that hosts Intelligence Server, the metric levels log file is automatically created by Cube Advisor. In this scenario you do not need to create the metric levels log file or provide the location for the file. Otherwise, you must create this file manually and provide the directory it is stored in.
  
  When manually creating the metric levels log file, the file is created in the Log directory within the MicroStrategy common files. The default directory is C:\Program Files\Common Files\ MicroStrategy\Log. For steps on how to create this file, see Creating the metric levels log.
- Enterprise Manager File: You can also supply a MicroStrategy Enterprise Manager report to include more information on the performance benefits of each Intelligent Cube as part of your dynamic sourcing strategy. For information on locating, executing, and exporting this report as well as the information provided by supplying the report, see Enterprise Manager report for Cube Advisor.
- Ignore Reports That Are Covered By Existing Cubes: Select this option to exclude reports that already connect to an Intelligent Cube from the Cube Advisor analysis. This prevents Cube Advisor from analyzing and recommending Intelligent Cubes for reports that already connect to Intelligent Cubes.
- Ignore Reports Not In Enterprise Manager File: Select this option to exclude reports that are not included in the Enterprise Manager report from Cube Advisor analysis. This allows Cube Advisor to focus only on the reports that were included in the Enterprise Manager report for further performance analysis.
Click OK to close the Options dialog box and return to Cube Advisor.
Provide the following connection information:

Computer Name: Type the name of the machine on which Intelligence Server is hosted.
Port: Type the port number used for Intelligence Server. The default is 34952.
User: Type the MicroStrategy user's user name to connect to a MicroStrategy project. The MicroStrategy user must have the privileges listed in the prerequisites for these steps.
Password: Type the password for the MicroStrategy user.

Click Connect. MicroStrategy projects are displayed.
Select a project, and then click Connect To Project. The Report Selection page opens.

If you supplied the Enterprise Manager report, you must select the project that was analyzed using the Enterprise Manager report.
Expand the folders of the project to locate the reports to analyze with Cube Advisor.

Selecting a check box for a report includes the report in the analysis. Selecting a check box for a folder includes all reports in that folder and all reports in folders within the folder in the analysis.
Once you have selected all the reports to analyze with Cube Advisor, click Get Cube Recommendations. The Analyzing Reports page opens and report analysis begins.

To analyze reports, only the report SQL is analyzed, which allows a large number of reports to be analyzed without having to execute the report SQL against the database.
If the analysis completes with a message that no reports can use dynamic sourcing, this can be caused by various scenarios:
- The features used in the reports prevent the reports from being able to use dynamic sourcing. To review a list of features that prevent the use of dynamic sourcing, see Features that prevent the use of dynamic sourcing.
- The metric levels log file was not created properly. Review the steps to create a metrics level log file (see Creating the metric levels log) and attempt the Cube Advisor analysis again.
- Dynamic sourcing is not enabled for the project, reports, or other objects and features in the project. For information on configuring dynamic sourcing, see Configuring dynamic sourcing.
Once analysis is complete, the Cube Recommendations page opens. This page allows you to review the recommended Intelligent Cubes, and select which Intelligent Cubes to create. Depending on whether you supplied an Enterprise Manager report, you can review Intelligent Cubes as described below:
- If you did not supply an Enterprise Manager report, the information on the recommended Intelligent Cubes that is displayed includes the number of reports that could connect to the Intelligent Cube using dynamic sourcing.
  
  You can select a recommended Intelligent Cube to display the attributes and metrics that would be included in the Intelligent Cube, as well as a list of reports that could connect to the Intelligent Cube using dynamic sourcing.
- If you supplied an Enterprise Manager report, a Flash visualization provides detailed information on the recommended Intelligent Cubes, an example of which is shown below:
Some of the benefits of the Flash visualization include:
- You can click Jobs, Result Rows, Users, and DB Time to sort the Intelligent Cubes by these various usage and performance statistics. These usage and performance statistics are displayed as microcharts, which are aligned with the report coverage microcharts provided for each Intelligent Cube. For information on these usage and performance statistics, see Enterprise Manager report for Cube Advisor.
- You can select an Intelligent Cube to see how creating it affects various performance statistics.
- The area at the top of the Flash visualization provides a summary of the estimated performance improvements that would come from building the selected set of Intelligent Cubes.
- You can expand an Intelligent Cube to review more information on the attributes and metrics that would be included in the Intelligent Cube, as well as a list of reports that could connect to the Intelligent Cube using dynamic sourcing.
  
  This also includes information on how many reports that could connect to the Intelligent Cube require certain attributes and metrics. This analysis allows you to determine the benefit of including each attribute and metric in an Intelligent Cube.
- Each Intelligent Cube includes a microchart, which represents the subset of reports that could connect to the Intelligent Cube using dynamic sourcing. A bar in the microchart represents that the report is covered by the Intelligent Cube and no bar means that report is not covered by the Intelligent Cube. By moving the cursor over the microchart, a tooltip is displayed that lists the various reports that could connect to the Intelligent Cube using dynamic sourcing.
- Once you select a specific Intelligent Cube, the bars for all reports covered by that Intelligent Cube become blue. This color coding also helps to visually analyze the overlap of reports across Intelligent Cubes. If you see two Intelligent Cubes have a very similar report coverage distribution, then only one of the two Intelligent Cubes should be created.
Once you have reviewed the information, select the check boxes for the Intelligent Cubes to create using Cube Advisor, and then click Create Cube Design. The Browse for Folder dialog box opens.
Select a folder to create the Intelligent Cubes in. Each Intelligent Cube is created in a separate folder that is created within the folder you select. Click OK. The Cube Design Options dialog box opens.
Select one of the following options:

Design Intelligent Cubes with outer join properties: Select this option to enable outer joins on all Intelligent Cubes to be created. This ensures that all warehouse data is captured; however, this can potentially increase the size of the Intelligent Cubes.
Allow Dynamic Sourcing without enabling outer join properties: Select this option to allow reports to connect to Intelligent Cubes using dynamic sourcing even when some outer join properties are not defined. However, this can cause incorrect data to be returned in certain scenarios.

For additional information on enabling dynamic sourcing for Intelligent Cubes, see Enabling or disabling dynamic sourcing for Intelligent Cubes.

Click OK to begin creating the Intelligent Cubes. The Creating Cubes dialog box opens.

Once the Intelligent Cubes are created, click OK.

Each Intelligent Cube is created in a separate folder. Each folder also contains shortcuts to all the reports that can connect to the Intelligent Cube using dynamic sourcing. This provides easy recognition of the reports that can connect to each Intelligent Cube using dynamic sourcing. You must publish the Intelligent Cubes to allow the reports to connect to the new Intelligent Cubes using dynamic sourcing. For information on how to publish Intelligent Cubes, see Publishing Intelligent Cubes.

The results of the Cube Advisor analysis are stored in the MicroStrategy common files folder (the default is C:\Program Files\Common Files\ MicroStrategy). The name of the file is in the format ProjectName.details.txt. For example, analyzing the MicroStrategy Tutorial project creates a Cube Advisor results file named MicroStrategy Tutorial.details.txt. You can use this file with Cube Advisor to review and create Intelligent Cubes at another time.