MicroStrategy ONE

Connecting to data with schema that you define

In addition to data in CSV, JSON, and Excel format, the Data Connector SDK also supports connectors to data with a schema you define. When you use a custom schema, MicroStrategy does not detect column types, but instead displays the data using the schema you define for the connector. This allows you to configure your connector to send only a subset of the data. For example, if you have tabular data with hundreds of columns, you can focus on the few columns that users are really interested in.

The steps below demonstrate how to connect to data with a schema that you define. The connector in this example is called "omdb" and assumes that you are saving your connector files in a folder called "omdb".

Create the HTML file that specifies the Javascript file and defines the page the user interacts with

In the omdb folder, create a connector HTML file named omdb.html. Copy and paste the code below into the file. You should also put the MicroStrategy SDK library (mstrgdc-2.0.js file) in the same folder.

Copy 
<html>
  <head>
    <title>Search Movies</title>
    <meta http-equiv="Cache-Control" content="no-store" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js" type="text/javascript"></script>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS" crossorigin="anonymous"></script> -
    <script src="./mstrgdc-2.0.js" type="text/javascript"></script>
    <script src="omdb.js" type="text/javascript"></script>
    <link rel="shortcut icon" href="">
  </head>
  <body>
    <div class="container container-table">
      <div class="row vertical-center-row">
        <div class="text-center col-md-4 col-md-offset-4">
          <h2>Select Movie</h2>
          <label>Enter name of movie here</label>
          <input id="Search"/>
          <br>
          <button type = "button" id = "submitButton" class = "btn btn-success" style = "margin: 10px;">Submit</button>
        </div>
      </div>
    </div>
  </body>
</html>

Create the Javascript file that defines the connector

In the omdb folder, create a a JavaScript file named omdb.js. Copy and paste the code below into the file. This code creates the connector object, downloads the data, creates the event listener, and validates the data.

Copy 
function parseResponse(response,tableData){
  resp = response.Search;
  for (i = 0, len = resp.length; i < len; i++) {
    tableData.push({
      "Title": resp[i].Title,
      "Year":parseInt(resp[i].Year),
      "imdbID":resp[i].imdbID,
      "Type":resp[i].Type,
      "Poster":resp[i].Poster
    });
  }
}
// Create the connector object
var myConnector = mstr.createDataConnector();
var setTableSchema = function(tableSchema){
            
  var cols = [{
    name: "Title",
    dataType: mstr.dataTypeEnum.string
  }, {
    name: "Year",
    dataType: mstr.dataTypeEnum.int
  }, {
    name: "imdbID",
    dataType: mstr.dataTypeEnum.string
  }, {
    name: "Type",
    dataType: mstr.dataTypeEnum.string
  }, {
    name: "Poster",
    dataType: mstr.dataTypeEnum.string
  }];
    tableSchema.column = cols;
}
            
  
// Download the data
myConnector.fetchTable = function(table, params, doneCallback) {
  // mstrObj represents all useful information that the connector saved before submit
  var mstrObj = JSON.parse(params);
  var dataObj = mstrObj.connectionData;
  var tableData=[];
 // The value of apikey (shown as nnnnnnnn) is a free API key you get from omdb
  var baseUrl = "http://www.omdbapi.com/?apikey=nnnnnnnn&s="+dataObj.value;
  setTableSchema(table.tableSchema);
  $.ajax(baseUrl).success(function(response) {
    parseResponse(response,tableData);         
    var total = parseInt(response.totalResults);
    var pages = Math.ceil(total/10);
    var ajaxArray = []
    for (i=2;i<=pages;i++){
      url = baseUrl + "&page="+i;
      var a=$.ajax(url).done(
        function(response){
          parseResponse(response,tableData)
        }
      )
      ajaxArray.push(a);
    }
    $.when.apply(null,ajaxArray).done(function(){
      table.appendFormattedData(tableData);
      doneCallback(table);
    });
  });
};
// validateDataConnector will do validation check of the connector        
mstr.validateDataConnector(myConnector);
// Create event listeners for when the user submits the form
$(document).ready(function() {
  $("#submitButton").click(function() {
    var content = $("#Search").val();
    var dataObj = {
      value:content
    };
    mstr.connectionData = dataObj;
    // This will be the data source name in mstr
    mstr.connectionName = "omdb";         
    mstr.fileType = 'FORMAT_JSON';
    var params = {};
    mstr.connectionData = mstr.connectionData;
    mstr.authenticationInfo = "";
    mstr.authType = mstr.authTypeEnum.anonymous;
    mstr.tableList = [];
    mstr.tableList.push({tableName: "omdb"});
    console.log(JSON.stringify(params));
    window.mstr.submit();
  });
});

The structure of the Javascript code is similar to the code in the JSON and Excel example, with the following important differences:

  • table.tableSchema is required. tableSchema.column is an array of JSON objects, which represents the table schema. Each JSON object contains at least two properties: name and dataType. Supported data types include String and Double. Another optional property is dateFormat, which is used primarily when dataType is DATE, DATETIME, or TIME.
  • The value of the apikey parameter in the baseUrl is a free API key you get from the OMDb website. You just provide your email and they send you a free API key
  • To append data to the table, you use appendFormattedData, instead of AppendRawData.
  • tableData is an array of JSON objects. The structure of the JSON objects in tableData must be the same as the structure of the JSON objects in tableSchema.
  • fileType must be FORMAT_JSON.