April 16, 2008 at 9:39 am
One of our developers poses the following question; I've forward to some of my collegues at MS. Also wanted to share w/ the SSC community to see if anyone has any suggestions, alternatives, etc.
Thanks. 🙂
"As a reporting team, we are striving to single source our data for our reports to the OLAP cubes wherever possible so that our reports match the data users may pull using Analytics on said cubes. We have multiple cubes, each serving a specific data need. The structures of our cubes are such that there are multiple date dimensions in several of them. In simple reports dealing with a single cube and single date dimension, it is a trivial job of writing a Reporting Services report using MDX to query the cube directly. However, if we need to query a cube that has multiple date dimensions, where those date dimensions apply to the same measure but in a different way, we are having difficulties. Further, if we need to query multiple cubes, though LOOKUPCUBE() is available, we have found another blocker in the need for aggregation. An illustration may be useful:
The reporting goal is to track a support organization. One cube, [People], is used for tracking support staffing and another cube, [Incidents], is used for tracking support incidents. When tracking incidents, there is a need to track multiple dates, including when an incident was opened and when it was closed. When tracking staffing, we only need a single date dimension for tracking days at work.
If I want queries for open and closed incidents out of just the [Incidents] cube across a non-aggregated date range, I am forced to have separate queries for each of the two date types, date opened and date closed. This makes the job of presentation on the report difficult for the developer.
I can easily query out of [People] and I can use LOOKUPCUBE() to create members using the [Incidents] cube in the same query, replacing the string for the date name in each of the two [Incidents] date dimensions using the name of the single [People] date dimension. This trick can also be used to write a single query for multiple date dimensions in the [Incidents] cube. However, if I need to perform an aggregation in [Incidents], say of the customer regions for the incidents, then I cannot use the [People] cube as the host for [Incidents] queries using LOOKUPCUBE().
I have done a lot of research and, short of redesigning our cubes, the only solution to this issue I’ve found is to place the MDX queries for different cubes and/or date dimensions into some SQL and storing the resulting query results into a temp table which is then used in Reporting Services with ease. Otherwise, I could have dozens of data sets in Reporting Services, one for each MDX query, which could make the job of the report presentation developer extremely difficult. However, I am told that the performance impact of using SQL as a way to pull all of the queries together is very impactful on the SQL server. Is there another solution, common or otherwise?"
Tommy
Follow @sqlscribeMay 1, 2008 at 1:47 pm
Tommy
iT-Workplace is a Microsoft certified ISV partner working with business intelligence reporting. We have recently released a new tool “Intelligencia Query” designed to improve the experience of working with Analysis Services in Reporting Services. This tool comprises a state-of-the-art query builder combined with a custom data extension that removes the main restrictions imposed by the standard Reporting Services environment.
We are currently looking for feedback on this tool. From your blog entries I wonder if you might be interested in taking a look and letting us know what you think?
You can download IQ from http://www.it-workplace.co.uk/IQ.zip
Regards
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply