DPM2MDM

From XBRLWiki

Revision as of 18:17, 29 October 2013; Ignacio.santos (Talk | contribs)
(diff) ←Older revision | Current revision | Newer revision→ (diff)
Jump to: navigation, search

CEN Workshop Agreement

CEN WS XBRL Experts: Ignacio Santos (Bank of Spain), Roland Hommes (Rhocon), Katrin Heinze (Deutsche Bundesbank)

Contents

Foreword

This document has been prepared by CEN/WS XBRL, the secretariat of which is held by NEN. CWA XBRL 001 consists of the following parts, under the general title Improving transparency in financial and business reporting — Harmonisation topics:

  • Part 5: Mapping between DPM and MDM


Introduction

This document aims to provide an introduction in the topic of creating a conceptual model for storing multidimensional data which is received by XBRL instances that follow the rules defined by European taxonomies published by EBA (European Banking Authority) or by EIOPA (European Insurance and Occupational Pensions Authority).

Disclaimer: The Multidimensional Data Model (MDM) presented in this document is intended to be a starting point for a subsequent modelling process to be adjusted and extended to specific analytical or transactional needs. It solely refers to the concepts of DPM (Data Point Model) and EXTA (European XBRL Taxonomy Architecture) which build the basis of the European supervisory reporting.

The structure of the data model is based on metaclasses introduced in part 1 and 4 of the CWA1 document [26]. The data model represents a relational model using ROLAP (Relational Online Analytical Processing). To ease the understanding between the UML data structures of a DPM (Data Point Model) and the UML class model represented for the description of the European filing rules, the present document visualises the mapping between UML metaclasses and their correspondence in form of database tables in the MDM.

This page consists of seven sections and four annexes. The section 3 shows the aim of this work and requirements for their understanding. In the section 4 depicts the preconditions in the mapping. The section 5 defines the terms used for the mapping from the DPM to MDM. The section six is studied point by point the mapping. The annex A shows the metamodel defined by the EBA (FINREP and COREP) and its mapping in the MDM. The annex B displays the MDM implemented in a relational database. In annexes C and D two examples are shown. And the last section the bibliography referenced is shown.


Objective

The objective of this sample MDM is to provide an starting point into the topic of mapping DPM and XBRL instance structures into a multidimensional database. Based on an easily comprehensible example more complex issues are addressed that would be needed to be taken into account by defining a MDM for a productive usage.


Target Audience

This document is aimed at users of European supervisory taxonomies that have the need to store reporting data based on these data definitions and to retrieve them for analytical or transactional purposes. Database experts should get detailed information about the specifics to be taken into account when modelling multidimensional database structures for storing supervisory data based on XBRL. So, the audience of this document might be financial or economic institutions, agencies or Universities with the intension to provide micro or macro prudential analysis on supervisory data.


Relationship to other work

The reader of this document is expected to be familiar with the principles of data modelling, having a thorough understanding of the concept of DPM as well as basic knowledge about XBRL. The reader is also expected to have knowledge in creating conceptual models for relational and multidimensional databases.


Preconditions on mapping

Types of Database Management Sytems (DBMSs)

In this section is analysed some types of DBMSs where it is possible to store the DPM and a XBRL document. For this, in this section are not analysed all types of DBMSs, only where is possible in a first study to store the DPM and for extension XML or XBRL documents.

The typical solutions are (figure 1):

  • Hierarchical databases.
  • Multidimensional databases.
  • Relational databases.
  • Mixtures, where, normaly, the relational database is the base.


Figure 1. Different types of DBMSs.

A solution is the hierarchical databases (Tamino of Software AG, GT.M, IBM Information Management System (IMS),…), which is based in the hierarchical model, that is to say, databases organised into structure tree-like. In this structure, the data use relationships among their leaves. Each leaf in a superior level has 0..* leaves in the inferior level. And a leaf in an inferior level only has 0..1 leaf in the superior level.

Another solution are the Multidimensional Databases, not based in Relational Databases. In these databases the data are stored in an optimized multi-dimensional array storage, and not in relational format. However, before, it is necessary a process of the information in a cube. These databases have a very fast response time in the queries. Examples of Multidimensional databases are Essbase, icCube, Infor BI OLAP Server, ...

Relational databases. They store the information in relational format. And Mixtures, they are shown below.

In these solutions is necessary to analyse that database transactions are processed reliably. For this a database ought to have the ACID properties. And, not all databases carry out the ACID properties, it is a question that is dependent vendor. These properties are:

  • Atomicity. Each transaction is "all or nothing".
  • Consistency. It ensures that any transaction will bring the database from one valid state to another.
  • Isolation. It ensures that the concurrent transactions results in a system state that would be obtained if transactions were executed serially.
  • Durability. Once a transaction is committed, it will remain so, even in the event of power loss, crashes, or errors.


This page will analised databased that carry out the ACID properties. And, in this way is choosen the Relational Database Management Systems (RDBMS). These databases are very common in the Information Systems Departments of this environment. Examples of these RDBMSs are Oracle, DBII, or MS SQL Server among others.

There are two mainstream solutions for storing XBRL instances and their facts into a relational database system. The question is when Information Systems (IS) obtains a XBRL Taxonomy or a Document Instance, how these documents can be to store with a less cost in resources in a Database. As a Relational Database can only store relational data, and a XML document is not relational, then the mapping is not direct.

The topic to analyse is:

  • Mapping the XBRL document instance in the Relational Model.
  • Storing the XBRL document instance as photo, or a PDF document in the database.
  • Storing the XBRL as XML document or a XBRL document.


Initially, non-all XML document can be mapped to the Relational Model. Examples are LDAPs (Lightweight Directory Access Protocol), or Mail Servers. Although some LDAPs are store in RDBMSs. However, the documents XBRL can be mapped to the Relational Database, as shows a lot of references. But, in the XBRL specification is necessary a very important question the validation. The validation is based in XQuery (XML Query) and XPath (XML Path Language), both languages are based in XML. If the XBRL document instance is mapped to the Relational Model the document Instance cannot validated. Moreover, as theses validations are based in the XBRL Formulas and Calculation specification, the mapping to a RDBMS is not easy nor immediate. Then, there is a limit, the validation. Then, the validation is necessary to make in XML tools. There are a lot of validators of different vendors or open source (Openfiling) in XML. On the other hand, the mapping of document instances to Relational database, for example using ROLAP tool is relatively easy the mapping with different vendors or open source (Openfiling). In this point are had two worlds the XML world and the relational world. If the queries are analysed in both worlds are had:

  • In XML, these queries use XQuery and XPath.

- The end user has difficulties to access directly or through tools to this language of queries.

- The query language is very specific. Experts in this language are necessaries

- The performance and tuning is difficult.


  • Relational Database use the standard SQL.

- The end user can gain admission of an easy way through spreadsheets, or linked tables or other tools.

- The query language is a standard, and is studied in the University for the students.

- The performance and tuning of a Relational Database has been analysed from years.


Fundamental choices

If the XBRL document instance is stored directly in the Database, the problems are the same but the RDBMS is an inferior level. The cases are:

  • Storing as a photo (Blog, or Clog).
  • Storing as a XML Document.


The first case the database is only used as a storehouse. In the second case, storing as a XML document, with functions embedded in the engine of the database. If the XML Document is stored as a XML document, then this means that the manager of Database has embedded these functions in the engine. Since 5 or more years, some vendors have the type XML as Oracle, MS SQL Server or DB2. Depending on the vendors the main features are:

  • Generating XML Instances.
  • Methods or procedures of the XML data type.
  • Queries in XML instances.
  • Processing namespaces.
  • Indexes.
  • Navigation about the document.


XBRL is an extension of XML, but is not XML, then, if a RDBMS of a vendor there is to analyse the cost of implementation, and after the tuning and performance in database.

MS SQL Server has utilities for working with XBRL that is necessary to analyse.

Oracle 11g release 2 works with XBRL documents instances Oracle 11g with XBRL:

  • It manages XBRL content.
  • It can create multiple XBRL repositories and project XBRL data relationally or query it in various ways.
  • Operations of aggregated business and financial reports such as extraction, transformation, and loading (ETL); business intelligence (BI); and online analytical processing (OLAP).
  • The validation is outside to the database Out oracle.


These solutions with both vendors Microsoft and Oracle have to be analyse, the cost, resources, and tuning and performance in the engine of the database.

As in this document storing the XML document (instance) as a whole is not being considered as is storing the instance in a native XML database. Only storing the content of the XML document in a RDBMS is discussed. One can either: -store almost native facts and their aspects, or -convert the facts and the required aspects into a proprietary set of data before storage.

For both scenarios all relevant aspects on the facts will need to be determined from the analyst point of view.

Another consideration for the importance of aspects is to decide if the database will also be the source to generate (the same or new) XBRL instances (More information in Openfiling). More XBRL technically required aspects need to be considered to create a valid instance. When the target is to (re)create instances, special consideration has to be given to any merge processes on fact values. Merged fact values will cause problems for instance creation unless there is an 'undo' (split) routine possible or a structure more complex in the relational model. This can be created as easily as storing both the original fact values and the merged value.


Pros and cons of alternatives

P Native store Convert before store
Quantity of aspects to store (direct from instance) (+)(-) (+)(-)
Quantity of aspects to store (indirect from DTS) (-) (+)
Speed of storage process (+) (-)
Maintenance (mapping table, mapping software) (+) (-)
Analyst queries, degree of difficulty (-) (+)
Analyst queries, speed (-) (+)
Easy handling of new DTS versions (+) (-)
Extensibility towards proprietary XBRL reports (+) (-)
Extensibility towards proprietary non-XBRL reports (-) (+)


Fact definitions: presentation vs DPM

XBRL Taxonomies created with DPM contain two definitions of individual reportable facts:

  • Primaries, dimensions and members have readable labels and optional references to external documentation;
  • Table and axes headers and table footers have generic (text) labels and indicators pointing towards a 'RC' (row-column) value that identifies a cell in the templates that form the basis of the DPM.

Since there is no guarantee that both definitions will match, a reported fact can rely on either definition. It depends if the reporter used a form, based on the table linkbase, or a mapping based on the primaries/dimensions/members combinations. From a theoretical point of view the templates are transformed to DPM and DPM into XBRL concepts. Ergo, the concepts are leading. This has not been stated explicitly by EBA. In order to stay independent from EBA modelling it is best to store both definitions as relevant aspects. The definition texts as such are the only means for a business analyst to create a query and understand its outcome. Definitions that rely on documentation outside the DTS, and is referred to by XLink references, is only available on concepts. Not on the presentation of the table. Linking this information into the database (and query) is outside the scope of this document. In theory such external reference pointers could be created on the presentation, EBA has however not used this feature, it would be XBRL valid to be used.

When using the instance transformation option the definitions have to be manually mapped to the internal definitions. This is a onetime event. The maintenance task is to check every new release of the DTS for changes in definitions regardless where they are being used. Every change needs to be re-evaluated and again manually mapped into the internal definitions. Analyst queries work with internal definitions, their meaning should be clear to the users.

Another point of consideration is that there is no guarantee that what is dimensionally valid in the DTS will be presented as a cell in any table. The other way around, what is in a table is always dimensionally valid, is guaranteed. There needs to be a process to detect such anomalies, either upon loading a new version of the DTS or upon storage of the facts. There may even be a need for a disclaimer that facts reported without a proper 'cell' in a table are being disregarded. In this sense the table linkbase is forming a third validation mechanism of reportable facts (XSD and XDT being the others).

Lastly the introduction by EBA of a new mechanism called 'filing indicators', needs to be thought through. If instance creation from the database is in order, these XML nodes need to be stored too. They are used to ease the validation process of the XBRL formulae. The mechanism indicates from which tables the instance contains facts. Some facts could be placed in multiple tables (e.g. a total in the total table and in its specification table) and different formulae may need to be executed depending on its usage. There is no mechanism in place that links the filingIndicator value to anything in the DTS. So, one could report table 999 that doesn't exist as long as there are no facts reported against it. This makes for little use in back office applications, it only needs to be stored when instance creation is part of the requirements. The table number used stems directly from the templates and the number is accompanied by explanatory texts in the label that is placed on a presentable table. It is not part of any structured part of the taxonomy.


Storing native XBRL facts

Regulators will receive a container file (ZIP) with at least one XBRL instance in it. Depending on internal processes this container needs to be unzipped first and its content evaluated. Validation of the instance is not part of this document. A valid instance is assumed. Instances can represent multiple taxonomies; an assurance statement could be made part of the instance containing the reportable figures. Solutions to prevent or accommodate this are not part of this document. An instance based on a single taxonomy is assumed, referring to a taxonomy that is enabling reportable figures only. An instance can contain Xlink content. This is not discussed in this document. The instance is expected to contain only facts, units, contexts, one schemaRef and filingIndicators.

Technical part Aspects Comment
instance file name optional hash code For NSA's working with assurance solutions
root node xbrli:xbrl characterset, and optional language, version and id
at least one link:schemaRef contains an URI and a location this is considered to be the entrypoint of the DTS for which this instance is being reported.. XBRL allows multiple schemaRef nodes, EBA only one. EBA has determined that the URI represents an absolute location (web address) and the location only the name of the schema file.
optional multiple link:linkbaseRef EBA will not be using these
at least one find:fIndicators this contains multiple find:filingIndicator the value is string based and represents a table. With EIOPA this node is called tableIndicator.
optional multiple contexts using xbrli:context each context must have one ID attribute, one xbrli:entity node and one xbrli:period node. It may contain many xbrli:segment and xbrli:scenario nodes.
xbrli:entity contains an identifier value and its scheme URI value These represent the reporting entity with its unique identifier within the NSA and the owner of the identifiers (NSA).
xbrli:period contains either an instance date or a periodStart and periodEnd date. XBRL allows also 'forever' but EBA has prohibited this use.
xbrli:segment and xbrli:scenario container contain dimensional aspects and/or proprietary XML schema based content EBA allows only xbrli:scenario to be used and no proprietary content. The dimensional aspects consist of a set of dimension and member QNames and/or a dimension QName with a typed member QName AND its value.
optional multiple xbrli:unit each unit must have one ID attribute. It can hold either one measure or a set of numerator/denominator. These are all QNames. Each QName must have a value that goes with it.
optional multiple facts a fact is represented with a QName (a primary concept in the DTS). It holds a contextRef and unitRef attribute (the latter only on numeric typed concepts). It may hold a decimals, language, nilable and ID attribute.

For definition of the fact aspects the following may be of interest: Each concept (primary, dimension, member) will have at least one label, the standard label. There may be more types of labels to a concept. A label is defined by its role (the 'type') and the language it is in. Multiple labels of the same language and role may occur. EBA will provide only the language English and only one occurrence on each role. The label texts may contain special characters. Within a table in the DTS, any cell defined by a set of primary, dimension/member combinations may have multiple labels attached to it. These labels are also represented with a role and language. EBA will again utilize only one occurrence of text in each role per language, the language being English.


Dimension/defaultMember

Special attention needs to go to default dimension members. All EBA defined dimensions will have a default member. Often the definition of this member reads 'Total/Not applicable'. The XBRL specification describes that any default member that is discovered when starting to discover the DTS from the fact, is eligible for the default member. Even if that dimension is not used on the fact, even when the fact is not dimensional at all. In theory this means that all defaults apply to all facts since a single entrypoint will discover the whole of the EBA DTS. With some common sense a limitation can be applied that default members apply only on the facts reported in a certain table, when that table is using the parenting dimension. Logic could even go further stating that individual cells can be evaluated if the default member makes any sense at all. If not, the 'definition' of 'Not applicable' could be read in which case the dimension and member are not appropriate on the fact at all. In all other cases the default member applies to the fact and needs to be stored by an alternative (to storing only data from the instance) process.

Obviously these default dimension/member combinations must be identified in storage since they are not allowed in the instance.

XML schema also allows nodes to be identified carrying a default value. Especially when typed dimensions are being used there could be a typed element that carries a default. The EBA DTS does not use this option.


Options

XBRL allows for more presentation texts to be added besides primary, dimension, member, table or axis. These texts could be part of the definition of a fact. Careful evaluation of the taxonomy in a XBRL enabled tool using both XDT and TLB specifications can reveal these texts. If they are part of the definition they need to be stored or used for creating the mapping to local data elements.

  • Linkrole labels
  • Non dimensional abstract concept labels used for hierarchical presentation inside an axis
  • XBRL technical concepts have labels: domain, hypercube
  • EBA proprietary concepts have labels: module, framework, tableGroup, taxonomy, family


Versioning

When a new version of the DTS is being released, the EBA has chosen to include two special attributes on every concept: creationDate and modificationDate. Up to the public release DTS of September 18th 2013, there were no modificationDates present and the creationDate was increased on each new version. In theory these dates could be the trigger to signal any change in definition of the concept but if the mechanism is not used other ways to detect changes must be found. Another matter is that there is no such set of dates on the labels that form the table, which can be equally regarded as representing (a part of) the definition. For this part of the DTS a detailed 'diff' function needs to be designed. It is clear that every definition change brakes the trend on any reported fact. Manual intervention on mapping to local sources must be undertaken.


Changes on fact values

If the NSA has the authority to change reported fact values, they must be aware that recreating the original instance may be cumbersome, unless appropriate versioning mechanisms have been placed conserving the original fact values. Special has to be taken on business rules that have defined by the DTS author on such a fact. The change in value may trigger a business rule. These rules can however only be executed on an instance, not the RDBMS.


Terms and definitions

For the purposes of this document, the following terms and definitions apply. The terms definitions used in the mapping with Data Point Model are inspired by vocabulary already known through their use for describing multidimensional databases and data warehouses. IT specialists originally introduced these terms. However, for an understanding and creation of Data Point Models they are established in the language of business specialists as well.

In this section are shown the set of definitions necessaries for mapping the DPM in ROLAP. The majority of the definitions are obtained of [6] [7] [8] [9] [10]. When the definition is in the area of CEN WS XBRL (Main Page) [11] [22] only is shown a hyperlink to definition.

The terms used directly or indirectly in the mapping of DPM in the MDM are:

  • Concept.
  • Data Point Model.
  • Dimension.
  • Domain.
  • Family.
  • Framework.
  • Item.
  • (Domain) member.
  • Metric.
  • Namespace.
  • Owner.
  • Perspective.
  • Public elements.
  • TableGroup.
  • DataPoint.
  • DataCube.
  • Module.
  • Hypercube.

A hypercube is an abstract item declaration in the xbrldt:hypercubeItem substitution group. A Hypercube is an ordered list of dimensions, defined by the set of zero or more dimension declarations linked to the hypercube by hypercube-dimension relationships in a dimension relationship set, and ordered according to the order of this relationship [10].

In the DPM a hypercube is reflect in the DataCube. A DataCube is a set of DataPoints with its appropriate Dimensions and Members.

A hypercube in the MDM is a set of pairs <dimension, attributes of dimension> and calculated attributes defining one or more facts [19].

  • Taxonomy.
  • Context.

The context element contains information about the entity being described, the reporting period and the reporting scenario, all of which are necessary for understanding a business fact captured as an XBRL item [6].

In the MDM, the context is defined as a set of dimension of a fact or group of facts. A context belongs to an entity or financial institution, for a period, a meaning for the business (segment), and a scenario. The scenario shows the specific pairs of dimension and the dimension attribute of business logic [9].


Mapping from Data Point Model to Multidimensional Data Model

Economic-financial information in the global economy in which we find ourselves is increasingly important. This information has semantic content and must be easy to process, quickly transmittable and reliable. Since the late 90's some specifications of transmission of economic information show up. XBRL represents business information which is multidimensional, specilly in the European model, is for this that the logical location for its storage is a Data Warehouse (DW) [25].

The Multidimensional Data Model (MDM) is a Conceptual Model and the Relational Model as the Data Point Model (DPM) is a Logical Model. The difference is that the Conceptual Model is nearest of the Universe of the Discourse, nearest the requirement of business user. And the Logical Model is nearest the Physical Model, the implementation in XBRL or in Database.

This page tries to help to design the Economic-financial information of reports. For that, this set of pages are designed for helping to users of Information Systems to create taxonomies using the DPM. But, in a parallel way to map to the Relational Model using the MDM trhough the ROLAP tool, Relational Online Analytical Processing.


Introduction

This section presents the mapping between the DPM and the Relational model through ROLAP. In this mapping is not expected the transformation to XBRL taxonomies, however is possible its conversion [20]. Moreover, also, in this transformation is not established any process of validation. It is only mapped the DPM structure in the Relational model. However, it is expected that the reader of this document can understand better the DPM or even that the reader can store the DPM in a RDBMS (Relational Database Management System), using the MDM.

Like the aim of this document is to obtain a star model from the DPM. That is to say, the DPM is mapped to the MDM in Databases. The figure 1 shows the MDM of the DPM.

The object FactTable is the DataPoint, in the MDM is the fact table. It is a star model, because, to fact table goes in three dimensions, BaseDomain (set of primary items), Taxonomy and Context. To dimension Taxonomy goes in the dimension Framework. To the context goes into the dimension Context_Dimension_DimensionAttributes.To the last dimension the set of dimension/attributes of dimension. And, to the set dimension/attributes of dimensions the dimensions end DimensionAttributes. Also, it is possible to add the dimension familie to Dimensions, that it is not drawn, according to not complicate the drawing.


Image:Presentacion2U F13Peq.jpg

Figure 1. Star model of the DPM using ROLAP tool.


In the annex B is shown its implementation in a RDBMS, moreover, it is also displayed the diagram of this implementation.

There are a lot of bibliography about the mapping from different sources to a relational database especially from XML [12] [13] [15] [21], and about query in heterogeneous sources is interesting the paper of Levi et al. [14]. Nevertheless, the process of transformation of this section is based in Taentzer et al. [15}. This section will go step to step with the different constructors that they are corresponding in the DPM.

In this section the process of conversion is analysed. Normally, in a first step is to study the DPM element or element to transform. After, the mapping between the DPM element and the Relational elements. The transformation process in the figures show the DPM UML graph on the left hand side to UML class diagram to display the Relational model (ROLAP) from MDM. The black arrows between both UML language but customized extensions which are used to describe the graph transformation. The square between two black arrows contains an abbreviation what is begin mapped. In this document are distinguished the next different types of mappings rules between the two graphs [15]:

  • A2C is the automatic transformation between attributes of the DPM and columns of a table.
  • MC2T is the automatic transformation between metaclass of the DPM and a table.
  • NON is the transformation of a comment to the Relational Model.



Framework

The figure 2 shows the perspective structural of the framework and this is an extract of the figure 1 in the DPM [22]. The Data Point Model has from 1 to N public elements. From a public element inherits different classes, as element of the dictionary or frameworks [11] [22].


Image:Presentacion2U_F1Peq.jpg

Figure 2. Structural perspective of the framework.


The figure 3 show the transformation of the class public element and framework. The aim is to obtain the table Framework in ROLAP. For this the attributes of publicElement are mapped to the attributes of the table framework in relational model, as the constructor.


Image:Framework.jpg

Figure 3 Mapping for the framework.


Next table 3 shows the mapping of figure 3 but with format of table. From the attribute label of the metaclass PublicElement is obtained the label and in the transformation of the constructor Framework (ROLAP) is obtained the name and for deduction that the type of the label or name is string, the name is of a string type. The same with Creationdate, ModificationDate, and ID. The acronym pk means primary key.


Table 3 — Mapping DPM vs ROLAP of the constructor framework.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement Label Framework name string
PublicElement CreationDate Framework CreationDate DateTime
PublicElement ModificationDate Framework ModificationDate DateTime
PublicElement code Framework ID (Identifier) String pk


In the physical implementation of the annex B is updated the table in the ROLAP. The primary key is another attribute of numeric type, because is to make the independent uniqueness constrain of the name of business user in label and code. On the other hand, in the implemantation also is added the business user that has created the Framework. The figure 4 show the implementation. Example of this paragraph is in the annex A.


Image:Presentacion2U_F3MuyPeq.jpg

Figure 4. Framework in the Relational Model.


Taxonomy

In the same way the class taxonomy inherits of public element [11] [22], as figure 5 shows.


Image:Presentacion2U_F3Peq.jpg

Figure 5. Structural perspective of the taxonomy.


In figure 6 is shown the mapping between the metaclasses Publicelement and Taxonomy of the DPM and the constructor Taxonomy and the RM (Relational Model). It is added the oficial locationof the taxonomy (comment in the UML).


Image:Taxonomy.jpg

Figure 6. Mapping for the constructor taxonomy.


Next table 4 shows the mapping of figure 6 but with format of table. From the attribute label of the metaclass PublicElement is obtained the label and in the transformation of the constructor Taxonomy (ROLAP) is obtained the name and for deduction that the type of the label or name is string, the name is of a string type. The same with Creationdate, ModificationDate, and so on. The acronym pk means primary key.


Table 4 — Mapping DPM vs ROLAP of the constructor taxonomy.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement Label Taxonomy name string
PublicElement CreationDate Taxonomy CreationDate DateTime
PublicElement ModificationDate Taxonomy ModificationDate DateTime
PublicElement code Taxonomy ID (Identifier) String pk
PublicElement ValidFrom Taxonomy ValidFrom DateTime
PublicElement ValidTo Taxonomy ValidTo String
PublicElement version Taxonomy version String
PublicElement versionDate Taxonomy versionDate DateTime
Taxonomy Taxonomy nameTaxonomy String
Taxonomy schemaLocation String


In the physical implementation of the annex B is updated the table in the ROLAP. The primary key is another attribute of numeric type, because is to make the independent uniqueness constrain of the name of business user in label and code. On the other hand, in the implemantation also is added the business user that has created the Taxonomy. Moreover, the referetial constraint is defined. The figure 7 shows the implementation of both constructors Framework and Taxonomy. Example of this paragraph is in the annex A. The acronym pk means primary key and fk is foreign key.


Image:Presentacion2U_F22MuyPeq.jpg

Figure 7. Ralationship between framework and taxonomy in the relational model.


Dimensions

In this section is defined the mapping of the constructor dimension. The figure 8 shows a perspective of the structure of the dimension and this is an extract of the figure 1 in the DPM [22].


Image:Presentacion2U_F5Peq.jpg

Figure 8. Structural perspective of the dimension.


This figure shows two types of dimensions [9] [10], the enumerable and the non-enumerable dimensions. But in an upper level is the family. However the family is not mapped to MDM in this document [20]. On the other hand, in a non-enumerable dimension, their domain-members are not known in advance, then in th RM is not shown until the document instance is obtained, but they have an specific type.

The figure 9 shows the mapping of the enumerable and the non-enumerable dimensions to the ROLAP. Where the transformation among PublicElement, DictonaryElement and EnumerableDimension are detailed a little more, for comprehension of the reader.


Image:Dimension.jpg

Figure 9. Mapping for dimensions.


The table 5 shows the mapping of figure 9 but with format of table. From the attribute label of the metaclass PublicElement is obtained the label and in the transformation of the constructor Dimension (ROLAP) is obtained the name and for deduction that the type of the label or name is string. The same with Creationdate, ModificationDate, and so on. The acronym pk means primary key.


Table 5 — Mapping DPM vs ROLAP of the constuctor dimension.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement Label Dimension name string
PublicElement CreationDate Dimension CreationDate DateTime
PublicElement ModificationDate Dimension ModificationDate DateTime
PublicElement code Dimension ID (Identifier) String pk
DictionaryElement ValidFrom Dimension ValidFrom DateTime
DictionaryElement ValidTo Dimension ValidTo String
Dimension EnumerableDimension Dimension isEnumerable boolean
Dimension NonEnumerabledimension Dimension isEnumerable Boolean


The figure 10 depicts the costructor Dimension in the ROLAP, in the physical implementation (annex B). The primary key is another attribute of numeric type, because is to make the independent uniqueness constrain of the name of business user in label and code. The attribute typeData shows the data type of the members non-defined in the non-enumerable dimensions. Moreover, it is added an attribute in this constructor that is a reference to the domain. Example of this paragraph is referenced in the annex A. The acronym pk means primary key.


Image:Presentacion2U_F27MuyPeq.jpg

Figure 10. Constructor Dimension in ROLAP.


In the Relational model the constructors enumerable and non-enumerable are the constructor dimension (figure 11). The entity Dimension entity will have an attribute for showing if the dimension is non-enumerable or enumerable and another attribute with the data type of the domain-members of the constructor non-enumerable dimension. Moreover, in this implementation is added the name of the domain that belongs the dimension.


Image:Presentacion2U_F23MuyPeq.jpg

Figure 11. Constructors enumerable, non-enumerable and dimension in ROLAP.


If the dimensions are defined, nextly the domain-members are defined. The figure 12 shows the mapping of the members in the ROLAP. However, the name is changed, it is named as DimensionAttribute.


Image:DefinedMember.jpg

Figure 12. Mapping of members in the ROLAP.


The table 6 shows the mapping of figure 12, but with format of table. From the attribute label of the metaclass PublicElement is obtained the label and in the transformation of the constructor DimensionAttribute (ROLAP) is obtained the name and for deduction that the type of the label or name is string. The same with Creationdate, ModificationDate, and so on. The acronym pk means primary key.


Table 6 — Mapping DPM vs ROLAP of the constructor DimensionAttribute.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement Label DimensionAttribute name string
PublicElement CreationDate DimensionAttribute CreationDate DateTime
PublicElement ModificationDate DimensionAttribute ModificationDate DateTime
PublicElement code DimensionAttribute ID (Identifier) String pk
DictionaryElement ValidFrom DimensionAttribute ValidFrom DateTime
DictionaryElement ValidTo DimensionAttribute ValidTo String
DefinedMember isDefault DimensionAttribute isDefault boolean


The figure 13 depicts the costructor DimensionAttribute in the ROLAP, in the physical implementation (annex B). The primary key is another attribute of numeric type, because is to make the independent uniqueness constrain of the name of business user in label and code. This table in Relational Model is fill out with the concepts of the taxonomy, but also in run-time , because the attributes of dimension are for enumerable and non-enumerable dimensions. Moreover, it is added an attribute in this constructor that is a reference to the domain. Example of this paragraph is referenced in the annex A. The acronym pk means primary key.


Image:Presentacion2U_F28MuyPeq.jpg

Figure 13. DimensionAttribute in the ROLAP.


The figure 14 shows the mapping of Dimensions and domain-members in the DPM and Dimensions/Dimension attributes in the Relational data model (ROLAP).This constructor, named Dimension_DimensionAttribute, really is an artifice, because is not necessary the mapping from the DPM. However, this constructor is important, because the model claims that the combinations between dimensions and attributes of dimensions in the relational Model are precise.


Image:Presentacion2U_F9Peq.jpg

Figure 14. Mapping of Dimensions and Members.


Context

The context is not part of the DPM. The context is defined in the instance (XBRL document instance or XBRL report). The corresponding UML model is included in the filing rules document of CWA1 [23].

The figure 15 shows the mapping of the context and the pairs dimension/member belong to the instance.


Image:Context.jpg

Figure 15. Mapping of the context and the pairs dimension/member.


In the mapping to the ROLAP are two necessary constructors. These constructors are context and contextDimensionMemberPar. The mapping is shown in format tabulate in tables 7 and 8. In the transformation of the table 8, the three columns have the acronym pk (primary key), because the primary key is the set of the three attributes.


Table 7 — Mapping DPM vs ROLAP of the constructor context.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement idContext Context contextDescr string
PublicElement id Context IDContext String pk
PublicElement periodStart Context periodStart DateTime
PublicElement periodEndIntant Context periodEndIntant DateTime
PublicElement ValidFrom Context ValidFrom DateTime
PublicElement scheme Context scheme String


Table 8 — Mapping DPM vs ROLAP of the constructor contextDimensionMemberPair.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement id contextDimensionMemberPair IDContext String pk
PublicElement qNameDimension contextDimensionMemberPair dimensionID String pk
PublicElement qNemeMeber contextDimensionMemberPair memberID string pk


The first is the context that consists of the name of the context and in this approach the taxonomy. Because, in theory, it could have different taxonomies with the same context, but with different semantics. The table 7 shows this mapping with the context.


Image:Presentacion2U_F29MuyPeq.jpg

Figure 16. Relational model of the context and contextDimensionMemberPair.


Primary Items

The primary item could be a domain-member of a dimension, however, is a little special, because is associated with this concept two attributes, the type of the data and the time period type. The figure 17 shows the mapping with the relational model. The set of primary items are grouped in the base dimension, in this figure is called the constructor PrimaryItem. The EBA, annex A, considers the base domain as a normal dimension.

This specific dimension, called primary item or base domain has the next features that it holds more semantics contents [9] [10]:

  • It has a basic data type. This characteristic specifies the kind of data to be reported: a number, a date, a text, a monetary amount (a number plus a currency). This information is also used by IT (Information Technology) applications to determine the way data is represented in electronic files. If the type is monetary there is a attribute named balance, with two values: [credit|debit].
  • Time period type to which the data refers: does it refer to a specific point in time (instant) or to an interval of time (duration).


Image:Presentacion2U_F24Peq.jpg

Figure 17. Mapping for the Base Dimension (set of primary items).


The table 9 shows inf tabulate format this mapping.


Table 9 — Mapping DPM vs ROLAP of the constructor Base_Dimension.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement code Base_Dimension IDPrimaryItem String pk
PublicElement label Base_Dimension descrPrimItem String
PublicElement CreationDate Base_Dimension CreationDate DateTime
PublicElement ModificationDate Base_Dimension ModificationDate DateTime
DictionaryElement ValidFrom Base_Dimension ValidFrom DateTime
DictionaryElement ValidTo Base_Dimension ValidTo DateTime
DimensionElement dataType Base_Dimension dataType string
DimensionElement TimePeriodTime Base_Dimension periodTime string


The figure 18 shows the constructor Base_Dimension in the ROLAP. In this implementation is added the balance with its operation of check, and the time period type with its check. But, it is also added the user that has created the primary item.


Image:Presentacion2U_F30MuyPeq.jpg

Figure 18. Base_dimension (set of primary items) in the Relational Model.


Fact table or Data points

The Data point in the DPM is equivalent to the fact table in the MDM, and it is the union of the table context, set of primary items or base dimension and taxonomy. The figure 19 shows the mapping.


Image:Fact.jpg

Figure 19. Mapping of the data point and the fact table.


The table 10 shows inf tabulate format this mapping.


Table 10 — Mapping DPM vs ROLAP of the constructor Fact table.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
Fact qName FactTable IDFact String pk
SchemaRef href FactTable IDTaxonomy String
Fact contextRef FactTable contextID String
PublicElement code FactTable IDPrimaryItem String
Instance/Fact Unit FactTable Unit String
Instance value FactTable Value String
Instance language FactTable lang String
Instance Isnil FactTable Is_Null boolenan
Instance decimal FactTable decimal number


The figure 20 shows the constructor fact table in the Relational Model. However, in this modeled is added the type of unit, the accuracy, the value but depependig on the type will be string, numeric or boolean. It is also added the name that created the fact.


Image:Presentacion2U_F25Peq.jpg

Figure 20. Diagram ROLAP of the Fact table of the DPM.


Summary

The figure 21 shows with more detail the figure 1 of the star model. In this figure is possible to see constructors with their columns and the relationships through the foreign keys.


Image:ForeignKeyRelations.jpg

Figure 21. Diagram ROLAP, summary.


Next table 11 shows a sumary in tabulating format of the total mapping DPM versus ROLAP.


Table 11 — Mapping DPM vs ROLAP of the set of constructors.
DPM Attribute/constructor Costructor ROLAP Attribute Type Constrainst
PublicElement Label Framework name string
PublicElement CreationDate Framework CreationDate DateTime
PublicElement ModificationDate Framework ModificationDate DateTime
PublicElement code Framework ID (Identifier) String pk
PublicElement Label Taxonomy name string
PublicElement CreationDate Taxonomy CreationDate DateTime
PublicElement ModificationDate Taxonomy ModificationDate DateTime
PublicElement code Taxonomy ID (Identifier) String pk
PublicElement ValidFrom Taxonomy ValidFrom DateTime
PublicElement ValidTo Taxonomy ValidTo String
PublicElement version Taxonomy version String
PublicElement versionDate Taxonomy versionDate DateTime
Taxonomy Taxonomy nameTaxonomy String
Taxonomy schemaLocation String
PublicElement Label Dimension name string
PublicElement CreationDate Dimension CreationDate DateTime
PublicElement ModificationDate Dimension ModificationDate DateTime
PublicElement code Dimension ID (Identifier) String pk
DictionaryElement ValidFrom Dimension ValidFrom DateTime
DictionaryElement ValidTo Dimension ValidTo String
Dimension EnumerableDimension Dimension isEnumerable boolean
Dimension NonEnumerabledimension Dimension isEnumerable Boolean
PublicElement Label DimensionAttribute name string
PublicElement CreationDate DimensionAttribute CreationDate DateTime
PublicElement ModificationDate DimensionAttribute ModificationDate DateTime
PublicElement code DimensionAttribute ID (Identifier) String pk
DictionaryElement ValidFrom DimensionAttribute ValidFrom DateTime
DictionaryElement ValidTo DimensionAttribute ValidTo String
DefinedMember isDefault DimensionAttribute isDefault boolean
PublicElement idContext Context contextDescr string
PublicElement id Context IDContext String pk
PublicElement periodStart Context periodStart DateTime
PublicElement periodEndIntant Context periodEndIntant DateTime
PublicElement ValidFrom Context ValidFrom DateTime
PublicElement scheme Context scheme String
PublicElement id contextDimensionMemberPair IDContext String pk
PublicElement qNameDimension contextDimensionMemberPair dimensionID String pk
PublicElement qNemeMeber contextDimensionMemberPair memberID string pk
PublicElement code Base_Dimension IDPrimaryItem String pk
PublicElement label Base_Dimension descrPrimItem String
PublicElement CreationDate Base_Dimension CreationDate DateTime
PublicElement ModificationDate Base_Dimension ModificationDate DateTime
DictionaryElement ValidFrom Base_Dimension ValidFrom DateTime
DictionaryElement ValidTo Base_Dimension ValidTo DateTime
DimensionElement dataType Base_Dimension dataType string
DimensionElement TimePeriodTime Base_Dimension periodTime string
Fact qName FactTable IDFact String pk
SchemaRef href FactTable IDTaxonomy String
Fact contextRef FactTable contextID String
PublicElement code FactTable IDPrimaryItem String
Instance/Fact Unit FactTable Unit String
Instance value FactTable Value String
Instance language FactTable lang String
Instance Isnil FactTable Is_Null boolenan
Instance decimal FactTable decimal number


ANNEX A. Metamodel defined by the EBA (FINREP and COREP) mapped to MDM.

Introduction.

This annex maps the relational model of the DPM supplied by the EBA in the MDM, using ROLAP tool.

The EBA published on 15 March 2013, and after a modification on 27 March 2013 the updated version of the templates, instruction, validation rules, and data point model for implementing technical standards (ITS) on supervisory reporting (COREP and FINREP [16]. On the other hand, in that date EBA published the DPM Database 0.1.1 as Meta model structure used as the repository all the metadata defined in the DPM from which the XBRL taxonomies will be derived. This annex will map this structure of the EBA to the relational data model [18]. The database is built from this document and with the help of a paper under review [19]. For a better understanding the implementation is done in MS SQLServer, version 2012, Sp1. However its move to other RDBMS is easy, because SQL is a standard. In a first step is created the structure of the DPM in the RDBMS (Relational Data Base Management System), SQL Server. And the second step is to populate the DPM in database with the datamodel of the EBA (DPM Database 0.1.1) through a tool ETL (Extract, transformation, and load).

The EBA in this example don’t provide any XBRL Document Instance, then it is not possible to fill out the fact table with an example, but the structure of the DPM is complete. However, in this model is considered a difference with this datamodel propose, the base dimension is a normal explicit dimension, therefore the table base dimension is empty.


Creation of the structure and load the DPM of the EBA in a RDBMS.

In the annex B is shown the creation of the structure of the DPM in RDBMS using the MDM, hosted by CWA1.

On the other hand, from the EBA webpage [16] is possible to download the zip file with the Metadata model structure, DPM Database 0.1.1. After the structure and data will be move to RDBMS. In this document is used MS SQL Server (there free edition). However, it is possible to use other RDBMs, as oracle, DB2, etc.. From Access to SQL Server in this document is used Integration Services IS of MS SQL Server (there is free edition). Through IS (Information Systems) is implemented the importation of the metadata. In this toll, the origin is the Access (The used driver is Microsoft Access (Microsoft Set Database Engine), and the target the client, SQL Server Native client 11.0 and the database, in this document the name is DPM_EBA. After, all tables have to be selected, and the packet is submitted. The figure 14 shows a general view of the load of the Access in a RDBMS and the mapping to DPM in a Relational Database.

Image:Presentacion2U_F14Peq.jpg

Figure 14. General view of Access and RDBMS of the EBA and the DPM in ROLAP.


Loading DPM_ROLAP from DPM_EBA.

This section makes a mapping from DPM_EBA to DPM_ROLAP, both database. The DPM_EBA is loaded in the above section. And DPM_ROLAP is created using the annex B of this document.

As first step, the table Framework is loaded from ReportingFramework. This load is shown in the figure 22, through its design and after the code. In the code of this document the dates are simulated.

Image:Presentacion2U_F15MuyPeq.jpg

Figure 22. Mapping of the framework.

The code of M1 is:


use DPM_ROLAP
--
--	M1 CODE
--
delete from Framework
go
insert into Framework (ID_Framework, nameFramework, creationDate, userID_created)
select	FrameworkID as ID_Framework,
		FrameworkCode as nameFramework,
		convert(datetime, '20130327', 112),
		'EBA'
FROM DPM_EBA..ReportingFramework
go
select * from Framework
go


If the framework is loaded, next table is Taxonomy, that is loaded from the database DPM_EBA..Taxonomy. The figure 23 shows the mapping M2.

Image:Presentacion2U_F16MuyPeq.jpg

Figure 23. Mapping of the taxonomy.

The code of the mapping M2 is:

use DPM_ROLAP
--
-- code M2
--
--truncate table taxonomy
delete from Taxonomy
go
insert into Taxonomy(ID_Taxonomy, ID_Framework, nameTaxonomy, 
			labelTaxonomy, valid_from, versionTax,
			creationDate, userid_created)
select TaxonomyID as ID_Taxonomy, FrameworkID, TaxonomyCode, 
		TaxonomyLabel, convert(datetime, '20130327', 112), '0',
		convert(datetime, '20130327', 112), 'EBA'
from [DPM_EBA].[dbo].[Taxonomy]
go
select * from Taxonomy
go


The next step is to obtain dimensions from the EBA, and it is shown in the figure 24.


Image:Presentacion2U_F17MuyPeq.jpg

Figure 24. The mapping of dimensions.


The code of the mapping M3 is:

--
-- code M3
--
go
delete from Dimension
go
insert into Dimension (dimensionID, dimensionCode, 
	dimensionLabel, domainID, 
	isEnumerable, 
	typeData, creationDate,
	valid_from)
select a.DimensionID, a.DimensionCode, 
	a.DimensionLabel as dimensiondescr, a.DomainID, 
	a.IsTyped as typedDim, 
	cast(b.DataTypeID as nvarchar(30)) as typeData,
	convert(datetime, '20130327', 112) as creationDate, 
	convert(datetime, '20130327', 112) as valid_from 
from DPM_EBA.dbo.Dimension a inner join DPM_EBA.dbo.Domain b
	on a.DomainID=b.DomainID
go
select dimensionID, dimensionCode, 
	dimensionLabel, domainID, 
	isEnumerable, 
	typeData, creationDate,
	valid_from
from Dimension
go


After, it is obtained the dimension attributes, as it is shown in the figure 25.


Image:Presentacion2U_F18MuyPeq.jpg

Figure 25.- Mapping of the attributes of dimensión (ROLAP).


The code of the mapping M4 is:

---
--- Code M4
---
delete from DimensionAttribute
go
insert into DimensionAttribute(memberID, domainID, memberCode,
		memberLabel, isDefault, creationDate,
		valid_from)
Select MemberID, DomainID, MemberCode, 
		MemberLabel as memberLabel, IsDefaultMember as isDefault,
		convert(datetime, '20130327', 112) as creationDate,
		convert(datetime, '20130327', 112) as valid_from
from DPM_EBA.dbo.Member
go

select memberID, domainID, memberCode, memberLabel,
		isDefault, creationDate, valid_from,
		valid_to
from DimensionAttribute
go


The relations between dimensions and attributes of dimension is shown in the figure 26.

Image:Presentacion2U_F19MuyPeq.jpg

Figure 26. Relationship between dimensions and attributes of dimension.


The code of the mapping M5 is:

---
--- Code M5
---
go

delete from Dimension_DimensionAttribute
go
 
insert into Dimension_DimensionAttribute( 
	dimensionID, memberID)
select DimensionID, MemberID 
from DPM_EBA.dbo.DimensionalCoordinate
go

select dimensionID, memberID
from Dimension_DimensionAttribute
go


The next table is the context and the figure 27 shows the mapping. As a data point (a fact) can be referenced by a context, but this context belongs to a taxonomy, the context needs of the taxonomy (module is named by the EBA).


Image:Presentacion2U_F20Peq.jpg

Figure 27. Mapping of the context from DPM_EBA.


The code of the transformation M6:

---
--- Code M6
---
go

delete from Context
go
 
insert into Context (contextID, ID_Taxonomy, contextDescr, codeTaxonomy)
select g.ContextID, b.ModuleID as ID_Taxonomy, 
		h.ContextKey as contextDescr, b.ModuleCode as codeTaxonomy
from DPM_EBA.dbo.ModuleTable a inner join DPM_EBA.dbo.Module b 
		on a.ModuleID=b.ModuleID
		inner join DPM_EBA.dbo.TableVersion c
			on a.TableVID=c.TableVID
		inner join DPM_EBA.dbo.Axis d
			on a.TableVID=d.TableVID 
		inner join DPM_EBA.dbo.AxisOrdinate e
			on d.AxisID=e.AxisID
		inner join DPM_EBA.dbo.OrdinateCategorisation f
			on e.OrdinateID=f.OrdinateID
		inner join DPM_EBA.dbo.ContextDefinition g
			on (f.DimensionID=g.DimensionID and f.MemberID=g.MemberID)
		inner join DPM_EBA.dbo.ContextOfDataPoints h
			on g.ContextID=h.ContextID
group by g.ContextID, b.ModuleID, b.ModuleCode, h.ContextKey 
go

select contextID, ID_Taxonomy, contextDescr, codeTaxonomy
from Context
go


Regard the context and the dimensions and attributes of dimension the transformation can be analysed in the figure 28.


Image:Presentacion2U_F21Peq.jpg

Figure 28. Mapping of the Context_DimensionMemberPair.


And the transformation code M7:

---
--- Code M7
---
delete from contextDimensionMemberPair
go
insert into contextDimensionMemberPair(contextID, ID_Taxonomy, dimensionID, memberID)
select g.ContextID, b.ModuleID as ID_Taxonomy, f.DimensionID, f.MemberID
from DPM_EBA.dbo.ModuleTable a inner join DPM_EBA.dbo.Module b 
		on a.ModuleID=b.ModuleID
		inner join DPM_EBA.dbo.TableVersion c
			on a.TableVID=c.TableVID
		inner join DPM_EBA.dbo.Axis d
			on a.TableVID=d.TableVID 
		inner join DPM_EBA.dbo.AxisOrdinate e
			on d.AxisID=e.AxisID
		inner join DPM_EBA.dbo.OrdinateCategorisation f
			on e.OrdinateID=f.OrdinateID
		inner join DPM_EBA.dbo.ContextDefinition g
			on (f.DimensionID=g.DimensionID and f.MemberID=g.MemberID)
		inner join DPM_EBA.dbo.ContextOfDataPoints h
			on g.ContextID=h.ContextID
group by g.ContextID, b.ModuleID, b.ModuleCode, f.DimensionID, f.MemberID 
order by b.ModuleCode, g.ContextID
go

select contextID, ID_Taxonomy, dimensionID, memberID
from contextDimensionMemberPair
go 


ANNEX B. Implementation of the DPM in ROLAP.

Introduction.

This annex is divided in two sections, Relational model and the creation of the tables.


Structure ROLAP

The figure 29 shows the relational model of the Data Point Model (DPM), through a relational diagram obtained from Management Studio of MS SQL Server.

Image:ROLAPDiagram.jpg

Figure 29.- Structure of the MDM of the DPM.


Creation of the infrastructure through MS SQL Server.

This Section shows the script of creation of the tables. The first part of this script delete the tables (all) and after the tables and some object more are created.


use DPM_ROLAP
go


IF OBJECT_ID(N'FactTable', N'U') IS NOT NULL 
DROP TABLE FactTable;
go


IF OBJECT_ID(N'Period_DPM', N'U') IS NOT NULL 
DROP TABLE Period_DPM;
go

IF OBJECT_ID(N'TR_Base_Dimension_Balance_DPM', N'TR') IS NOT NULL
DROP TRIGGER TR_Base_Dimension_Balance_DPM;
go

IF OBJECT_ID(N'Base_Dimension', N'U') IS NOT NULL 
DROP TABLE Base_Dimension;
go

IF OBJECT_ID(N'contextDimensionMemberPair', N'U') IS NOT NULL 
DROP TABLE contextDimensionMemberPair;
go

IF OBJECT_ID(N'Context', N'U') IS NOT NULL 
DROP TABLE Context;
go

IF OBJECT_ID(N'Dimension_DimensionAttribute', N'U') IS NOT NULL 
DROP TABLE Dimension_DimensionAttribute;
go

IF OBJECT_ID(N'DimensionAttribute', N'U') IS NOT NULL 
DROP TABLE DimensionAttribute;
go

IF OBJECT_ID(N'Dimension', N'U') IS NOT NULL 
DROP TABLE Dimension;
go

IF OBJECT_ID(N'Taxonomy', N'U') IS NOT NULL 
DROP TABLE Taxonomy;
go

IF OBJECT_ID(N'Framework', N'U') IS NOT NULL 
DROP TABLE Framework;
go

create table Framework (
		ID_Framework		int	primary key, 
		nameFramework		nvarchar(255)	not null,
		labelFramework		nvarchar(255)	null,
		creationDate		datetime		not null default getdate(),
		modificationDate	datetime		null,
		userID_created	nvarchar(30)		not null	default current_user)
 
go

create table Taxonomy (
		ID_Taxonomy			int	primary key,
		ID_Framework		int				not null references Framework,
		nameTaxonomy		nvarchar(255)	not null,
		labelTaxonomy		nvarchar(255)	not null,
		creationDate		datetime		not null	default getdate(),
		modificationDate	datetime	null,
		valid_from		datetime		not null,
		valid_to		datetime		null,
		versionTax		nvarchar(10)	not null,
		versionDate		datetime		null,
		schemaLocation		nvarchar(255)	null,
		userid_created		nvarchar(30)	not null	default current_user)
go

 

create table Dimension (
		dimensionID		int		not null	primary key,
		dimensionCode		nvarchar(10)	not null, --Code of approach dimension
		dimensionLabel		nvarchar(255)	not null,
		creationDate		datetime	not null	default getdate(),
		modificationDate	datetime	null,
		domainID		int		not null,
		isEnumerable		bit		not null default(0),-- by default is enumerable (0), if not is non-enumerable (1).
		typeData		nvarchar(30), 
		valid_from		datetime	not null,
		valid_to		datetime	null
 		)
go

create table DimensionAttribute(
		memberID		int	primary key,
		domainID		int not null,
		memberCode		nvarchar(50)	not null,
		memberLabel		nvarchar(255)	not null,
		isDefault		bit	default(0), -- By default a domain-member is not the default
		creationDate		datetime	not null	default getdate(),
		modificationDate	datetime	null,
		valid_from		datetime	not null,
		valid_to		datetime	null
		);
go
		 
create table Dimension_DimensionAttribute(
	dimensionID		int	not null,
	memberID		int not null,
	constraint PK_Dimension_DimensionAttribute
		primary key (dimensionID, memberID),
	constraint FK_dimensionID foreign key (dimensionID)
		references Dimension,
	constraint FK_memberID foreign key (memberID)
		references DimensionAttribute
	);
go 


create table Context (
               contextID	int not null,
		ID_Taxonomy	int not null,
		contextDescr	nvarchar(255)	not null,
		codeTaxonomy	nvarchar(255)	null,
		periodStart	datetime		null,
		periodEndIntant	datetime		null,
		scheme		nvarchar(255)	null,
		constraint PK_Context primary key (contextID, ID_Taxonomy)--,
		--constraint FK_taxonomyID foreign key(ID_Taxonomy) 
		--	references Taxonomy
		); 
 
create table contextDimensionMemberPair(
		contextID		int	not null,
		ID_Taxonomy		int	not null,	
		dimensionID		int	not null,
		memberID		int not null,
		constraint PK_contextDimensionMemberPair 
			primary key (contextID, ID_Taxonomy, dimensionID, memberID),
		constraint FK_contextDimensionMemberPair_ContextID_ID_Taxonomy
			foreign key (contextID, ID_Taxonomy)
			references Context(contextID, ID_Taxonomy),
		constraint FK_contextDimensionMemberPair_dimensionID
			foreign key (dimensionID, memberID)
			references Dimension_DimensionAttribute(dimensionID, memberID)
		)
go


create table Base_Dimension (
		IDprimaryItem		int	identity(1,1)	primary key,
		code			nvarchar(10)	not null,
		creationDate		datetime	not null	default getdate(),
		modificationDate	datetime	null,
		valid_from		datetime	not null	default getdate(),
		valid_to		datetime	null,
		datatype		nvarchar(20)	not null
			check (DataType in ('String','Monetary','Integer','Numeric')),
		periodType		nvarchar(10)	not null
			CHECK  (PeriodType in ('Instant','Period','Forever')),
		balance			nchar(10)	null
			check (Balance in ('Credit','Debit')),
		userid_created	nvarchar(30)	not null	default current_user
		)
go

create trigger TR_Base_Dimension_Balance_DPM ON Base_Dimension
after insert, update
as

declare @Balance	nchar(10),
		@DataType	nvarchar(20),
		@code		nvarchar(10)
select	@code =code,
     	@Balance =balance,
        @DataType =datatype
from inserted
if @Balance is null and @DataType='Monetary'
begin
     raiserror ('If the DataType is Monetary the Balance attribute can not be NULL
ATTENTION: The PrimaryItem with name: %s is not inserted.', 16, 1, @code)
     rollback transaction
end
go

go
create table Period_DPM(
	IDPeriod int identity (1,1) primary key,
	start_date			datetime	null,
	end_date_Instant	datetime	not null,
	instant_Year		nvarchar(4)	not null,
	instant_month		nvarchar(2)	not null,
	instant_day			nvarchar(2)	not null,
	date_created		datetime	not null		default getdate())
 

go

create table FactTable(
 		IDFact			int 	primary key, -- Identification of the DPM or the  Fact
		ID_Taxonomy		int	not null,
		contextID		int	not null,
		IDprimaryItem		int	not null,
		unit_simple		nvarchar(10)	null,	--EUR, PURE, ETC.
		unit_numerator		nvarchar(10)	null,
		unit_denominator	nvarchar(10)	null,	
		accuracy		dec(1)		null,	--Decimals value
		numeric_value		dec(17,4)	null,
		string_value		nvarchar(4000)	null,
		boolean_value		bit		null,
		date_value		datetime	null,	
		is_Null			nchar(1)	null,	--CHECK: Y ODER N
		language		nvarchar(40)	null,
		userid_created		nvarchar(30)	null,
		CONSTRAINT CK_boolean_value_DPM CHECK  (boolean_value in (1,0)),--CHECK: Y ODER N
		CONSTRAINT CK_nil_value_DPM CHECK  (is_Null in ('Y','N','y','n')),--CHECK: Y ODER N
		constraint FK_FactTable_Context_Taxonomy 
			foreign Key (contextID, ID_Taxonomy) 
			references Context(contextID, ID_Taxonomy),
		constraint FK_FactTable_Taxonomy 
			foreign Key (ID_Taxonomy) 
			references Taxonomy(ID_Taxonomy),
		constraint FK_FactTable_primaryItem 
			foreign Key (IDprimaryItem) 
			references Base_Dimension(IDprimaryItem)
)



ANNEX C. DPM of FINREP 2012.

Introduction.

This annex is based in the New 2012 FINREP taxonomy, and in the job referencied in [20].

From the page referenciade in Eurofiling can be downloaded the file DataPointsModel.xls with the version of the DPM in an EXCEL spreadsheet.

The DPM in this case, [20] is obtained from the taxonomy and an example of XBRL Document Instance, as the figure 30 shows. The DPM is obtained from the taxonomy, Metadata, and the Fact Table from a XBRL Document Instance, Data Points.


Image:Presentacion2U_F31Peq.jpg

Figure 30. Process of creation ofa DPM from the taxonomy FINREP 2012 and an example of XBRL Document Instance of this taxonomy.


DPM of FINREP 2012

The first sheet shows the set of families, including the base dimension, figure 31. However the families are out of this documment, because this more readable and less compless.


Image:Sheet1_DimensionsMuyPeq.jpg

Figure 31. The families.


The table 12 shows only the families.


Table 12 — DPM in format ROLAP for the constructor Family.
Code Family
CT
CI
AT
PL
SE
GA
CU
TI
CD
BA
CL
RP
RT
MA
CU
TI
RS
EC


The next sheet, figure 32, shows the set of base dimension.


Image:Sheet1_BaseDimMuyPeq.jpg

Figure 32. Base dimension.

The table 13 shows the constructor BaseDimension.


Table 13 — DPM in format ROLAP for the constructor BaseDimension.
Code BaseDimension
ad1
dd14
dd7
md10
md11
md12
md3
mi1
mi13
mi2
mi3
mi4
mi5
mi8
mi9
pi15
sd6


The next sheet, figure 33, shows only a sheet of a dimension and theirs domain-members.


Image:Sheet3_Dim_MemberMuyPeq.jpg

Figure 33. Sheet of one dimension and theirs domain-members.


The table 14 shows the constructor Dimension.


Table 14 — DPM in format ROLAP for the constructor Dimension.
Code Dimension
AL
AS
AT
BT
CD
CI
CL
CR
CS
DL
EL
EQ
JI
LI
MA
OC
OM
PL
RI
RM
RP
RS
RT


The table 15 shows the constructor DimensionAttribute.


Table 15 — DPM in format ROLAP for the constructor DimensionAttribute, only it is shown a subset. The number of tuples of the DimensionAttribute is 171.
Code DimensionAttribute
dAT:x1
dAT:x10
dAT:x11
dAT:x12
dAT:x13
dAT:x14
dAT:x15
dAT:x16
dAT:x17
dAT:x18
dAT:x19
dAT:x2
dAT:x20
dAT:x3
dAT:x4
dAT:x5
dAT:x6
dAT:x7
dAT:x8
dAT:x9
dBA:x1
dBA:x2
... ...


The table 16 shows the constructor Relation_DimensionAttribute.


Table 16 — DPM in format ROLAP for the constructor Relation_DimensionAttribute, only it is shown a subset. The number of tuples of the Relation_DimensionAttribute is 203.
dimensionID memberID
AL dCT:x1
AL dCT:x12
AL dCT:x13
AL dCT:x22
AL dCT:x23
AL dCT:x28
AL dCT:x38
AL dCT:x4
AL dCT:x44
AL dCT:x7
AL dCT:x9
AS dCT:x1
AS dCT:x10
AS dCT:x11
AS dCT:x13
AS dCT:x14
AS dCT:x15
AS dCT:x16
AS dCT:x17
AS dCT:x18
AS dCT:x19
AS dCT:x2
AS dCT:x20
AS dCT:x22
AS dCT:x26
AS dCT:x27
AS dCT:x29
AS dCT:x3
AS dCT:x30
AS dCT:x39
AS dCT:x40
AS dCT:x41
AS dCT:x42
AS dCT:x44
AS dCT:x7
AS dCT:x9
AT dAT:x1
AT dAT:x10
AT dAT:x11
AT dAT:x12
AT dAT:x13
DL dTI:gt180d_le1y
DL dTI:gt1y
DL dTI:gt90d_le180d
DL dTI:x1
EL dCT:x1
EQ dCT:x1
EQ dCT:x21
EQ dCT:x34
EQ dCT:x35
EQ dCT:x36
EQ dCT:x37
EQ dCT:x43
JI dGA:emu
JI dGA:x2
JI dGA:x4
LI dCT:x1
LI dCT:x22
LI dCT:x23
LI dCT:x24
LI dCT:x25
LI dCT:x31
LI dCT:x32
... ... ...


The table 17 shows the constructor ContextDimensionMemberPair. In this case the attribute taxonomy is taken out because is only FINREP.


Table 17 — DPM in format ROLAP for the constructor ContextDimensionMemberPair, only it is shows subset. The number of tuples of the ContextDimensionMemberPair is 1278.
contextID dimensionID memberID
e_x11_x3_emu_eur_x10_x1 AS dCT:x11
e_x11_x3_emu_x2_x10_x1 AS dCT:x11
e_x11_x3_eu_x10_x1 AS dCT:x11
e_x11_x3_x10_x1 AS dCT:x11
e_x11_x3_x2_eur_x10_x1 AS dCT:x11
e_x11_x3_x2_x2_x10_x1 AS dCT:x11
e_x11_x3_x4_x10_x1 AS dCT:x11
e_x13_x3_emu_x14_eur_x16_x1 AS dCT:x13
e_x13_x3_emu_x14_x2_x16_x1 AS dCT:x13
e_x13_x3_eu_x14_x16_x1 AS dCT:x13
e_x13_x3_x14_x16_x1 AS dCT:x13
e_x13_x3_x2_x14_eur_x16_x1 AS dCT:x13
e_x13_x3_x2_x14_x2_x16_x1 AS dCT:x13
e_x13_x3_x4_x14_x16_x1 AS dCT:x13
e_x17_x3_x21_x1 AS dCT:x17
e_x17_x3_x3_x1 AS dCT:x17
e_x17_x3_x6_x1 AS dCT:x17
e_x19_x3_x21_x1 AS dCT:x19
... ... ... ... ... ... ... ...


The table 18 shows the constructor Context. In this case the attribute taxonomy is taken out because is only FINREP.


Table 18 — DPM in format ROLAP for the constructor Context, only it is shows subset. The number of tuples of the Context is 237.
contextID periodEndIntant entity
e_x11_x3_emu_eur_x10_x1 2011-06-12 abc
e_x11_x3_emu_x2_x10_x1 2011-06-12 abc
e_x11_x3_eu_x10_x1 2011-06-12 abc
e_x11_x3_x10_x1 2011-06-12 abc
e_x11_x3_x2_eur_x10_x1 2011-06-12 abc
e_x11_x3_x2_x2_x10_x1 2011-06-12 abc
e_x11_x3_x4_x10_x1 2011-06-12 abc
e_x13_x3_emu_x14_eur_x16_x1 2011-06-12 abc
e_x13_x3_emu_x14_x2_x16_x1 2011-06-12 abc
e_x13_x3_eu_x14_x16_x1 2011-06-12 abc
... ... ... ... ... ... ...


The table 19 shows the constructor FactTable.


Table 19 — DPM in format ROLAP for the constructor FactTable, only it is shows subset. The number of tuples of the FactTable is 237 (Data points).
IDFact ID_Taxonomy contextID IDprimaryItem unit_simple accuracy numeric_value
1 FINREP e_x7_x20_x14_eq0d_x11_x1 mi1 EUR 0 5
2 FINREP e_x7_x20_x14_gt0d_le90d_x11_x1 mi1 EUR 0 5
3 FINREP e_x7_x20_x14_gt90d_le180d_x11_x1 mi1 EUR 0 5
4 FINREP e_x7_x20_x14_gt180d_le1y_x11_x1 mi1 EUR 0 5
5 FINREP e_x7_x20_x14_gt1y_x11_x1 mi1 EUR 0 5
6 FINREP e_x7_x20_x2_eq0d_x11_x1 mi1 EUR 0 1
7 FINREP e_x7_x20_x2_gt0d_le90d_x11_x1 mi1 EUR 0 1
8 FINREP e_x7_x20_x2_gt90d_le180d_x11_x1 mi1 EUR 0 1
9 FINREP e_x7_x20_x2_gt180d_le1y_x11_x1 mi1 EUR 0 1
10 FINREP e_x7_x20_x2_gt1y_x11_x1 mi1 EUR 0 1
11 FINREP e_x7_x20_x5_eq0d_x11_x1 mi1 EUR 0 1
12 FINREP e_x7_x20_x5_gt0d_le90d_x11_x1 mi1 EUR 0 1
13 FINREP e_x7_x20_x5_gt90d_le180d_x11_x1 mi1 EUR 0 1
14 FINREP e_x7_x20_x5_gt180d_le1y_x11_x1 mi1 EUR 0 1
15 FINREP e_x7_x20_x5_gt1y_x11_x1 mi1 EUR 0 1
16 FINREP e_x7_x20_x4_eq0d_x11_x1 mi1 EUR 0 1
17 FINREP e_x7_x20_x4_gt0d_le90d_x11_x1 mi1 EUR 0 1
18 FINREP e_x7_x20_x4_gt90d_le180d_x11_x1 mi1 EUR 0 1
19 FINREP e_x7_x20_x4_gt180d_le1y_x11_x1 mi1 EUR 0 1
20 FINREP e_x7_x20_x4_gt1y_x11_x1 mi1 EUR 0 1
21 FINREP e_x7_x20_x12_eq0d_x11_x1 mi1 EUR 0 1
22 FINREP e_x7_x20_x12_gt0d_le90d_x11_x1 mi1 EUR 0 1
-- FINREP --- --- --- --- --- --- - ---



ANNEX D. DPM of the first prototype of Solvency II.

Introduction.

This annex is based in a first idea or concept of the taxonomy of Solvency II ('2012-07-01-mdt.rar'). As this taxonomy in this phase is very easy and simple, it is possible that can help to reader of this page to understand better the structure of the DPM [24] [20].

The DPM in this case [24] is obtained from the taxonomy and an example of XBRL Document Instance, as the figure 34 shows. The DPM is obtained from the taxonomy, Metadata, and the Fact Table from a XBRL Document Instance, Data Points.


Image:Presentacion2U_F32MuyPeq.jpg

Figure 34. Process of creation ofa DPM from the taxonomy Solvency II and an example of XBRL Document Instance of this taxonomy.


DPM of the prototype

The table 20 shows the constructor BaseDimension.


Table 20 — DPM in format ROLAP for the constructor BaseDimension, only it is shown a subset. The number of tuples of the BaseDimension is 140 (primary items).
Code BaseDimension
a1
A10A
A10B
A11
A12
A13
A14
A14A
A16
A17
A18
A18A
A19
A19A
A2
A20
A21
A23
A25B
A26
A27
A29
A3
A30
A4
A5
A6
A7
A7A
A8
A8A
A8C
A8D
A9
AS1
AS10A
AS10B
AS11
AS12
AS13
AS14
---


The table 21 shows the constructor Dimension.


Table 21 — DPM in format ROLAP for the constructor Dimension.
Code Dimension
PeriodicityDimension
SoloOrGroupDimension


The table 22 shows the constructor DimensionAttribute.


Table 22 — DPM in format ROLAP for the constructor DimensionAttribute.
Code DimensionAttribute
per:AdHoc
per:Quarterly
per:Yearly
soc:Group
soc:Solo


The table 23 shows the constructor Relation_DimensionAttribute.


Table 23 — DPM in format ROLAP for the constructor Relation_DimensionAttribute.
dimensionID memberID
PeriodicityDimension per:AdHoc
PeriodicityDimension per:Quarterly
PeriodicityDimension per:Yearly
SoloOrGroupDimension soc:Group
SoloOrGroupDimension soc:Solo


The table 24 shows the constructor ContextDimensionMemberPair. In this case the attribute taxonomy is taken out because is only Solvency II.


Table 24 — DPM in format ROLAP for the constructor ContextDimensionMemberPair.
contextID dimensionID memberID
Context_Instant_Quarterly_Solo PeriodicityDimension per:Quarterly
Context_Instant_Yearly_Solo PeriodicityDimension per:Yearly
Context_Instant_Quarterly_Solo SoloOrGroupDimension soc:Solo
Context_Instant_Yearly_Solo SoloOrGroupDimension soc:Solo


The table 25 shows the constructor Context. In this case the attribute taxonomy is taken out because is only Solvency II.


Table 25 — DPM in format ROLAP for the constructor Context.
contextID periodEndIntant entity
Context_Instant_Quarterly_Solo 2012-06-30 123456
Context_Instant_Yearly_Solo 2012-06-30 123456


The table 26 shows the constructor FactTable.


Table 26 — DPM in format ROLAP for the constructor FactTable.
IDFact ID_Taxonomy contextID IDprimaryItem unit_simple accuracy numeric_value
1 Solvency II Context_Instant_Quarterly_Solo AS17A EURO 0 42000
2 Solvency II Context_Instant_Quarterly_Solo AS18 EURO 0 29655
3 Solvency II Context_Instant_Quarterly_Solo AS17 EURO 0 12345
4 Solvency II Context_Instant_Yearly_Solo AS18 EURO 0 69000
5 Solvency II Context_Instant_Yearly_Solo AS17 EURO 0 666
6 Solvency II Context_Instant_Yearly_Solo AS17A EURO 0 100000


Bibliography

  • [1] Inmon W. H. (2005) Building the Data Warehouse, 4th Edition. John Wiley & Sons 2005.
  • [2] Kimball R. (2004) The Data Warehouse Toolkit series. John Willey & Sons 1996-2004.
  • [3] Jarke M., Lenzerini M., Vassiliou Y. and Vassiliadis P, (2003). Fundamentals of Data Warehouses, 2nd edition, Springer.
  • [5] Data Warehouse Institute (2013),TDWI.
  • [6] Engel P, Hamscher W., Shuetrim G., Vun Kannon D., Wallis H. (2008). Extensible Business Reporting Language (XBRL) 2.1. July 2nd, 2008. XBRL International. http://www.xbrl.org/Specification.
  • [8] Santos I, Castro E (2011) XBRL and the Multidimensional Data Model. In Proceedings of the 7th International Conference on Web Information Systems and Technologies, WEBIST 2011, pages 161-164, Noordwijkerhout. The Netherlands, May 6th-9th, 2011.
  • [9] Santos I, Castro E (2011) XBRL Interoperability through a Multidimensional Data Model. IADIS International Conference on Internet Technologies & Society (ITS2011), Shanghai, China. December 8th-10th, 2011.
  • [11] Declerck T, Homes R, Heinze K (2013) European XBRL Taxonomy Architecture V2.0. CEN Workshop Agreement. www.xbrlwiki.info/index.php?title=European_XBRL_Taxonomy_Architecture_V3.0.
  • [12] Bernstein P A, Halevy A Y, Pottinger RA (2000). A vision for management of complex models, SIGMOD Record 29 (4), 2000, 55-63.
  • [13] Chewathe S, García-Molina H, Hammer J, Ireland K, Papakonstantinou Y, Ullman J, and Widom J (1994) The TSIMMIS Project: Integration of heterogeneous information sources. In Proc. 10th Meeting of the Information Processing Societ of Japan, pages 7-18, 1994.
  • [14] Levi A, Rajaraman A, and Ordille J (1996) Querying heterogeneous information sources using source descriptions. VLDB’96, Proceedings of Twenty-second International Conference on Very Large Data Bases.
  • [15] Taentzer G, Ehrig K, Guerra E, Lara J, Lengyel L, Levendovszky T, Prange U, Varro D, and Varro-Gaypay S (2005) Model Transformation by Graph Transformation: A comparative Study. Model Transformation in Practice Workshop 2005 (MIIP2005).
  • [17] Santos I (2013) Data Point Model (DPM) versus Multidimensional Data Model (MDM). Contribution for DPM chapter in CEN WS XBRL Plenary Session, Dublin, April 19th 2013. Hosted by Central Bank of Ireland.
  • [19] Santos I, Castro E, Velasco M (2013) Conceptual and Logical Models in the Design of Economic and Financial Reports Using the XBRL Specification. In the journal Business & Information Systems Engineering (BISE), under review.
  • [20] Santos I, Castro E (2011) Proof of concept of mapping a XBRL report versus a RDBMS. Openfiling 1st General Assembly, organized by XBRL Operational Network of the European Banking Authority, and hosted by Bank of Italy. September 5th, 2011. Banca d’Italia, Rome, Italy. http://www.openfiling.info/?page_id=286.
  • [21] Della Penna G, Di Marco A, Intrigila B, Melatti I, and Pierantonio A (2003) Xere: Towards a Natural Interoperability between XML and ER Diagrams. Lecture Notes in computer Science, volume 2621 2003, pp 356-371. Book: Fundamental Approaches to software Engineering.
  • [23] Declerk T, Hommes R, Heinze K (2013) European Filing Rules. CEN Workshop Agreement. European Filing Rules.
  • [24] León Y (2012) Automation and mapping from the data model of the XBRL specification in Database. Final Project of the Polytechnic School of the Carlos III University of Madrid, Spain. Date: October 4th, 2012. Tutors: Santos I, Castro E. Automation and mapping
  • [25] Boixo I, Flores F (2005) New Technical and Normative Challenges for XBRL: Multidimensional in the COREP Taxonomy. July 18th, 2005. The International Journal of Digital Accounting Research, Vol. 5, N. 9, 2005, pages 79-104. ISSN:1577-8517.