DPM2MDM
From XBRLWiki
Revision as of 18:09, 14 October 2013 (edit) Ignacio.santos (Talk | contribs) ← Previous diff |
Current revision (09:30, 26 November 2013) (edit) Ignacio.santos (Talk | contribs) |
||
Line 1: | Line 1: | ||
- | <span style="font-size:18pt">'''CEN Workshop Agreement'''</span> | + | <span style="font-size:18pt">'''Workshop Group'''</span> |
- | '''CEN WS XBRL Experts''': Ignacio Santos (Bank of Spain), Roland Hommes (Rhocon), Katrin Heinze (Deutsche Bundesbank) | + | '''Working Group CWA1 Draft Experts''': Ignacio Santos (Bank of Spain), Roland Hommes (Rhocon), Katrin Heinze (Deutsche Bundesbank) |
=Foreword= | =Foreword= | ||
- | This document has been prepared by CEN/WS XBRL, the secretariat of which is held by NEN. | + | |
+ | 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: | 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 | + | * Part 1: European data point methodology for supervisory reporting. |
+ | * Part 2: Guidelines for data point modelling | ||
+ | * Part 3: European XBRL Taxonomy Architecture | ||
+ | * Part 4: European Filing Rules | ||
+ | * Part 5: Mapping between DPM and MDM. | ||
=Introduction= | =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). | + | |
+ | |||
+ | This document aims to provide an introduction to the topic of creating a conceptual model for storing multidimensional data which is received as XBRL instances that follow the rules defined by European taxonomies published by the European Banking Authority (EBA) or by the European Insurance and Occupational Pensions Authority (EIOPA). | ||
Disclaimer: | 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 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 Data Point Model (DPM) and European XBRL Taxonomy Architecture (EXTA), which build the basis of 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 Relational Online Analytical Processing (ROLAP). In this document UML data structures of a DPM are used because its comprehension will be easier. With the UML class model representing the description of the European filing rules, the present document visualises the mapping between UML meta classes and their correspondence in the form of database tables in the MDM. | ||
+ | |||
+ | This document consists of eight sections, save the bibliography. Section one explains working with a Multidimensional Data Model as a step towards working with the Relational Data Model. Section two makes a study of the architecture of XBRL, the databases and their aims, requirements and preconditions in catering for XBRL. Section three defines the conditions used for mapping from DPM to MDM. Section four is detailing point by point the mapping. Section five shows the metamodel defined by the European Banking Authority (EBA) through the FINREP (Financial Report) and COREP (Common Solvency Report) taxonomies and its mapping into MDM. Section six displays the MDM implemented in a relational database. Sections seven and eight show two implementation examples. | ||
- | 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= | =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== | + | The objective of this sample MDM is to provide a 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 need to be taken into account by defining an MDM for production use. |
- | 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. | + | |
+ | |||
+ | =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. Therefore, the audience of this document might be financial or economic institutions, agencies or universities with the intention 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 of XBRL. The reader is also expected to have knowledge in creating conceptual models for relational and multidimensional databases. | ||
+ | |||
+ | |||
+ | =Introduction to the Multidimensional Data Model (MDM)= | ||
+ | |||
+ | |||
+ | The multidimensional database is primarily used to create OLAP (On-line Analytical Process) applications and their databases using a fact table and set of dimensions. A multidimensional structure stores multidimensional data, that is to say, cubes. A cell or fact is an intersection consisting of elements that form the dimension(s) which in turn form a cube. A cell can have zero or more measures, but in this document only one measure is taken into account. | ||
+ | |||
+ | The Multidimensional Data Model (MDM) is used instead of the Relational Model, because the European architecture of economic-financial reports is relying on dimensions heavily, which makes implementation in MDM the logical choice. Moreover, the performance of queries is better in this type of database. | ||
+ | |||
+ | The goal of this document is to store the Data Point Model in a database, in an efficient, easy way. | ||
- | ==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= | =Preconditions on mapping= | ||
+ | |||
+ | |||
+ | ==Types of Database Management Sytems (DBMSs)== | ||
+ | |||
+ | In this section some types of DBMS's are analysed that appear suitable for storing DPM and XBRL documents. Only those databases are considered where, in a previous study, it seemed possible to store the DPM and to extend XML or XBRL documents. | ||
+ | |||
+ | The typical solutions are (figure 1): | ||
+ | |||
+ | * Hierarchical databases. | ||
+ | |||
+ | * Multidimensional databases. | ||
+ | |||
+ | * Relational databases. | ||
+ | |||
+ | * Mixtures, where, normally, the relational database is the base. | ||
+ | |||
+ | |||
+ | [[Image:Presentacion2U_F33MuyPeq.jpg]] | ||
+ | ;Figure 1. Different types of DBMSs. | ||
+ | |||
+ | Hierarchical databases (e.g. Tamino by Software AG, GT.M, IBM Information Management System (IMS)), which rely on the hierarchical model, that is to say, databases organized into a tree-like structure. In this structure, data uses relationships among their leaves. Each leaf on a superior level has 0..* relationship with leaves on the inferior level. A leaf on an inferior level only has a 0..1 relationship with a leaf on the superior level. | ||
+ | |||
+ | Multidimensional databases, not being based on relational databases, have the data is stored in an optimized multi-dimensional storage array, and not in a relational format. However, it is necessary to organize the information in a cube beforehand. These databases have very fast response times in queries. Examples of Multidimensional databases are: Essbase, icCube, Infor BI OLAP Server. | ||
+ | |||
+ | In relational databases the information is stored in relational format. But, moreover, in these databases it is possible to store cubes, but in a relational format, changing their internal structures. | ||
+ | |||
+ | In these solutions is necessary to analyse that database transactions are processed reliably. For this a database must fulfil ''ACID'' ('''A'''tomicity, '''C'''onsistency, '''I'''solation, and '''D'''urability) 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 valid state. | ||
+ | |||
+ | * 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 document will not analyse whether databases carry out the ACID properties. However, the majority of commercial Relational Database Management Systems (RDBMS) achieve these properties. These databases are very common in the Information Systems Departments of this environment. Examples of these RDBMS's, are Oracle, DB2 or MS SQL Server, amongst others. | ||
+ | |||
+ | |||
+ | |||
==Fundamental choices== | ==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. | + | This section will discuss, if the XBRL document instance is stored directly in the database in part or in a relational model. |
+ | |||
+ | There are two mainstream solutions for storing XBRL instances and their facts into a relational database system. The question is, when Information Systems (IS) receive a XBRL taxonomy or an instance document, how these XML documents can be stored with the lowest cost in resources in the database. As relational databases can only store relational data and XML documents are not relational, the mapping is not a direct process. | ||
+ | |||
+ | The topic to analyse is: | ||
+ | |||
+ | * Mapping the XBRL document instance in the Relational Model. | ||
+ | |||
+ | * Storing the XBRL document instance as a photo, or a PDF document in the database. | ||
+ | |||
+ | * Storing the XBRL as XML document or a XBRL document. | ||
+ | |||
+ | |||
+ | Not all XML documents can be mapped into the relational model. However, XBRL instance documents can be mapped to the relational database, as they show many references. The XBRL specification contains a very important aspect: validation by formulae. Formulae are based on XPath 2.0 (XML Path Language), which is based on XML. When the XBRL instance document is transformed into the relational model, the instance document cannot be validated by formulae anymore. Moreover, as these validations are based on the XBRL Formulae and Calculation specifications, the mapping to a RDBMS is not easy nor immediate [19]. As XBRL validation requires the use of XML enabled tools, this cannot be done in the RDBMS. There are many validators, both commercial and open source (Openfiling) in XML. On the other hand, the mapping of instance documents into a relational database is available through different commercial or open source vendors (Openfiling). | ||
+ | |||
+ | An XBRL instance document can be stored in a relational database as an XML document or in a relational format. Analyzing the queries in both solutions resulted in: | ||
+ | |||
+ | * In XML, these queries use XQuery and XPath. | ||
+ | |||
+ | - The end user has difficulties accessing the language of the queries directly or through tools; | ||
+ | |||
+ | - The query language is very specific. Experts in this language are necessaries; | ||
+ | |||
+ | - The tuning of XML documents is complex. | ||
+ | |||
+ | |||
+ | * Relational Database use the standard SQL. | ||
+ | |||
+ | - The end user can obtain the data in an easy way through spread sheets, linked tables or other tools; | ||
+ | |||
+ | - The query language is a standard, and is part of university IT curricula; | ||
+ | |||
+ | - The performance and tuning of a relational database has been extensively analyzed. | ||
+ | |||
+ | |||
+ | • If the XBRL instance document is stored directly in the database (as a blob), the problems are the same but the RDBMS is an inferior level. Cases are: | ||
+ | |||
+ | - Storing as a photo (Blog or Clog); | ||
+ | |||
+ | - Storing as a XML document. | ||
+ | |||
+ | |||
+ | In the first case the database is only used as a storehouse. In the second case, storing as an XML document, with functions embedded in the engine of the database. This means that the database manager has embedded these functions in the engine. Today there are vendors that add 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 it is not XML, the cost of implementation therefore has to be evaluated, and the performance of the database must be re-tuned for optimization. | ||
+ | |||
+ | MS SQL Server has also utilities for working with XBRL that is necessary to analyse, in the same way. | ||
+ | |||
+ | Oracle 11g release 2 ( from ''11.2.0.3.0'') works with XBRL documents instances [http://docs.oracle.com/cd/E20212_01/doc/doc.11/e17070/intro.htm 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 [http://www.xbrl.org/xbrl-solutions-oracle Out oracle]; | ||
+ | |||
+ | |||
+ | Both the Microsoft and Oracle solutions have to be evaluated in terms of costs, resources, tuning and performance in the engine of the database. | ||
+ | |||
+ | In summary; this document is not considering storing the XML document (instance) as a whole, as it 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 on Openfiling). More XBRL-specific requirements 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 a possibility of an ‘undo’ (split) routine 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. However, different instances can coexist because, as it is explained below, each fact is defined in a time period and it belongs to an entity. | ||
+ | |||
+ | |||
+ | Table 1 below shows a summary of the possible advantages and disadvantages of both methods. | ||
+ | |||
- | '''Pros and cons of alternatives''' | + | ;Table 1. Pros and cons of alternatives. |
{| border="1" cellpadding="2" cellspacing="0" | {| border="1" cellpadding="2" cellspacing="0" | ||
- | !P | + | !Proposals |
!Native store | !Native store | ||
!Convert before store | !Convert before store | ||
Line 49: | Line 200: | ||
|(+)(-) | |(+)(-) | ||
|- | |- | ||
- | |Quantity of aspects to store (indirect from DTS) | + | |Quantity of aspects to store (indirect from ''Discoverable Taxonomy Set'' (DTS)) |
|(-) | |(-) | ||
|(+) | |(+) | ||
Line 81: | Line 232: | ||
|(+) | |(+) | ||
|} | |} | ||
+ | |||
==Fact definitions: presentation vs DPM== | ==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. | + | XBRL Taxonomies created with DPM contain two definitions of individual reportable facts: |
- | 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. | + | * Primaries, dimensions and members have readable labels and optional references to external documentation; |
- | 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. | + | * 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. |
- | 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. | + | Since there is no guarantee that both definitions will match, a reported fact can rely on either definition. It depends on whether 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 then the DPM into XBRL concepts, i.e., 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 for 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 used in accordance with XBRL specifications. |
+ | |||
+ | When using the instance transformation option, the definitions have to be manually mapped to the internal definitions. This only needs to be done once. 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. Therefore, 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== | ==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. | ||
+ | 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. Table 2 shows different aspects of storing native XBRL facts. | ||
+ | |||
+ | |||
+ | ;Table 2. Diffenrent aspects of storing native XBRL facts. | ||
{| border="1" cellpadding="2" cellspacing="0" | {| border="1" cellpadding="2" cellspacing="0" | ||
!Technical part | !Technical part | ||
Line 107: | Line 263: | ||
!Comment | !Comment | ||
|- | |- | ||
- | |instance file name | + | |Instance file name. |
- | |optional hash code | + | |Optional hash code. |
- | |For NSA's working with assurance solutions | + | |For NSA's (National Supervisory Authorities) working with assurance solutions. |
|- | |- | ||
- | |root node xbrli:xbrl | + | |Root node ''xbrli:xbrl''. |
- | |characterset, and optional language, version and id | + | |Characterset, and optional language, version and id. |
| | | | ||
|- | |- | ||
- | |at least one link:schemaRef | + | |At least one ''link:schemaRef''. |
- | |contains an URI and a location | + | |Contains an URI (Uniform Resource Identifier) 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. | + | |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 | + | |Optional multiple ''link:linkbaseRef''. |
| | | | ||
- | |EBA will not be using these | + | |EBA will not be using these. |
|- | |- | ||
- | |at least one find:fIndicators | + | |At least one ''find:fIndicators''. |
- | |this contains multiple find:filingIndicator | + | |This contains multiple ''find:filingIndicator''. |
- | |the value is string based and represents a table. With EIOPA this node is called tableIndicator. | + | |The value is string based and represents a table. With EIOPA this node is called tableIndicator. |
|- | |- | ||
- | |optional multiple contexts using xbrli:context | + | |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. | + | |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 | + | |''xbrli:entity''. |
- | |contains an identifier value and its scheme URI value | + | |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). | |These represent the reporting entity with its unique identifier within the NSA and the owner of the identifiers (NSA). | ||
|- | |- | ||
- | |xbrli:period | + | |''xbrli:period''. |
- | |contains either an instance date or a periodStart and periodEnd date. | + | |Contains either an instance date or a ''periodStart'' and ''periodEnd'' date. |
- | |XBRL allows also 'forever' but EBA has prohibited this use. | + | |XBRL allows also ''forever'' but EBA has prohibited this use. |
|- | |- | ||
- | |xbrli:segment and xbrli:scenario container | + | |''xbrli:segment'' and ''xbrli:scenario'' container. |
- | |contain dimensional aspects and/or proprietary XML schema based content | + | |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. | + | |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 | + | |Optional multiple ''xbrli:unit''. |
- | |each unit must have one ID attribute. It can hold either one measure or a set of nominator/denumerator. | + | |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. | + | |These are all ''QNames''. Each ''QName'' must have a value that goes with it. |
|- | |- | ||
- | |optional multiple facts | + | |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. | + | |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. | + | For the 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 English language and only one occurrence of 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. |
- | 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== | ==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. | + | 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. This applies even if that dimension is not used on the fact and even when the fact is not dimensional at all. In theory this means that all defaults apply to all facts since a single entry point will cover 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. |
+ | |||
+ | Naturally, these default dimension/member combinations must be identified in storage since they are not allowed in the instance. | ||
+ | |||
+ | The XML schema also allows nodes to be identified carrying a default value. In particular, when typed dimensions are being used there could be a typed element that carries a default. The EBA DTS does not use this option. | ||
+ | |||
+ | In the MDM the default member is another normal attribute of dimension. However, it is marked as attribute by default, because it is only relevant for the mapping process and has no a special meaning in the MDM. | ||
- | 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== | ==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. | + | |
+ | 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 an 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. | ||
<ul> | <ul> | ||
<li>Linkrole labels</li> | <li>Linkrole labels</li> | ||
Line 171: | Line 332: | ||
<li>EBA proprietary concepts have labels: module, framework, tableGroup, taxonomy, family</li> | <li>EBA proprietary concepts have labels: module, framework, tableGroup, taxonomy, family</li> | ||
</ul> | </ul> | ||
+ | |||
==Versioning== | ==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. | + | 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 breaks the trend on any reported fact. Manual intervention on mapping to local sources must be undertaken. |
+ | |||
==Changes on fact values== | ==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. | + | |
+ | 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 put in place to conserve the original fact values. Special care has to be taken with business rules that have been 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= | =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. | + | For the purposes of this document, the following terms and definitions applied are shown. The terms and definitions used in the mapping with Data Point Model are inspired by vocabulary already known from their use for describing multidimensional databases and Data Warehouses [1] [2] [3] [4] [5]. In turn, the DPM is based in the XBRL Meta-metadata Model [27]. 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 ([http://www.xbrlwiki.info/index.php?title=Main_Page Main Page]) [11] [22] only is shown a hyperlink to definition. | + | In this section, the set of definitions necessary for mapping the DPM in ROLAP are shown. The majority of the definitions are obtained from [6] [7] [8] [9] [10] [26]. When the definition is in the area of CEN WS XBRL([http://www.xbrlwiki.info/index.php?title=Main_Page CEN WS XBRL Main Page]) [11] [22] [26] only the name of the term is shown. |
The terms used directly or indirectly in the mapping of DPM in the MDM are: | The terms used directly or indirectly in the mapping of DPM in the MDM are: | ||
Line 233: | Line 398: | ||
* Context. | * 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]. | + | 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]. | + | 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= | =Mapping from Data Point Model to Multidimensional Data Model= | ||
- | <span style="background-color:yellow">[[Talk:DPM2MDM#Comment-02|Comment-03]]</span> | ||
+ | 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 for the transmission of economic information have emerged. XBRL represents business information, which is multidimensional. Specific to the European model is that the logical location for its storage is a Data Warehouse (DW) [25]. | ||
- | ==Introduction== | + | The Multidimensional Data Model (MDM) is a Conceptual Model and the Relational Model as well as the Data Point Model (DPM) is a Logical Model. The difference is that the Conceptual Model is nearest to the Universe of the Discourse (UD), nearest to the requirement of business user. The Logical Model is nearest to the Physical Model, the implementation in XBRL or in a Database. |
- | 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. | + | This document aims to help to design the Economic-financial information of reports [17]. For this reason, this set of pages is designed to help users of Information Systems create taxonomies using the DPM and in parallel, to map to the Relational Model using the MDM through the Relational Online Analytical Processing (ROLAP) tool. |
- | 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. | + | ==Introduction== |
+ | This section presents the mapping between the DPM and the Relational model through ROLAP. In this mapping the transformation from XBRL taxonomies is not handled, however its conversion is possible. [20]. Moreover, in this transformation no process of validation is established, only the DPM structure is mapped into the relational model. However, it is expected that the reader of this document can understand the DPM better when storing the DPM in a RDBMS (Relational Database Management System) using the MDM. | ||
- | [[Image:Presentacion2U F13Peq.jpg]] | + | The aim of this document is to obtain a star model representing the DPM. That is to say, the DPM is mapped to the MDM in databases. Figure 2 shows the MDM of the DPM. |
- | ;Figure 1. Star model of the DPM using ROLAP tool. | + | |
+ | The constructor ''FactTable'' of the figure is equivalent to the set of ''data points'' in the DPM. It is a Star model because ''BaseDomain'' (set of ''primary items''), ''Taxonomy'' and ''Context'' are linked to fact tables in three dimensions. The dimension Taxonomy is linked with the dimension ''Framework''. The ''Context'' is linked to the dimension ''Context_Dimension_DimensionAttributes''. The last one is Dimension the set of dimension/attributes of a dimension. And, to the set dimension/attributes of dimensions the dimensions end DimensionAttributes. It is also possible to add the dimension Family to dimensions but these are not drawn, not overcomplicating the drawing | ||
- | 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. | + | [[Image:Presentacion2U F13Peq.jpg]] |
+ | ;Figure 2. Star model of the DPM using ROLAP tool. | ||
- | 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. | + | In the annex B is shown its implementation in a RDBMS, moreover, it is also displayed the diagram of this implementation. |
- | + | ||
- | * A2T is the automatic transformation between attributes to taxonomy. | + | |
- | * C2C is the automatic transformation between concepts. | + | TThere are various references in the biography that deal with mapping from different sources to a relational database, especially from XML [12] [13] [15] [21] and about query in heterogeneous sources. In particular the paper by Levi et al. [14] is interesting. Nevertheless, the process of transformation of this section is based on Taentzer et al. [15]. This section deals with the different constructors that are corresponding in the DPM, step by step. |
+ | In this section, the process of conversion is analysed. Normally, a first step is to study the DPM element or elements to transform. Following this approach, the mapping between the DPM elements and the relational elements are gathered. The transformation process in the figures show the DPM UML graph on the left are the UML class diagrams and to the right the relational model (ROLAP) from the MDM. The black arrows between both are UML syntax but they are customized extensions, which are used to describe the graph transformation. The square between two black arrows contains an abbreviation which is mapped. In this document the following types of mapping rules between the two graphs are distinguished [15]: | ||
- | * C2CTx is the automatic transformation between classes to contexts. | + | * A2C is the automatic transformation between attributes of the DPM and columns of a table. |
- | * C2CTxDM is the automatic transformation between classes to Contexts, dimensions, and attributes of dimension. | + | * MC2T is the automatic transformation between metaclass of the DPM and a table. |
- | * C2D is the automatic transformation between classes of dimensions to dimensions. | + | * NON is the transformation of a comment to the Relational Model. |
- | + | ||
- | * 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. | + | |
Line 293: | Line 443: | ||
==Framework== | ==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]. | + | The figure 3 shows the perspective structural of the ''framework'' and this is an extract of the figure 1 in the DPM, referencied in [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]] | [[Image:Presentacion2U_F1Peq.jpg]] | ||
- | ;Figure 2. Structural perspective of the framework. | + | ;Figure 3. 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. | + | The figure 4 shows 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]] | [[Image:Framework.jpg]] | ||
- | ;Figure 3 Mapping for the framework. | + | ;Figure 4 Mapping for the framework. |
Line 319: | Line 469: | ||
! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ||
|- | |- | ||
- | | PublicElement || Label || Framework || name || string || | + | | PublicElement || Label || Framework || name || String || |
|- | |- | ||
| PublicElement || CreationDate || Framework || CreationDate || DateTime || | | PublicElement || CreationDate || Framework || CreationDate || DateTime || | ||
Line 325: | Line 475: | ||
| PublicElement || ModificationDate || Framework || ModificationDate || DateTime || | | PublicElement || ModificationDate || Framework || ModificationDate || DateTime || | ||
|- | |- | ||
- | | PublicElement || code || Framework || ID (Identifier) || String || pk | + | | 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. | + | 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 5 show the implementation. Example of this paragraph is in the annex A. |
- | [[Image:Presentacion2U_F26MuyPeq.jpg]] | + | [[Image:Presentacion2U_F3MuyPeq.jpg]] |
- | ;Figure 4. ''Framework'' in the Relational Model. | + | ;Figure 5. ''Framework'' in the Relational Model. |
==Taxonomy== | ==Taxonomy== | ||
- | In the same way the class taxonomy inherits of public element [11] [22], as figure 5 shows. | + | In the same way the class taxonomy inherits of public element [11] [22], as the figure 6 shows. |
[[Image:Presentacion2U_F3Peq.jpg]] | [[Image:Presentacion2U_F3Peq.jpg]] | ||
- | ;Figure 5. Structural perspective of the taxonomy. | + | ;Figure 6. 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). | + | In figure 7 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]] | [[Image:Taxonomy.jpg]] | ||
- | ;Figure 6. Mapping for the constructor taxonomy. | + | ;Figure 7. 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''. | + | Next table 4 shows the mapping of figure 7 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''. |
Line 372: | Line 522: | ||
| PublicElement || code || Taxonomy || ID (Identifier) || String || pk | | PublicElement || code || Taxonomy || ID (Identifier) || String || pk | ||
|- | |- | ||
- | | PublicElement || ValidFrom || Framework || ValidFrom || DateTime || | + | | PublicElement || ValidFrom || Taxonomy || ValidFrom || DateTime || |
|- | |- | ||
| PublicElement || ValidTo || Taxonomy || ValidTo || String || | | PublicElement || ValidTo || Taxonomy || ValidTo || String || | ||
Line 387: | Line 537: | ||
- | 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''. | + | In the physical implementation of the annex B is updated the table in the ROLAP design. 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 8 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]] | [[Image:Presentacion2U_F22MuyPeq.jpg]] | ||
- | ;Figure 7. Ralationship between ''framework'' and ''taxonomy'' in the relational model. | + | ;Figure 8. Ralationship between ''framework'' and ''taxonomy'' in the relational model. |
Line 397: | Line 547: | ||
==Dimensions== | ==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]. | + | In this section is defined the mapping of the constructor ''dimension''. The figure 9 shows a perspective of the structure of the dimension and this is an extract of the figure 1 in the DPM, referenced in [22]. |
[[Image:Presentacion2U_F5Peq.jpg]] | [[Image:Presentacion2U_F5Peq.jpg]] | ||
- | ;Figure 8. Structural perspective of the dimension. | + | ;Figure 9. 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. | + | 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 the Relational model (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. | + | The figure 10 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]] | [[Image:Dimension.jpg]] | ||
- | ;Figure 9. Mapping for dimensions. | + | ;Figure 10. 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''. | + | The table 5 shows the mapping of figure 10 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''. |
Line 425: | Line 575: | ||
! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ||
|- | |- | ||
- | | PublicElement || Label || Dimension || name || string || | + | | PublicElement || Label || Dimension || name || String || |
|- | |- | ||
| PublicElement || CreationDate || Dimension || CreationDate || DateTime || | | PublicElement || CreationDate || Dimension || CreationDate || DateTime || | ||
Line 437: | Line 587: | ||
| DictionaryElement || ValidTo || Dimension || ValidTo || String || | | DictionaryElement || ValidTo || Dimension || ValidTo || String || | ||
|- | |- | ||
- | | Dimension || EnumerableDimension || Dimension || isEnumerable || boolean || | + | | Dimension || EnumerableDimension || Dimension || isEnumerable || Boolean || |
|- | |- | ||
| Dimension || NonEnumerabledimension || Dimension || isEnumerable || Boolean || | | Dimension || NonEnumerabledimension || Dimension || isEnumerable || Boolean || | ||
Line 443: | Line 593: | ||
- | 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''. | + | The figure 11 depicts the costructor ''Dimension'' in the ROLAP design, 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]] | [[Image:Presentacion2U_F27MuyPeq.jpg]] | ||
- | ;Figure 10. Constructor Dimension in ROLAP. | + | ;Figure 11. 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. | + | In the Relational model the constructors ''enumerable'' and ''non-enumerable'' are the constructor dimension (figure 12). The entity ''Dimension'' 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]] | [[Image:Presentacion2U_F23MuyPeq.jpg]] | ||
- | ;Figure 11. Constructors ''enumerable'', ''non-enumerable'' and ''dimension'' in ROLAP. | + | ;Figure 12. 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''. | + | If the dimensions are defined, nextly the ''domain-members'' are defined. The figure 13 shows the mapping of the ''members'' in the design ROLAP. However, the name is changed, it is named as ''DimensionAttribute''. |
[[Image:DefinedMember.jpg]] | [[Image:DefinedMember.jpg]] | ||
- | ;Figure 12. Mapping of members in the ROLAP. | + | ;Figure 13. 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''. | + | The table 6 shows the mapping of the figure 13, 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''. |
Line 492: | Line 642: | ||
- | 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''. | + | The figure 14 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]] | [[Image:Presentacion2U_F28MuyPeq.jpg]] | ||
- | ;Figure 13. ''DimensionAttribute'' in the ROLAP. | + | ;Figure 14. ''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. | + | The figure 15 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]] | [[Image:Presentacion2U_F9Peq.jpg]] | ||
- | ;Figure 14. Mapping of ''Dimensions'' and ''Members''. | + | ;Figure 15. Mapping of ''Dimensions'' and ''Members''. |
Line 510: | Line 660: | ||
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 ''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. | + | The figure 16 shows the mapping of the ''context'' and the pairs ''dimension/member'' belong to the instance. |
[[Image:Context.jpg]] | [[Image:Context.jpg]] | ||
- | ;Figure 15. Mapping of the ''context'' and the pairs ''dimension/member''. | + | ;Figure 16. 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. | + | In the mapping to the design 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. | + | ;Table 7 — Mapping DPM vs ROLAP of the constructor ''context''. |
{| border="1" cellpadding="2" cellspacing="0" | {| border="1" cellpadding="2" cellspacing="0" | ||
! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ||
Line 529: | Line 679: | ||
! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ||
|- | |- | ||
- | | PublicElement || idContext || Context || contextDescr || string || | + | | PublicElement || idContext || Context || contextDescr || String || |
|- | |- | ||
| PublicElement || id || Context || IDContext || String || pk | | PublicElement || id || Context || IDContext || String || pk | ||
Line 543: | Line 693: | ||
- | ;Table 8 — Mapping DPM vs ROLAP. | + | ;Table 8 — Mapping DPM vs ROLAP of the constructor ''contextDimensionMemberPair''. |
{| border="1" cellpadding="2" cellspacing="0" | {| border="1" cellpadding="2" cellspacing="0" | ||
! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ||
Line 552: | Line 702: | ||
! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ! scope="col" width="80px" bgcolor="#E6E6E6" | Constrainst | ||
|- | |- | ||
- | | PublicElement || id || contextDimensionMemberPar || IDContext || String || pk | + | | PublicElement || id || contextDimensionMemberPair || IDContext || String || pk |
|- | |- | ||
- | | PublicElement || qNameDimension || contextDimensionMemberPar || dimensionID || String || pk | + | | PublicElement || qNameDimension || contextDimensionMemberPair || dimensionID || String || pk |
|- | |- | ||
- | | PublicElement || qNemeMeber || contextDimensionMemberPar || memberID || 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''. | + | In the physical implementation, the table ''context'' 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''. |
- | ;Figure 16. Relational model of the ''context'' and ''contextDimensionMemberPair''. | + | [[Image:Presentacion2U_F29MuyPeq.jpg]] |
+ | ;Figure 17. Relational model of the ''context'' and ''contextDimensionMemberPair''. | ||
Line 569: | Line 720: | ||
==Primary Items== | ==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''. | + | 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. And therefore has an important semantic content. The figure 18 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]: | This specific dimension, called ''primary item'' or ''base domain'' has the next features that it holds more semantics contents [9] [10]: | ||
Line 579: | Line 730: | ||
[[Image:Presentacion2U_F24Peq.jpg]] | [[Image:Presentacion2U_F24Peq.jpg]] | ||
- | ;Figure 17. Mapping for the Base Dimension (set of primary items). | + | ;Figure 18. Mapping for the ''Base Dimension'' (set of ''primary items''). |
+ | The table 9 shows in tabulate format this mapping of the figure 18. | ||
+ | |||
+ | |||
+ | ;Table 9 — Mapping DPM vs ROLAP of the constructor ''Base_Dimension''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Attribute/constructor | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Costructor ROLAP | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Attribute | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Type | ||
+ | ! scope="col" width="80px" bgcolor="#E6E6E6" | 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 19 shows the constructor ''Base_Dimension'' in the design ROLAP. In this implementation is added the ''balance'' with its operation of check, and the time period type with its check, although in this document is not dealed the validation. But, it is also added the user that has created the ''primary item''. | ||
+ | |||
+ | |||
+ | [[Image:Presentacion2U_F30MuyPeq.jpg]] | ||
+ | ;Figure 19. ''Base_dimension'' (set of ''primary items'') in the Relational Model. | ||
- | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | ||
==Fact table or Data points== | ==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. | + | 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 20 shows the mapping. |
+ | |||
+ | |||
+ | [[Image:Fact.jpg]] | ||
+ | ;Figure 20. Mapping of the ''data point'' and the ''fact table''. | ||
+ | |||
+ | |||
+ | The table 10 shows in tabulate format this mapping of the figure 20. | ||
+ | |||
+ | |||
+ | ;Table 10 — Mapping DPM vs ROLAP of the constructor ''Fact table''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Attribute/constructor | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Costructor ROLAP | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Attribute | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Type | ||
+ | ! scope="col" width="80px" bgcolor="#E6E6E6" | 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 21 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]] | [[Image:Presentacion2U_F25Peq.jpg]] | ||
- | ;Figure 16. diagram ROLAP of the fact table of the DPM. | + | ;Figure 21. Diagram ROLAP of the ''Fact table'' of the DPM. |
+ | |||
+ | |||
+ | |||
+ | ==Summary== | ||
+ | |||
+ | |||
+ | The figure 22 shows with more detail the figure 2 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 22. Diagram ROLAP, summary. | ||
+ | |||
+ | |||
+ | Next table 11 shows a sumary in tabulating format of the total mapping DPM versus design ROLAP. | ||
+ | |||
+ | |||
+ | ;Table 11 — Mapping DPM vs ROLAP of the set of constructors. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | DPM | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Attribute/constructor | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | Costructor ROLAP | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Attribute | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Type | ||
+ | ! scope="col" width="80px" bgcolor="#E6E6E6" | 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 || | ||
+ | |} | ||
+ | |||
Line 600: | Line 960: | ||
==Introduction.== | ==Introduction.== | ||
- | This annex maps the relational model of the DPM supplied by the EBA in the MDM, using ROLAP tool. | + | This annex maps the relational model of the DPM supplied by the EBA in the MDM, using the design ROLAP. |
- | 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 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), in this case MS 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. | 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.== | + | ==Creation of the structure and load of the DPM from 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. | + | In the annex B is shown the creation of the structure of the DPM in a 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, Microsoft® SQL Server® 2012 Express). 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). In this tool, the data source 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 of the database is DPM_EBA. After, all tables have to be selected, and the packet is submitted. The figure 23 shows a general view of the load of the Access in a RDBMS and the mapping to DPM in a Relational Database. | ||
- | 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]] | [[Image:Presentacion2U_F14Peq.jpg]] | ||
- | ;Figure 14. General view of Access and RDBMS of the EBA and the DPM in ROLAP. | + | ;Figure 23. General view of the mapping from Access to the RDBMS of the EBA and after the DPM in the design ROLAP. |
==Loading DPM_ROLAP from DPM_EBA.== | ==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. | + | This section makes a mapping from the database DPM_EBA to the database DPM_ROLAP, but with different models. The DPM_EBA is loaded in the above section. And the database 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 24, through its design and after the code. In the code of this document the dates are simulated. | ||
- | 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. | ||
[[Image:Presentacion2U_F15MuyPeq.jpg]] | [[Image:Presentacion2U_F15MuyPeq.jpg]] | ||
- | ;Figure 15. Mapping of the framework. | + | ;Figure 24. Mapping of the framework. |
+ | |||
The code of M1 is: | The code of M1 is: | ||
Line 633: | Line 996: | ||
-- M1 CODE | -- M1 CODE | ||
-- | -- | ||
- | --truncate table framework_DPM | + | delete from Framework |
- | delete from Framework_DPM | + | |
go | go | ||
- | insert into Framework_DPM (ID_Framework, nameFramework, valid_from, userID_created) | + | insert into Framework (ID_Framework, nameFramework, creationDate, userID_created) |
select FrameworkID as ID_Framework, | select FrameworkID as ID_Framework, | ||
- | FrameworkCode as nameFramework, | + | FrameworkCode as nameFramework, |
- | convert(datetime, '20130327', 112), | + | convert(datetime, '20130327', 112), |
- | 'EBA' | + | 'EBA' |
FROM DPM_EBA..ReportingFramework | FROM DPM_EBA..ReportingFramework | ||
go | go | ||
- | select * from Framework_DPM | + | select * from Framework |
go | 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. | + | |
+ | If the ''framework'' is loaded, next table is ''Taxonomy'', that is loaded from the database ''DPM_EBA..Taxonomy''. The figure 25 shows the mapping M2. | ||
+ | |||
[[Image:Presentacion2U_F16MuyPeq.jpg]] | [[Image:Presentacion2U_F16MuyPeq.jpg]] | ||
- | ;Figure 16. Mapping of the taxonomy. | + | ;Figure 25. Mapping of the taxonomy. |
+ | |||
The code of the mapping M2 is: | The code of the mapping M2 is: | ||
+ | |||
use DPM_ROLAP | use DPM_ROLAP | ||
Line 658: | Line 1,024: | ||
-- code M2 | -- code M2 | ||
-- | -- | ||
- | --truncate table taxonomy_DPM | + | --truncate table taxonomy |
- | delete from Taxonomy_DPM | + | delete from Taxonomy |
go | go | ||
- | insert into Taxonomy_DPM(ID_Taxonomy, ID_Framework, nameTaxonomy, | + | insert into Taxonomy(ID_Taxonomy, ID_Framework, nameTaxonomy, |
labelTaxonomy, valid_from, versionTax, | labelTaxonomy, valid_from, versionTax, | ||
- | date_created, userid_created) | + | creationDate, userid_created) |
select TaxonomyID as ID_Taxonomy, FrameworkID, TaxonomyCode, | select TaxonomyID as ID_Taxonomy, FrameworkID, TaxonomyCode, | ||
TaxonomyLabel, convert(datetime, '20130327', 112), '0', | TaxonomyLabel, convert(datetime, '20130327', 112), '0', | ||
Line 669: | Line 1,035: | ||
from [DPM_EBA].[dbo].[Taxonomy] | from [DPM_EBA].[dbo].[Taxonomy] | ||
go | go | ||
- | select * from Taxonomy_DPM | + | select * from Taxonomy |
go | go | ||
- | The next step is to obtain dimensions from the EBA, and it is shown in the figure 17. | + | The next step is to obtain dimensions from the EBA, and it is shown in the figure 26. |
+ | |||
[[Image:Presentacion2U_F17MuyPeq.jpg]] | [[Image:Presentacion2U_F17MuyPeq.jpg]] | ||
- | ;Figure 17. The mapping of dimensions. | + | ;Figure 26. The mapping of dimensions. |
+ | |||
The code of the mapping M3 is: | The code of the mapping M3 is: | ||
+ | |||
-- | -- | ||
-- code M3 | -- code M3 | ||
-- | -- | ||
- | insert into Dimension_DPM (dimensionID, dimensionCode, | + | go |
- | dimensiondescr, domainID, | + | delete from Dimension |
- | typedDim, | + | go |
- | typeData, | + | insert into Dimension (dimensionID, dimensionCode, |
+ | dimensionLabel, domainID, | ||
+ | isEnumerable, | ||
+ | typeData, creationDate, | ||
valid_from) | valid_from) | ||
select a.DimensionID, a.DimensionCode, | select a.DimensionID, a.DimensionCode, | ||
a.DimensionLabel as dimensiondescr, a.DomainID, | a.DimensionLabel as dimensiondescr, a.DomainID, | ||
a.IsTyped as typedDim, | a.IsTyped as typedDim, | ||
- | cast(b.DataTypeID as nvarchar(30)) as typeData, | + | cast(b.DataTypeID as nvarchar(30)) as typeData, |
+ | convert(datetime, '20130327', 112) as creationDate, | ||
convert(datetime, '20130327', 112) as valid_from | convert(datetime, '20130327', 112) as valid_from | ||
from DPM_EBA.dbo.Dimension a inner join DPM_EBA.dbo.Domain b | from DPM_EBA.dbo.Dimension a inner join DPM_EBA.dbo.Domain b | ||
Line 697: | Line 1,070: | ||
go | go | ||
select dimensionID, dimensionCode, | select dimensionID, dimensionCode, | ||
- | dimensiondescr, domainID, | + | dimensionLabel, domainID, |
- | typedDim, | + | isEnumerable, |
- | typeData, | + | typeData, creationDate, |
valid_from | valid_from | ||
- | from dimension_DPM | + | from Dimension |
go | go | ||
- | After, it is obtained the dimension attributes, as it is shown in the figure 18. | + | |
+ | After, it is obtained the dimension attributes, as it is shown in the figure 27. | ||
+ | |||
[[Image:Presentacion2U_F18MuyPeq.jpg]] | [[Image:Presentacion2U_F18MuyPeq.jpg]] | ||
- | ;Figure 18.- Mapping of the attributes of dimensión (ROLAP). | + | ;Figure 27.- Mapping of the attributes of dimensión (ROLAP). |
+ | |||
The code of the mapping M4 is: | The code of the mapping M4 is: | ||
+ | |||
--- | --- | ||
--- Code M4 | --- Code M4 | ||
--- | --- | ||
- | insert into Domain_Member_DPM(memberID, domainID, memberCode, | + | delete from DimensionAttribute |
- | memberDescr, byDefault, createFrom, | + | go |
+ | insert into DimensionAttribute(memberID, domainID, memberCode, | ||
+ | memberLabel, isDefault, creationDate, | ||
valid_from) | valid_from) | ||
Select MemberID, DomainID, MemberCode, | Select MemberID, DomainID, MemberCode, | ||
- | MemberLabel as memberDescr, IsDefaultMember as byDefault, | + | MemberLabel as memberLabel, IsDefaultMember as isDefault, |
- | convert(datetime, '20130327', 112) as createFrom, | + | convert(datetime, '20130327', 112) as creationDate, |
convert(datetime, '20130327', 112) as valid_from | convert(datetime, '20130327', 112) as valid_from | ||
from DPM_EBA.dbo.Member | from DPM_EBA.dbo.Member | ||
go | go | ||
- | select memberID, domainID, memberCode, memberDescr, | + | |
- | byDefault, createFrom, valid_from, | + | select memberID, domainID, memberCode, memberLabel, |
+ | isDefault, creationDate, valid_from, | ||
valid_to | valid_to | ||
- | from Domain_Member_DPM | + | from DimensionAttribute |
go | go | ||
- | The relations between dimensions and attributes of dimension is shown in the figure 19. | + | |
+ | The relations between dimensions and attributes of dimension is shown in the figure 28. | ||
+ | |||
[[Image:Presentacion2U_F19MuyPeq.jpg]] | [[Image:Presentacion2U_F19MuyPeq.jpg]] | ||
- | ;Figure 19. Relationship between dimensions and attributes of dimension. | + | ;Figure 28. Relationship between dimensions and attributes of dimension. |
+ | |||
The code of the mapping M5 is: | The code of the mapping M5 is: | ||
+ | |||
--- | --- | ||
--- Code M5 | --- Code M5 | ||
--- | --- | ||
- | insert into Dimension_Domain_Member_DPM( | + | go |
+ | |||
+ | delete from Dimension_DimensionAttribute | ||
+ | go | ||
+ | |||
+ | insert into Dimension_DimensionAttribute( | ||
dimensionID, memberID) | dimensionID, memberID) | ||
select DimensionID, MemberID | select DimensionID, MemberID | ||
from DPM_EBA.dbo.DimensionalCoordinate | from DPM_EBA.dbo.DimensionalCoordinate | ||
go | go | ||
+ | |||
select dimensionID, memberID | select dimensionID, memberID | ||
- | from Dimension_Domain_Member_DPM | + | from Dimension_DimensionAttribute |
go | 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). | + | |
+ | The next table is the ''context'' and the figure 29 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]] | [[Image:Presentacion2U_F20Peq.jpg]] | ||
- | ;Figure 20. Mapping of the context from DPM_EBA. | + | ;Figure 29. Mapping of the context from DPM_EBA. |
+ | |||
The code of the transformation M6: | The code of the transformation M6: | ||
+ | |||
--- | --- | ||
--- Code M6 | --- Code M6 | ||
--- | --- | ||
- | insert into context_DPM (contextID, ID_Taxonomy, contextDescr, codeTaxonomy) | + | go |
+ | |||
+ | delete from Context | ||
+ | go | ||
+ | |||
+ | insert into Context (contextID, ID_Taxonomy, contextDescr, codeTaxonomy) | ||
select g.ContextID, b.ModuleID as ID_Taxonomy, | select g.ContextID, b.ModuleID as ID_Taxonomy, | ||
h.ContextKey as contextDescr, b.ModuleCode as codeTaxonomy | h.ContextKey as contextDescr, b.ModuleCode as codeTaxonomy | ||
Line 774: | Line 1,173: | ||
on (f.DimensionID=g.DimensionID and f.MemberID=g.MemberID) | on (f.DimensionID=g.DimensionID and f.MemberID=g.MemberID) | ||
inner join DPM_EBA.dbo.ContextOfDataPoints h | inner join DPM_EBA.dbo.ContextOfDataPoints h | ||
- | on g.ContextID=h.ContextID | + | on g.ContextID=h.ContextID |
group by g.ContextID, b.ModuleID, b.ModuleCode, h.ContextKey | group by g.ContextID, b.ModuleID, b.ModuleCode, h.ContextKey | ||
go | go | ||
+ | |||
select contextID, ID_Taxonomy, contextDescr, codeTaxonomy | select contextID, ID_Taxonomy, contextDescr, codeTaxonomy | ||
- | from context_DPM | + | from Context |
go | go | ||
- | Regard the context and the dimensions and attributes of dimension the transformation can be analysed in the figure 21. | + | |
+ | |||
+ | In regard to the context and the dimensions and attributes of dimension the transformation can be analysed in the figure 30. | ||
+ | |||
[[Image:Presentacion2U_F21Peq.jpg]] | [[Image:Presentacion2U_F21Peq.jpg]] | ||
- | ;Figure 21. Mapping of the Context_Dim_Members. | + | ;Figure 30. Mapping of the Context_DimensionMemberPair. |
+ | |||
And the transformation code M7: | And the transformation code M7: | ||
+ | |||
--- | --- | ||
--- Code M7 | --- Code M7 | ||
--- | --- | ||
- | insert into Context_Dim_Members_DPM(contextID, ID_Taxonomy, dimensionID, memberID) | + | delete from contextDimensionMemberPair |
+ | go | ||
+ | insert into contextDimensionMemberPair(contextID, ID_Taxonomy, dimensionID, memberID) | ||
select g.ContextID, b.ModuleID as ID_Taxonomy, f.DimensionID, f.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 | from DPM_EBA.dbo.ModuleTable a inner join DPM_EBA.dbo.Module b | ||
Line 810: | Line 1,217: | ||
order by b.ModuleCode, g.ContextID | order by b.ModuleCode, g.ContextID | ||
go | go | ||
+ | |||
select contextID, ID_Taxonomy, dimensionID, memberID | select contextID, ID_Taxonomy, dimensionID, memberID | ||
- | from Context_Dim_Members_DPM | + | from contextDimensionMemberPair |
- | go | + | go |
+ | |||
- | =ANNEX B. Implementation of the DPM in ROLAP.= | + | |
+ | =ANNEX B. Implementation of the DPM in the MDM using the design ROLAP.= | ||
==Introduction.== | ==Introduction.== | ||
+ | |||
This annex is divided in two sections, Relational model and the creation of the tables. | This annex is divided in two sections, Relational model and the creation of the tables. | ||
+ | |||
==Structure ROLAP== | ==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. | + | The figure 31 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]] | [[Image:ROLAPDiagram.jpg]] | ||
- | ;Figure 22.- Structure of the MDM of the DPM. | + | ;Figure 31.- Structure of the MDM of the DPM. |
Line 833: | Line 1,246: | ||
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. | 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 | use DPM_ROLAP | ||
go | go | ||
- | IF OBJECT_ID(N'FactTable_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'FactTable', N'U') IS NOT NULL |
- | DROP TABLE FactTable_DPM; | + | DROP TABLE FactTable; |
go | 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 | + | IF OBJECT_ID(N'Period_DPM', N'U') IS NOT NULL |
- | DROP TRIGGER TR_Base_Domain_Balance_DPM; | + | DROP TABLE Period_DPM; |
go | go | ||
- | IF OBJECT_ID(N'Base_Domain_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'TR_Base_Dimension_Balance_DPM', N'TR') IS NOT NULL |
- | DROP TABLE Base_Domain_DPM; | + | DROP TRIGGER TR_Base_Dimension_Balance_DPM; |
go | go | ||
- | IF OBJECT_ID(N'Context_Dim_Members_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'Base_Dimension', N'U') IS NOT NULL |
- | DROP TABLE Context_Dim_Members_DPM; | + | DROP TABLE Base_Dimension; |
go | go | ||
- | IF OBJECT_ID(N'Context_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'contextDimensionMemberPair', N'U') IS NOT NULL |
- | DROP TABLE Context_DPM; | + | DROP TABLE contextDimensionMemberPair; |
go | go | ||
- | IF OBJECT_ID(N'Dimension_Domain_Member_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'Context', N'U') IS NOT NULL |
- | DROP TABLE Dimension_Domain_Member_DPM; | + | DROP TABLE Context; |
go | go | ||
- | IF OBJECT_ID(N'Domain_Member_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'Dimension_DimensionAttribute', N'U') IS NOT NULL |
- | DROP TABLE Domain_Member_DPM; | + | DROP TABLE Dimension_DimensionAttribute; |
go | go | ||
- | IF OBJECT_ID(N'Dimension_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'DimensionAttribute', N'U') IS NOT NULL |
- | DROP TABLE Dimension_DPM; | + | DROP TABLE DimensionAttribute; |
go | go | ||
- | IF OBJECT_ID(N'Taxonomy_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'Dimension', N'U') IS NOT NULL |
- | DROP TABLE Taxonomy_DPM; | + | DROP TABLE Dimension; |
go | go | ||
- | IF OBJECT_ID(N'Framework_DPM', N'U') IS NOT NULL | + | IF OBJECT_ID(N'Taxonomy', N'U') IS NOT NULL |
- | DROP TABLE Framework_DPM; | + | DROP TABLE Taxonomy; |
go | go | ||
- | create table Framework_DPM ( | + | IF OBJECT_ID(N'Framework', N'U') IS NOT NULL |
+ | DROP TABLE Framework; | ||
+ | go | ||
+ | |||
+ | create table Framework ( | ||
ID_Framework int primary key, | ID_Framework int primary key, | ||
nameFramework nvarchar(255) not null, | nameFramework nvarchar(255) not null, | ||
labelFramework nvarchar(255) null, | labelFramework nvarchar(255) null, | ||
- | valid_from datetime not null, | + | creationDate datetime not null default getdate(), |
- | valid_to datetime null, | + | modificationDate datetime null, |
- | date_created datetime not null default getdate(), | + | |
userID_created nvarchar(30) not null default current_user) | userID_created nvarchar(30) not null default current_user) | ||
- | + | ||
go | go | ||
- | create table Taxonomy_DPM ( | + | create table Taxonomy ( |
- | ID_Taxonomy int primary key, | + | ID_Taxonomy int primary key, |
- | ID_Framework int not null references Framework_DPM, | + | ID_Framework int not null references Framework, |
nameTaxonomy nvarchar(255) not null, | nameTaxonomy nvarchar(255) not null, | ||
labelTaxonomy nvarchar(255) not null, | labelTaxonomy nvarchar(255) not null, | ||
- | valid_from datetime not null, | + | creationDate datetime not null default getdate(), |
- | valid_to datetime null, | + | modificationDate datetime null, |
+ | valid_from datetime not null, | ||
+ | valid_to datetime null, | ||
versionTax nvarchar(10) not null, | versionTax nvarchar(10) not null, | ||
- | versionCreated datetime not null default getdate(), | + | versionDate datetime null, |
- | date_created datetime not null default getdate(), | + | schemaLocation nvarchar(255) null, |
- | userid_created nvarchar(30) not null default current_user) | + | userid_created nvarchar(30) not null default current_user) |
- | go | + | go |
+ | |||
- | + | create table Dimension ( | |
- | create table Dimension_DPM ( | + | |
dimensionID int not null primary key, | dimensionID int not null primary key, | ||
dimensionCode nvarchar(10) not null, --Code of approach dimension | dimensionCode nvarchar(10) not null, --Code of approach dimension | ||
- | dimensiondescr nvarchar(255) not null, | + | dimensionLabel nvarchar(255) not null, |
+ | creationDate datetime not null default getdate(), | ||
+ | modificationDate datetime null, | ||
domainID int not null, | domainID int not null, | ||
- | typedDim bit not null default(0), -- by default is explicit (0), if not typed (1). | + | isEnumerable bit not null default(0),-- by default is enumerable (0), if not is non-enumerable (1). |
- | typeData nvarchar(30) null, | + | typeData nvarchar(30), |
- | 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_from datetime not null, | ||
- | valid_to datetime null | + | valid_to datetime null |
- | ) | + | ) |
go | go | ||
- | create table Domain_Member_DPM( | + | create table DimensionAttribute( |
memberID int primary key, | memberID int primary key, | ||
- | domainID int not null, | + | domainID int not null, |
memberCode nvarchar(50) not null, | memberCode nvarchar(50) not null, | ||
- | memberDescr nvarchar(255) not null, | + | memberLabel nvarchar(255) not null, |
- | byDefault bit not null default(0), -- By default a domain-member is not the default | + | isDefault bit default(0), -- By default a domain-member is not the default |
- | createFrom datetime not null, | + | creationDate datetime not null default getdate(), |
+ | modificationDate datetime null, | ||
valid_from datetime not null, | valid_from datetime not null, | ||
- | valid_to datetime null | + | valid_to datetime null |
); | ); | ||
go | go | ||
- | create table Dimension_Domain_Member_DPM( | + | create table Dimension_DimensionAttribute( |
- | dimensionID int not null, | + | dimensionID int not null, |
- | memberID int not null, | + | memberID int not null, |
- | constraint PK_Dimension_Domain_Member_DPM | + | constraint PK_Dimension_DimensionAttribute |
- | primary key (dimensionID, memberID), | + | primary key (dimensionID, memberID), |
- | constraint FK_dimensionID foreign key (dimensionID) | + | constraint FK_dimensionID foreign key (dimensionID) |
- | references Dimension_DPM, | + | references Dimension, |
- | constraint FK_memberID foreign key (memberID) | + | constraint FK_memberID foreign key (memberID) |
- | references Domain_Member_DPM | + | references DimensionAttribute |
); | ); | ||
go | go | ||
- | create table Context_DPM ( | + | create table Context ( |
- | contextID int not null, | + | contextID int not null, |
- | ID_Taxonomy int not null, | + | ID_Taxonomy int not null, |
- | contextDescr nvarchar(255) not null, | + | contextDescr nvarchar(255) not null, |
- | codeTaxonomy nvarchar(255) null, | + | codeTaxonomy nvarchar(255) null, |
- | constraint PK_Context_DPM primary key (contextID, ID_Taxonomy)--, | + | 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) | --constraint FK_taxonomyID foreign key(ID_Taxonomy) | ||
- | -- references Taxonomy_DPM | + | -- references Taxonomy |
- | ); | + | ); |
- | go | + | |
- | + | create table contextDimensionMemberPair( | |
- | create table Context_Dim_Members_DPM( | + | contextID int not null, |
- | contextID int not null, | + | ID_Taxonomy int not null, |
- | ID_Taxonomy int not null, | + | dimensionID int not null, |
- | dimensionID int not null, | + | memberID int not null, |
- | memberID int not null, | + | constraint PK_contextDimensionMemberPair |
- | constraint PK_Context_Dim_Members_DPM | + | |
primary key (contextID, ID_Taxonomy, dimensionID, memberID), | primary key (contextID, ID_Taxonomy, dimensionID, memberID), | ||
- | constraint FK_Context_Dim_Members_DPM_ContextID_ID_Taxonomy | + | constraint FK_contextDimensionMemberPair_ContextID_ID_Taxonomy |
foreign key (contextID, ID_Taxonomy) | foreign key (contextID, ID_Taxonomy) | ||
- | references Context_DPM(contextID, ID_Taxonomy), | + | references Context(contextID, ID_Taxonomy), |
- | constraint FK_Context_Dim_Members_DPM_dimensionID | + | constraint FK_contextDimensionMemberPair_dimensionID |
foreign key (dimensionID, memberID) | foreign key (dimensionID, memberID) | ||
- | references Dimension_Domain_Member_DPM(dimensionID, memberID) | + | references Dimension_DimensionAttribute(dimensionID, memberID) |
) | ) | ||
go | go | ||
- | create table Base_Domain_DPM ( | + | create table Base_Dimension ( |
IDprimaryItem int identity(1,1) primary key, | IDprimaryItem int identity(1,1) primary key, | ||
code nvarchar(10) not null, | code nvarchar(10) not null, | ||
- | nsuri nvarchar(200) 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 | datatype nvarchar(20) not null | ||
check (DataType in ('String','Monetary','Integer','Numeric')), | check (DataType in ('String','Monetary','Integer','Numeric')), | ||
Line 990: | Line 1,406: | ||
balance nchar(10) null | balance nchar(10) null | ||
check (Balance in ('Credit','Debit')), | check (Balance in ('Credit','Debit')), | ||
- | date_created datetime not null default getdate(), | + | userid_created nvarchar(30) not null default current_user |
- | userid_created nvarchar(30) not null default current_user | + | |
) | ) | ||
go | go | ||
- | create trigger TR_Base_Domain_Balance_DPM ON Base_Domain_DPM | + | create trigger TR_Base_Dimension_Balance_DPM ON Base_Dimension |
after insert, update | after insert, update | ||
as | as | ||
Line 1,004: | Line 1,419: | ||
select @code =code, | select @code =code, | ||
@Balance =balance, | @Balance =balance, | ||
- | @DataType =datatype | + | @DataType =datatype |
from inserted | from inserted | ||
if @Balance is null and @DataType='Monetary' | if @Balance is null and @DataType='Monetary' | ||
Line 1,011: | Line 1,426: | ||
ATTENTION: The PrimaryItem with name: %s is not inserted.', 16, 1, @code) | ATTENTION: The PrimaryItem with name: %s is not inserted.', 16, 1, @code) | ||
rollback transaction | rollback transaction | ||
- | |||
end | end | ||
go | go | ||
Line 1,017: | Line 1,431: | ||
go | go | ||
create table Period_DPM( | create table Period_DPM( | ||
- | IDPeriod int identity (1,1) primary key, | + | IDPeriod int identity (1,1) primary key, |
- | start_date datetime null, | + | start_date datetime null, |
- | end_date_Instant datetime not null, | + | end_date_Instant datetime not null, |
- | instant_Year nvarchar(4) not null, | + | instant_Year nvarchar(4) not null, |
- | instant_month nvarchar(2) not null, | + | instant_month nvarchar(2) not null, |
- | instant_day nvarchar(2) not null, | + | instant_day nvarchar(2) not null, |
- | date_created datetime not null default getdate()) | + | date_created datetime not null default getdate()) |
+ | |||
go | go | ||
- | + | create table FactTable( | |
- | + | IDFact int primary key, -- Identification of the DPM or the Fact | |
- | + | ID_Taxonomy int not null, | |
- | create table FactTable_DPM( | + | contextID int not null, |
- | ID_DPM int primary key, -- Identification of the DPM or the Fact | + | IDprimaryItem int not null, |
- | contextID int not null, | + | |
- | ID_Taxonomy int not null, | + | |
- | IDprimaryItem int not null, | + | |
unit_simple nvarchar(10) null, --EUR, PURE, ETC. | unit_simple nvarchar(10) null, --EUR, PURE, ETC. | ||
unit_numerator nvarchar(10) null, | unit_numerator nvarchar(10) null, | ||
Line 1,043: | Line 1,455: | ||
boolean_value bit null, | boolean_value bit null, | ||
date_value datetime null, | date_value datetime null, | ||
- | nil_value char(1) null, --CHECK: Y ODER N | + | is_Null nchar(1) null, --CHECK: Y ODER N |
- | date_crated datetime not null default getdate(), | + | language nvarchar(40) null, |
- | userid_created nvarchar(30) not null default current_user, | + | userid_created nvarchar(30) null, |
CONSTRAINT CK_boolean_value_DPM CHECK (boolean_value in (1,0)),--CHECK: Y ODER N | 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 CK_nil_value_DPM CHECK (is_Null in ('Y','N','y','n')),--CHECK: Y ODER N |
- | constraint FK_FactTable_DPM_Context_Taxonomy | + | constraint FK_FactTable_Context_Taxonomy |
foreign Key (contextID, ID_Taxonomy) | foreign Key (contextID, ID_Taxonomy) | ||
- | references Context_DPM(contextID, ID_Taxonomy), | + | references Context(contextID, ID_Taxonomy), |
- | constraint FK_FactTable_DPM_Taxonomy | + | constraint FK_FactTable_Taxonomy |
foreign Key (ID_Taxonomy) | foreign Key (ID_Taxonomy) | ||
- | references Taxonomy_DPM(ID_Taxonomy), | + | references Taxonomy(ID_Taxonomy), |
- | constraint FK_FactTable_DPM_primaryItem | + | constraint FK_FactTable_primaryItem |
foreign Key (IDprimaryItem) | foreign Key (IDprimaryItem) | ||
- | references Base_Domain_DPM(IDprimaryItem) | + | references Base_Dimension(IDprimaryItem) |
) | ) | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | =ANNEX C. DPM of FINREP 2012 in the MDM using the design ROLAP.= | ||
+ | |||
+ | |||
+ | |||
+ | ==Introduction.== | ||
+ | |||
+ | |||
+ | This annex is based in the [http://www.eurofiling.info/finrepTaxonomy/taxonomy2012.shtml New 2012 FINREP taxonomy], and in research works referencied in [20] [24]. | ||
+ | |||
+ | 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, is obtained from the ''taxonomy'' and an example of ''XBRL Document Instance'', as the figure 32 shows [20]. The DPM is obtained from the ''taxonomy'', ''Metadata'', and the ''Fact Table'' from a ''XBRL Document Instance'', ''Data Points''. | ||
+ | |||
+ | |||
+ | [[Image:Presentacion2U_F31Peq.jpg]] | ||
+ | ;Figure 32. Process of creation of the ''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 33. However the ''families'' are out of this documment, because this document is more readable and less compless. | ||
+ | |||
+ | |||
+ | [[Image:Sheet1_DimensionsMuyPeq.jpg]] | ||
+ | ;Figure 33. The ''families''. | ||
+ | |||
+ | |||
+ | The table 12 shows only the ''families''. | ||
+ | |||
+ | |||
+ | ;Table 12 — DPM in format ROLAP for the constructor ''Family''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Code Family | ||
+ | |- | ||
+ | | CT | ||
+ | |- | ||
+ | | CI | ||
+ | |- | ||
+ | | AT | ||
+ | |- | ||
+ | | PL | ||
+ | |- | ||
+ | | SE | ||
+ | |- | ||
+ | | GA | ||
+ | |- | ||
+ | | CU | ||
+ | |- | ||
+ | | TI | ||
+ | |- | ||
+ | | CD | ||
+ | |- | ||
+ | | BA | ||
+ | |- | ||
+ | | CL | ||
+ | |- | ||
+ | | RP | ||
+ | |- | ||
+ | | RT | ||
+ | |- | ||
+ | | MA | ||
+ | |- | ||
+ | | CU | ||
+ | |- | ||
+ | | TI | ||
+ | |- | ||
+ | | RS | ||
+ | |- | ||
+ | | EC | ||
+ | |} | ||
+ | |||
+ | |||
+ | The next sheet, figure 34, shows the set of ''base dimension''. | ||
+ | |||
+ | |||
+ | [[Image:Sheet1_BaseDimMuyPeq.jpg]] | ||
+ | ;Figure 34. ''Base dimension''. | ||
+ | |||
+ | |||
+ | The table 13 shows the constructor ''BaseDimension''. | ||
+ | |||
+ | |||
+ | ;Table 13 — DPM in format ROLAP for the constructor ''BaseDimension''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Code BaseDimension | ||
+ | |- | ||
+ | | ad1 | ||
+ | |- | ||
+ | | dd14 | ||
+ | |- | ||
+ | | dd7 | ||
+ | |- | ||
+ | | md10 | ||
+ | |- | ||
+ | | md11 | ||
+ | |- | ||
+ | | md12 | ||
+ | |- | ||
+ | | md3 | ||
+ | |- | ||
+ | | mi1 | ||
+ | |- | ||
+ | | mi13 | ||
+ | |- | ||
+ | | mi2 | ||
+ | |- | ||
+ | | mi3 | ||
+ | |- | ||
+ | | mi4 | ||
+ | |- | ||
+ | | mi5 | ||
+ | |- | ||
+ | | mi8 | ||
+ | |- | ||
+ | | mi9 | ||
+ | |- | ||
+ | | pi15 | ||
+ | |- | ||
+ | | sd6 | ||
+ | |} | ||
+ | |||
+ | |||
+ | |||
+ | The next sheet, figure 35, shows only a sheet of a ''dimension'' and theirs ''domain-members''. | ||
+ | |||
+ | |||
+ | [[Image:Sheet3_Dim_MemberMuyPeq.jpg]] | ||
+ | ;Figure 35. 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | dimensionID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | dimensionID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | periodEndIntant | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''). | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="55px" bgcolor="#E6E6E6" | IDFact | ||
+ | ! scope="col" width="140px" bgcolor="#E6E6E6" | ID_Taxonomy | ||
+ | ! scope="col" width="230px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="130px" bgcolor="#E6E6E6" | IDprimaryItem | ||
+ | ! scope="col" width="110px" bgcolor="#E6E6E6" | unit_simple | ||
+ | ! scope="col" width="100px" bgcolor="#E6E6E6" | accuracy | ||
+ | ! scope="col" width="120px" bgcolor="#E6E6E6" | 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 in the MDM using the design ROLAP.= | ||
+ | |||
+ | |||
+ | |||
+ | ==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 document 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 36 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 36. Process of creation of the ''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''). | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | Code Dimension | ||
+ | |- | ||
+ | |PeriodicityDimension | ||
+ | |- | ||
+ | |SoloOrGroupDimension | ||
+ | |} | ||
+ | |||
+ | |||
+ | The table 22 shows the constructor ''DimensionAttribute''. | ||
+ | |||
+ | |||
+ | ;Table 22 — DPM in format ROLAP for the constructor ''DimensionAttribute''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | dimensionID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | dimensionID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="200px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | periodEndIntant | ||
+ | ! scope="col" width="150px" bgcolor="#E6E6E6" | 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''. | ||
+ | {| border="1" cellpadding="2" cellspacing="0" | ||
+ | ! scope="col" width="55px" bgcolor="#E6E6E6" | IDFact | ||
+ | ! scope="col" width="140px" bgcolor="#E6E6E6" | ID_Taxonomy | ||
+ | ! scope="col" width="230px" bgcolor="#E6E6E6" | contextID | ||
+ | ! scope="col" width="130px" bgcolor="#E6E6E6" | IDprimaryItem | ||
+ | ! scope="col" width="110px" bgcolor="#E6E6E6" | unit_simple | ||
+ | ! scope="col" width="100px" bgcolor="#E6E6E6" | accuracy | ||
+ | ! scope="col" width="120px" bgcolor="#E6E6E6" | 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= | =Bibliography= | ||
- | *[1] Inmon W. H. (2005) Building the Data Warehouse, 4th Edition. John Wiley & Sons 2005. | + | *[1] BUILDDW. Building the Data Warehouse. Inmon W. H, 4th Edition. John Wiley & Sons 2005. |
+ | |||
+ | *[2] DWTOOLKIT. The Data Warehouse Toolkit series. Kimball R. 2004. John Willey & Sons 1996-2004. | ||
+ | |||
+ | *[3] FUNDAMENTALSDW. Fundamentals of Data Warehouses. Jarke M., Lenzerini M., Vassiliou Y. and Vassiliadis P. 2nd edition, 2003S, Springer. | ||
+ | |||
+ | *[4] KIMBALLGROUP. Kimball Group, 2013. [http://www.rkimball.com/html/articles.html Kimball Group] | ||
+ | |||
+ | *[5] DWINSTITUTE. Data Warehouse Institute, 2013,[http://www.tdwi.org TDWI]. | ||
+ | |||
+ | *[6] XBRL21. Extensible Business Reporting Language (XBRL) 2.1. Engel P, Hamscher W., Shuetrim G., Vun Kannon D., Wallis H. July 2nd, 2008. XBRL International. [http://www.xbrl.org/Specification/XBRL-RECOMMENDATION-2003-12-31+Corrected-Errata-2008-07-02.htm Extensible Business Reporting Language (XBRL) 2.1]. | ||
- | *[2] Kimball R. (2004) The Data Warehouse Toolkit series. John Willey & Sons 1996-2004. | + | *[7] DMMATRIXSCHEMA. Data Model and Matrix Schemas. Schmehl K. November 16th, 2009. XI European Banking Supervisor, XBRL Workshop hosted by the Oesterreichische Nationalbank, Vienna. [http://www.eurofiling.info/11th_workshop/11th_Workshop.shtml XI European Banking Supervisors XBRL Workshop] |
- | *[3] Jarke M., Lenzerini M., Vassiliou Y. and Vassiliadis P, (2003). Fundamentals of Data Warehouses, 2nd edition, Springer. | + | *[8] XBRL_MDM. XBRL and the Multidimensional Data Model. Santos I, Castro E. 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. |
- | *[4] Kimball Group (2013). http://www.rkimball.com/html/articles.html. | + | *[9] XBRLINTEROPERABILITY. XBRL Interoperability through a Multidimensional Data Model. Santos I, Castro E. IADIS International Conference on Internet Technologies & Society (ITS2011), Shanghai, China. December 8th-10th, 2011. |
- | *[5] Data Warehouse Institute (2013). http://www.tdwi.org”. | + | *[10] XBRLDIM. XBRL Dimensions 1.0 XBRL International. Hernandez-Ros I, Wallis H. April 26th, 2006. http://www.xbrl.org/Specification/XDT-CR3-2006-04-26.rtf. |
- | *[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. | + | *[11] EURXBRLTAXONARCHIT. European XBRL Taxonomy Architecture V2.0. Declerck T, Homes R, Heinze K, 2013. CEN Workshop Agreement. [http://www.xbrlwiki.info/index.php?title=European_XBRL_Taxonomy_Architecture_V3.0 European XBRL Taxonomy Architecture V3.0]. |
- | *[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. | + | *[12] COMPLEXMODELS. A vision for management of complex models. Bernstein P A, Halevy A Y, Pottinger RA. SIGMOD Record 29 (4), 2000, 55-63. |
- | *[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. | + | *[13] TSIMMIS. The TSIMMIS Project: Integration of heterogeneous information sources. Chewathe S, García-Molina H, Hammer J, Ireland K, Papakonstantinou Y, Ullman J, and Widom J. In Proc. 10th Meeting of the Information Processing Societ of Japan, pages 7-18, 1994. |
- | *[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. | + | *[14] QUERING. Querying heterogeneous information sources using source descriptions. Levi A, Rajaraman A, and Ordille J. VLDB’96, Proceedings of Twenty-second International Conference on Very Large Data Bases. |
- | *[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. | + | *[15] MODELTRANSFORMATION. Model Transformation by Graph Transformation: A comparative Study. Taentzer G, Ehrig K, Guerra E, Lara J, Lengyel L, Levendovszky T, Prange U, Varro D, and Varro-Gaypay S. Model Transformation in Practice Workshop 2005 (MIIP2005). |
- | *[12] Bernstein P A, Halevy A Y, Pottinger RA (2000). A vision for management of complex models, SIGMOD Record 29 (4), 2000, 55-63. | + | *[16]REQUIEBA. Update on the technical standards on supervisory reporting requirements. EBA. 2013. http://www.eba.europa.eu/-/update-on-the-technical-standards-on-supervisory-reporting-requirements. |
- | *[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. | + | *[17] DPMVERSUSMDM. Data Point Model (DPM) versus Multidimensional Data Model (MDM), Santos I. Contribution for DPM chapter in CEN WS XBRL Plenary Session, Dublin, April 19th 2013. Hosted by Central Bank of Ireland. |
- | *[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. | + | *[18] ACADEMY. Academy works. 2013. Openfiling. [http://www.openfiling.info?page_id=286 Openfiling Academy]. |
- | *[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). | + | *[19] CMLM. Conceptual and Logical Models in the Design of Economic and Financial Reports Using the XBRL Specification. Santos I, Castro E, Velasco M. 2013. In the journal Business & Information Systems Engineering (BISE), under review. |
- | *[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. | + | *[20] POC1. Proof of concept of mapping a XBRL report versus a RDBMS. Santos I, Castro E. 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. |
- | *[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. | + | *[21] XERE. Xere: Towards a Natural Interoperability between XML and ER Diagrams. Della Penna G, Di Marco A, Intrigila B, Melatti I, and Pierantonio A- Lecture Notes in computer Science, volume 2621 2003, pp 356-371. Book: Fundamental Approaches to software Engineering. |
- | *[18] Academy works (2013) Openfiling. http://www.oprmfiling.info?page_id=286. | + | *[22] EEURDPM. uropean Data Point Methodology V2.0. Declerk T, Hommes R, Heinze K. 2013. CEN Workshop Agreement. [http://www.xbrlwiki.info/index.php?title=European_Data_Point_Methodology_V2.0#Framework European Data Point Methodology V2.0]. |
- | *[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. | + | *[23] EURFILINRULES. European Filing Rules. Declerk T, Hommes R, Heinze K. 2013. CEN Workshop Agreement. [http://www.wikixbrl.info/index.php?title=European_Filing_Rules European Filing Rules]. |
- | *[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. | + | *[24] AUTOMATION. Automation and mapping from the data model of the XBRL specification in Database. León Y.2012. Final Project of the Polytechnic School of the Carlos III University of Madrid, Spain. Date: October 4th, 2012. Tutors: Santos I, Castro E. [http://www.openfiling.info/?page_id=286 Automation and mapping] |
- | *[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. | + | * [25] MULTIDIMENSIONALCOREP. New Technical and Normative Challenges for XBRL: Multidimensional in the COREP Taxonomy. Boixo I, Flores F. July 18th, 2005. The International Journal of Digital Accounting Research, Vol. 5, N. 9, 2005, pages 79-104. ISSN:1577-8517. |
- | *[22] Declerk T, Hommes R, Heinze K (2013) European Data Point Methodology V2.0. CEN Workshop Agreement. [http://www.xbrlwiki.info/index.php?title=European_Data_Point_Methodology_V2.0#Framework European Data Point Methodology V2.0]. | + | * [26] CEN WS XBRL: Open Working Area. 2013. [http://www.xbrlwiki.info/index.php?title=Main_Page XBRL Spain Main page] |
- | *[23] Declerk T, Hommes R, Heinze K (2013) European Filing Rules. CEN Workshop Agreement. [http://www.wikixbrl.info/index.php?title=European_Filing_Rules European Filing Rules]. | + | * [27] XBRL Meta-metadata Model. Valencia J. 2011. Final project of Computer Engineering Technology Management, Carlos III University of Madrid. Date September 27th, 2011. Tutors: Santos I, Castro E. [http://www.openfiling.info/?page_id=286 XBRL Meta-metadata Model.] |
Current revision
Workshop Group
Working Group CWA1 Draft Experts: Ignacio Santos (Bank of Spain), Roland Hommes (Rhocon), Katrin Heinze (Deutsche Bundesbank)
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 1: European data point methodology for supervisory reporting.
- Part 2: Guidelines for data point modelling
- Part 3: European XBRL Taxonomy Architecture
- Part 4: European Filing Rules
- Part 5: Mapping between DPM and MDM.
Introduction
This document aims to provide an introduction to the topic of creating a conceptual model for storing multidimensional data which is received as XBRL instances that follow the rules defined by European taxonomies published by the European Banking Authority (EBA) or by the European Insurance and Occupational Pensions Authority (EIOPA).
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 Data Point Model (DPM) and European XBRL Taxonomy Architecture (EXTA), which build the basis of 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 Relational Online Analytical Processing (ROLAP). In this document UML data structures of a DPM are used because its comprehension will be easier. With the UML class model representing the description of the European filing rules, the present document visualises the mapping between UML meta classes and their correspondence in the form of database tables in the MDM.
This document consists of eight sections, save the bibliography. Section one explains working with a Multidimensional Data Model as a step towards working with the Relational Data Model. Section two makes a study of the architecture of XBRL, the databases and their aims, requirements and preconditions in catering for XBRL. Section three defines the conditions used for mapping from DPM to MDM. Section four is detailing point by point the mapping. Section five shows the metamodel defined by the European Banking Authority (EBA) through the FINREP (Financial Report) and COREP (Common Solvency Report) taxonomies and its mapping into MDM. Section six displays the MDM implemented in a relational database. Sections seven and eight show two implementation examples.
Objective
The objective of this sample MDM is to provide a 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 need to be taken into account by defining an MDM for production use.
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. Therefore, the audience of this document might be financial or economic institutions, agencies or universities with the intention 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 of XBRL. The reader is also expected to have knowledge in creating conceptual models for relational and multidimensional databases.
Introduction to the Multidimensional Data Model (MDM)
The multidimensional database is primarily used to create OLAP (On-line Analytical Process) applications and their databases using a fact table and set of dimensions. A multidimensional structure stores multidimensional data, that is to say, cubes. A cell or fact is an intersection consisting of elements that form the dimension(s) which in turn form a cube. A cell can have zero or more measures, but in this document only one measure is taken into account.
The Multidimensional Data Model (MDM) is used instead of the Relational Model, because the European architecture of economic-financial reports is relying on dimensions heavily, which makes implementation in MDM the logical choice. Moreover, the performance of queries is better in this type of database.
The goal of this document is to store the Data Point Model in a database, in an efficient, easy way.
Preconditions on mapping
Types of Database Management Sytems (DBMSs)
In this section some types of DBMS's are analysed that appear suitable for storing DPM and XBRL documents. Only those databases are considered where, in a previous study, it seemed possible to store the DPM and to extend XML or XBRL documents.
The typical solutions are (figure 1):
- Hierarchical databases.
- Multidimensional databases.
- Relational databases.
- Mixtures, where, normally, the relational database is the base.
- Figure 1. Different types of DBMSs.
Hierarchical databases (e.g. Tamino by Software AG, GT.M, IBM Information Management System (IMS)), which rely on the hierarchical model, that is to say, databases organized into a tree-like structure. In this structure, data uses relationships among their leaves. Each leaf on a superior level has 0..* relationship with leaves on the inferior level. A leaf on an inferior level only has a 0..1 relationship with a leaf on the superior level.
Multidimensional databases, not being based on relational databases, have the data is stored in an optimized multi-dimensional storage array, and not in a relational format. However, it is necessary to organize the information in a cube beforehand. These databases have very fast response times in queries. Examples of Multidimensional databases are: Essbase, icCube, Infor BI OLAP Server.
In relational databases the information is stored in relational format. But, moreover, in these databases it is possible to store cubes, but in a relational format, changing their internal structures.
In these solutions is necessary to analyse that database transactions are processed reliably. For this a database must fulfil ACID (Atomicity, Consistency, Isolation, and Durability) 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 valid state.
- 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 document will not analyse whether databases carry out the ACID properties. However, the majority of commercial Relational Database Management Systems (RDBMS) achieve these properties. These databases are very common in the Information Systems Departments of this environment. Examples of these RDBMS's, are Oracle, DB2 or MS SQL Server, amongst others.
Fundamental choices
This section will discuss, if the XBRL document instance is stored directly in the database in part or in a relational model.
There are two mainstream solutions for storing XBRL instances and their facts into a relational database system. The question is, when Information Systems (IS) receive a XBRL taxonomy or an instance document, how these XML documents can be stored with the lowest cost in resources in the database. As relational databases can only store relational data and XML documents are not relational, the mapping is not a direct process.
The topic to analyse is:
- Mapping the XBRL document instance in the Relational Model.
- Storing the XBRL document instance as a photo, or a PDF document in the database.
- Storing the XBRL as XML document or a XBRL document.
Not all XML documents can be mapped into the relational model. However, XBRL instance documents can be mapped to the relational database, as they show many references. The XBRL specification contains a very important aspect: validation by formulae. Formulae are based on XPath 2.0 (XML Path Language), which is based on XML. When the XBRL instance document is transformed into the relational model, the instance document cannot be validated by formulae anymore. Moreover, as these validations are based on the XBRL Formulae and Calculation specifications, the mapping to a RDBMS is not easy nor immediate [19]. As XBRL validation requires the use of XML enabled tools, this cannot be done in the RDBMS. There are many validators, both commercial and open source (Openfiling) in XML. On the other hand, the mapping of instance documents into a relational database is available through different commercial or open source vendors (Openfiling).
An XBRL instance document can be stored in a relational database as an XML document or in a relational format. Analyzing the queries in both solutions resulted in:
- In XML, these queries use XQuery and XPath.
- The end user has difficulties accessing the language of the queries directly or through tools;
- The query language is very specific. Experts in this language are necessaries;
- The tuning of XML documents is complex.
- Relational Database use the standard SQL.
- The end user can obtain the data in an easy way through spread sheets, linked tables or other tools;
- The query language is a standard, and is part of university IT curricula;
- The performance and tuning of a relational database has been extensively analyzed.
• If the XBRL instance document is stored directly in the database (as a blob), the problems are the same but the RDBMS is an inferior level. Cases are:
- Storing as a photo (Blog or Clog);
- Storing as a XML document.
In the first case the database is only used as a storehouse. In the second case, storing as an XML document, with functions embedded in the engine of the database. This means that the database manager has embedded these functions in the engine. Today there are vendors that add 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 it is not XML, the cost of implementation therefore has to be evaluated, and the performance of the database must be re-tuned for optimization.
MS SQL Server has also utilities for working with XBRL that is necessary to analyse, in the same way.
Oracle 11g release 2 ( from 11.2.0.3.0) 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;
Both the Microsoft and Oracle solutions have to be evaluated in terms of costs, resources, tuning and performance in the engine of the database.
In summary; this document is not considering storing the XML document (instance) as a whole, as it 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 on Openfiling). More XBRL-specific requirements 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 a possibility of an ‘undo’ (split) routine 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. However, different instances can coexist because, as it is explained below, each fact is defined in a time period and it belongs to an entity.
Table 1 below shows a summary of the possible advantages and disadvantages of both methods.
- Table 1. Pros and cons of alternatives.
Proposals | Native store | Convert before store |
---|---|---|
Quantity of aspects to store (direct from instance) | (+)(-) | (+)(-) |
Quantity of aspects to store (indirect from Discoverable Taxonomy Set (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 on whether 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 then the DPM into XBRL concepts, i.e., 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 for 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 used in accordance with XBRL specifications.
When using the instance transformation option, the definitions have to be manually mapped to the internal definitions. This only needs to be done once. 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. Therefore, 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. Table 2 shows different aspects of storing native XBRL facts.
- Table 2. Diffenrent aspects of storing native XBRL facts.
Technical part | Aspects | Comment |
---|---|---|
Instance file name. | Optional hash code. | For NSA's (National Supervisory Authorities) working with assurance solutions. |
Root node xbrli:xbrl. | Characterset, and optional language, version and id. | |
At least one link:schemaRef. | Contains an URI (Uniform Resource Identifier) 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 the 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 English language and only one occurrence of 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. This applies even if that dimension is not used on the fact and even when the fact is not dimensional at all. In theory this means that all defaults apply to all facts since a single entry point will cover 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.
Naturally, these default dimension/member combinations must be identified in storage since they are not allowed in the instance.
The XML schema also allows nodes to be identified carrying a default value. In particular, when typed dimensions are being used there could be a typed element that carries a default. The EBA DTS does not use this option.
In the MDM the default member is another normal attribute of dimension. However, it is marked as attribute by default, because it is only relevant for the mapping process and has no a special meaning in the MDM.
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 an 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 breaks 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 put in place to conserve the original fact values. Special care has to be taken with business rules that have been 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 applied are shown. The terms and definitions used in the mapping with Data Point Model are inspired by vocabulary already known from their use for describing multidimensional databases and Data Warehouses [1] [2] [3] [4] [5]. In turn, the DPM is based in the XBRL Meta-metadata Model [27]. 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, the set of definitions necessary for mapping the DPM in ROLAP are shown. The majority of the definitions are obtained from [6] [7] [8] [9] [10] [26]. When the definition is in the area of CEN WS XBRL(CEN WS XBRL Main Page) [11] [22] [26] only the name of the term is shown.
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 for the transmission of economic information have emerged. XBRL represents business information, which is multidimensional. Specific to the European model is 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 well as the Data Point Model (DPM) is a Logical Model. The difference is that the Conceptual Model is nearest to the Universe of the Discourse (UD), nearest to the requirement of business user. The Logical Model is nearest to the Physical Model, the implementation in XBRL or in a Database.
This document aims to help to design the Economic-financial information of reports [17]. For this reason, this set of pages is designed to help users of Information Systems create taxonomies using the DPM and in parallel, to map to the Relational Model using the MDM through the Relational Online Analytical Processing (ROLAP) tool.
Introduction
This section presents the mapping between the DPM and the Relational model through ROLAP. In this mapping the transformation from XBRL taxonomies is not handled, however its conversion is possible. [20]. Moreover, in this transformation no process of validation is established, only the DPM structure is mapped into the relational model. However, it is expected that the reader of this document can understand the DPM better when storing the DPM in a RDBMS (Relational Database Management System) using the MDM.
The aim of this document is to obtain a star model representing the DPM. That is to say, the DPM is mapped to the MDM in databases. Figure 2 shows the MDM of the DPM.
The constructor FactTable of the figure is equivalent to the set of data points in the DPM. It is a Star model because BaseDomain (set of primary items), Taxonomy and Context are linked to fact tables in three dimensions. The dimension Taxonomy is linked with the dimension Framework. The Context is linked to the dimension Context_Dimension_DimensionAttributes. The last one is Dimension the set of dimension/attributes of a dimension. And, to the set dimension/attributes of dimensions the dimensions end DimensionAttributes. It is also possible to add the dimension Family to dimensions but these are not drawn, not overcomplicating the drawing
- Figure 2. 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.
TThere are various references in the biography that deal with mapping from different sources to a relational database, especially from XML [12] [13] [15] [21] and about query in heterogeneous sources. In particular the paper by Levi et al. [14] is interesting. Nevertheless, the process of transformation of this section is based on Taentzer et al. [15]. This section deals with the different constructors that are corresponding in the DPM, step by step. In this section, the process of conversion is analysed. Normally, a first step is to study the DPM element or elements to transform. Following this approach, the mapping between the DPM elements and the relational elements are gathered. The transformation process in the figures show the DPM UML graph on the left are the UML class diagrams and to the right the relational model (ROLAP) from the MDM. The black arrows between both are UML syntax but they are customized extensions, which are used to describe the graph transformation. The square between two black arrows contains an abbreviation which is mapped. In this document the following types of mapping rules between the two graphs are distinguished [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 3 shows the perspective structural of the framework and this is an extract of the figure 1 in the DPM, referencied in [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 3. Structural perspective of the framework.
The figure 4 shows 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 4 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 5 show the implementation. Example of this paragraph is in the annex A.
- Figure 5. Framework in the Relational Model.
Taxonomy
In the same way the class taxonomy inherits of public element [11] [22], as the figure 6 shows.
- Figure 6. Structural perspective of the taxonomy.
In figure 7 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 7. Mapping for the constructor taxonomy.
Next table 4 shows the mapping of figure 7 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 design. 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 8 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.
- Figure 8. Ralationship between framework and taxonomy in the relational model.
Dimensions
In this section is defined the mapping of the constructor dimension. The figure 9 shows a perspective of the structure of the dimension and this is an extract of the figure 1 in the DPM, referenced in [22].
- Figure 9. 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 the Relational model (RM) is not shown until the document instance is obtained, but they have an specific type.
The figure 10 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.
- Figure 10. Mapping for dimensions.
The table 5 shows the mapping of figure 10 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 11 depicts the costructor Dimension in the ROLAP design, 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.
- Figure 11. Constructor Dimension in ROLAP.
In the Relational model the constructors enumerable and non-enumerable are the constructor dimension (figure 12). The entity Dimension 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.
- Figure 12. Constructors enumerable, non-enumerable and dimension in ROLAP.
If the dimensions are defined, nextly the domain-members are defined. The figure 13 shows the mapping of the members in the design ROLAP. However, the name is changed, it is named as DimensionAttribute.
- Figure 13. Mapping of members in the ROLAP.
The table 6 shows the mapping of the figure 13, 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 14 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.
- Figure 14. DimensionAttribute in the ROLAP.
The figure 15 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.
- Figure 15. 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 16 shows the mapping of the context and the pairs dimension/member belong to the instance.
- Figure 16. Mapping of the context and the pairs dimension/member.
In the mapping to the design 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 |
In the physical implementation, the table context 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.
- Figure 17. 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. And therefore has an important semantic content. The figure 18 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).
- Figure 18. Mapping for the Base Dimension (set of primary items).
The table 9 shows in tabulate format this mapping of the figure 18.
- 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 19 shows the constructor Base_Dimension in the design ROLAP. In this implementation is added the balance with its operation of check, and the time period type with its check, although in this document is not dealed the validation. But, it is also added the user that has created the primary item.
- Figure 19. 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 20 shows the mapping.
- Figure 20. Mapping of the data point and the fact table.
The table 10 shows in tabulate format this mapping of the figure 20.
- 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 21 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.
- Figure 21. Diagram ROLAP of the Fact table of the DPM.
Summary
The figure 22 shows with more detail the figure 2 of the Star model. In this figure is possible to see constructors with their columns and the relationships through the foreign keys.
- Figure 22. Diagram ROLAP, summary.
Next table 11 shows a sumary in tabulating format of the total mapping DPM versus design 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 the design ROLAP.
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), in this case MS 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 of the DPM from the EBA in a RDBMS.
In the annex B is shown the creation of the structure of the DPM in a 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, Microsoft® SQL Server® 2012 Express). 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). In this tool, the data source 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 of the database is DPM_EBA. After, all tables have to be selected, and the packet is submitted. The figure 23 shows a general view of the load of the Access in a RDBMS and the mapping to DPM in a Relational Database.
- Figure 23. General view of the mapping from Access to the RDBMS of the EBA and after the DPM in the design ROLAP.
Loading DPM_ROLAP from DPM_EBA.
This section makes a mapping from the database DPM_EBA to the database DPM_ROLAP, but with different models. The DPM_EBA is loaded in the above section. And the database 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 24, through its design and after the code. In the code of this document the dates are simulated.
- Figure 24. 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 25 shows the mapping M2.
- Figure 25. 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 26.
- Figure 26. 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 27.
- Figure 27.- 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 28.
- Figure 28. 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 29 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 29. 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
In regard to the context and the dimensions and attributes of dimension the transformation can be analysed in the figure 30.
- Figure 30. 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 the MDM using the design ROLAP.
Introduction.
This annex is divided in two sections, Relational model and the creation of the tables.
Structure ROLAP
The figure 31 shows the relational model of the Data Point Model (DPM), through a relational diagram obtained from Management Studio of MS SQL Server.
- Figure 31.- 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 in the MDM using the design ROLAP.
Introduction.
This annex is based in the New 2012 FINREP taxonomy, and in research works referencied in [20] [24].
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, is obtained from the taxonomy and an example of XBRL Document Instance, as the figure 32 shows [20]. The DPM is obtained from the taxonomy, Metadata, and the Fact Table from a XBRL Document Instance, Data Points.
- Figure 32. Process of creation of the 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 33. However the families are out of this documment, because this document is more readable and less compless.
- Figure 33. 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 34, shows the set of base dimension.
- Figure 34. 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 35, shows only a sheet of a dimension and theirs domain-members.
- Figure 35. 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 in the MDM using the design ROLAP.
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 document 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 36 shows. The DPM is obtained from the taxonomy, Metadata, and the Fact Table from a XBRL Document Instance, Data Points.
- Figure 36. Process of creation of the 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] BUILDDW. Building the Data Warehouse. Inmon W. H, 4th Edition. John Wiley & Sons 2005.
- [2] DWTOOLKIT. The Data Warehouse Toolkit series. Kimball R. 2004. John Willey & Sons 1996-2004.
- [3] FUNDAMENTALSDW. Fundamentals of Data Warehouses. Jarke M., Lenzerini M., Vassiliou Y. and Vassiliadis P. 2nd edition, 2003S, Springer.
- [4] KIMBALLGROUP. Kimball Group, 2013. Kimball Group
- [5] DWINSTITUTE. Data Warehouse Institute, 2013,TDWI.
- [6] XBRL21. Extensible Business Reporting Language (XBRL) 2.1. Engel P, Hamscher W., Shuetrim G., Vun Kannon D., Wallis H. July 2nd, 2008. XBRL International. Extensible Business Reporting Language (XBRL) 2.1.
- [7] DMMATRIXSCHEMA. Data Model and Matrix Schemas. Schmehl K. November 16th, 2009. XI European Banking Supervisor, XBRL Workshop hosted by the Oesterreichische Nationalbank, Vienna. XI European Banking Supervisors XBRL Workshop
- [8] XBRL_MDM. XBRL and the Multidimensional Data Model. Santos I, Castro E. 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] XBRLINTEROPERABILITY. XBRL Interoperability through a Multidimensional Data Model. Santos I, Castro E. IADIS International Conference on Internet Technologies & Society (ITS2011), Shanghai, China. December 8th-10th, 2011.
- [10] XBRLDIM. XBRL Dimensions 1.0 XBRL International. Hernandez-Ros I, Wallis H. April 26th, 2006. http://www.xbrl.org/Specification/XDT-CR3-2006-04-26.rtf.
- [11] EURXBRLTAXONARCHIT. European XBRL Taxonomy Architecture V2.0. Declerck T, Homes R, Heinze K, 2013. CEN Workshop Agreement. European XBRL Taxonomy Architecture V3.0.
- [12] COMPLEXMODELS. A vision for management of complex models. Bernstein P A, Halevy A Y, Pottinger RA. SIGMOD Record 29 (4), 2000, 55-63.
- [13] TSIMMIS. The TSIMMIS Project: Integration of heterogeneous information sources. Chewathe S, García-Molina H, Hammer J, Ireland K, Papakonstantinou Y, Ullman J, and Widom J. In Proc. 10th Meeting of the Information Processing Societ of Japan, pages 7-18, 1994.
- [14] QUERING. Querying heterogeneous information sources using source descriptions. Levi A, Rajaraman A, and Ordille J. VLDB’96, Proceedings of Twenty-second International Conference on Very Large Data Bases.
- [15] MODELTRANSFORMATION. Model Transformation by Graph Transformation: A comparative Study. Taentzer G, Ehrig K, Guerra E, Lara J, Lengyel L, Levendovszky T, Prange U, Varro D, and Varro-Gaypay S. Model Transformation in Practice Workshop 2005 (MIIP2005).
- [16]REQUIEBA. Update on the technical standards on supervisory reporting requirements. EBA. 2013. http://www.eba.europa.eu/-/update-on-the-technical-standards-on-supervisory-reporting-requirements.
- [17] DPMVERSUSMDM. Data Point Model (DPM) versus Multidimensional Data Model (MDM), Santos I. Contribution for DPM chapter in CEN WS XBRL Plenary Session, Dublin, April 19th 2013. Hosted by Central Bank of Ireland.
- [18] ACADEMY. Academy works. 2013. Openfiling. Openfiling Academy.
- [19] CMLM. Conceptual and Logical Models in the Design of Economic and Financial Reports Using the XBRL Specification. Santos I, Castro E, Velasco M. 2013. In the journal Business & Information Systems Engineering (BISE), under review.
- [20] POC1. Proof of concept of mapping a XBRL report versus a RDBMS. Santos I, Castro E. 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] XERE. Xere: Towards a Natural Interoperability between XML and ER Diagrams. Della Penna G, Di Marco A, Intrigila B, Melatti I, and Pierantonio A- Lecture Notes in computer Science, volume 2621 2003, pp 356-371. Book: Fundamental Approaches to software Engineering.
- [22] EEURDPM. uropean Data Point Methodology V2.0. Declerk T, Hommes R, Heinze K. 2013. CEN Workshop Agreement. European Data Point Methodology V2.0.
- [23] EURFILINRULES. European Filing Rules. Declerk T, Hommes R, Heinze K. 2013. CEN Workshop Agreement. European Filing Rules.
- [24] AUTOMATION. Automation and mapping from the data model of the XBRL specification in Database. León Y.2012. 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] MULTIDIMENSIONALCOREP. New Technical and Normative Challenges for XBRL: Multidimensional in the COREP Taxonomy. Boixo I, Flores F. July 18th, 2005. The International Journal of Digital Accounting Research, Vol. 5, N. 9, 2005, pages 79-104. ISSN:1577-8517.
- [26] CEN WS XBRL: Open Working Area. 2013. XBRL Spain Main page
- [27] XBRL Meta-metadata Model. Valencia J. 2011. Final project of Computer Engineering Technology Management, Carlos III University of Madrid. Date September 27th, 2011. Tutors: Santos I, Castro E. XBRL Meta-metadata Model.