Creating A Debugger Visualizer For Dynamic Entities

Simon Hutson wrote this on Tuesday, July 08, 2008 9:12 PM

---

Are You Gonna Be My Girl....

Over the last couple of months I've been spending quite a bit of time with the SDK, building custom workflow activities and plug-ins for CRM 4.0. One of the problems working with the IPlugin interface is that you are forced to use the DynamicEntity class when accessing the InputParameters and OutputParameters properties of the plug-in context.

This wouldn't be so bad, except that it is a real pain trying to find out what properties and values are actually contained within a Dynamic Entity whilst debugging and so I kept having to write the same "test harness" code over and over again just to examine their contents. However, last week I found out you can actually extend Visual Studio to provide this support by implementing a custom debugger visualizer.

Custom debugger visualizers were introduced in Visual Studio 2005 and enable you to view the contents of an object or variable in a meaningful way whilst debugging your application. Out of the box, Visual Studio ships with four standard visualizers, including the "Text Visualizer", "HTML Visualizer", and "XML Visualizer", all of which work on string objects, and the "DataSet Visualizer", which works for DataSet, DataView, and DataTable objects. However, none of these understand the how to work with the complexities of DynamicEntity object.

Fortunately there is plenty of information available online (how to write a visualizer, Creating Debugger Visualizers with Visual Studio 2005), and even a good Channel9 video interview with the Visual Studio program manager responsible for this feature - Scott Nonnenberg - Visualizers in VS 2005. This is well worth watching if you want to really understand how you can improve your debugging experience.

After playing around with this for a few hours I came up with a really cool solution using a TreeView and DataGridView control to provide detailed drill-down into dynamic entities. As you can see below, whenever the Visual Studio comes across an object of type Microsoft.Crm.Sdk.DynamicEntity, the visualizer is represented in the debugger by a magnifying glass icon. When you see the magnifying glass in a DataTip, in a debugger variables window or in the QuickWatch dialog box, you can click on the magnifying glass to select the DynamicEntity Visualizer.

Once launched, the DynamicEntity Visualizer iterates through all the dynamic entity properties to build a tree view on the left side of the form, showing the property name and the property type. Clicking on each property, displays the property attributes in a table on the right side of the form.

At a basic level, a custom debugger visualizer is just a Windows Forms application that is launched by Visual Studio, with the object being debugged passed as a parameter.

The basic code you need to implement the visualizer is shown below. You will need to get a reference to "Microsoft.VisualStudio.DebuggerVisualizer.dll" which ships with Visual Studio, but you do need to make sure you are using the correct version of this assembly depending on whether you are using Visual Studio 2005 or Visual Studio 2008.

Imports System.ComponentModel

Imports System.Windows.Forms

Imports Microsoft.VisualStudio.DebuggerVisualizers

Imports Microsoft.Crm.Sdk

<Assembly: DebuggerVisualizer(GetType(DynamicEntityVisualizer), GetType(VisualizerObjectSource), Target:=GetType(Microsoft.Crm.Sdk.DynamicEntity), Description:="DynamicEntity Visualizer")> 

Public Class DynamicEntityVisualizer

    Inherits DialogDebuggerVisualizer

    Protected Overrides Sub Show(ByVal windowService As IDialogVisualizerService, ByVal objectProvider As IVisualizerObjectProvider)

        Dim de As DynamicEntity = CType(objectProvider.GetObject, DynamicEntity)

        ' Process dynamic entity and display windows form here

    End Sub

End Class

The <Assembly> attribute is used by Visual Studio to associate the Microsoft.Crm.Sdk.DynamicEntity type with the Visualizer, and once compiled, you can simply copy your assembly to the folder "C:\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\". Visual Studio will automatically use any visualizers it finds in this folder.

To build the user interface, simply create a new Windows Form in the project, add a "SplitContainer" control and place a "TreeView" control on the left side of the split and a "DataGridView" control on the right.

Next, create a "DynamicEntityProperty" class that we can use to store property attribute names and values.

Friend Class DynamicEntityProperty

    Private _propertyName As String

    Public Property PropertyName() As String

        Get

            Return _propertyName

        End Get

        Set(ByVal value As String)

            _propertyName = value

        End Set

    End Property

    Private _propertyValue As String

    Public Property PropertyValue() As String

        Get

            Return _propertyValue

        End Get

        Set(ByVal value As String)

            _propertyValue = value

        End Set

    End Property

    Public Sub New(ByVal name As String, ByVal value As String)

        _propertyName = name

        _propertyValue = value

    End Sub

End Class

Next we have to implement the logic that iterates through the dynamic entity and populates the "TreeView" control as shown below.

Private Function AddDynamicEntityToTreeNode(ByVal node As TreeNode, ByVal entity As DynamicEntity) As TreeNode

    For Each [property] In entity.Properties

        Dim childNode As New TreeNode([property].Name + " (" + [property].GetType.Name + ")")

        If TypeOf [property] Is DynamicEntityArrayProperty Then

            For Each childEntity In CType([property], DynamicEntityArrayProperty).Value

                childNode = AddDynamicEntityToTreeNode(childNode, childEntity)

            Next

        Else

            childNode = AddDynamicEntityPropertyToTreeNode(childNode, [property])

        End If

        node.Nodes.Add(childNode)

    Next

    Return node

End Function

Once we have a "TreeView" with one or more "TreeNode" objects, we have to implement the logic that iterates through the attributes for each individual property, and add them to the TreeNode.Tag property as shown below. To keep the code short, and avoid writing the same For...Next code for each CRM property type, I decided to make use of .NET Reflection within the "AddDynamicEntityPropertyValueToTreeNode" function.

Private Function AddDynamicEntityPropertyToTreeNode(ByVal node As TreeNode, ByVal [property] As [Property]) As TreeNode

    If TypeOf [property] Is CrmBooleanProperty Then

        Dim p = CType([property], CrmBooleanProperty)

        Dim value = CType(p.Value, CrmBoolean)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CrmDateTimeProperty Then

        Dim p = CType([property], CrmDateTimeProperty)

        Dim value = CType(p.Value, CrmDateTime)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CrmDecimalProperty Then

        Dim p = CType([property], CrmDecimalProperty)

        Dim value = CType(p.Value, CrmDecimal)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CrmFloatProperty Then

        Dim p = CType([property], CrmFloatProperty)

        Dim value = CType(p.Value, CrmFloat)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CrmMoneyProperty Then

        Dim p = CType([property], CrmMoneyProperty)

        Dim value = CType(p.Value, CrmMoney)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CrmNumberProperty Then

        Dim p = CType([property], CrmNumberProperty)

        Dim value = CType(p.Value, CrmNumber)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is CustomerProperty Then

        Dim p = CType([property], CustomerProperty)

        Dim value = CType(p.Value, Customer)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is EntityNameReferenceProperty Then

        Dim p = CType([property], EntityNameReferenceProperty)

        Dim value = CType(p.Value, EntityNameReference)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is KeyProperty Then

        Dim p = CType([property], KeyProperty)

        Dim value = CType(p.Value, Key)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is LookupProperty Then

        Dim p = CType([property], LookupProperty)

        Dim value = CType(p.Value, Lookup)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is OwnerProperty Then

        Dim p = CType([property], OwnerProperty)

        Dim value = CType(p.Value, Owner)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is PicklistProperty Then

        Dim p = CType([property], PicklistProperty)

        Dim value = CType(p.Value, Picklist)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is StateProperty Then

        Dim p = CType([property], StateProperty)

        Dim value = CType(p.Value, String)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is StatusProperty Then

        Dim p = CType([property], StatusProperty)

        Dim value = CType(p.Value, Status)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is StringProperty Then

        Dim p = CType([property], StringProperty)

        Dim value = CType(p.Value, String)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    ElseIf TypeOf [property] Is UniqueIdentifierProperty Then

        Dim p = CType([property], UniqueIdentifierProperty)

        Dim value = CType(p.Value, UniqueIdentifier)

        node = AddDynamicEntityPropertyValueToTreeNode(node, value)

    Else

        Throw New ArgumentException("Property type is not supported.", "property")

    End If

    Return node

End Function

Private Function AddDynamicEntityPropertyValueToTreeNode(ByVal node As TreeNode, ByVal value As Object) As TreeNode

    ' Create a list of DynamicEntityProperty

    Dim values As New BindingList(Of DynamicEntityProperty)

    ' Check this is not a string or a value type (i.e. this is a reference type)

    If Not (TypeOf value Is String) And Not (Type.GetType(value.GetType.AssemblyQualifiedName).IsValueType) Then

        ' Use reflection to get the public instance properties and their values from each property of DynamicEntity.value

        For Each propertyInfo In Type.GetType(value.GetType.AssemblyQualifiedName).GetProperties(Reflection.BindingFlags.Public Or Reflection.BindingFlags.Instance)

            ' Check that the property can be read.

            If propertyInfo.CanRead Then

                Try

                    values.Add(New DynamicEntityProperty(propertyInfo.Name, GetString(propertyInfo.GetValue(value, Nothing))))

                Catch ex As Exception

                    ' An unknown error occured, so ignore it and do nothing

                End Try

            End If

        Next

    Else

        values.Add(New DynamicEntityProperty("Value", value.ToString))

    End If

    ' Add the list to the TreeNode.Tag property

    node.Tag = values

    Return node

End Function

Private Function GetString(ByVal value As Object) As String

    If value Is Nothing Then

        Return String.Empty

    Else

        If TypeOf value Is Guid Then

            Return CType(value, Guid).ToString

        ElseIf TypeOf value Is Boolean Then

            Return value.ToString

        ElseIf TypeOf value Is Integer Then

            Return value.ToString

        ElseIf TypeOf value Is Date Then

            Return value.ToString

        ElseIf TypeOf value Is String Then

            Return value.ToString

        Else

            Return String.Empty

        End If

    End If

End Function

Finally, the last piece of code is needed to update the "DataGridView" by binding data stored in the "TreeNode.Tag" property when selecting a Dynamic Entity property in the "TreeView". We can also take this opportunity to set some properties on the "DataGridView" in order to make the table format look nice.

Imports System.Windows.Forms

Imports System.ComponentModel

Public Class DynamicEntityVisualizerForm

    Private Sub DynamicEntityTreeView_AfterSelect(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles DynamicEntityTreeView.AfterSelect

        PropertyDataGridView.DataSource = Nothing

        If Not (e.Node.Tag Is Nothing) Then

            PropertyDataGridView.DataSource = CType(e.Node.Tag, BindingList(Of DynamicEntityProperty))

            PropertyDataGridView.Columns.Item(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.None

            PropertyDataGridView.Columns.Item(0).MinimumWidth = 100

            PropertyDataGridView.Columns.Item(0).Name = "Property Name"

            PropertyDataGridView.Columns.Item(1).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

            PropertyDataGridView.Columns.Item(1).MinimumWidth = 100

            PropertyDataGridView.Columns.Item(1).Name = "Property Value"

            PropertyDataGridView.ClearSelection()

        End If

    End Sub

    Private Sub DynamicEntityVisualizerForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        PropertyDataGridView.Enabled = True

        PropertyDataGridView.SelectionMode = DataGridViewSelectionMode.CellSelect

        PropertyDataGridView.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText

        PropertyDataGridView.RowHeadersVisible = False

        PropertyDataGridView.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True

        PropertyDataGridView.RowsDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft

        PropertyDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders

    End Sub

End Class

So there we have it. A really useful piece of functionality that saves time and effort when debugging your plug-ins and custom workflow assemblies. I built this solution in Visual Studio 2008, for debugging in Visual Studio 2008 and you can download the project files here.

If you want to use this "AS IS" you can just copy file "SRH.CRM.VisualStudio.DebuggerVisualizer.dll" from the \bin\debug project folder to the folder "C:\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\". However, if you do wish to modify the functionality, or re-compile it to run under Visual Studio 2005, then please feel free to do so.

This posting is provided "AS IS" with no warranties, and confers no rights.

Laughing Boy

Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM

I.B.I.S. CRM Blog wrote

---

Creating a Dashboard using MOSS, Excel Web Services, Excel 2007 and Microsoft CRM

Creating a dashboard using SharePoint, Excel Pivot Charts and Microsoft CRM data is nothing new. Perform a Google search and you will receive thousands of results. However, finding information on how to create a live dashboard using the latest components of MOSS 2007, Excel Web Services, Excel 2007 and Microsoft CRM data is a bit more difficult.

1) Using the old method available in SharePoint 2003 of inserting the Office Pivot Chart web part and connecting to your CRM data source to retrieve the data does not work in MOSS 2007. The Office Pivot Chart web part is not available in this version using MOSS 2007 and Excel 2007.

2) Setting up Excel Web Services authentication methods (Windows Authentication, Single Sign On, or none) to allow the automatic refresh of the data in the spreadsheets on your dashboard page can be a bit confusing. I will discuss this in more detail later in this post.

3) Certain items used in Excel spreadsheets are not supported in Excel Web Services.

4) There is not one document that explains it all from start to finish, with all of the pitfalls highlighted. That is what I am hoping to accomplish with this post. My initial hurdles in getting this to work were all centered on getting the data to refresh with live data every time the page is opened. I kept receiving a data retrieval error saying the connection was unavailable. This was mainly the combination of the following:

a. Data Authentication

b. Creating the Excel Report based on a Query table, instead of a Pivot Table and Chart

c. Publishing the Excel Chart as a Report instead of a dashboard

This post is written based on the assumption that you have knowledge of MOSS 2007, creating a dashboard page in SharePoint and that the environment has been configured to use Excel Web Services. If not, below are some links that go into detail about how to do this and some other helpful links:

1) Plan external data connections for Excel Services – This article contain full instructions on how to configure MOSS to use Excel Web Services. Note: All of the steps in this article should be completed before moving on to building your reports and your dashboard.

http://technet.microsoft.com/en-us/library/cc262899.aspx#section7

2) Using Analysis Services data in Excel Services – This article goes into depth on configuring your servers to use Kerberos Authentication. This is required if you are using Windows authentication as your method of authenticating your spreadsheets to the data source.

http://www.tonstegeman.com/Blog/Lists/Posts/Post.aspx?List=70640fe5%2D28d9%2D464f%2Db1c9%2D91e07c8f7e47&ID=43

3) Excel Services part 12: Unsupported features

http://blogs.msdn.com/excel/archive/2005/12/01/499206.aspx

Before moving on with the rest of this post, a word on authentication; when creating your Excel spreadsheet and connecting to your CRM database to retrieve data, you have to select a method of authentication. There are three options:

1) Windows Authentication

2) Single Sign On

3) None

All three have their caveats, but the third option of “None” is the easiest to configure and is the one I have chosen to use in this example. The other two options require a much deeper understanding of Kerberos authentication and this will not be addressed in this post. Read the section entitled “Authentication to external data” in the “Plan external data connections for Excel Services” article listed above for a complete explanation on the configuration of each.

As I noted above, you need to configure your MOSS environment prior to actually creating your spreadsheet and building your page. A synopsis of the steps is listed below and is explained in detail in the “Plan external data connections for Excel Services” article:

1) Enable MOSS to use Excel Web Services

2) Add a trusted file location

3) Enable external data access for a trusted file location

4) Configure the unattended account settings – This is required if your authentication method is “None.” In this step, you will want the unattended account to be a domain user that is also a user in CRM. In my case, I just used the administrator login used when we installed CRM.

5) Create a data connection library

6) Add a data connection library to trusted list

7) Set a registry key to surface a data connection library in the client – This step is not required, but if you would like the data connection library to show up as a location to select data connections from when building your spreadsheet and creating a connection, you will have to do this. Otherwise, you can still access the location by typing in the path to the URL when browsing for your data connections.

Once you have completed these steps, you are ready to create your worksheet and integrate it into your website.

1) Create a new connection within a new Excel spreadsheet.

· Open Microsoft Excel 2007.

· Go to the Data tab.

· Select “From Other Sources.”

· Select “From Data Connection Wizard.”

· Select Microsoft SQL Server. Click Next.

· Enter your server name. It is ok to use Windows Authentication here. Click Next.

· Select your CRM database and the view in which to use. In this example, I am going to create a simple graph using the Opportunity View. Click Next.

· Select a file name for your data source that will be saved. We will need to modify the location so that the data connection is stored to the Data Connection Library on the SharePoint site. To do this, click Browse. If you do not have a link to your SharePoint Data Connection Library, you can find this by browsing to your SharePoint site using Internet Explorer and selecting the Data Connection Library link. Copy the location from the Address Bar in IE (All the way through the DataConnections only. Do not include the .aspx ending. Ex. - http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/Data%20Connections/) and paste this before the file name you have selected.

· Select the “Always attempt to use this file to refresh data check box.”

· Click on the Authentication Settings button.

· Select your authentication method. In my case, I select “None.” This will trigger the Excel Web Services to use the Unattended Account we set up earlier.

· Select OK on this dialog and then Finish on the previous dialog.

· When you are prompted with the Web File Properties dialog, select SharePointLibrary as your Connection Type and ReadWrite as your UDC Purpose.

· On the Import Data dialog, select PivotChart and PivotTable Report. Note: Selecting Table will not work. It is called a Query Table and is not an option that is supported in Excel Web Services.

· Build your Pivot Chart. Here I am performing a simple summation of the Estimated Value of my opportunities in a particular category.

· Click on any area within the Pivot Table, click Data from the top menu bar, then Connection Properties.

· Under the usage tab, select all three of the Data Refresh options.

· Under the definition tab, select the “Always use the connection file” checkbox.

· Since we have changed the connection information, we will have to re-save it to the Data Connection Library on the SharePoint site. To do this, click on the Export Connection File button. If your location did not default to the Data Connection Library, browse to it as described in step (h). Click Save to update the Data Connection file.

· Click OK to close the Connection Properties dialog.

· We are now ready to publish the spreadsheet to the Reports Library on the SharePoint site. Click on the Office Button in the top left corner, select Publish, then select Excel Services.

· Before saving the file, make sure you are publishing it to the Reports Library on the SharePoint. As in step (h), if you do not have a link to the Reports Library, you can find it by navigating to the Reports Library on your SharePoint site using Internet Explorer. Copy the address and paste it before the file name. (Ex. http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/ReportsLibrary/)

· The next dialog will prompt you with choices of the items you would like to publish. In my case, I only want to display the chart, so from the Show tab, I select Items in the Workbook and Chart 1. You can choose to show any item in the spreadsheet that is support by Excel Web Services. Select OK.

· The next dialog is important as well. Be sure to select Dashboard Page. If you select Report, whatever you intend to publish is only published as a snapshot and the data will not refresh when revisiting the web page containing your data and charts.

· Your published report will be rendered in Internet Explorer. The next step will be to add the report from your Reports Library to your SharePoint Dashboard page.

Assuming that your base dashboard page has already been built in SharePoint, I am jumping ahead a few steps to actually adding the new Excel Chart to the page.

· From your dashboard page, select Add a Web Part.

· From the dialog box, select the Excel Web Access part.

· After the part is added click on the “Click here to open the tool pane” link.

· In the “Workbook Display” section of the Properties, select the ellipsis to browse your SharePoint Report Library. Select the Excel file that you uploaded earlier.

· Go through all of the Properties areas for the web part, adjusting what is displayed and what is not. Once you are done with the Properties, select OK to add your web part to the dashboard page.

There you have it! To build more reports and data connections, simply follow the steps listed above and then add them to your dashboard page. In this example I used the CRM database as my data source, but in reality this will work for any external data source.

Microsoft Dynamics CRM Mail Merge Templates

Shashi Ranjan wrote on Tuesday, June 10, 2008 8:23 AM

In this blog article I would like to give you more insight into Microsoft Word document that can be uploaded into Microsoft Dynamics CRM as mail merge templates. We will talk about

1. Different word document formats
2. Different versions of word XML formats
3. Controlling version of blank template used in CRM.
4. Upload to CRM via web and outlook client.

When talking about mail merge, we need to be aware of the three types of documents.

     1. Mail merge template document (.DOT or .DOTX/ .DOTM).

- The template document is the one that is used as a basis for creating the main mail merge document.

- On the local client box, the word mail merge templates are typically located inside the folder templates folder (i.e. C:\Program Files\Microsoft Office\Templates\<language>\<type>)

     2. Mail merge main document (.DOC or .DOCX, .DOCM).

- The main document is the template used for generating the individual documents. It contains the link to the data source and the mail merge tags.

- The mail merge engine works on the main document and generate the individual documents.

     3. Mail merge generated document.

- The individual documents are the output documents generated by the mail merge engine.

- These documents can be directed as email, fax or to a single document that can be printed out.

A CRM mail merge document has to be a word XML file. Users can upload both mail merge template document and mail merge main document to CRM as CRM mail merge template files.

To upload the documents into CRM, the user might need to convert the files into their XML representation. Converting a word document into XML file can be easily done by using the “Save as” option in word.

CRM supports both versions of the Microsoft Word XML file format.

1. 2003 Microsoft Word  called wordml
2. 2007 Microsoft Word call the open office format. 

There are issues running document open macros on Word 2007 when template documents are used. Also files like .DOCM and .DOTM contains macros and can not be saved in 2007 word XML format. The new XML format does not allow macros. For these,  the user would need to save the documents in 2003 XML format. So the safest bet is to use and upload the main document of the mail merge in 2003 XML format to CRM.

Currently, when using blank mail merge template in a CRM web mail merge run, we also use a blank 2003 format word document as a base to start with. If you ever want to flip it to use the 2007 format, say if all your users use the new Office 2007 documents then you can do that by replacing the BlankMailMerge.XML file located in the folder “C:\Program Files\Microsoft Dynamics CRM\Server\ApplicationFiles” with another file that is in word 2007 format.

Note: the location of the CRM mail merge button on word 2007 depends on the template format version that was used.

Uploading Templates to CRM

Both outlook client and web clients can be used to upload templates to CRM. If you already have word XML file that needs to be uploaded, you can navigate to the Settings->Templates->Mail Merge template section and create a new or edit an existing mail merge template record and upload the document in the file attachment section. When templates are uploaded via web no processing of the uploaded document happens at upload time.

On the other hand when working inside the CRM outlook client. Users can start mail merge from the grid or the template detail page and then on the final screen of mail merge wizard, click the Upload to CRM button. When uploading the template vi2 to a Microsoft Outlook client, the users field selection, language id, associated entity etc are all automatically set to the settings they started with. Also the uploaded template file is processed to get rid of the data source and the linked query. So if the user opens the template document next time Word warnings about the Data source not found are not triggered.

Cheers,

Shashi Ranjan

Publishing a Newsletter in Microsoft Dynamics CRM

James Matteson wrote on June 03, 2008 8:37 AM

---

One of the marketing issues I’ve run into in Microsoft Dynamics CRM is that of the routine publishing of a newsletter. Sending newsletters to customers and prospects is a common marketing strategy and one that some Microsoft Dynamics CRM marketers have found perplexing. Some of the reasons for this are as follows:

• In Microsoft Dynamics CRM you must ensure that every subscriber has been properly configured to receive your newsletter. You also have to provide the means for them to “opt out” if they don’t want to receive it. This issue is complicated by the Microsoft Dynamics CRM architecture that requires (with good reason) that accounts, leads, and contacts (the folks you want included in your subscriber list) are distinctly maintained. Therefore, you cannot create just one list of subscribers (marketing list), you possibly need three.
• Composing a newsletter in Microsoft Dynamics CRM can also be a challenge. The e-mail templates available out-of-the-box in Microsoft Dynamics CRM are configured to be text-only. While quality text-only newsletters are in distribution, many marketers want to use images and complex formatting. Using images from within Microsoft Dynamics CRM is limited to web-based images. Sometimes marketers can become annoyed that they cannot simply drag and drop images inside Microsoft Dynamics CRM, like they do in Outlook or Word, but have to use images already available or published on the Web. The process for using web-based images is simple, getting used to it is the human factor.
• Also, marketers that have complex formatting requirements must use Microsoft Word or Outlook (or some other compatible tool) for newsletter composition. This means they might need special templates, knowledge of Mail Merge, and should posses some problem-solving skills to overcome any unique issues that arise while composing and distributing their newsletters over a vast range of possible platforms.
• Once all of the above issues are addressed, attention must be given to tracking subscriber responses to the newsletter. We’ve already published several articles about campaign responses and generating metrics around the marketing process. These are also available by drilling down into the new Microsoft Dynamics CRM Resource Center. (Under Areas, select Marketing.) In addition, there are a host of third-party products that could be used to extend functionality in meeting tracking and metric requirements.

I am preparing a new article for Microsoft Dynamics CRM customers that I hope will collect all of the newsletter issues in one place and bring clarity to the detailed process of publishing a business newsletter from Microsoft Dynamics CRM. It’s draft title is 4 things to know about managing e-mail newsletters in Microsoft Dynamics CRM OP or Online. Look for it in the Microsoft Dynamics CRM Resource Center in the next few weeks, I would appreciate any comments you might have to share with me.

James Matteson

Microsoft Dynamics CRM 4.0 Data Migration Performance

Koushik Bhattacharjee wrote on June 02, 2008 8:15 AM

---

This blog post is intended to provide necessary information regarding performance of the Data Migration feature. I will provide some details on the tests and results on a specific hardware environment and some guidance on the deployment side when performing a high volume data migration.

Hardware Configuration:

I have used this hardware set for the data migration tests covered in this blog.

• Domain Controller : Dell Power Edge 2850, 2 Proc Xeon 3.6 GHz, 2 MB L2 Cache, 800 MHz FSB, 4 GB DDR2 RAM
• Application Server : Dell Power Edge 2850, 2 Proc Xeon 3.6 GHz, 2 MB L2 Cache, 800 MHz FSB, 4 GB DDR2 RAM
• SQL Server : Dell Power Edge 2850, 2 Proc Xeon 3.6 GHz, 2 MB L2 Cache, 800 MHz FSB, 4 GB DDR2 RAM
• DMM Machine : HP Compaq dc 7700 SFF Intel Core 2 Duo 2 GHz, 2 MB L2 Cache, 800 MHz FSB, 2 GB DDR2 RAM

Software Configuration: The domain controller, application server and the SQL server were running Windows 2003 Enterprise server sp2. The SQL server was configured to run an instance of SQL server 2005 Enterprise edition sp2. The DMM machine was prepared with Windows XP professional edition sp2 and was running an instance of SQL Server 2005 Standard edition sp2. All the latest hotfixes and updates were applied in the machines.

Workload: The Migration scenario is valid for a fresh installation. The system was having one organization with 200 users. One user (with system administrator role) was dedicated for data migration. There was no static data load or run time load of users on the test system when migration was in progress.

System Settings: No workflow was defined during the course of migration. Duplicate detection was turned off.

Scenarios:

Based on different size and need of the organization, we came up with the following three migration scenarios.

1. Simple: An organization migrates Leads, Contacts, Campaigns and similar entities without any relationship. This is the simplest form of data and does not involve any complex mapping or lookup resolution. DMM intelligently migrates this type of independent data in a parallel fashion and achieves very high throughput.
2. Moderate: An organization migrates records with picklist and lookup fields defined. But there is no complex transformation defined.
3. Complex: An organization using some other CRM, migrates to Microsoft CRM. There are multiple entities with rich interdepending relationships and large number of records. The used map involves picklist, lookup and complex transformations.
4. All these three scenarios are unique in nature and the migration time can be very different with these situations. I am listing the scenarios and the end to end throughput (records per second) that I got while executing these scenarios.

Scenario Complexity	Data Size	Migration Time	End to end throughput (records per second)
Simple (Without any picklist, lookup or complex transformations)	5 million	20 hr	70
Moderate (picklist and lookup transformation but no complex transformation)	1 million	9.2 hr	30
Complex (14 CRM entities with picklist, lookup and complex transformation)	2.5 million	37 hr	19

Let me elaborate more on the complex migration scenario. The migration data was very high volume and spanning over 14 CRM entities with SF like data. The entity wide record distribution is provided in the below table.

Entity	Count	Entity	Count
Account	100000	Opportunity	500000
Contact	200000	OpportunityProduct	500000
Lead	500000	Product	10000
Contract	200000	PriceLevel	10000
Case	100000	ProductPriceLevel	10000
Event	100000	Solution	10000
Task	200000	Campaign	10000

The file sizes for some of the entities were more than 32 MB and hence I had to split them and adjust the out of box SF map. The resultant map was mostly same as the original map, only I need to duplicate the mapping for the entities where I had split the data files.

The above tests show that DMM can handle data of different complexity and can migrate really high volume of data. The migration time and end to end throughput actually varies a lot depending on the complexity of your data. But these results will give you a starting point if you wish to know how much time the migration may take in your deployment.

Tips for high volume Data Migration:

Before you do a migration involving huge number of records, I suggest you to do the following steps.

1. Deploy your CRM server according to your business need and targeted capacity. DMM should be installed in a 2 proc 32 bit machine with at least 4 GB RAM.
2. First do a test migration with very lees records but the same map and same type of data. This will help you to know whether the map is appropriate and you can undo the test migration, by using “Delete Data” option in DMM.
3. If you do not want to automatically customize the CRM server during migration, then uncheck that option during migration. The repercussion is that custom attributes of picklist types will not be populated automatically.
4. Do not migrate data using a virtual machine. Use a physical high end machine to achieve faster migration.
5. Follow the below mentioned optimization techniques, wherever possible.

Optimization Techniques:

1. All the machines in the system must be in the same time zone and use same regional settings.
2. In the Internet Options -> Connections -> LAN Settings, set the physically nearest proxy server and set ‘Bypass proxy server for local addresses’. Do not use the option ‘Automatic Configuration’ as it involves in IL code generation (observed for .NET 2.0 SOAP classes).
3. For best results, make sure that the DMM and CRM server are on a fast LAN connection with no TCP routing hops or HTTP proxies in between.
4. Dynamics CRM 4.0 application has been launched at least once before testing.
5. Make sure that tracing is off in both server and DMM. Tracing might drastically slowdown the application.
6. For best results, CRM server, SQL server and DMM should be in separate boxes.
7. If user has a license for SQL Server 2005, then it is advisable to have DMM database in that SQL server and not in SQL Server 2005 Express edition. SQL Express is not advisable if you intend to do a high volume data migration.
8. Follow the optimization techniques mentioned in the whitepaper Optimizing and Maintaining Microsoft Dynamics CRM 4.0.

Conclusion:

The above mentioned results outline certain aspects of Data Migration performance for Dynamics CRM 4.0. The combination of fast algorithms used in Microsoft Dynamics CRM 4.0 Data Migration feature, along with the new generation Intel Xeon servers results in the high throughput for complex Asynchronous Data Migration jobs.

When reading this blog, user should keep in mind the following points.

• The above results are obtained when only one user is performing Data Migration. The results may not be same if multiple users are performing data migration or other operations are being executed concurrently.
• This test was done in an on premise CRM deployment and the results may not be same for CRM online.
• There is no workflow or plug-in defined during the course of migration.

Any deployment in a similar environment may not provide the exact same numbers, but the provided numbers indicate the pattern of performance numbers for Data Migration. This can be useful in initial planning for migrating your data into CRM using Data Migration Manager.

Koushik Bhattacharjee

Enhancing CRM 4: Workflows vs. Plugins

mitch wrote on June 4th

---

Back in the dark ages, like with CRM 3.0, we were often faced with writing .NET code to provide certain enhanced data manipulation capabilities as either Callouts or Workflow Assemblies.  CRM 4.0 totally changes that mind set - and as a solution developer, you really need to keep that in mind before you jump off into the depths of Visual Studio and the CRM SDK.

An Example

To illustrate my point, let's take the PostInvoicePlugin example from the CRM SDK ( v4.0.5 ).

Note: this example is found in the SDK folder: \sdk\server\fullsample\pluginpostinvoice

The purpose of this sample code is to show you how to update a custom field on the Account record whenever a user closes out an Invoice as Paid.  The plugin checks the state of the Invoice, and if Paid, it will update the value of a custom field on Account, using the following code:

// If the Total Invoiced exists, update it… otherwise set it
if (parentAccount.Properties.Contains("new_totalinvoiced"))
{
    ((CrmMoney)parentAccount.Properties["new_totalinvoiced"]).Value += totalAmount;
}
else
{
    parentAccount.Properties.Add(new CrmMoneyProperty("new_totalinvoiced",

                                 new CrmMoney(totalAmount)));
}

Pretty cool, huh?

 

An Alternative

If you haven't spent much time reviewing CRM 4.0 workflows, you should.  You can perform some amazing things without ever writing code.  Let's perform the exact same steps using CRM 4.0 workflow.

First, we create a workflow based on the Invoice Entity that has the following properties:

Step 1:

Create a Check Condition using the following information:

Step 2:

Add and Update Record step, on the Related Entity Customer (Account):

Step 3:

Click the Set Properties button, then set the Total Invoiced Attribute to the following:

 

Note: Total Invoiced is a custom Money Field.

By using the Increment by Operator, we are able to increment any existing Total Invoiced amount by the amount found in the Invoice's Total Amount field.

Final Step:

Save and Publish this workflow.

Conclusion:

So, is there a difference between these techniques?  Not really.  Both accomplish the job in a very similar manner but the Workflow solution requires zero code maintenance and a normal user or administrator can create the workflow.

The biggest difference between the two techniques is in the timing of the actual update. Plugins can be executed either Synchronously or Asynchronously.   Workflows function in an asynchronous manner.

The Synchronous operation will modify the data stream as it is being saved to the database, which can introduce a delay in the user's experience, but will provide results back to the user in a quicker fashion.

Asynchronous operations will happen shortly after the data has been saved which will not impact the user, but which may result in a slight delay between the time the user saved the record and when the value will be updated.

If you can live with the time delay, I feel that workflow is the way to go, in most cases.  If you can't, then download the SDK and start working through their examples.

CRM Customer Video