Developing different types of reports in SSRS 2008 R2

SSRS 2008 R2 has advanced the reporting functionality to a long way, since the time of 2000. There are many different types of reports that can be created using SSRS and each serves a different functionality, but still each of these can be categorized into a few common groups. Different types of reports that can be created using SSRS are as below:

• Parameterized reports
• Linked reports
• Snapshot reports
• Cached reports
• Ad hoc reports
• Clickthrough reports
• Drilldown reports
• Drillthrough reports
• Subreports

The categories of reports that I see from a development / production aspect are as below:

• Business Users authored reports: Ad hoc reports and Clickthrough reports can be classified in this category of reports.


• Performance Boosters: Cached reports and Snapshot reports can be classified in this category of reports.


• Data / Problem Decomposition: Drill-down and Drill-through reports can be classified in this category of reports. These are probably the reports of maximum value for business users.


• Business As Usual: Parameterized reports are the most common kind of reports, and can be classified in BAU category.


• Reusable / Versioned reports: Subreports and Linked reports can be classified in this category. These are probably the most focused category of reports from a development perspective.

For more detail on the types of reports in SSRS 2008 R2, a very detailed explanation can be read on MSDN from here.

Zip and Email SSRS reports requires use of SSIS - True or False ?

Recently one of my blog reader posted me a question where the requirement is to zip and email SSRS reports to users. Reader wanted to check with me if this can be done using subscriptions and any kind of delivery method. The two main delivery methods available are file-system and email delivery, and to the best of my knowledge, none of them include zipping of files.

When report files are huge in size, to the magnitude of several MBs, emailing those files is quite a big challenge and also it's not an advisable option. In my views, SSRS team should make an option available of zipping the file to at least any one compressed file format, when the file are delivered using file-system or email delivery methods. In my views, these are some of the very common reporting related requirements that SSRS would have to incorporate to gain acceptance as a serious and enterprise class reporting platform.

The order of this process can be as follows:

1) Create report output stream using subscriptions, and the output can be in requested file format.

2) Zip the output stream to any compressed file format, at least .cab format which windows has been using since the times of it's evolution.

3) Deliver the file using file-system / email delivery methods.

As of now, there are no out-of-box options to directly deliver zipped reports to file share output / email the same. SSIS can be used for the same purpose. In the whitepaper that I have authored, I have explained how to generate SSRS reports in bulk using SSIS and SSRS 2008 R2. With little more coding in the Script task, you can zip the created files and email them if required. Two of the articles that demonstrate how to implement the zipping functionality can be read from here and here.

Achieve high availability of cubes using SSAS and SSIS

Recently, I was faced with two different questions at two different events and both of questions were directly or indirectly linked to high availability of cubes for querying. Those two questions were:

1) What is the difference between scale out and scale up?
2) How would use ensure 24 x 7 availability of a cube, considering the point that globally users are accessing the cube, and the cube should always remain available for querying?

The answer to the first question is when you need to achieve parallelism for concurrency in querying or processing, you distribute / replicate processing operations and/or data on multiple nodes. Scale up usually means that you increase the capacity of the host to enable the server to cater the incoming load. When the capacities of scaling up ends, scaling out steps in.

The next question was quite interesting, and the challenge was that I was in a situation to instantly think of a design and answer this query. I answered this question correctly, and to my delight, I found this whitepaper which is exactly what I answered. Such moments bring a lot of happiness and confidence that my knowledge has not gone stale and I can continue to provide consulting in MS BI business.

The presentation layer is coupled with SSAS query server. Data is read from relational engine and cube is processed on a separate server, which can be considered another layer altogether. After the cube is processed, query server and processing server are synchronized. For multi-server synchronization, SSIS is used. The below two diagrams demonstrates the same. Entire whitepaper can be read from here.

MS BI RFP - Key differentiators to stay ahead in the league

Right from the role of an associate architect to the role of an account manager, right from pre-sales to account management, I have evidenced that at every stage, one remains faced with the challenge of providing a key differentiator. This challenge can with be with your competitors or even within different teams competing for excellence. Talking about the Request For Proposals that clients invite from multiple solution providers, there are many factors that act as a differentiator which helps client in their decision making. In my views, RFP is not just science, it's art + science, where you blend a solution such that the client is able to accept, digest and accommodate. Please keep in view that the below points are from the eyes of an architect, and not a typical sales person / project management professional.

1) Robustness, outsourcing, design patterns, certified professionals, company size and more. Sales folks would swear by these words, but in my views these are not the differentiators. These can bring a good level of confidence in the engagement economics of a project, but not in the design of the solution. All reputed solution providers would have more or less same traditional design, same outsourcing pyramid, same quality of staff more or less. I have seen huge multinational clients giving away projects to small companies, considering the philosophy that "It's better to be top valued client of 10th best solution provider, than being 50th valued client of the top ranked solution provider."

2) Outsourcing might provide a cheaper solution development cost, but if the solution design has the effect of raising TCO (Total Cost of Ownership), then clients would definitely shy away from the same. This would work well with support projects, but not in case of a fresh solution design and development. One should always pay attention to the technology roadmaps shared by technology providers like Microsoft, Oracle and others, so that the designed solution is ensured to sustain for an acceptable duration of time with reasonable support.

3) Choice of technology platform and components is very vital. Technology is highly driven by innovation, and if your solution completely lacks innovation, even a weak competitor may nail a big blow with an innovative solution that might appeal the client in a brilliant manner. Cloud and SaaS are two of such areas. When a solution can be provided using MS BI and Amazon EC2, a solution proposed using even SQL Server Standard Edition with the biggest range of outsourcing, would fall flat. An innovative solution using a mix of cloud and in-premise technology components can defy even an extremely low cost traditional in-premise solution, as license costs would be the killer.

4) Fabrication speed of solution design and development can help you quote very aggressive "Time to Market". This can be powered by a very well planned Center Of Excellence practice within an organization. This gives the dual advantage of readiness to deal with any technical challenges and speed of execution. Whenever some client visits a production plant, they are shown the machinery and the production units, as a part of confidence building exercise. Listing out in-house developed components / framework ready to be plugged in the proposed solution can be a very big catalyzer to convince clients of an aggressive time to market proposal.

5) Pluggability. I know there is no such word like this, but what I mean is, how flexible is the solution in terms of to and fro integration with external systems, along with it's reusability. Archiecture should be consciously designed, such that each logical unit can be consumed by any external subscribers with least possible complexity, which is generally buzzed by the term Service Oriented Archiecture.

Different stakeholders of project acquisition and management would have different views to it. But being an architect, one needs to contribute directly or indirectly to pre-sales activities, and the above views can be helpful to save your shoulder from burning, if architecture design is at blame. Solution and architecture might be perfect from a technology perspective, but this is not the perspective from which clients would see at it. There are a lot of factors that contribute to a winning bid, but considering the scope and role of an architect in the RFP, I feel about points would sound fair. If you have more to add to this, please feel free to add your comments or email me to share your views.

Limitations / Disadvantages of using Calculated Measures / Calculated Members in SSAS

In my views, Designing is a process that is driven entirely by impact analysis. However trivial a product / tool / technology feature may be, if it's used without thoroughly analyzing the impact it can have on the overall system / solution that has to be developed, this would mean that you just signed up a guaranteed future roadblock.

Calculated measures / members seem like a very straightforward design feature, and whenever you feel need / shortage of some members / measures, calculated ones seems to be a very easily available option from a development perspective. But there is another side of the same, and these points should be kept in consideration before you make the decision of going with calculated members / measures.

1) Drillthrough does not operate on calculated measures or any other calculations that reference calculated measures / calculated members. This means, for example, if you have created a calculated measure on the cube which the user might opt to use as a part of drillthrough dataset, this means that now you are stuck and you need to find a workaround.

2) Calculated measures cannot be secured using Dimension Security in a straight forward manner, in fact they won't be listed at all in the Dimension tab of the role where we define the Dimension security. Also when security is applied on regular members, and due to the same, if they are not available to calculated members, they would fail i.e. when such measures are browsed in client tools like Excel, the value that would be displayed is an error value like #VALUE.

Based on the above two points, calculated measures / members should be scanned against drillthrough and security requirements, so that a trivial overlook in design doesn't translate into a showstopper issue over the period of time at a later stage.