View source for Walkthrough load and store XBRL taxonomies

== Introduction ==

This document describes how to:

# Install Reporting Standard software related with this walkthrough in a new computer
# Request an evaluation license suitable for all required packages
# Manually installing xbrl-packages into the local taxonomy catalog
# Open and Store all the information into a relational database
# Understanding the table model and how to query the information stored

== Install Reporting Standard software related with this walkthrough in a new computer ==

In order to run this walkthrough you need to install two packages:
* the regular Setup package that contains the main tools set
* the Database package that provide additional tools to work directly with the database

Visit the [[Walkthrough downloading Reporting Standard XBRL tools set]] page in order to find the download URLs for the packages that are not publicly available in the [https://www.reportingstandard.com/downloads/?lang=en downloads page]

Visit also the [[Hardware requirements]] and [[Software requirements]] pages and check your environment meets them.

* ''Step 1'': Download the latest version of the tools set '''RS_XBRL_Setup_X.Y.Z-xAR.msi''' package. Change the X, Y, Z and AR to met the latest version and your operating system architecture.
* ''Step 2'': Download the latest version of the database tools '''RS_XBRL_Database_X.Y.Z-xAR.msi''' package.
* ''Step 3'': Install the Setup package. Resist the temptation of executing any of the installed tools until Step 4 is finished. If you have already executed any of the installed tools and the software is requesting you to enter a license key, jump to the next section ''Request an evaluation license suitable for all required packages'' before doing step 4. The license is needed to run any of the tools installed on the setup package or the database package.
* ''Step 4'': Install the database package

== Request an evaluation license suitable for all required packages ==

Find the "License Manager" tool (the icon is a gold key) and request an evaluation license. There is more information on the [[Walkthrough requesting evaluation license]] page. The same dialog will pop-up if you start any other tool that requires a license and the license is not installed. Licenses are delivered to the email address you provide during the process. If you have questions please ask via email to info at reportingstandard dot com. You need a license with access to the regular tools set and also the XBRL Database tools set.

== Installing required XBRL packages into the local taxonomy catalog ==

Starting with version 2.8.5 of Reporting Standard tools set we incorporated support for the [https://www.xbrl.org/Specification/taxonomy-package/PWD-2014-01-15/taxonomy-package-PWD-2014-01-15.html Taxonomy Package 1.0 Public Working Draft 15 January 2014] in addition to that taxonomy catalog.

In order to speed up taxonomy loading process it is worth installing local copies of the remote official files. Installation of taxonomy packages is a simple straight forward step.

# Open XBRLizer or any other similar tool such as the Instance Document Viewer
# Select Options -> Catalog -> Remote Catalog
# From the list of taxonomies available, install the following ones: ''Asset Encumbrance 2014-03 (1.0.0)'', ''COmmon REPorting 2014-03 (2.0.2)'', ''EBA CRR Dictionary and FINancial REPorting 2014-03 (2.1.0)''

for installing a taxonomy package. Click on the line with the taxonomy name and click on the ''Install'' button below.

== Prepare the database to store XBRL information ==

Follow the instructions indicated on the [[Installing the XBRL Database]] pages in order to install and set up the table models. For this test we are using Microsoft SQL Server Express 2008, but the database can be installed on Oracle, IBM DB2 or PostgreSQL. Then come back to this page and continue with the next steps.

== Store a taxonomy into the database ==

Storing an XBRL report can be done form XBRLizer, the XBRL Viewer and most of the generic XBRL tools. Storing an XBRL report will store all files in the DTS too. Note the database is optimized and no information is stored twice so the files stored in one DTS will be reused for the next XBRL report referencing the same files at their corresponding official locations.

Storing a taxonomy can only be executed from the '''StoreXBRL.exe''' tool that is installed with the database package.

# Double click on the StoreXBRL.exe icon and launch the StoreXBRL tool.
# Select File -> Open and Save menu
# On the Open dialog window, click on the ''Add Cat...'' button to select an entry point form the taxonomy catalog
# Find the line for the ''FINancial REPorting 2014-03 (2.1.0)'' taxonomy (you should have it installed from previous steps)
# once you click on that line, two entry points appear below. Select the one that says ''Financial Reporting, Consolidated (Prudential scope) IFRS'' and click on OK.
# The official URL(s) for the selected entry point gets copied to the Open dialog and you can click on OK to start the loading and storing process

The complete process may take a couple of minutes. Loading the DTS in memory may require up to 6 Gb of RAM.

Now is time to get a cold coke from the fridge

== Doing some queries on the database model ==

The database table model is a normalized relational model composed of different layers. At the top is there is the Documents layer, that contains a physical copy of the files that compose the DTS. At the bottom there is the XML layer that contains several tables to define an XML Fragment composed by XML Nodes (every attribute is a node, every element is a fragment, the element value is a node too) and every node is a pair of a qualified name and qualified value. 

The model contains two structures that are related one to the other. The table structure represents the database tables, indexes, and foreign keys between the tables. The entity structure contains the XBRL object model that is serialized to the database. The complete documentation about those two models is [https://www.reportingstandard.com/pojo/documentation/entities/index.html on this web page]

The model is extensible. Users can define additional entities and tables and the add the user model to the XBRL model. During the DTS Storage process the user defined objects that are populated and related with the XBRL model will be written to the database too. This feature requires some knowledge about the technologies used to document the entity model so they will not be covered by this documentation. Please contact us if you want more information in this regard.

In order to do some queries we will use another tool that gets installed with the Database package. This is the '''RetrieveXBRL.exe''' tool. That tool contains several tabs. Each one of the tabs can be used to see what is in the database. The first tab, called HQL allows users to write their own query expressions. HQL is a query language for Java persistance it is based on SQL but is more powerful because it can handle the entities directly so there is no need to go down to the tables and relationships between the tables.

Let's run our first query:
# open RetrieveXBRL.exe (There shall be no need to define the database connection parameters. The connection parameters is shared across all tools using the XBRL database
# on the HQL Expression text box at the HQL tab, write the following expression: '''select conceptName from XBRLConcept''' and hit enter or press the '''Exec''' button

The result is just one column that contains the name of all concepts defined in all taxonomies stored into the database.

Let's run another query: '''select q.name from XBRLResource r, QName q where r.value = q''' on this example we are going to retrieve the name column from the QName table for all resources stored into the database. Note we are joining two tables here using the entities ''XBRLResource'' and ''QName'' and not the tables where the information is stored. The query shall return 61767 results if you are following all steps in this tutorial.

Another example: '''select c.parent.namespace, c.conceptName from XBRLConcept c where c.conceptName like 'x6'''' here we are selecting the XBRLConcept entity and filtering all them whose concept name is just '''x6'''. in our example, there are 24 definitions of this concept name in 24 different taxonomy schemas. In order to know the schema namespare where the concept is defined, we are going to include the taxonomy (the parent property of a concept) and from the taxonomy we will access the namespace definition (that is in another entity called Namespace). Now you can see how useful and powerful the dot notation is in obtaining information related to an entity that is stored in another related entity.

The last examples are using the XBRLRelationship table to select specific relationships and then exploring the from and to sides of them.

Obtain element labels from labels text: '''select r.to.value.name from XBRLRelationship r where r.arcrole.uri like '%element-label' and r.to.value.name like '%Assets%''''. This query obtains element labels that contains the word '''Assets''' anywhere in the label text.

This query obtains the element name for rows returned in the previous query '''select r.from.name.name from XBRLRelationship r where r.arcrole.uri like '%element-label' and r.to.value.name like '%Assets%''''

This screen capture merges the output of the previous two queries:
[[File:hql-sample-element-and-label.png]]

This query obtains concept labels from concept names: '''select r.to.value.name from XBRLRelationship r where r.arcrole.uri = '<nowiki>http://www.xbrl.org/2003/arcrole/concept-label</nowiki>' and r.from.conceptName = 'x6'''' this one obtains the labels defined for concepts where the concept name is x6. The query traverses the XBRLRelationship entity and filter those who has an specific arc role (concept-label) and, at the from part of the relationship, there is a concept where the concept name is 'x6'. You can now feel the power of the dot hehe. Once the relationships are selected, we will obtain the to side of the relationship and from that point we will go further to the 'value' (which is the XBRLResource) and from there to the 'name' which is the local part of the QName.

[[File:hql-sample-query.png]]

= Feedback =
Send us feedback about this information to: [mailto:support@reportingstandard.com Support at ReportingStandard dot com]

=Navigate=
[[Main Page]]