Analysis and Design of the Test Client



| | |

| | |

ESSNET on SDMX II

Harmonization of existing tools (Work Package IV)

ExcelSDMX GetData

Version 1.0

with Microsoft Framework Version 4.0 and SDMX-RI Version 2.4.1

08/2012

|Type of Document |Project deliverable |

|Reference: | |

|Issue: | |Revision: | |Status: |Final |

|Created by: |Dario Camol |Date: |01/12/2011 |

|Updated by: |Dario Camol, Bianchi Mauro | |12/10/2012 |

|Approved by: | |

Document Change Record

|Issue/ Revision|Date |Change |

|Start |01/12/2011 |First draft |

|1.0 |14/08/2012 |Draft |

|1.1 |18/08/2012 |Final draft |

|1.2 |30/09/2012 |Final Revision |

|1.3 |08/11/2012 |Final version |

| | | |

| | | |

| | | |

Table of contents Page

1 Introduction 5

1.1 Purpose 5

1.2 Reference Documents and Standards 5

2 Description of ExcelSDMX GetData project 6

2.1 Workflow 6

2.2 Will focus on… 10

3 Creation of the example 12

3.1 Include the SDMX-RI module SDMXIo as reference 12

3.2 Include the SDMXIo as source code (project) 13

3.3 Include the SDMXModel as reference 14

3.4 Include SDMXModel as reference 15

3.5 Getting Categories schema 16

3.6 Dimension listing 21

3.7 Codes listing 23

3.8 Getting data and writing to Excel 26

3.9 Common objects 30

3.9.1 Tools.StructureWarp 30

3.9.2 WebServiceLayer – WSClientStream 31

4 Appendix A – User Manual 35

4.1 Requirements 35

4.2 Set up 35

4.3 ExcelGetSDMXData (GUI) 40

4.4 Save/Open a query 45

4.5 Extract data to Excel 46

4.6 Export Data 46

4.7 Visualize the SDMX Query 47

List of figures Page

Figure 2.1-1 : Classes used 6

Figure 2.1-2 : Classes schema 7

Figure 2.1-3 : Workflow 8

Figure 2.1-4 : Sequence diagram 9

Figure 2.2-1 : Category schema 10

Figure 2.2-2 : Dimensions listing 10

Figuea 2.2-3 : Codes listing 11

Figure 2.2-4 : Getting data 11

Figure 3.1-1 : References 12

Figure 3.1-2 : Add references... 12

Figure 3.1-3 : dll selection 13

Figure 3.3-1 : References 14

Figure 3.3-2 : Add references... 14

Figure 3.3-3 : dll selection 15

Figure 3.5-1 : MetadataLoader class 16

Figure 3.9-1 : Registry to Structure message 30

Figure 3.9-2 : WebServiceConnector 32

Figura 3.9-3 : WebServiceLayer 33

Figure 3.9-4 : WSClientStream 34

List of tables Page

Table 1-1: Terms and Abbreviations 5

Introduction

1 Purpose

The purpose of this paper is to illustrate how to reuse some classes implemented into the SDMX-RI (version 2.4.1 for .NET) using an approach step by step. The example on which this document is based reuses the functionalities of getting structural metadata and data from a dissemination database mapped with the Mapping Assistant tool in order to import them into the software Microsoft Excel.

2 Reference Documents and Standards

For major details see the documentation relating to SDMX-RI tool:

1. SDMX-ML: SCHEMA AND DOCUMENTATION

2. Microsoft Excel ()

Standards and Conventions

Documents or standard named in the document

Terms and abbreviations

|Acronym |Definition |

|DSD |Data Structure Definition |

|MA |Mapping Assistant |

|NSI |National Statistical Institute |

|SQL |Structured Query Language |

|DDL |Data Definition Language |

Table 1-1: Terms and Abbreviations

Definitions

Definitions used in the document

|Term |Definition |

|[DLL Local path] |Indicates the path on your computer where to create the new class library project |

|[MA Local Path] |Indicates the path on your computer where unzip the Mapping Assistant source code |

Description of ExcelSDMX GetData project

The ExcelSDMX GetData is an Excel’s Add-in application, developed to get directly into Microsoft Excel the data available through a SDMX-RI (version 2.4.1 for .NET ) web service.

The Add-in provides a navigation three aimed to simplify the creation of an SDMX query in order to get data through the SDMX-RI web service and store into an Excel worksheet. The system will be able to store data into the file system in a CSV or SDMX format file.

Due to the complexity of the interface and of the whole project, this document will focus only on the part of the example related to the interaction between the Add-In and the SDMX-RI.

1 Workflow

In the picture below is shown the workflow of the application. In the Figure 1 is highlighted the set of the SDMX-RI classes used into the project.

[pic]

[pic]

*the classes highlighted with the red border interact with the SDMR-RI

In the figure above are shown all the classes of the project. The several objects can be aggregate in the following group:

• System classes: classes generated and managed by the system

• Common classes: classes containing global objects used in the whole solution. A specific class to contain the most used namespaces has been added.

• Data e metadata loader: classes aimed to interact with the SDMX-RI web service.

• Form management: classes aimed to manage the user interaction

• Extensions: the ClonableExtensions class, is a class created to add to the IList object the clonable property

• Tools: classes aimed to support the processes of the application

• M.A. SDMX-RI cloned class: classes from the M.A. that, due to the differences between the SDMX-RI infrastructure contained in the M.A. application and the standard version, will be used in order to not import both of the two packages

• SDMX-RI Web service: The class WebServiceLayer contained in this packaged creates a simple infrastructure in order to interact with the SDMX-RI web service.

• Excel Ribbon Management: predefined classes by Microsoft to add a new ribbon into the Microsoft Excel menu

In the figure above is shown the interaction among the set of the classes in order to get the data through the SDMX-RI web service.

The user, using Microsoft Excel, can access the information stored into the dissemination database through the Metadata Loader class. It sends a request to SDMX-RI web service and returns the list of the categories and related data flows. The user selects a dataflow. This selection allows him to get the list of the dimensions of the DSD associated to the dataflow, with the related dimension members. After the selection of the dimension members the application will generate the SDMX query. The request will be sent to the SDMX-RI data web service using the Data Loader class. The sequence of operation is shown in the sequence diagram below.

Both the two classes (Metadata Loader and Data Loader) interact with the web services using the WebServiceLayer, a customization of the classes of the SDMX-RI aimed to call the remote server. Furthermore, the classes use the SDMX-RI classes shown in Figure 2.1-2.

[pic]

Figure 2.1-4 : Sequence diagram

2 Will focus on…

As already said in the previous paragraph, the document will focus on some specific features of the application, the ones strictly related to the reuse of the SDMX-RI libraries.

• Getting Categories schema from the registry and loading into a tree

• Dimensions listing: dimensions related to the dataflow selected

[pic]

• Codes listing: codes related to the dimension selected

• Getting data and writing to Excel

[pic]

Figure 2.2-4 : Getting data

Creation of the example

In this chapter will be described the steps done in order to realize the example. The example can use one or more modules belonging to the SDMX-RI, some of these modules can be modified and new code can be added in order to realize the example.

1 Include the SDMX-RI module SDMXIo as reference

In order to import the “SDMXIo” module in the developer’s solution, the following steps must be performed:

Open the References folder in your Explore Solution tree in Visual Studio

[pic]

Figure 3.1-1 : References

Click the right button on your mouse until Visual Studio will show the Add References menu.

[pic]

Figure 3.1-2 : Add references...

A specific form will be shown, where you can select the SDMXIo.dll in your file system.

[pic]

[pic]

Figure 3.1-3 : dll selection

2 Include the SDMXIo as source code (project)

In order to include the SDMXIo as source code in you solution you need:

1. Download the 2.4.3 version of the SDMX-RI web service (NSIWebService_.NET package).

2. Unzip in your file system.

3. Open the References folder in your Explore Solution tree in Visual Studio and select your solution.

4. Click the right button and Visual Studio will visualize the menu with the Add existing project item.

5. Click on this item and select the SdmxIo.proj or SdmxIo.sln file in the unzipped folder

Ex.: \NSIWebService_.NET_v2.1.5_2011.06.10\src\SdmxIo\SdmxIo.proj

3 Include the SDMXModel as reference

In order to import the “SDMModel” module in the developer’s solution as a DLL, the following steps must be performed:

Open the References folder in your Explore Solution tree in Visual Studio

[pic]

Figure 3.3-1 : References

Click the right button on your mouse until Visual Studio will show the Add References menu.

[pic]

Figure 3.3-2 : Add references...

A specific form will be shown, where you can select the SDMXModel.dll in your file system.

[pic]

Figure 3.3-3 : dll selection

4 Include SDMXModel as reference

In order to include the SDMXModel as source code in you solution you need:

1. Download the 2.4.3 version of the SDMX-RI webservice (NSIWebService_.NET package).

2. Unzip in your file system.

3. Open the References folder in your Explore Solution tree in Visual Studio and select your solution.

4. Click the right button and Visual Studio will visualize the menu with the Add existing project item.

5. Click on this item and select the SdmxIo.proj or SdmxIo.sln file in the unzipped folder

Ex.: \NSIWebService_.NET_v2.1.5_2011.06.10\src\SdmxIo\SdmxIo.proj

5 Getting Categories schema

In this application the Categories schema is loaded into a specific object of the SDMX-RI structure. This object is required by the TreeView in order to visualize all the categories and the data flows available.

[pic]

In the text box above is shown the procedure aimed to load the Category Schema.

The class MetadataLoader takes as input two fixed queries to require all the categories and all the data flows, these queries are sent to the web service and they will return an object SDMXStructure ( Estat.Sdmx.Model.Structure.StructureBean) that will contain a list of the categories (SDMXStructure.CategorySchemes) and the related data flows (SDMXStructure.Dataflows).

[pic]

Figure 3.5-1 : MetadataLoader class

The query shown below represent the query fixed that is passed to the web service to retrieve the categories. This query is stored into a path predefined (CommonItem.CurrentSettings.QueriesPath ) and has a predefined name (getCategorySchema.xml).

[pic]

This query will be loaded into a System.Xml.XmlDocument object (queryDoc) through the method Load.

queryDoc.Load(CommonItem.CurrentSettings.QueriesPath + "\\getCategorySchema.xml");

The result will be stored into a string CategoryResultDoc. In the following row of source code is visualized the call to the InvokeMethod. It needs the SDMX query (queryDoc) and the web method required, in this case the specific one designed to return the structural metadata (WebServiceLayer._QUERYSTRUCTURE).

CategoryResultDoc = CommonItem.WebService.InvokeMethod(queryDoc, WebServiceLayer._QUERYSTRUCTURE);

[pic]

The message above is a sample of the message returned by the web services, it is a Registry message and for that reason cannot be loaded automatically into an Estat.Sdmx.Model.Structure.StructureBean, and therefore, part of the object must be extracted and wrapped into a structure message.

[pic]

This part is nested in a structure:CategorySchema xml node

[pic]

The same action must be performed for the linked data flows using another fixed query shown below, also in this case the query will be loaded into the queryDoc object.

[pic]

The query above will require the whole set of the data flows.

The same process will be executed:

• The getDataFlows.xml (it contains the SDMX query message) is loaded into the System.Xml.XmlDocument queryDoc

• The web method, aimed to return the structural metadata, of the SDMX-RI web service is invoked.

• The result (the string DataflowResultDoc) is loaded into a temporary System.Xml.XmlDocument tmp.

• A selection is performed on tmp in order to extract the Dataflow part from the registry message returned by the web service.

• The result of the selection will be nested in a new node

[pic]

The xml with the categories has been stored into tmpWord[0] and the xml related to the data flows in tmpWord[1]

The creation of these two variables, elements of an array of strings (tmpWord = new string[2]) is due to the need to wrap in the same structure message two information (categories and dataflow).

The method Tools.StructureWrap(tmpWord) (described in the paragraph 3.3.1) will create a new structure message that can be imported by the Estat.Sdmx.Model.Structure.StructureBean object.

Also if this document does not have the objective to describe the interaction between the user and the application, it could be useful add few sentences to explain how to easily visualize the categories schema and the dataflow.

The category schemas and the related data flows are shown using the classes System.Window.Forms.TreeView that was implemented using part of the code in the SDMX-RI Test Client application.

The frmMain.TreeManagement.cs file contains the source code used to manage the TreeView.

Two System.Collection.Generic.List must be defined.

[pic]

These two instances are loaded in the call of the ShowCategoriesDataFlows() method.

After the loading of the structural metadata the

• InitializeTreeView();

method will fill the TreeView used in the main form.

6 Dimension listing

The next step is the visualization of the linked dimensions.

In each leaf node of the TreeView, representing a dataflow are stored all the information required to “identify” an SDMX object:

• Name (in the specific case the DSD code)

• Versioning

• Agency ID

When the tree view is selected (AfterSelect event) the dataflow information are stored in an Estat.Sdmx.Model.Structure.DataflowBean throughout the SelectedNode.Tag .

The information can be retrieved separately using the code below:

[pic]

The DataflowBean will be used as input parameter for another method (ShowConcept) of the MetadataLoader class.

[pic]

This method has been developed to get through the SDMX-RI web service the dimension list and the codes of the related code lists(this feature will be described in the next paragraph).

[pic]

The LoadConcepts method is aimed to retrieve all the dimensions linked to a specific dataflow, represented by the DfBean object (Instance of an Estat.Sdmx.Model.Structure.DataflowBean).

This method use the same mechanism described in the previous paragraph in the case of the Categories Schema.

A similar process will be executed:

• An SDMX structure query will be loaded into the System.Xml.XmlDocument queryDoc (see below)

• The web method, aimed to return the structural metadata, of the SDMX-RI web service is invoked.

• The result (the string DataflowResultDoc) is loaded into a temporary xml document (System.Xml.XmlDocument), tmp

• A selection is performed on tmp in order to extract the Dataflow part from the registry message returned by the web service.

[pic]

• The result of the selection will be nested in a new node

[pic]

• The resulting string tmpWord[0] will be the parameter for the Tools.StructureWrap method that will wrap the string in an Estat.Sdmx.Model.Structure.StructureBean compatible format.

In this case the SDMX query has to be modified before to be loaded into the queryDoc

Below the generic version of the query getStructureWithReference.xml

[pic]

The generic version has got three fields that must be replaced:

• ###AGENCYID###

• ###KEYFAMILY###

• ###VERSION###

[pic]

The information replaced can be retrieved from the DfBean object passed to the ShowConcept method.

The method will return an Estat.Sdmx.Model.Structure.StructureBean object containing all the dimensions.

7 Codes listing

The listing of the codes of the code lists for all the dimension, is the following required step in order to generate an SDMX data query.

The previous paragraph described as retrieve a StructureBean that contains the list of the dimensions.

[pic]

The object above is a System.Collection.Arraylist of Estat.Sdmx.Model.Structure.DimensionBean.

In the application, elements of this ArrayList are taken in exam through a foreach cycle.

For each dimension is required the set of the dimension members and the result will be a tab of a System.Windows.Forms.TabControl

Below a simplified version of the code used in the application

[pic]

The method ShowDimensionMember is aimed to get the dimension member list.

Also in this case the ShowDimensionMember will use a method part of the MetadataLoader class: LoadDimensionMember.

[pic]

Below is shown the SDMX query stored in the getConstrainedDimensionMember.xml file.

In this case the query requires information related to the dataflow selected and to the specific dimension taken in exam.

[pic]

The following string will be replaced

|###CL_AGENCYID### |It represents the maintenance agency ID of the code list linked to the dimension taken in exam |

| | |

| |QueryText.Replace("###CL_AGENCYID###", DimensionItem.CodelistAgency); |

|###CL_CODELIST### |It represents the name of the code list linked to the dimension taken in exam |

| | |

| |QueryText.Replace("###CL_CODELIST###", DimensionItem.Codelist); |

|###CL_VERSION### |It represents the version of the code list linked to the dimension taken in exam |

| | |

| |QueryText.Replace("###CL_VERSION###", DimensionItem.CodelistVersion); |

|###DF_AGENCY### |It represents the maintenance agency ID of the dataflow of which the dimension taken in exam is|

| |part |

| | |

| |QueryText.Replace("###DF_AGENCYID###", DataFlowItem.AgencyId); |

|###DATAFLOWID### |It represents the name of the dataflow of which the dimension taken in exam is part |

| | |

| |QueryText.Replace("###DATAFLOWID###", ((IdentifiableArtefactBean)DataFlowItem).Id); |

|###DF_VERSION### |It represents version of the dataflow of which the dimension taken in exam is part |

| | |

| |QueryText.Replace("###DF_VERSION###", DataFlowItem.Version); |

The same mechanism, already seen in previous MetadataLoader methods described, will be applied:

• An SDMX structure query will be loaded into the System.Xml.XmlDocument queryDoc (see above)

• The web method, aimed to return the structural metadata, of the SDMX-RI web service is invoked.

• The result (the string DataflowResultDoc) is loaded into a temporary xml document (System.Xml.XmlDocument), tmp

• A selection is performed on tmp in order to extract the Codelists part from the registry message returned by the web service.

[pic]

• The result of the selection will be nested in a new node

[pic]

• The resulting string tmpWord[0] will be the parameter for the Tools.StructureWrap method that will wrap the string in an Estat.Sdmx.Model.Structure.StructureBean compatible format.

The elements of the result of the ShowDimensionMember call will be easily accessed using the properties of the Estat.Sdmx.Model.Structure.CodeListBean object

Below, an example about, how to get the name of the code list.

[pic]

8 Getting data and writing to Excel

After that all the structural metadata are retrieved and that all required filters are defined on the codes of the code lists, the SDMX query can be created.

In the specific case of the application the navigation through the structural metadata can be summarized:

• Use of a TreeView to visualize the categories and the data flows

• Use of a TabControl to visualize the dimensions. Each TabPage is reserved for one dimension.

• Use of a list of check box in the TabPage to allow the user to select the dimension members that will take part to the SDMX query.

In the case that the user completed the navigation and decided to generate the query the application will collect all the information by the user interface (check box selected…) and the common objects:

[pic]

in which are stored the selection performed by the user.

To help the collecting of the dimension member and the creation of the SDMX query will be used the

• ExcelGetSDMXData.classes.DataMetadataLoader.DimensionStatement

and

• ExcelGetSDMXData.classes.DataMetadataLoader.DataLoader

In the source code below is shown the double foreach cycle developed to collect the selected codes of the code lists selected.

The first cycle will focus on the TabPages (ConceptsTabControl.TabPages), by which will be extracted each TabPage (tbPage). From each TabPage will be extracted the list of the check boxes.

The information linked to the selected check box will be an item of the DimensionStatement instance.

[pic]

The next step will be the call to the following method.

[pic]

This call will unify the DimensionStatement items in a unique statement and will transform it in a SDMX query message. Below is shown the source code aimed to perform this task.

[pic]

The class ExcelGetSDMXData.classes.DataMetadataLoader.DataLoader provides the method getData to send the request to the web service and to import the data in Microsoft Excel.

[pic]

This method requires the following parameters:

• String Statm: the SDMX query statements related to the dimension member selected.

• String TimeStatm: the SDMX query statement related to the time dimension

• DataflowBean DataFlowBeanSelected: the instance of the Estat.Sdmx.Model.Structure.DataflowBean class that contains the information related to the selected dataflow

• bool ExtractDSD: flag created to allow the user to download also the DSD from the SDMX-RI web service.

In order to improve the performances has been decided to download the data, write them in a temporary file, open the file and write the content into Microsoft Excel.

The method DataLoader.WriteCSV is responsible for the downloading of the data and the writing of the file.

A similar process performed during the download of the structural metadata will be applied for the data:

• An SDMX data query will be loaded into the System.Xml.XmlDocument queryDoc (QueryGenericMessageAND.xml)

[pic]

• The string embedded the “###” characters are replaced.

|###TIME_DIMENSION### |the SDMX query statement related to the time dimension |

|###DATAFLOW### |the name of the dataflow selected |

|###DIMENSION_STATEMENT### |the SDMX query statements related to the dimension member selected |

• The web method, aimed to return the data, of the SDMX-RI web service is invoked.

[pic]

In this case, due to the possibility to get huge quantity of data, the data are requested using a streaming mechanism. For further information about this method see the paragraph 3.3.2.

[pic]

Once created the temporary file, using the features of the Microsoft.Office.Interop.Excel classes will be possible open it and fill an Excel worksheet.

[pic]

The code shown allows Excel to create a new worksheet and name it with the dataset code. In case of already existing worksheet with the same name the application will apply a random number to create a new name.

After clearing the eventual content of the cells of the worksheet the application starts reading the line of the temporary file and splitting the value of each row using the “;” character.

With a foreach cycle a specific cell, selected by the two counter “x - y”, will be chosen. The value extracted by the splitting will be written in the cell.

[pic]

9 Common objects

In this paragraph will be described, more in detail, a set of methods that, due to their specific features, are often reused in the application.

1 Tools.StructureWarp

As already explained in the previous paragraphs, the SDMX-RI web service returns, in case of answer to a structural metadata request, a SDMX registry message. This type of message cannot be used by Estat.Sdmx.Model.Structure.StructureBean because it accepts only SDMX structure message. The structure of these two messages is quite similar. A “trick” must be applied to convert a registry format to the structure format.

|Registry messages |Structure message |

| | |

| | |

| | |

| | |

| |NSICLIENT |

| |false |

| |2011-06-22T10:35:43 |

|  […] | |

|  | |

| | |

|  | |

| | |

|[...] | |

| | |

| | |

|  | |

|  | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|  […] | |

|  | |

| | |

|  | |

| | |

|[...] | |

| | |

| | |

|  | |

|  | |

| | |

| | |

| | |

Figure 3.9-1 : Registry to Structure message

From more than one registry message can be extracted a single item of the message. All these components can be a part of the structure message. The Tools.StructureWarp reads from the file system the header of the structure message and inserts, after the renaming of the main tag, the components in a unique message.

The message created can be easily imported and used by the SDMX-RI libraries.

[pic]

2 WebServiceLayer – WSClientStream

The WebServiceLayer and the WSClientStream are the methods aimed to send the request to the SDMX-RI webservice.

To perform this action the method interacts with the external library WebServiceConnector (the schema of this library is shown below).

The WebServiceConnector code has been extrapolated from the SDMX-RI TestClient application. In the figure below is shown the set of properties and methods of this dynamic link library. In the WsConfigurationSettings class are stored the information needed to open a connection with the SDMX-RI web service. In the application is available the whole set of settings but is used a strict subset of them:

|EndPoint |URL of the web service |

|WSDL |URL of the WSDL of the web service |

|EnableProxy |Boolean flag to specify if the client uses a proxy server |

|ProxyServer |URL of the proxy server |

|ProxyServerPort |Port number of the proxy server |

|ProxyUsername |User name of the proxy server |

|ProxyPassword |Password of the proxy server |

|Username |User name of the web service |

|Password |Password of the web service |

These information can be specified, by the user, in the specific settings form (see appendix A – User Manual).

[pic]

Figure 3.9-2 : WebServiceConnector

In the application the user can access the WebServiceLayer through the constructor WebServiceLayer, to the settings stored in the configuration file. The user can use two methods to invoke the web service:

1. public string InvokeMethod(XmlDocument QuerySDMX, string Action)

2. public void InvokeStreamMethod(XmlDocument QuerySDMX, string Action, string path, string extFile)

[pic]

Figura 3.9-3 : WebServiceLayer

[pic]

In the first method, the application performs a “not streamed” call to the web service. The method access to the WebServiceClient.InvokeMethod. This method is able to create the soap envelope, to send the request to the SDMX-RI web service and to return a string that contains the SDMX message.

In case of Soap Exception the method will throw an exception.

The streamed call will use a WSClientStream object. The mechanism of request and retrieve is more complex than the “not streamed” method and will be not described in this document. It is important to highlight that the call is quite similar to the version that does not perform a streamed connection. The complexity is totally transparent to the user that will notice the difference due only the specification of two additional parameters

• path: path of the temporary file that contains the result of the query

• extfile: type of result (SDMX or CSV)

[pic]

Figure 3.9-4 : WSClientStream

Appendix A – User Manual

1 Requirements

• Microsoft Excel 2007 or later

• Microsoft Framework 4.0

• SDMX Io.dll and SDMXMiModel.dll are developed with Microsoft Framework 2.0

• Visual Studio 2010 Tools for Office Runtime  ()

2 Set up

• Open the folder containing the setup files.

• Run setup.exe

• Automatically the software will be installed as Add-In in your version of Microsoft Excel.

[pic]

At the end of the installation, run Excel.

At the first execution after the installation the software will open the Settings windows.

• Web service settings

[pic]

First of all the user is obliged to add at least one web service by which the application will get the data. The web service is uniquely identified by the Web service URL field, not by the Name Field.

|Form field |Description |

|Name |..Name of the web service (it does not identify the ws) |

|Description | Description of the web service |

|Web service url* | URL of the web service (it identifies the ws) |

|User name | User name in case of login required |

|Password |Password in case of login required |

The picture above shown the form used to allow the user to submit the information related to the web service.

The web services can be added, removed or modified.

• Proxy server settings

In case of use a proxy server the user can fulfill the fields present into the Proxy Server settings panel.

• Folders settings

[pic]The application gives to the user the possibility to enable a log file where it can check the eventual errors. The user must specify the file and select the checkbox in order to activate the writing of the log.

• Flags settings

In the panel shown above the user can specify some settings to customize some features of the application. The user is obliged, in order to save the settings, to specify the preferred language of the structural metadata in case that the dissemination database provides more than one language.

|Form field |Description |

|Data language |Specify the language of the data. |

| |At the present time ISTAT makes available: |

| |it : Italian |

| |en : English |

|Load all dimension |The application loads all the dimensions and the related codes after the selection of the |

| |dataflow. If the option is unchecked the codes will be loaded after the selection of each |

| |dimension. |

|Load NOT constrained DSDs |If the user does not want to load (faster loading) the structural metadata constrained. A |

| |code list constrained contains only the dimension members for which are present data. In |

| |case of a not constrained DSD all the codes of code lists will be shown, increasing the |

| |possibility to get empty result during the data request. |

|Show start connection dialog |If there are more than one web services available at the starting phase the application |

| |will provide a dialog in order to choose the web service. Unchecking this option the |

| |application will be automatically connected to the default web service. |

|Excel series |Specify the maximum number of series that can be imported in Excel in one query. This |

| |feature has been provided taking into account the limits, in term of performances and of |

| |limits of rows and columns of Excel, in case of huge amount of data. |

3 ExcelGetSDMXData (GUI)

After the Set Up , the Add-In can be run opening Microsoft Excel and selecting the tab Add-In.

The specific new icon Get SDMX data will be available.

[pic]

If the “Show start connection dialog” option in the settings panel is active the application will show to the user the possibility to select the webservice with which start the connection.

On the main page the category schema will be shown. The underlined nodes represent the dataflows. With one click the dataflow with its related structural metadata will be loaded. In the menu will be enabled also the Open Query button, to open a previously saved query.

After the selection of the dataflow, in the right part of the application will be shown the set of the dimensions and the time dimension.

For each of these artefacts will be shown the list of the codes of the codelists associated to the dimensions.

For each dimension must be selected at least one dimension member before to send the query to the webservice.

[pic]

After the selection of the codes a set of action will be available:

• Save the query

• Extract the data to Excel

• Export the data

• Visualize the SDMX Query

All the action can be reached through the menu File and Action, or through the Icons on the top of the windows.

• File menu

o Open: Open a query previously saved

o Save: Save the current query

o Settings: Open the settings form

o Close: Close the application

• Action

o Extract data to Excel: Extract the data to the current Excel’s worksheet

o Export data: Export data in CSV or SDMX format

o SDMX Query preview: Visualize the query in SDMX 2.0 format

o Load all artifacts members: Load all the artifacts in one step. Disabling this option can be useful in case of dataset with a huge series of data. In this case the dimension member will be loaded on the click on each tab.

[pic]

[pic]

[pic]

4 Save/Open a query

In order to save a current query, the Save item in the menu or the specific icon in the top menu can be clicked.

If at least one dimension member is checked for each dimension the Save Query form will be shown to specify the customized name of the query.

[pic]

In order to open a previously saved query, the Open item in the menu or the specific icon in the top menu can be clicked.

The Open Query form will be shown. Double clicking on the query name will be loaded the query. The queries can be removed with the specific button on the right of the form.

[pic]

5 Extract data to Excel

In order to Extract the data to Excel, the Extract Data item in the menu or the specific icon in the top menu can be clicked.

If at least one dimension member is checked for each dimension the data will be imported into the current worksheet of Excel. The Add-In will be closed. If the user wants to perform a new data extraction, not overwriting the data just gotten, he must change the worksheet.

6 Export Data

In order to Export data in CSV or SDMX 2.0 Generic Data Message, the Export Data item in the menu or the specific icon in the top menu can be clicked.

The user has to specify which type of format is required. The user will be able specify the path and the file name of the exporting data.

[pic]

7 Visualize the SDMX Query

In order to visualize the SDMX Query message of the current query, the SDMX Query preview item in the menu or the specific icon in the top menu can be clicked.

-----------------------

Figure 2.1-1 : Classes used

NSI RI Modules

NSI-Client customized classes

Legend

SDMX-RI Registry

SDMXIo

namespace: Estat.Sdmx.IO.Metadata

User Interface

NSI classes customized to fill .NET TreeView

SDMXIo

namespace: Estat.Sdmx.Model.Structure

SDMXIo

namespace: Estat.Sdmx.Model.Base

Tools

MA SDMX-RI

cloned class

Common classes

Form management

Set of classes aimed to manage the user interaction

Tools

Data e metadata loader

Extensions

Set of classes aimed to manage the user interaction

Excel ribbon management

System classes

SDMX-RI Web service service

Figure 2.1-2 : Classes schema

Figure 2.1-3 : Workflow

Add-In

Microsoft Excel

Form management classes

Data e metadata loader

WebServiceLayer

SDMX-RI architecture classes

SDMX-RI webservice

User

Figure 2.2-1 : Category schema

Figure 2.2-2 : Dimensions listing

Figuea 2.2-3 : Codes listing

The complete set of library can be found in the latest version of the SDMX-RI web service, lib subfolder

Es: ..NSIWebService_.NET_v2.1.5_2011.06.10\lib

private void ShowCategoriesDataFlows()

{

try

{

MetadataLoader MetadataLoader = new MetadataLoader();

StructureBean SDMXStructure=null;

try

{

SDMXStructure = MetadataLoader.LoadCategorySchema();

}

catch (Exception ex)

{

CommonItem.ErrManger.CategorySchemaErrorMenagement(ex, this);

return;

}

_categories = SDMXStructure.CategorySchemes;

_dataflows = SDMXStructure.Dataflows;

CommonItem.LoadedStructure.CategorySchemes = _categories;

CommonItem.LoadedStructure.Dataflows = _dataflows;

}

catch (Exception ex)

{

…..

}

}

tmp.LoadXml(CategoryResultDoc);

nsmgr = CommonNamespace.RegistryNamespace(tmp.NameTable);

Req = tmp.SelectSingleNode("//registry:CategorySchemes", nsmgr);

tmpWord = new string[2];

tmpWord[0] = "";

tmpWord[0] += (Req.InnerXml);

tmpWord[0] += ("");

queryDoc.Load(CommonItem.CurrentSettings.QueriesPath + "\\getDataFlows.xml");

DataflowResultDoc = CommonItem.WebService.InvokeMethod(queryDoc, WebServiceLayer._QUERYSTRUCTURE);

tmp.LoadXml(DataflowResultDoc);

Req = tmp.SelectSingleNode("//registry:Dataflows", nsmgr);

tmpWord[1] = ("");

tmpWord[1] += (Req.InnerXml);

tmpWord[1] += ("");

private List _categories;

private List _dataflows;

_AgencyID = ((DataflowBean)tmpView.SelectedNode.Tag).AgencyId;

_KeyFamilyCode = ((DataflowBean)tmpView.SelectedNode.Tag).KeyFamilyRef.Id;

_Version = ((DataflowBean)tmpView.SelectedNode.Tag).Version;

ShowConcepts((DataflowBean)tmpView.SelectedNode.Tag);

private void ShowConcepts(DataflowBean DfBean);

Estat.Sdmx.Model.Structure.StructureBean

tmpStructure = MetadataLoader.LoadConcepts(DfBean,true);

XmlNode Reg=tmpDoc.SelectSingleNode("//registry:KeyFamilies", nsmgr);

tmpWord[0] = "";

tmpWord[0] += (Req.InnerXml);

tmpWord[0] += ("");

NSICLIENT

false

2011-07-15T11:50:38

###AGENCYID###

###KEYFAMILYID###

###VERSION###

string AgencyID = DfBean.KeyFamilyRef.AgencyID;

string KeyFamilyCode = DfBean.KeyFamilyRef.Id;

string Version = DfBean.KeyFamilyRef.Version;

QueryText = QueryText.Replace("###AGENCYID###", AgencyID);

QueryText = QueryText.Replace("###KEYFAMILYID###", KeyFamilyCode);

QueryText = QueryText.Replace("###VERSION###", Version);

tmpStructure.KeyFamilies[0].Dimensions

foreach (DimensionBean Dmn in tmpStructure.KeyFamilies[0].Dimensions)

{

tmpTab = new TabPage(Dmn.ConceptRef);

tmpTab.ImageIndex = 0;

tmpTag = new Tools.DataFlowDimensionWrap();

tmpTag.Dimension = Dmn;

tmpTag.DataFlow = DfBean;

tmpTab.Tag = tmpTag;

this.ConceptsTabControl.TabPages.Add(tmpTab);

[…]

ShowDimensionMember(Dmn, DfBean, tmpTab);

[…]

}

public StructureBean LoadDimensionMember(DimensionBean DimensionItem, DataflowBean DataFlowItem)

NSICLIENT

false

2011-07-15T11:50:38

###CL_AGENCYID###

###CL_CODELIST###

###CL_VERSION###

###DF_AGENCYID###

###DATAFLOWID###

###DF_VERSION###

XmlNode Reg=tmpDoc.SelectSingleNode("//registry:CodeLists", nsmgr);

tmpWord[0] = "";

tmpWord[0] += (Req.InnerXml);

tmpWord[0] += ("");

tmpCodeList = tmpStructure.CodeLists[0];

tbpPage.ToolTipText=tmpStructure.CodeLists[0].GetName(CommonItem.CurrentSettings.Language);

public static StructureBean LoadedStructure;

public static DatasetSelected SelectedDataset;

foreach (TabPage tbPage in ConceptsTabControl.TabPages)

{

if (tbPage.Tag != null)

{

chkLst = (CheckedListBox)(tbPage.Controls[0]);

DimName = tbPage.Text;

foreach (object chkBox in chkLst.CheckedItems)

{

DimValue = chkBox.ToString().Split('-')[0].Trim();

DimStatmnt.AddStatement(DimName, DimValue);

}

}

String Statm = DimStatmnt.getXMLStatements();

public String getXMLStatements()

{

try

{

String CurrentKey,Statement;

ItemValue tmp;

IEnumerator Ienum;

Ienum = _collectionStatements.GetEnumerator();

CurrentKey = _collectionStatements.First().Item;

Statement = "";

while (Ienum.MoveNext())

{

tmp = Ienum.Current;

if (tmp.Item != CurrentKey)

{

Statement += "";

Statement += "";

}

Statement += "" + tmp.Value + "";

CurrentKey = tmp.Item;

}

if (Statement.Substring(Statement.Length - 10,10)=="")

Statement=Statement.Substring(0, Statement.Length - 10); //Remove last

if (Statement.Substring(Statement.Length - 11, 11) != "")

Statement += ""; //Close eventually opened

return Statement;

}

catch (Exception e)

{

[…]

}

}

public void GetData(String Statm, String TimeStatm, DataflowBean DataFlowBeanSelected,bool ExtractDSD)

NSICLIENT

false

2011-08-01T13:27:28

###TIME_DIMENSION####

###DATAFLOW###

###DIMENSIONS_STATEMENT####

CommonItem.WebService.InvokeStreamMethod(queryDoc, WebServiceLayer._GETGENERICDATA, path, WebServiceLayer._CSV);

public void InvokeStreamMethod(XmlDocument QuerySDMX, string Action, string path, string extFile)

{

try

{

_settings.Operation = Action;

_clientStream = new WSClientStream(_settings);

_clientStream.InvokeMethod(QuerySDMX, path, extFile);

}

catch (Exception ex)

{

[..]

}

}

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook.Worksheets.Add();

try

{

worksheet.Name = CommonItem.SelectedDataset.DataSetCode;

}

catch (Exception ex)

{

worksheet.Name = CommonItem.SelectedDataset.DataSetCode + "_" + new Random().Next();

}

worksheet.Cells.ClearContents();

TxtRdr = new StreamReader(temporaryPath);

y = x = 1;

while ((line = TxtRdr.ReadLine()) != null)

{

row = line.Split(';');

x = 1;

foreach (String rowElement in row)

{

worksheet.get_Range(Tools.NumberToString(x, true) + y, misValue).Formula = rowElement;

x++;

}

y++;

}

[...]

[...]

public static string StructureWrap(string[] message)

{

try

{

TextReader txtRdr = new StreamReader(CommonItem.CurrentSettings.QueriesPath + "\\StructureHeader.xml");

String appoWord = txtRdr.ReadToEnd();

txtRdr.Close();

foreach (string tmpBlock in message)

{

appoWord += tmpBlock;

}

appoWord+=("");

return appoWord;

}

catch (Exception ex)

{

[…]

}

}

public string InvokeMethod(XmlDocument QuerySDMX, string Action)

{

try

{

_settings.Operation = Action;

_client = new WebServiceClient(_settings);

String result=_client.InvokeMethod(QuerySDMX, true);

if (result.IndexOf("") > 0)

{

throw new Exception("Error, SOAP FAULT" + result);

}

return result;

}

catch (Exception ex)

{

[…]

}

}

Save the current query

Export data in CSV or SDMX Generic format

Extract data to Excel

SDMX Query Preview

Enable/Disable the Load all artifacts members

Settings

Close the Add-In

Open previously saved query

Remove the selected query

Double click on the selected query in order to open the query or through the button Open

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download