Tuning MS CRM 2011 Performance

 

Voor Performance verbeteringen op SQL heb ik de afgelopen dagen de volgende resources gebruikt

Voor onze klanten die SQL2012 draaien zijn de volgende Performance Dashboard Report een uitkomst.
Features zoals Missing Indexes zijn een uitkomst… hiervoor is dus geen SQL trace en SQL optimizer meer nodig… zo lijkt het.

Microsoft® SQL Server® 2012 Performance Dashboard Reports
http://www.microsoft.com/en-us/download/details.aspx?id=29063
http://www.mssqltips.com/sqlservertip/2670/install-sql-server-2012-performance-dashboard-reports/

Verder levert een overvollen AsyncOperationBase ook performance problemen, zie hiervoor het volgende artikel.
Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM
http://support.microsoft.com/kb/968520

 

Dan is er nog de kwestie van de PrincipalObjectAccess tabel, die ook kan groeien tot meer dan één miljoen.
Hiervoor is ook een MS supported oplossing beschikbaar.
How to control PrincipalObjectAccess table growth in Microsoft Dynamics CRM 2011
http://support.microsoft.com/kb/2664150

 

En… de http://CRMJOBEDITER.CODEPLEX.COM is onmisbaar voor het correct schedulen van de ReindexAll, Deletion Service etc..

Dus..!

MS CRM 2011 Outlook Client x64 on Windows8

With many thanks to Merlin Schwaiger – Senior CRM Consultant at PowerObjects for writing this post on how to support MS CRM 2011 Outlook Client on Windows and Outlook 2013.

http://crmwizard.blogspot.nl/2011_02_01_archive.html

Long story shortened:

  • The x64 installer for the CRM 2011 Outlook client does not include everything necessary for the client to work.
  • You must download the x64 installer for SQL Compact 3.5.
  • You must upgrade the x64 version of SQL Compact 3.5 to version 3.5.8082.0 with the hotfix.
  • At this point the Outlook client should be able to connect to your CRM 2011 organization.

Load JavaScript WebResource at runtime

Update... added Browser-agnosticity


function LoadWebResource(resource)
{
   var httpRequest = _createXMLHttpRequest();
   httpRequest.open("GET", Xrm.Page.context.getServerUrl() + "/webresources/" + resource, false);
   httpRequest.send(null);
   try {
      eval(httpRequest.responseText);
   }
   catch (e) {
      alert("Error loading " + resource + ":\n" + e.description);
   }
}

// Browser-agnostic factory function
function _createXMLHttpRequest()
{
   if (window.XMLHttpRequest) {
      return new XMLHttpRequest();
   } else if (window.ActiveXObject) {
      return new ActiveXObject('Microsoft.XMLHTTP')
   } else {
      _error("Could not create XMLHttpRequest on this browser");
      return null;
   }
} 


------



function LoadWebResource(resource) {    var orgName = Xrm.Page.context.getOrgUniqueName();    var httpRequest = new ActiveXObject("Msxml2.XMLHTTP");    httpRequest.open("GET", "/" + orgName + "/webresources/" + resource, false);    httpRequest.send(null); try {       eval(httpRequest.responseText); } catch (e) {       alert("Error loading " + resource + ":\n" + e.description);    } }

How to make MS CRM 2011 ADX Studio Customer Portal MultiLingual

I had some issues making the ADX Studio Customer Portal multilingual.
First I tried having a duplicate website with duplicatie site markers and all… this was way too much.

I solved it just a little easier… use as much translations from CRM as possible… since there are a whole load of translations available when you install a language pack.
In this article I use Dutch and English.

First let me show you the markup for a page with a grid view displaying CRM data.

asp:GridView
ID=”GridView1″ runat=”server” CssClass=”cases” AutoGenerateColumns=”False”
>


<Columns>

    <asp:TemplateField
HeaderText=”Study”>

        <HeaderTemplate >

            <asp:Literal
ID=”ltrHeader1″ runat=”server” Text=’<%# GetHeaderText(“cbr_study”) %>‘></asp:Literal>

        </HeaderTemplate>

        <ItemTemplate>

        <asp:HyperLink
ID=”hlUrl” runat=”server” NavigateUrl=’<%# EducationUrl( (Guid)Eval(“cbr_educationid”) ) %>

Text=’<%# Eval(“cbr_study”)%>
/>

        </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField
HeaderText=”Institution”>

        <HeaderTemplate >

            <asp:Literal
ID=”ltrHeader2″ runat=”server” Text=’<%# GetHeaderText(“cbr_institutionid”) %>‘></asp:Literal>

        </HeaderTemplate>

        <ItemTemplate>

            <%#
ContentUtility.FormatCrmEntityReference(Eval(“cbr_institutionid”) as
CrmEntityReference) %>

            <%# Eval(“cbr_institute_not_listed”)%>

        </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField
HeaderText=”Start Date”>

        <HeaderTemplate >

            <asp:Literal
ID=”ltrHeader3″ runat=”server” Text=’<%# GetHeaderText(“cbr_startdate”) %>‘></asp:Literal>

        </HeaderTemplate>

        <ItemTemplate>

            <%# GetLocalDate(Eval(“cbr_startdate”) as
DateTime?) %>

        </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField
HeaderText=”End Date”>

        <HeaderTemplate >

            <asp:Literal
ID=”ltrHeader4″ runat=”server” Text=’<%# GetHeaderText(“cbr_graduationdate”) %>‘></asp:Literal>

        </HeaderTemplate>

        <ItemTemplate>

            <%# GetLocalDate(Eval(“cbr_graduationdate”) as
DateTime?)%>

        </ItemTemplate>

    </asp:TemplateField>

    </Columns>

</asp:GridView>

 

In the code behind of this page, you´ll need the method GetHeaderText… taking the CRM field name as only argument..

In the Session-object I stored MyLanguageCode… holding the 1033 or 1043… whatever the user picked as display language.

 

protected
string GetHeaderText(string crmField)

{

return
ContentUtility.GetLocalizedLabel(XrmContext, crmField, “cbr_education”, (int)Session[“MyLanguageCode”]);

}

 

I add in the ContentUtility class a two methods … GetLocalizedLabel and GetLocalizedPickListValues

 

public
static
string GetLocalizedLabel(XrmServiceContext context, string crmField, string crmEntity, int LanguageCode)

{


RetrieveAttributeRequest CrmAttributeRequest = new
RetrieveAttributeRequest();

CrmAttributeRequest.EntityLogicalName = crmEntity;

CrmAttributeRequest.LogicalName = crmField;

 


RetrieveAttributeResponse response = (RetrieveAttributeResponse)context.Execute(CrmAttributeRequest);

 


if (response.AttributeMetadata.DisplayName.LocalizedLabels.Where(l => l.LanguageCode == LanguageCode).Any() == true)

{


return response.AttributeMetadata.DisplayName.LocalizedLabels.Where(l => l.LanguageCode == LanguageCode).SingleOrDefault().Label;

}

 


// Otherwise return the default label


return response.AttributeMetadata.DisplayName.LocalizedLabels.Single().Label;

}

 

public
static
ListItemCollection GetLocalizedPickListValue(XrmServiceContext context, string crmField, string crmEntity, int LanguageCode)

{


RetrieveAttributeRequest CrmAttributeRequest = new
RetrieveAttributeRequest();

CrmAttributeRequest.EntityLogicalName = crmEntity;

CrmAttributeRequest.LogicalName = crmField;

 


RetrieveAttributeResponse response = (RetrieveAttributeResponse)context.Execute(CrmAttributeRequest);


PicklistAttributeMetadata picklist = (PicklistAttributeMetadata)response.AttributeMetadata;

 


ListItemCollection ddItems = new
ListItemCollection();


string _localizedlabel = string.Empty;

 


foreach (OptionMetadata option in picklist.OptionSet.Options)

{


if (option.Label.LocalizedLabels.Where(l => l.LanguageCode == LanguageCode).Any() == true)

{

_localizedlabel = option.Label.LocalizedLabels.Where(l => l.LanguageCode == LanguageCode).SingleOrDefault().Label;

}


else

{

_localizedlabel = option.Label.LocalizedLabels.Single().Label;

}

 

ddItems.Add(new
ListItem(_localizedlabel, option.Value.ToString()));

}


return ddItems;

}


 

How is this for a cool MS CRM 2011 feature explanation?

I love it when people make blog posts like these
http://blogs.msdn.com/b/crm/archive/2010/10/25/bing-maps-and-microsoft-dynamics-crm-online.aspx

Like this you can learn to use and understand MS CRM 2011 Solutions, Dashboard, JQuery, BingMaps, CRM Discovery Service, usage of REST services, Web Resources,.

Here’s the direct download link of the source code.

All I needed.

DateJS.js and MS CRM

My customer had a wish of calculating estimated revenue based on hour per week, amount per hour and calculated based on business days between start date and the end data.
There was a tiny little detail that caused some thinking… which was that the end data is most cases start date + 3 months… so I needed to calculate the end data.


Problem #1 was the calculation of business days between two dates.
This wasn’t too hard, since Google found me an answer that looked like this:

Solution #1

function calcBusinessDays(dDate1, dDate2) { // input given as Date objects

var iWeeks, iDateDiff, iAdjust = 0;

if (dDate2 < dDate1) return -1; // error code if dates transposed

var iWeekday1 = dDate1.getDay(); // day of week

var iWeekday2 = dDate2.getDay();

iWeekday1 = (iWeekday1 == 0) ? 7 : iWeekday1; // change Sunday from 0 to 7

iWeekday2 = (iWeekday2 == 0) ? 7 : iWeekday2;

if ((iWeekday1 > 5) && (iWeekday2 > 5)) iAdjust = 1; // adjustment if both days on weekend

iWeekday1 = (iWeekday1 > 5) ? 5 : iWeekday1; // only count weekdays

iWeekday2 = (iWeekday2 > 5) ? 5 : iWeekday2;

// calculate difference in weeks (1000mS * 60sec * 60min * 24hrs * 7 days = 604800000)

iWeeks = Math.floor((dDate2.getTime() – dDate1.getTime()) / 604800000)

if (iWeekday1 <= iWeekday2) {

iDateDiff = (iWeeks * 5) + (iWeekday2 – iWeekday1)

} else {

iDateDiff = ((iWeeks + 1) * 5) – (iWeekday1 – iWeekday2)

}

iDateDiff -= iAdjust // take into account both days on weekend

return (iDateDiff + 1); // add 1 because dates are inclusive

}

Problem #2 was the calculation of the end date.
Adding a month is not a function in standard JS… so I tried

Let’s say the start date was December 1st this year, which in CRM looks like this:

Wed Dec 1 10:27:00 UTC+0100 2010 [Object]

If I calculate my end date in the old school fashion JS:

New Date.setDate(startDate.getDate() + 90) 
My date will look like this 
"Wed Dec 1 10:27:00 UTC+0100 201090" [string] 
MS CRM will handle this string, when a field is set to this value. 
However de calcBusinessDays function required a Date Object and not a string. 

Solution #2

http://www.datejs.com/ is the solution here.

Add the Data.js to be loaded in the <CRM Install Folder>\_static\_common\scripts\global.js

document.write(‘<script language=”javascript” src=”\/_customJava\/customDate.js”><\/script>’)

The calculation then looks like this:

crmForm.all.new_expectedrevenue.DataValue = calcBusinessDays(crmForm.all.new_startdate.DataValue, crmForm.all.new_enddate.DataValue.add(3).month()) * crmForm.all.new_rateperhour.DataValue;

The date function in the DateJS library do return Date objects.

Other examples of the DataJS library are below… it’s a very powerful library.

// Get today’s date

Date.today();

// Add 5 days to today

Date.today().add(5).days();

// Get Friday of this week

Date.friday();

// Get March of this year

Date.march();

// Is today Friday?

Date.today().is().friday(); // true|false

// What day is it?

Date.today().getDayName();

// Get the first Monday of the year

Date.january().first().monday()

// Get the last Friday of the year

Date.dec().final().fri()

// Set a date to the 15th of the current month at 4:30 PM,

// then add 90 days and make sure that date is a weekday,

// else move to the next weekday.

var d1 = Date.today()

.set({ day: 15, hour: 16, minute: 30 })

.add({ days: 90 })

if (!d1.isWeekday()) {

d1.next().monday();

}

// Lets start simple. “Today”

Date.parse(‘today’);

// How about tomorrow?

Date.parse(‘tomorrow’);

// July 8?

Date.parse(‘July 8’);

// With a year?

Date.parse(‘July 8th, 2007’);

// And time?

Date.parse(‘July 8th, 2007, 10:30 PM’);

// Get the date, move to Monday (if not already Monday),

// then alert the date to the user in a different format.

var d1 = Date.parse(‘8-Jul-2007’);

if (!d1.is().monday()) {

d1.last().monday();

}

alert(d1.toString(‘dddd, MMMM d, yyyy’));