A few months ago we released a Preview of the Amazon Redshift connector in Power BI Desktop. This new connector allows users to easily build reports based on their Redshift data, either by importing the data into Power BI Desktop or by using DirectQuery mode. You can find more details about the Redshift connector in Power BI Desktop in this previous blog post.
Today we are very excited to announce support for Redshift-based reports in the Power BI Service, allowing users to publish their reports to unlock seamless consumption of reports & dashboards based on Redshift data. In this blog post, we will show you step-by-step how to accomplish this task.
Creating Your Report Using Power BI Desktop
The first step is to create your report using Power BI Desktop. Note that the Redshift connector is exposed as a Preview feature in Power BI Desktop and, as such, it needs to be enabled from the “Preview Features” tab within the Options dialog (File->Options and Settings –> Options).
After enabling the connector, you will be able to find it under the Database category in the “Get Data” dialog.
In order to connect to Amazon Redshift, you have to provide Server and Database information. From this dialog, you can also specify whether you would like to use Import or DirectQuery mode. More information regarding the differences between these two connectivity modes in Power BI can be found in the following article. In this case, we will use DirectQuery.
After specifying a Server and Database to connect to, you will be prompted for credentials. You need to provide a username and password in order to access your Amazon Redshift database.
Upon specifying credentials, a connection to Amazon Redshift will be established and you will be presented with the Navigator dialog where you can browse all databases and tables in your database and select one or multiple items.
Once you have selected objects from their databases, you can decide to Load or Edit data:
- If you select Edit, you will be taken into the Query Editor dialog where you can apply several different data transformations and filters on top of your Amazon Redshift data, before the data (or metadata only in the DirectQuery case) is transferred to the Data Model. Many of these operations will be performed against the underlying Amazon Redshift database, depending on whether such operations are supported by the backend or not.
- If you select Load, and had selected DirectQuery mode, metadata about the selected tables will be transferred to the Data Model and you will be able to start creating report visualizations that are linked to the underlying data source and will issue live queries as users interact with your visualizations. If using Import, after clicking Load, queries will run against your Redshift database to extract the data and load it into the local Data Model.
Afterwards, you can author report visualizations, measures and other report elements within the main Power BI Desktop window just like you would do with any other data sources.
Publishing Your Report to the Power BI Service
After you’re done authoring your report in Power BI Desktop, it is time to publish it to the Power BI Service. Note that, unlike in most other cases, the Publish button is disabled in Power BI Desktop for now. This will change with next month’s (April) release of Power BI Desktop, allowing you to publish your Redshift-based reports directly from Power BI Desktop.
For now, you will have to upload your PBIX file from within the PowerBI.com “Get Data” experience. We will select “Files” in the “Get Data” screen within PowerBI.com.
At this point, you can either upload your PBIX file directly or you can save it to OneDrive and connect to it from PowerBI.com to add the report to your workspace.
In this case, we will upload the Local File that we just created in Power BI Desktop. Please remember to close the PBIX file in Power BI Desktop before trying to upload it, or you will get an error otherwise.
After uploading the PBIX file, the dataset and report will be imported into your workspace. At this point you can open the report, but when using DirectQuery you will get an error that credentials need to be configured. This is caused by the fact that trying to open the report will trigger live queries against Redshift to retrieve the latest data for your visualizations.
Configuring Credentials and Refresh Settings
Clicking the “Enter credentials” link will take you to the Settings page for your dataset, where you can configure credentials by clicking the “Edit credentials” link. If you were using Import connectivity mode for this dataset, this is the page where you would be able to configure Refresh Schedule and check the Refresh History. Since Redshift is a cloud service, there is no need to use an On-Prem Data Gateway in this scenario; Power BI Service will directly connect to Redshift on your behalf to keep your reports up to date, either based on a Scheduled Refresh policy or based on user interactions with DirectQuery reports.
After credentials have been specified, you can go back to your report and, because we are using DirectQuery, now you will see the visualizations load up-to-date data from Redshift and refresh contents as you interact with them. At this point, you can leverage any other PowerBI.com feature, such as pinning visualizations from this report to your dashboards and share them with other users, consuming this content from Power BI Mobile apps, etc.
What’s Next?
We want to encourage you to try out these capabilities and give us feedback about both the Power BI Desktop Redshift connector as well as the report consumption experiences in PowerBI.com. We will continue making enhancements to this connector based on your feedback, leading up to General Availability state which we hope to declare soon.