ODBC | SQL Configuration

Please make sure that when installing/upgrading Apliqo UX you also select the option to install the Apliqo Server in order to be able to connect to SQL databases.

Since version 2024.02 users have the option to store ODBC source and credentials configuration in the ContentStore. To use this functionality remember to upgrade the ContentStore with packages provided in the Upgrade process article.

Check step for the instructions.

Add new System DNS in ODBC Data Source

NOTE:

ODBC DSNs needs to be done on the server where Apliqo UX is installed.

1.1 Open ODBC Data Source Administrator

1.2 Select System DSN tab

1.3 Click Add button

1.4 Select SQL Server and click Finish

1.5 Define Data Source and click Next

  • Name: database name

  • Description: custom description, not required

  • Server: server name (can be selected from the dropdown list)

1.6 Select authentication method (windows or SQL server authentication) and click Next

1.7 Define default database (check the checkbox and select the database from the dropdown list) and click Next

1.8 If necessary change the language and log file location and click Finish 

1.9 Now you can review the configuration and Test Data Source

1.10 After successful test click OK, new Data Source will appear in the System DSN tab 


deafult.constant.js configuration

2.1 Configure source

source: configure type, odbc_src_name, tableArray and displayName

  • type: type of data source

  • odbc_src_name: name of the data source defined in ODBC (step 1 of the configuration)

  • tableArray: list the table names defined ODBC source that you want to have listed in the cube settings (can be more than one - need to be comma separated e.g. ["table1","table2"])

  • displayName: name that will be displayed in the Apliqo UX cube settings

2.2 Provide the credentials used to log into the SQL Database

  • u: user name used to log into the SQL Database

  • p: password

  • encoded: true/false

Example:

"source":[ { "type":"odbc", "odbc_src_name": 'EmployeeDB', "tableArray":["Employees"], "displayName": 'Employee Details', "u":"QXBsaXFvVVhfVXNlcg==", "p":"b3dld2R1aW0xMw==", "encoded":"true" }, ],

2.2 Repeat step 2.1 to add additional data sources

Example:

"source":[ { "type":"odbc", "odbc_src_name": 'EmployeeDB', "tableArray":["Employees"], "displayName": 'Employee Details', "u":"QXBsaXFvVVhfVXNlcg==", "p":"b3dld2R1aW0xMw==", "encoded":"true" }, { "type":"odbc", "odbc_src_name": 'BikeStores', "tableArray":["production.brands","production.categories","production.products","production.stocks","sales.customers","sales.order_items","sales.orderes","sales.staffs","sales.stores"], "displayName": 'Bike Stores', "u":"QXBsaXFvVVhfVXNlcg==", "p":"b3dld2R1aW0xMw==", "encoded":"true" }, { "type":"odbc", "odbc_src_name": 'AdventureWorks', "tableArray":["HumanResources.Department"], "displayName": 'Sample Data', "u":"QXBsaXFvVVhfVXNlcg==", "p":"b3dld2R1aW0xMw==", "encoded":"true" } ],

How to store credentials in the ContentStore

Users have the option to save their credentials in the ContentStore }APQ UX UserPreference Cube. This provides a secure method for storing and managing authentication information.

When configuring the data source, if the source information is specified in both the default.constant.js file and the }APQ UX UserPreference Cube, the configuration from the ContentStore takes precedence. This means that the settings in the default.constant.js file will be overwritten by the data stored in the ContentStore.

3.1 Set up process

Users are required to first set up the array (configuration process remains consistent with the previous step, with the exception that only information between square brackets [ ] is relevant - check the example below). Updated array should be then pasted into the ODBCSource Measure for the Admin user. This ensures that the necessary ODBC configurations are correctly set up for the specified user and data sources.

[ { "type": "odbc", "odbc_src_name": "EmployeeDB", "tableArray": ["Employees"], "displayName": "Employee Details", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" }, { "type": "odbc", "odbc_src_name": "BikeStores", "tableArray": ["production.brands", "production.categories", "production.products", "production.stocks", "sales.customers", "sales.order_items", "sales.orderes", "sales.staffs", "sales.stores"], "displayName": "Bike Stores", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" }, { "type": "odbc", "odbc_src_name": "AdventureWorks", "tableArray": ["HumanResources.Department"], "displayName": "Sample Data", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" } ]

How above configuration looks in Apliqo UX cube settings