Monday, July 11, 2011

MS BI Architecture Design Layers - classifying layer specific logic

I'm reading: MS BI Architecture Design Layers - classifying layer specific logicTweet this !
Any architecture design diagram is composed of several layers vertically and horizontally. Horizontal layers are discrete logical areas and their association in the diagram describes the way they are connected to each other. Vertical layers run across the entire solution and all these logical areas, which means they are applicable throughout the solution. For example, data repository can be classified as a horizontal layer and auditing can be classified as a vertical layer. This is a very well known fact and most of us would be knowing this very well.

When it comes to implementation, the association of these layers are honored and the solution is developed keeping this layers in view. But this is only in terms of how these layers are associated with each other, i.e. data and process would flow vertically and horizontally as defined in the architecture diagram. One very vital point that many miss is where to deploy your logic. Below are few logic deployment challenges or confusions that most of us would have faced as decision makers at some point of time in our careers:

1) Should logic be stored in .NET App or in DB Stored Procs

2) Should logic be stored in Stored Procs in OLTP DB or in ETL package

3) Should logic be stored in scheduled batch jobs or in ETL driver package

4) Should logic be stored in Stored Proc or SSRS Report

5) Should logic be stored in SSAS MDX Script or Client App

6) Should logic be stored in Dashboard or SSAS Cube

I have seen many genius taking their comfort route to make their jobs easy and jeopardize the future of the solution, just by deploying the logic that belongs to one layer of the architecture into another layer.

Once I had come across a scenario where one genius project manager tried to defend a solution with the argument that as the application was designed as a reporting application, entire logic is stored at the report level. This means SSRS RDLs contained the entire query logic and formatting logic within it. The solution in discussion was developed as a reporting application, and after few months down the line the requirement came up to act as a data source for other systems. As the logic was completely embedded in reports, it was not reusable at all and the solution design fell flat. Looking at embedded SQL in RDLs, any logical developer would ask, what an uncompiled SQL is doing in SSRS report ? SQL belongs to DB inside a SP and formatting the UI of the report is the report specific logic that can be contained in reports.

I have also been evident of scenarios where an application architect is in the driver seat, and the approach pursued it to embed entire logic into .NET code and treat DB as a blackbox to pump-in and pump-out data. In any corporate IT systems history you would find that application layer i.e. the User Interface / Web Front End layers are changed like changing the curtains of your windows, but corporate DB are hardly changed and whenever DBs are migrated they are a result of a large scale corporate IT systems revamping exercise.

I do not intend to hint that all logic should reside in the DB. Whatever logic that belongs to a particular architecture design layer, it should be deployed in that layer only, which is one of the implicit communication of layering in an architecture design diagram. Entire functionality can be achieved by deploying code in a single layer of the solution, but in the long term it would defeat the very purpose of layering and design patterns. Dissecting the right part of the logic in the right layer, followed by best practices of developing the layer would provide the most ideal solution from a stabilized solution design perspective.

In the field of technical architecture design, my career experience has been that each piece of logic should remain with it's deserving layer. Feel free to prove me wrong !!

Sunday, July 10, 2011

Evaluate MS BI Architecture Design

I'm reading: Evaluate MS BI Architecture DesignTweet this !
Architecture Design of any solution in general is done by a solution architect or a group of architects like App Architect, Data Architect, Infra Architect etc and this group is guided by a solution architect. Design is one part of the SDLC but other part is reviewing the design. Design review is generally a very debatable matter, and almost every time there is a design review, one can expect a debate on right and wrong. The reason for the same is none of the sides have a defined parametrized process, against which one can assess the design. And as every design as well as design review is carried out on one's gut feeling and historical career experience / organizational design templates, design review ends up just as a formality than as a productive exercise.

There are two ways to deal with this issue.

1) Architecture TradeOff Analysis Method (ATAM) : It's an ideal solution where architectures are designed in an ideal way. If you live in an ideal world, the SEI Architecture Tradeoff Analysis Method (ATAM) is the leading method in the area of software architecture evaluation. An evaluation using the ATAM typically would require a trained evaluation team, architects, and representatives of the architecture's various stakeholders. You can read more about this from here. This kind of review would also prepare you to shield your architecture against any audits like CMM, PCI etc.


2) Custom tailored design review process: The above mentioned design review process is a very standard one from a SEI perspective. If you live in a non-ideal world, many organizations would not be complying to SEI methods, standards and practices. Even those organization that claim to comply, would not have their 100% projects complying to the SEI methods. For projects and organizations of those scale, a very modest complexity of evaluation process is required, where a modest team or even a IT professional that wears many hat can conduct the design review.

For projects and requirements in such orbit, a custom tailored methodology for design as well as review should be defined, and architecture should be based on the defined method. One cannot develop the architecture on X method and review it against Y method. I have posted about one such custom tailored architecture design document template earlier, which can be read from here.

I by no mean intend to signal that one should avoid using ATAM, but if that method is not achievable start with a custom tailored one, which of course would not be a standard one. But once you have the maturity, skills and resources at your disposal, you should try to benchmark your architecture design against SEI standard methodologies, and that evaluation would be the correct design review for your architecture.

Wednesday, July 06, 2011

Data warehouse planning tool

I'm reading: Data warehouse planning toolTweet this !
Data warehouse planning is practiced more as an art than as a science. Art is something that one learns with a gifted skill to pursue the craft and deliver the end product. While science is something that anyone with the correct logical algorithm can apply to create the end product. Consultants step in with their own set of questions and start a psychometric analysis class in the office of a CxO of the company, this is a typical way in which most of the data warehouse planning and assessment starts. The end users or the analysts with the organizations have very little clue of why the questions are being asked and what would they derive from it. If someone from the IT Operations of the enterprise approaches the consulting firm regarding the credibility of the process, consultants would pull out a Data Warehouse Toolkit book and justify their theory, making clients almost perceive that data warehouse planning is an art not a science.

How can data warehouse planning become a science ? If the regular set of processes defined to plan and asses data warehouse engineering are available, and the same processes are built into a tool in the form of a workflow, then it becomes science. Any reasonably experienced data architect / BI analysts can use the tool, understand and fill up the workflow with data points and create an assessment, plan the model and come out with a prototype to evaluate the design of a prospective data warehouse.

In the Microsoft BI world, till date the most easy and popular tool of choice for planning a data warehouse, in my knowledge, has been the data warehouse modeling worksheet available from Kimball's Data Warehouse Toolkit book. This worksheet helps at the requirements gathering and modeling level, but not much at the planning level.


The motivation of this post is a new tool that has hit the BI market, and the name is WhereScape 3D. Presently as of the draft of this post, this tool is available as a free trial beta. I gave a try to this tool, and it seems quite of the modeling flavor. In my personal opinion, the tool is not that self explanatory even for a BI Analyst to start using it in a fast track manner, it would require the analyst to learn the tool for a day or two and then start capturing requirements and planning for the warehouse. The striking features is the documentation part, and its one of the unique tools I have come across till date that help the user in modeling the requirements itself to plan the DW right from source systems to data mart. You can read more about this tool from
here and download the same from here.

At this time, I am not sure too much about how effective is the tool, but I am pretty impressed by the value it aims to bring to the table and that is indeed one area that has not been targeted by BI product vendors till date. I am sure this is the start of a new race where vendors would start converting enterprise class DW to science, rather than restricting it as art !!

Sunday, July 03, 2011

SSRS Reportviewer webpart / control REPLACEMENT / ALTERNATIVE

I'm reading: SSRS Reportviewer webpart / control REPLACEMENT / ALTERNATIVETweet this !
SSRS Reportviewer webpart in Sharepoint and SSRS Reportviewer control in .NET are two major application interfaces to SSRS from the microsoft application world. UI limitations and lack of programability of reportviewer control continues to remains a challenge for the application world, as this interface visibly floats out as an odd component from the theme of application.

One of the elegant mechanism that most application environments are resorting today, is an alternative to using this control. And this alternative is by using the SSRS webservice directly. In my whitepaper "Bulk report generation using SSIS and SSRS 2008 R2", I have demonstrated how one can use SSRS webservices, which is a classic example of how much control client applications can gain by consuming these services directly.

How can applications benefit by using SSRS webservices directly and replace SSRS Reportviewer webpart / control ? The technique to implement is as below:

1) Execute your report by calling correct functions from the webservice
2) Collect the output stream in HTML format
3) Display the output on the application page.

This sounds very straightforward, but it is not free of challenges.

1) Firstly one would need to create a custom control, and then embed this functionality within this control.

2) Secondly, report output can contain images too which would be sent by the output stream without any image extension. Displaying the same would not be straightforward. You might need to stage those images to disk, or modify the IIS settings for your application. Code sample in SSRS SDK for PHP shows a fine example of how to deal with this issue.

3) You would not have parameters, page navigation or report exporting functionality, and you would have to program the same yourself too.

But if you have a die-hard requirement to blend SSRS reports with the user interface of your application, instead of abandoning SSRS or duplicating entire report functionality within your Sharepoint / .NET application just due to the limitations of reportviewer webpart / control, a better way out is to replace the reportviewer webpart / control with a custom control, and program and embed your functionality within the same.

I hope someone from Microsoft is reading this post and taking a note of this burning requirement from the application programming world.

Tuesday, June 28, 2011

Trellis Chart Analysis using Analyzer

I'm reading: Trellis Chart Analysis using AnalyzerTweet this !
The true power of any report authoring platform lies in its ability to let users analyze data effortlessly and visually, involving the least amount of manual calculation. To support such a level of visual analysis, the main ingredient of the reporting platform is visualizations and infographics. In simple terms, a reporting platform should be very rich in charting and graphing capabilities. This does not necessarily mean that graphs need to be available in 3D form, gradient based colors, animation, or other fancy “eye candy” UI, which may look impressive at first glance but not provide additional information to the business user. By rich features, I mean that right kind of graph authoring capabilities required for various kinds of analysis like outlier analysis, correlation analysis, frequency distribution analysis and others such regular form of analysis which should be supported out-of-box by the reporting platform.

In this recipe we would take a look at how to create a typical trellis chart and analyze data in a sophisticated manner. The beauty of this recipe is the ease of authoring this level of complex graph, without any report programming effort or knowledge. I will intentionally not explain a trellis chart right now, as the intention is to understand how the resultant graph helps in a deeper analysis, which in this case would be a trellis chart. Through the course of discussion, you will develop an understanding of how to develop trellis chart compared to a regular chart.

Using the AdventureWorks cube, I have created a very regular column chart, that displays the Reseller Sales Amount measure, for the Year attribute of date dimension sliced by the Country attribute of Geography dimension. As you can see in the below screenshot, many of you will notice that missing data for some years is not represented for some countries.



To analyze the graph in a better way, many would want to rotate the axes, and have the Year attribute on the Y-axis and slice by Countries. Ideally, reporting is mostly done on a reporting period like a financial year or calendar year. So after rotating the axis, we would get the below kind of graph. Rotating the axis is as easy as clicking a menu option in Analyzer for the Quick Chart component. After rotating the axis, the chart looks like the below screenshot:


Now comes the real challenge with the analysis part. If you look carefully, the first limitation with the chart is that one does not have data of the same country side by side for analysis. This analysis is done only in the mind of the user. If we rotate axis, we would get data of the same country side by side, but at the cost of not having the reporting period in sequence, which makes the chart further hard to analyze. From the above chart, as the data is of a modest size, this analysis might still be feasible from the chart, but in the case of a larger quantitative analysis, the problem would worsen.

The solution to this problem is to have a pivotal view of data, with the representation of data still in a chart-based format, which is known as trellis chart. Achieving this functionality is just a matter of clicks using Analyzer. You can edit the settings of the chart to display all the series in different charts.


After selecting “Draw each series in separate charts” setting for the chart, the chart would take the shape as shown in the below screenshot.


Using this trellis chart representation, you can analyze data for the country individually in each chart for all the reporting periods, as well as compare data of one country with another country for each year or across the entire period just by analyzing the size of the bars, as the scale of each chart is exactly the same.

After this analysis, one might find that some countries performed quite low compared to others, but as the scale is too high, exact value of the reseller sales amount made in each year is not visible. For example, if you look at the chart of Germany you would be hardly able to make out the sales in CY 2007 and CY 2008. This is also a challenge with a regular column chart, that you cannot have individual scales for each countries / reporting periods, as the entire data is clubbed in one chart. But with the trellis chart we should have this flexibility, and as expected, Analyzer provides report authors with this flexibility too. If you edit the settings of the chart, and uncheck “Unified Scales” option as shown in the above screenshot, you would get the chart as shown in the below screenshot, which solves this issue.


With scales adjusted individually for each chart, you can analyze data for each country individually, and when you want to compare data across charts, it’s just a matter of unifying the scales.

Such report authoring would generally take two different reports involving programming efforts with many reporting platforms. But with Analyzer, once you are familiar with the report authoring interface, it’s just a matter of clicks. You can try out the evaluation version of Analyzer, to learn about more such amazing report authoring capabilities.
Related Posts with Thumbnails