Client apps
Client applications to work against Analysis Services
As I was walking through the vendors expo during PASS conference, I saw many interesting products, but two of them caught most of my attention. I am not saying that these two were the best on the floor, it's just that I either already saw other products before, or that they were OLTP oriented, and my main interest is in BI products. By the way, the product which in my opinion stole the show in this PASS - are Data Mining Add-Ins for Excel 2007. They are truly something special - any Excel user can have power of Data Mining without being an expert in Data Mining. But I already saw them before, so they weren't as new to me as to the rest of the audience. The products that I want to discuss here are Q4Bis and RSinteract. I have heard about both of them, but never had a chance to see them in action. I want to extend my thanks to Arpad from Q4Bis and to project manager from RSinteract (whose name I regretfully forgot) for spending tons of time with me patiently answering all of my questions.
Q4Bis
There are actually several different products that this company has in its portfolio, including DataServer - and ETL tool which builds data warehouses and AS cubes; templates for doing it from JD Edwards, Axapta and Microsoft Dynamics AX; Mobile Dashboard - a hosted service to deliver BI to cell phones etc. But I was mostly interested to see Q4Bis Analysis - which is AS client tool in two versions - Desktop and zero footprint Web. Overall you would find all the functionality that you would expect from the OLAP browser, but there was one piece which I especially liked. We live in the days, when the modern search engines revolutionized the way we look for and interact with information. Instead of categorizing the data and using complex UIs to get to it - we simply type what we want to find into a simple text box, and let the search engine to find the most relevant answers. Sure nothing can be simpler than a text box ! So I am somewhat surprised why BI products - the very products whose mission is to help users to find relevant information, don't embrace the Search paradigm. People are already familiar with it, and people don't like learning new complex things (take MDX, for example, it is a beautiful language, yet most people are reluctant to dive into it :). At best, BI vendors do provide some search functionality, but it is buried inside menus and submenus multiple clicks away from where user can find it, instead of being prominently placed like a search text box in IE7. Now, I am not an expert in UI. In fact, when AS team members want to make a joke on me - they say "We will let Mosha design the UI". But when I saw how Q4Bis put a search box and a magnifying glass icon next to it (the same icon as everybody is used to see on www.live.com) - I knew that this was exactly the right thing:
We searched for the word "garden". Q4Bis intelligently understood the context of the search based on the current view. There were 3 different hierarchies on the screen, so it started to search only inside these hierarchies. As a result, there were 3 products which contained the word "garden" in their names
I think this is brilliant. Arpad showed me that it is possible to make more advanced searches, and search within results etc, but I think that the most powerful feature is not in the advanced staff, but in simple staff. They've made search to be simple to use, yet intelligent enough to be relevant.
After that we went over the generated MDX statements. Querying dimensions is one of my favorite subjects, I even started series of blogs about it, but so far had only time to write the first one in the series. Nevertheless, I made couple of suggestions how to improve the the MDX, hopefully they will find those useful and incorporate into the product at some stage. More information about the product is available from the www.q4bis.com website.
RSinteract
This is a relatively new product, which works with Reporting Services. It doesn't do its own rendering or formatting, but generates RDL for Reporting Services to render and format. So the purpose is pretty much the same as with ReportBuilder, but RSinteract is clearly easier to use. They added support for AS data sources just couple of months ago, and I obviously spent most of my time in that area. BTW, in the marketing materials, I found the best resolution of the UDM vs. cube terminology argument. They simply state, that RSinteract supports UDM cubes for both 2000 and 2005 versions :) The UI for "UDM cubes" takes traditional OLAP approach, as opposed to ReportBuilder. I.e. ReportBuilder presents different measure groups as different entities, and dimensions are entities being linked to them etc. Perhaps this is appealing approach for people familiar with relational schemas, but to me RSinteract approach was much more intuitive. If we do a report against cube - let it look like cube with measures and dimensions ! RSinteract doesn't aim to be general purpose OLAP browser, but some of the classic functionality of OLAP browsers is there - you can pick any number of hierarchies into filter, and select one or more members out of them. RSinteract automatically converts this into RS parameters. We reviewed the generated MDX statements, and they were very clean. RSinteract also has chosen an interesting way to support drillthrough. Since they allow to choose multiple members of same level in filter, the DRILLTHROUGH statement wouldn't work, since drillthrough only works on single cell (I hope this limitation is lifted in the next version). So RSinteract generates straight SELECT with subselects to incorporate filter and using STRTOSET(@p1, CONSTRAINED) to decode parameters. Everything is a good choice for this scenario (I hope in the next version AS supports rich data types on the parameters, so STRTOSET calls won't be necessary anymore). We discussed the important differences between DRILLTHROUGH and SELECT statements with respect to how they treat calculations. DRILLTHROUGH will ignore any calculations and go straight to the fact table of measure group. But since RSinteract works with a cube as a whole - it is only logical to send queries to the cube and not to measure group. I enjoyed talking with the RSinteract representative. He told me that he is just project manager, but it looked to me that he is from the engineering team, since he was able to answer all the technical questions I had. Unfortunately the expo was closing at that time, so I didn't have a chance to take screenshots. Some of them are available at www.rsinteract.com website, but they are all examples for relational data sources, and not for OLAP.
When Microsoft bought Proclarity, there were much speculation in the news, blogs, forums etc about what it would it mean to other third party tools. There were some predictions that the number of client tools to Analysis Services will shrink. Yet, 6 months later, we see that the list of such tools is longer then ever, and new products keep coming to the market. One of such new products is RadarSoft, which just shipped Analysis Services enabled version in September 2006. RadarSoft is a company which developed HierCube - a proprietary OLAP engine and RadarCube - library of client UI components. One unique thing about RadarCube was that it was probably the only OLAP control for VCL on the market. While there are probably not as many Delphi programmers these days as there are C# or Java programmers, Delphi programmers are known for their strong (almost religious) attachment to the platform. Borland has always been a developer-friendly company. So Delphi developers who wanted to embed OLAP functionality into their applications could use RadarCube VCL control, but previously it could only connect to the HierCube OLAP engine. So these developers have been asking RadarSoft to provide connectivity to Analysis Services, and RadarSoft delivered. It released version of RadarCube.VCL which works with Analysis Services (both 2000 and 2005 versions, although 2005 is recommended). Here is a sample screenshot how the browsing of the AdventureWorks cube looks like
But RadarSoft didn't stop there. They rightfully realized, that the community of ASP.NET developers is much bigger then the community of Delphi developers. And that there is a great need in good ASP.NET control which can be hooked to the Analysis Services cube. So they released RadarCube ASP.NET control for Analysis Services too. It is modern AJAX based UI with multibrowser support (IE 5.5 and higher, FireFox and Opera too). It has support for AS2005 features such as KPIs and attribute hierarchies etc. It integrates with Dundas charting control in order to show charts. Below is the sample screenshot of what can be done with this control:
Some people think that "the world doesn't need another OLAP client". I disagree. We are still far away from the intuitive, powerful OLAP client which generates clean and efficient MDX. But it is true that there are plenty of existing clients. What we miss most now are controls embedable in the application, such that developer can just drop this control inside the application, point it to the cube, and don't think about all those tricky UI and MDX issues. For many years, OWC was fitting this role for the thick applications (as opposed to Web apps). OWC had its own set of pluses and minuses, but OWC is being discontinued now (although OWC11 will be still supported until 2011 and on extended support until 2016). OWC was native Win32 code, implemented as ActiveX control and it used OLEDB for OLAP. It was first developed in 1997, and since then new technologies were born. The major breakthrough in UI technologies is, of course, Avalon, or Windows Presentation Foundation (WPF) as it is officially called. I, therefore, was excited to discover XAML based control called PivotTable built with WPF. My Vista machines are in the office, and my laptop is still on Windows XP, but luckily WPF can be installed on Windows XP SP2 by downloading .NET framework 3.0 RC. I promptly installed it on my machine and downloaded the source code for this XAML PivotTable control. In order to compile it in VisualStudio I also had to download Orcas CTP which contains the preview of Visual Designer for WPF (codename Cider). After going through all this hassle I was really looking forward to see how PivotTable control exploited power of WPF. The resulting UI disappointed me somewhat - it is your standard grid with the right pane for metadata. No 3D effects or animations etc. I guess only the gradient shades in the grid were the only indication that it is built with WPF. The MDX generation of this control is pretty bad - for example if you select single measure, it would still put all the measures in the axis, which, especially in the case of Adventure Works, is a big performance hit. But they I browsed through the source code, and realized that this control is just a sample code, which was built by an intern during last summer in Microsoft (the alias of the author is t-tomkm, and "t-" prefix is given to interns in Microsoft). He probably built 10 other samples as well, the intent was not to built the killer OLAP client control, but showcase WPF technologies, and in this case data bindings through ADOMD.NET. Since all the source code is open - hopefully somebody will pick it up and develop into something more real. But even in the current form, it is already usable in some simpler and less demanding applications. Download it and judge for yourself. Below is a sample screenshot:
The most interesting part of the announcement is
Specifically, Hyperion customers will be able to use SQL Server Reporting Services to access Hyperion System 9 BI+ Essbase Analytics, and also will be able to display Microsoft reports in the Hyperion System 9 Workspace environment. In addition, Microsoft customers will be able to access SQL Server Analysis Services data using Hyperion System 9 BI+. Also, Hyperion System 9 will leverage SQL Server Integration Services for data access.
So, I guess, soon I will add Hyperion System 9 BI+ to the list of clients for Analysis Services at
http://www.mosha.com/msolap/util.htm. Microsoft and Hyperion while are competitors in the OLAP Servers market, have experience of working together. In 2001 Hyperion joined Microsoft in support for XMLA standard. As a Microsoft representative in XMLA council, I worked closely with Hyperion team, and it was very healthy and productive cooperation - together we finished XMLA 1.0 and built XMLA 1.1 standards. And I beleive it is that work that we invested into writing the standard, developing its implementations and doing vendor interop etc, that ultimately enabled this announcement. I am really happy about this development, as I beleive that such cooperation, especially on the standards, benefit everybody. And the customers, both ours and Hyperion's, will have more choice and more interop.
P.S. It is symbolic in a sense. I still have my "Wired for OLAP" t-shirt from 1997 which I got when AppSource announced support for OLAP Services 7.0. Of course, it was later bought by Arbor, which merged with Hyperion, and support for OLAP Services/Analysis Services was discontinued then. Glad too see it coming back after 9 years :)
Surprisingly nobody blogged about it yet, but today Microsoft announced that it agreed to acquire Proclarity.
This is a significant boost to the Microsoft overall BI vision and strategy.
If you think you have seen all of the Web based thin clients for AS2005 - you haven't. Some time ago I
hinted that I saw new client tool which I couldn't disclose at that time. Well, now I can. This client application comes from Chinese company
USOFT. Similar to
StrategyCompanion, it delivers very rich user experience through the Web browser, and it is very fast too. Also it features very complete support for Analysis Services 2005 features such as KPIs etc. Unfortunatelly, the USOFT Web site is only in Chinese and no English version. You could get more information from
here, but it is also in Chinese only - well at least there are plenty of screenshots over Adventure Works cube, as well as some architectural diagrams with English labels on them. Below is one sample screenshot:
This summer, I was lucky to get in contact with CTO of Strategy Companion Corp, who showed me their new product - Analyzer 2005. I have seen slick Web based thin browsers before, but this one looks absolutely amazing. It is pure ASP.NET/DHTML, and it is written specifically for Analysis Services 2005. There is support for almost every client oriented feature we have - multiple translations, display folders, KPIs, etc. They already have 70% market in Taiwan, and now are looking to expand internationally. The web site for this product has gone public since then, so you can visit it and judge for yourself -
www.strategycompanion.com. Below is a screenshot of Analyzer 2005 working against Adventure Works sample cube.
Last week
ExoLogic annonced general availablility of the CubePort product. CubePort is a wizard driven application which migrates Essbase cubes to Analysis Services cubes. Previously CubePort was available only with assistance from the consultant, but with the current version improvements it can be run directly by the customers. CubePort is pretty sophisticated application, it migrates most aspects of Essbase application - outlines, loadrules, report scripts, security (users/groups/filters), substitution variables, complex member formulas and more. The picture below shows the conceptual architecture of the CubePort, with more details available at
http://www.exologic.com/products.htm. Now moving away from Essbase to Analysis Services became easier then ever !
I have worked with ExoLogic engineers in the past, and I can tell, that they have very deep knowledge of Analysis Services, one of the best in the consultance business (
http://www.mosha.com/msolap/consult.htm) and I am sure this expertise will show itself in CubePort.
In this
whitepaper Hermann Däubler he exlplains step by step how to load data from SAP BW into Analysis Services cube using SAP's Open Hub Services. To me SAP BW always looked like something very complex that I didn't even know how to approach, so I was excited to see documentation which explained exactly that. Here is the summary of the whitepaper:
Summary
The following paper should help to get a quick start on how into an external Microsoft Analysis Services MOLAP cube Service. Unlike using SAP .NET Connector to access one necessary to write any code. Open Hub Service is the officially unload data from SAP BW into external data targets.
As an example, here are two screenshots of the hierarchy tree, first is the original hierarchy in BW and the second one - the migrated hierarchy in Analysis Manager.
New addition to already long list of Analysis Services clients at
http://www.mosha.com/msolap/util.htm .Yesterday I saw press release from DataJungle, that they added support to the Microsoft BI stack - Analysis Services, Reporting Services, Sharepoint etc to their flagship product Matrix 3.0. Previously, DataJungle only supported Cognos, but now they extended the support to Microsoft platform. Here is the nice quote from their COO: “
The Microsoft BI market is probably already the largest and fastest growing segment in the BI industry. Our entry into this market with such a compelling and unique offering takes DataJungle to a whole new level as a company”. Matrix - is a pure thin client Web browser based solution, but the quality of the UI in screenshots is really impressive. I don't know what technology did they use, but if it is pure DHTML - they've done great job. I probably saw only one more product which does comparable UI in DHTML, but this product is not released yet, and I cannot disclose which company implemented it. Example of screenshot is on the right. There were no trial download on the website, so I couldn't try it with Yukon.
The press release can be found here -
http://www.datajungle.com/news.htm
Everybody who is interested in OLAP knows about Nigel Pendse's "OLAP Report" - at www.olapreport.com. 4 years ago, in addition to publishing OLAP Report, he also started another project - OLAP Survey. There were 4 OLAP Surveys since then - one each year. Every year it became bigger - more participants, more questions in survey, more analysis, more pages in report etc. Microsoft has bought all the copies of OLAP Survey, and we went page by page over all of them. Even though Microsoft had rights to use the results in the marketing materials, our marketing didn't do it (which is a shame, since Analysis Services has won 1st or 2nd place in most categories of the survey). However, we, the product team, have studied it extensively, both the areas where we have done well, and the areas where we needed to improve. The participation in OLAP Survey has always been good, but it always has been closed survey, i.e. by invitation only. I guess this was done to ensure that vendors don't influence the results by convincing their customers to fill answers which would be favorable to the vendor. But this year Nigel decided to choose different strategy. Now he encourages vendors to spread the word about the survey, and the link to it is open to everybody. This way, OLAP Survey 5 promises to get much more people to answer then in any previous year. Of course, like before the vendors (including Microsoft) don't have any input in formulating the survey's questions so it still stays independant and creditable. As part of "spreading the word" company, Nigel asked me to publish this information in my blog and on www.mosha.com/msolap, which I happily do. Please file the survey and tell us all the good and bad things about Analysis Services. I personally will be going over the results.
The links are:
English version: http://www.survey.com/olap5sur2.html
German version: http://www.survey.com/olap5survey2.html
The official text of customer invitation follows:
Customer Invitation
We would very much welcome your participation in The OLAP 5 Survey. This is the largest independent survey of OLAP users worldwide. The Survey will obtain input from a large number of users to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical respondents are welcome.
The OLAP Survey is strictly independent. While Microsoft and other vendors assist by inviting users to participate in the Survey, the vendors do not sponsor the survey, nor influence the questionnaire design or survey results. As a participant, you will not only have the opportunity to ensure your experiences are included in the analyses, but you will also receive a summary of the results from the full survey. You will also have a chance of winning one of ten $50 Amazon vouchers. Click here to complete the survey on-line.
Back in 1997, when OLAP Services 7.0 was still in Beta there were no client tools to work with it. Back then I received email from far New Zeland - it was from Richard Lees, who worked in New Zeland's subsidary of Microsoft MCS. He wrote that he developed ASP based thin OLAP browser called “ThinSlicer”. ThinSlicer was available for everybody together with source code, and it was in facto the only publicly available reference implementation of thin client for OLAP cubes until another Richard, Richard Tkachuk, wrote his version called AnalysisServicesThinWebClient which was included in the
SQL Server 2000 Resource Kit. This friendly competition between two Richards went for some time. Recently, as readers of my blog are aware, Richard Tkachuk released new incarnation of his project, this time in the form of ASP.NET control called
CellSetGrid. Response from Richard Lees didn't take long. He wrote me this week, that he now also completed .NET version of
ThinSlicer, also freely available with source code. In addition, Richard also has
live demonstration of ThinSlicer working against datasets such as New Zeland census, Jim Gray's SkyServer weblogs and more. BTW, it is also showcase of Analysis Services running on Itanium2 box, and soon all of it will move to Yukon build. When
Jim Gray saw them, he said the following:
This guy in New Zeeland (Richard Lees) harvests the Skyserver weblogs from Fermilab and JHU via a webservice every hour (much as we do at http://skyserver.sdss.org/log/en/traffic/). But he puts them in an SQL OLAP cube (rather than a boring SQL tabular database). The results are stunning! very crisp and clear reports that allow us to explore (drill down into and pivot on) the 75M web hits and 13M SQL queries.
Nigel Pendse updated his olapreport site few minutes ago with the provisional OLAP market share numbers for 2004. I have been eagrly waiting for it , checking his site every day last month (unfortunatelly there is no RSS feed in it). Analysis Services holds firmly on the first place, and further increased its market share - which means that it grows faster then the market. Full analysis is as always at Nigel's site: http://www.olapreport.com/market.htm
