How do I get non-Table data into SQL Server Reporting Services?

Question

Given: A C# calculation engine that loads an object model, crunches huge amounts of numbers, and saves the results to a couple of gigantic mega-indexed database tables in SQL Server. Those tables provide data to web interfaces, other software modules, and SQL Server Reporting Services 2005 reports.

I managed to make the engine a lot faster in the latest version of the software, fast enough now that it can provide the data on request - sometimes even faster than the time it takes to query the database for the pre-calculated numbers. I am very happy about this.

That breakthrough means that we can generate data on request for the web interfaces and other software modules. But the cache tables cannot die yet, because they're consumed by the SSRS reports (or more specifically, by stored procedures that query the tables and provide the data to SSRS.)

The cache tables are a pain, in much the same way that any cache is a pain in the world of software. Without going into too much detail, they have sync'ing problems, locking problems, etc etc. The software would work so much more nicely if I didn't have to worry about keeping those darned tables up to date.

But how else can I get the data into SSRS? I've done a fair bit of research and nothing looks too promising:

• We could provide the data via a web service and use the SSRS XML DPE. But that looks kind of hideous - am I right that you have to parse your SOAP envelope yourself?! And it doesn't support XPath, but a proprietary XPath-y dialect?? Our report writers know T-SQL, and that's what they're best at.
• Using the SQL CLR to host our API is not desirable - it's a big app and you can't do anything without creating an application object and logging in, etc.
• Using the SQL CLR to contact a web service on the web application - this is the most promising so far (this article was helpful http://www.simple-talk.com/sql/sql-server-2005/practical-sql-server-2005-clr-assemblies/.) Has anybody tried this approach? Does it perform okay, can it provide large data sets? OTOH I'm turned off by the extra setup we would have to do on client DB servers.
• Any other suggestions would be greatly appreciated.

Answer 1

If I understand you correctly, you're building a report on non-SQL data. You're storing the data in tables for the specific purpose of making them reportable.

I can think of two solutions. The first is to extend your C# calculation engine with a reporting part. The Microsoft.Reporting.WinForms and Microsoft.Reporting.WebForms namespaces can be used to build reports on any data source, not just SQL Server. If the end users use your app as a client, you can generate the data and the reports on the fly.

The second is to use the SQL CLR. You can use a CLR stored procedure as the basis for a report (enter "exec mysp" as the datasource.) This CLR procedure is C# code, and could include your calculation engine as a library. This would allow you to generate reports on the fly, while still using the Report Server user interface.

Interesting question and I'm hoping that more knowledgeable people can provide a better answer :)

Answer 2

I've been in a similar situation previously and tried both the SSRS XML data source and the reporting extension that Andomar mentions. My recommendation is to use the SSRS XML data source feature. If the data structure returned by the web service is simple then the XPath is also simple. I also found it easier to debug. Main thing to watch out for is the timeouts.

The good thing with using web services is that they are easily consumed by many different technologies so they can become very handy to have anyway.

Also, it's a personal choice but despite the SQL CLR features I still don't feel comfortable with putting code in the database. By using an XML data source in SSRS there is no need to involve custom CLR code at all.

Answer 3

You could wrap your data in an ADO.NET DataSet and use it as a Reporting Services Data Source..

I've never personally used this, so I can't give you much more information. However, I knew you could do this from a SSRS class I attended. The example the instructor gave me for when you would do this in a "real world" example would be if you need to join data from two different data sources. For example, you would do this if you wanted to correlate data from SQL Server and Oracle together in one report.

I know this isn't what you want to do, but it sounds like it would provide the same layer of abstraction that you need.

Answer 4

Have you considered writing your own custom database driver that sits on top of your calculation engine? You could then point reporting services right at it. This would keep things fast although it may be a complicated task. I've considered it in the past for proprietary data warehouse software I was working on but never got the go ahead to build the driver.

Great question by the way.

Answer 5

Using the SQL CLR to contact a web service on the web application

This is the way I'd recommend - I did this to access SharePoint list data via their web services. If the data schema can be fixed, a SQL CLR TVF might be a good fit and yields the greatest flexibility. If you need to the schema to be determined at execute time, a SQL CLR stored procedure is what you want since it is not bound to a schema.

The key things you'll need is to:

-- enable CLR on the server
sp_configure 'clr_enabled', 1
GO
RECONFIGURE
GO

-- allow your db to execute clr code marked EXTERNAL or UNSAFE
alter database mydb set trustworthy on

then create a VS sql clr project (you don't have to but it help with deploying and debugging it), set the permission level to 'external'. If you use "Add Web Reference" in the VS project, you'll have to turn on the generation of the serialization assembly and CREATE ASSEMBLY to load it after you build/deploy.

This is a little hand-wavy right now -- honest, I've done this -- I'll add some more detail later. I gave a talk on SQL CLR last month. My sample code has GetFibs project that calls a web service (Fibonnaci) which is also included.

Also take a look here: http://footheory.com/blogs/bennie/archive/2006/12/07/invoking-a-web-service-from-a-sqlclr-stored-procedure.aspx and http://www.codeproject.com/KB/database/SQLCLR.aspx?display=Print

Does it perform okay, can it provide large data sets?

Yes, it seems to perform well. With a little extra work, you can build it in a more stream oriented manner where it won't consume memory for the whole set. See how in the Range sample or here.

Answer 6

I would deinitely go down the ADO.NET DPE road. If your data source can provide the neccessary data in real time, then it would make most sense. You will save yourself 1,2 or 3 tiers, which would surely improve the overall performance. And not talk about maintenance of each layes of code. Expose the number crunched data as an ADO.NET dataset and let your reporting services query it directly, is the best in my opinion.

Answer 7

In SQL 2008 you can use a SQL SSIS package as a datasource. Write a SSIS package to execute your calculation engine on the fly and output an in-memory .net dataset (DataReaderDestination). This can then be used for reporting.