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 MySQL 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 on the computer where Macrobond is running. There are many possible options and settings not used in this example, so please carefully read the documentation at https://help.macrobond.com/technical-information/the-macrobond-sql-database-connector/

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

Metadata organisation

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 Edit menu > Settings > My series: 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
    • User name and password to the SQL database
      sqlih-settings
  • Go to the Analytics activity and create a new document
  • In the Series list type: ih:col:plcpi
    sqlih-serieslist
  • 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
    sqlih-textsearch
  • In the Series browser of the Analytics activity select the newly added sql in-house database and open one of the listed tree nodes:
    sqlih-tree

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 Edit menu > 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 Edit menu > Settings > My series: 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
    • Login User name 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:
    sqlih-browse-leaf