Customer experience | Sales, Customer Insights,...

Retrieve Marketing Insights Data in SQL Server Reporting Services using Azure AD

(0) Share

Report

Posted on by Daniel Gz

Hello there,

i am currently having an Issue using SQL Server Reporting Services to get the Marketing Insights Data from an Dynamics 365 CE Online (v9.1) Instance.
I am bulding the Report with Reporting Authoring Extension in Visual Studio 2015.
I need to get all Marketing Insights Data (Email clicked, send etc) according to a single Contact in Dynamics 365 due to GDPR policy.
There is a specific API Action Call for this purpose (msdyncrm_LoadInteractionsPublic). I am able to access this Action trough SSRS using a XML Query against the Dynamics API and passing Parameters to it.

Because of Limitation i can only use anonymus Authentification or integrated Security Windows Authenification.
When i want to exeute the Query against the API Action using anonymus Auth i get a 401 Unauthorized Error back.
If i use a LogIn prompt and using my Dev user (which is a Azure Active Directory User) i get the response that my credentials are wrong.

As far as i figured out, i have to use a Azure AD Authentification becuase the Marketing Insights Data are stored somewhere in the Azure Cloud.
I registered an app/the user(?) in the Azure Portal to use Common Data Services to get a Client ID but i dont know how to use it with SSRS.

Is there any possibility to connect SSRS to use Azure AD Authentification?
Or is there another way to get these data?

Thankful for every hint.

best regard

Daniel

All responses (3)

Answers (0)

cloflyMao 25,198 on at

Like (0)

Report

RE: Retrieve Marketing Insights Data in SQL Server Reporting Services using Azure AD

Hi Daniel,

Could the solution work for you?

Regards,

Clofly
Suggested answer

sbtron on at

Like (0)

Report

RE: Retrieve Marketing Insights Data in SQL Server Reporting Services using Azure AD

Hey Daniel GZ

If you are looking to create reports based on interaction data like Email clicked etc. then you will first need to export it out to Azure Blob storage using this capability-

Prepare for analytic reporting with Power BI (Dynamics 365 Marketing) | Microsoft Docs

You can then pull it into your reporting tool of choice. The

The steps above from Clofly are for pulling data from CDS which will contain the individual entity types like the email being sent etc. but might not have all the interaction data (as described here: Glossary of common insights terms (Dynamics 365 Marketing) | Microsoft Docs) If you need to report all all the interaction insights then consider using the blob export functionality and reporting on that.

Thanks
cloflyMao 25,198 on at

Like (0)

Report
RE: Retrieve Marketing Insights Data in SQL Server Reporting Services using Azure AD

Hi Daniel,

The process to SSRS integration with Web API is a bit complicated: firstly we need to run localhost webform.

In the webform class file, with ActiveDirectory library for AAD authentication to connect to Dynamics 365 to execute msdyncrm_LoadInteractionsPublic API, then convert the response data to XML format.

https://www.codeproject.com/Tips/986958/SSRS-Report-Integration-With-Web-Api

Alternatively, I would suggest calling the API directly in flow and create a csv from response data.

Overview of the flow

Action 1

msdyncrm_LoadInteractionsPublic is an unbound action

Action 2

Parse the Data property of response to JSON with sample JSON payload.

[ { "SendingId": "121764e4-62b1-1b89-ef36-5b779aedb5e9", "ContactId": "bf5c52e1-022b-eb11-a813-000d3aa080ec", "EmailAddressUsed": "clofly@test.com", "EmailDomain": "test.com", "MessageId": "8a68c12e-b027-eb11-a813-000d3aa080ec", "CustomerJourneyId": "8273746d-572d-eb11-a813-000d3aa080ec", "CustomerJourneyIterationId": "5e663f77-4531-4532-b70b-da4473319ce1", "UsageType": "CustomerJourney", "ActivityId": "bf845412-1acc-d44d-422e-364519cfe9d6", "OrganizationId": "26d382eb-326c-47f8-93f4-4ecd22c245a8", "InteractionType": "EmailSent", "Timestamp": "2020-11-23T06:46:48.6080000 00:00" }, { "SendingId": "d2b5f0f6-1c76-6918-be7e-ae01a82d5f89", "ContactId": "bf5c52e1-022b-eb11-a813-000d3aa080ec", "EmailAddressUsed": "clofly@test.com", "EmailDomain": "test.com", "MessageId": "8a68c12e-b027-eb11-a813-000d3aa080ec", "CustomerJourneyId": "57edf92a-5a2d-eb11-a813-000d3aa080ec", "CustomerJourneyIterationId": "6c776ac5-6e38-4817-b805-0d03bc362b1a", "UsageType": "CustomerJourney", "ActivityId": "b77b8f42-4e7f-300b-68f3-c835337bb8b9", "OrganizationId": "26d382eb-326c-47f8-93f4-4ecd22c245a8", "InteractionType": "EmailSent", "Timestamp": "2020-11-23T07:06:06.4000000 00:00" } ]

Action 3:

Create a CSV table from Body of the parsed JSON

Action 4:

Create a csv file from output of action 3 and save it to OneDrive.

Result

The converted file in OneDrive folder:

CSV content:

Regards,

Clofly