Smart Data Publications

Smart data publications are a data integration option that allow you to download and extract all of the data within a solution. The exported data can be used for data analysis and modeling. You can schedule publications to run at set times.

Smart data publications allow you to do additional reporting, data analysis, and modeling on your Archer content.

If you want to extract data within an application, consider using the Content API.

This feature is only available to Archer SaaS customers.

What data can you publish?

Publications work at the solution level.

Tip: If you want to publish only particular applications within a solution, or you want to publish applications from multiple solutions, you can create a new solution with just the applications that you want to publish. You may want to delete the default workspace that is created for that solution.

Within those applications, questionnaires, and sub-forms, the following field types are supported for smart data publication.

How is data available?

Exported files are stored in the Archer SaaS instance and are available on-demand for 30 days using the API or Website.

What is created in the output file?

The Smart Data Publication (SDP) converts records in your system applications into a .sql or .csv file structure, depending on the selected file schema, relational or flat. The default file schema selection is relational, which is similar to the Data Publication output.

• The smart data publication process uses the alias value to name file elements. Using the alias values ensures naming consistency, independent of the display name.
• Applications, questionnaires, and sub-forms are exported into their individual file structures.

File schema

The following table describes the different file schema types and their output file structures.

Schema type	Description	Output file structure
Relational	Produces normalized data in multiple tables per application, questionnaire, or sub-form, based on the field type.	SQL
Flat	Combines all fields in an application, questionnaire, or sub-form, into a single de-normalized table.	SQL, CSV

CSV file structures

CSV file structures display data in columns that represent the exported fields in each application, questionnaire, or sub-form.

The following table describes the different field types and values of the column-formatted data.

Field Type	Column Value
Cross-Reference	ID of the cross-reference record created in the referenced application
Attachment, Image	Name of the attachment or image
Text, Numeric	Values of the respective fields

SQL file structures

SQL file structure outputs in 2 different file types: DDL and DML. Both file types contain the .sql files for their respective applications, questionnaires, and sub-forms.

• DDL files contain scripts to create the database table where the exported fields are displayed in column format.
• DML files contain scripts to insert or modify field values in the database table.

The following table describes the differences between how Archer represents the table columns in the SQL file structure output for both the Relational and Flat file schema types.

Column	Relational Structure	Flat Structure
ContentID	ContentId	Not present
Tracking_ID	The field is part of the same master table for both schema types.
First_Published_Date	First_Published_Date	First_Published
First_Published_UserName	First_Published_UserName	Not present
First_Published_DisplayName	First_Published_DisplayName	Not present
Last_Updated_Date	Last_Updated_Date	Last_Updated
Last_Updated_UserName	Last_Updated_UserName	Not present
Last_Updated_DisplayName	Last_Updated_DisplayName	Not present
Text	The field is part of the same master table for both schema types.
Numeric	The field is part of the same master table for both schema types.
IP Address	The field is part of the same master table for both schema types.
Record Status	Shows Integer	Shows Value
User Groups List	Mapping table	Shows name only, delimiter-separated
Record Permissions	Mapping table	Shows name only, delimiter-separated
Image	Mapping table	Shows name only
Attachment	Mapping table	Shows name only, delimiter-separated
External Links	Mapping table	Shows name only, delimiter-separated
Matrix	Mapping table with Enum table	Shows value only, delimiter-separated
Values List	Mapping table with Enum table	Shows value only, delimiter-separated
Voting	The field is part of the same master table for both schema types.
z_metadata	Present	Not present

Example

Assume that you have an application, AppA, with a level of AppA, in the solution with an alias name of AppA. This application contains several fields including an Attachment field, Text field, Sub-form field, and a Values List field. When the smart data publication runs, .sql or .csv files are published, depending on the output type selected in a publication.

The exported files are named using alias values:

• Applications: "AppAlias_LevelAlias." 

• Sub-forms: AppAlias_LevelAlias_SubformAlias

The application AppA uses the file name AppA_AppA, and the subform uses AppA_AppA_Subform.

Who can publish data?

Through an access role, you must have Read rights to the Integration: Manage Smart Data Publication page. Read rights also allow you to download the smart data publications.