Extending SSRS Data Sources

Article
05/16/2007

Introduction

SQL Server Reporting Services comes out of the box with a good few data sources. Microsoft SQL Server and Microsoft Analysis Services are the main ones I use, and you can pretty much connect to any other database product using OLEDB/ODBC. What can you do when you want to display in reporting services that does not come from a database ?

There are two common methods used to provide data extensibility with reporting services:

Method

Description

Pros/cons

XML Data Source

The XML data provider flattens the XML structure into a data set that can be accessed by the reporting engine. The flattening and integration with diverse Web services, Web pages, and arbitrary XML documents is powerful, but sometimes confusing.

Pros

Best choice for Web Services

Can model non “flat” data structures

Custom Dot.Net Code can be exposed as web service through ASMX

Does not need rely on SQL Server

Cons

Needs to deploy to an IIS web server. I find this really annoying as I like a report to encapsulate all it needs without bits hanging off.

Not as intuitive to work with as Dataset based sources

custom Data Processing Extension

Dot.Net Library that enables you to bridge a data source and a dot.net dataset.

Modeled after a subset of the .Net Framework data provider but has some extensions such as server side aggregates

One the Extension is deployed and registered in the configuration file it appears in the list of data sources in report designer.

A simplified data access architecture, often with better maintainability and improved performance.

The ability to directly expose extension-specific functionality to consumers.

Best choice for complex extension which will be re-usable. Such as exposing a suite of data from non ODBC compatible source.

Cons

Complex to develop and fiddly to deploy, especially in web farm (deployed to each node)

Not easily compatible with Report Builder.

Rather than pull your data from reporting services you could just use the extensibility features of a supported DataSource to push the data to reporting services. There are two common routes here:

Method

Description

Pros/Cons

Sql CLR / stored procedures

Visual Studio “Database” Project used to write dot.net classes that can be registered in sql server 2005 as first class objects. Stored procedures, Table Values Functions, Scalar Functions are useful here.

The DLL is loaded into the host database via the “create assembly” command and is then hosted within SQL Server.

SQL Server creates one thread pool per user per assembly.

You can also provide COM and external calls using legacy extended stored procedures (xp_*) in SQL Server. I generally avoid this in favour of sqlclr, although xp_cmdshell is still very handy.

Pros

Written in common dot.net language

Best choice for Flat Data

Integrated into T-SQL Query Language

Hides complexity from Report Designer

Data Source can be exposed to Report Builder through a Report Model.

Scripted Deployment via t-sql

Cons

Requires sqlclr to be enabled (disabled by default)

Largely limited to the 12 safe namespaces

By their nature sqlclr functions that access heterogeneous data tend to not be “safe” so some CAS issues when loading into database.

More suited to relational tabular format.

SSAS Stored Procedures

Dot.Net class library aka MDX extensions can be loaded into the “Assemblies” section of Analysis Services using the xmla <ObjectDefinition><Assembly> (See sample CubeInfo.xmla in example code)

These functions can then be exposed within MDX to be used in the same way as calculated measures using XMLA

Pros

Written in common dot.net language

Best choice for integrating with other OLAP data sources using MDX.

Integrated into MDX Query Language

Hides complexity from Report Designer, normal Analysis Services Data Source used.

Deployed to SSAS Server rather than individual database

Scripted deployment through xmla

Cons

Requires Assembly to be registered within Analysis Services.

Data is exposed through MDX, which may be less familiar to report writers than SQL.

The last and I guess most obvious way to get non standard data into a report is to data warehouse it: e.g write an ETL or custom code to transform the data into a relational format where reporting services can suck it up natively. The architectural “pivot point” on this approach is how real time you want the data and if you can feasibly maintain a copy for reporting.

Recently I have been trying quite a seemingly simple task: to display the currently running SSIS packages on a report. It would have been really cool if I could have brought this into SQL Server as sqlclr Table Valued Function so we could have syntax like “Select * FROM RunningDtsPackages(@Server)”, but alas this is not possible as the ManangedDTS namespace is not in the list of allowed external namespaces (even though it is technically part of the SQL product !), assumably this is because it references the windows.forms and many other namespaces itself. I’m working on implementing as a web service to expose XML instead.

Example

This example looks at how to take data only retrievable through dot.net and return in to Reporting Services through an Analysis Services Stored Procedure. The best use case for this is of course data that you want to join to olap data via MDX.

In this case I want to return meat data on an OLAP cube such as: Last Processed Time, Data Source, Server Name, Database Name, Build Number and current user.

This can pretty much be implemented in any one of the methods above, but for real time access to cube meta data it makes sense to use an Analysis Services stored procedure.

CubeInfo SSAS Stored Procedure (vb.net)

Open Visual Studio 2005
Start a New Class Library Project called “CubeInfoMDX”
Add a reference to Microsoft.Analysis.AdoMdServer (msmgdsrv.dll). This is used to use the “Context” object to refer to current analysis services session.
Add a reference to Microsoft.AnalysisServices (Microsoft.AnalysisServices.dll). This is used to create a new AMO session.
Enter code to use AMO to return Last Processed Time

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.AnalysisServices

Imports Microsoft.AnalysisServices.AdomdServer

Namespace ASStoredProcs

Public Class CubeInfo

''' <summary>

''' Returns Last Processed Date

''' </summary>

Public Shared Function GetCubeLastProcessedDate() As DateTime

Dim oServer As New Microsoft.AnalysisServices.Server

Try

oServer.Connect("Data Source=" + Context.CurrentServerID)

Dim dtTemp As DateTime = oServer.Databases.GetByName(Context.CurrentDatabaseName).Cubes.GetByName(Context.CurrentCube.Name).LastProcessed

oServer.Disconnect()

Return dtTemp

Catch ex As Exception

Throw

Finally

oServer.Dispose()

End Try

'//return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606

End Function

End Class

End Namespace

Compile Project
Connect to SQL Server Management Studio\Analysis Server
Right Click on Assemblies and select New
Enter Assembly path with security of “unrestricted”
Test the MDX extension with the following MDX Query
with member [Measures].[LastProcessed]

with member [Measures].[LastProcessed]

as [CubeInfoMDX].GetCubeLastProcessedDate()

select {[Measures].[LastProcessed]

}

on columns

from [Adventure Works]

We Can Now Make Up Report:

Add a new VS Project of Type “Report Server Project Wizard”
Add shared data source called “AdventureWorksMDX”
Click Design Mode in Query Builder and enter your MDX directly. Note that you can use the Graphical Query Designer if you add calculated measures in your cube for the extended measures. However I prefer not to tie the cube to the extensions.
Add your column to report and call Report “CubeInfoMDX”
You can now deploy and use you report.

In My sample project (attached) I've added some more meta data that you might want to bring into MDX using AMO such as: server name, database name, cube name, Data Source Name.

Conclusion

Microsoft SQL Server 2005 Reporting Services offers integration with heterogeneous environments through lots of routes, giving you some nice options for accessing non relational heterogeneous data.

There are some great samples of using Analysis Services Stores Procedure on CodePlex at:

https://www.codeplex.com/ASStoredProcedures