Data publications are a data integration option that allow you to extract all of the data within a solution and load it into an relational database. You can schedule publications to run at set times.
Data publications enable 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.
Data publications are available for on-prem, hosted, and SaaS deployments.
On this page
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 data publication.
- Attachment
- Cross-Reference/Related Records
- Date
- External Links
- First Published Date
- Image
- IP Address
- Last Updated Date
- Matrix
- Numeric (Prefixes and suffixes cannot be published.)
- Record Permissions
- Record Status
- Sub-Form (Upon publication, sub-form field values display a link to the table containing the sub-form data.)
- Text
- Tracking ID (Prefixes and suffixes can be published.)
- User/Groups List
- Values List (Fields that display the value "No Selection" contain no value in the published version.)
Note: It is recommended to include Cross-Referenced and Related Record applications and questionnaires in the selected solution for a Data Publication.
Note: Calculated fields publish the current value of the field, not the calculation formula.
What type of database can you publish to?
Archer currently supports publishing to either SQL Server or Oracle Database. The data publication only creates tables within the database; the database itself must already exist before you publish.
Note: Consider the amount of data that you may be publishing from Archer when you set your initial database size.
What is created in the external database?
The Data Publication Service (DPS) converts records in your system applications into a relational database structure.
- The data publication process uses the alias value to name database elements. Using the alias values ensures naming consistency, independent of the display name.
- Applications, questionnaires, and sub-forms become database tables.
- Fields become columns in the table of their parent entity (application, questionnaire, or sub-form).
- Any field type that can contain multiple values becomes a separate table (Attachments, External Links, Image, Subform, Users/Groups, and Values List). All other field types store their information in the main table.
Example
Assume that you have an application in the solution with an alias name of AppA. This application contains an attachment field called AttachIt, a text field called TextIt, and a Values List field called ValueIt. When the publication runs, three tables are created. The first is the main table and the table name would be AppA. This table contains columns, such as TextIt. A second table is created for the attachment information, and it would be called AppA_AttachIt. A third table would also be created for the Values List field, and it would be called AppA_ValueIt.
Subforms are a little more complex, as they are contained within an application, but have their own set of complex fields. For example, assume that you have a subform in AppA called MySubform. This subform contains an attachment field called SubformAttachIt, and a text field called SubformTextIt. In this case, two more tables would be created: one called AppA_MySubform, which contains the text field (and a few other fields), and the second table would be for the subform attachment content, and it would be called AppA_MySubform_SubformAttachIt.
Cross References have what is called a "join" table or "link" table. This table contains the content IDs from the two applications that are linked together. The table names of the join tables are a combination of the two applications.
Note: This table only exists if both applications that are part of the relationship are included in the solution being published.
Who can publish data?
Through an access role, you must have Create, Read, and Update rights to the Integration: Manage DPS page.
Security
The Job Engine Service must have access to the necessary ports and protocols for the system where your external database is located. For more information, see the Archer Security Configuration Guide.