In the ever-evolving landscape of data analytics, the agility to adapt and present information clearly and securely is not just an advantage—it's a necessity. As we strive to craft more impactful dashboards, particularly in the critical area of Adobe Real-Time CDP, leveraging the capabilities of database views with in Data Distiller has emerged as a transformative approach. Here's how creating views in your insights data model can offer unparalleled benefits:
Simplified Data Access
Crafting a user-friendly experience starts with the complexities of our data models. By creating views, we distill complex relationships into accessible insights. This abstraction layer means that dashboard users can focus on the insights, not the intricacies of SQL joins or table schemas.
Tailored Data Perspectives
Different stakeholders require different data slices to inform their decisions. Views serve as a bespoke lens, offering customized snapshots of data tailored to the needs of various roles within the organization—be it marketing, compliance, or customer service.
Consistent and Intuitive Reporting
The heart of a good dashboard lies in its consistency and ease of interpretation. Views encapsulate common calculations and transformations, presenting a consistent, read-only data model that can power your dashboards efficiently, allowing end-users to generate reports with confidence.
Optimal Performance
A well-designed view can significantly reduce query complexity and improve the performance of your dashboards. By materializing views, we can pre-compute and store complex aggregations, ensuring that dashboards load swiftly, and user experience remains smooth.
An Example to Embrace Views: The Consent Dashboard
To illustrate the capability of Views in Customizable Insights, I plan to develop a view for crafting a dashboard utilizing the consent insights data model. This view will streamline "fact_marketing_consent_aggregates" and "fact_preferred_consent_aggregates," enabling business users to easily report consent via email, SMS, etc., and total consents for preferred channels. By centralizing this information, the view eliminates the need for users to switch between fact tables or manage dimension tables, simplifying dashboard creation.
![annamalai_0-1711854211918.png annamalai_0-1711854211918.png](https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/72167i72B4283453DFCE93/image-size/large?v=v2&px=999)
Here is the sample SQL to create the view for dashboards:
--Using Consents Insights Data model DB:consenttracking, Schema:consenttrackingmodel
CREATE VIEW
consenttracking.consenttrackingmodel.view_full_marketing_consent AS
SELECT
fmc.date,
fmc.marketing_type AS marketing_type_id,
dmt.display_name AS marketing_type_name,
fmc.consent_value_id,
dcv.consent_value,
fmc.total_consents,
fpc.total_consents AS preferred_total_consents
FROM
fact_marketing_consent_aggregates fmc
JOIN dim_marketing_type dmt ON fmc.marketing_type = dmt.marketing_type
LEFT JOIN dim_consent_value dcv ON fmc.consent_value_id = dcv.consent_value_id
LEFT JOIN fact_preferred_consent_aggregates fpc ON fmc.date = fpc.date
AND fmc.marketing_type = fpc.marketing_type;
Since this view is created under the consent insights data model with Database name: consenttracking and Schema name: consenttrackingmode..., it will automatically show up in Dashboards for users to start authoring dashboards.
![annamalai_1-1711854211926.png annamalai_1-1711854211926.png](https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/72168i3F18422E80C02D0F/image-size/large?v=v2&px=999)
For examples of art of possible consent dashboards, check out this blog covering the end-to-end creation from model to dashboards.
As we continue to innovate in how we present and analyze data, views stand out as a pivotal tool in our arsenal. They are the unsung heroes in our quest to unlock the full potential of our insights data models, and they play a critical role in transforming raw data into strategic assets.
Harness the power of views in your dashboards, and turn the complexity of data into clear, actionable insights that drive decision-making and uphold the trust of your users.
Author: @annamalai