Connecting to multiple tables

The Data Connector SDK supports the ability to import multiple tables in one connection. You use the tableList field to tell MicroStrategy how many tables should be fetched. You can set custom parameters for each table.

If you created a custom data connector with a version of the product prior to 11.0, you must upgrade your MicroStrategy product and replace mstrgdc-1.0.js with mstrgdc-2.0.js to take advantage of new features, such as table-level settings for multiple tables. In releases prior to 11.0, if multiple tables were imported in one connection, they all had to share the same settings. Simply download mstrgdc_20js.zip, extract the latest version of the mstrgdc-2.0.js Javascript file, and use it to replace the existing Javascript file in your data connector.

The previous examples, Connecting to a CSV file and Connecting to JSON and Excel files, imported only one table. The sample code looked like this:

// MUST define tableList field. Can import multiple tables in one connection.
mstr.tableList = [];
mstr.tableList.push({tableName: "RawDataCsv"});

The sample code below imports two tables, called magPlace and timeUrl. In this example, both tables are added to tableList as JSON objects, and the logic is added to get the data for each table. The sample code is followed by an explanation of important parts of the code.

// MUST define tableList field. Can import multiple tables in one connection.
mstr.tableList = [];
mstr.tableList.push({tableName: "magPlace"});
mstr.tableList.push({tableName: "timeUrl"});
if (table.tableSchema.tableName == "magPlace") {
  for (i = 0, len = feat.length; i < len; i++) {
    tableData.push({
      "id": feat[i].id,
      "mag": feat[i].properties.mag,
      "title": feat[i].properties.title,
      "lon": feat[i].geometry.coordinates[0],
      "lat": feat[i].geometry.coordinates[1]
    });
  }
}
if (table.tableSchema.tableName == "timeUrl") {
  for (i = 0, len = feat.length; i < len; i++) {
    tableData.push({
      "id": feat[i].id,
      "url": feat[i].properties.url,
      "time": new Date(feat[i].properties.time) // Convert to a date format from epoch time
    });
  }
}

• table.tableSchema.tableName will be set by MSTR before invoking fetchTable.

Setting different parameters for each table

When you connect to multiple tables, you are able to set table-level parameters for each table. For example, you can set a different file type for each table. Just as with single tables, when you import multiple tables as raw data files, the connector needs to set the fileType before redirecting to MicroStrategy. With table-level parameters, you can set a different file type for each table.

The sample code below illustrates how to use table-level parameters when you construct an mstr object that includes multiple tables. In this example, you are importing three tables - A, B, and C. The file type for Table A is CSV, while the file type for Tables B and C is JSON. In addition, Table C has a table-level parameter called filePath.

mstr.connectionData = ...;
mstr.connectionName = "RawDataFiles"; // This will be the data source name in mstrmstr.fileType = 'JSON';
mstr.tableList = [];
mstr.tableList.push({tableName: "A", fileType: “CSV”});
mstr.tableList.push({tableName: "B", fileType: “JSON”});
mstr.tableList.push({tableName: "C", filePath: “/test”}); 

After the Intelligence Server receives a request from a data connector, it generates fetch table parameters for the fetchTable function based on the mstr object. Each set of fetch table parameters corresponds to a separate table in tableList.

The final fetchTable parameters have the same values as the mstr object, excluding the tableList field. The values provided for the table-level parameters are used to overwrite the values of the mstr object in the tableList field.

The fetchTable parameters for each table:

Before you set table-level parameters, there are only tableName fields in the tableList parameter, as shown in the sample code below:

// some default settings
connectionData = ...,
connectionName = "RawDataFiles",
fileType = 'JSON',
tableList = [{tableName:"A"},{tableName:"B"},{tableName:"C"}]; 

After you set table-level parameters, the tableList parameter would look like the sample code below:

// some default settings
connectionData = ...,
connectionName = "RawDataFiles",
fileType = 'JSON',
tableList = [{tableName: "A", fileType: “CSV”},{tableName: "B", fileType: “JSON”},{tableName: "C", filePath: “/test”}]; 

The default file type for all tables is "JSON" because this is the setting for the mstr object:

mstr.fileType = 'JSON';

If you do not set a table-level fileType parameter for a table, "JSON" will be used by default.

In our example:

• Table A has a "CSV" file type so you need to set a table-level fileType parameter to "CSV":

  mstr.tableList.push({tableName: "A", fileType: “CSV”});

• Table B has a "JSON" file type. We set a table-level fileType parameter to "JSON", but it was not necessary since this is the default file type.

  mstr.tableList.push({tableName: "B", fileType: “JSON”});

• Table C has a "JSON" file type and a special parameter called filePath. We set a table-level filePath parameter to "/test". It was not necessary to set a fileType parameter since the file type for Table C is JSON and this is the default file type.

  mstr.tableList.push({tableName: "C", filePath: “/test”});