0

Drill Through Options and querying related data sources

What are the options for drill through and querying related data sources in BI Office?

Is there a solution to reading the underlying relational data that is aggregated in the cube? It seems the "drillthrough" option only goes down to the granularity of the cube itself - which may still be missing the grain of the underlying database.

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Samuel, BI Office exposes Analysis Services Drill Through Actions in both multidimensional and tabular models. The granularity is dependent on how the Drill Through Action is defined in either the cube or in Data Modeler.

    Reply Like
  • If you need to go beyond the grain in the cube, you can leverage Reporting Actions.  This allows you to pass the information to SQL Server Reporting Services so you can build whatever you need.  Also, we support Rowset Actions which can query the underlying relational database.  See Microsoft's help file for more information on what is possible with a Rowset action.

    Reply Like
  • You could also provide a link over to, or embed an adjacent SSRS report, to allow to user to find their relevant context. 

    Reply Like
  • SSRS does not handle dynamic cell coordinates well as you need to build parameters for each possible hierarchy. Also, how do you build a dynamic sql statement for row sets? Same issue.
    Reply Like
  • No drill-through options are without their issues.  Anything outside of the standard drill-through becomes more difficult with large data models as it requires the need to pass every hierarchy value.  However, there are creative ways to solve the problem.  In regards to Rowsets, you can create a more dynamic SQL string by concatenating the various CurrentMember values to the query.  That can certainly become tedious if you have a lot of attributes so I would suggest combining it with
    the Analysis Services Stored Procedure Project (ASSP) on CodePlex that allows you to get the full cell coordinates and dynamically construct the SQL.  Here is the link to the project: LINK

    Reply Like
login to reply
Like Follow
  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 620Views
  • 5 Following