SQL 2008 in Q3 ’08

Teo Lachev has brought up that a clarification has been made that SQL 2008 will be released in Q3 2008.  The original blog entry can be found here.

I guess this is good news – but I have to say that many of the places I’m working are just getting through the 2000 to 2005 migration.  I know that Microsoft has been hard at work to deliver SQL within the 24-36 month range.  I have to ask myself – is that because they think it is good for their customers or because it is good for their revenue stream?

Can customers keep up with the upgrade cycle? Can technologists keep up with all the new features?

I also think that Microsoft is undermining their certifications program because it is quite an investment to achieve one and before you know it, you have to start all over with the next version.

Have a good weekend.

Consuming the Reporting Services web service inside SSIS

I had a request to go into more detail as to an implementation of the ReportService web service inside SSIS.  I decided I’d take a crack at it.

Taking a snapshot of a report after a successful ETL load is a common task.  You could automate this task into the ETL load by consuming the Reporting Services web service and calling the UpdateReportExecutionSnapshot method and invoking Reporting Service to take a snapshot.  This could then be a catalyst for subsequent subscriptions for users who have asked to be notified when a new version of a report is ready.

First of all, we need to create a proxy object for the service by extracting the WSDL document from the service and using it as an input into a tool that Microsoft provides with Visual Studio called wsdl.exe.  The most straight forward execution for wsdl.exe would look something like this:

wsdl.exe /language:VB /out:ReportService.vb http://%5BReportServerName]/ReportServer/ReportService.asmx?WSDL

OR

wsdl.exe /language:CS /out:ReportService.cs http://%5BReportServerName]/ReportServer/ReportService.asmx?WSDL

You can choose your output language according to your liking, VB or C#.  Complete documentation of all the options for invoking wsdl.exe can be found here.

Once the proxy class’ code has been generated, it is time to get that code imported into an SSIS package.  Go open an SSIS package and place a Script Task into the Control Flow area.  Once that is completed, double click on the new Script Task to open it up.  Next go to the Script “tab” over on the left and then click Design Script… on the right side.  We are now in the code editor for the Script Task.

Next we need to add a new code file into the task.  To do this, go to the top level of the tree view in the Project Explorer (mine is on the left side of the Microsoft Visual Studio for Applications window) and right click.  This will bring up a context sensitive menu.  Choose Add, and Add Class….  When the next dialog box comes up, name the file ReportService.  After clicking the Add button, there should be a new item in your Project Explorer tree view called ReportService.

There will be a little bit of code auto generated by SSIS inside the new file.  We’ll go into the file by double clicking on it and delete out the auto generated code.  Next, go to the file that was created by wsdl.exe and open it up in notepad.  We just need to select all the code in it and paste it into the new file inside the SSIS task we just created.  The code editor should now look something like this:

We need to add a couple of references inside the script task as well.  Right click on References in the Project Explorer and click Add Reference….  Then choose System.XML and System.Web.Services and click OK.  Now we are ready to program against our proxy object.  To begin using the new class we created we need to instantiate it into an object.

You can see here that after I instantiate the object, I’m able to view its methods (known as web methods) using IntelliSense.  For every operation that ReportService.asmx exposes, there are two versions.  One is an asynchronous version and a synchronous version.  The asynchronous version allows you to make the call out to begin the web method call and be alerted when the call is finished by using a callback.  This allows the calling process to keep from blocking its main thread during the call.  These methods work in a pair and have Begin[MethodName] and an End[MethodName] procedure.  The synchronous method blocks the calling thread during execution and is just named [MethodName].  We’ll use the synchronous method here.  You can find more information on using asynchronous web method calls here.

The method we will concentrate on here is the UpdateReportExecutionSnapshot method.  Reports in Reporting Services can be rendered from a snapshot that is taken at an earlier time so that no database query is needed and report response is increased.  One way to take a new snapshot is to declare a schedule and have the snapshot taken at a particular time.  This is sometimes not practical since ETL completion time can vary from day to day.  We can change the properties of a report to render from snapshot and then create a new snapshot after ETL processes are complete.

Before the UpdateReportExecutionSnapshot method will work, security must be set up properly.  You can either change the ReportServer directory to allow anonymous access (not recommended) or apply the credentials of the caller to the web service call.  This is done by using the System.Net.CredentialCache namespace and assigning the Credentials property of our proxy object to be equal to System.Net.CredentialCache.DefaultCredentials.

To make the complete call and create a new snapshot, here is the code:

Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.UpdateReportExecutionSnapshot(“[ReportName]”)

ReportName is a string that describes the full path to the report.  If a report called “Test Report” were in the root or home directory, you would just need a forward slash then the report name – “/Test Report”.  If the same report were in a directory called “foo” then the ReportName string would look like this: “/foo/”Test Report”.

Hopefully this example will help everyone use this method for interacting with Reporting Services at ETL time.

Good Luck!

Passing NULL in a ReportParameter Object to ReportService.asmx

Came across a need to pass NULL to a paremeter in a Reporting Services report through the ReportService web service.  Turns out (thanks Teo Lachev) that if this is what you need, omit the parameter altogether from the parameter collection that is passed into the web service.  Teo mentioned that he figured this out by doing a server trace.  Very clever.  He remembered that Reporting Services is using the ReportService.asmx to render the reports itself and so all he had to do was render the report using the null checkbox in the parameter box and see what it was doing.  Well done.

Mark

Trouble setting up Reporting Services 2000

I was getting this error:

Microsoft SQL Server 2000 Reporting Servies

Setup Error

An error has occurred during setup

An Error has occured while performing the following setup action. Please select help for more information about this error. You may be able to skip this action by selecting ignore, or retry the action by selecting retry. Selecting cancel will end the installation of SQL Server.

SetDialogs

Error Code: 1603

I found a blog entry by Aaron Myers that explained that you must have the user ASPNET on your localmachine.  What a crummy error message.

Thanks Aaron.

No DRILLTHROUGH queries in Reporting Services

I was needing to do an mdx DRILLTHROUGH report in Reporting Services and got this strange error: “Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)”.  Went looking and came across Nick Barclay’s blog entry that talks about how you can’t do mdx DRILLDOWN queries in RS and that Darren Gosbell had written a bug report about it.  Microsoft responded that they decided not to support it since you could easily perform the same query in 2005 w/o using a drilldown statement.

It took me a little while to work out all the syntax of the different queries and thought I’d share it here as well as some performance differences that I found.

First of all, here is the DRILLTHROUGH query that I wrote.

DRILLTHROUGH
SELECT NON EMPTY { [Measures].[Internet Sales Amount]} ON 0
FROM [Adventure Works]
WHERE ( [Date].[Calendar Year].&[2003])
RETURN
     [$Customer].[Customer]
     ,[$Date].[Calendar Year]
     ,[$Product].[Category]
     ,[$Product].[Model Name]
     ,[$Product].[Product]
     ,[$Product].[Product Line]
     ,[$Product].[Subcategory]
     ,[$Promotion].[Promotion]
     ,[$Sales Reason].[Sales Reason]
     ,[$Sales Territory].[Sales Territory Region]
     ,[$Sales Territory].[Sales Territory Group]
     ,[$Ship Date].[Date]
     ,[Internet Sales].[Internet Sales Amount]
     ,[Internet Sales].[Internet Order Quantity]

Here is the equivalent query written in a way that will work in Reporting Services 2005.

SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY {
     [Customer].[Customer].[Customer].ALLMEMBERS *
      [Date].[Calendar].[Date].&[550] : [Date].[Calendar].[Date].&[914] *
      [Product].[Category].[Category].ALLMEMBERS *
      [Product].[Model Name].[Model Name].ALLMEMBERS *
      [Product].[Product].[Product].ALLMEMBERS *
      [Product].[Product Line].[Product Line].ALLMEMBERS *
      [Product].[Subcategory].[Subcategory].ALLMEMBERS *
      [Promotion].[Promotion].[Promotion].ALLMEMBERS *
      [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
      [Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS *
      [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS *
      [Ship Date].[Date].[Date].ALLMEMBERS
} ON ROWS
FROM [Adventure Works]

I have found (very unscientifically) that on my server, there is a significant difference in performance.  The DRILLTHROUGH query ran in 14-16 seconds on average and the CROSSJOIN query ran in 23-25 seconds.  When the queries were smaller and included fewer dimensions and fewer rows because of the WHERE clause, they converged and there wasn’t a significant difference in performance – although on a small query I was finding that the CROSSJOIN query took 3 seconds and the DRILLTHROUGH took 1 second – a 200% difference.

Now I’m not an expert in how the query engine works, but it isn’t a surprise to me that the CROSSJOIN query would be slower than the DRILLTHROUGH query.  Maybe someone out there can detail why the DRILLTHROUGH is faster or if maybe there is another way to write the query to get the same results into RS without using a CROSSJOIN.

This seems to me that we have another instance of the integration of Reporting Services and Analysis Services getting shortshrifted again (read here).  Hopefully Microsoft will see this as an opportunity to improve the integration of OLAP into the Reporting Services product.