MicroStrategy ONE

Connect to Shopify Using ODBC

Starting in MicroStrategy ONE Update 12, a Shopify ODBC driver is used to connect to Shopify 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 Shopify Using JDBC

Shopify is an e-commerce platform for online stores and retail point-of-sale, which takes care of digital receipts, inventory and sales reports, and provides valuable analytics and feedback.

Shopify is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The Shopify 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 Shopify ODBC driver to connect Shopify as a data source using the Access Token method. You can find it on the data source dialog when doing a data import.

Using Access Token method

DRIVER={MicroStrategy Shopify ODBC Driver}; HOST=[Host]; Auth_Type=OAuth 2.0; Auth_AccessToken=[Access_Token];

Shopify has a rate limit of 2 requests per second.

ODBC Schema Tables

Shopify stores data in structures which do not follow the rules of data typing and structure that apply to traditional relational tables and columns. The ODBC drivers use schema tables to map the Shopify data. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Changing Table Permissions

You can change permissions of tables through your Shopify store account. You can set these permissions while creating a new private app or change them any time after the app has been created.

  1. Click Apps > Manage private apps and select your app from the list.
  2. Apply your changes in the ADMIN API PERMISSIONS module.

Configure Shopify using JDBC 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 Shopify.

  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 and enter the required field 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 Shopify 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 Shopify.

  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 Shopify.

  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 Shopify for both Database and Version. Use one of the following methods to configure the DSN-less connection:
    • Access Token: Enter the required fields Host Name, Access Token, User, Password, and Data Source Name.

    • 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 Shopify 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 Shopify 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 Shopify

  1. Create a Shopify store.
  2. Log into your Shopify store.
  3. Click Apps > Manage private apps.
  4. Create a private app in Apps module. The credentials will be available in your private app dashboard.

    The Access Token is referred to as Password in Shopify, and can be found in Admin API module. The Storefront API is not supported by the driver and the "Storefront access token" should be ignored.