CRM 2011 – Will Denali, the new version of SQL Server help ease the pain of creating reports in CRM 2011

This week I have been writing some reports using SSIS and have been tearing my hair out at the slow and painful process using SSIS can be.  Writing reports in CRM 2011 is one area Microsoft really need to improve.

I have read on a few blog posts that in the next release of SQL Server which is codenamed Denali might improve this so I tried to find some information on it and I have found a bit of information but nothing to get my hopes up.  The good news is Denali is meant to be released in 2011 and you can see from the table below, which I found here

Sql Server historical releases

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

  • 1993 – SQL Server 4.21 for Windows NT
  • 1995 – SQL Server 6.0, codenamed SQL95
  • 1996 – SQL Server 6.5, codenamed Hydra
  • 1999 – SQL Server 7.0, codenamed Sphinx
  • 1999 – SQL Server 7.0 OLAP, codenamed Plato
  • 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
  • 2003 – SQL Server 2000 64-bit, codenamed Liberty
  • 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
  • 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
  • 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
  • Next – SQL Server 2011, Codenamed Denali
I did finally find a blog with top 9 features in Denali, you can read the whole blog post here but these features did give me some hope

3. Juneau

Juneau is the code name for the new SQL Server Development Tools. Its purpose is to provide a single development environment for all database related project types including bringing BIDS (Business Intelligence Development Studio) and SSMS (SQL Server Management Studio) into the same IDE.

4. Apollo

Apollo is the codename for Column-based Query Accelerator. I have to admit that this is the feature I’m most excited about. I watched some impressive demos showing how much performance gain is realized on very large result set queries. Microsoft claims an approximate 10 times increase in query performance.

Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. Thewhitepaper on the new Columnstore Index points out the following benefits:

  • only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
  • it’s easier to compress the data due to the redundancy of data within a column, and
  • buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.

5. Crescent

This cool new tool aimed at the business user is actually still yet to come. Project “Crescent” furthers Business Intelligence for everyone by providing a powerful and speedy data visualization tool in the browser. It has a feature called storyboarding which allows the user to create multiple snapshots of the data in order to tell a story about that data. Once the snapshots are created, the storyboard can be exported to PowerPoint where the data is displayed live inside the PowerPoint slides. The demos of this product are a must-see. Below are two links. The last is the keynote from Day 1 of the PASS Summit (by Ted Kummert, Microsoft Sr. Vice President, Business Platform Division) and contains a lot more than just Project Crescent.

Data Visualization Done Right: Project Crescent

PASS Summit 2010 Day One Live Streaming Keynote

so at the end of the article I am starting to feel a bit more hopeful but because I haven’t heard anything about the release of a new version of SQL server then it does dampen my enthusiasm a bit.  Please Microsoft help ease the pain of many CRM developers by improving the reporting in CRM 2011.

CRM 2011 – Getting the ServerUrl in Javascript and using Xrm.page

Whilst I was developing I had hard coded the serverUrl which I then used whilst doing an oData query.

So when I moved the code from the Dev system to production for the first time, my trusty Javascript stopped working.  I know you shouldn’t hard code stuff like this and I had only done it in development to get oData working.

The quick answer for those of you who have Googled your way to my blog, is you use the Xrm.page.context and use the method getServerUrl();  This will return you the url, including the organisation.  After that all you have to do is paste the oData address.

var serverUrl = Xrm.Page.context.getServerUrl();
var GlobalODataPath = serverUrl + "/XRMServices/2011/OrganizationData.svc";

this is the code I used to do the oData call
function retrieveUserRecord(Id) {

var select = "/SystemUserSet?$select=InternalEMailAddress&$filter=SystemUserId eq guid'" + Id + "'";

    ///SystemUserSet?(guid'4d82f1fc-262e-e011-9645-00155d106b02')
    showMessage("retrieveUserRecord function START");
    var retrieveUserReq = new XMLHttpRequest();
    retrieveUserReq.open("GET", GlobalODataPath + select, true);
    retrieveUserReq.setRequestHeader("Accept", "application/json");
    retrieveUserReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    retrieveUserReq.onreadystatechange = function () {
        retrieveUserReqCallBack(this);
    };
    retrieveUserReq.send();
    // showMessage("retrieveAccountRecord function END.");
    showMessage("retrieveAccountRecord function END.");
}

function retrieveUserReqCallBack(retrieveUserReq) {
    if (retrieveUserReq.readyState == 4 /* complete */) {
        if (retrieveUserReq.status == 200) {
            //Success
            var retrievedUser = JSON.parse(retrieveUserReq.responseText).d;
            showMessage("ACTION: Retrieved email address = \"" + retrievedUser.results[0].InternalEMailAddress);
            setValuesIFrameQueryString(retrievedUser.results[0].InternalEMailAddress);
        }
        else {
            //Failure
            showMessage("retrieveAccountReqCallBack function failure END");

        }
    }

}

Although the initial change from CRM 4 to CRM 2011 Javascript has a steep learning curve, once you are get used to it the CRM 2011 is I think better than CRM 4.  One of the cool new features is the Xrm.page object.  This is a object which Microsoft generate and fill with lots of lovely variables and methods.

It has three useful parts to it.  What I really like is the separation Microsoft has done here, it’s logical and easy to use.

Xrm.Page.context
Xrm.Page.context provides methods to retrieve information specific to an organization, a user, or parameters that were passed to the form in a query string
Xrm.Page.data.entity
Xrm.Page.data provides an entity object that provides collections and methods to manage data within the entity form
Xrm.Page.ui
Xrm.Page.ui provides collections and methods to manage the user interface of the form.
Out of the the three the one I use the most is Xrm.page and it has some great methods to get default/organisational information from.
  • getAuthenticationHeader: Returns the encoded SOAP header necessary to use Microsoft Dynamics CRM 4.0 Web service calls usingJScript.
  • getCurrentTheme Returns a string representing the current Microsoft Office Outlook theme chosen by the user.
  • getOrgLcid: Returns the LCID value that represents the Microsoft Dynamics CRM Language Pack that is the base language for the organization.
  • getOrgUniqueName: Returns the unique text value of the organizations name.
  • getQueryStringParameters: Returns an array of key value pairs representing the query string arguments that were passed to the page.
  • getServerUrl: Returns the base server URL. When a user is working offline with the Microsoft Dynamics CRM for Microsoft Office Outlook client, the URL is to the local Microsoft Dynamics CRM Web services.
  • getUserId: Returns GUID value of the SystemUser.id value for the current user.
  • getUserLcid: Returns the LCID value that represents the Microsoft Dynamics CRM Language Pack that is the user selected as their preferred language.
  • getUserRoles: Returns an array of strings representing the GUID values of each of the security roles that the user is associated with.
  • isOutlookClient: Returns a Boolean value indicating if the user is using the Microsoft Dynamics CRM for Microsoft Office Outlook client.
  • isOutlookOnline: Returns a Boolean value indicating whether the user is connected to the Microsoft Dynamics CRM server while using the Microsoft Dynamics CRM for Microsoft Office Outlook with Offline Access client. When this function returns false, the user is working offline without a connection to the server. They are interacting with an instance of Microsoft Dynamics CRM running on their local computer.
  • prependOrgName: Prepends the organization name to the specified path.