Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Cognos Analytics 11.1.6 connection to Google BigQuery

    Posted Mon June 22, 2020 04:57 PM
    We are transitioning to the use of Google BigQuery for our DW and recently tried to connect our on-premise Cognos Enterprise Platform via our internally connect access point ( Equinox server) to GCP and Big Query.  Looking for guidance as we are experiencing an error:

     

    Using the steps in this article https://pmsquare.com/analytics-blog/2019/1/7/google-bigquery-in-cognos-analytics we receive the following error on the first connection test "XQE-GEN-0002 An unexpected exception occurred:

    JVMCFRE113 unexpected EOF; class=com/simba/googlebigquery/googlebigquery/utils/BQCoreUtils, offset=6151​​" and the following error on all subsequent test connection attempts "XQE-GEN-0002 An unexpected exception occurred:

    com/simba/googlebigquery/googlebigquery/utils/BQCoreUtils"

     



    ------------------------------
    Kay Vandevanter
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Cognos Analytics 11.1.6 connection to Google BigQuery

    Posted Tue June 23, 2020 10:47 AM
    Hi Kay, 
    I followed the instructions with the drivers (SimbaJDBCDriverforGoogleBigQuery42_1.2.4.1007) from Google and was able to connect. What Cognos version are you using? We will also be using different .json files so I'll need to assume yours is valid.
    Thanks,
    Trevor

    ------------------------------
    TREVOR COMEAU
    ------------------------------

    Original Message


  • 3.  RE: Cognos Analytics 11.1.6 connection to Google BigQuery

    Posted Tue June 23, 2020 05:00 PM
    Thanks Trevor.

    I work with Kay so thought I would chime in.

    We do have the same drivers installed and are failing connection tests in Cognos.

    This particular environment has two report servers. On each server I have successfully connected to our target source using the DBeaver client so we don't believe there is a driver issue or an issue with the .json file.

    I did try to connect today with a manual credential which we knew was wrong but wanted to confirm that we would get a refusal from GCP as opposed to an inability to connect. I'll paste the error below but I believe this should confirm that we had a refused credential - which was the goal in this case.

    XQE-DS-0006 Unable to logon to the data source.

    [Simba][JDBC](10100) Connection Refused: [Simba][JDBC](11640) Required Connection Key(s): ProjectId; [Simba][JDBC](11480) Optional Connection Key(s): AdditionalProjects, AllowLargeResults, DefaultDataset, EnableHighThroughPutAPI, FetchThreadsPerResultSet, ForceHTAPI, HighThroughPutMinTableSize, KMSKeyName, Language, LargeResultDataset, LargeResultsDatasetExpirationTime, LargeResultTable, MaxResults, OAuthAccessToken, OAuthClientId, OAuthClientSecret, OAuthPvtKeyPath, OAuthRefreshToken, OAuthServiceAcctEmail, OAuthType, ProxyHost, RequestGoogleDriveScope, StringColumnLength, Timeout, TimestampFallback, useQueryCache



    ------------------------------
    Pat Birkeland
    ------------------------------

    Original Message


  • 4.  RE: Cognos Analytics 11.1.6 connection to Google BigQuery

    Posted Tue June 23, 2020 05:00 PM
    Hello Trevor.

    I actually work with Kay so thought I would pile on here.

    We do have the same drivers installed on the two report servers that are used in this environment. We can actually use DBeaver with the .json file and same driver to connect to GCP on each of these servers.

    This morning I actually ran a test that I knew would fail this morning where I omitted the .json file and manually added a credential that I knew would fail. The error message seemed to indicate a bad credential as opposed to an inability to make a connection to GCP. I'll post the error below and see if you have an opinion based on the error message.

    Thanks again for your help!

    Pat

    XQE-DS-0006 Unable to logon to the data source.

    [Simba][JDBC](10100) Connection Refused: [Simba][JDBC](11640) Required Connection Key(s): ProjectId; [Simba][JDBC](11480) Optional Connection Key(s): AdditionalProjects, AllowLargeResults, DefaultDataset, EnableHighThroughPutAPI, FetchThreadsPerResultSet, ForceHTAPI, HighThroughPutMinTableSize, KMSKeyName, Language, LargeResultDataset, LargeResultsDatasetExpirationTime, LargeResultTable, MaxResults, OAuthAccessToken, OAuthClientId, OAuthClientSecret, OAuthPvtKeyPath, OAuthRefreshToken, OAuthServiceAcctEmail, OAuthType, ProxyHost, RequestGoogleDriveScope, StringColumnLength, Timeout, TimestampFallback, useQueryCache



    ------------------------------
    Pat Birkeland
    ------------------------------

    Original Message


  • 5.  RE: Cognos Analytics 11.1.6 connection to Google BigQuery

    Posted Thu June 25, 2020 07:54 AM

    I realize now that the version is 11.1.6 as per the title. The is what I have also used.

    The only things I can think of that could be causing the failure are:
    1.) Conflicting driver files in the /drivers directory (try having only the Content Store driver and GBQ drivers there)
    2.)  An incorrect JDBC connection string. 

    Is your JDBC connection string in this format? 

    jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=aaaaaa-000000;OAuthType=0;OAuthPvtKeyPath=C:\aaaaaa-000000-000a00a0a0a.json;OAuthServiceAcctEmail=aaaaaa@aaaaaa-000000.iam.gserviceaccount.com;Timeout=7200;allowLargeResults=1

    I am assuming you can run a query in DBeaver and return data.



    ------------------------------
    TREVOR COMEAU
    ------------------------------

    Original Message