Transcript: Using APRA Connect Taxonomy Artefacts

This instructional video explains how to use the taxonomy artefacts when preparing data for submission in APRA Connect.

Transcript

The following video explains how to use the taxonomy artefacts when preparing data to be submitted via APRA Connect. The concepts demonstrated in this video can be used by all industries. However, when there are industry specific features, I will call this out.

This video can be paused if necessary, and a transcript is also available.

The artefacts can be found on the APRA website and are grouped into one zip file per industry and a change log is maintained to capture all the amendments APRA has made over time.

The taxonomy artefacts consist of one data dictionary for all collections, a reporting taxonomy per collection., one validation rules spreadsheet per industry and an XSD file per collection.

The change log is intended to centrally capture a full description of all changes made to the taxonomy artefacts over time and include changes to the schemas, validation rules, including new or deleted rules, rule restrictions and release notes that may describe any known issues or additional information.

Before commencing the demonstration, I'm going to walk you through a high level summary of each type of artefact and some key information.

First is the data dictionary. There is one dictionary covering all collections, regardless of the industry the collection belongs to.

The elements that are listed in the dictionary can be used by any collection. Every APRA connect element is listed in the dictionary. The status will tell you if the element is released or deprecated.

There are five base data types that all data types are categorised by: boolean, date, number, string, or enumeration.

Each element will have one of these base data types, APRA Connect will validate that the correct data type has been applied. For example, text cannot be reported for an element with a monetary data type which uses the number base type.

The number and string properties will determine the length and content you are allowed to enter.

Some data types are enumerations, also known as a dimension.  A separate worksheet lists all the enumerations and the full list of the members for each enumeration. The key must be used when uploading to APRA Connect.

The reporting taxonomy artefacts share a lot of similarities with the same worksheets in the data dictionary.

The main difference here is that only the elements, data types and enumerations applicable to the specific collection will be listed.

The schema worksheet contains the version number and an embedded holes restriction file. The holes file will contain one worksheet per form or table.

The element worksheet lists the objects used by the selected collection.

The data types worksheet lists only the data types used by the selected collection.

The enumeration worksheet lists only the enumerations used by the selected collection, but is important to note all the members of the enumeration will be listed.

If you want to see which subset of members are used on each form, you will need to refer to the specific form layout or the corresponding worksheet in the holes file. I will highlight this in the upcoming demo. You will need to use the key in your upload file whereas the label is used on screen in the APRA Connect interface.

The Form Set worksheet contains this form set version and the Folders and Forms worksheets contains a summary of properties including the form names and form codes.

The Rules worksheet lists the rules only used by the selected collection and includes a plain English definition of what the rule is checking. The EntityDetails and form layout worksheets are very important if you plan to upload your data to APRA Connect using Excel.

These worksheets are the only worksheets that you should include when you submit your data via APRA Connect, and the structure of these worksheets should not be altered. In particular, rows and columns should not be added or removed between the headings. On the EntityDetails worksheet add your ABN, Reporting End Date and if required Reporting Consolidation. The entity name is not required.

Depending on the collection, there may be one or more form layouts depicting the design of the form and they are your template for submitting data via Excel.

Each form layout will have the following characteristics.

The worksheet name - it is important and should only be changed by entities who are reporting multiple copies of the same table. For example, Life Insurers will report one worksheet for each fund in their portfolio for collections like the Statement of Financial Position. 

The starting row must not be altered, particularly for open tables.

When multiple rows are reported, otherwise you run the risk of your data being incomplete.

The hyperlinks and notes will provide specific enumeration members that are allowed for that particular table.

The last two files that make up the full suite of artefacts are the validation rules spreadsheet and the XSD files.

The validation rules spreadsheet contains a full version history of the rules, their unique identifier, a plain English definition of what the rule is checking for, and whether the rule is an error or confirmation.

The XSD is an XML file that must be used to validate your XML file before uploading to APRA Connect, the XSD will ensure the enumerations have been applied correctly. It also contains the GUID and version number.

This demonstration will take an in-depth look at the data dictionary and it’s features.

The Elements tab lists every single object across every collection in APRA Connect, regardless of whether it is a metric or an enumeration. 

Each element has a description that defines the object. You can use the hyperlinks to navigate from a specific element to its data type. If I click this text 400 link, I can see from the properties that this is a string field. With a maximum length of 400 characters. the purpose of the data types worksheet is to capture all the properties like the length or how many numbers are allowed after the decimal place.

APRA Connect will enforce these properties when you either upload or manually enter data.

Back on the Elements worksheet, if I click the hyperlink for asset class characteristic one, I'm taken to the data types worksheet where I can see this is an enumeration. If I follow this hyperlink, I would be taken to the enumerations worksheet.

When uploading data, APRA Connect is expecting the key to be submitted exactly as it is shown in these artefacts. An error will be triggered when uploading incorrect keys. The label is the value you will see in the drop down when you're using APRA Connect interface. Sometimes the key in the label are the same, but more often than not they will not match each other.

From here I can see every possible enumeration member, the requirements of the individual table in a collection will determine which members are allowed, but more on that shortly.

The first reporting taxonomy artefact I'm going to demonstrate is the SRS 705.1 Performance Benchmark.

From the Schema worksheet you can view the version and it also contains the holes restriction file.

I'll come back and explain the holes file shortly.

You can see that the list of elements is much shorter compared to the data dictionary. That's because this artefact only contains the elements that are relevant to this collection. The elements are grouped by the table they appear in, like the data dictionary the description defines the element and the guidance column may include extra guidance specific to this collection.

The Elements worksheet also includes the mandatory and primary key columns. If an element is mandatory, it cannot be left blank and the combination of elements that are part of the primary key must be unique. 

Before you upload your data, it is recommended that you review your data for duplicate combinations or missing mandatory fields.

You can use the hyperlinks to help you navigate to more specific information. In this collection the only enumeration is Return Measurement and there are four possible members.

The Data Types worksheet only lists the data types used within this collection, much like the data dictionary the properties for each data type is shown here. The properties of the percent 4 decimal places provides a description for how this data type should be entered and it shows how many digits are allowed before and after the decimal place.

The Rules worksheet contains the current list of validation rules for this version of the collection. Each rule has a unique identifier and the business definition describes what the rule is testing for.

The Form Set worksheet shows the version of the form set.

As you start preparing your data, it is important to remember if you plan to upload your data to APRA Connect using Excel, you only need to include the entity details and form layout worksheets in your upload.

I have highlighted them here in yellow.

These worksheets should be used as a template and for uploading data via Excel.

It is extremely important that you do not alter the layout of any of these worksheets.

On the EntityDetails worksheet if you hover your mouse over the Notes, you'll see that the entity name is dynamic, that means it will be derived by APRA Connect but the ABN and Reporting End Date are metrics and you are expected to provide this information.

Both Table One and Table Two are open tables. In other words, we are expecting any number of rows of data to be entered after the headings. It is vital that you do not alter this layout. In particular, do not remove any of the heading rows. APRA Connect is going to load the data from row five onwards. If you remove a heading row and enter data on row 4, APRA Connect will ignore that data.

The starting row of the open table may be different for some collections and these form layouts in the reporting taxonomy artefact are your source of truth and should not be altered. 

Like the EntityDetails worksheet, the notes and hyperlinks can help you prepare your data.

The metrics are elements that you enter data into.

The dimensions are the enumerations that have a set valid value. 

On Table 1 the Return Measurement Type allows all values as denoted by all members, whereas on Table 2 the Return Measurement Type lists only two of the four valid values and in the other enumeration in this table, all members are allowed.

You can also see this on the holes file embedded on the schema worksheet.

This is a worksheet for Table 2 and you can say the same to enumeration members are listed and all members are allowed in the Return Measurement Option Type. On Table One, all members of Return Management Measurement Type are allowed. The value of 1 indicates that a value is permitted for that metric.

Now let's take a look at LRS 300 Statement of Financial Position.

This collection consists of two closed tables. Closed tables have a fixed layout. Unlike open tables, the number of rows or columns don't change. Table 1 in this collection is repeatable. That means Table One needs to be repeated for each fund in the life insurer or friendly society portfolio.

The Folders worksheet indicates if any tables are repeatable. You will see what that means for you a little later in this demonstration.

Derived fields are typically more prevalent in closed tables and have a grey background. The Note uses the expression dynamic text followed by the formula for the derived field.

Closed tables often use spaces for formatting purposes and blank cells are denoted with a cross. For the metric fields, don't forget these are the fields that you will enter data into. The Notes will include the name of the metric and any enumeration, and in this collection Table One is also completed for each fund within the life insurer or friendly society. For this reason, you will need to create multiple copies of the form layout and enter a sequential number in brackets at the end of the worksheet name.

Here is an example workbook I've prepared earlier for a life insurer who has three funds. I have already created a copy of Table One and renamed both to include sequential numbers in brackets. Now I just need to create a third instance of the table.

As you start preparing your data, it is important to remember just like open tables, you only need to include the EntityDetails and form layout worksheets.

On their EntityDetails if you hover your mouse over the Notes, you'll see that the entity name is dynamic. that means it will be derived by APRA Connect but the ABN and Reporting End Dates are metrics and you are expected to provide this information.

Both Table One and two are closed tables. In other words, the layout is fixed and should not be altered in any way.

The only part of this header area that requires an input is the life company fund name.

As mentioned in the OpenTable demo, metrics are elements, so you enter data into. In this case, for each of the three Table One templates, the fund name is also required.

Grey cells and cells with a cross are to be left blank.

Enter data into the metric cells and once you've entered all your data, you can follow your own internal processes to validate and review the submitted data.

To recap, the data dictionary covers all collections for all industries.

The reporting taxonomy artefacts contain the templates for uploading data to APRA Connect using Excel - do not alter either of these templates.

There are separate validation rules spreadsheets for each industry.

And finally, if you upload your data using XML, the XSD files must be used to ensure the file is valid before uploading to APRA Connect.