KB484197: Connect MicroStrategy to a Yellowbrick database

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description

In Strategy 2020 Update 2, the Yellowbrick connector was introduced. This out-of-the-box connector allows users to easily connect to the Yellowbrick database and use it as your data warehouse.
Note: If you are using a Strategy version prior to 2020 Update 2, see this article. 

Create a DSN in Windows

• If you are running Intelligence server on a Windows platform, using Workstation for Windows, you can create a DSN to point to the Yellowbrick database. Strategy uses the PostgreSQL native driver to connect Yellowbrick. The driver is shipped along with Strategy.
  Prior to the 2019 release, users had to install version 10 or 11 of the PostgreSQL native driver themselves in the Intelligence server host or the machine running Workstation.
  
• In Windows, open the ODBC Data Source Administrator (64-bit).
• Open the System DSN tab.
• Click Add.
  

• Choose PostgreSQL Unicode(x64) and click Finish.
• Enter the Data Source, Database, Server, and Port.
  

• Data Source A name for the data source you are going to create. Choose a simple and user-friendly name.
  Database The name of the database you are connecting to.
  Server The name of the server you are connecting to.
  SSL Mode Choose require if SSL encryption is required by the server.
  
• Enter the User Name and Password and click Test to test connectivity to the target Yellowbrick instance.
  

Create a DSN in Linux

• If you are running Intelligence server on a Linux platform, you need to manually add the DSN information to the odbc.ini file that is located in the home path of the Intelligence server installation. See an example DSN in odbc.ini below.
  

  
  [ODBC Data Sources]
  WH_T38.Yellowbrick=PostgreSQL Unicode(x64)
  
  [WH_T38.Yellowbrick]
  Driver=/usr/pgsql-11/lib/psqlodbcw.so
  Description=PostgreSQL Unicode(x64)
  Database=xxxxx
  Servername=xxxxxxxxxxxxxx
  Port=5432
  Protocol=9.3
  ReadOnly=No
  RowVersioning=No
  ShowSystemTables=No
  ConnSettings=
  DriverUnicodeType=1
  

  
  
• When the DSN is ready in odbc.ini, run mstrtodbcx to test connectivity.
  

Connect to Yellowbrick from Web or Workstation Using the DSN Connection

• Choose Add Data

• > Add New Data.
  

• Search for Yellowbrick.
  

• On the Import from Table dialog, click the plus icon to add a new data source.
• Choose DSN Data Sources.
  

• In DSN, select the DSN you created.
• Enter the user name and password to log into the Yellowbrick instance.
• Enter the new data source name in Data Source Name and click Save.
• Click on the newly created data source to check connectivity.
  

• If the data source works properly—the catalog information for namespace and table are listed correctly—you can use the data source to create a dossier in Web or Workstation.
  

Connect to Yellowbrick from Web or Workstation Using a DSN-less Connection

• Choose Add Data

• > Add New Data.
  

• Search for Yellowbrick.
  

• On the Import from Table dialog, click the plus icon to add a new data source.
• Choose DSN-less Data Sources.
  

• In Database and Version, enter Yellowbrick.
• Enter the Host Name, Post Number, and Database Name.
• Enter the User, Passsword, and Data Source Name and click Save.
• Click on the newly created data source to check connectiivity.
  

• You can also directly input a DSN-less connection string to create a new data source. In Connection String, enter the ODBC or JDBC connection string to the Yellowbrick database.
  ODBC Connection String:
  

  
  DRIVER={PostgreSQL Unicode(x64)};Servername=Yellowbrick_Instance_NAME_OR_IP_ADDRESS;Port=Yellowbrick_Instance_Port;Database=dbname;DriverUnicodeType=1;UseDeclareFetch=1;Fetch=10000;

  
  JDBC Connection String
  

  
  JDBC;DRIVER={org.postgresql.Driver};URL={jdbc:postgresql:// Yellowbrick_Instance_NAME_OR_IP_ADDRESS: Yellowbrick_Instance_Port /DATABASE_NAME};

  
  

Connect to Yellowbrick from Developer

In Developer, use the DSN you previously created to create a new data instance. This process consists of three steps: creating the database instance, creating a database connection, and configuring and storing credentials. You can also create a DSN-less database instance in Developer, using a JDBC or DSN-less ODBC connection.

• Create a new database instance and select Yellowbrick as the Database connection type.
  

• Create a database connection that points to the DSN that was previously created in the Windows ODBC Admin tool or in the odbc.ini file on the Linux platform.
  

• Configure and store credentials for the login that has privileges to access the target Yellowbrick database.
  

• Configure the DSN-less connection to use the JDBC driver or ODBC DSN-less connection. Open the Advanced tab. In Additional connection string parameters, enter the JDBC or ODBC DSN-less connection string. Then configure the remaining settings as necessary.