Pogo69's Blog

March 28, 2012

CRM SSRS Reports, SQL CLR and External Data – A Case Study

Filed under: .NET, C#, CRM, Cutting Code, SQL Server, Transact SQL — pogo69 [Pat Janes] @ 14:21

I met some interesting requirements recently, wherein I needed to produce a CRM Report that joined standard CRM Data using the FilteredEntityName views with data provided by a 3rd party dll.

Given the complexities of the resultant solution, I thought it would make for an interesting Case Study.  Hopefully, others can learn something from my experiences and perhaps broaden their minds to the possibilities of CRM Integration.

Architectural Overview

A picture speaks a thousand words, so I’ll shutup for a moment and:

In summary:

  1. My SSRS Report (uploaded to CRM) renders data in a single table provided by;
  2. An SQL query that joins data retrieved from;
  3. A CRM Database FilteredEntity View and;
  4. A Table Valued Function wrapping an;
  5. SQL-CLR Table Valued Function that retrieves the required data from;
  6. A Web Service that calculates Fatigue ratings using;
  7. A 3rd-party 32-bit DLL

Now, if that seems a little bit convoluted, keep reading for the reasoning behind this spider-web of data manipulation and retrieval.

I will start backwards, as the final piece of the puzzle is actually the beginning of the story.

How (and Why) It All Works

32-bit DLLs and CRM 2011

In contrast to CRM 4.0 that preceded it, CRM 2011 is now available only as a 64-bit installation.  This means that if you ever have a need to access 32-bit DLLs (legacy code, 3rd-party functionality, pretty much every ODBC driver ever written) you are unable to do so unless you write a “thunking” layer; providing access to the 32-bit functionality in a separate memory space in order to maintain compatibility.

I chose to wrap my 32-bit 3rd-party DLL in a WCF Service.  I have exposed several endpoints, including a basicHttpBinding that allows interoperability with older SOAP based clients – this is necessary for interaction from an SQL-CLR object as the requisite assemblies are not available to create a more modern WCF client.

As touched upon briefly above, I have implemented the same sort of idea to interoperate with ODBC drivers for legacy database applications that provide no other interface.

P/Invoke – The Murky World of Managed/Unmanaged Interoperability

I will just briefly mention a tool called the ‘P/Invoke Interop Assistant’ that I found invaluable when building a C# wrapper for the above mentioned 32-bit DLL.  It was written in C and exposed only a legacy C based interface that cannot be easily consumed by managed code; which is where P/Invoke comes in.

By adding your legacy 32-bit DLL to your managed project and marking it as content, it will be copied to your output folder.  You must then create DllImport wrappers for any functions that you wish to access e.g. (names changed to protect the innocent:

[DllImport("xxxx.DLL", EntryPoint="_xxx_yyyy@nn")]
 internal static extern int xxx_yyyy(
 int p1,
 ref int p2,
 int p3,
 ref int p3,
 ref int p4,
 ref int p5,
 ref double p6,
 ref double p7,
 int p8,
 ref int p9,
 ref double p10
);

I won’t go into the nightmare that was trying to figure out exactly what that signature should look like, nor the corresponding nightmare that was figuring out how to properly talk to it.  However, I can say that the P/Invoke Interop Assistant made life somewhat easier.

SQL-CLR Table-Valued Functions are awesome!

I got so excited about SQL-CLR when we first came in contact with each other.  In fact, I was all over a whole stack of new “toys” that became available with SQL Server 2005 (I will mention another, Common Table Expressions or CTEs a little later).

SQL-CLR allows you to write a managed assembly (.NET code) that can perform tasks not ordinarily available via Transact SQL and access that functionality via a Stored Procedure or Function wrapper.  My first attempt at such an assembly was to expose some simple .NET Regular Expression pattern matching/replacing functionality, which became invaluable (and surprisingly fast) for a data cleansing project on which I was working.

In this case, however, I wrote my first SQL-CLR Table Valued function; which does exactly what it says – returns data from a CLR managed assembly that is represented and consumed in Transact SQL as a Table.

So; what does an SQL-CLR Table Valued function look like?  I’m glad you asked.  I will let MSDN explain in details, but basically, you have to implement two methods:

  1. One method does that actual data retrieval/collection/however you obtain the data – this method must return the interface System.Collections.IEnumerable (or something that implements same) and will be marked with metadata describing various SQL related properties and the name of the method that will be used to “feed” the data back to SQL Server
  2. The second method does the feeding – taking a generic Object (which are individual elements of the datatype returned from the first method), ripping it apart, and populating out parameters for each column in the resultset.  This allows SQL Server to stream the data back to the query that calls your assembly
Example as follows:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using Microsoft.SqlServer.Server;
using BMP.FAID.SqlClr.WebServiceWrapper;
public partial class CalculateFatigue
{
 [SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.None, TableDefinition = "Start datetime, Fatigue float", IsDeterministic = true, IsPrecise = false)]
 public static System.Collections.IEnumerable InitMethod(SqlXml rosterPeriods, string faidServiceUrl)
 {
  return WebService.Process(rosterPeriods.Value, faidServiceUrl);
 }

 public static void FillRow(Object obj, out SqlDateTime start, out SqlDouble fatigue)
 {
  HourlyFatigue hourBlock = (HourlyFatigue)obj;
  start = hourBlock.Start;
  fatigue = hourBlock.Fatigue;
 };
}

NB: I implemented my Web Service Wrapper in another assembly so that I could unit test it outside of SQL Server.

As per the in-depth explanation of SQL-CLR Table Valued functions, I will leave the details of the deployment of these managed assemblies to your MSDN searching capabilities – in fact, I think I may have gone through the process in another blog posting.

The Transact SQL Query

Apologies for the verbosity, but there are several elements of the query that I would like to explain for those who are not familiar with the concepts within.

--declarations and setup (hard-coded for demonstrative purposes)
declare
 @pilotId uniqueidentifier = 'A4D1BAF3-BC41-E111-B9ED-00012E244939',
 @year int = 2012,
 @month int = 2;

declare
 @monthBegin datetime = (select cast(@year as nvarchar) + '-' + cast(@month as nvarchar) + '-01');
declare
 @monthEnd datetime = DATEADD(SECOND,-1,DATEADD(Month,1,@monthBegin));

declare
 @daysInMonth int = (select DAY(@monthEnd));

declare
 @faidServiceUrl nvarchar(255) = (select top 1 mrcrm_faidserviceurl from Filteredmrcrm_rosterandavailabilitysettings);

create table #HourBlock
(
 [Start] [datetime] NULL,
 [Fatigue] [float] NULL
);

-- find roster items
declare
 @rosterPeriods xml =
 (
  select
   rsi.mrcrm_Start, rsi.mrcrm_End, rt.mrcrm_RiskLevel
  from
   mrcrm_rosterscheduleitem rsi
  join
   mrcrm_rostertype rt on rt.mrcrm_rostertypeId = rsi.mrcrm_RosterTypeId
  where
   rsi.mrcrm_EmployeeId = @pilotId
  and
   rt.mrcrm_RiskLevel > 0
  and
   rsi.mrcrm_Start <= @monthEnd
  and
   rsi.mrcrm_End >= DATEADD(DAY, -14, @monthBegin)
  and
   rsi.statecode = 0
  order by
   rsi.mrcrm_Start
  for xml path('period'), root('roster')
 );

-- use faid service to calculate hourly fatigue
insert
 #HourBlock
select
 Start, Fatigue
from
 [BMP_Functions].[dbo].[InitMethod](@rosterPeriods, @faidServiceUrl);

;with
-- obtain list of all hours in the day
Hour_CTE (Hour)
as
(
 select 0

 union all

 select Hour + 1 from Hour_CTE where Hour < 23
),
-- obtain list of all days in the month
Day_CTE (Day)
as
(
 select @monthBegin

 union all

 select DATEADD(Day, 1, Day) from Day_CTE where Day < @monthEnd
)
-- join it all up together
select
 Day_CTE.Day,
 Hour_CTE.Hour,
 ISNULL(h.Fatigue, 0.0) as Fatigue
from
 Hour_CTE
cross join
 Day_CTE
left join
 #HourBlock h on DATEPART(Hour, h.Start) = Hour_CTE.Hour and Day(h.Start) = Day(Day_CTE.Day);

Again; apologies for the verbosity but it is as simple as I could make it while still demonstrating what I had hoped (yes, it is more complicated than that).  Now, to break it down.

Configuration Data

I store system-wide configuration data in a “Settings” entity instance in the CRM.  This allows me to set variables such as Web Service URLs centrally and access them from anywhere that has access to CRM Data.  CRM components access the Fatigue Web Service that I show here in exactly the same way via plugins etc.

declare
 @faidServiceUrl nvarchar(255) = (select top 1 mrcrm_faidserviceurl from Filteredmrcrm_rosterandavailabilitysettings);

Table Inputs into SQL-CLR Functions (are not allowed) – ‘FOR XML PATH’

In order to calculate Fatigue ratings, the 3rd-party component I was using requires a “table” of input data.  Unfortunately, although you can output a table of data from an SQL-CLR function, you cannot input such data.  So I decided to convert my input data into XML and feed it into my managed assembly function via an SQL XML datatype.

This is another of the “new for SQL Server 2005” Transact SQL enhancements that I was very excited about upon first use.  While the “FOR XML …” syntax was available prior to SQL Server 2005, the introduction of “FOR XML PATH…” made the structured formatting of XML data so much cleaner.

-- find roster items
declare
 @rosterPeriods xml =
 (
  select
   rsi.mrcrm_Start, rsi.mrcrm_End, rt.mrcrm_RiskLevel
  from
   mrcrm_rosterscheduleitem rsi
  join
   mrcrm_rostertype rt on rt.mrcrm_rostertypeId = rsi.mrcrm_RosterTypeId
  where
   rsi.mrcrm_EmployeeId = @pilotId
  and
   rt.mrcrm_RiskLevel > 0
  and
   rsi.mrcrm_Start <= @monthEnd
  and
   rsi.mrcrm_End >= DATEADD(DAY, -14, @monthBegin)
  and
   rsi.statecode = 0
  order by
   rsi.mrcrm_Start
  for xml path('period'), root('roster')
 );

This query outputs data in the following format:

<roster>
 <period>
  <mrcrm_Start>2012-02-06T12:00:00</mrcrm_Start>
  <mrcrm_End>2012-02-06T13:30:00</mrcrm_End>
  <mrcrm_RiskLevel>3</mrcrm_RiskLevel>
 </period>
</roster>

Such data can then be processed in C# using the usual System.Xml provided mechanisms.

Using the SQL-CLR Table Valued Function

Using the SQL-CLR Table Valued Function is as simple as referencing its “wrapper” user-defined function, passing in any required parameters (in this case, the XML data and web service URL) and selecting from the resultant table.

-- use faid service to calculate hourly fatigue
insert
 #HourBlock
select
 Start, Fatigue
from
 [BMP_Functions].[dbo].[InitMethod](@rosterPeriods, @faidServiceUrl);

Common Table Expressions or CTEs – Outputting the Data

Also newly introduced in SQL Server 2005, is the concept of Common Table Expressions.  CTEs provide the equivalent of an inline function, which can be very handy when building reports for Dynamics CRM given that user-defined elements such as custom stored procedures and user-defined functions are disallowed within the CRM database.

NB: in the case of my SQL-CLR function, I stored the managed assemblies and function in a separate database.

In addition to providing a mechanism for an “inline” function, CTEs allow the definition of a recursive function as per my Hour_CTE and Day_CTE below; in this case, I am building a list of:

  1. Each Hour of the Day (0-23)
  2. Each Day in the Month (1-however many days are in the current month)
Using the results of the CTEs and SQL-CLR function, the results are output via a select statement – CROSS JOINS are used to ensure that a value is produced for each combination of Day and Hour.
;with
-- obtain list of all hours in the day
Hour_CTE (Hour)
as
(
 select 0

 union all

 select Hour + 1 from Hour_CTE where Hour < 23
),
-- obtain list of all days in the month
Day_CTE (Day)
as
(
 select @monthBegin

 union all

 select DATEADD(Day, 1, Day) from Day_CTE where Day < @monthEnd
)
-- join it all up together
select
 Day_CTE.Day,
 Hour_CTE.Hour,
 ISNULL(h.Fatigue, 0.0) as Fatigue
from
 Hour_CTE
cross join
 Day_CTE
left join
 #HourBlock h on DATEPART(Hour, h.Start) = Hour_CTE.Hour and Day(h.Start) = Day(Day_CTE.Day);

SSRS and SQL-CLR Functions – A Difficulty

One final word on my experiences with this process.

There is a well-known issue with SSRS Reports, wherein the rendering of large quantities of data can cause excess memory consumption and the resultant failure of a Report to complete.  I encountered this issue, however the supposed cause of such issues did not apply to me – 25 columns and 31 rows of data (in the Production Report) does not constitute a large quantity of data.  Nor does the single, lone table rendering that data constitute a complex visual display.

While I cannot prove causality, I was able to overcome the memory consumption issue, by moving my table and query defined above into a Sub-Report.

My hypothesis is that there is something in my query that could not deal with another DataSet having been defined in the same report (I was using other DataSets to provide data for the Year, Month and Pilot parameters).  This hypothesis is supported by the subsequent removal of the additional DataSets and hard-coding the parameters, allowing the successful rendering of the Report.

I asked for assistance in the SSRS MSDN Support Forums, but was unable to elicit any useful information.  When time permits, I will follow up with this new discovery (I have only just stumbled upon the Sub-Report workaround) and see if it sparks any further ideas.

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: