Last Updated On: March 19, 2025
This article helps admins connect Power BI with Customer Communities Data Lake.
Overview
Analyzing data in the Gainsight Customer Communities (CC) data lake provides valuable insights into user behavior, engagement, and overall community performance.
To achieve this, admins must connect Power BI to the CC data lake using the ODBC driver. This requires Amazon Web Services (AWS) access credentials (Access Key ID and Secret Access Key), which can be obtained from the Control.
IMPORTANT:
- If any of the four Data Connectors is not used for 60 days consecutively, they are automatically uninstalled by Gainsight.
- Data for newly installed connectors may take up to 48 hours to be fully available.
Generate AWS Credentials
Gainsight CC provides a one-click functionality to simplify and speed up the process of creating the Power BI connection.
To generate AWS credentials:
- Login to Control.
- Navigate to Integrations > Apps > Power BI Connector.
- Under the Installation tab, click Install.
- Copy the Access Key ID and the Secrect Access Key.
IMPORTANT : Ensure to copy and store the Secret Access key securely. The Secret Access key is displayed only one time. Once you navigate away from the page the key will not be shown again.
Install and Configure the ODBC Driver
- This connector requires a driver to talk to the database. You might already have the required driver installed on your computer. If not please install install ODBC driver
- From the Windows Start menu, right-click ODBC Data Sources (64 bit), and then choose More, Run as administrator.
- In the ODBC Data Source Administrator, choose the Drivers tab.
- In the Name column, verify that Amazon Athena ODBC (x64) is present.
- Do one of the following:
- To configure the driver for all users on the computer, choose the System DSN tab. Because applications that use a different account to load data might not be able to detect user DSNs from another account, we recommend the system DSN configuration option.
Note: Using the System DSN option requires administrative privileges.
-
To configure the driver for your user account only, choose the User DSN tab.
- Choose Add. The Create New Data Source dialog box opens
- Choose Amazon Athena ODBC (x64), and then choose Finish.
- In the Amazon Athena ODBC Configuration dialog box, enter the following information. For detailed information about these options, see Main ODBC 2.x connection parameters.
- Data source name: add your source name
- Description: add your description
- Region, for ex: us-west-1 or eu-west-1
- Catalog: AwsDataCatalog
- Database: {CommunityId}
- Workgroup: {CommunityId}/
- Output location: s3://production-bi-connector-query-results-{AWSRegion}/{CommunityId}/
- Encryption options: SSE_S3
- To specify configuration options for IAM authentication, choose Authentication Options and enter the following information:
- For Authentication Type, choose IAM Credentials.
- For Username, enter a user name: Access Key ID that you got when Installing BI connector
- For Password, enter a password: The Secret Access Key that you got when Installing BI connector
- For Session Token [keep empty]
- Click OK
- At the bottom of the Amazon Athena ODBC Configuration dialog box, choose Test. If the client computer connects successfully to Amazon Athena, the Connection test box reports Connection successful. If not, the box reports Connection failed with corresponding error information
- Choose OK to close the connection test. The data source that you created now appears in the list of data source names
Power BI App
- Go and log into your Power BI
- In the Home tab, select Get Data
- In the search box, enter Athena
- Select Amazon Athena, and then select Connect
- On the Amazon Athena connection page, enter the following information:
- For DSN, enter the name of the ODBC DSN that you want to use. For instructions on configuring your DSN, go to the ODBC driver documentation. See above
- For Data Connectivity mode, choose a mode that's appropriate for your use case, following these general guidelines:
For smaller datasets, choose Import. When using import mode, Power BI works with Athena to import the contents of the entire dataset for use in your visualizations.
For larger datasets, choose DirectQuery. In DirectQuery mode, no data is downloaded to your workstation. While you create or interact with a visualization, Microsoft Power BI works with Athena to dynamically query the underlying data source so that you're always viewing current data. More information: Use DirectQuery in Power BI Desktop
- Select OK.
- At the prompt to configure data source authentication, sign in into your MS account and select USE DATA SOURCE CONFIGURATION and select Connect
- Your data catalog, databases, and tables appear in the Navigator dialog box.
Additional resources:
https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc-and-power-bi.html
Errors handling
If you get the following error when trying to connect CC Data Lake as a source
please check Data source settings in Options and settings in Power BI
In Data source settings search for the DSN you configured in ODBC, click Edit permissions and make sure you are signed in into your MS Office account and as a type of Credentials you use: Use Data Source Configuration.
