Exporting Large Reports as Delimited Text Files

The bulk export feature allows you to select a report and export it to a delimited text file. Using this feature, you can retrieve data from large result sets without having to load the result sets in memory. As the data is brought back incrementally, no limit is set on the size of the result set to be retrieved.

While you can schedule a bulk export report for execution with Distribution Services, 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 on MicroStrategy Developer. (All the other views are disabled.)

You can convert a bulk export report back to a standard report, to be able to execute the report in MicroStrategy Developer. For steps, see To Convert a Bulk Export Report to a Standard Report.

To Configure a Report for Bulk Export

1. In MicroStrategy Developer, open a report in the Report Editor.
2. Choose Data > Configure Bulk Export.
3. Click the General tab.
4. 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.
5. 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.
6. Click the Advanced tab.
7. Enter 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.
8. Enter 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.
9. Enter the Number of rows retrieved in each step, which is the number of database rows that the bulk export retrieves at a time.
10. Type the Table space, which is the database table space in which the temporary table for the bulk export is created.
11. Type the Table prefix, which is the table prefix for the bulk export temporary table.
12. Click the SQL Statements tab.
13. 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.
14. Click OK.
15. Save the report.

    After you save a report as a bulk export report, its icon in MicroStrategy Developer changes as shown in the following figure.

    

    • 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, from the Project Configuration Editor, select Database instances in the Project Definition category, then click VLDB Properties, and then select the Default To Metric Name option from the Metrics folder. Select the Use the metric name as the default metric column alias option, and save the changes. Restart the MicroStrategy Intelligence Server. For detailed information on VLDB Properties, see the Supplemental Reference for System Administration.
    • 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 Report Services documents. For information on Report Services documents, see the Report Services Document Creation Guide.
    • Large bulk export reports may require that a substantial amount of data be transferred between Intelligence Server and the data source. You can improve the performance of this process by transferring the data in multiple, incremental transfers of data. To do this, enable the Incremental Data Transfer VLDB property, in the Query Optimizations VLDB properties group. For steps to set VLDB properties, see the VLDB Properties chapter of the Supplemental Reference for System Administration. This property is available at both the project and report levels.

To Convert a Bulk Export Report to a Standard Report

1. In MicroStrategy Developer, open the bulk export report in the Report Editor.
2. Choose Data > Remove Bulk Export Definition.
3. Save the report.