Power BI and Reporting Analytics Environment (RAE) Connection

This step-by-step guide covers how to establish a connection between Power BI and the RAE. A video demo of establishing a connection is also available.

Work seamlessly in Power BI Desktop with the ability to use institutional data in the RAE as a datasource. This allows vetted, validated data to be used as a data source for reporting within Power BI Desktop and Service. 

Before you establish a connection, please make sure:

• You have direct access to the RAE domains you will be using as a data source. Visit ServiceNow to request direct access.
• Download the Amazon Redshift ODBC driver. This is available via Software Center.
• You are connected to the same VPN you use to query from the RAE.

1. Open Power BI Desktop. Under ‘select a data source’, choose ‘get data from other sources’.
2. Search for ODBC. Select ODBC. Click Connect.
3. If it is your first time connecting via ODBC,
   1. DSN: None (Amazon Redshift will appear here in the future)
   2. Advanced settings:
      1. Paste:
         1. Driver={Amazon Redshift (x64)}; Server=osu-edw-prd-01.cys8zs8qp7jm.us-east-2.redshift.amazonaws.com; Database=rsprd
   3. Under database, enter your credentials to the RAE. This is your IDM ID and password, available in PAM.
4. Click connect.
5. Under Navigator, search for the schema and select the table.
6. Click Load to load the data or ‘Transform Data’ to write calculations.
7. Build the report.
8. Click Publish under Home to publish to the Power BI Service.
9. Once published, click the link to access the report in the Power BI service.
10. Go to the semantic model of the report.
11. Click Refresh. If the refresh fails, you need to establish a personal gateway.
    1. Under File in the semantic model, select Settings.
    2. If you have not established a personal gateway before, you will be prompted to do so in the semantic model under ‘Gateway’ – ‘Cloud’ and ‘Install Now’.
    3. Credentials are:
       1. Basic
       2. IDM ID (available in PAM)
       3. Password (available in PAM)
       4. Privacy level: organization
    4. Go back to the semantic model and click Refresh.
    5. Check Refresh History under Refresh to view if it is successful.