Dependency analysis for estimating BI solution development efforts

Creating a WBS (Work Breakdown Structure) is the starting point to start listing your tasks for any development efforts. After a list is created, a complexity factor is introduced for each task, and based on that a generalized amount of effort for each category of complexity is allocated and the sum of the same becomes the total tentative effort. Categorization of tasks from simple to most complex is generally classified on the basis of deep understanding of the dependency analysis of these tasks.

Dependency can be of different types like dependency on tools, dependency to use a component, dependency on infra, dependency on platform, dependency on operational processes involving approval cycles, dependency on release environment (Dev/QA/APT/UAT/prod) constraints etc.

This needs to be accounted while assigning a complexity factor to even the most modular level of work item. It might sound a very exaggerated theory from the way it looks. The best way to realize this is by the way of a real life example. One of the examples from my experience that suits this flavor is "Activating excel services on MOSS 2007 and ensuring it's available on the site". It sounds a very simple item and one might not even account more than an hour for the same, and the same was the case on a project on which I was working. The issue chain started as below:

1) Firstly one needs to activate excel services on the farm, which is pretty simple (clicking just an action button).

2) You need to have a site on which you need at least a web part page.

3) You need to have Excel Service enabled for the site and site collections, so that you can use the service on the site and get Excel Web Access web part on your page.

4) When you configure excel web access web part with a workbook uploaded to some document library on your site, you need to ensure that the same should be identified by excel services as secured location. For this you need to configure trusted locations for excel services.

5) To configure excel web services related setting you need to have shared services administration site installed in moss 2007 environment, which is an explicit process done only on demand by application engineering i.e. infrastructure and application management teams.

6) To install this site, you need to have Indexer service enabled. Also you need a web application created that you can use for this shared service provider, and this web application should not be configured to use "Network Service" for the security configuration.

7) You might need excel installed on this environment, as Excel 2003 workbooks many a times creates issues and you get an error on the web part when you try to configure your web part with this workbook. Most organizations would consider installing office only on client machines, and installing the same on server would be considered as a security exception.

From the above example, it's very easy to tell that how much experience goes into dependency analysis and how important is this factor to consider during estimation. If you have similar stories, feel free to share it with me.

Excel Services : Alternative / Replacement for SSRS ( SQL Server Reporting Services ) from a User Experience perspective

SSRS is the reporting backbone in the MS BI stack. SSRS has acquired more grace in terms of visualizations by the dundas license, but there are some areas of SSRS which are highly limited in features from a user experience perspective. Any technology is perceived by the user ( who is the end client funding the technology procurement and use ) based on how well is the usage experience followed by performance, especially when the deliverable out of the technology is the face of the solution i.e. reports in case of SSRS.

In most of the environments, you would find SSRS deployed over Sharepoint. It can be either in sharepoint integrated mode, or there would be SSRS Report viewer web part accessing reports deployed on report server. Some of the serious limitations of SSRS, from the user experience perspective are as follows:

1) Parameter toolbar in SSRS report is non-programmable. Say if you have 12-15 parameters and your report returns just 10 records, it can be the case that the length of report toolbar would be equal or more than the length of the report as the toolbar would always display parameters in two columns.

2) On the report body, once the data is dumped, there is no way to filter this data. For example, in a report where I have 100 records, and I do not want to use pagination. On these 100 records if I want to filter records based on a criteria, those filters needs to be pre decided as would have to be implemented as report parameters. User's do not get flexibility to decide the filter / formula at will.

3) There is very limited amount of interactivity available on the report. For example, once report is generated at client end that is having a graph and a table of records related to it, there is almost no interaction possible on this piece of data. Users would want a what-if analysis on almost all reports where a graphical visualization that reflects some sort of comparison is used.

Considering the above points, the answer from Excel Services can be as follows:

1) Excel Services can accept parameter values from Filter web part, and you can customize this web part to a great extent. SSRS reports can also work on same theory, but programming needs to be done to pass the parameter values to SSRS reports. Even Report viewer web part can accept parameters from filter web part, but the report needs to be designed and configured in that way.

2) Excel web access web part provides all the kind of filtering capabilities that excel provides, and it easily outperforms SSRS report user experience. You can easily filter values on the report for each fields in the report.

3) One of the best part of excel services based report is it facilitates what-if analysis in a very interactive manner. User can key in parameter values which appears in a dockable sidebar, and everything on the report can change in an interactive manner without making server trips.

Whether Excel Services is better or SSRS is better, would remain a debatable topic. But Excel Services definitely wins over SSRS, in the User Experience category.

Use of visualizations for analyzing multi-dimensional data

Visualizations are a very powerful ways of representing complex data. The visualizations that you should choose depends on the kind of data you want to represent and kind of analysis that you want to facilitate on the top of this data. There are a few heavily used visualizations for data analysis, and below is a brief list of the visualizations that I admire the most for analytical data representation.

1) Box plot / Scatter plot charts - These charts are mostly used for outliers analysis.

2) Candlestick charts - For analyzing extremely volatile data like movement of a particular stock during the day, with associated values like high-low-open-close.

3) Line charts / Range charts: For displaying multiple trends on the same graph for trend analysis and correlation analysis.

4) Tree map / Performance map: For portfolio analysis and measuring weighted values of each item within a portfolio.

5) Decomposition Trees: For problem decomposition using drill-down and drill-through techniques in the same visualization.

BI Capacity Planning Tools for Sharepoint 2010 Business Intelligence

Sharepoint 2010 is a fairly new product even as of date, and I keep hearing different stories from my professional network, where organizations are still not having enough maturity on sizing sharepoint environments even for development purposes. In thoroughly process oriented organizations, generally you would find regular server induction teams/groups and exercises to incorporate new/upgrade versions of server technologies/products. Once these teams craft the induction process which includes installation, maintenance and support SOP (standard operating procedures), the same is floated as the technology standard throughout the organization. As per this theory, everything sounds very procedural and under control.

But many a times architects often comes under fire, when they are asked to submit different documents describing application architecture, development environment configuration, capacity and sizing estimates for a technology that has not got inducted within the organization through SOP. Architecture has significantly changed from Sharepoint 2007 to Sharepoint 2010, the primary one being the change from Shared Service Provider (SSP) in 2007 to Service Applications in 2010. Services that can be used/categorized for BI related requirements can be managed in the form of service applications. Examples of such services are Access Database Services, Secure Store Service, Visio Graphics Service, Business Data Connectivity, Excel Calculation Service, and PerformancePoint Service.

This was just the tip of the iceberg, and when you have Sharepoint 2010 as a part of your BI solution, the journey of an architect just starts with selecting services of use followed by capacity planning and related documents for infrastructure and storage teams. In such cases, there are some tools, reference topologies, and architectures that can make your life a bit easier as an architect. Below are link to such resources:

1) HP Sizer for MOSS 2007
2) HP Sizer for MOSS 2010
3) Reference architecture and topologies diagram
4) Reference architecture and topologies reference material

Creating data marts from data warehouse : Architecture Design considerations

In a typical BI architecture, the regular layers that you find in an architecture diagram are source systems, ETLs, data warehouse and reporting layers. But whether to encompass data marts into your architecture is one such design decision that demands some convincing reasons.

Below are a few scenarios when you might want to consider creating data marts in your architecture design.

1) Customization for business units: Different business units of an organization can need their own version, shape and volume of data which would be originating from a set of common source systems. Customization to this degree is not possible at the data warehouse layer, so independent data marts can be created to cater this requirement.

2) Performance Optimization: A single cube / sets of cubes created out of a single data warehouse and sourcing data from the same data warehouse can be real challenge to performance. By creating data marts you can divide the load depending on the user base and corresponding volumes of data access.

3) Detailed What-If Analysis: Data needs to be manipulated for what-if analysis and for the same it might require a write-back to your underlying data. This is not possible when you have a lot of users who would be accessing the same data warehouse. This can be very well catered by creating a data mart for this requirement.

4) Isolating data discovery related initiatives: Lots of research and development related activities needs to be carried out on a OLAP system for intelligent data discovery like predictive analysis, adjusting the data model to use with advanced analytical visualizations, data mining, forecasting and budgeting activities by applying external data to your data in DW. Such RnD are safe to carry out on an isolated environment, and data mart can be one perfect solution for this requirement.

Creating a Data Mart is not free of efforts. It requires additional ETLs, additional space, additional maintenance overheads. But considering the business value it brings to the table, it is worth creating data marts in certain scenarios. Above list is not an isolated list of scenarios, but in my experience, these has been the prominent ones. Feel free to share your experience with me on the same lines.

MS BI Technical Evaluation against Gartner's BI capabilities requirement list

Gartner's Magic Quadrant for Business Intelligence platforms is one such reports, that almost every serious BI professional should read in detail to get a summarized report of the winds of change in the BI industry. The latest version of the report, that has been published as of 27-Jan-2011, can be read from here.

A section of this report states that BI platform is a software platform that should deliver 13 capabilities, classified in 3 categories of functionality: Integration, Information Delivery and Functionality. Below are my views for Microsoft BI platform against these 13 capabilities. Before reading the below, it would be beneficial to go through the details of these capabilities from the original report.

Integration:

BI Infrastructure - Microsoft has tight integration between it's development platforms like .NET, Sharepoint, SQL Server, Biztalk and others. Appliance solutions like Parallel Data Warehouse, brings MS BI in competition to IBM for appliance based solutions too.

Metadata management - MS BI still struggles in this area, and metadata management has been the major ask from customers and practitioners from SQL Server.

Development tools - In terms of development tools, it's often termed that Microsoft BI is more friendly to developers / IT than to users, such is the extent of efficiency and variety in this area.

Collaboration - Sharepoint is the highest selling server technology of Microsoft, and probably Microsoft is the only BI player to provide BI integration with it's collaboration platform.

Information Delivery:

Reporting - My personal opinion is that this is the weakest corner of MS BI. Reporting can be seen as a strong supplement in the MS BI Stack, but still it needs to evolve a lot. Developer tools are limited and professionals still need to depend upon tools like Fiddler and frameworks like SCRUBS. Report access from smart devices is far from even imagination. Players like QlikTech are sweeping this market.

Dashboards - Microsoft has been comparatively weaker in the area of innovative visualizations. With the license acquired from Dundas, SSRS has received a healthy boost in this area. But PPS is still quite immature and needs to attain the same level that Proclarity used to have. After dissolving Performancepoint Server and integrating it with Sharepoint has been the only progress. Visualization like performance maps are still missing, and MS BI can see seen to be doing just okay in this area.

Ad-hoc Query - This means self-service BI. Powerpivot is a strong candidate in this area, and with the news of BISM, MS BI can be seen as doing great in this area. Technology / Component like PivotLabs adds a thrust to this area.

Microsoft Office Integration - MS Office is one of world's most successful softwares ever built, in my views. MS Office, especially Excel, has evolved so powerfully, that it can no longer be seen just as a spreadsheet. It's a thick client that is integrated with almost every MS server technology. Office Webapps takes collaboration to a new level.

Search-based BI - Microsoft's candidate in this category is FAST Search. Two indexors available with FAST deployed on Sharepoint, are the providers of search on it's collaboration platform. Sharepoint is equipped with BI capabilities in terms of reporting and collaboration facilitation. I do not have much experience in this area to comment confidently, but I feel this is still not such a strong area. Metadata search is still weak in MS BI parlance.

Analysis:

OLAP - SSAS and OLAP are the most successful adopted areas of MS BI. Columnar databases and in-memory analysis are used as weapons of creating great hypes, but customers still bank on SSAS as the technology for their DW needs.

Interactive visualization - This area has always been weaker with MS BI. To measure this, compare visualizations available in PPS and SSRS, and compare it with vendors like Tableau, QlikTech. You would get the reason for my such viewpoint.

Predictive modeling and data mining - MS BI stack has it's offering of data mining, but this area has not been that successful in terms of adoption by the industry. There are dedicated players in this area, who are considered as standards. I am even of the opinion that Microsoft might just slice out this section from MS BI stack and come out with a new product in itself to cater this area.

Scorecards - MS BI still facilitates scorecard creation using traditional grid like UI and indicators / trends. Visio comes in as a backup force to create strategy maps and associate KPIs with interactive visualizations. But still it's not as good as what BeGraphic provides.

These are my unbiased viewpoints on what MS BI delivers in these 13 capabilities. I am of the strong opinion that, if you are a serious BI professional, you should definitely read Gartner's Magic Quadrant for Business Intelligence Platform, and enlighten your maturity about BI industry.

Data Analysis using Performance map / Heat map / Tree map visualization ( missing in Performancepoint Services 2010 )

Data Analysis using visualizations: Data analysis is a very complex process depending upon the volume of data, nature of data and nature of data analysis. The most basic form of data representation is in a tabular or matrix format, but the amount of data analysis that can be done straightaway with such representation is very limited and time-consuming. Especially when the volume is huge and the nature of analysis is complex and detailed, a better mechanism of data representation is required to facilitate better data interpretation by the data analyst. Check out this gallery to get an overview of different kinds of visualization available for data representation. Those who are innovative and have special interest in creating and using innovative data visualizations should check out Google Visualization API Gallery.

One of the commonly used data visualizations is Tree Map and Heat Map. They have quite a lot of resemblance, but the difference is the purpose for which they are used. Heat map is generally used more in scientific applications, and Tree Map is the visualization that is used widely for portfolio analysis like Securities data, Performance data, Statistical distribution analysis and others. Tree Maps are also interchangeably known as Performance maps. Those who are curious to learn in detail about the scientific theory and usage of Tree Maps, can check out more from here.

Analyzer Recipe: Proclarity used to have Performance maps, but after the evolution of PerformancePoint Server 2007 which now exists in the form of PerformancePoint services in SharePoint 2010, this visualization is still missing. Developers still try to emulate creation of tree maps using mathematical algorithms, which shows the importance and need of this visualization. This post is focused on discussing how data can be analyzed using Strategy Companion's Analyzer with this data visualization.

Let's get started with creating a simple dataset to analyze. I have used the AdventureWorks cube as the primary source of data for reporting and analysis. To get an idea of the Analyzer report authoring environment, please go through the previous Analyzer tip.

1) Create a pivot table, which shows data in an advanced tabular / matrix format.

2) On the row axis, I have selected the namedset "Top 50 Customers". On the column axis, I have selected the "Calendar Year" hierarchy from the "Date" dimension. On the values / details area, I have used the "Internet Sales Amount" measure. After you have configured your pivot table with this data, your report should look something like the below screenshot:

3) Before we move ahead, I would suggest to give a thought on what can you analyze out of this report and how quickly can you analyze this data. From the menu option of the PivotTable control, select the "Analyze in TreeMap" option. Configure the options as shown in the below screenshot. To get sufficient screen space, I have opted to create the map in a new sheet.

4) After your Tree Map has been created, your report should look like the below screenshot. If you hover over individual boxes / rectangles, you would find the values displayed in the tooltip.

Just give a thought now as to what can you analyze from this report and how quickly? As per the configuration set by us, the white color is the indication of the lowest value and brightest blue is the indication of the highest value. If you analyze carefully, you can easily make out clusters of worst, average and best sales values and customers associated with them. Analysis can be done within a single year as well as a comparative analysis can be done across years from the size of box created for each year.

5) To take our analysis a level further, go to the sheet containing the pivot table and select "Expand Members" at the "Calendar Year" hierarchy. Now come back to this sheet, and the report should look like the below screenshot. Just looking at this screen, I was able to make out that H2 - 2006 and H1 - 2007 was not good in terms of internet sales. There is much more analysis that can be done using this Tree Map.

Read-world Application: A real-world example of a company using this Analyzer visualization is Citigroup, which stores performance and capacity-planning related data about all of its global servers and applications (which as you can imagine is a lot of servers and applications) in Analysis Services cubes. Citigroup uses Analyzer Tree Maps to visualize all of this data, and uses the traditional red and orange colors to indicate current and potential problem areas, such as a server reaching its capacity limit. They store this information in a hierarchy of server-application-hour of day, so they can drill down to a specific hour of a specific day for a specific application running on a specific server, anywhere in the world. The colors automatically highlight for them which servers or applications should be looked at more closely.

I suggest that one should at least download an evaluation version of Analyzer and give a try to this recipe. Analyzer as a reporting solution supports the Tree Map visualization, which is still a limitation with PPS 2010 and SSRS 2008 R2 too. I am sure this must be on the plans of PPS and SSRS teams. To realize how valuable it is to have this visualization created with a single-click, try to simulate this effect in your SSRS reports and you would feel the value of this visualization and worth of Analyzer as a reporting solution that supports this visualization.

Customizing and Formatting SSRS Parameters Toolbar : Limitations, Risks and Solution

Parameters toolbar in SSRS reports is the out-of-box UI based mechanism for parameter selection. The good part is you get this user interface without any effort, and this makes your report ready to use within seconds. The real part when you would see problem with this comes at a later stage, and you run with an unforeseen risk. The parameter toolbar is very much primitive in look and feel. Few limitations with this toolbar in my views are as below:

1) You cannot apply any custom styles and formatting to the text / controls in this toolbar.

2) You would always have a single button with the caption "View report". You can neither add any more buttons program anything in this button.

3) You do not get a reset button, neither you can add any such button besides the "View report" button.

4) You always get parameters in two columns, which makes the parameters toolbar too huge in height many a times.

5) When multiple values are selected from multi-selection parameter dropdowns, they are displayed as comma separated values. With tens to hundreds of values, this UI becomes almost unacceptable to users.

6) Multi-selection parameter dropdowns have "Select All" as the parameter value which acts as a function to select all values. But the parameter dropdown does not display the text "All". It still displays comma-separated values. This kind of UI confuses and stimulates users to bang their IT departments with CRs (Change Requests). This becomes a big issue, as by the time users hit you with this requirement, you would have dozens of reports ready and deployed all over the place.

7) Whether the report is displayed using .NET Reportviewer control / Sharepoint ( MOSS ) reportviewer webpart, this toolbar is still not programmable.

So what is the solution to this problem ? First option is that you can switch to .NET based UI for parameter selection and programmatically pass parameter values to reports, thereby abandoning display of this toolbar. Another option if you are using MOSS Reportviewer webpart is to use some other webpart like Filter webpart or create parameters with MOSS development, and apply the same technique suggested in the case of .NET.

In my views, Microsoft should expose a programmable API for customizing this toolbar. SSRS has evolved healthily since the SSRS 2005 version, but even in SSRS 2008 R2, this toolbar is in it's primitive form. Power Users would interpret SSRS as an immature / incapable technology as it does not allow any flexibility to program even cosmetic issues and provides a primitive toolbar out-of-box, and IT department would have tough time explaining that it's the only odd corner of the technology. Till the time some evolution happens on this toolbar, it's better to plan your solution in advance for the risks associated with usage of this toolbar.

PS: Above viewpoint is based on the best of my knowledge about customizing SSRS parameters toolbar. I am aware that upto an extent you can customize this toolbar for Reports Manager with some styles, but it's not programmable using any kind of object model. In case you are aware of such solution, please feel free to correct me and share your solution for the benefit of all.