Extract a Large Volume of Data

You can use Publisher reports to extract a large volume of data.

To extract a large volume of data and generate a CSV output:
  1. Create a data model with a single SQL Query type data set.
    1. If you have multiple queries, combine all the queries into a single query using the WITH clause.

      For example. If you have these Q1, Q2, and Q3 data sets:

      Q1 data set : SELECT DEPARTMENT_ID, DEPARTMENT_NAME,LOCATION FROM DEPARTMENTS

      Q2 data set: SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB,SALARY FROM EMPLOYEES Q3-dataset: SELECT JOB_ID,MAX_SALARY,MIN_SALARY FROM JOBS

      Q3 data set: SELECT JOB_ID,MAX_SALARY,MIN_SALARY FROM JOBS

      Instead of linking the Q1, Q2, and Q3 data sets to form a parent-child hierarchy, create a single data set combining the queries of Q1, Q2, and Q3 data sets as shown below:

      WITH Q1 AS(SELECT DEPARTMENT_ID, DEPARTMENT_NAME,LOCATION FROM DEPARTMENTS),

      Q2 AS (SELECT EMPLOYEE_ID, FIRST_NAME,JOB_ID AS EMP_JOB_ID,SALARY FROM EMPLOYEES),

      Q3 AS(SELECT JOB_ID, MAX_SALARY,MIN_SALARY FROM JOBS)

      SELECT Q1.*,Q2.*,Q3.* FROM Q1,Q2,Q3

    2. Click View Data.
    3. Click Save As Sample Data.
  2. Edit the data model properties to select the Enable CSV Output option.
  3. Save the data model.
  4. Create a report based on the data model.
  5. Edit the report properties and deselect the Auto Run and Run Report Online options.
    If you run the report online, you might encounter performance issues while the large volume of data is processed.
  6. Click List View and select only the Data (CSV) output format for the report..
  7. Save the report.
  8. Schedule the report.
    Make sure you deselect the Save Data for Republishing option in the Output tab.
  9. Submit the job to extract data.
  10. After the report job completes successfully, download the output from the Report Job History page.