Data sources
  • 27 Aug 2024
  • 15 Minutes to read
  • Contributors
  • Dark
    Light

Data sources

  • Dark
    Light

Article summary

This topic describes the Data sources section, the information it provides, and its features.

Data source connections allow you to retrieve data from various Data sources through Data reader protocols in order to populate the desired Cubes of a Data model.

In the Data source section, you can create new Data source connections or edit already existing ones.

All Data source connections created within a Platform are stored here.

To access the Data sources section, access the System Administration space of the desired Platform and click on the Data sources tile to open the Data sources page.

contents/assets/images/system.admin.data.sources.png

In the Data sources page, you can see all existing Data sources and their main information: the table is sortable and searchable using the interactive header fields. You can also show or hide columns to your liking, by clicking the Column chooser button in the upper right corner of the table.

The table contains the following information:

  • Name. This column displays the name of each Data source connection

  • Type. This column displays the type of each Data source connection

  • Connection details. This column displays specific details about the connection of each Data source

Creating a Data source connection

The type of Data source connections that you can create are the following:

Creating a SQL connection

You can create two types of SQL connections:

  1. OLE-DB

  2. ODBC

The configuration options vary depending on the type of connection you choose.

SQL Data source with OLE-DB connection

To configure an OLE-DB connection to an SAP server, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "SQL" from the "Type" dropdown list. The "OLE-DB" option below should be enabled by default, if not, click on the toggle to enable it

  3. Configure the connection string by clicking on the "Connection string" field or on the "CONFIGURE" button at the end of the row. A popup window for configuring the connection string will then appear. The connection parameters depend on the provider you want to connect to. Click the "SAVE" button when you're done.

    Once you have finished to configure the connection string, you can click on the "TEST" button at the bottom to test the configured connection.

  4. Configure the SQL dialect for queries. This can be changed to match whichever database type is in use: if the database you are connecting to is not listed, set the type to "General".
    The following properties are available:

    1. Query timeout: set of waiting seconds. When you're using an ODBC database, such as Microsoft SQL Server, there may be delays because of network traffic or heavy use of the ODBC server. Rather than waiting indefinitely, you can specify how long to wait before the database engine produces an error.

      If you set a low query timeout value, some queries on the ODBC data sources may always time out: be sure to check the queries you need and set the query timeout value accordingly.

    2. Disable record counting:  Enable/disable counting of affected rows in a query. If disabled, the data reading process completion percentage is not available during the protocol execution

    3. Disable table sampling: Enable/disable sampling on the database

  5. Enter the credentials in the respective "Username" and "Password" fields. The credentials are required and are defined on the SQL server that you want to connect to

  6. Click on "CREATE" to save the Data source configuration.

contents/assets/images/SQL.create.png

SQL Data source with ODBC connection

To configure an ODBC connection to an SAP server, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Choose "SQL" from the "Type" dropdown list and enable the "ODBC" option by clicking on the toggle below

  3. Choose the preferred ODBC SQL server from the "ODBC" dropdown list. The name of the Data source connection will be the same as the chosen ODBC SQL server name.
    The ODBC data sources that are displayed in the dropdown list are those configured in the environment where Board is installed. To add new ODBC data sources, proceed as follows:

    • For Board Cloud installations:

      1. Access the Admin Tool (also known as Administration portal) of the desired Platform

      2. Click on the "DATA PIPELINE" button in the menu on the left to access the Data Pipeline section

      3. Click on the "CLOUD DATA PIPELINE PORTAL" button to access the Data Pipeline portal and then log in. You can use the credentials displayed in the Data Pipeline section of the Admin Tool if you are logging in for the first time
         contents/assets/images/assets/images/Data_Pipeline_ADMIN_PORTAL.png

      4. Click on the "+New Data Source" button in the upper right corner of the Data Pipeline portal page to add a new data source
         contents/assets/images/assets/images/data_pipeline_PORTAL.png

      5. Click on the desired data source and configure its options. If your data source is located on a local machine (on-premises), for example a MySQL database, then you will need to install the On-Premise Connector (OPC) on that machine or another one in the same network and connect it with the Cloud Data Pipeline Portal of your Platform. See this page for more information
         contents/assets/images/assets/images/Data_Connectors.png

      6. After configuring the data source connection, return to the Data Pipeline section of the Admin Tool and click on the "ADD NEW" button at the bottom of the page. A pop-up window will appear
         contents/assets/images/assets/images/Data_Pipeline_ADMIN_PORTAL_add_connection.png

      7. Enter an alias in the "Connection name" field. This is the name that will appear in the "ODBC" drop-down list in the Data sources section of Board

      8. Enter the name of the data source in the "Data source name" field. The data source is the one you configured in the Cloud Data Pipeline Portal
         contents/assets/images/assets/images/ADD_NEW_ALIAS_DATA_SOURCE.png

      9. Click on "SAVE"

    • For on-premises Board installations:

      1. Open the ODBC Data Source Administrator (64bit version) in the machine where Board is installed by going in the Control Panel, clicking on Administrative Tools (inside the "System and Security" category), and finally clicking on "ODBC Data Sources". See this page for more information

      2. Go to the "System DNS" tab

      3. Click on "Add" to add and configure your desired ODBC data source

      4. When done, click "OK"

  4. Configure the SQL dialect for queries. This can be changed to match whichever database type is in use: if the database you are connecting to is not listed, set the type to "General". The following properties are available:

    1. Query timeout. Set a wait time in seconds. When you're using an ODBC database, such as Microsoft SQL Server, there may be delays because of network traffic or heavy use of the ODBC server. Rather than waiting indefinitely, you can specify how long to wait before the database engine produces an error

      If you set a low query timeout value, some queries on the ODBC data sources may always time out: be sure to check the queries you need and set the query timeout value accordingly.

    2. Disable record counting. Enable/disable counting of affected rows in a query. If disabled, the data reading process completion percentage is not available during the protocol execution

    3. Disable table sampling. Enable/disable sampling on the Database

  5. Enter the credentials in the respective "Username" and "Password" fields. The credentials are required and are defined on the SQL server that you want to connect to

  6. Click on "CREATE" to save the Data source configuration.

contents/assets/images/sql.odbc.png

Creating an SAP connection

To configure a Data source connection to an SAP server, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "SAP" from the "Type" dropdown list

  3. Enter the address of the SAP connector Server in the "Url" field

  4. Click on "CREATE" to save the Data source configuration.

contents/assets/images/sap.create.data.source.png

Mutual TLS (mTLS) authentication method for SAP data sources

The configuration of SAP data sources supports the mutual TLS (mTLS) authentication method. This method verifies the identity of both Board and SAP in a connection between those two environments using the dedicated Board Connector for SAP.

The adoption of the mTLS authentication ensures that traffic is secure and trusted in both directions between Board and SAP: it prevents various kinds of cyberthreats, such as Brute force attacks, Spoofing attacks, Phishing attacks, and many more.

The mutual TLS authentication method relies on 3 aspects:

  1. Public key and private key. Those keys are used from both parties to encrypt and decrypt messages: anything encrypted with the public key can be decrypted only with the private key and vice versa

  2. TLS certificate. The TLS certificate is a file that holds required important information for verifying the identity of both parties, information on who issued the certificate, and the certificate’s expiration date

  3. TLS handshake. The TLS handshake process verifies the TLS certificate and both communicating parties exchange messages to acknowledge each other, verify each other, establish the encryption algorithms they will use, and agree on session keys

How to enable the mutual TLS (mTLS) authentication method for SAP data sources in Board

To use mTLS for connecting Board to SAP data sources, proceed as follows:

  1. From the “Data sources” section under System Administration, click on the desired SAP connection

  2. Click on the “Enable mTLS” toggle

  3. Upload the required TLS Certificate (required). The Certificate file extension must be .p12 or .pfx

  4. Enter the Certificate password that Board will use to read the certificate during the TLS handshake process

  5. Click on “CREATE”.

contents/assets/images/data.source.mtls.png

Please note that the mutual TLS (mTLS) authentication method is available only for Cloud Platforms and it’s supported by the Board Connector for SAP version 5.6 and newer.

Connection to cloud storage services

If you want to set up a Text file Data reader and the file you wish to read is stored on a cloud storage service, you need to configure a connection to it in the Data sources section of Board.

Board supports the following cloud storage services:

Creating an Amazon S3 connection

To configure a Data source connection to an Amazon S3 cloud storage, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "Amazon" from the "Type" dropdown list

  3. Enter the Client Key in the "Client Key" field

  4. Enter the Client Secret in the "Secret" field

  5. Enter the AWS Region in the "Region" field

  6. Enter the name of the desired S3 bucket container in the "Bucket name" field

  7. Click on "TEST CONNECTION" to perform a connection test and then click on "CREATE" to save the new Data source connection.

contents/assets/images/data.source.amazon.png

Fields marked with an asterisk (*) are required

Please refer to Amazon S3 documentation for more details.

Creating an Azure Blob Storage connection

To configure a Data source connection to an Azure Blob storage, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "Azure" from the "Type" dropdown list

  3. Enter the connection string of the desired Azure Storage Account in the "Connection string" field

  4. Enter the name of the desired Azure Blob container in the "Container name" field

  5. Click on "TEST CONNECTION" to perform a connection test and then click on "CREATE" to save the new Data source connection.

contents/assets/images/data.sources.azure1.png

Fields marked with an asterisk (*) are required

Please refer to Azure Blob Storage documentation for more details.

Creating a Google Cloud Storage connection

To configure a Data source connection to a Google Cloud storage, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "Google" from the "Type" dropdown list

  3. Enter the key of the desired Google service account in the "Service account key" field

  4. Enter the name of the desired Google Cloud bucket container in the "Bucket name" field

  5. Click on "TEST CONNECTION" to perform a connection test and then click on "CREATE" to save the new Data source connection.

contents/assets/images/data.sources.google.cloud.png

Fields marked with an asterisk (*) are required
Please refer to Google Cloud storage documentation for more details.

Creating an API connection

To configure a Data source connection using an API, proceed as follows:

  1. From the Data sources section of the desired Platform, click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Give it a name and choose the “API” option in the “Type” dropdown menu

  3. Next, you need to choose one of the supported authentication methods:

    • None

    • API Key. Credentials are transferred as key/value pairs. The key is an encrypted string, for example, AIzaSZasfDRa4JsXZ-HjGw7ISLn_3namBGewQe, where the value is used by the other system to uniquely identify the key

    • Basic Authentication. Credentials are transferred as user ID/password pairs

    • OAUTH V2 (Client credentials). Credentials are transferred as Client ID/Client Secret to authenticate Board and get an access token to allow the Procedure step to call the external API. Learn more about Client Credentials flow specifications.
      When you choose an authentication method, the necessary authentication configuration fields will automatically appear below (for example “Client ID”, “Client Secret” and so on).  contents/assets/images/data.sources.api1.png
      The OAUTH V2 authentication type also offers a "TEST CONNECTION" button in the lower left corner of the configuration panel. If the connection is not successful, a notification message will appear

  4. (Optional) Enable the mTLS authentication

  5. Click CREATE to save the new Data source connection.

Mutual TLS (mTLS) authentication method for API Data sources

The configuration of API data sources supports the mutual TLS (mTLS) authentication method. This method verifies the identity of both Board and APIs. When activated, the Admin user can upload a certificate into the Data Source configuration and use the uploaded certificate for REST API calls towards the specific Data source so that the source system will validate the requests before returning the information.

The adoption of the mTLS authentication ensures that traffic is secure and trusted in both directions between Board and the API: it prevents various kinds of cyberthreats, such as Brute force attacks, Spoofing attacks, Phishing attacks and many more.

This feature is supported by API key, Basic authentication, and OAuth2.

This feature is the same offered for the SAP connection type.

This enhancement is only available for Cloud customers. 

The mutual TLS authentication method relies on 3 aspects: 

  1. Public key and private key. Those keys are used from both parties to encrypt and decrypt messages: anything encrypted with the public key can be decrypted only with the private key and vice versa

  2. TLS certificate. The TLS certificate is a file that holds required important information for verifying the identity of both parties, information on who issued the certificate and the certificate’s expiration date

  3. TLS handshake. The TLS handshake process verifies the TLS certificate and both communicating parties exchange messages to acknowledge each other, verify each other, establish the encryption algorithms they will use, and agree on session keys.

How to enable the mutual TLS (mTLS) authentication method for API Data sources in Board

To use the method for connecting Board to API data sources, proceed as follows:

  1. In the “Data sources” section under System Administration, click on the desired API connection or create a new one by clicking the blue plus icon  next to Data sources and following the instructions above

  2. Click on the “Enable mTLS” toggle once all other required fields are entered

  3. Upload the required TLS Certificate (required). The Certificate file extension must be .p12 or .pfx 

  4. Enter the Certificate password that Board will use to read the certificate during the TLS handshake process

  5. Click on “SAVE” to save or “CREATE” if creating a new Data source.

The following fields are common to all authentication methods:

  • The "API Base URL" field is useful when the external API to be called by Board using the configured Data source connection has a consistent prefix in its URL. If the "API Base URL" field is filled out, the saved URL will be automatically imported in the step configuration.

  • The "Timeout" field is useful in the event that you anticipate a Data source connection to take longer than 100 seconds, which is the default timeout parameter. You can customize the timeout time of the REST API call by editing the "100" that automatically appears in this field.

    contents/assets/images/data.source.api.timeout.png

    The timeout parameter refers to an amount in seconds.

    If you do not fill out this field, it will remain the default, which is 100 seconds.

Creating an Azure ML Connection

Once the Azure ML feature requirements are satisfied, you can create a Data source connection to the Azure ML cloud. To do that, proceed as follows:

  1. Go to the Data sources tile in the System Administration area and click on the blue plus button contents/assets/images/blue.plus.PNG next to "Data source" to open the configuration panel

  2. Enter a name for the new connection in the "Name" field and choose "Azure AutoML" from the "Type" dropdown list

  3. Enter the following Azure AutoML cloud information:

    1. Tenant ID. Enter the Tenant ID, which is a unique identifier assigned to your Azure Active Directory (ADD) tenant.

    2. Client ID. Enter the Client ID of your ADD service

    3. Client Secret. Enter the Client secret for the authorization of the entered Client ID

    4. (Optional) Subscription ID. Enter the Subscription ID, which is a unique identifier associated with your Azure subscription. This is the workspace where all the necessary Azure AutoML resources are located. If not specified, the default subscription will be chosen

    5. (Optional) Resource group. Enter the name of the Resource group, which is a sub-section of the Subscription. If not specified, the first available Resource group will be chosen

    6. (Optional) Workspace name. Enter the name of the Azure ML Workspace. If not specified, the first available Workspace will be used. The associated storage account is automatically recognized

    7. (Optional) Container name. Enter the name of the Blob storage container where the training data and experiment outputs are stored. If not specified, the first available Blob container in the Workspace will be used

    8. Cluster. Enter the name of the Azure compute cluster within the Workspace that will be used to train and develop the models

    9. Virtual machine size. Enter the Azure Virtual Machine size which specifies the hardware configuration and computational resources allocated for the inference calls to the Endpoints
       contents/assets/images/data.sources.azure ml.png

  4. Click on "TEST CONNECTION" to check if the connection works and then click on "CREATE" to save the Data source

Once the Data source is correctly configured, you can start using it in the Azure ML page.

Managing a Data source connection

To edit one or more Data source connections, select one or more and click the pencil icon or right click and select "Edit". To edit a single connection, click on the connection row to open the configuration panel and modify the desired options explained in the steps above. When editing multiple connections, the "Type" can be edited simultaneously, and once the "Type" is the same among the multiple connections, its specific settings can be configured together.

To delete one or more Data source connections, select one or more and then click on the trash icon.

To retrieve data from a Data source through a Data reader, follow the instructions on this page.


Was this article helpful?

What's Next