SQL Server Business Intelligence: April 2008

Using Reporting Services (SSRS) with SSAS data

Over the past few weeks I have had to work on a project to generate Reporting Services 2005 reports with data from an Analysis Services (SSAS) 2005 database. I decided I would go ahead and blog about some of my discoveries and discuss the different options that are available along with any findings (gotchas). I cannot cover every single option in detail, that would require writing a few chapters in a SSRS book (which I would be more than willing to do), but I will go into some detail into what I experienced.

I basically explored four different ways to reference the SSAS data in SSRS. Two of the ways utilized the Microsoft SQL Server Analysis Services type of data source and the other two used Integration Services (SSIS) 2005 and SQL Server 2005 stored procedures. With using the SSAS data source within SSRS you can either use the new SSAS designer within SSRS and drap-and-drop to create your report and parameters or you can edit the dataset and enter in your own query string. If you go with the SSAS designer you can switch from design mode to see the generated MDX statement by click on the icon in the toolbar (see picture below) and when using this designer you need to keep the Measures in the columns.

Here is a quick walk through of what the SSAS designer looks like within SSRS 2005 in BIDS with the AdventureWorks Report Sample Pack that you can download from CodePlex (this is the Sales Reason Comparisons Report):


Here is what the SSAS designer looks like within the ProductData dataset. You can also add your own calculated measures here if needed (like Profit is shown here).	By clicking the Design Mode button in the toolbar here is the MDX view.	By setting up the Product parameter that you see in the first screenshot this automatically creates the ProductCategory report parameter and ProductList dataset.

Here is a view of the report parameter window.	I added another parameter on the Date Fiscal Time hierarchy and here is the dataset that was created by doing so.	Here is a preview of the report showing the two parameters.

The designer is really nice and a major improvement over SSRS 2000. The other option when using the SSAS data source would be to just enter in your query string by editing the data source, but by doing so you don't get the nice drag-and-drop functionality or the parameter generation. The other thing you doing get is the auto generation of the dataset fields which isn't a straight forward exercise to setup if you are not familiar with XML and the design of the cube structure. Here is a preview of what is setup with the ProductData fields:

If you want to enter in the query string by not using the designer or the MDX window that comes with it I would use the designer initial to generate the field list and then put the MDX generated statement into the query string window and modify it accordingly to get your field list generate for you. The one thing to point out in regards to using SSAS data is that hierarchies get flattened out and you can see that above in the Date Fiscal Year dataset. Another option of getting at SSAS data would be utilizing a Report Model and using Report Builder to create ad-hoc reports, but that is beyond the scope of this blog posting and that also could entail a complete chapter in a book. If you are interested then you can view this in the SQL Server BOL.

Getting on to the other two options with SSIS and SQL Server stored procedures. Using SSIS as a data source for SSRS in BIDS requires you to modify the RSReportDesigner.config and once deployed to the web server a modification to the RSReportServer.config. This is fully documented here. Now within SSIS you will need to setup the Data Flow Task to generate the DataReader Destination which will be used by the SSRS report. To get the data from SSAS in SSIS you can use two different Data Flow Sources, either the DataReader source or the OLE DB source. If you use the DataReader source you will create a ADO.NET connection to use to connect to the SSAS dabase and by doing this you will be able to bypass this annoying message that you get with using the OLE DB source when you try to preview the results of the MDX statement:

If you click 'OK' it will still display your flattened out data as expected with some long column name references which you will want to modify the names of in the output columns so they are more easily readable and can be referenced appropriately in other Data Flow Transformations (like the Derived Column).

Setting up the DataReader source and the OLE DB source is a little different. I won't go into great detail to keep this posting somewhat short, but the two main differences are that the DataReader doesn't have a preview button and it uses DT_NTEXT as the source data type and OLE DB has a preview button and uses DT_WSTR as the data type (which produces the warning message above). The other thing to point out is if you are going to use the OLE DB source you will need to modify your connection string to include the following Data "Format=Tabular;" (without the quotes) otherwise you will get an error message when you try to use this SSIS package in SSRS. Once you get these sources setup and the output columns renamed appropriately then you can add in any additional business requirements and logic needed to produce the data to be used by the DataReader destination. Here is a screenshot where I had a requirement to combine two sets of data from SSAS and inter-mix the output columns from the data and I had to add a third data source eventually to combine that had data based off a different hierarchy that did not align with the first two sets of data. An odd request, but none the less a real world example that needs to be solved (nothing is ever easy). I had to create a nice column header column to unflatten the date hierarchy and establish level and row sorting information to inter-mix the two sets of data to be easily utilized in a SSRS matrix report item.

Once you get this all setup the one thing that you will need to do before you are ready to reference this SSIS package in SSRS is to go into the DataReader destination and actually select the Input columns that you want to Output for SSRS (an easy thing to overlook when setting this all up). The other thing to note here is that the Error Output was setup for the source of the data and this was needed for some reason because I was receiving an error message, which of course I cannot reproduce now to display, but to overcome this error message I added the Error Output and this solved the issue (I used the Trash Destination which you can download from here).Once this is saved you are ready to setup your SSIS data source in SSRS to utilize the SSIS package. Here is a screenshot of how the data source is setup and setting the query string to be the name of the DataReader destination that is in the SSIS package being referenced.

If everything is setup properly and working then your fields will be populated within the dataset. If you need to utilize parameters and pass them to the SSIS package to set variables then a good blog posting to reference is by Russell Christopher here.

Now for the final option that I will be talking about to get at SSAS data is to use SQL Server stored procedures. This was the preferred method that was used since the client wanted to be able to manipulate the data so that it was in a format that would easily be plugged into SSRS (just like the end-result of the SSIS package) and they would be more able to support the stored procedure than the SSIS package. To get this to work you need to perform to setting changes, one in the SQL Server Surface Area Configuration to enable 'Ad Hoc Remote Queries' and the other in SQL Mgmt Studio in Server Objects to the MSOLAP Linked Server provider to 'Allow inprocess'. Here are a couple of screenshots that show these changes (and these do not require you to restart the SQL Service):

Once you have this setup you are ready to create the stored procedures that can be referenced by the SSRS report. Only members of the sysadmin role can utilize the OPENROWSET command, so for security reasons you will most likely end up using the OPENQUERY statement which will require you to setup the Linked Server using the MSOLAP provider. You can then setup the security settings if you are going to map a SQL login to a valid NT domain account that has access to the SSAS database.

Now that this is setup you can generate your OPENQUERY statements. If you need to parameterize these statements at all, which most likely you will, you will need to generate the OPENQUERY statement into a variable, execute the variable statement, and then if any additional modifications are needed to the dataset or if you want to get more intuitive column names populate this into a temp table (table variable or temp table - if table variable then this will need to be included into the statement that you are generating before you execute it). Here is a sample stored procedure going against the Tutorial SSAS database for AdventureWorksDW that is passing in a single Product Category ID to return the dataset for the SSRS report that is using the linked server setup previously:

CREATE PROCEDURE [dbo].[usp_DataSet] (
@ProductCategoryID nvarchar(10))
AS

SET NOCOUNT ON;

DECLARE @sql_data nvarchar(max)

    --define the temporary table for the data
    CREATE TABLE #DataSet (SalesReason varchar(255) NULL,
                            SalesTerritoryGroup varchar(255) NULL,
                            SalesAmount decimal(18,2) NULL,
                            OrderQuantity decimal(18,2) NULL,
                            TotalProductCost decimal(18,2) NULL)

    --statement
    SET @sql_data = 'INSERT INTO #DataSet (SalesReason, SalesTerritoryGroup, SalesAmount,
        OrderQuantity, TotalProductCost)
    SELECT * FROM OPENQUERY(ADVWORKS_SSAS,
    ''SELECT NON EMPTY { [Measures].[Internet Sales-Sales Amount], [Measures].[Internet Sales-Order Quantity],
    [Measures].[Internet Sales-Total Product Cost]} ON COLUMNS,
    NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
        [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) } ON ROWS
    FROM [Analysis Services Tutorial]
    WHERE ([Product].[Category].&[' + @ProductCategoryID + '])'' )'

    --populate the temp table, return nothing if there is an error in MDX
    begin try
        EXEC sp_executesql @sql_data
    end try
    begin catch
    end catch

    --return the dataset
    SELECT SalesReason,
            SalesTerritoryGroup,
            SalesAmount,
            OrderQuantity,
            TotalProductCost
    FROM #DataSet

DROP TABLE #DataSet

Once this is setup then you can use a SQL Server data source in the SSRS report and use the stored procedure type and pass the parameter. I know this was kind of a lengthy posting and I could have gone into much more detail, but I wanted to highlight each option and provide a little insight into each one. This could easily be a few chapters in a book or a white paper to provide more screenshots and to provide more specifics about each option, but this will give you a general idea of what you have available. I hope you enjoyed this and if you have any questions or want more detail feel free to drop me a line (dane@magenic.com).

There were a bunch of screencasts added to the Channel9 site this past week in regards to Microsoft Business Intelligence. If you don't subscribe to an RSS feed to get this information or haven't checked out this site before then I would recommend it to get you up-to-speed. You can download the files for viewing offline or to synch up to your Zune to take on the road with you or you can view them online.

Here is what is available (looks like the second one on the list is not uploaded yet and the third one got uploaded twice):

#00: Platform Overview (25 minutes, 25 seconds)

#01a: Introduction to Microsoft BI (20 minutes, 33 seconds) ??? (not available yet)

#01b: Introduction to SQL Server 2005 Tools and Sample Databases (13 minutes, 16 seconds)

#02a: Developing a Query-Driven E-mail Delivery System (17 minutes, 37 seconds)

#03a: Loading and Integrating Data from Multiple Sources (17 minutes, 13 seconds)

#04a: Identifying and Importing New Customers by Using Fuzzy Lookup (12 minutes, 5 seconds)

#04b: Implementing Slowly Changing Dimensions in the Data Flow (16 minutes, 11 seconds)

#04c: Advanced Package Design (13 minutes, 26 seconds)

#05a: Designing a Basic Report (18 minutes, 10 seconds)

#05b: Adding Interactivity to a Report (13 minutes, 59 seconds)

#06a: Developing a Report Model and Designing a Report Builder Report (15 minutes, 46 seconds)