Boyan Penev on Microsoft BI

As Reporting Services allows us to implement some quite complex logic, report authors are often tempted to build a lot of it in their reports. Having some simple calculations there does not generally cause harm, as simple + and – operations specific to a report should probably be built in it. However, I have seen aggregations, complex queries and code hacks, which would normally be performed fairly easily in Analysis Services or SQL Server built in Reporting Services.

There are a few distinct benefits we can get from keeping logic out of our reports.

• Firstly, by having our MDX scripts and SQL Queries (stored procedures) in the back-end allows us to use them in different reports with no need to maintain them by painfully synchronizing them with every change. Also, this allows business MDX-ignorant users to use complex measures, increasing the value of the BI solution for them.
• Then, normally, our database server is usually maintained by people who have skills in SQL or MDX, and can analyse and optimise the scripts there instead of relying on report authors to do so. Even if we have BI professionals maintaining the whole solutions, it is far more convenient to have most of the logic stored on the same server. Whether it is a performance bottleneck, or a bug, it is far easier to fix it by tracing either cube calculations or stored procedures than logic stored in a report.
• Sending a large amount of data between the back-end server and SSRS is increasing the stress on the network, which can be critical when multiple requests with millions of rows in results are passed to it.
• Caching is important when different reports use the same stored procedures or MDX calculations.
• And a last point: a database/OLAP server is just faster in calculations that SSRS.

In general, using stored procedures as SQL Server data sources instead of building queries in SSRS; or alternatively, keeping MDX in calculations in the OLAP cubes is the first step towards avoiding excessive code accumulation in SSRS. Then, avoiding anything more complex than simple sums and groupings in the reports and moving them to the stored procedures or MDX calculations is another desirable way to structure our solutions. Finally, even simple things like Actual – Budget variance measure should be built in MDX or SQL rather than in the reports. Following these simple rules and keeping ourselves disciplined when building reporting solutions improves their performance, maintainability and even usability.

SSRS Reporting Services

We often need to build some advanced functionality into our reports and since the Reporting Services query designer does not provide a very advanced way to create MDX statements, our only option is to get our hands dirty and write some code ourselves. One of the things we must consider is the time that the query designer needs to validate our syntax, execute the query and determine what fields to be created based on the data set it receives from Analysis Services.

When we specify a number of query parameters, we have the option to give them default values:

If we do not do this, the query will be executed with the default dimension member instead of the a specific value as a parameter, which can be slow and unnecessary in the usual case where the default member is the (All) member. Instead, we should always specify a value which is to be used for limiting the sample data set. Ideally, is should be small but not empty. If the parameter is too limiting and the MDX query does not return any values the data fields will not be populated.

Specifying default values speeds up development time, especially when using complex cubes, where Reporting Services queries can be slow as its dimension cross joins can be very big.

Chris Webb also blogged in regards to this issue. I strongly recommend you read his post.

SSRS Report Designer, Reporting Services

A small issue with BIDS I just found out about – if you want to modify the visibility expression of a column in Business Intelligence Development Studio 2008 you may find that the Fields list is empty and that Report Designer cannot see a Dataset binding for the column. Regardless of that, if you type in a Fields!<field_name> expression and you get a red curvy line indicating something is wrong with it, you will be able to deploy the report and the visibility of the column will get toggled by the expression. So it actually works but also could be annoying.

Note (2009-02-07): This appears if you have a tablix within a tablix and the outer tablix does not have a dataset associated with it.

SSRS Report Designer, Reporting Services, Visual Studio

In Reporting Services we already have a refresh button:

which refreshes the displayed report data. Users, though, tend to click the View Report button instead and it is sometimes necessary to enforce a data refresh every time a user clicks on View Report.

Normally, the View Report button should be used when a report user changes the report parameters and Reporting Services will try to retrieve data from the data store after clicking it only if some parameter value has changed.

Using this knowledge, we can set up a dummy internal parameter which changes its value constantly. If we want to make sure the report value changes, we can build a datetime parameter with available and current value of Now() – which returns the current date and time and will always be different between clicks on View Report.

A possible issue with this way of enforcing data refresh is that the report cache will never be used, thus possibly causing a performance problem. However, if performance is less desirable than avoiding the need to educate our users this report “hack” may be quite useful.

SSRS Reporting Services

In order to have comments on a report we need to ensure we have a comments storage place (a database table) and an user interface (an asp page). I will skip the explanation of how to build and integrate the asp page in our reports because I have already discussed javascript in Advanced javascript in Reporting Services, while building an asp comments collection page is outside of the scope of this blog and is a fairly simple task for any .NET developer.

Having provided the prerequisites, the way our report will behave is very simple – it will pass to the asp page the location of the node against which the comment is entered, which in turn will call a stored procedure which will update the comments table.

The following graph illustrates this set-up:

Our comments table has to be built after considering the grain of the nodes against which we store comments. Our users may require the ability to comment against fact values on any level of our hierarchies – including aggregations. In order to allow for maximum flexibility we may need to have a table which mimics our fact table structure but instead of facts values stores comments.

Also, we need to build two stored procedures – one to update our comments table and another one to retrieve the comments we want to display on the reports. These are similar in every way but the action they perform. Each of the stored procedures has to accept our dimension coordinates as parameters – most likely the dimension keys – in order to be able to correctly store or locate the table value which it needs to update or select.
There are some other considerations we need to keep in mind:

• Unfortunately, Reporting Services does not allow us to have report items which are dynamically updated, so it is not possible to have the comments to get displayed without refreshing the report data.
• If two report users simultaneously try to comment on the same issue the .NET code must be able to resolve the contention issue caused by the “commentators”.
• Data storage may be a problem as users are usually reluctant to set a limit on the length of the comments.

Adding comments to our reports is simple and easy and should not cause too much headache if it is designed well. It is a very desired functionality on most reports as these are generally shared by many users, who can be responsible for different report items and need to be able to explain the report contents to their peers (especially when forecasting and planning is involved).

Amendment 1 (2008-10-13): In a conversation with a colleague who has recently implemented a comments collection solution, I came across the idea of having the comments stored in a dimension comments table, then mapped to the already described fact table mock-up. The reason for separating the comments through a mapping table is the reduced storage in case the same lengthy comments are used against multiple dimension coordinates (in her project – rolling over comments periodically).

SSRS comments, Reporting Services