Tip #349: Bulk delete in bulk

After using Bulk Delete Wizard in Dynamics CRM

one can be mistaken for thinking that bulk delete is only capable deleting one advanced find result screen at the time.

But if mentioning of C# invokes more that just musical associations for you, welcome to the realm of SDK, where things are not what they seem in user interface. As per SDK documentation:

With bulk deletion you can perform the following operations:… Delete data across multiple entities…

BulkDeleteRequest class contains a member variable QuerySet which, as the name suggests, is an array of QueryExpression objects.

Bonus

If you ever spent time trying to make your bulk delete to run faster by modifying query page size, stop it. System duly ignores all your attempts and creates its own PageInfo object that processes 1,000 records batches.

Tip #348: Watch your audit partition size

We have had several tips this week regarding managing storage on CRM online, and I wanted to finish the week with one more way that the storage gremlins can creep up on you: Audit partitions.

I recently was reviewing one of my environment’s audit logs, and I found one of these things was not like the other.

Due to some unfortunate circumstances, the most recent audit partition is 164 GB (that’s right, with a G).

What you need to know about audit partitions:

You can delete them, starting with the oldest one.
You cannot delete the current audit partition until the end date. They happen quarterly, so the end of the current partition is 3/31.
You can delete them via the SDK, but it has the same limitations as number 2.

So what this means is that if you have a very large audit partition and it is using all of your available storage, there is no supported way for you to shrink it or delete it. You have to wait until the end of the period, then you can delete it.

For on premises users, you can go the unsupported route and delete audit records via T-SQL. If you use CRM Online, open a ticket with Microsoft support or add additional storage should this happen to you.

Tip #347: How to use datetime fields in WHERE clause

Gotta love user Q&A sessions, like the one at Convergence 2015 that I had a privilege to take part in. Questions from users are always refreshing, very often challenging and sometimes they are just the reminder that any small bit of information that one possesses is not necessarily the common knowledge.

On this occasion question was about poor performing reporting queries dealing with the activity records. There were tons of good suggestions but one thing that the user seems to have missed is how to properly use CRM datetime fields in the queries. Let’s say we’d like to filter all activities that started last month. The first query that comes to mind:

select activityid, subject 
from FilteredActivityPointer
where scheduledstart >= '1-feb-2015' 
  and scheduledstart < '1-mar-2015'

Reasonable? Yes. Best performing? Heck, no. The problem is that filtered views, among other things, convert datetime CRM fields into the timezone of the current user. To do that, datetime column names have suffix “utc” added to them and the original names are used to display local time equivalent. For example, every entity in fitered view would have createdon (local time) and createdonutc (the actual value stored in the database).

How this local time is calculated? Using scalar-value T-SQL functions. What chances are of the scalar-value functions in WHERE clause using an index? None. Hello, table scans.

The Spießrutenlaufen Rule of Filtered Views

Whenever CRM datetime field needs to be in the WHERE SQL clause, the utc version of the field should be used.

The above would become:

select activityid, subject 
from FilteredActivityPointer
where scheduledstartutc >= 
            dbo.fn_LocalTimeToUTC('1-feb-2015') 
  and scheduledstartutc < 
            dbo.fn_LocalTimeToUTC('1-mar-2015')

How much do you save? YMMV but on my test server against random CRM organization the execution time of the first query was 2280ms, whilst the second has finished under 80ms.

Like a boss meme 96% improvement.

Tip #346: Scheduling daily bulk delete jobs

I noticed that my CRM Online storage was getting low, so I created a scheduled bulk delete job to remove system jobs. My environment has many workflows, and since upgrading to CRM 2015 and creating a bunch of rollup fields, I have noticed a slight increase in the number of system job records being created. I want to have a job that runs daily and deletes successfully completed jobs. However, the minimum frequency of bulk deletion job recurrence is 7 days.

Adam Vero gave me a great, simple suggestion–schedule multiple bulk delete jobs, one for each day of the week. Set each to recur every 7 days. The end result is a daily schedule.

UPDATE: Turns out there is an even easier way. Even though the drop-down doesn’t give you a daily option, you can still choose 1 by typing in the field. Like the phone call duration field, the “run the job after every X days” field gives you pre-set options, but also allows you to type in virtually any number you want. So simply type in “1” rather than selecting a value from the drop down.

Tip #345: Turn CRM Green

Today is St. Patrick’s Day, a day when many celebrate by wearing green and ingesting green-colored beer.

As you probably know, we at crmtipoftheday are fans of making CRM more festive. So wouldn’t it be great to be able to make CRM green?

With the CRM Online Spring ’15 Update, you can!

Go to Settings–>Customization
Click “Themes”
Select the CRM default theme, then click “Clone” to make a copy of the default theme.
Change the value for Header Color to HTML color code #006551

The results will leave you yearning for the emerald isle.

Not only can you change the application colors, you can also now replace the logo. This “branding” feature is something that CRM users have frequently requested. We suggest that you choose something seasonally appropriate. A leprechaun, for example.

Tip #344: Why Can’t I Create Any Records?

If you use CRM Online, and you run out of space, what happens?

This:

The Quick Create links disappear, replaced by a gray bar.

And the “New” button disappears from views and grids.

When this happens, either add space or delete records (like completed system jobs) to free up memory. After doing that, there will still be some delay before you can once again add records. If it continues to not allow record creation, open a support case with Microsoft support.

Tip #343: In Dynamics CRM we trust

Having customer’s commitment issues? Something like this?

Oh, I don’t trust corporations, they are out there to get you and your money. Remember Lehman Brothers?

To start with, unlike subprime mortgages, Microsoft Dynamics CRM is a real product. What’s important though, that Microsoft, unlike payday lenders, recognizes concerns customers and prospects alike might have about entrusting their valuable business data to a cloud provider, Microsoft or not.

Enter Microsoft Dynamics CRM Trust Center. Abridged summary follows, to save you a visit until you have stocked up on your coffee supplies and have a spare afternoon to sieve through the abbreviations and trying to understand how PII is different from SSN.

Privacy

No ads, no mixing the data with other customers, data portability – unless there is a court injunction stopping you from doing so, you can “whack” your data at any time (by proxy, of course, no one will let you anywhere near the datacenter, and that’s a good thing).

Transparency

You know where the datacenters are. You know who has access to your data and under what circumstances.

Security obsession

Decades of experience, five layers of the security, proactive monitoring, restricted server access. ‘Nuff said.

Standards

First major cloud provider to support ISO/IEC 27018. Certified for ISO 27001, support for EU Model Clauses, HIPAA, SSAE annual attestation, FedRAMP, FISMA, U.S.–EU Safe Harbor, CJIS. Enough abbreviations?

What’s equally if not more important, is that Microsoft is absolutely open when CRM is no-worky. Take for example Regulatory Compliance FAQ

Office 365 and Microsoft Dynamics CRM Online do not support the processing, transmitting, or storing of PCI-governed data, such as credit card numbers.

In other words, any solution vendor claiming safe storage of credit card data inside Dynamics CRM, was designing subprime mortgages in their previous corporate lives.

I don’t know about you but I feel safe for my and my customers’ data.

Bonus points to Microsoft for making Australian site prettier than US one and for correctly spelling Centre.

Tip #342: Demystifying pop3/smtp for CRM Online

Having troubles creating server-side synchronization between CRM Online and pop3/smtp system? You are in the right place then.

You may have read in the documentation that GMail and Yahoo are listed as supported pop3/smtp providers for Microsoft Dynamics CRM Online and

Although other POP3/SMTP systems may work with Microsoft Dynamics CRM, those systems were not been tested by Microsoft and are not supported.

Let’s be clear about “not supported“. In this context it means precisely “you will not be able to go past server profile screen as we will reject any pop3/smtp provider that is not GMail or Yahoo.”

Yahoo

Easy one first. Use the following settings:

Incoming Server Location: pop.mail.yahoo.com
Outgoing Server Location: smtp.mail.yahoo.com
Important! In Advanced section change outgoing port to 587

Given the right credentials, you should be all set.

GMail

That was epic. Google tightened up the security belt on gmail to the extent that yours eyes will pop out first before pop3 will start working. Abridged summary (all items are equally important):

Incoming Server Location: pop.gmail.com
Outgoing Server Location: smtp.gmail.com
In Advanced section change outgoing port to 587
In the same section set Minimum Polling Interval in Minutes to 15 or more
Google, most likely, will reject CRM advances to login. Why? Because. Go to https://security.google.com/settings/security/activity and if you see this:

indicate that device is yours.
Follow Google advice to allow less secure apps to access your account. Apparently, CRM Online servers are one of those.
Read and weep, and then follow the instructions. All of them
And the last bit I’d like to reiterate separately because it killed couple hours of my time. Make sure your password strong. I had mine set to all lowercase + some numbers and it just was not enough. Sad part is that neither Google nor CRM Online give you any information to assist with troubleshooting

Now that you have it all working, remember what Google said in one of the documents:

Make sure your mail app isn’t set to check for new email too frequently. If your mail app checks for new messages more than once every 10 minutes, the app’s access to your account could be blocked.

That’s why 15 minutes or more. I hope you saved more by reading this.

Tip #341: Lean and mean UAT

One of the most wildly accepted ways to refresh your test database is to copy/redeploy production database back into the test environment. (If you don’t have a test database, go and receive your Spießrutenlaufen, then come back and create the database; if you can still move, that is).

Common issue with this approach, however, is the size of the production database. Blind copy will take unnecessary (and expensive in case of CRM Online) space that is simply wasted. Unless there is a specific requirement to test some obscure functionality, there is no reason to cling to those email attachments and notes files.

Find the offending entities

The following SQL script will display database tables in the descending order by space occupied.

declare  @tmpTableSizes as table 
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert @tmpTableSizes
EXEC sp_MSforeachtable 
   @command1="EXEC sp_spaceused '?'"

select  * from @tmpTableSizes order by 
   cast(LEFT(reservedSize, LEN(reservedSize) - 4) 
      as int) desc

It’ll produce something like this:
SQL tables & space occupied

Delete unwanted records

Now that you identified the offending entities, take approach documented in the administrator’s guide and use it as a baseline. For example, you’d want to expand definitions of the emails with attachments (method 4) to cover not only Completed status reason but also Sent and Received. And don’t hesitate to specify different time conditions or maximum attachment size to reduce database size even further.

This approach will work equally well on-premises as well as in CRM Online. To get the space occupied by your data per table/entity basis, simply raise a support request – and you’ll have this information for your CRM organization in no time.

Tip #340: Sky is the limit for the workflows

From time to time I come across posts referring to CRM Online limits and citing 200 (active) workflows as a limit to be aware of.

Let me use 1000-word equivalent:
Unlimited workflows in CRM Online
What you see here is the user interface lagging behind the product capabilities. As far as I know, limit for the number of custom entities still stands however it can be increased by placing a support request (and having a good reason handy).

« Previous
1
…
110
111
112
113
114
…
147
Next »

Power Platform
Dynamics CRM

Tip of the Day