Andrew Wiles Olap Warehousing Blog

As I mentioned in my last blog post one of the nice side effects of implementing the drillthough capability in Intelligencia Query has been access to the dmv objects (mdschema tables) in SSAS. To show what this allows you to do we have been working on a generic cube browser for Reporting Services which can simply be hooked up to an OLAP database and will allow end-users to build their own reports.

 

I have made a short video showing how this looks.

Our intention is to ship this with the sample databases for IQ in the near future. It should be fairly simple to modify and customize the rdl for the browser to create a variety of other reports.

I was involved in giving a training session to a partner in Norway earlier this week (HANDS AS - very nice people with an interesting solution for Dynamics customers!) when the subject of implementing drillthrough reports from SSAS in Reporting Services came up. This is an interesting problem as the MDX submitted for a drillthrough returns a recordset rather than a cellset. The OLEDB provider will return recordsets for drillthrough and dmv queries but it is not possible to specify parameters in these queries as the interface will throw exceptions. The Microsoft Analysis Services data source also throws an exception when presented with this scenario and indeed so did Intelligencia initially. After a little work however we have modified the Intelligencia Query datasource to check for the return type of the query and construct a dataset from a tabular recordset if needed. The result is that you can now do "proper" drillthrough using SSAS in SSRS. The following video shows how it works:

 

 

Another nice side-effect of this change is the ability to select cube metadata from the dmv objects using mdschema queries using parameters. For example the statement:

 

select * from $system.mdschema_cubes

WHERE [CATALOG_NAME]=@DatabaseName

 

Will return a list of cubes in the specified database. This opens up quite a lot of possibilities using the mdschema queries to populate parameter datasets.

During the last week or so I have at long last set some time aside to do some work with Silverlight. I have been wanting to do this for quite a long time but simply couldn't find enough time. Anyway - here are my first impressions:

 

For a .NET winforms programmer Silverlight is most definately the route to the web. I have done a certain amount of ASP and ASP.NET programming in the past and have never felt comfortable with the tools. HTML + CSS + javascript etc. always felt to me like bailing wire + gaffer tape + rubber bands a lashed together toolkit with many opportunities for failure. I also took a look at AJAX a while back and the unpleasant feeling was magnified even further. On the occasions that I have strayed into this world I have also found the development environment to be horribly dis-jointed making debugging and testing unneccesarily difficult.

 

Silverlight is winforms, or to be more accurate WPF for the web, an integrated development environment in which it is straightforward to create rich content applications without having to bridge multiple, disjointed, programing environments. You have complete control over application appearance without having to fight against the random behavior exhibited by HTML. Deployment, which was always an issue for winforms apps, becomes a non-issue, plus it is a complete joy to use C# as a consistent language for both the client and server. For the first time in my career I feel like I might become a web developer!

 

It is not perfect, of course. For example I am not a fan of the split between Visual Studio and Expression. Whilst I understand the desire to provide designers and developers with their own tools the result is that neither environment is complete. This leads to a lot of unneccesary switching between tools whilst generating the basic layout for controls. I am neutral about working with xaml. It is not very pleasant to work with directly but not unduly difficult. As the tools improve it will become less neccesary to work directly with xaml anyway.

 

What I do find interesting is that Microsoft do not seem to have made the most of Silverlight's capabilities in their marketing. When you look at the Silverlight resources there is an over-empahsis on video and graphics manipulation which pitches to the consumer market. Where I believe Silverlight will be a killer is for business applications where it will be possible to create robust data-bound apps which can dramatically change the end-user experience for "information workers". These apps can be delivered using a "small" core skill set which in turn will keep costs down and limit the opportunity for failure as a result of skill shortages when development team members move on. Silverlight for enterprise applications is a combination that will be difficult to beat in the future.

 

Anyway, I will look forward to posting a working app on this blog in the near future and hopefully this will give me a reason to be a mot more active than I have been recently.

I have just completed some interesting new functionality which I would like to share.

 

For some time we have supported a simple form of parameterized commentary in our Word add-in. This allows parameterized text to be added to a document effectively keyed against the filter axis for the queries embedded in the report.

 

The new functionality allows for the definition of commentary keyed against any combination of members in the cube along with a new user interface available in the "Investigator" query builder. The screenshot below will give an idea of how this looks:

 

 

The commentary is stored in a SQL table and accessed via a web service. Clr stored procedures and functions are used to handle the matching of record keys which can contain multiple member values to define either an individual cell or a sub-cube. The Clr proceudures handle efiicient sorting and matching of key elements.

 

The text is stored in RTF format which allows user formatting whilst being fairly easy to convert to the target formats we need for different applications (Word, Excel, Investigator and HTML).

 

As with all new projects the completion of this first phase of work has raised dozens of smaller opportunities, not least of which is the challenge of how we might "aggregate" comments for high level reporting. If anyone has any thoughts on that subject I would be very interested to hear them :-)  

This week I have been following the discussions that broke out after Microsoft announced it was dropping its PerformancePoint planning product.  Like most people I was surprised at this decision. The first iteration of PerformancePoint clearly had some weaknesses but Microsoft has a good track record of improving software once they have the benefit of live customer feedback and I fully expected the second release to be pretty good.

I have tried on several occasions to “get into” PPT but determined fairly early in the process that it was not a product that one could learn by dipping in and out for half an hour at a time. I have therefore restricted my research to areas where iT-Workplace may be able to interface its products. From this I have learned that:

1.       PerformancePoint implements a layer of “PPT proprietary” middleware that sits between Excel and Analysis Services and handles the data entry.

2.       There were no published apis available to Microsoft partners wishing to extend or enhance the PerformancePoint data entry and modelling experience.

In a way I am grateful for this closed architecture. If the design was more open iT-Workplace would probably be one of those partners who are now looking at writing off an 18month investment in new technology. I know of Systems Integrators that have made a substantial bet that PerformancePoint would be driving new business for the coming year and would not want to be in their position of writing a new business plan whilst staring the world recession in the face.

Certain comments have been made that Microsoft has a good reputation for compensating customers when they delete a product line. I would like to think that they will also look at some practical assistance for partners who have been affected by this decision.

One thought is that they might release the code base for PerformancePoint to the open source community and provide some resources to get an open source project set up which could at least provide bug fixes and minor enhancements to customers who do not have the budget to implement another planning solution in the short term. This might even be a path to plugging the product gap left by PerformancePoint and preserving the completeness of the Microsoft BI solution.

I am interested to hear whether other people would be interested in such an initiative and would be happy to make representations to Microsoft if it was deemed viable.