Snowflake is a data warehouse, and it has gained a lot of popularity in recent years. Organizations choose to store and process their data in Snowflake which is a great data platform. Most organizations also need an analytics platform to build their forecast, perform ad-hoc analysis, and create dashboards. IBM Planning Analytics (Cloud) is an excellent choice to serve these purposes. A user receives the most benefits from these platforms when they are connected, and the connection allows Planning Analytics to pull data from tables and views in Snowflake on demand via ODBC (Open Database Connectivity). The connection is set up by following the simple steps below.
- Identifying the data which is needed in Planning Analytics
Before creating the connection, you should understand what data is needed in Planning Analytics, where the data is from (e.g. outside Snowflake), where the data is stored, and where the data is used in Planning Analytics. After you understand about the data, you can decide whether you need a new database, schema, table, and view in Snowflake. Some organizations create a new database specifically to supply the data to Planning Analytics.
- Creating a service account in Snowflake
Although a user account can be used for the connection between Snowflake and Planning Analytics, most organizations create a non-human service account for the connection. It is easier to manage the access and security that the service account requires.
- Whitelisting the IP address of Planning Analytics
Snowflake does not allow an IP address to connect to its platform unless the IP address is whitelisted. Therefore, you must know the IP address of the Planning Analytics server that you want to connect to Snowflake. One way to find the IP address is to open a case with the IBM support team and request the IP Address from IBM. Please use the below link for opening a case with IBM.
IBM Support Website: https://www.ibm.com/mysupport/
- Installing the ODBC driver
An ODBC driver is required if you want to use ODBC on the Planning Analytics platform. You must send the driver to the IBM support team to install it on the Planning Analytics server. A sample of Snowflake ODBC Driver is available below. Additionally, you have to provide the information about the connection to the IBM support team. The sample connection information can also be found below.
Example of Snowflake ODBC Driver: Downloading the ODBC Driver — Snowflake Documentation
Sample Connection Information:
- Data Source: SnowflakeConnectionName
- User: ServiceAccount
- Password: ServiceAccountPassword
- Server: SnowflakeServerName
- Database: SnowflakeDatabaseName
- Schema: SnowflakeSchemaName
- Warehouse: SnowflakeWarehouseName
- Role: ServiceAccountRole
- Planning Analytics URL: https://PAServerProd.planning-analytics.cloud.ibm.com
- Testing the connecting between Snowflake and Planning Analytics
The last step is to test the connection between Snowflake and Planning Analytics (Cloud). You can create a simple TurboIntegrator process in Planning Analytics with Watson to pull data from Snowflake. The parameters required for this testing are the connection name (data source), username (service account), password (service account), and a sample query. If you can pull the data from the sample process, this means that our new connection is working.
Setting up the connection between Snowflake and Planning Analytics is simple if you have the information which is required for the connection. By following the above steps, you can collect the information before contacting the IBM support team. If you know the IP address of Planning Analytics, a Snowflake administrator can add the IP address to the whitelist of Snowflake beforehand. If you need further assistance with the connection, please do not hesitate to contact support@quebit.com.