Is it possible to use multiple database connections in the same data configuration?

Is it possible to use multiple database connections in the same data configuration?

book

Article ID: KB0074236

calendar_today

Updated On:

Products Versions
Spotfire Statistica 12.5 and higher

Description

Is it possible to use two/multiple database connections in the same data configuration in Enterprise Manager?

Issue/Introduction

Is it possible to use multiple database connections in the same data configuration?

Environment

Windows

Resolution

When creating a data configuration in Enterprise Manager,only one database connection can be mapped. You will need merge data from different databases before querying them in the same data configuration.

It is recommended customer first create database connections for each different database (e.g. one for Oracle, and the other for SQL), then create data configuration to query the datasheets from each database connection, merge the datasheets into a single combined datasheet, and then create a data configuration querying that merged datasheet.

For example, merge two datasheets from two databases (e.g. Oracle, SQL) and then bring the combined datasheet into Statistica data configuration.

1. First create database connections & data configurations to query the specific datasheets from different databases

  • Create a Database Connection for Oracle database (Database connection 1)
  • Create a Data Configuration to query the specific dataset stored in Oracle database (Data Configuration 1)
  • Create a 2nd Database Connection for SQL database
  • Create a 2nd Data Configuration to query the specific dataset stored in SQL database (Data Configuration 2)

2. Merge the multiple specific datasheets into a single one through workspace

  • Open a new workspace with Beta procedures
  • from Node browser, load two "Enterprise Data Configuration" nodes, one node point to Data Configuration 1, and the other node point to Data Configuration 2 
  • from Node browser, load "Merge" data node or "Query Spreadsheet" node. There are different merge nodes in Node Browser for combining data. Which merge node to use will depend on how user want to combine the two datasheets from the two databases. User can also write SQL script in "Query Spreadsheet" node do a more complex matching and querying to merge data. 
  • Run the workspace, the merged datasheet is the output from "Merge" or "Query Spreadsheet" nodes in the workspace

3. Bring the merged datasheet from workspace to Enterprise Data Configuration

There are two methods to proceed for this step:

3.1. Use a "write spreadsheet back to database" node.

This will allow user to write the merged spreadsheet back to one of the databases (e.g. Oracle or SQL database connection), if the user has the permission to write back to the databases. Once the merged dataset is written back to the databases, then user can create a simple Enterprise data configuration to query the merged data through database connection. This would be preferred because it would not need any custom SVB code. 

3.2. Create and customize a SVB data configuration that call the workspace & deploy the output data from workspace (Note: customized SVB codes need to be developed).