Database Reference
In-Depth Information
Reporting Services have a lot of other capabilities such as displaying information in
charts and on maps. All the functionality can be combined with Analysis Services.
One thing that you need to be aware of, is that the queries that Reporting Services
automatically creates may not be optimal from a performance standpoint.
An example of this is when you add an entire hierarchy to the report; in our example,
the query that is created when adding the Sales Amount measure together with the
product hierarchy is the following:
SELECT NON EMPTY { [Measures].[Sales Amount] }
ON COLUMNS,
NON EMPTY { ([Product].[Product
Hierarchy].[Product].ALLMEMBERS ) } DIMENSION
PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works DW2012] CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
What this code does, is that it selects the information from the lowest level in the
product hierarchy. If you start adding other dimensions it will create cross joins
between the dimensions. This may not be the most performing way of writing your
queries. If you start adding a lot of dimensions you will soon query every single cell
in the cube which is very costly.
Note
The way around the bad performance you will get when querying all the levels is
to create your own drilldown in Reporting Services using subreports or paramet-
erized reports; a good description on how to do this found in the following link:
ht-
Another way that you can increase the performance of your Reporting Services re-
ports that runs against cubes, is to change the aggregate used in the report. The re-
port that wecreated inthe previous step includes a
SUM
aggregate that will makeRe-
Search WWH ::
Custom Search