Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Using Reporting Services (SSRS) with SSAS data

Posted by denglishbi on March 1, 2008

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 do not 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 database 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):

sql_config sql_msolap

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))


    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)

    SET @sql_data = ‘INSERT INTO #DataSet (SalesReason, SalesTerritoryGroup, SalesAmount,
        OrderQuantity, TotalProductCost)
    ”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,
    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).


4 Responses to “Using Reporting Services (SSRS) with SSAS data”

  1. Jim said

    I could really use your help using the OLE DB source and the SAS drivers. I have searched all over for a working example of how to configure the connection manager and the OLE DB Source object to work. Currently when I test the connection manager it passes the test, but when I try to get a list of tables from the OLE DB object I get an error:A server error has occurred: ; fn =ydedopn; context =sasej9lib0901.dll; fn ydedopn; context =sasej9lib0901.dll; fn =ydeopn; context =sasej9lib0901.dll.Is there some magic setup that I am not finding, or something odd about having the drivers in one place or another? I have them installed on my workstation…I am totally lost. Your help would be much appreciated.

  2. NarayanaSwamy said

    Hi this is very helpful me

  3. GregC said

    I am impressed with your article. And your fourth solution with the OpenQuery I have adopted, but never saw anyone write about it which left me in some doubt of its quality. To me, this allows me to let SSRS reports work with the relational table I bring back instead of it trying to work with the SSAS connection source (which SSRS does not do well, and forget about trying to customize the MDX in the designer very much without excessively painful hours of figuring what SSRS did not like about it.)

    I much rather make and test my own MDX; then send it back to SSRS as regular flattened SQL SERVER OLEDB recordset to work with. Thanks for this great blog.

    After the initial setup of the LinkServer, are there any big drawbacks about using an OPENQuery? It seems that many of my colleagues first impression is negative when I mention this option for SSRS-SSAS integration.

    • denglishbi said

      I never realized this option was even available until I explored it as well. Not having the environment to easily create and test your queries is kind of a pain, so you have to do that in SSMS ahead of time and then copy them into the stored procedures. You need to watch out for memory issues when using Linked Servers. There have been services packs that have resolved some of these over the years. I did run into issues when the linked server queries would return memory errors. The rest of SQL Server would function without issue, so it appeared that it was just the virtual memory space that was exhausted. The only way to get this back is to restart the SQL Server process.

      The main reason that we used this option at the time was the need to be able to merge datasets together in SSRS. In SSRS 2008 R2 the new Lookup and LookupSet functions provide this functionality, so if I was to explore this today I would most likely avoid linked server. The solution does come in handy though in certain cases when you just need the additional flexibility and I have seen the linked servers used to query the SSAS system tables and DMV information as well.

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

%d bloggers like this: