↑

Steps overview to set up FactSet:

  1. Accept the invitation from FactSet. invitation
  2. Whitelist your IP address in the FactSet Control Center. Whitelist IP
  3. Install Microsoft ODBC SQL driver.
  4. Install Azure Data Studio to connect to the SQL database.
    1. Alternatively, could use Python Programmatic Access. Need to install pyodbc to manage the connection.

Install Microsoft ODBC

Installation of MS-SQL driver.

  1. To install Microsoft ODBC driver 17 for SQL Server on macOS, run the following commands: 
    /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)" 
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release 
brew update 
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y 
brew install msodbcsql17 mssql-tools

    You may encounter “Error: Your Command Line Tools are too outdated.”, just follow the instruction showed on the screen and update your command line tools.

  2. Resource file loading
    The driver needs to load the resource file in order to function. This file is called msodbcsqlr17.rll. The default resource file path on macOS is /usr/local/share/msodbcsql17/resources/en_US/.

  3. Troubleshooting
    Some users encounter an issue when trying to connect after installing the ODBC driver and receive an error like: "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)". It may be the case that unixODBC is not configured correctly to find registered drivers. In these cases, creating a couple symbolic links can resolve the issue.

    sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

For ODBC 18 (latest version in 2024): https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

Components Description
libmsodbcsql.18.dylib or libmsodbcsql.17.dylib The dynamic library (dylib) file that contains all of the driver’s functionality. This file is installed in /usr/local/lib/.
msodbcsqlr18.rllor msodbcsqlr17.rll The accompanying resource file for the driver library. This file is installed in [driver .dylib directory]../share/msodbcsql18/resources/en_US/ for Driver 18, [driver .dylib directory]../share/msodbcsql17/resources/en_US/ for Driver 17.

Connect Using Azure Data Studio

Azure Data Studio homepage: https://learn.microsoft.com/en-us/azure-data-studio/

Server info

  • Server name: ofprod-norduni-1214693-sql.database.windows.net
  • Authentication type: Microsoft Extra ID - Universal with MFA support
  • Account: menghan.yuan@nord.no
  • Database name: fds

image-20210914084652582

SQL Credentials

  • Username: menghan.yuan@factset.net
  • Application Id: 175bb48c-3918-4882-864d-e0005a3fb1b9
  • Application Secret: s*adq9nuPyIlO6?6
  • Tenant Id: 19c2ed02-03ce-49b9-ab35-7e07b729dc04

The connection window looks like this:

Azure Connection

Q: What are differencec between .sql and .ipynb?

A: .sql can run one single query. Notebooks (.ipynb) can put multiple queries together.

Azure Jupyter Notebook shortcuts

Command Keybinding
Run Current Cell F5
Add Code Cell end of file ctrl + shift + C
Add Text Cell end of file ctrl + shift + T

Python Connection

SQL Query Examples

SELECT TOP 100 * from fp_v2.fp_basic_dividends;
SELECT TOP 100 * FROM fp_v2.fp_basic_shares_current;
SELECT TOP 100 * FROM fp_v2.fp_basic_splits;
SELECT TOP 100 * from fp_v2.fp_sec_coverage;
SELECT top 10 * from fp_v2.fp_sec_entity;
SELECT top 10 * from tv_v2.tv_esg_ranks;
SELECT top 10 * from fp_v2.fp_basic_prices;

Download Sample Python Codes Here

Troubleshooting

Connection error - multiple matching tokens detected

Error message

Failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryInteractive). Error code 0xmultiple_matching_tokens_detected The cache contains multiple tokens satisfying the requirements. Try to clear token cache.

Fix: Clear the Azure Token Cache using the command in the Command Palette. Specifically.

  1. Click F1
  2. type something like clear
  3. Run the Clear Azure Token Cache entry.

Azure clear cache

FactSet Support Contact at Nord: