23 Generate CSV Output

This topic describes how to generate a CSV output for large reports.

To output a report that includes millions of rows, select to output the report only in CSV format. When you select only CSV format for the output of a large report, the data processor directly generates the CSV output without generating an XML file, which reduces the likelihood of out-of-memory issues.

Requirements

To generate the CSV output for a large volume of data, make sure you:

  • Create a data set using SQL query.

  • Set the Enable CSV Output property in the Data Model Properties page.

  • Select only the Data (CSV) output format for the report.

  • Deselect the Auto Run and Run Report Online options for the report.

  • Deselect the Save Data for Republishing option in the Output tab, if you are scheduling the report.

Limitations

The data engine cannot generate a CSV output when:

  • The SQL query contains the CLOB/BLOB columns

    When a query includes CLOB columns, the data processor cannot generate the CSV output because the LOB might be XML data or non-structured data. Converting the LOB columns to flatten a CSV format doesn't scale. This type of conversion requires complex code changes that are similar to embedding a formatting engine inside the data engine.

  • Data model has a complex parent-child data set hierarchy with multiple groups

    When a data model has multiple data sets with complex parent - child links, the data engine cannot generate CSV output.

  • The SQL query contains the DFF/EFF columns

    When a query includes DFF (Descriptive Flexfield) or EFF (Extensible Flexfield) columns, the data processor cannot generate a CSV output. Each DFF or EFF column is treated as a nested sub-group in the query, which results in nested XML data. You can't directly flatten nested XML data to a CSV file.

  • The data model has both SQL and Non-SQL data sets

    When a data model contains a mix of SQL and Non-SQL data sets (Non-Standard data sets such as web service, HTTP, and XML), the data processor cannot generate a CSV output.

  • The data model has a non-standard SQL query

    When a data model has a non-standard query such as a procedure or a function that returns a reference, the data processor cannot directly generate a CSV output. The non-standard queries returning nested XML structure cannot be handled inside the data engine.

  • The scheduled report has multiple output formats

    If you select multiple output formats for the scheduled report, the data processor doesn't generate the CSV output.

  • The scheduled report uses bursting

    Splitting and delivering data requires raw XML data. The data engine cannot generate the CSV format for bursting reports.