SQL In-house – examples

Sample configurations for the Macrobond SQL database provider

The following sample solutions should be treated as an example or starting point rather than a complete solution. The presented configuration might not be efficient for a large number of series or for series having very long history. If you already have your own time series database, you can use these examples as inspiration when creating configuration files with queries for your database.

In this example MS SQL is assumed as a database server, but with minimal adjustments, the sample can be used with other database servers. A suitable .NET Data Provider has to be installed for others. There are many possible options and settings not used in this example, so please carefully read the documentation at The Macrobond SQL Database Connector.

The sample configuration files contain further comments that might be of interest.

Metadata organization

Metadata describing individual time series can be organized and reported to Macrobond in two different ways:

  • With a single row of the SQL table describing the time series. Attributes are stored in multiple columns:
    sqlih-data_in_columns
  • With multiple rows of the SQL table describing different attributes of the same time series:
    sqlih-data_in_rows

Metadata organized in multiple columns

To use that sample and test that everything works, follow these steps:

  • Create the test database and tables and fill them with the sample data by using the metaInColumns.sql script
  • Save the metaInColumns.xml configuration file on your local disk, edit it and modify the address of the server and the name of the database
  • In the Macrobond application go to the Configuration  > Settings > My series (for MB pre-1.28: Edit > Settings > My series) then Add > Sql database and provide:
    • The name that will be displayed within Macrobond for this database
    • The prefix that will be used to access the series – col in this case
    • The full path to the xml file saved in the earlier step
    • Username and password to the SQL database See here pre-1.27 version view

 

  • Go to the Analytics activity tab and create a new document
  • In the Series list type: ih:col:plcpi
  • In the Series browser of the Analytics activity select the newly added SQL in-house database and try running a text search for the phrase cpi
  • In the Series browser of the Analytics activity select the newly added SQL in-house database and open one of the listed tree nodes:

For troubleshooting it can be helpful to enable the SQL Log Console in the Macrobond application. You will then see the SQL queries executed and results returned. It can be enabled via the Configuration > Settings > Advanced (for MB pre-1.28: Edit > Settings > Advanced).

Metadata organized in multiple rows

To use the sample and test that everything works, please follow the steps described above but use:

  • metaInRows.sql to create and populate the SQL tables
  • metaInRows.xml as the Macrobond in-house definition file
  • row as the database prefix and name, ih:row:plcpi as the series code to be entered in the Series list

Dynamic tree of varying depth

To construct a dynamic series browser:

  • Create the test database and tables, fill them with the sample data by using the metaInColumns-isLeaf.sql script
  • Save the metaInColumns-isLeaf.xml configuration file on your local disk, edit it and modify the address of the server’s address and the name of the database’s name
  • In the Macrobond application go to the edit Configuration > Settings > My series (for MB pre-1.28: Edit > Settings > My series) then Add > Sql database and provide:
    • The name that will be displayed within Macrobond for this database - leaf
    • The prefix that will be used to access the series – leaf in this case
    • The full path to the xml file saved in the earlier step
    • Log in username and password to the SQL database
  • Go to the Analytics activity and create a new document
  • In the Series browser select the leaf database and expand all of the nodes:

Single identifiers with special characters

In this example you can use single identifiers that do not match the default ParameterName limitations (a-z, 0-9 and underscore) specifically being able to use spaces and upper-case letters. Please follow the steps described above but use:

Tags: sql