Tool for Undertakings
Objectives, solution architecture and development status
2
Overview of the solutionnTool for Undertakings (T4U) and NCAs toolkit
The main goals of the XBRT (component of which is the T4U) in order of importance are:1. to easily create, edit/correct/complete and validate XBRL instance documents, in order to help Undertakings without XBRL knowledge to
implement Solvency II harmonized quantitative reporting in XBRL,2. to provide a reusable solution for other projects and specifically for NCAs’ local requirements,3. to provide a reusable solution for internal EIOPA and NCAs’ needs to manage XBRL reporting.
eXtensible Business Reporting Toolkit - a set of tools/services developed by/for EIOPA to manage the process of data description, exchange/collection, validation, storage, analysis and decision making, in particular related to the Solvency II information requirements modelled according to the DPM and exchanged in the XBRL format. Some components of this solution can be used by the Undertakings (T4U) or NCAs (XBRT component for national extensions, etc.).
Excel Add in T4U – component to be used by the Undertakings to manage (produce, edit/correct, review, etc.) their XBRL filings with an Excel Add In
NCAs
Undertakings
Windows T4U – component to be used by Undertakings to manage (produce, edit/correct, review, etc.) their XBRL filings with a Windows application
NCA Toolkit – component supporting NCAs with extensions and second level reporting
3
Work streams first betas
TFU Work stream Windows T4U (June Release)
o provide a user friendly interface for rendering Solvency II templates, manual input of data and validation;
o create, read and validate XBRL reports;o validate and view the created XBRL reports using a third-party viewer and validator;o database-based: built around a database with architecture similar to CRDIV, and with
structures to facilitate extensive data migrations;o import capabilities from Excel (plain Excel files).
Excel Add In T4U (August Release)o provide input forms for the Solvency II templates with functionalities similar to the Windows
application, but based on Excel. o developed as a back-up solution in case the Windows based application does not meet all the
user requirements as agreed in the functional requirements (the excel import function); in comparison to the Windows application this will be:
• easier to integrate as it will be Excel based;• better for automatic migration of data to Excel structures;• more complex to develop and to deploy.
NCA Toolkit Work stream (September Release) - as a second work stream the NCA Toolkit will be developed to extend the above and add the following functionalities to the tool:
o handling of XBRL reports received by the NCAs from the undertakings and facilitating second-level reporting to EIOPA;
o provision of guidelines on the creation of new input templates and/or the algorithm to generate input templates for NCA extensions;
o provision of tools that may help NCAs when creating extension taxonomies;o provision of basic business intelligence functionalities using external tools.
June
August
September
These timelines correspond to versions made available to EIOPA members. Public version may differ 1 or 2 months.
4
T4U: value for undertakings and NCAs
The Tool for Undertakings (T4U) is a tentative solution aiming to enable small and medium reporting entities as well as European National Competent Authorities to input/load data using common interfaces and manage data using centralised database, designed to process data structures represented in the EIOPA Solvency II XBRL DPM-based taxonomy , in order to produce XBRL reports compliant with the Solvency II requirements.
T4U
XBRL technology: handle XBRL instances received from the undertakings and facilitate second-level reporting
substantial data volumes: structures to create efficient ETL data presentation and input: rendering and modifying data with
user friendly interfaces customising / tailoring data scope collected using T4U: extension
capability data validation: validate data with XBRL and other alternatives data analysis: provide basic BI capabilities generation of XBRL Taxonomy according to Eurofiling Architecture
Help for Undertakings with: Help for NCAs with:
XBRL technology: create and read XBRL instances from source data
substantial data volumes: structures to create efficient ETL data presentation and input: rendering and modifying data with
user friendly interfaces standardising European supervisory architecture: applying
standards common with EBA and ECB data validation: validate data with XBRL and other alternatives
aiming to be in PoC functionality
after-PoC functionality
Legend
PoC functionality
5
T4U functions: in- and out-of-scope
Easy-to-use with multiple data input/load interfaces
XBRL validation (open source component) for
reported data
DPM-based database for metadata and data
storage
XBRL load, validation and creation with database
approach
Generation of XBRL taxonomy according to
the Eurofiling architecture
Generation of XBRL reports according to
Solvency II DPM XBRL Taxonomy (Level 1 & 2)
Support for other XBRL Architectures than EIOPA/Eurofiling
Advanced data analysis
First-level reporting support/services for
extensions
Reports and submissions management
Basic BI analysis capabilities
Second-level reporting support
T4U functionality
Out-of-scope
6
Architecture (Design)
AT
XBRL taxonomyExcel
Add-In
Windows application
DPM Architect
Deci-mate
EBA-like deliverables (PDF, XLS)
5
6
7F
T4U
Other UIs (e.g.
mobile)
Technical annotation
Interim format/tools
W
21
Validations12
Aggregations 16
DPMdict
BLBT
2a1
4
A
D
H
G
9
10
2b
8
Database
Relational Structures
V
3b
DPM-RS map
DR
DPM Metadata3a
EBA MS Access DPM Database
EB
22
AA3c
BC
Applications’ information
Business Rules
Flags (FS concepts), derived values, subsets
(aggregations)
3f
Translations
Designstage
E
Definition of views
DV
Excel RS Views23
RX
Business Templates and Business Logs• modification of content (preparatory/full)• inclusion of row/column codes• translations• flagging (aggregates, FS, derived values, etc.)• business/validation rules notationDPM Dictionary and Annotated Templates• as above (BT+BL) but in structured manner (including
normalization of tables)• business annotation – new approach (including codes,
clear relations to hierarchies)• technical annotation – named ranges and stylesDatabase population• from DPM dict + AT using new business and technical
annotation• reverse engineering of the input• migration of EBA MS Access content to T4UdbDatabase• DPM Metadata – as in EBA + EIOPA S2 T4U specific
structures• Relational Structures – explained later• Validation/Aggregations – defined as metadata using
row/column codes; execution on Relational Structures• Applications’ information – localization and translations of
menu, buttons, messages, …Interfaces• Windows application – reading from DPM Metadata with
Design stage; linking to Relational Structures in progress• Other – first draft of iOS app (reusing code/logic of
Windows application)• Excel Add-In – works restarted now,• Excel RS Views – new concept under considerationLonger term:• XBRL taxonomy generation (with DPM Architect engine)• other supportive deliverables
7
Architecture (Primary reporting)
Interfaces – work in progress on read/write to Relational Structures
Data in Relational Structures• validations (with support of views) on data in Relational Structures• migration of data in Relational Structures (between information requirements versions)
Maps and conversion between Relational Structures and DPM data
Generation/load of XBRL from/to DPM data using Arelle engine + validation if required
8
Architecture (Secondary reporting)
9
Table: mTableTableID TableCode
1365 S.99.12.31.011699 S.44.01.02.01
Table: mTableAxisTableID AxisID
1365 1221365 1231365 1241699 1311699 1321699 133
Table: mAxisAxisID Orientation
122 X123 Y124 Z131 Y132 Y133 X
Table: mAxisOrdinateAxisID OrdinateID OrdinateCode IsRowKey
122 201 10 122 202 20 122 203 30 122 204 40 122 205 50 123 210 10 123 211 20 123 212 30 123 213 40 123 214 50 124 215 131 428 10 true132 429 20 true133 439 30 133 440 40
Table: mOpenAxisValueRestrictionAxisID HierarchyID
124 12132 12
Table: mOrdinateCategorisationOrdinateID DimensionCode MemberCode
201 MET mi2201 BAS x26202 MET mi5203 MET mi10204 MET mi12205 MET mi1210 PFL x12211 PFL x24212 PFL x32213 PFL x43214 PFL x23215 CTP open428 IDC open429 CTP open439 MET mi67439 BAS x12440 MET pi68
Table: mMappingTableID RSTableName RowColumnCode Signature
1365 S.99.12.31.01 PAGE1 s2c_CTP(*)1365 S.99.12.31.01 R10C10 MET(s2md_mi2)|s2c_BAS(s2c_BL:x26)|s2c_PFL(s2c_PL:x12)1365 S.99.12.31.01 R10C20 MET(s2md_mi2)|s2c_BAS(s2c_BL:x26)s2c_PFL(s2c_PL:x12)
…1399 S.44.01.02.01 C10 s2c_IDC(*)1399 S.44.01.02.02 C20 s2c_CTP(*)1399 S.44.01.02.03 C30 MET(s2md_mi67)|s2c_BAS(s2c_BA:x12)1399 S.44.01.02.04 C40 MET(s2md_pi68)
Table: dFactInstanceID Signature Value Unit Decimals
1 MET(s2md_mi2)|s2c_BAS(s2c_BA:x26)|s2c_CTP(eu_GA:PL)|s2c_PFL(s2c_PL:x12) 2345 EUR 01 MET(s2md_mi10)|s2c_CTP(eu_GA:PL)|s2c_PFL(s2c_PL:x12) 345 EUR 01 MET(s2md_mi12)|s2c_CTP(eu_GA:PL)|s2c_PFL(s2c_PL:x12) 436 EUR 0
…1 MET(s2md_mi67)|s2c_BAS(s2c_BA:x12)|s2c_CTP(eu_GA:PL)|s2c_IDC("12") 1001 EUR 01 MET(s2md_pi68)|s2c_CTP(eu_GA:PL)|s2c_IDC("12") 0.15 pure 21 MET(s2md_mi67)|s2c_BAS(s2c_BA:x12)|s2c_CTP(eu_GA:Es)|s2c_IDC("322") 2034 EUR 01 MET(s2md_pi68)|s2c_CTP(eu_GA:ES)|s2c_IDC("322") 0.34 pure 2
Table: 1365_S.99.12.31.01InstanceID Page R10C10 R10C20 R10C30 R10C40 R10C50 R20C10 …
1 eu_GA:PL 2345 345 436
Table: 1699_S.44.01.02.01InstanceID C10 C20 C30 C40
1 12 PL 1001 0.151 322 ES 2034 0.34
S.99.12.31.01
Page PL
C10 C20 C30 C40 C50R10 2345 345 436 R20 R30 345 R40 4567 R50 234
S.44.01.02.01
C10 C20 C30 C4012 PL 1001 0.15
322 ES 2034 0.34
Templates
DPM Annotated Templates Metadata
DPM Data:
DPM and Relational Structures Mapping:
Data in Relational Structures:
T4U DatabaseDPM Metadata and Data vs Relational Structures
10
Technologies used
• EIOPA’s Subversion repository (Apache licence before GitHub was used)
• IIS for the website and Arelle validation service• VM ware for testing machines• Microsoft Excel 2007 and 2013 (For Excel T4U and testing)• SQLite 3.0 Public Domain License (database repository, used
as open source)• SQLite Studio (v 2.1.5) GNU General Public License (Database
manager for SQLite)• SQLite POCO entities and SQLite driver + Entity Framework 5.0
(for data access)• Visual Studio (for all solutions except non windows based)• Xamarin (iOS and non windows interfaces)• C# with .Net framework 3.5 (Windows T4U)• C# 4.0 (Excel T4U add-in)• NetOffice http://netoffice.codeplex.com/ (MIT License)ExcelDNA http://exceldna.codeplex.com/ (Custom open License)• Obejctlistview: for open tables in Windows T4U• Trello, Microsoft Word+ Project + Visio for documentation• Arelle
11
Technical solution implementation
DPM database
· AT and DPM Dictionary information · Taxonomy artefacts information· Validation information· Rendering information· Application information· Multi reports storage based on:
A) “Abstract XBRL model”B) “Classical relational model”
Components for XBRL stream· Loading of XBRL instances into the database· Creating XBRL instance into the database· Sending XBRL instance to a XBRL validator(locally or to a web service)· Load validation results into the database· Database based validations
External components· Business log and Templates· Annotated templates and Dictionary· Taxonomies (including translations)· Instance validators· XBRL Taxonomy creators· EBA Ms Access database
T4U Data Access Layer· CRUD data access for SQLite data base.· DDL access
Uses
Uses
NCA Toolkit(Orchestrates the design phase and the NCA basic functinalities) · AT and DPM dictionary reader and generator· Application GUI form generator· Basic analyses tools· Second level reporting· Database convertor and generator
Windows Form T4U
ExcelAdd-In T4U
Other T4U GUIs
Create Interfaces
Uses
Uses
Uses
Business Application Layers· Interaction with XBRL stream· Execution of database based validation
12
Current Team
Project Management Team:
Project Role Names Project Board Project Sponsor: Patrick Hoedjes
Project Executive: Philip Kitzmantel Business owner: Pierre-J ean Vouette Technical owner: Aitor Azcoaga Senior Supplier(s): HCL, BR-AG
Project Manager: Sudipta Mazumdar (HCL) Project Team:
Project Role Names Team leader/ XBRL expert Bartosz Ochocki .Net/C# developer Arun Venkatesan .Net/C# developer Vijay Rengarajan Database developer Shankar Ganesh Database Designer Karol Minczynski XBRL stream developer Herman Fischer Windows stream developer Nicholas J ory