MicroStrategy ONE
Configuring large reports as bulk export reports
The bulk export feature allows you to select a report and export it to a delimited text file. Using this feature, you can retrieve result sets from large datasets without having to load the datasets in memory. As the result sets are brought back incrementally, no limit is set on the size of the dataset to be retrieved.
While you can schedule a bulk export report for execution with Distribution Services in MicroStrategy Web, you cannot directly execute a bulk export report in MicroStrategy Developer. When you right-click a bulk export report, the Run Report option is not available. You can, however, view the report in Design View and SQL View in MicroStrategy Developer. (All the other views are disabled.) For more information on scheduling a bulk export report, see the MicroStrategy Web Help.
If you need to, you can convert a bulk export report back to a standard report, to be able to execute the report in MicroStrategy Developer. For instructions, see Converting a bulk export report to a standard report.
The following procedure describes how to define a report as a bulk export report.
Prerequisites
-
To use the Bulk Export feature, you must have a Distribution Services license. If you do not have a Distribution Services license, contact your MicroStrategy account executive for information on obtaining one.
-
To define a report as a bulk export report in MicroStrategy Developer, you must have the Use Bulk Export Editor privilege.
-
To execute a bulk export report from MicroStrategy Web, you must have the Web Subscribe To Bulk Export privilege.
To configure a report as a bulk export report
-
In MicroStrategy Developer, open a report in the Report Editor. (How?)
-
From the Data menu, select Configure Bulk Export. The Bulk Export Setup dialog box is displayed.
-
Click the General tab.
-
By default, the Bulk export database instance, which is the database instance for the database containing the report data, is defined as the data warehouse for the project. You can specify another database instance in this field.
-
Select the Default delimiter for the exported text file, which is the character that separates the data in the text file. If the character that you want is not available, select Other, then type the character.
-
Click the Advanced tab.
-
Type the Maximum time for SQL execution to build table, in seconds. If this time limit is reached before the table is built, the export is cancelled with a timeout error.
-
Type the Maximum time for exporting data to file, in seconds. If this time limit is reached before the file is completely written, the export is cancelled with a timeout error.
-
Type the Number of rows retrieved in each step, which is the number of database rows that the bulk export retrieves at a time.
-
Type the Table space, which is the database table space in which the temporary table for the bulk export is created.
-
Type the Table prefix, which is the table prefix for the bulk export temporary table.
-
Click the SQL Statements tab.
-
Specify the SQL statements to be executed after the table is populated, but before the data is exported to a file. For instance, if you need to format the warehouse data in a particular way, you can run a SQL script to do so.
-
Click OK. The Bulk Export Setup dialog box closes and the report is now specified as a bulk export report.
-
Save the report.
-
After you save a report as a bulk export report, its icon in MicroStrategy Developer changes as shown here:
-
-
If you open a bulk export report in SQL view, certain column names are replaced by column aliases. You can view the column names by changing the default VLDB properties for metrics. To do this:
-
Open the Project Configuration Editor.
-
Select Database instances in the Project Definition category.
-
Click VLDB Properties.
-
Select the Default To Metric Name option from the Metrics folder.
-
Select the Use the metric name as the default metric column alias option.
-
Save the changes.
-
Restart the MicroStrategy Intelligence Server.
For detailed information on VLDB Properties, see Details for All VLDB Properties in the System Administration Help.
-
-
If you have selected multiple reports, including bulk export reports, and choose the Run Report option from the right-click menu, all the bulk export reports are filtered out and are opened in the Report Editor; the other reports are executed as usual.
-
Bulk export reports have the same limitations and restrictions as data mart reports. In particular, bulk export reports cannot be used in HTML documents or Report Services documents.