Creating Intelligent Cubes using Freeform SQL and Query Builder

The Freeform SQL Editor and Query Builder Editor provide two alternative methods to create reports in MicroStrategy. In addition to creating reports directly, you can use the Freeform SQL Editor or Query Builder Editor to create an Intelligent Cube. This stores the data as an Intelligent Cube, which allows you to take advantage of various Intelligent Cube features, including the improved response time of reporting against Intelligent Cubes.

Once an Intelligent Cube is created, reports can be created based on the Intelligent Cube. These reports can analyze the Freeform SQL or Query Builder data, while also taking advantage of OLAP Services analysis features such as derived elements.

The steps below show you how to create an Intelligent Cube using Freeform SQL or Query Builder.

Prerequisites

• You must make the data sources to report on available for use with Freeform SQL and Query Builder. You can make data sources available for the following tasks:

• To access data sources such as databases, Microsoft Excel files, and text files using the Freeform SQL Editor, see Database instance selection.

• To access web services using the Freeform SQL Editor, see Allowing connections to web services in a project.

• To access data sources such as databases, Microsoft Excel files, and text files using the Query Builder Editor, Database instance selection for Query Builder.

• You need the Use Intelligent Cube Editor privilege to create Intelligent Cubes. This privilege is part of OLAP Services.

To create an Intelligent Cube based on an MDX cube

1. Using MicroStrategy Developer, log in to a project connected to an MDX cube source.

2. From the File menu, select New, and then select Intelligent Cube. The New Intelligent Cube dialog box opens.

3. On the ODBC Sources tab, in the Source area, select the database instance that connects to the data source to report on using Freeform SQL or Query Builder.

4. Select one of the following options:

   • To create an Intelligent Cube using the Freeform SQL Editor, select Use Freeform SQL Editor and click OK. The Freeform SQL Editor opens.

   • To create an Intelligent Cube using the Query Builder Editor, select Use Query Builder Editor and click OK. The Query Builder Editor opens.

5. You can now begin to create the Intelligent Cube using the standard techniques to create a Freeform SQL report or Query Builder report, which include:

   • Create a Freeform SQL report to access databases

   • Create a Freeform SQL report using a stored procedure

   • Creating a report to analyze web service data

   • Create a Query Builder report to access relational databases

   • Creating Freeform SQL and Query Builder reports from Excel files

   • Creating Freeform SQL and Query Builder reports from text file

6. Once you have finished developing the Intelligent Cube, in the Report Editor, from the File menu, select Save. The Save Intelligent Cube As dialog box opens.

7. In the Object name field, type a descriptive name for the Intelligent Cube.

8. Click OK to save the Intelligent Cube and return to the Report Editor.

9. To make the Intelligent Cube available to be reported on, you must publish the Intelligent Cube. This can be done by selecting Run Report in the toolbar.

10. Once the Intelligent Cube is published, reports can be created on the Intelligent Cube.