Data warehouse planning tool

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 !!

Silverlight based Business Intelligence using Infragistics Motion Framework

Data discovery is a branch of BI that is applied over strategic data i.e. long-term historic data. Visualization plays a very vital role in BI data representation to facilitate effortless data analysis. Silverlight based BI components and motion frameworks is the new trend gradually propagating in the MS BI world. Dundas Dashboards, Telerik Charting and Grid controls, Silverlight Toolkit from Microsoft etc are fine examples of Silverlight based components that can be used for BI reporting.

Before few years, the way bullet charts came as a new candidate for KPI reporting, motion graphics based data analysis is coming up as a new visualization for data discovery. When you have a set of attributes of an entity, and you want to analyze individual and group entities changing over a scale, typically time scale, motion graphics based analysis can be a very effective tool.

This statement may sound very confusing to interpret and digest, but can be better understood by this example. For ex, when you have a trend analysis report, you have the entire trend on a graph. If you want to study trend step by step, that is possible only in your mind but not on the graph on which trend is displayed. If you want to see trend step by step, you need to keep changing the scale of the graph and keep on increasing the same to study it step by step.

Studying trends using motion graphics solves this problem. Silverlight is a rich ingredient for an appealing user experience and to abstract lot of detail on the report, still making is available conveniently on demand. In my last experience, I remember Trend Compass having a motion graphics visualization. I also vaguely remember Telerik having a similar component in their charting controls. Now Infragistics is a new player coming up with a new rich motion framework, that sounds very appealing from the way it has been portrayed.

Infragistics has been a very popular name in the .NET world for the rich UI capabilities it offers with its frameworks. The new offering that I see from Infragistics in the context of BI is its Motion Framework. You can understand the use of this framework only from a demo, and one of the very intresting demos can be accessed from here. Over a slow internet connection it might take a little time than usual to load, but your patience would pay off by the way the experience of the demo would delight you.

I suggest to check out the following from the demo:

1) Click on the Play button on the top-left of the screen. This is the main highlight of what motion-framework offers, and what you can study from trends that change over time.

2) Hover your mouse on the colored world-map on the top-right in different colored sections, and see the effect on the upper chart. This map acts like a filter !!

3) Click on a few random countries in the bottom-right. It acts as parameters to the bottom chart.

4) Watch out the tooltip of the countries, and see the visual of the tooltip.

5) Watch out the tooltip of any of the country icon in the upper chart, which has numeric as well as graphical details.

There is much more to explore, and I leave the rest of the exploration to the delight of the readers.

Free AddIn to create Strategy Maps / innovative data driven visualizations

Strategy Maps is a very vital, but often one of the more ignored component of a MIS Dashboard in a MS BI Solution Development methodology. Visio is one of the primary means in MS BI Solution stack to create data driven diagrams - which is also known as strategy maps when used and developed in the context of the dashboard requirements. Visio is ofcourse not a free tool, and it's not the best tool for business users to create visualizations for their data generally stored in MS Office document formats. Visio is not dedicated to creation of strategy maps, though it facilitates it. Visio is meant for a variety of purposes, but still a need is always felt to have a dedicated designer for creation of Strategy Maps. Such designer can/should facilitate to custom tailor innovative visualizations as per your business needs.

One of the partially FREE tools to create data driven visualization is BeGraphic. Tons of different types of charts can be created from your data stored in Excel and Powerpoint. The repository is so rich that one can even learn what kind of business charts / analytical data visualizations exists in the industry. Also it has very broad support for the data sources from where it can fetch data. It offers 10,000 interactive maps for free. I feel that using this tool, you can add a flavor of GeoSpatial support without depending upon the Bing Maps control. It also offers a variety of gauges, meters, sparklines, and other infographics for free. In summary, in my experience and knowledge, this is customization for data visualizations, provided at it's best. In my opinion, it's a must check-out tool. Click on the below image to watch the animated graphic.

Web based OLAP client for SSAS : Report Portal

In my views, OWC (Office Web Components) available in SSMS is by no means a professional OLAP browser. Excel is one of the widely used client for browsing OLAP cubes, but there are some limitations with it when one wants to consider it as an enterprise client, some of which are listed below:

1) It needs to be installed on every client workstation
2) Data visualizations available are more suited for operational reporting than analytical reporting
3) There are almost no features for collaboration i.e. sharing generated reports with other users on a common platform unless you use Office Web Apps or Sharepoint
4) No spatial data support either in consumption or presentation with Bing Maps / Google Maps

Actually Excel has the capability to use OLAP / SSAS cube as a data source, but that does not mean that this capability can be treated as the primary purpose of using Excel. In one of my posts, I mentioned about Analyzer which is a product from Strategy Companion. Recently I came across one another such product - Report Portal.

Zero footprint, SaaS, Cloud are some of the buzz words while selecting a technology / product for solution development, and Report Portal is also a zero footprint OLAP web client for SSAS. In my view, this product lacks that professional and sleek features that is expected from a web client. Documentation is quite poor, export formats of reports are limited, there is no mention of SaaS offering. But some of the noteworthy features of this product are as below:

1) Report portal has got very distinguished data visualizations, which are quite impressive from a data browsing point of view. You would not use these kind of data visualizations generally for reporting, but to develop analytical reports, these visualizations can be quite helpful for analysis. Check out the Pie-Chart Tree Report, which is one of the quite unique kind of data visualization for data analysis.

2) Collaboration and organization of the analyzed data, which can be saved in the form of reports, is considerably mature.

3) Metadata can be browsed very efficiently, and this product makes a very extensive use of XML/A ( XML for Analysis ). The browsing features are quite friendly to take away the pain of forming MDX queries.

If you use it as a tool to save your OLAP analysis i.e. the results that you gathered while browsing or analyzing OLAP data, and want to stick it in the form of reports on a collaborative platform, it's a nice web client with zero footprint installation ( as it's a web based solution ). But if you are looking out to use as a full fledged self-service enterprise reporting solution out of your OLAP, I would feel much more comfortable and confident in Analyzer than Report Portal.

A wise strategic move can be to use Report Portal when you are taking your baby steps in your OLAP reporting i.e. the phase when you are developing your data marts / data warehouse. This product could well support your OLAP browsing needs efficiently as well as facilitate reporting at the same time, keeping the pressure of immediate reporting from your evolving data warehouse, at bay. When the solution is in the next level, you can choose a SaaS reporting solution like Analyzer and retire Report Portal. Please keep in view, this always depends upon scenarios and it's not a generalized strategy. The key difference between Analyzer and Report Portal is that Analyzer is a reporting solution, and Report portal is a web client and reporting is one of it's features and not a primary function.

Self service enterprise reporting solution for data warehouse powered by SSAS

In a typical Microsoft solution involving MS BI and .NET, after the data warehouse development generally a BI development team hands over the next lap of development to the application development team. BI team and/or App team remains responsible for developing reports that are hosted within the proprietary .NET application and/or Sharepoint portal. The disadvantage is that, it takes away the self-service feature from the business users. When the requirement is "race against time", i.e. when one needs reporting solution asap which can serve enterprise class BI reporting on the top of your data warehouse or cubes, the selection moves towards ready to use reporting solution that can sit on the top of your BI artifacts.

One such solution is a product from Strategy Companion known as Analyzer. This reporting solution is said to be developed using ASP.NET and DHTML. Some of the features that I found pretty regular in line with what other such reporting products might offer are reporting using KPIs and indicators, drag-and-drop report creation, security integration with LDAP and Kerberos, different visualizations and gauges, integration with IE, SSRS, PPS, Sharepoint, Excel. But some of the features are quite impressive which any project would desire, and they are as follows:

1) Zero foot-print installation: Thin client is one of the most sought after requirements for any kind of enterprise wide client access. If you can facilitate your analytics requirement just using a browser, and still facilitate drag-and-drop report creation which for self-service report creation with rich report development features, it's very much favorable for any business.

2) Software as a Service solution: No business would want to invest straight away for a full fledged enterprise wide license, just based on an evaluation version, until and unless the product is a premiere class product listed in Gartner's report. Also small and medium businesses might want such reporting in an on-demand fashion. Analyzer provides the option for an SaaS deployment too.

3) Logic as a service: This is not a standard term, it's just my synonym for the term webservice. Analyzer features can also be used in the form of webservices, to embed reporting functionality in existing applications. This is a nice to have feature, but in my opinion, I would not recommend for this option. Instead there are better options than this, but still if you have deployed Analyzer for your enterprise wide BI reporting requirements, and some proprietary applications might be getting left out, this option can help in such cases.

4) Support for load-balancer / clusters: Analyzer can be deployed on IIS that is deployed on multiple servers and managed by a load balancer.

If I am given the requirement of agile DW development and facilitating reporting on the top of my MS BI artifacts, and provided that neither I do have enough funds to invest into ramping up a team to carry out a full fledged development life cycle nor I have time to wait for the availability of such solution, I would opt for tools driven DW and BI Reporting development. For the same I would pick up products like WhereScape RED for agile DW development and Strategy Companion's Analyzer for BI reporting. I do not intend to say that these are the best options, but these are definitely the two prominent ones.

Tableau and BonaVista Dimensions for self service data analysis and diversified data visualizations

The term "self-service" has been making a strong buzz since quite some time. Though PPS caters the platform for dashboard creation and sharepoint hosts it very well, but still I feel that Microsoft has a lot of scope to improve in the data visualization area. Microsoft acquired the license from Dundas for charts and graphs, and the same can be seen being available in SSRS and Excel, with the addition of visualization enhancements like Sparklines, Indicators etc. But in my views, still it's at a considerable distance from the data visualization capabilities that few other tool offers. Proclarity had some of the best data visualization capabilities, and phase-by-phase same are being added to PPS. It was one of the best tools for data analysis and the visualizations that it created, especially the decomposition tree was spectacular.

Tableau is one of the leading tools in the area of data analysis for it's extremely rich data visualization capabilities, which makes analysis quite easy and appealing. One might immediately think that Excel has also got rich visualization capabilities as a client tool. Nothing can be rated without a comparison, and I suggest to go through this product tour video of tableau, to get a feel of the data visualization capabilities that it offers. Honestly speaking, I see it as one of the accelerator replacement, if you are not intending to invest into PPS and developer hours to build dashboards and want to equip your business analysts to self-serve data analysis. Also if you want to add the self-service effect with power packed data visualization capabilities, Tableau is one of the top 10 options to consider. Check out this amazing data visualization gallery.

BonaVista Systems, has launched a new product called BonaVista Dimensions. To me this product seems like another version of powerpivot. If you want to learn the rationale, based on what I am making this statement, check out this architecture diagram. In my views, what makes this product different from powerpivot, is the data visualization that it adds to Excel. It can be said that it adds tableau data visualization effect to Excel. I have not done any research into the pricing and licensing details, but what I can say by and large is that if you are looking to powerpack your data analysis using Excel as the client tool with Tableau class data visualizations, this product has the potential to add value to the data visualization and effectively the data analysis department.

One question that might come to one's mind is that why not Powerpivot? My immediate answer to the question would be that powerpivot still misses data visualization capabilities and whatever it offers in this area is on the shoulders of it's hosting tool i.e. Excel. I do not intend to say that these products are better replacements for powerpivot, each product has got it's own space. Till microsoft makes the quality of data visualization enriched to the level of Tableau, it can be seen as one of the best value additions on your MS BI solution, which again depends on factors like budget and time to market / time to delivery. In summary, excel is a good data analysis client tool to consume data from SSAS, but it can still be made a lot better.

Products for Data Warehouse management on cloud and in premises : CloudOlap and WhereScape RED

There are two ways to create Data Warehouse, one is using the traditional development life-cycle and other is by using accelerators (i.e. products) that build on the top of data warehouse related technologies provided by vendors such as Microsoft, IBM, Oracle, Teradata and others. In the traditional development methodology, we start with profiling metadata & cataloguing data quality in OLTP, develop relational DW and Cube design, and corresponding ETL tasks in parallel. This process is the suitable way when you have sufficient time, resources and funds to roll out your enterprise wide DW in a phased approach, mostly when your DW is integrated into your proprietary application.

But when you need to ramp up your DW in a short period of time, and you have a limited IT Staff with the right skills, within your organization for developing your DW, one can consider investment into products like WhereScape RED. Some of the noteworthy features of this product are:

1) It provides a GUI for developing DW in a very agile manner

2) It builds ETL processes for fueling the data into DW that you build with this tool

3) It has support for a majority of standard DB / BI vendors

4) It creates automated documentation as a part of the development process

5) License of this product can be quite costly, and the way it can be compensated is by saving on the duration of time for which technical staff needs to be working on the project.

A better idea can be gained from this client story ( Coinstar ) of WhereScape, where they used it for developing their corporate data warehouse.

This product works on the top on DB and BI technologies that work in-premise. Had it been on the cloud, it would be one of the most impressive accelerators for MS BI DW development. WhereScape has been around in this market since quite a few number of years, but seems like cloud on not on their agenda till now.

One another product that provides similar kind of functionality in-premise or on the cloud ( Amazon EC2 ) is CloudOlap. Though I have not evaluated this product much deeply, this video gives a very nice overview of the functionality of this product. CloudOlap provides various power products for Microsoft and SaaS Data Warehousing, in-premises as well as on the EC2 cloud environment. If this product is what it claims to be, I would rate this product much higher compared to WhereScape RED, and the reason it that it is one of the rare products that leverages MS BI to a SaaS platform on the EC2 cloud, and it provides functionality similar to WhereScape in-premises. May be WhereScape should start thinking about it !!

Difference between QlikView and Powerpivot , is it Mobile Business Intelligence ?

Qlikview is one of the fastest growing product in terms of self-service business intelligence. I felt the need to compare and technically evaluate the differences between QlikView and Powerpivot. There are two very important articles that explains the comparison between these two products. One is authored by Donald Farmer titled "QlikView from a Powerpivot standpoint" and the other is by Darren Kerfoot "Microsoft's new Powerpivot from a QlikView standpoint". Though more or less, both of these products offers similar set of services, one difference that caught my attention was Qlikview's support for mobile business intelligence.

Before you read ahead, below is an interesting comic strip from Dilbert, that I think would fit the theme. Companies who are acting as solution providers to their end clients, often need to provide BI application and services on mobile devices, and the target users can be business leadership as well as power users. And if your product has no support for mobile business intelligence when other competitors are flaunting their demos on IPads, IPhones and Blackberrys to CEOs and CIOs, it can be a big concern to worry.

In the similar way, it might sting a little when you announce that you are not / never going to provide business intelligence delivery on mobile devices. Major providers of Business Intelligence products / solutions have already started supporting delivery of BI reporting and access of BI solutions on mobile devices, for example SAP BusinessObjects Mobile , Microstrategy 9 Mobile , IBM Cognos 8! Go Mobile. Presently in my knowledge, there is almost no significant support for mobile business intelligence in Micorsoft BI Stack. I am sure Microsoft must be having it's eyes on this growing requirements or rather I would say growing market. Accelerators like Roambi can still be used to extend the reach of SQL Server based BI solutions on mobile devices.

Sharepoint 2010 with limited features on the cloud without BI support = Sharepoint 2010 Online

Microsoft Business Productivity Online Suite (BPOS) is a collection of products that are available on the cloud. These products are Exchange Online, Sharepoint Online, Office Livemeeting and Office Communications Online. These products / services are available in Standard and Dedicated edition. Standard version is a shared services platform and Dedicated edition provides an isolated and dedicated server space in the cloud. Microsoft has made office web apps available for free usage on sky drive, and during the later part of this year, office web apps is also expected to be available on the cloud. This is a complete SaaS offering from Microsoft.

Being a MS BI professional, my interest goes specially in Sharepoint online. Presently it's based on Sharepoint 2007 platform, so it can be easily interpreted that it won't have features like Access Services and Performancepoint Services. During the later half of this year, Sharepoint online is going to be upgraded on Sharepoint 2010 platform. This immediately raises a curiosity in my mind, whether Performancepoint Services and Access Services would be made available in this version. Microsoft has though not officially confirmed anything (to the best of my knowledge), but several Microsoft as well as unofficial sources have confirmed that performancepoint services is definitely not going to be a part of Sharepoint 2010 online. This means an end to the scope of dashboard development and hosting on the cloud.

Access Services may or may not be made available on Sharepoint 2010 online. One interesting feature of Access Services is Web databases. If Access services and effectively creation of web databases on the cloud is made available in sharepoint 2010 online, this can be seen as one of the competition / alternative to SQL Azure. My mind raises a question that when I have capabilities of sharepoint 2010 (though limited) available in the cloud, with web databases facilitated through access services, how many chances are there that I would opt Windows Azure storage and SQL Azure against Sharepoint 2010 online ? In fact, news are that Sharepoint 2010 online might be made available on Azure platform.

In short, Sharepoint 2010 online diminishes the hope and scope of BI by eliminating performancepoint services. Those who are curious to learn more about Sharepoint online can download Microsoft Sharepoint Online Standard Developer Guide. Though it's quite old and there can be changes in the present offerings, but still it would provide a deep insight into Sharepoint Online features.

Data Warehouse as a Service (DaaS) in MS BI Stack

Fast track data warehouse architecture and parallel data warehouse are two entirely different kinds of solutions but targeting a common domain of data warehousing. As SaaS solution is not available in MS BI Stack, similarly DaaS solution is also not available in MS BI Stack. If these kind of solutions are made available on private cloud solutions like Windows Azure Appliance Solution, a DaaS kind of offering can be made available on Microsoft platform. I am not sure if this would be of interest to Microsoft, but in my views, this is definitely of interest to solution provider kind of organizations. I feel that combining Azure and MS BI Stack, Microsoft has all the raw material that is required to develop SaaS or DaaS solutions, but the integration part is missing.

One of the leaders in DaaS solution is Kognitio. Enterprises are looking for on-demand services, and Azure can be seen as the first step in this direction. And whether it's for self-service business intelligence or data warehousing, there is a huge market for each of these solutions and microsoft is eagerly awaited to make entry into these (SaaS and/or DaaS) markets. Integrating MS BI on cloud would not be simple, and again making the same available in the form of service would change the existing MS BI markets. Not only that, solution providers would also have to adapt to this change, and look at a completely Service Oriented Architecture (SOA) solution proposition.

Below is an excerpt from Kognitio DaaS page, to which I fully agree.

• DaaS solves several issues that are common within the data analytics industry. Most notably, the inability to prove business value from data warehousing and analytics projects before spending large capital sums of money.


• DaaS allows you to run your large-scale data analytics projects (marketing campaigns, business reporting etc) and you simply pay on a pay-per-use basis. There is no overhead of implementing a data warehouse onsite, there is no need for your IT department to service, maintain and support the database and its users.

More about Kognitio and the services offered by this company can be read from here.

Self service Business Intelligence on SaaS Platform Alternatives ( Powerpivot Alternatives )

Self-service Business Intelligence is one of the growing markets these days and business users are looking for flexibility and capability to analyze data and design reports or dashboards at will. Products / Services that provide dashboarding solutions, but are IT Developers toys and makes Business Users dependent on IT Staff to request for almost anything facilitated by these products, are miles away from self-service BI.

Any MS BI professional would be aware of Powerpivot which is a "managed" self-service BI tool for business users in the Microsoft BI Stack. But this is not the only star in the constellation of self-service BI tools in the BI galaxy. Below are the alternatives (in my knowledge and in no specific order) that are worthy to be explored, and you would find that one or more products fit your needs depending on at which stage of BI maturity is your enterprise.

Few of these are cost-effective, few are good for enterprise that are starters in the BI space, few are good for better in-memory performance, some are good for better visualizations, and some are good from an overall partner support and product integration perspective. I am reserving a detailed comparison of few or all of these for a future post. If I am asked the top 3 favorites, as a MS BI professional I would be interested in Powerpivot, Qlikview and PivotLink.

1) Powerpivot

2) Qlikview

3) PivotLink

4) Tibco Spotfire

5) IBM Cognos TM1

6) Advizor

7) Altosoft

8) Vizubi

9) SAP BusinessObjects BI OnDemand

Panorama Novaview with Powerpivot for NON managed self-service BI

Recently, someone made me aware about an interesting article titled "Powerpivot & Analysis Services - The value of both" authored by Panorama Software product manager. This article explains how Analysis Services can be useful for enterprise to model the analytical solution for their known requirements and how Powerpivot becomes helpful to extend the analytical solution by self-service. It also states how Novaview can help business users to build similar to the capabilities and potential of Performancepoint Services i.e. build KPIs, charts, etc.

If I think of a poor man's analytical BI solution, I would think about the cost. Cost can be gauged in terms of expensive licenses of feature rich editions, and IT staff required to facilitate and maintain the BI solution for volatile needs of the business users. PPS is a part of Enterprise Edition of Sharepoint 2010. Also this does not fall in the category of self-service BI, as a business user cannot be expected to develop a dashboard using PPS. A economic recipe of a poor man's analytical solution can be to use Powerpivot as a data source ( by building cubes using powerpivot ) as well as analysis engine too for front-end tools like Novaview. This can eliminate the need for Sharepoint 2010 Enterprise Edition and one can use Sharepoint Foundation Edition too for collaboration of deliverables developed using tools like Novaview. This is the first part of the savings.

Second part of the savings can come from the fact that these tools are claimed to be easy to learn and targeted to be used by business users than the IT services providers. So the dependency on IT Staff to manage and extend the solution becomes less, which effectively translates to savings.

Of course, these tools cannot be a complete replacement for Sharepoint 2010. But if you need a middle path where you want the BI services to build just few Dashboards / KPIs / Charts by business users, at the same time you do not want to invest into Sharepoint 2010 as you might not be sure that you would exploit the full potential of services like PPS, Excel Services, Visio Services and others, Powerpivot with Novaview can be one of the options to try out. Powerpivot is managed self-service BI tool, but by adding a layer with tools like Novaview and using Powerpivot as a data-source, the managed quotient can be reduced to a fair extent. I would not stress on which kind of enterprises would benefit from this recipe of solution, but I am sure that there are enterprises who would want to start slowly and steadily before they hire a full fledged IT Staff or a Solution providing vendor to design a huge enterprise class analytical solution using tools like SSAS and Sharepoint 2010 Enterprise Edition. Those enterprises should give a thought on this recipe.

Silverlight Charts - Data visualization for SSRS

I have been always wanting since long, that Microsoft should come out with Silverlight based data visualization controls that can be used in Business Intelligence solutions. Products / Services like Performancepoint Services which are still found digesting the codebase of Proclarity, are not equipped with Silverlight based data visualizations. SSRS Team also bagged the license from Dundas to distribute and use Charts, Bars and data visualization of Dundas but still they do not have that silverlight magical touch. In my previous post about Trend Compass, I mentioned that this is a wonderful way of visualizing or analyzing data as Time can be literally used as a dimension of analysis by the end users instead of using it as a filter, and that too with animation.

Sometimes I happen to find that this BI world is too big and my knowledge is too small. Coming to why I feel so, the reason for the same is Silverlight Toolkit. This toolkit is available from Microsoft and I was completely unaware of one of the wonderful gems that has been developed jointly by SSRS Team and Silverlight team. This gem is known as Silverlight Chart Control which is a part of Silverlight toolkit. This control can be bound to data and has got a lot of functionality to explore, and if you have read my previous post, you would find that this control can cater what Trend Compass offers. A picture is worth thousand words, so watch the below effect that can be obtained from this control. I found this picture on a post of a blogger Tim Heuer.

So if you feel interested, jump on to download this toolkit and try out the use of this control in SSRS. I am also ready to get my hands on trying it's use in SSRS as soon as it get time of my regular work. If you want to check out demo of different controls available from this toolkit including the chart control, you can check out the same from here.

Trend Compass - A raw gem in the Data Visualization treasure box

On my recent post, I received a request is the form of comment from someone claiming to be from EPIC Systems requesting a review of their product. Out of curiosity I took a look of the product and after my initial review, I can say that this product definitely looks like a raw gem which can be made an industry class standard in data visualization and analysis. There are still some questions open in my mind regarding this software, which I would come across very shortly.

If anyone has worked on dashboards using platforms / technologies like performancepoint, novaview, mySap portal, dundas dashboards, telerik dashboards etc, analytical charts is one of the components that works in the context of scorecards. We are accustomed to analyze and represent data in the form of year to year or quater to quater using functions like ParallelPeriod in MDX. Now this period is defined in the form of slicers which functionally works as filters rather than an axis. Also many on these analytical charts developed using excel services and other regular image / graphic based functionality are not that impressive, though the Santa Claus of Microsoft ( I mean to say Silverlight ) is changing the traditional way of dashboard development. In summary interactive motion graphics and time as an axis concept is almost absent in present days dashboards, except Silverlight based ones.

Coming to the star of the post is a Data Visualization software "Trend Compass" from a company called EPIC Systems. I have a favourite quote of mine, "A picture is worth thousand words and A Dashboard is worth thousand reports" (may be I should get it patented), here is a must look demo application of this software.

1) The graphics are very impressive and still it manages the issue of "Too Much Information" in a very descent way. Data Applied ( a silverlight based data mining service provider for modest volumes of data ) suffers too much from the issue of "Too Much Information" in my views.

2) The data visualization presented contains the concept of layers which we see in present days SSRS reports development using Bing Maps layers. This helps to manage the data representation is a very nice manner and give control to the viewer to analyze the information in a very interactive way.

3) The biggest USP of this data visualization software is it provides time scale as an axis with interactivity of data visualization and analysis still preserved at its best. In fact it really fuels the way data is represented for analysis.

4) Exporting data to different formats is one of the most admired functionality in any applications, and Trend Compass scores full marks for the same. It can export data in EXE, SWF, AVI and other formats too which is really fantastic.

Coming to the downside of the application, which is the reason I call it as a RAW GEM, is as follows:

1) For any application to get widely accepted, providing developer information is very vital, which leads to developing an army of technical workers around it. Developer expertise is one of the major factors considered while evaluating any product for procurement. On the product website, there is no technical information available like object model, integration, data sources supported etc.

2) Nice demos are presented, also trial version is available for download. But whether it can be used as a component on collaborative environments like Sharepoint using some webparts, such kind of information is also missing. One might be very much interested in using this component as a constituent of a dashboard instead of using it as a off the shelf tool which does not integrate into the solution.

3) There are no comparative study available on the site, or no whitepapers available where they demonstrate how this software can be used in collaboration with other leading products in the same domain.

It seems like still the viewpoint of the promoters of this application is not that wide, an there is still a lot for them to learn from industry leaders like Performancepoint, Dundas and Panorama. But I have no doubt that this is a RAW GEM and the data visualization it offers is very impressive. Many would argue that it might not be efficient and collaborative, which even I would agree, but it would not fail to appeal the users ( senior management ) of the organization for sure. And some of the features like exporting to formats like AVI and EXE which have never even been heard of, should make the minds of industry leaders ticking in this direction.

Dundas Dashboards v2.0 Technical Evaluation Considerations

Dundas Dashboard v2.0 is in the beta stage and soon going to get released. I am a big admirer of Dundas Data Visualization, and I always wish that after digesting the codebase of Proclarity, if Performancepoint can manage to consume a few data visualization components of Dundas considering it as dessert, the meal would be complete.

As usual I had a quick sortie over the features of upcoming version of Dundas Dashboard, and I feel that some of the features are quite revolutionary, some of them are just leveraged, and some do not manage to influence my viewpoint. Below is the summary of my views:

1) OLAP Support: The features related to OLAP supported are quite okay and in fact necessary to create a bridge for OLAP data to land into Dundas Dashboard territory. One of the features that catches my attention is, it allows creation of what they term as virtual cubes. It would be interesting to see how this feature adds value over perspectives created in SSAS for the cube.

2) Sharepoint Integration: Apart for other normal features, one striking features is yet another star in constellation of Sharepoint webparts - a dashboard viewer web part. This can effectively mean that, you can view multiple dashboards on a single dashboard ? To me this would not make any sense from a usability viewpoint. But yes it can be that using the web part, one can have more grip over customization of the dashboard.

Dundas wins in this aspect over performancepoint, where it facilitates dashboards to make it's way out of Sharepoint world. Dundas dashboards can be used in .NET and other host applications, while this is not possible with Performancepoint. To be honest I was not aware of this feature of dundas, till recently this came to my knowledge.

3) KPI Stubs: I like this feature, and it reminds me of my days in .NET development when we used to start with prototyping until the project sign-off was confirmed. It allows prototype creation for the dashboard without KPIs being available, and I am quite interested to learn how can the same be facilitated in the workflow. How placeholders can be arranged for different sections of a dashboard ? This feature is called Dundas DashFlow.

One unique part that I recently learned about Dundas was that, it facilitates usage of KPI with charts, gauges and other elements while in performancepoint, usage of KPI is limited just to scorecards. This is one major weapon in the technical arsenal of Dundas against performancepoint.

4) Dundas Dashboard Datasets: This feature does not manage to make an entry into my head at all. It allows usage of database tables in dashboard, claiming that skipping of KPI creation would help shortening development time. This seems like more of a step to provide a spinal cord to the feature I described in point 3. I would definitely like to find out how and why Dundas feels this is helpful.

5) Dashboard and Data Visualization Templates: This can be seen more of like master pages in .NET world or CSS for web pages. I do not find anything great about it, apart from the happiness that it offers templates. I like this template concept as it saves a developers life from getting drowned in learning CSS and decoration related workflows of sharepoint.

6) Data Visualizations: If performancepoint would be hearing this, it's answer would be "Okay, I quit this round". There is no comparison where is comes to data visualizations of dundas, as it's the USP of dundas.

Dundas Dashboard Map can be thought of it's own version of Bing layer. It would facilitate viewing of geographical and geometric data, and to the best of my knowledge performancepoint and bing has never been even discussed together to the best of my knowledge.

7) Mashups: It can be seen as something like the concept our ReportBuilder in SSRS, allowing business users to work on a superlative version of Excel flavor customizations. I feel that Virtual Cubes and Mashups are more or less overlapping.

8) User Annotations: It can be seen as discussion forum on a dashboard ! It might be useful in a way, but to be honest, if I were a power user, I would like my dashboard clean and capture all the analysis in some tracking system like Mercury Quality Center. This still is a good to have feature and it makes the dashboard more interactive, but only if it's left to interactivity and not made a discussion forum.

Check out this page for a view of detailed features of Dundas Dashboards v2.o beta and Dundas video explaining features of this version. This version definitely weighs heavier compared to performancepoint as of the present offering landscape.

SSIS , SSRS , Powerpivot , Sharepoint and .NET with SAP

Today I was going through an article featured on powerpivot-info.com, and I stumbled upon an interesting set of components offered by this company called "Theobald software". Before I go ahead with the entire story, a little background about SAP and why a MS BI geek would be interested in SAP. Every mature and informed professional in BI should be knowing that SAP is the world-class ERP solution for almost every kind of business and Germany can be considered as Mecca of SAP. Now like any Enterprise level products it provides an application level interface and also holds data within itself in a proprietary format. Entire business process workflow for almost any kind of business and any kind of process can be managed using this product, and the biggest USP of this product is that it comes with a proprietary programmable API called BAPI. I can write for an year about this product and still I would fall short, so I would cut this story short here and move ahead to how on earth I know about SAP and what a MS BI guy has to do with it.

I have been working since the past 1 year on a systems integration programme where I am involved in migrating data from a proprietary ERP application to SAP, and the tool we have been using is SSIS for data migration. It has been a very interesting, challenging and one of it's kind of experience as a Technical Lead as this is the first time I am working in such combination of technologies.

Coming back to where we started, this company offers components that can be used as a wrapper between SAP and Microsoft technologies like .NET, SSIS, SSRS, Sharepoint and Powerpivot. I have glided over the features that this product offers and based on my whatsoever little experience of working with SSIS and SAP, below are some points that should be considered before using these or any components while working with SSIS and SAP.

1) Business data in held in proprietary format in SAP, and any change triggers a workflow or a set of processes in SAP. It can be thought of an application equivalent of mainframe systems, and it would take you to be no less than a senior level business analyst or senior SAP functional consultant to change any data into SAP.

2) Based on point 1, it can be deemed that for other applications which may aspire to hook into SAP, like SSIS or SSRS, it can only be a read mode access and not a write mode access.

3) The components that this company provides, claims to have features that facilitate to read data directly from SAP tables. My experience has been that, first you need to have a broad knowledge about what these tables contain, as neither the names would be self-relevant nor it would be that easy to figure out relationships from any table like foreign-keys in a database. For example, in the Materials Management module would have following tables: MARA, MAKT, MBEW, MLTX and others. Even all the column names would be in German. A VC++ programmer would easily co-relate with these kind of nightmares.

4) Another feature that these components offers is of SAP queries, something like our stored procedures. Believe me, you would never require any reporting tool if you are using SAP. Almost any kind of reports can be created with any level of flexibility that can be imagined. To put just one of the fact to the table, just any single module of SAP comes with approximately 800 built-in different kind of reports which can be modified too. Also if the report is provided in Excel format, Excel itself can handle much of the graphical or pivoting features to a considerable extent.

5) If I consider the usage of mySAP portal (which can be thought of similar to Dashboards hosted on Sharepoint created using Performancepoint Dashboard Designer), master data, and BW Cubes which might need to be used and/or analyzed with another system that is hosted on a different platform like Sharepoint, in this case I see a good use of these components to create a BI solution on the top of SAP and any other ERP level product or any data models hosted in a different platform (including Microsoft). I recently read on a whitepaper that Panorama Novaview is able to hook into these SAP ERP Tables, and Microsoft SSIS also has a connector to read from SAP but we do not have any components out-of-box in SSIS / SSRS specially designed to work with SAP.

This company is German in my understanding, has all the German business partners, and claims to have 650 clients out of which half of them are german. But even if I consider the rest, it should be having 300+ clients who are using MS BI integration with SAP, and in my career till date I have worked and even heard of only a single project where SSIS is used with SAP. I really wonder how big is the BI Universe and how smaller is my knowledge !! :) These components are interesting and if you have even any brittle idea of a few terms of SAP, I suggest to check out these components and catalogue them in your list as this cannot be considered less than an MDX library available in the form of Excel Functions ( I should have said DAX in short ).

I plan to publish another post sometime after I recover from the above shock, to share how we use SSIS and SAP R/3 for data migration from a proprietary CRM application that is stored in Oracle Warehouse to SAP for all the modules like Materials Management, Customers, Vendors, Account Receivable, Accounts Payable, Human Resources with Parallel Payroll (the most complex module I have ever worked in my life), Cost Centres, WBS, Fixed Assets, Purchase Orders, GL Balances and many more.

SSAS + Excel Services + SSRS + Performancepoint Services = Powerpivot + Office 2010 + Panorama Novaview ?

The equation mentioned in the subject of this post seems to be something like a combination of keywords that one might throw at Google to find out some results. If you use google with some values with binary arithmetic operators, google might answer the result. But for the above equation, the only engine than can answer the result of the equation are the BI Engines of Microsoft and Panorama.

Panorama has come out with quite a handful of products or product features that works with powerpivot and microsoft office 2010. After going through the features that Novaview offers for leveraging the capabilities of Microsoft Powerpivot, I particularly feel interested with the Universal Data Connector capabilities of NovaView, as it can hook into a variety of data sources ranging from Excel to SAP ERP tables.

My equation might not be precise, but from my initial grazing on the Parorama's datasheets and whitepapers, I have summarized my understanding in a single equation. And as it is said that "Keep your friends close, and your enemies even closer", I like keeping a watch on other competitive (I mean to say Partnering) products. And if you adopt the same philosophy, check out these panorama's integration with microsoft business intelligence whitepapers from here, and one another great source of information that is more precisely banged on target is a post on Ella Maschiach's BI Blog.

Considerations for opting Dundas Dashboards against Performancepoint

Recently I got a query from one of my blog reader which is the topic of this post. I thought of posting it on this blog, for the review from a greater audience. Below are my 1st level thoughts on the same. Feel free to correct me on the same, as these are just my views and not verified facts.

1) Developer expertise with dundas object model is a big concern and blocking factor in speedy and efficient development. Your team would be able to make it but cannot guarantee efficient development and in case of an issue, it would always be a debate whether it's a limitation with product or developer bug.

2) Most of the visual components are based on Silverlight, and scripting would require development team to learn object model for the same.

3) SSRS already has got now Dundas chars and graphs, so just dashboard part of it makes sense for use.

4) Performance impact - You need to develop a few POCs to make out if these components dump out lot of html or huge images like what Infragistics does. Should not be the case as Silverlight is supposed to be a thin client support, but still its a question.

5) If these are planned to be shared over a tool like Sharepoint for collaboration, these components would be treated as just another ActiveX components. Also if you plan to use sharepoint, PPS is already a part of sharepoint 2010.

6) I cannot comment from a pricing point of view, but Dundas Dashboards would come more economic than Sharepoint 2010. Also both cannot be compared as both are totally different areas, and PPS cannot be extracted out from Sharepoint 2010 though this may be your only requirement.

Dundas may definitely win over pricing, but over a technical tug-of-war, the same cannot be guaranteed.

Excel on browser ( Excel Web App ), Powerpivot and Sharepoint

I was reading Andrew Fryer's Blog on a post regarding Powerpivot management and the first sentence said that "The most important thing about PowerPivot is the ability to share users analysis into SharePoint so that these other users can slice and data form within a browser."

For those who are not aware, Microsoft Office 2010 has come out with a new neat feature that can be thought of as a NANO version of Microsoft Sharepoint, but it brings to the table a very important functionality. This application is called Excel Web App and is a light-weight Excel client that allows to collaborate working on excel workbooks more or less like Sharepoint using a browser. Keep in view, that multiple users can work together on it, but it should not go under assumption that it would have all features that are available while working on worksheets that are hosted on Sharepoint. Obviously, it won't have those features as they are not Excel but Sharepoint features.

The point that I am trying to make is that, if Powerpivot can be used in conjunction with Excel Web App, need of Sharepoint can reduced helping small scale organizations to still have the minimal required features like simultaneous collaboration and analysis capability of Powerpivot. I don't think powerpivot is supported from this application as of this draft, as I read on the Excel Webapp overview post comments that "Excel Web App cannot run add-ins built for the Excel client app". Thru the course of evolution or thru some workaround, it can be effectively used to replace Sharepoint if just collaboration of Excel worksheets is the most used feature on Sharepoint for your organization which would reduce huge costs.

I am NO expert at Excel Web App but having worked with Excel Services on Sharepoint Server and having read the post on Excel Web App overview (do not miss reading the comments on this post) and this post on Collaborative editing using Excel Web app from Excel Team, I am very sure of the point that I am trying to make is possible and a practitioner or Excel team can confirm the same. I am happy to learn if there's anyone out there who have got different views on the same. I have posted question on the same to Excel Team, do check out the Excel Web App overview post for the answer from Excel Team.

Data Analysis using Data Mining and Microsoft Silverlight

I posted my first post of this year on the topic of which all technologies would be the focus of this year that would affect Microsoft Business Intelligence. One of those that I listed was Silverlight Applications that would integrate with MS BI Deliverables, and mostly they integarte with the reporting and/or dashboarding modules.

While browsing the web, I stumbled upon a very interesting web based service that provides data mining analysis capabilities, and the visualizations are too good to believe. The interesting point here is these visualizations are developed using Microsoft Silverlight. As I stated in my earlier posts that we have not yet seen a bright out-of-the-box silverlight based tools packaged with microsoft business intelligence stack, but independent vendors have already started finding an excellent use of these technologies.

The only downside of this service is that it provides analysis only over the web in the form of a web service. And one would not want to sent out an entire data warehouse over the web or analyze data of these volume using a web service. Check out yourself these fantastic product which is called Data Applied and below is one it's fantastic visualizations.