Customer Address revisited

Customer Address revisited

Recently two new features were introduced to dataverse that allows us to better manage customer addresses. A few years ago (2012) I wrote an article on the subject (https://community.dynamics.com/blogs/post/?postid=b889a366-d3fb-4b39-bdb9-7f3716283d4c) which goes into some detail about how the customeraddress table works. This post will revisit some of that and also discuss how the two new features regarding the customeraddress can help us better manage our data.

Here is a detailed link from Microsoft regarding the customer address table. It is well worth reading this to understand how the system handles customer addresses.
Customer tables (account, contact, and customeraddress) (Microsoft Dataverse) – Power Apps | Microsoft Learn

To make this a bit shorter, the addresses of accounts and contacts are not stored in these tables but actually in a special table called customeraddress. This is hidden to most user operations but it does show up in some cases, for instance in storage calculations in PPAC. The same goes for the lead table which has a special leadaddress table that supports that.

With the default settings, for every account, you will get 2 customer addresses, and for every contact you will get 3 customer addresses. Lead has two addresses and hece will have two records of lead addresses.

This can start becoming an issue if you have lots of these base records, contact, account or leads, which are more or less empty. So even if there is no address data on these records, they will still have these supporting records and all records in dataverse will take up some storage, based on the fact that there are some fields that always are created like modifiedon, createdon, modifiedby etc. Typical scenario where this can start becoming an issue is if you are using dataverse for marketing and you have several million leads to whom you send newsletters but don’t really know that much more about them. With 1 million leads, you will get 2 million empty lead addresses.

So, what is new? The new functionality Microsoft have released are two features. The first feature is that the customeraddress or leadaddress records are not created until there is actually information that needs to be stored in these records. This is generally a good setting to be set to true, as storing more or less empty records isn’t really very useful.

If you have an existing system with 2 million contacts and 4 million leads, this will however not remove the existing customeraddresses (6 million) and leadaddresses (8 million) that are empty. For this, there is the second new feature which allows you to remove customeraddresses without removing the contact/account/lead. There is, of course a workaroud for this, and that is to create a new contact/account/lead with the first feature switched on, hence not creating any address records, and then merging this record with the old one and finally removing the old deactivated records. It is, however, much easier to solve it with just removing the address records.

Before running anything like this, make sure you make a forced backup of your instance. I have seen onprem systems where there are lacking address records and that is just very very weird.

Hope this helps explaing a bit about how this functionality works.

CI-D Measure dependency on Dataverse

CI-D Measure dependency on Dataverse

When measures are created in CI-D to be used in CI-J there are dependencies created which will stop you from removing the measure. CI-D is not very helpful in telling you how to remove it. I will try to be a bit more helpful.

When trying to remove the Measure this is the error I got:

Error: Detected DataVerse dependencies in Measure: ListMemberships. Please delete these dependencies and merge again.
Request ID: 14056846-b9c1-4ec8-98c2-88778e518b88
Time: 2024-12-05, 11:12:10

My investigation below with the help of Microsoft Support lead me to the understanding according to the image above. As you can see dependency is actually sort of circular, or at least one part of the dependency (dependent component) is at the entityanalytics on the record which points to msdynci_listmemberships, on the other hand (required component) on the table msdynci_listmemberships.

The fix, is to remove the record in entityanalytics. Easiest way in this case, is to just use a browser console where you are logged into the right Dynamics.

To remove the entityanalytics record that is blocking this:

Xrm.WebApi.deleteRecord('entityanalyticsconfig', 'fc105928-3170-ef11-a671-000d3a64e5b5')

After this I was able to remove the measure in CI-D.

Below I will detail the steps I did to understand this.

The first step is to look in the table “entityanalyticsconfig” searching for the name of the Measure. Its name is “msdynci_” + name of the measure. Check out the metadata if you are unsure.

<fetch top="50">
  <entity name="entityanalyticsconfig">
    <filter>
      <condition attribute="parententitylogicalname" operator="eq" value="msdynci_listmemberships" />
    </filter>
  </entity>
</fetch>
{
  "value": [
    {
      "isenabledforadls": true,
      "parententityid": "dd7613fe-806e-ef11-a670-000d3ad82e35",
      "modifiedon": "2024-09-11T11:29:54Z",
      "componentidunique": "807e7e6d-71f5-446a-8009-437cbf5a922a",
      "isenabledfortimeseries": false,
      "versionnumber": 15487568487,
      "parententitylogicalname": "msdynci_listmemberships",
      "entityanalyticsconfigid": "fc105928-3170-ef11-a671-000d3a64e5b5",
      "componentstate": 0,
      "organizationid": "ba0a9fa5-462b-45cc-b603-e9b16acd63c4",
      "solutionid": "fd140aae-4df4-11dd-bd17-0019b9312238"
    }
  ]
}

As you can see above the id is fc105928-3170-ef11-a671-000d3a64e5b5. This is the record I did the removal on above.

To further understand the dependency record for this, let’s dig into that table, searching for a record with this id.

<fetch top="50">
  <entity name="dependency">
    <attribute name="dependencyid" />
    <attribute name="dependencytype" />
    <filter>
      <condition entityname="dcn" attribute="objectid" operator="eq" value="fc105928-3170-ef11-a671-000d3a64e5b5" />
    </filter>
    <link-entity name="dependencynode" from="dependencynodeid" to="dependentcomponentnodeid" link-type="inner" alias="dcn">
      <attribute name="componenttype" />
      <attribute name="objectid" />
      <attribute name="parentid" />
    </link-entity>
    <link-entity name="dependencynode" from="dependencynodeid" to="requiredcomponentnodeid" link-type="inner" alias="rcn">
      <attribute name="componenttype" />
      <attribute name="objectid" />
      <attribute name="parentid" />
    </link-entity>
  </entity>
</fetch>
{
  "value": [
    {
      "rcn.componenttype": 1,
      "rcn.parentid": "00000000-0000-0000-0000-000000000000",
      "dcn.parentid": "00000000-0000-0000-0000-000000000000",
      "dcn.objectid": "fc105928-3170-ef11-a671-000d3a64e5b5",
      "dependencyid": "e46e52db-46a7-4585-8a6a-6ba888a5bd1f",
      "dcn.componenttype": 430,
      "rcn.objectid": "dd7613fe-806e-ef11-a670-000d3ad82e35",
      "dependencytype": 2
    }
  ]
}

The result shows us that the required component id is: dd7613fe-806e-ef11-a670-000d3ad82e35 which is of type: 1.

By checking this table in Microsoft Learn, we can see what this is: (both link and table)

By searching the table called “entity” for 430, we can find what table this represents:

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/reference/dependency?view=dataverse-latest

ValuesLabel
1Entity
2Attribute
3Relationship
4Attribute Picklist Value
5Attribute Lookup Value
6View Attribute
7Localized Label
8Relationship Extra Condition
9Option Set
10Entity Relationship
11Entity Relationship Role
12Entity Relationship Relationships
13Managed Property
14Entity Key
16Privilege
17PrivilegeObjectTypeCode
20Role
21Role Privilege
22Display String
23Display String Map
24Form
25Organization
26Saved Query
29Workflow
31Report
32Report Entity
33Report Category
34Report Visibility
35Attachment
36Email Template
37Contract Template
38KB Article Template
39Mail Merge Template
44Duplicate Rule
45Duplicate Rule Condition
46Entity Map
47Attribute Map
48Ribbon Command
49Ribbon Context Group
50Ribbon Customization
52Ribbon Rule
53Ribbon Tab To Command Map
55Ribbon Diff
59Saved Query Visualization
60System Form
61Web Resource
62Site Map
63Connection Role
64Complex Control
70Field Security Profile
71Field Permission
90Plugin Type
91Plugin Assembly
92SDK Message Processing Step
93SDK Message Processing Step Image
95Service Endpoint
150Routing Rule
151Routing Rule Item
152SLA
153SLA Item
154Convert Rule
155Convert Rule Item
65Hierarchy Rule
161Mobile Offline Profile
162Mobile Offline Profile Item
165Similarity Rule
66Custom Control
68Custom Control Default Config
166Data Source Mapping
201SDKMessage
202SDKMessageFilter
203SdkMessagePair
204SdkMessageRequest
205SdkMessageRequestField
206SdkMessageResponse
207SdkMessageResponseField
210WebWizard
18Index
208Import Map
300Canvas App
371Connector
372Connector
380Environment Variable Definition
381Environment Variable Value
400AI Project Type
401AI Project
402AI Configuration
430Entity Analytics Configuration
431Attribute Image Configuration
432Entity Image Configuration
<fetch top="50">
  <entity name="entity">
    <attribute name="logicalname" />
    <filter>
      <condition attribute="entityid" operator="eq" value="dd7613fe-806e-ef11-a670-000d3ad82e35" />
    </filter>
  </entity>
</fetch>
{
  "value": [
    {
      "logicalname": "msdynci_listmemberships",
      "entityid": "dd7613fe-806e-ef11-a670-000d3ad82e35"
    }
  ]
}

Hence, from this I generated the image above. Since it is something of a circular reference, Microsoft Support suggested that I remove the entity analytics record with id: e46e52db-46a7-4585-8a6a-6ba888a5bd1f. However, not entirely sure what this is and hence I tried the one I had gotten in the first steps above, and that worked.

Generally I think it is a good idea to investigate what the dependencies point to, before removing something. In this case the dependency record in itself wasn’t removed, only the dependent part.

Thanks to Microsoft support for helping out with this and I hope it might shed some insights if you are having similar issues. I also think breaking down the dependency table in a tool in for instance XrmToolBox would be a great idea. I have a bit of a bad conscience for BulkDeleteManager that I own and I am not giving enough love, so feel free to build it based on this and I guess some more stuff. I will be happy to help out the the investigations if you are willing to build it.

Customer Insight Journeys Real Time Dynamic Forms Loader

Customer Insight Journeys Real Time Dynamic Forms Loader

In a previous post I blogged about how to break down the Form script that was exported from CI-J (Real Time). As some customers asked me about making this into a script that was the same and was dynamic based on query string parameters (parameters in the URL), I worked a bit on that and thought I’d share it here;

<div id="formdiv">
</div>

<script>
function getQueryParam(name) {
         const queryString = window.location.search;
         const urlParams = new URLSearchParams(queryString);
         const queryparam = urlParams.get(name);
         return queryparam;
        }

        const formobj = document.querySelector('#formdiv');
        const formid = getQueryParam('id');
        const formapiurl = 'https://public-eur.mkt.dynamics.com/api/v1.0/orgs/' + getQueryParam("orgid") + '/landingpageforms'; 
        const formcachedurl = 'https://assets-eur.mkt.dynamics.com/' + getQueryParam("orgid") + '/digitalassets/forms/' + formid;

        formobj.setAttribute('data-form-id', formid);
        formobj.setAttribute('data-form-api-url', formapiurl);
        formobj.setAttribute('data-cached-form-url', formcachedurl);
</script>
    
    <script src='https://cxppusa1formui01cdnsa01-endpoint.azureedge.net/eur/FormLoader/FormLoader.bundle.js'></script>

A few things that can be mentioned. This script expects that there will be two query string parameters:
id – the id of the form. Click on the form and copy it from the URL after the “id=”
orgid = the orgid which you can find in the PPAC or in the export of the script as I described in the previous article. If it is placed on the url: https://contoso.com/form.html then an example of the url would be:

https://contoso.com/form.html?id=6d10214b-cda1-41d8-9bf7-2c7262c527aa&orgid=b1616e23-557d-4a3f-8112-e8fac97f8ebe

Finally some organizations also have problems with loading a script from external sources. I will look at that too. Mainly there are two option. Copy-paste the entire script inline into this script or copy the script file to an “internal” or recognized store with a public URL and change the src-attribute. If you move this away from referencing Microsoft, I would recommend checking their website on a regular basis to make sure it hasn’t changed.

I hope this helps out!

Calculated columns + Azure Synapse Link != true

Calculated columns + Azure Synapse Link != true

I was recently helping my colleague Ebba Linnea Nilsson with a support ticket with data not being propagated correctly from dataverse to a datalake via Azure Synapse Link. It turned out that this was all by design. A design that might not be what normal users would expect.

Calculated columns and now recently the formula columns are both very useful way of being able to calculate data in a field that is based on other fields. Common scenarios are calculations like “Weighted revenue” which is the probability multiplied by the estimated revenue for an opportunity. However, there are scenarios where you need to be aware of how these fields actually work or you might get an unwanted or unexpected behaviour.

The first thing that needs to be understood is that these column types are calculated “on-the-fly” everytime dataverse attemts to access these columns. It might seem like the data is “in the columns” but it really isn’t, it is calculated. This is a big difference from for instance rollup-columns is that those columns are calculated on a regular interval by the system, and the result is stored in the record.

What does this mean for Azure Synapse Link? Well, let’s say we have a simple calculation, that sets the value “A” into all records for this calculated column. We then enable the Azure Synapse Link which will make an initial sync and set the column in the datalake to “A”. Now we change the calculation of the rule to output “B” instead. As no records are actually changed, this will not cause any records in the datalake to be updated, hence they will all still have the value “A”. From a user perspective comparing Dynamics 365 to the datalake without any underlying understanding of how this functions, it will look like an error. Same column has different values comparing what is in dataverse with what is in the datalake.

As soon as a record is actually changed, all columns for that record will then be sent to the datalake, and hence the calculated column will be set to “B” at that time. It is hence possible, to manually or semimanually force a resync, but it would require some bulk like for instance SSIS with Kingswaysoft especially for implementations with large amounts of records.

An important question to ask, is why would you want to calculate the data in dataverse and then use it in in the datalake. If you have a propper datalake architecture it should be easier to make calculated columns/fields in the datalake/datalakehouse. If the data is calculated only for use in the datalake, I would suggest moving the calculation to the datalake.

There are, of course, scenarios when it is preferrable to have calculations in one place and reuse the output in many places. However, this understanding of what can reasonably be expected is then essential.

As for product improvements, I have added an idea on the subject, if you agree with me, please vote! Microsoft Idea (dynamics.com)

A final note is that this type of unexpected behaviour is not limited to just Azure Synapse Link but really to any integrations based on either “modified on” or change tracking without doing periodic synchronizations. Hence I would also like to give a general warning about this.

Utilize query from Marketing reports in data flows

Utilize query from Marketing reports in data flows

Recently I needed to get the marketing insight replicated data from the data lake where it is replicated to, to Customer Insight. However, that turned out to not be very easy as the data had some formatting issues. However, I found that the Marketing reports that Microsoft have release for Power BI can be used as inspiration for how to query the data.

I am currently working on a rather long Proof of concept of Customer Insight for a customers. One of the things they wanted to see if it worked to get into Customer Insight was EmailClicked which is part of the Marketing Insight data that Marketing stores in an internal database. This can rather easily be replicated to an external datalake, with some configuration in the admin tab in Marketing. However, when I tried to connect to that data, using the Azure Datalake gen2 adapter in Customer Insight, it consistently said that there was no data. After some digging I found that the main reason for this was that the files in the datalake did not have the propper file-ending for the datalake connector to understand them. In short, they are csv-files, but do not have the filename xyz.csv. Simple enought problem, I thought but as I am not super comfortable working with datalake data, I tried to figure out some way of easier solving this issue. First I tried using the dataflow connector to ADLS gen 2 but that got the same problem. Just so you know.

Then it struck me, data flows use PowerQuery/M which is the same thing that is used in Power BI, AND Microsoft have release some marketing reports for Power BI that utilize the data in the datalake combined with dataverse. I hence opened one of these and tried to copy the entire data query part. It turned out to be more than 20 different components. Datasources, configuration, functions and more. When pasted into the Data Flow, it simply didn’t work and as usual, I didn’t get a very good errormessage. But my hunch was that maybe the logic is too complicated with too many internal connections for the data flow. If you know the limitations here, please leave a comment. This didn’t stop me, my next step was to remove all unnecessary stuff from it, like the dataverse queries, config and such, but still, it didn’t work. So I attempted to move it all into one single M-script with a hardcoded referece to EmailClicked. When I did that, it worked! This is the final result:

let

    Source = AzureStorage.Blobs("<datalakename>"),
    #"ContainerContent" = Source{[Name="<containername>"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(ContainerContent,{"Content", "Name", "Date modified", "Attributes"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Name] <> "model.json"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Sorted Rows", "Attributes", {"Size"}, {"Size"}),
    #"File Name column" = Table.DuplicateColumn(#"Expanded Attributes", "Name", "File Name"),
    #"Remove csv" = Table.ReplaceValue(#"File Name column","csv/","",Replacer.ReplaceText,{"File Name"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Remove csv", "File Name", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Interaction Name", "File Name"}),
    #"Transform" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Interaction Name", type text}, {"File Name", type text}, {"Size", Int64.Type}}),    
    #"Add Datestamp" = Table.DuplicateColumn(#"Transform", "Date modified", "Datestamp"),
    #"DateStampFormat" = Table.TransformColumnTypes(#"Add Datestamp",{{"Datestamp", type date}}),
    TodayFunction = DateTime.FixedLocalNow,    
    #"Add Today" = Table.AddColumn(#"DateStampFormat", "Today", each TodayFunction()),
    #"Changed TodayType" = Table.TransformColumnTypes(#"Add Today",{{"Today", type date}}),
    #"Add DaysFromToday" = Table.AddColumn(#"Changed TodayType", "DaysFromToday", each [Datestamp]-[Today]),
    #"Changed DaysFromToday" = Table.TransformColumnTypes(#"Add DaysFromToday",{{"DaysFromToday", Int64.Type}}),
    Result = Table.RemoveColumns(#"Changed DaysFromToday", "Today"),
  result2 = Table.SelectRows(Result, each [DaysFromToday] >= -180),
  FilteredByInteraction = Table.SelectRows(result2, each [Interaction Name] = "EmailClicked"),
  #"AddFileContents" = Table.AddColumn(#"FilteredByInteraction", "FileContent", each
    Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true])),
  #"ContentTable" = Table.SelectColumns(AddFileContents,{"FileContent"}),
  #"NoDataFiles" = Table.IsEmpty(#"ContentTable"),
  InteractionTable = Table.ExpandTableColumn(#"ContentTable", "FileContent", Table.ColumnNames(ContentTable{0}[FileContent])),
  #"Transformed" = Table.TransformColumnTypes(InteractionTable,{{"Timestamp", type datetimezone}}, "en-US"),
  #"Duplicated Column" = Table.DuplicateColumn(#"Transformed", "Timestamp", "Datestamp"),
  #"Datestamp" = Table.TransformColumns(#"Duplicated Column",{{"Datestamp", DateTime.Date, type date}}),
  #"RenameId" = Table.RenameColumns(#"Datestamp",{{"InternalMarketingInteractionId", "Id"}}),          
  #"keyedtable" = Table.AddKey(#"RenameId", {"Id"}, true)
in
  #"keyedtable"

I have highlighted the datalake name, container and the table. I hope this helps if you are having a similar issue!