Power BI with AWS Redshift

In this short article, I will share with you a real-time working experience using AWS Redshift/Spectrum on Power BI.

One of our valued client was looking for a solution to blend data from files uploaded by users with data from ERP Sources in the cloud environment with their existing landscape in AWS. With Power BI extended support to connect to AWS – Redshift Spectrum allows users to analyze the entire set of data that is stored in the S3 data lakes with Redshift.

The above sales dashboard allows you to analyze sales performance by the important metrics used by the various events and categories. This estimation and revenue allow the event manager to think of strategies to achieve desired sales result in the upcoming quarter or year.

If you still want to get personalized or deeper insights, let’s see what are the possibilities if you use AWS Redshit/Spectrum as a data source

Data Connectivity:

• We can convert the data source to import query.
• If we use direct query there are certain limitations.
• Native Query is not supported as of now with AWS Redshift /Spectrum.
• Custom SQL Query supports this data source.

Data Preparation Using Direct Query:

Once the data is pulled on the power query we can change the data type of column as per our report requirement. The custom column can be added to the data model but it only works when we add a pass-through column in a table or other visuals. we can also parameterize the connection string of the data source so it helps for migrating the Power BI dataset pbix to a different environment as followed.

Data Modeling:

Here comes the interesting part in Power BI using AWS redshift/spectrum we can do the following operations in the data modeling part that are:

• Aggregations
• Composite Data Model
• Many-to-Many Relationships
• Multiple Fact Tables

DAX using Direct Query:

As we all know there is a certain limitation using dax in DQ. If you’re creating a calculated table using dax functions like UNION, VALUES, DISTINCT, INTERSECT   can only be used.  for the calculated column, only the limited dax can be used since there is no native query support and another important factor is Time Intelligence functions mentioned below can be used in dax measures

• DATE
• MONTH
• YEAR
• DAY
• EOMONTH
• EDATE
• YEARFRAC
• TIMEVALUE
• WEEKDAY
• WEEKNUM
• SECOND
• TIME
• QUARTER
• MINUTE
• HOUR

Row Level Security (RLS):

Dynamic and Fixed Row-Level Security features are supported. The below-mentioned DAX functions are not supported as of 23rd Nov 2021.

• PATH
• PATHCONTAINS
• LOOKUPVALUE

Gateway:

The On-Premises gateway is not required for the DirectQuery dataset and refreshing the import dataset in the Power BI service. We don’t need to install any additional drivers ODBC/JDBC.

Dashboard Credit: Siva-mani
Power BI Data Stories Gallery: Sales Dashboard Theme – Amazon Redshift

Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Power BI.

Click here to view AWS Tableau Blog