Trellis Chart Analysis using Analyzer

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.

Planning visually appealing dashboard using MS BI

The notion of developing something that is very visually appealing is so much ingrained into the minds of business stakeholders and professionals, that whatever technology that provides fancy, animating, and colorful visuals, they develop an image that its the technology of choice to develop visually appealing dashboards. There are several points to consider, while planning to develop a visually appealing dashboard. Below are few points which are very important to consider when you plan to develop a visually appealing dashboard using MS BI and Sharepoint BI.

1) Visually appealing is a subjective measurement, so the design should be developed keeping the end users in mind. There is a difference between a visually appealing website design and visually appealing dashboard design. Keep in mind that websites might be meant to capture data whereas dashboards always report data.

2) Consider controlling the level of fanciness on your dashboard, as the intention of dashboard is to make the user concentrate on data and facilitate the user to visually and effortlessly analyze data. Too much fanciness like animations, too much colors, etc would take distract users attention from data and might even confuse the user.

3) You might have seen MS BI VPC and got impressed with the visuals of Excel Services, Performancepoint Services and other elements. But do not get carried away but just what you see. Consider studying the inventory of features that these technologies provide and map it against your requirements.

4) Do not base your POCs or analysis based on easy data sources like XML files, Tabular data or Sharepoint lists. Mostly data flows from an OLAP model into dashboards (at least in tactical or strategic flavors).

5) Consider studying the architecture of the technology before selecting it for your visually appealing wonder. You might create a POC and find that data from one spreadsheet displays data well with excel web parts and when you need context-sensitive and user role and privilege specific data, the model might fall apart.

6) In the dashboard parlance, visually appealing would mean analyzing data interactively like drilling, slicing-dicing etc and other form of visually appealing would be data analysis using visualizations and info-graphics.

You do not hire a fashion designer to design the interiors of your home, to make it pleasing to the eyes. In the same way, theory of making a visually appealing website cannot be applied to develop a visually appealing dashboard.

What to use for reporting - .NET or SSRS : Application Development Framework / Report Server ?

One quality that has always helped me in my career till date is that when I come across something technical that I do not know / understand, instantly a huge amount of curiosity gets created in my mind. This has been a virtue / trait since the time I was in my academics. Due to this curiosity, our mind generates lots of questions, and this eventually makes us learn a lot as we try to find answer to those questions. With growing maturity and experience in the industry, we start taking many fundamental level things as granted and ignoring things that we do not know or understand. Our cup of mind becomes so full with the acquired knowledge, that we do not agree to empty this cup for a while to draw another cup of knowledge into the bucket of our brain. For example DBAs would want to ignore BI, BI folks would ignore Application Design Patterns, Application folks would ignore databases, still most professionals want to be a Solution Architect !! One thing that I myself struggle after approximately a journey of a decade in the IT industry, is how to sustain myself to continue thinking like a fresher or even a child who is full of curiosity and keep learning more and more everyday. One fresh member in my team asked me this question about reporting, and her curiosity made me author this post.

Though a very simple question, but the rationale behind this can be very interesting and debatable. In my opinion, you firstly need to figure out if you just intend to display data on a screen or you intend to report data. Though it might sound similar, there is a difference. If you just intend to display data, and surrounding it there would be GUI where user can manipulate data, application frameworks like .NET are best suited. And a huge .NET community would already favor using .NET for reporting any data. The main interest would be in why we should NOT use .NET for reporting data ? Though not a complete list, below are a few reasons in my viewpoint for the same:

1) SSRS Charts, Graphs, Gauges is a very unique capability that is not available with almost any other microsoft tools, apart from the ones like Excel. Visualizations are the ideal platform for reporting data, if the intention of reporting is analysis. Does .NET provide equally rich graphing / charting capabilities?

2) Using .NET one would have to hook into SMTP APIs and program to simulate the functionality of subscriptions, which is readily available with SSRS server.

3) From an architecture design perspective, if you want to follow a component model, you decouple report design from application UI as both are two different elements. It the same philosophy with which .NET came with web pages (.aspx) and code-behind files (.vb / .cs) to decouple UI and application logic. In the similar way report logic needs to be hosted and managed in its own layers.

4) SSRS reports introduces a thorough architecture that can be easily integrated and extended. You can collect SSRS reports output and still display in Sharepoint / .NET, and keep your report server sufficiently decoupled.

5) Finally, just give a thought to how would you simulate functionality of snapshots and other kind of reporting available in SSRS? You might be able to re-invent the wheel, but is it really worth the effort?

Load balanced BI Solution - Storage and deployment strategy

In a MS BI platform based solution, Sharepoint it mostly used as the deployment platform for web-applications, reports and other artifacts of the solution and it's a very standard practice. But this remains as simple as it sounds only till you have one server per technology / hosting platform, for eg Application Server, Database Server, Report Server, Analytics Server, ETL Server etc.

As soon as you start scaling your solution infrastructure, like load balanced federated server solution, implicitly your solution gets replicated across servers. There are standard practices to keep the codebase synchronized, but the caution area that is not visible implicitly is that Sharepoint itself can act as a data storage medium for a variety of content. In fact Sharepoint is recognized as a Content Management System. You can store SSRS reports, Performancepoint artifacts, Excel Services worksheets, Visio diagrams and much more on sharepoint. If you are not well informed of the internals of how sharepoint stores data, you would end up replicating tons of these artifacts on different servers.

The key piece to this issue is a one piece of information - every sharepoint site has a content database and that database is a SQL Server based database. Application can store data in sharepoint in sharepoints lists and other objects, and BI solution artifacts are also stored on sharepoint sites. When you replicate your sites to different servers in a load balanced environment, all sites should refer to a common content database, and configuration should be done accordingly for all the sites.

This strategy gives a huge benefit, the prime ones being centralized deployment and bullet proof shield against data integrity issues. Anything stored on one site is automatically available on all other sites, avoiding any synchronization between sites replicated on different servers. Also you deploy artifacts on one site, it would get stored in shared content database and effectively the same becomes available on all the replicated sites.

Dimensional Modeling, Cube Design, Dashboard Design, ETL Design, Reports Design etc are popular BI areas, but solution design is the ultimate expectation from an Architect. Professionals can shy away by taking refuge under their strength area like Data Architect, Application Architect, BI Architect, Integration Architect etc, but someone would have to assume role of driving these genius and this class of architect is known as Solution Architect.

Planning integration of source systems in your data warehousing solution

Any ETL designer would think of integration with source systems, with ideas like, ETL would connect using X driver, X database, stage to X server, load to X destination etc. But an architect cannot take anything as provided and would have to validate even the basic questions. Some of this basic questions are listed below, and when you start figuring out answers to these questions, you would feel a need to start planning the integration from an infrastructure and integration perspective, even before ETL designers can think of the feasibility of an ETL solution.

1) Where are the source systems hosted, on a physical cluster or a VMWare based server in a virtualization environment ? Are load balancers placed on these servers ? You would require to collect Virtual IPs to connect to those servers and keep in mind how your ETL solution would read data from these servers.

2) What is the classification of data in source systems and is this in sync with the zone in which your ETL server is hosted ? Generally in any data center based hosting or enterprise class IT environments, source systems / data is classified as RESTRICTED / UNRESTRICTED / CONFIDENTIAL etc, and different data traversing policies revolve around this classification. Data can generally be exchanged only within systems in the same zone of data classification.

3) Is your connectivity uni-directional or bi-directional ? Many systems just allow uni-directional connectivity from other applications to the database server. So database server might be able to connect to other system, but other systems would not be able to connect back to the DB server i.e. connectivity can be initiated only from DB server and network policy itself would impose this restriction.

4) What is the type of connectivity allowed with the source system database server - Windows integrated or SQL based ? Mostly enterprise class IT environments allow only windows integrated connectivity, as policies can be enforced on windows id.

5) Where is your scheduling server located ? Generally application specific scheduling agents are not allowed in any enterprise class IT environment. You need to locate your scheduling batch server, and check whether you would be able to trigger execution from that server, or you would need an agent installed on your DB server and trigger execution from DB server. If your ETL execution is triggered from a scheduling batch server, connectivity needs to be available to source and destination servers from the batch server. And if execution is going to be triggered from your DB server, connectivity to source system should be available from this server.

Unless and until your solution environment has no governing policies, and you have complete discretion and governance over source and destination systems, and your solution is just hosted on application specific servers and not in any data center based hosting environments, you need to plan your integration at a much deeper level. I hope the above points would provide a few hints in this direction.