Data Models (or cubes) as the data source for Reporting Services reports - Considerations

Reporting Services allows us to author reports consuming heterogeneous data sources and one which is optimized for data retrievals; Data Model (or Cube) is one among most common sources. Although data models are optimized for reporting and analysis (of course, data retrieval), we should not assume that SSRS consumes data in optimal way, specifically when forming the query through query designer, hence following guidelines must be considered.

Note that these are not only for improving the performance, they are considered as best practices too.

• Remove NON EMPTY clause if required
  Query Designer in SSRS adds this clause to the query for making sure that no empty records are shown. This compacts the resultset as well as improve the performance. But in reporting, it is supposed to show all records whether the particular measure has a value or not. Example, when showing Sales for Sales Persons by Region, whether a particular Sales Person has Sales for particular Region or not, it is always better show the Sales Person with a 0 value.
   
• Let the model perform aggregation
  Yes, SSRS automatically adds the aggregate function when showing totals. Remember, it always uses  Sum aggregate function and allows you to change to other if required. However this is not the best way for showing aggregates when the source is a Data Model. First, we do not know whether it is Sum or not, Model can use a different aggregate function. Second, the measure cab be semi-additive, hence calculation is depend on dimensions selected. There for it is always better use Aggregate function instead of Sum or other functions.

• Let the model perform sorting
  Applying sorting with expression should be avoided when possible. All attributes in dimensions are always sorted, it can be based on Key, Name or Value columns, hence it is always better to use the sorting added at the Data Model level rather adding them at Reporting Level that uses resources decreasing the performance.
  
• Edit the query in a rich tool
  If need a complex MDX query to be used with the report, always design the query with a rich tool that using Query Designer in the SSRS. It is always better to use either Report Builder query designer or Report Designer query designer.