DPM2MDM
From XBRLWiki
(diff) ←Older revision | Current revision | Newer revision→ (diff)
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 conceptional 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 meta classes introduced in part 1 and 4 of the CWA1 document. 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 meta classes and their correspondence in form of database tables in the MDM.
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 complexe issues are adressed 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, agencie or Universities with the intension to provide mikro or makro prudentional 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 an thorough understanding of the concept of DPM as well as basic knowledge about XBRL. The reader is also expected to have knowledge in creating conceptional models for relational and multidimensional databases.
Preconditions on mapping
Fundamental choices
There are two main stream solutions for storing XBRL instances and their facts into a relational database system. 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 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. 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 nominator/denumerator. | 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
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.
- 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]:
- A2A is the automatic transformation between attributes to attributes.
- A2Pi is the automatic transformation between classes to the primary item.
- A2T is the automatic transformation between attributes to taxonomy.
- C2C is the automatic transformation between concepts.
- C2CTx is the automatic transformation between classes to contexts.
- C2CTxDM is the automatic transformation between classes to Contexts, dimensions, and attributes of dimension.
- C2D is the automatic transformation between classes of dimensions to dimensions.
- C2DA is the automatic transformation between classes to attributes of dimension.
- C2F is the automatic transformation between concepts and frameworks.
- C2Fact is the automatic transformation between classes and the fact table.
- C2Pi is the automatic transformation between classes to the primary item.
- C2T is the automatic transformation between concepts and taxonomy.
- T2T is the automatic transformation between taxonomies.
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].
- 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.
- 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.
| 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 the annex A.
- 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.
- 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).
- Figure 6. Mapping for the constructor taxonomy.
On the other hand, in the Relational Model (RM) a class framework has 1**n classes taxonomy.
- Figure 6. Ralationship between framework and taxonomy in the relational model.
Dimensions
In this section is defined the mapping of the constructor dimension. The figure 7 shows a perspective of the structure of the dimension and this is an extract of the figure 1 in the DPM [22].
- Figure 7. 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 or group of dimensions and they belong to a same domain, it is not mapped to MDM. On the other hand, a non-enumerable dimension is not known in advance, then in th RM is not shown until the document instance is obtained.
The figure 8 shows only the mapping of the enumerable dimension. Where the transformation between DictonaryElement (DPM) and EnumerableDimension (RM) is detailed a little more, for comprehension of the reader.
- Figure 8. Mapping for Enumerable dimensión.
The figure 9 depicts the transformation of the DPM to ROLAP, and the reverse mapping of the typed and explicit dimensions. In this figure is added the union between the non-enumerable or enumerable dimension.
- Figure 9. Mapping of transformations for dimensions.
However, in the Relational model both constructors are one, enumerable and non-enumerable. 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, figure 10.
- Figure 10. RM, the constructor dimension.
On the other hand, from the members defined or not are obtained the dimension attributes of the dimension, figure 11. But in both cases they will defined in this constructor, although they are filled out when the taxonomy is defined or in run time of the document instance. The figure 8 shows the mapping of the dimension attributes with the members.
- Figure 11. Mapping of members defined or not to dimension attributes.
The figure 12 shows the mapping of Dimensions and domain-members in the DPM and Dimensions/Dimension attributes in the Relational data model (ROLAP).This figure really is an artifice, because is not necessary the mapping from the DPM.
- Figure 12. Mapping of the Relations.
Context
The context is not part of the DPM but of the XBRL instance structure. The corresponding UML model is included in the filing rules document of CWA1 [23].
The figure 13 shows the mapping of DPM to the Relational Model. There are two Context and another for the context with the dimensions and the domains-members. In the relational model the dimensions can be explicit or typed.
In the mapping is obtained two constructor for the RM, the Context_taxonomy, and Context_Dim_Member. A context in the DPM is a DataCubes with the same set of dimensions but in different taxonomies. This makes that this context has meaning different depending on the taxonomy. For this the context is shared by the taxonomy. Moreover, each context has a set of dimensions (explicit or typed) with its domain-member (if is explicit). Then, it is defined another constructor contex_Dim_Member. Each object of this constructor is a dimension/member/context.
- Figure 13. Mapping of the context.
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 14 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.
- Figure 14. Mapping for the Base Dimension (set of primary items).
Fact table or Data points
The fact table, figure 16, in the MDM (Data point in the DPM) is the union of the table context, set of primary items or base dimension and taxonomy.
- Figure 16. diagram ROLAP of the fact table of the DPM.
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.
- 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_DPM is loaded from ReportingFramework. This load is shown in the figure 15, through its design and after the code. In the code of this document the dates are simulated.
- Figure 15. Mapping of the framework.
The code of M1 is:
use DPM_ROLAP
--
-- M1 CODE
--
--truncate table framework_DPM
delete from Framework_DPM
go
insert into Framework_DPM (ID_Framework, nameFramework, valid_from, userID_created)
select FrameworkID as ID_Framework,
FrameworkCode as nameFramework,
convert(datetime, '20130327', 112),
'EBA'
FROM DPM_EBA..ReportingFramework
go
select * from Framework_DPM
go
If the framework is loaded, next table is Taxonomy_DPM, that is loaded from the database DPM_EBA..Taxonomy. The figure 16 shows the mapping M2.
- Figure 16. Mapping of the taxonomy.
The code of the mapping M2 is:
use DPM_ROLAP -- -- code M2 -- --truncate table taxonomy_DPM delete from Taxonomy_DPM go insert into Taxonomy_DPM(ID_Taxonomy, ID_Framework, nameTaxonomy, labelTaxonomy, valid_from, versionTax, date_created, 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_DPM go
The next step is to obtain dimensions from the EBA, and it is shown in the figure 17.
- Figure 17. The mapping of dimensions.
The code of the mapping M3 is:
-- -- code M3 -- insert into Dimension_DPM (dimensionID, dimensionCode, dimensiondescr, domainID, typedDim, typeData, 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 valid_from from DPM_EBA.dbo.Dimension a inner join DPM_EBA.dbo.Domain b on a.DomainID=b.DomainID go select dimensionID, dimensionCode, dimensiondescr, domainID, typedDim, typeData, valid_from from dimension_DPM go
After, it is obtained the dimension attributes, as it is shown in the figure 18.
- Figure 18.- Mapping of the attributes of dimensión (ROLAP).
The code of the mapping M4 is:
--- --- Code M4 --- insert into Domain_Member_DPM(memberID, domainID, memberCode, memberDescr, byDefault, createFrom, valid_from) Select MemberID, DomainID, MemberCode, MemberLabel as memberDescr, IsDefaultMember as byDefault, convert(datetime, '20130327', 112) as createFrom, convert(datetime, '20130327', 112) as valid_from from DPM_EBA.dbo.Member go select memberID, domainID, memberCode, memberDescr, byDefault, createFrom, valid_from, valid_to from Domain_Member_DPM go
The relations between dimensions and attributes of dimension is shown in the figure 19.
- Figure 19. Relationship between dimensions and attributes of dimension.
The code of the mapping M5 is:
--- --- Code M5 --- insert into Dimension_Domain_Member_DPM( dimensionID, memberID) select DimensionID, MemberID from DPM_EBA.dbo.DimensionalCoordinate go select dimensionID, memberID from Dimension_Domain_Member_DPM go
The next table is the context and the figure 20 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).
- Figure 20. Mapping of the context from DPM_EBA.
The code of the transformation M6:
--- --- Code M6 --- insert into context_DPM (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_DPM go
Regard the context and the dimensions and attributes of dimension the transformation can be analysed in the figure 21.
- Figure 21. Mapping of the Context_Dim_Members.
And the transformation code M7:
--- --- Code M7 --- insert into Context_Dim_Members_DPM(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 Context_Dim_Members_DPM 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 22 shows the relational model of the Data Point Model (DPM), through a relational diagram obtained from Management Studio of MS SQL Server.
- Figure 22.- 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_DPM', N'U') IS NOT NULL
DROP TABLE FactTable_DPM;
go
IF OBJECT_ID(N'Period_DPM', N'U') IS NOT NULL
DROP TABLE Period_DPM;
go
IF OBJECT_ID(N'TR_Base_Domain_Balance_DPM', N'TR') IS NOT NULL
DROP TRIGGER TR_Base_Domain_Balance_DPM;
go
IF OBJECT_ID(N'Base_Domain_DPM', N'U') IS NOT NULL
DROP TABLE Base_Domain_DPM;
go
IF OBJECT_ID(N'Context_Dim_Members_DPM', N'U') IS NOT NULL
DROP TABLE Context_Dim_Members_DPM;
go
IF OBJECT_ID(N'Context_DPM', N'U') IS NOT NULL
DROP TABLE Context_DPM;
go
IF OBJECT_ID(N'Dimension_Domain_Member_DPM', N'U') IS NOT NULL
DROP TABLE Dimension_Domain_Member_DPM;
go
IF OBJECT_ID(N'Domain_Member_DPM', N'U') IS NOT NULL
DROP TABLE Domain_Member_DPM;
go
IF OBJECT_ID(N'Dimension_DPM', N'U') IS NOT NULL
DROP TABLE Dimension_DPM;
go
IF OBJECT_ID(N'Taxonomy_DPM', N'U') IS NOT NULL
DROP TABLE Taxonomy_DPM;
go
IF OBJECT_ID(N'Framework_DPM', N'U') IS NOT NULL
DROP TABLE Framework_DPM;
go
create table Framework_DPM (
ID_Framework int primary key,
nameFramework nvarchar(255) not null,
labelFramework nvarchar(255) null,
valid_from datetime not null,
valid_to datetime null,
date_created datetime not null default getdate(),
userID_created nvarchar(30) not null default current_user)
go
create table Taxonomy_DPM (
ID_Taxonomy int primary key,
ID_Framework int not null references Framework_DPM,
nameTaxonomy nvarchar(255) not null,
labelTaxonomy nvarchar(255) not null,
valid_from datetime not null,
valid_to datetime null,
versionTax nvarchar(10) not null,
versionCreated datetime not null default getdate(),
date_created datetime not null default getdate(),
userid_created nvarchar(30) not null default current_user)
go
create table Dimension_DPM (
dimensionID int not null primary key,
dimensionCode nvarchar(10) not null, --Code of approach dimension
dimensiondescr nvarchar(255) not null,
domainID int not null,
typedDim bit not null default(0), -- by default is explicit (0), if not typed (1).
typeData nvarchar(30) null,
ap_dim_nsuri nvarchar(200) null, --Namespace.
ap_dim_rem_code nvarchar(20) null, --Code of members.
pt_dim_code nvarchar(10) null, --Code of portfolio dimension.
pt_dim_nsuri nvarchar(200) null, --Namespace
pt_dim_mem_code nvarchar(20) null, --Code of members
ga_dim_code nvarchar(10) null, --Code of country dimension
ga_dim_nsuri nvarchar(200) null, --Namespace
ga_dim_rem_code nvarchar(20) null, --Code of members.
valid_from datetime not null,
valid_to datetime null
)
go
create table Domain_Member_DPM(
memberID int primary key,
domainID int not null,
memberCode nvarchar(50) not null,
memberDescr nvarchar(255) not null,
byDefault bit not null default(0), -- By default a domain-member is not the default
createFrom datetime not null,
valid_from datetime not null,
valid_to datetime null
);
go
create table Dimension_Domain_Member_DPM(
dimensionID int not null,
memberID int not null,
constraint PK_Dimension_Domain_Member_DPM
primary key (dimensionID, memberID),
constraint FK_dimensionID foreign key (dimensionID)
references Dimension_DPM,
constraint FK_memberID foreign key (memberID)
references Domain_Member_DPM
);
go
create table Context_DPM (
contextID int not null,
ID_Taxonomy int not null,
contextDescr nvarchar(255) not null,
codeTaxonomy nvarchar(255) null,
constraint PK_Context_DPM primary key (contextID, ID_Taxonomy)--,
--constraint FK_taxonomyID foreign key(ID_Taxonomy)
-- references Taxonomy_DPM
);
go
create table Context_Dim_Members_DPM(
contextID int not null,
ID_Taxonomy int not null,
dimensionID int not null,
memberID int not null,
constraint PK_Context_Dim_Members_DPM
primary key (contextID, ID_Taxonomy, dimensionID, memberID),
constraint FK_Context_Dim_Members_DPM_ContextID_ID_Taxonomy
foreign key (contextID, ID_Taxonomy)
references Context_DPM(contextID, ID_Taxonomy),
constraint FK_Context_Dim_Members_DPM_dimensionID
foreign key (dimensionID, memberID)
references Dimension_Domain_Member_DPM(dimensionID, memberID)
)
go
create table Base_Domain_DPM (
IDprimaryItem int identity(1,1) primary key,
code nvarchar(10) not null,
nsuri nvarchar(200) not 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')),
date_created datetime not null default getdate(),
userid_created nvarchar(30) not null default current_user
)
go
create trigger TR_Base_Domain_Balance_DPM ON Base_Domain_DPM
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_DPM(
ID_DPM int primary key, -- Identification of the DPM or the Fact
contextID int not null,
ID_Taxonomy 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,
nil_value char(1) null, --CHECK: Y ODER N
date_crated datetime not null default getdate(),
userid_created nvarchar(30) not null default current_user,
CONSTRAINT CK_boolean_value_DPM CHECK (boolean_value in (1,0)),--CHECK: Y ODER N
CONSTRAINT CK_nil_value_DPM CHECK (nil_value in ('Y','N','y','n')),--CHECK: Y ODER N
constraint FK_FactTable_DPM_Context_Taxonomy
foreign Key (contextID, ID_Taxonomy)
references Context_DPM(contextID, ID_Taxonomy),
constraint FK_FactTable_DPM_Taxonomy
foreign Key (ID_Taxonomy)
references Taxonomy_DPM(ID_Taxonomy),
constraint FK_FactTable_DPM_primaryItem
foreign Key (IDprimaryItem)
references Base_Domain_DPM(IDprimaryItem)
)
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.
- [4] Kimball Group (2013). http://www.rkimball.com/html/articles.html.
- [5] Data Warehouse Institute (2013). http://www.tdwi.org”.
- [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.
- [7] Schmehl K (2009) Data Model and Matrix Schemas. November 16th, 2009. http://www.eurofiling.info . XI European Banking Supervisor, XBRL Workshop hosted by the Oesterreichische Nationalbank, Vienna.
- [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.
- [10] Hernandez-Ros I, Wallis H (2006) XBRL Dimensions 1.0 XBRL International. April 26th, 2006. http://www.xbrl.org/Specification/XDT-CR3-2006-04-26.rtf.
- [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).
- [16] EBA (2013) Update on the technical standards on supervisory reporting requirements. http://www.eba.europa.eu/-/update-on-the-technical-standards-on-supervisory-reporting-requirements.
- [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.
- [18] Academy works (2013) Openfiling. http://www.oprmfiling.info?page_id=286.
- [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.
- [22] Declerk T, Hommes R, Heinze K (2013) European Data Point Methodology V2.0. CEN Workshop Agreement. European Data Point Methodology V2.0.
- [23] Declerk T, Hommes R, Heinze K (2013) European Filing Rules. CEN Workshop Agreement. European Filing Rules.
