Exporting a Report Model
Last updated
Last updated
Astera makes it easy to export data from a report model directly to a number of different destination types such as Excel and CSV. Moreover, it supports the export of data to a wide range of on-premise and cloud databases including SQL Server, Oracle, DB2, Sybase, MySQL, etc.
In this document, we will learn how to export data in a report model to three different destination types.
In this example, we have incoming data extracted by the report model below.
Download the sample data file and report model here.
The model contains a hierarchical structure with three data regions and multiple fields.
Click on the Preview Data icon placed in the toolbar above Astera's designer. A Data Preview window will open, displaying the extracted data.
Let’s export the data extracted by this report model to an Excel destination, a CSV file, and a SQL database table.
Select the Create New Export Settings and Run (to Excel) icon in the toolbar under the Model Layout panel.
A configuration window will open. This is where the export properties are defined.
Provide a File Path where Astera will save the exported data with a .xlsx extension.
Once you have provided the destination file path, you can choose multiple options from the export setting window to configure the data export file.
First Row Contains Header – directs Astera to read headers from the source file.
Worksheet – specifies the title for the worksheet to export the data to within the Excel file.
Append to File(If Exists) – adds the exported data to an existing Excel sheet without overwriting the data it contains.
Write to Multiple Files – saves the exported data to multiple files instead of one single file. Specify the same file in multiple Excel destinations and Astera will create a single file with multiple worksheets and write all data to that file.
Rules for Filtering – specifies a criterion to export only filtered records to an Excel sheet. On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. This feature is explained in detail in the next section.
Click Next. This is the Layout Builder screen where the extracted data fields can be modified as per the requirements.
Click OK to start the export of data. View its job trace in the Job Progress window.
Astera will generate the Excel file and provide its clickable link in the job trace.
The export to an Excel sheet is complete.
Select the Create New Export Settings and Run (to CSV) icon placed in the Toolbar under the Model Layout panel.
A configuration window will open. This is where the export properties for CSV files are defined.
Provide a File Path where Astera will save the exported data with a .csv extension.
Once the file path has been provided, move on to other options to configure the exported file.
First Row Contains Header – directs Astera to read headers from the source file.
Field Delimiter – allows users to select a delimiter from the drop-down list for the fields.
Record Delimiter – allows users to specify any delimiter for the records within fields.
Encoding box – allows users to choose the encoding scheme for the CSV file from a list of choices. The default value is Unicode (UTF-8).
Text Qualifier – is a symbol that identifies where text begins and ends. It is used specifically when importing data rather than exporting.
Apply Text Qualifier to All Fields – adds the specified qualifier to all the incoming fields.
Null Text – directs Astera to replace a certain value in the report model with a null value.
Append to File(If Exists) – adds the exported data to an existing CSV file without overwriting the data it contains.
Hierarchical Destination – sorts the incoming data into hierarchies where necessary.
Write to Multiple Files – saves the exported data to multiple files instead of one single file.
Rules for filtering data – specifies a criterion to export only filtered records to a CSV file. On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. This feature is explained in detail in the next section.
Click Next. This is the Layout Builder screen where the extracted data fields can be modified as per the requirements.
Click OK, this starts the export of data. View its job trace in the Job Progress window. At the end of a successful run, Astera will generate the CSV file and provide its clickable link in the job trace.
The export to a CSV file is complete.
Select the Create New Export Settings and Run (to Database) icon in the Toolbar under the Model Layout panel.
A configuration window will open. The first screen is the Database Connection screen. If a connection has been recently set up, access its configuration settings through the Recently Used option.
Select the required Data Provider from the drop-down list. In this example, we are exporting data to a SQL Server database table.
Establish a connection with the database by filling in all the necessary details.
Click on the Test Connection button and Astera will notify the user upon successful connection.
Click Next. On this screen, either pick an existing table or create a new table for export data. In this example, we have created a new table called Invoice_FurnitureMart.
Once a database table has been specified move on to other options to configure the database table as per your requirements.
Define Input Ports for Mapping – defines a writing strategy for the database table for each record. For example, if the data is being exported to an existing database table then users can also enable update and upsert options for each record.
Database Options – deals with system-generated keys and null values.
Data Load Options – specifies the type of insert of your records into a destination database. Records can be inserted in bulk or each record at a time.
Click Next. This is the Layout Builder screen where the extracted data fields can be modified as per the requirements.
Click OK, this starts the export of data. View its job trace in the Job Progress window.
The export to a SQL Server database table is complete.
Astera enables users to specify a criterion to export only filtered records to the destination file. This option is available when data is exported to an Excel sheet or a CSV file.
Select the export destination of your choice. In this example, data in a report model is being exported to an Excel sheet. On its configuration window, after specifying the properties, expand Rule for Filtering Data.
On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. Expand the Order_8 node to access the fields of the extracted data.
Now, we only want to export data that contains SOFA in the Items field. For that, we have to specify an expression in the expression builder.
In the expression builder, write Contains(”SOFA”, Items). Click on the Compile button to verify whether the expression is successful or not.
Click OK to close the export settings window.
Now click on the Run Selected Export icon placed in the toolbar under the Data Export Settings panel.
Export will begin. Observe its trace in the Job Progress window.
At the end of a successful export, Astera will generate the Excel file with filtered records and provide its clickable link in the job trace.
This completes rule-based filtered export.
