Monday, March 7, 2011

Importing sdmx into Sql Server

I am trying to import and sdmx file into Microsoft SQL Server using SSIS (Integration Services). It is just an experiment and I went to OECD and downloaded migration data. It has around 150k data-points. The sdmx file is about 15k KB or just less than 15 MB. I downloaded the same data as PC-Axis file, it was about 1.5k KB or just less than 1.5MB. I also downloaded the data as csv. I got two files, one with data around 4k KB or 4 MB. The other was about the same size containing all kinds of metadata.

Anyway, the morale of the blog is not the size of sdmx versus other formats. In order to be able to work with the sdmx file I had to get the schemas since they define the namespaces. SSIS complained without all of them and I wasn´t able to do anything without the schemas. So I went to sdmx.org and downloded all the schemas.

Now my problem really started. There were 43 possible outputs that SSIS suggested! There were suggestions like DataSet__SeriesKey, Group, Series__Obs, Dataset__Attributes bla bla. Next thing I know, I´m reading xsd files and I can tell you they are quite well documented. I know SDMX fairly well, I understand the structure and know the concepts. I am still not able to find out which one of the 43 options given to me by SSIS is the correct one.

There has to be an easier way!

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.