Integrating Power BI with Adobe Real-Time Customer Data Platform: A Game-Changer for Business Intelligence

[#1]

In the age of data-driven decision-making, the ability to quickly analyze and act upon customer data is not just an advantage; it's a necessity. BI tools such as Power BI help businesses visualize and interpret their data, while Adobe Real-Time Customer Data Platform (CDP) has transformed the management and activation of customer information. However, the real magic happens when these two powerhouses are directly integrated. This integration, coupled with the capability to author additional insights data models directly within the Adobe Real-Time CDP using Data Distiller, is not just beneficial—it's critical.

The Value of Direct Integration

1. Immediate Access to Data: Direct integration between Power BI and Adobe Real-Time CDP eliminates delays inherent in transferring data between systems. This immediacy allows businesses to react to customer behaviors and market trends, making decisions that are both timely and informed.
2. Reduced Complexity: By streamlining the data pipeline, direct integration simplifies the analytics process. This simplicity not only saves time but also reduces the likelihood of errors that can occur when transferring data between multiple platforms.
3. Enhanced Data Security: Direct connections minimize the number of touchpoints through which data must pass, reducing the risk of breaches and ensuring that sensitive customer information remains secure.
4. Deep, Customizable Insights: Authoring additional insights within Adobe Real-Time CDP using Data Distiller allows for tailored, sophisticated analyses that go beyond surface-level data interpretation.
5. Change Management: Business users familiar with Power BI or similar BI solutions can continue to leverage those platforms, enriched with additional key insights, thus eliminating the need to learn a new tool.

Step-by-Step Guide to Connecting Power BI to Adobe Real-Time CDP

Step 1: Identify Your Adobe Real-Time CDP Insights

The list of available insights, along with sample questions they address and their respective SQL queries, includes profiles, audiences, and destinations

Depending on the insights you are interested in, you can note down the respective fact tables for access via Power BI. Choose one of the methods below to identify the fact and dimension tables of interest.

1. Review all the available data models and the respective tables to use via dashboard widget creation flow.
2. Go to the interested charts in Profiles, Audiences, and Destinations overview pages and use the View SQL functionality to note down the tables.
3. You can also review the entire Real-Time CDP Insights data model to get a preview of all the available fact and dimension tables provided by Adobe.

For this blog, I am going to focus on building an audience trendline chart and hence I will be accessing “adwh_fact_profile_by_segment_trendlines” table along with the lookups as shown below:

Step 2: Create Necessary Credentials

For this step you need to follow the query service credentials guide to create a credential.

Note down the Host, Port, Database, and Username as this is same for both expiring as well as non-expiring credentials.

Password option 1: Expiring credentials are a quick way to test these steps to integrate Power BI with Adobe Real-Time CDP but note that you will have to refresh the credentials every day.

Password option 2: Non-expiring credentials are ideal for setting up a more permanent connection to Power BI

For this blog I am going to create a non-expiring credentials password as follows:

 Once the non-expiring credentials are generated, a JSON file is downloaded.

In my case I have opened “Power BI Demo.json” and have the following:

{"technicalAccountName":"*******************@TECHACCT.ADOBE.COM","credential":"**********","technicalAccountId":"********************"}

Note: The password value takes the form: {technicalAccountId}:{credential}

We will be using the password above for establishing a direct connection next.

Step 3: Establish a Direct Connection

For this step, I will be using Power BI desktop. Once you open Power BI hit “Get data” and select “PostgreSQL database” and hit “Connect”

Next enter server and database noted from Step 2 as follows:

• Server: {Host}:{Port}
• Database: {Database}

Then hit “OK”

Then enter the User name and Password from Step 2 as follows:

• User name: {Username}
• Password: {technicalAccountId}:{credential}

Note: The password from non-expiring credentials JSON file value takes the form: {technicalAccountId}:{credential}

Hit “Connect” to start importing the desired data.

Step 4: Import Your Data

Power BI displays all the fact and dimension tables from which you can select the desired tables that were narrowed down in Step 1 above and hit 'Load’.

While Power BI might also display additional tables not surfaced in Step 1, it is recommended not to select those tables, as they might not be optimized for reporting and analytics needs. Refer to Data Distiller examples for guidance on how you can add additional insights to data models.

Step 5: Build Insights and Dashboards

Once the data is loaded you can build dashboards and report as you would in Power BI. Refer to Power BI documentation on guidance for how to author charts.

Step 6: Share and Act on Your Insights

While the creation of Power BI dashboards requires Power BI Desktop, once a dashboard has been created, it can be shared with all users who have the Office 365 version of Power BI, which is accessible via a browser. Below are some examples of what is possible:

Sample 1: Audience overlap report.

Sample 2: Campaign Performance

Sample 3: Customer AI propensity scores

Sample 4: Email performance

The Need for Authoring Customizable Insights via Data Distiller

Customizable Insights by Data Distiller benefits

1. Beyond Surface-Level Analysis: While Adobe Real-Time CDP provides valuable insights, the ability to author additional, customized data models directly within the platform allows for deeper analysis. This deeper analysis can uncover trends and opportunities that pre-built insights might miss.
2. Flexibility and Customization: Custom data models give businesses the flexibility to tailor their analysis to specific needs. Whether it's segmenting customers in a novel way or analyzing unique behavioral patterns, the ability to create these models within the Adobe Real-Time CDP is invaluable.
3. Enhanced Analytical Capabilities: The structured nature of STAR schemas facilitates more sophisticated analyses, such as trend analysis and predictive modeling, allowing businesses to not only understand the current landscape but also forecast future trends.

Conclusion

The integration of Power BI with Adobe Real-Time CDP, complemented by the capability to author additional insights data models directly via Data Distiller, represents a significant leap forward in business intelligence. This combination not only streamlines the analytical process but also enhances the depth and relevance of the insights generated. As businesses continue to navigate an increasingly data-driven world, the ability to analyze customer data quickly and efficiently will remain a key competitive advantage.

Author: @annamalai 

[#2]

Super helpful post - thanks! I have got a problem, do you know what only the highest in schema hierarchy data shows up? Two screenshots for you to understand what I talk about:

Power BI:

How the particular event looks like: 

[#3]

Thanks for sharing this post. 
I have questions around how did you handle de-duplication of user level data in power-bi. 
Always had that as an issue. 

[#4]

In you scenario data is in Accelerated Store not in Data Lake?

[#5]

@Michael_Soprano Yes it is recommended to have the data in the Accelerated Store before accessing via Power BI for ideal performance and avoid data loading timeouts

[#6]

Many thanks for respose!

@annamalai there is lack of documentation how to transfer data from Data Lake into Accelerated Store.

So in Accelerated Store you need to: 

1. Create database

2. Create Schema

3. Create Table 

4. Insert Data from Data Lake into Accelerated Store.

5. If the data is in Accelerated Store then we might based on that data build dashboards in PowerBI because data has flat structure (not nested) 

I found this blog: https://data-distiller.all-stuff-data.com/unit-1-prerequisites/prereq-103-key-topics-overview-archit...

Do you know any place in the documentation when its deeply explained?

[#7]

@Michael_Soprano yes here is a blog that has a demo vide explaining the full process: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-platform-blogs/a-guide-to-building... 

Also documentation is here: https://experienceleague.adobe.com/en/docs/experience-platform/query/data-distiller/sql-insights/rep...