The Macrobond SQL Database Connector

Introduction

With the Macrobond SQL Database connector you can use the Macrobond application to read time series from a SQL database.

Getting started

There is no standard way of storing time series in a SQL database. The Macrobond SQL connector can be configured to read data in most cases. Sometimes additional views or stored procedures are needed and in rare cases additional tables can be helpful.

The configuration of the Macrobond application consists primarily of a set of SQL queries that the application will use to retrieve data.

There are some requirements on the database imposed by the Macrobond application:

  • Each time series must have a unique identifier that can be used for retrieving the time series values and metadata. Any lower-case characters except double quote and colon are allowed, but it is best if the identifier begins with a-z and continues with a-z, 0-9 or underscore since such identifiers can be used unquoted in the Macrobond application.
  • A time series cannot start before year 1600 or end after year 2499.
  • The time series frequencies are limited to the periodicities of day, week, month, quarter, 4 months, half year and year.
  • There must be a query that given a time series identifier, returns a table where there is an observation on each row and where there is one column of dates and one column of values.
  • There must be a query that given a time series identifier, returns a table of metadata that contains at least the frequency and the description of the time series. The table may contain other metadata such as the region, currency, and scale.
  • There must be a .NET Framework 4.x Data Provider installed that can be used to read data from the data source. .NET comes with a connector for Microsoft SQL Server, ODBC and OLE DB. There are also providers available from other vendors, such as Oracle. On 64-bit Windows you should make sure to install both 32 and 64-bit versions of the provider.

Configuration file

For the Macrobond application to be able to read data from the database, it must be configured with some information on how to retrieve the data. This is done using a configuration file in the form of an XML file.

You specify the configuration file in the Configuration > Settings dialog (for MB pre-1.28: Edit > Settings) in the Macrobond application by adding a new data source of type SQL as in this example.

See here pre-1.27 version view

 

The prefix will be used as part of the series identifier, and you probably want to keep this short. If more than one user has access to the same database, the configuration file can be placed in a shared location on file server. You should take care to use the same prefix for all users since this will make it possible to share documents.

The file uses schema http://schemas.mbnd.eu/2011/sqlseriesprovider and has root element of the configuration file is called SqlSeriesProvider. There is a schema file available that can be used for validation here:
https://schemas.macrobond.com/SqlSeriesProvider.xsd.

The configuration file consists of five sections: information on how to connect to the database, information how to retrieve tables containing the values and metadata of time series, information how to interpret the metadata, information about the browse tree and information about text searching.

In many places '{n}' (where n is a number) will be replaced by a parameter as explained below. In such cases, the characters '{' and '}' are reserved and must be written as '{{' and '}}' if used for other purposes.

DataProvider

Name

The name of the provider to use. This refers to the name of the provider in Microsoft .NET Framework. It is typically one of the following:

System.Data.SqlClient

For all variants of MS SQL Server (on-prem or in Azure).

System.Data.OleDb

Can be used for Microsoft SQL Server 7 and later.

System.Data.Odbc

In most cases it is best to use a native .NET provider. Microsoft only provides a driver for Microsoft SQL Server, but there are for instance drivers for Oracle and MySql provided by the vendor. If there is no native provider, the OleDb provider is the best option. ODBC should be used only as a last resort.

For MySQL you need to download the provider from http://dev.mysql.com/downloads/connector/net/ and then use this name: MySql.Data.MySqlClient

ConnectionString

The connection string is unique to each provider. You may be able to find a hint here: https://www.dofactory.com/connection-strings

You can use the placeholders {0} and {1} for the username and password specified in the settings dialog. This is convenient if you do not want the same authentication for all users.

Example:
<sql:ConnectionString>
Data Source=MyServer;Initial Catalog=Series; User Id={0}; Password={1};
</sql:ConnectionString>

SQL In-house - connection strings provides more examples.

Series

Values

A SQL query or stored procedure that returns a table of all the values in the time series.

The following attributes can be used:

Attribute Default Comment
Command (required) Specifies either a query or stored procedure.
IsStoredProcedure false Specifies that the command is a stored procedure. In this case you must also specify ParameterName. You probably want to set MultipleIdentifiers to false in this case.
ParameterName The name of the parameter to be used in the command that contains the series identifier. Exactly how you use this name in a command, is provider specific, but almost all SQL providers use @ followed by the parameter name. Oracle is known for using : in some cases.

If no name is specified, you should use {0} as a placeholder for the identifier in the command. In this mode, you are limited to identifiers containing a-z, 0-9 and underscore. The benefit with this mode is that you can use MultipleIdentifiers. This mode also uses the attributes Separator and Quote.

MultipleIdentifiers true Multiple identifiers can be sent to the command. If set to false, multiple commands will be used instead, which is less efficient. You probably want to set this to false if you use stored procedures, since it might be difficult to handle a list of identifiers in this case.
Separator , The separator to use when multiple identifiers are sent. The separator is used to form a list like this: ‘usgdp’,’segdp’,’dkgdp’.
Quote The character used as quotes around the identifier when no ParameterName is used.
InterpretSkippedDateAsMissingValue false By default, dates that are not included, will be excluded from the series calendar. By setting this attribute to true, missing dates will instead be interpreted as missing values.

If the command is a query, the query text should include the text {0}. This will be replaced with the requested identifier or identifiers.

Identifier If MultipleIdentifiers is true, this column must be present and contain the series identifier specified in the command.
SeriesDate If this column is not included in the table, the StartDate metadata value must be specified for the series.
The SQL provider must be able to convert this to the .NET data type System.DateTime. If two dates refer to the same time period, the last value will be used. If there are no dates corresponding to a time period, that period will be skipped in the time series calendar.
SeriesValue A numerical value or null. A null value will be represented as a missing value in the time series.
The SQL provider must be able to convert this to the .NET data type System.Int, System.Float or System.Double.

A configuration for using a query can typically look like this when no parameter name is used:

<sql:Values sql:Command="SELECT Identifier, SeriesDate, SeriesValue FROM SeriesValues WHERE Identifier IN ({0})"/>

When the ParameterName attribute is specified, it could look like this

<sql:Values sql:ParameterName="series" sql:MultipleIdentifiers="false"  sql:Command="SELECT Identifier, SeriesDate, SeriesValue FROM SeriesValues WHERE Identifier = @series"/>

Metadata

The metadata contain information about the series in addition to the series values.

The Macrobond application will ask for metadata by using a query in a similar way to how it asks for time series values. Each piece of metadata consists of a metadata name and a metadata value.

You can specify default values and conversions for metadata. This is done in the Metadata section as explained later.

The metadata names are the names of the sections in the XML files and the name of records or columns in the table of metadata returned by the SQL command described below.

Attribute Default Comment
Command (required) Specifies either a query or stored procedure.
IsStoredProcedure false Specifies that the command is a stored procedure. In this case you must also specify ParameterName. You probably want to set MultipleIdentifiers to false in this case.
ParameterName The name of the parameter to be used in the command that contains the series identifier. Exactly how you use this name in a command, is provider specific, but almost all SQL providers use @ followed by the parameter name.

If no name is specified, you should use {0} as a placeholder for the identifier in the command. In this mode, you are limited to identifiers containing a-z, 0-9 and underscore. The benefit with this mode is that you can use MultipleIdentifiers. This mode also uses the attributes Separator and Quote.

MultipleIdentifiers true Multiple identifiers can be sent to the command. If set to false, multiple commands will be used instead, which is less efficient. You probably want to set this to false if you use stored procedures, since it might be difficult to handle a list of identifiers in this case.
Separator , The separator to use when multiple identifiers are sent. The separator is used to form a list like this: ‘usgdp’,’segdp’,’dkgdp’.
Quote The character used as quotes around the identifier when no ParameterName is used.
Layout Row Row or Column

If Layout is Row, the resulting table must contain the columns MetadataName and MetadataValue. The columns should be of a string type.

If Layout is Column, the column names should match the metadata names as described in the section about the Metadata element. The columns can then either be strings or the same type as the metadata. A null value will be interpreted as if the metadata is not specified, and the default value will be used.

If MultipleIdentifiers is true, the table must also contain a column called Identifier.

A configuration for using a query can typically look like this when no parameter name is used:

<sql:Metadata sql:Command="SELECT Identifier, MetadataName, MetadataValue FROM SeriesMetadata WHERE Identifier IN ({0})"/>

When the ParameterName attribute is specified, it could look like this.

<sql:Metadata sql:ParameterName="series" sql:MultipleIdentifiers="false" sql:Command="SELECT Identifier, MetadataName, MetadataValue FROM SeriesMetadata WHERE Identifier = @series"/>

Metadata

Most of the metadata attributes are optional and have default values. You can also specify default values in the configuration file that should be used if no value is returned from the query. In the configuration file you can also specify if metadata names, and values returned from the query should be translated.

Description

The metadata attribute called Description is a string that describes the series. The application will automatically combine the description with the Region to form a full description of the series. If you use the Region attribute, you typically do not want to include the region in the description.

Attribute Default Comment
Name Description The name of the record or column in the table.

Frequency

Can be any of the following values: Daily, Weekly, Monthly, BiMonthly, Quarterly, QuadMonthly, SemiAnnual, Annual

This attribute is not optional and must be specified in either the configuration file, in the table or both.

Attribute Default Comment
Name Frequency The default frequency.
Default (required) The default frequency.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To One of the frequency values used by Macrobond.
Example:
<sql:Frequency sql:Name="Freq" sql:Default="Monthly">
 <sql:Conversion sql:From="Yearly" sql:To="Annual" />
 <sql:Conversion sql:From="2*Months" sql:To="BiMonthly" />
</sql:Frequency>

DayMask

A string that specifies what days of the week that are used for daily time series. It is a list that contains one or more of the following values: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

Attribute Default Comment
Name DayMask The name of the record or column in the table.
Default Monday Tuesday Wednesday Thursday Friday The default set of weekdays.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To One of the frequency values used by Macrobond.
Example:
<sql:DayMask sql:Name="WeekDays" >
 <sql:Conversion sql:From="work" sql:To="Monday Tuesday Wednesday Thursday Friday" />
 <sql:Conversion sql:From="full" sql:To="Monday Tuesday Wednesday Thursday Friday Saturday Sunday" />
</sql:DayMask>

Class

The class is a string with one of the values Stock or Flow. This determines how the automatic frequency conversion in Macrobond works. If the value is Flow, the data will be aggregated when converted to a lower frequency and distributed over the period when converted to a higher frequency.

Attribute Default Comment
Name Class The name of the record or column in the table.
Default Stock The default class.

StartDate

The start date of a series can optionally be specified as part of the metadata. This attribute is not specified in either the configuration file or in the table, there must be a column called SeriesDate in the response from the Values query as explained in the section about the Series element.

Attribute Default Comment
Name StartDate The name of the record or column in the table.
Default (required) The default start date.

LastModifiedTimeStamp

This optional date can be accessed through APIs and displayed as dynamic text in presentations.

Attribute Default Comment
Name LastModifiedTimeStamp The name of the record or column in the table.
Default (none) The default date.

LastValueDate

This optional date is displayed in the Macrobond application as an optional column when searching for time series and browsing the database. This is used as an information for the user only in that context and is not used for determining the end of the time series.

Attribute Default Comment
Name LastValueDate The name of the record or column in the table.
Default (none) The default date of the last value.

LastValue

This optional value is displayed in the Macrobond application as an optional column when searching for time series and browsing the database. This is used as an information for the user only in that context and is not used for or checked against the time series values.

Attribute Default Comment
Name LastValue The name of the record or column in the table.
Default (none) The default value of the last value.

PreviousLastValue

This optional value is displayed in the Macrobond application as an optional column with the rate of change when searching for time series and browsing the database. This is used as an information for the user only in that context and is not used for or checked against the time series values.

Attribute Default Comment
Name PreviousLastValue The name of the record or column in the table.
Default (none) The default value of the previous last value.

Currency

The currency of the values in the time series expressed as a three letter ISO 4217 code such as USD and GDP. This will be used by the application to perform currency conversion. You can get a list of the supported countries at https://www.macrobond.com/go/currencyList/.

Attribute Default Comment
Name Currency The name of the record or column in the table.
Default (none) The default currency.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To One of the frequency values used by Macrobond.
Example:
<sql:Currency>
 <sql:Conversion sql:From="XEU" sql:To="EUR" />
</sql:Currency>

Region

Specifies what region or country that the series refers to. The region codes used in Macrobond are based on the two letter ISO 3166 codes. For example US, GB, DE, and SE. You can find a complete list of the supported regions at https://www.macrobond.com/go/regionList.

The region, if present, will be included in the title of the series.

Attribute Default Comment
Name Region The name of the record or column in the table.
Default (none) The default region.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To One of the currency codes used by Macrobond.
Example:
<sql:Region sql:Name="Ctry">
 <sql:Conversion sql:From="UK" sql:To="GB" />
 <sql:Conversion sql:From="USA" sql:To="US" />
</sql:Region>

Scale

The scale is an integer number that determines the scale as the power of ten

Attribute Default Comment
Name Scale The name of the record or column in the table.
Default 0 The default scale.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To One of the currency codes used by Macrobond.
Example:
<sql:Scale sql:Name="Magnitude">
 <sql:Conversion sql:From="1" sql:To="0" />
 <sql:Conversion sql:From="1000" sql:To="3" />
 <sql:Conversion sql:From="1000000" sql:To="6" />
</sql:Scale>

ForecastCutoffDate

All observation at this date and later will flagged as forecasts. The date must not be before the start or after the end of the series.

Attribute Default Comment
Name ForecastCutoffDate The name of the record or column in the table.
Example:
<sql:ForecastCutoffDate sql:Name="FDate" /

MaturityDate

Some analyses, such as the Yield curve analysis, can use this information to automatically configure the maturity length. If you set this value, you should also set RateMethod but not set the MaturityDays value.

Attribute Default Comment
Name MaturityDate The name of the record or column in the table.
Example:
<sql:MaturityDate sql:Name="MatDate" />

MaturityDays

This value is a positive integer that some analyses, such as the Yield curve analysis, can use this information to automatically configure the maturity length. If you set this value, you should also set RateMethod but not set the MaturityDate value. 1 week is 7 days, 1 month is 30 days and one year is 360 days.

Attribute Default Comment
Name MaturityDays The name of the record or column in the table.
Default (none) The default maturity length for all series.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To The maturity length used by Macrobond.
Example:
<sql:MaturityDays sql:Name="MatLen">
 <sql:Conversion sql:From="1M" sql:To="30" />
 <sql:Conversion sql:From="3M" sql:To="90" />
 <sql:Conversion sql:From="1Y" sql:To="360" />
</sql:MaturityDays>

RateMethod

This value should be either Simple or Effective that some analyses, such as the Yield curve analysis, can use this information for automatically configuration. If you set this value, you should also set one of the MaturityDate or MaturityLength values.

Attribute Default Comment
Name RateMethod The name of the record or column in the table.
Default (none) The default rate method.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To The rate method used by Macrobond.
Example:
<sql:RateMethod sql:Name="MatLen">
 <sql:Conversion sql:From="1M" sql:To="Simple" />
 <sql:Conversion sql:From="3M" sql:To="Simple" />
 <sql:Conversion sql:From="1Y" sql:To="Effective" />
 <sql:Conversion sql:From="3Y" sql:To="Effective" />
</sql:RateMethod>

DisplayUnit

Specify the text that should be used as the unit.

Attribute Default Comment
Name DisplayUnit The name of the record or column in the table.
Default (none) The default unit.

EntityState

This value should be either Active or Discontinued. Users will get warnings in the application when using discontinued series.

Attribute Default Comment
Name EntityState The name of the record or column in the table.
Default Active The default state.

Conversion

Specify one or more conversion elements if the values in the table does not correspond to the values used by Macrobond.

Attribute Comment
From The value in the table.
To The state used by Macrobond.
Example:
<sql:EntityState sql:Name=”state”>
   <sql:Conversion sql:From=”ok” sql:To=”Active” />
   <sql:Conversion sql:From=”dead” sql:To=”Discontinued” />
</sql:EntityState>

Comment

In the Macrobond application, the comment can be viewed in the Time series information report for a time series.

Attribute Default Comment
Name Comment The name of the record or column in the table.
Default (none) The default comment.

BrowseTree

In the Macrobond application, the user can browse for data in a tree structure. When the user clicks on a leaf in the tree, a list of time series is presented. Here is an example from the Macrobond main database:

The BrowseTree element makes it possible to define such a tree structure for a SQL database. The element is optional.

There are three types of nodes that can be nested in order to build the tree: Static, Dynamic and Output. The first level of the tree must be either Dynamic or one or more Static elements.

The 'most nested' level is called a 'leaf' and must be an Output element.

Static

This element defines a static node in the tree. More than one Static element can be defined at each level, which is the most common case when this element is used. Each element will form a node in the tree.

Attribute Comment
Name The node name displayed in the application.
Example:
<sql:BrowseTree>
 <sql:Static sql:Name="Industrial">
  … other nested tree nodes…
 </sql:Static>
 <sql:Static sql:Name="Agriculture">
  … other nested tree nodes…
 </sql:Static>
</sql:BrowseTree>

Dynamic

This element is used when the nodes in the tree is defined by a SQL query.

Attribute Default Comment
Command (required) Specifies either a query or stored procedure.
IsStoredProcedure False Specifies that the command is a stored procedure.
Id (required) The identifier to be used for this level in the tree. This can be used as the name of the parameter in nested commands.

The result should be a table with two or three columns. There must always be two columns called IdValue and Description. The first column is the identifier value that can be used in nested commands and the second column is used as the node description that is presented to the user. A third field called IsLeaf is optional and is described below in the section Trees of varying depth.

You can refer to identifier values from levels higher up in the tree by using the SQL parameter syntax, which is typically @ followed by the parameter name.

This example shows a list of countries, and each country contains a number of categories:

<sql:BrowseTree>
 <sql:Dynamic sql:Id="CountryId" sql:Command="SELECT DISTINCT CountryCode AS IdValue, CountryDescription AS Description FROM OurSeries">
  <sql:Dynamic sql:Id="CategoryId" sql:Command="SELECT DISTINCT CategoryCode AS IdValue, CategoryDescription AS Description FROM OurSeries WHERE Country=@CountryId">
  … other nested tree nodes…
  <sql:Dynamic>
 <sql:Dynamic>
</sql:BrowseTree>

Trees of varying depth

If the Description is empty or null, then the node will not be added to the current level. Instead the children of that node will be added and in effect a level will be skipped in the visual presentation.

Another way of creating a tree of varying depth is to return a third field called IsLeaf. If this value is True, all nested levels will be skipped. The parameters associated with skipped levels will be null.

There must be no Static sub nodes when this feature is used.

In addition to the Boolean data type, null will be interpreted as False. A numerical value will be interpreted as True it is not equal to zero. If it is a string, it will be interpreted as True if it is the string “true" and False if it is “false". The string comparison is not case sensitive.

Output

This element is used as the last level in the tree. When the user selects such a node in the tree, a query is sent to the server which should return a list of time series that is presented to the user.

Attribute Default Comment
Command (required) Specifies either a query or stored procedure.
IsStoredProcedure False Specifies that the command is a stored procedure.
Layout Row Row or Column

You can refer to identifier values from levels higher up in the tree by using the SQL parameter syntax, which is typically @ followed by the parameter name. Please note that the parameter value can be null if the IsLeaf attribute of the Dynamic node is used.

The table must also contain a column called Identifier that identifies the time series.

If Layout is Row, the resulting table must contain the columns MetadataName and MetadataValue. The columns should be a string type.

If Layout is Column, the column names should match the metadata names. The columns can then either be strings or the same type as the metadata.

The metadata values are described in the section about the Metadata element.

The metadata called Description must be present. In addition to that the result may optionally contain Frequency, StartDate and Currency. The user can select to show this information as columns in the application.

Search

This element defines how and if text searching is done in the database.

Attribute Default Comment
Command (required) Specifies either a query or stored procedure.
IsStoredProcedure False Specifies that the command is a stored procedure.
ParameterName (required) The name of the parameter.
Layout Row Row or Column

The keywords entered by the user are passed as a parameter in the command.

The SQL syntax for full-text search differs a lot between database types and often it has to be configured for specific columns in the table.

The table must also contain a column called Identifier that identifies the time series.

If Layout is Row, the resulting table must contain the columns MetadataName and MetadataValue. The columns should be a string type.

If Layout is Column, the column names should match the metadata names. The columns can then either be strings or the same type as the metadata.

The metadata values are described in the section about the Metadata element.

The metadata called Description must be present. In addition to that the result may optionally contain Frequency, StartDate and Currency. The user can select to show this information as columns in the application.

Example:
<sql:Search sql:ParameterName="SearchWords" sql:Layout="Column" sql:Command="SELECT Identifier, Description, Frequency FROM SeriesTable WHERE FREETEXT(*, @SearchWords)"/>

Sample configuration

<?xml version="1.0" encoding="utf-8"?>
<sql:SqlSeriesProvider xmlns:sql="http://schemas.mbnd.eu/2011/sqlseriesprovider">
 <sql:DataProvider>
  <sql:Name>System.Data.SQL</sql:Name>
  <sql:ConnectionString>Data Source=MyServer;Initial Catalog=Series; Integrated Security=SSPI</sql:ConnectionString>
 </sql:DataProvider>
 <sql:Series>
  <sql:Values sql:Command="SELECT Identifier, SeriesDate, SeriesValue FROM SeriesValues WHERE Identifier IN ({0})"/>
  <sql:Metadata sql:Command="SELECT Identifier, MetadataName, MetadataValue FROM SeriesMetadata WHERE Identifier IN ({0})"/>
  </sql:Series>
  <sql:Metadata>
   <sql:Description sql:Name="Descr"/>
   <sql:Class sql:Default="Stock"/>
   <sql:Currency sql:Default="SEK"/>
   <sql:Frequency sql:Default="Daily"/>
   <sql:DayMask sql:Default="Monday Tuesday Wednesday Thursday Friday"/>
  </sql:Metadata>
  <sql:BrowseTree>
   <sql:Static sql:Name="Countries">
    <sql:Dynamic sql:Id="Country" sql:Command="SELECT DISTINCT MetadataValue, MetadataValue FROM SeriesMetadata WHERE MetadataName='Country'">
   <sql:Output sql:Command="SELECT Identifier, MetadataName,MetadataValue FROM SeriesMetadata
     WHERE Identifier IN (
      SELECT Identifier FROM SeriesMetadata WHERE
      MetadataName='Country' AND MetadataValue=@Country)"/>
   </sql:Dynamic>
  </sql:Static>
 </sql:BrowseTree>
</sql:SqlSeriesProvider>

More examples

SQL In-house – examples provides a reference implementation.

Troubleshooting

You can enable logging for the SQL Database Connector in the Macrobond application on the Configuration > Settings dialog (for MB pre-1.28: Edit > Settings):
See here pre-1.27 version view

 

This will cause a log window to be displayed as soon as there is any communication with the SQL server. The log contains information about what queries that are sent, what the response is and some diagnostic messages that can be helpful for troubleshooting problems with the configuration file.

The .xml configuration file defining SQL In-house is loaded during each startup of the Macrobond application. Starting from Macrobond 1.26 the configuration file can be also re-loaded by clicking the refresh button in the time series tree for given data source.