6.3.1 SQL Server - Introduction

Report Authority's SQL server adaptor can import data into a document based on any reporting framework from two tables in a SQL Server database.  

The two tables are:

1. AS_DPMContext
2. AS_DPMData

AS_DPMContext Table

The context table is used to define a particular dataset with the following fields:

1. ID. This is the unique ID for the dataset and will be used as the foreign key in the AS_DPMData table.
2. Entity.  This must contain the entity code to be used in the XBRL instance document, such as a LEI Code, FRN code (in the case of a UK entity) or any other local identifier used in Report Authority.
3. Year. This contains the calendar year of the document's reference date.
4. Period. This contains an integer representing the calendar month of the document's reference date. 

In the example above, two data sets have been defined, both for entity code "123456", one for the period 2019-6 and the other for 2019-12.

AS_DPMData

The data table contains the data for all datasets for all reporting frameworks:

1. ContextID. This is the foreign key and refers to the ID of the dataset in table AS_DPMContext.
2. Table. This contains the table code, e.g. “C 00.01” or “S.01.01.01.01”
3. Sheet.  This contains the sheet code for the specific datapoint.  E.g. “GBP”, or “SOUTH AFRICA”.  Please note that this only applies to dimensional templates.  Templates without dimensions, for example COREP “C 01.00” or Solvency II “S.06.01.01.01” do not have sheets, and the value in these cases will be NULL.  If the table uses multiple dimension members for sheets, each dimension should be delimited with a ",".  See Dimensional Tables below for a more detailed description including examples. 
4. Row. This contains the row code of the data point e.g. “010” or “R0010”.  For variable row tables, this will contain the row dimensions (delimited with a "," if multiple dimensions). Please see Variable Row Tables below for a more detailed description including examples.
5. Column. This contains the column code of the data point e.g. “010” or “C0010”.
6. Value. This contains the value of the data point.  Because data points can be either text, numeric or dates, this column is of type string.  For numeric and date values, we use invariant culture to interpret the canonical value.  
   
   For numeric items, the value should not contain any thousand separators and the decimal symbol must be ".".  The "," in "155,55" would be ignored and the value would be interpreted as "15555.00".  
   
   For dates, the date format must be in yyyy-mm-dd format, such as 2020-12-31.

Dimensional Tables

Some tables are dimensional and can be repeated - once for each dimension member. An example is C 09.01.a in the COREP Own Funds reporting framework.  In this table the sheet can be repeated for each country:

In this case, the value in the Sheet column must agree to one of the dimensions in the list, e.g. "AUSTRIA" or "DENMARK".

To extract the full list of available dimension members, please refer to Creating Database Scripts in this article.

Dimensional Tables With Multiple Dimensions

Some tables use multiple dimensions, for example, S.16.01.01.01 in the Solvency II taxonomy:

In this case, a value for each dimension must be provided in the Sheet column delimited with ",".

Variable Row Tables

Variable row tables contain at least one row dimension.  The row dimension value must be inserted into the Row column.

Some variable row tables have multiple row dimensions, e.g. T 30.00 from the EBA's Resolution framework:

In this example, the value in the Row column will be all 4 row dimensions delimited with ",", i.e. "1,1,1,PS".