Archive | July 2015
July 24, 2015  

Errors in Goal Recalculation

Platform: Dynamics CRM 2013 On-Premise SP1

Entity: SalesOrderDetail

Goal Metric: Extended Amount (sum)

Rollup Query: Product Group

Time Range: Custom

Symptom: Goal Progress totals are inaccurate (when two or more records have the same extended amount, product group and creation date.)

I noticed that after recalculating the above mentioned goal, the resulting progress total amount was inaccurate.  Started a SQL profile, and found out that the supporting SQL statement, yields less results than a direct query.

A direct query like the following one yields 82 rows:sqlok

However, the SQL generated by the goal recalculation yields less records:sql_Union

If we look at the first few rows in both result sets, we realize that since the goal recalculation uses a UNION statement, it excludes records with the same information (thinking that they are duplicates). For example, the first sales order has 2 lines with basically the same data (amount 46.51), but in the second set, we appreciate that only one is accounted for.

The problem happens when you have 2 or more salesorderdetail records with the same “extended amount” and the same product group. Since your SQL script uses a UNION statement, it thinks that those records are duplicates. The goal recalculation SQL statement fetches the following columns:

ExtendedAmount, new_productgroupid, ownerid, createdon, ownerbusinessunit, salesorderid

I am not sure why doesn’t it fetch salesorderdetailid (the primary key), which would resolve the problem in the recalculation.

The MSFT Dynamics CRM product team needs to look into this.

Advertisement
July 15, 2015  

Is Goal Recalculation Time-zone Independent? Well, maybe not.

One of the nice features in Dynamics CRM when it comes to dates, is that it stores everything in Universal Time. For the most part, this works pretty well until you run in the following scenario related to goal recalculation.

Let’s suppose you have teams of salespeople grouped by regions, and that you have members in those teams that have goals not tied to any direct sales but goals assigned to the entire team, for example administrative staff. In this scenario, you would create goals with the following characteristics:

  • Goal Metric: Amount of Sales
  • Goal Owner: Admin staff
  • Target: Fixed Dollar Amount
  • From: Fixed Date
  • To: Fixed Date
  • Rollup Only from Child Goals: No
  • Record Set for Rollup: All (sales records owned by all team members)
  • Rollup Query: A query to filter by region

In this case, the expectation would be that all administrative staff shows the same progress at any moment. Well, at least that’s what I thought until I looked at the following chart:

goals

Figure 1 – Goal Progress Chart

It’s obvious that the progress for the first member is different from all others. After verifying that all the goals were exactly the same and have been recalculated around the same time, I was running out of ideas.

Fortunately, this is a CRM on premise implementation, so I had more chances to look at the server at a deeper level. The CRM async service was running OK, and no errors were found in the event log or CRM trace.

So I needed to dig even deeper. I ran a SQL Profiler session, and decided to recalculate the goals for the first and second person in the team. After a few hours of research, I found the smoking gun:

Person_Central

Figure 2 – Excerpt query person 1

Person_EST

Figure 3 – Excerpt query person 2

It turns out that person 1 had his time-zone set for Central Standard Time, whereas person 2 had his time-zone set for Eastern Standard Time; therefore, the query for person 1 was missing all transactions for the first day in the time range, given that all sales data only records the date of the transaction and the data is imported using Eastern time.

Now that I found this, at least I know what caused the discrepancy, and the fix is as simple as making every team member use the same time-zone. However, that may not be a realistic scenario. You could perfectly have teams that have members across different time-zones, in which case the goal recalculation should really be time-zone agnostic. That’s up for debate or perhaps to have the Dynamics product team take a look at.