MicroStrategy ONE

Connect to Square Using ODBC

Starting in MicroStrategy ONE Update 12, a Square ODBC driver is used to connect to Square to import data on Web and Workstation, rather than a JDBC driver.

If you are using MicroStrategy ONE Update 11 or earlier, see Connect to Square Using JDBC.

Square is a point of sale that takes care of digital receipts, inventory, sales reports, and provides valuable analytics and feedback.

Square is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The Square ODBC driver supports standard SQL-92 syntax and functions but is limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

  • Data Retrieve Mode: ODBC Call Only
  • Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

ODBC Connection URL

MicroStrategy ships the Square ODBC driver to connect Square as a data source using Access Token. You can find it on the data source dialog when importing data.

  • Using access token

    DRIVER={MicroStrategy Square ODBC Driver};host=@hostname;port=@portnumber;Auth_Type=OAuth 2.0;Auth_AccessToken=@tokenname

See How to Get Credentials for Square for steps to acquire the correct parameters.

Square has an API limit of 10,000 requests per day.

ODBC Schema Tables

The Square API operates at System and Location levels. Organization-wide information objects and operations are surfaced under the System schema. Data for individual locations are surfaced under their own schema, according to their Location nickname (defined in the Location table). When you create a new location, a corresponding location schema containing all location level tables will be created at the same time.

Users can only see the schema and tables that they have access to according to their login credentials.

Configure Square using ODBC on Web and Workstation

  • Workstation
  • Web
  1. Add an environment connection and log in to Workstation.
  2. Click Data Source under Administration.

  3. Find and choose Square.

  4. Expand Default Database Connector and choose Add New Database Connection.

  5. Enter the required information.

  6. In the Authentication Mode drop-down, choose Access Token:
    • Access Token: Enter the required fields Name and Access Token.

  7. Click Save.
  8. In the Add Data Source dialog, choose a Project to assign and access the data source.

  9. Click Save.

In-Memory Data Import

After creating and connecting to a Square data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

  1. In the left Navigation pane, click next to Datasets.
  2. Choose a Project and select Data Import Cube.

  3. Click OK.
  4. Click on the data source to connect to Square.

  5. If needed, click Prepare Data to wrangle your data or rename columns.

  6. Click Finish.

  7. Select Import as an In-memory Dataset.

  1. Click Create > Add External Data.

  2. Find and choose Square.

  3. Choose Select Tables, Type a Query, or Build a Query to import data.

    It is recommended to use Select Tables to reduce joins on the driver side for best performance.

  4. In the Data Source interface, select DSN-less Data Source and choose Square for both Database and Version. Use one Access Token to configure the DSN-less connection:

    • Access Token: Enter the required fields Host Name, and Access Token.

    • Edit the Connection String: Click Advanced Settings and select Edit connection string. Enter the connection information of the ODBC connection string to connect.

      • Type any character in the User and Password fields, as they will not be used in the connection.

  5. Click Save.
  6. Click on the new Data Source to connect.

In-Memory Data Import

After creating and connecting to a Square data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

  1. Click your Square data source and select the tables you want to import.

  2. If needed, click Prepare Data to wrangle your data or rename columns.
  3. Click Finish.
  4. Select Import as an In-memory Dataset Mode to publish a cube.

How to Get Credentials for Square

Sign up for a Square account if you don't have an account.

  1. Log in to your Square account.
  2. Click Applications to open the Applications Dashboard.

  3. Create a Connect API application or choose an existing one.
  4. On the Credentials tab, you can find the Access Token for the connection string in MicroStrategy.

    5. Related Topics

    Square Introduction

    KB486201: Namespace and table names returned by Square ODBC are not the same as JDBC