Accessing Snowflake data warehouse

Access the Snowflake data warehouse, and connect to IBM® Cognos® Analytics, Tableau, Microsoft Excel, and Power® BI for analysis and reporting.

Before you begin

  1. Check that you have administrator-level privileges to install drivers and softwares.
  2. Open a command terminal and use one of the following commands to generate a private key. Store the private key in a secure location.
    • For unencrypted private key, use following command.
      openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
    • For encrypted private key, use following command.
      openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
    Note: The private key is stored in file rsa_key.p8, but you can specify a different file name with .p8 extension.
  3. Use the following command to generate the public key by using the private key. Replace rsa_key.p8 with the appropriate private key file.
    openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
  4. Share the public key with the IBM Maximo® Renewables team.
  5. You receive a region-specific Snowflake URL and credentials from the Maximo Renewables team. For example, https://<region>.snowflakecomputing.com/
  6. Download and install the Snowflake ODBC driver. For more information, see https://docs.snowflake.com/en/developer-guide/odbc/odbc-download.
  7. Configure the Snowflake ODBC driver to implement key pair authentication. In the Snowflake Configuration dialog, enter the following details.
    1. In the Data Source field, enter a name to identify the data source.
    2. In the User field, enter your login ID.
    3. Leave the Password field empty.
    4. In the Server field, enter the region-specific URL for Snowflake, for example <region>.snowflakecomputing.com. Replace <region> with the appropriate detail as provided by the Maximo Renewables team.
    5. In the Database field, enter the database name
    6. From the Schema list, select the schema.
    7. From the Warehouse list, select the warehouse.
    8. From the Role list, select the role.
    9. In the Authenticator field, enter SNOWFLAKE_JWT.
    10. Click OK. For more information on configuration, see https://docs.snowflake.com/en/developer-guide/odbc/odbc

Procedure

  1. Log in to Snowflake and confirm your default role (CLIENT). Switch roles if needed.
  2. From the sidebar, click Data > Databases to view the list of database tables accessible to you. Accesses to the following databases are provided by default.
    Operational Data Source (ODS)
    This database contains raw data as received from the source with minimal transformations.
    Data Mart (DM)
    This database contains aggregated (hourly, daily, monthly, and yearly) key performance indicators and metrics for reporting purpose.
  3. From the schema list, select Public.
  4. To query data using SQL, From the Worksheets menu, click Add, and select SQL Worksheet.
  5. Use ODBC to connect external applications with the snowflake datastore.
    1. In Microsoft Excel, go to Data > Get Data > From Other Sources > From ODBC.
    2. In Power BI, go to Home > Get Data > More .... In the Get Data dialog box, select ODBC and click Connect
    3. From the Data Source Name (DSN) list, select the Snowflake DSN created at installation of Snowflake ODBC.
    4. In the Advanced options section, enter PRIV_KEY_FILE = <file path>; in the Connection string field. Replace <file path> with the appropriate file path to private key
    5. If the private key is encrypted, then append PRIV_KEY_FILE_PWD = <password>;. Replace <password> with the appropriate password that is used for generating the key pairs.
    6. In the From ODBC dialog, click OK.
    7. In the User name field, enter the login ID, and click Connect.
      Keep the Password field empty.
    8. In the Navigator window, select the database tables.
    9. Click Load to import data or click Transform Data to refine data in the Power Query Editor window.