SSAS Cube & SSRS Report RRS feed

  • Question

  • Hey There,
    I am new to SSAS. Thus asking few very basic questions?
    1) In our relational database we have 1 table which stores data for 2 groups. We have created a cube based on this table. Now we do not want the 2 groups to see each others data. So should I be creating:
         a) 2 different perspective (please note that both the groups need to have the same dimensions) .
         b) Should I be creating 2 partitions (I guess not, because I think cube partitions are more of performance thing and to the end user its all the same).
         c) Should I be creating 2 different data source views and then have 2 different cubes connecting to their respective data source views.
    2) How do I pass parameters from SSRS (reports) to query the cube? Should MDX query be written in .rdl OR should I be writing a stored procedure to query the cube, call the stored procedure from the report and return a data set to the report?
    3) Though I tried creating a SSRS report to SSAS cube, but the performance is very poor when compared to browsing a cube. How can I achieve the same browsing performance in SSRS?
    Any help or any pointers shall be highly appreciated.
    Thursday, May 21, 2015 8:26 PM


All replies

  • 1. Look at Roles in Analysis Services. Perspectives do not limit data, roles do.

    2. The MDX and parameters will have to be in SSRS report.

    3. It would be hard to diagnosis a performance issue without more detail. Does the SSRS report run on a different server? Where and how are you previewing the cube that runs good?


    TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA

    Thursday, May 21, 2015 8:58 PM
  • Hi dasqlbi,

    1) You want to limit data access for different group of users.

    You need to create roles for different groups of users. You can specify allow members set and denied members set in Dimension Data.

    2) You want to know how to use parameter in for MDX query in SSRS.

    To use parameter in report using SSAS cube as data source, we can specify parameters in the MDX query designer. Since the parameter is processed as text, we need to convert it into set/member so that we can filter the data.

    Create First SSRS Report with SQL Server Analysis Services OLAP Cube
    Developing a SSRS report using a SSAS Data Source

    3) You want to troubleshoot the performance when working on a SSRS report using SSAS cube as data source.

    In Reporting Services, since the MDX designer also connect to Analysis Services Server, it works same way as you browsing in SSMS. Please check if there's any network traffic issue on connection. If it has good performance in MDX query designer, the problem should be related to processing and rendering. Please refer to articles below to do troubleshooting and optimization.

    Troubleshooting Reports: Report Performance
    Reporting Services Performance and Optimization


    Simon Hou
    TechNet Community Support

    Friday, May 22, 2015 8:58 AM