Monthly Archives: November 2006
OLAP Warehouse Project
Client Tools: The Next Generation
After the Proclarity acquisition earlier this year, my thinking was that while it was a good thing overall for the Microsoft BI community it would have the negative effect of reducing choice and innovation. But in the months since I’ve been surprised to see a steady stream of new third party applications enter the market. Some, like RSInteract, I’ve mentioned before, but since there are quite a few now that are worthy of mentioning I thought I’d round up a few in a single posting.
Radius, from 90 Degree Software, has got a bit of exposure over the last few days thanks to Russell Christopher’s blog entry. Russell sums up its features pretty well and I have to say I was quite impressed by the various webcasts I’ve seen – I like the idea of being able to reuse pieces of other people’s reports while building your own. I’m not sure it supports AS as a data source at the moment, alas, but I was told that they are planning on doing so soon. While the general rubbishness of Report Builder can only help the prospects of Radius it also highlights the point I want to make here quite well: although Microsoft have now killed the market for traditional, vanilla BI apps, if you are innovative and execute your ideas efficiently there are still going to be openings for you.
Of course visualisation has been another area where client tools have sought to set themselves apart from the pack over the last year. I blogged about Tableau a while ago and Fractal:Edge the other week, and more and more other tools are improving their visualisation capabilities. Take a look at the pie-chart tree report or the data mining report demos at http://www.reportportal.com/ for instance. It’s not surprising that the likes of Dundas (disclosure: I recently signed up to their partner program and got a freebie chess set in the post from them this morning) are also entering this space – Dundas OLAP 5.5 just got released, and has some cool features like the ability to draw freehand over or add comments and arrows to a report; I see Chart FX have something similar, Chart FX OLAP, but I’ve not checked it out yet.
Another way to distinguish your product is to focus on a particular niche. I saw a demo of Intelligencia the other week and initially couldn’t see what it offered beyond any other more general-purpose tool. However it’s the details that are important, and this tool is targetted specifically at financial users who want to create printable reports for regulatory (Sarbanes-Oxley etc) reasons. This is something that you’d struggle to do with, say, Reporting Services, and having the tool as a Word 2007 addin makes a lot of sense; the OLAP querying functionality is also aimed at financial-style reports, making it easy to build up asymmetric sets of tuples for example. Also in the financial apps space is the more general CPM tool Calumo; looks impressive and is probably more sophisticated than Biz#/PerformancePoint will be, and I suppose the advantage of being a small software company in this area is that you can respond quickly to your customers’ requests and try to stay one step ahead of Microsoft.
So, then, a thousand flowers are blooming and all that. Having cool features does not necessarily entail commercial success but I wish all these tools well and I’m pleased to see that there’s still a lot of diversity out there – it can only benefit us all. And remember, if you’ve got a product that uses Analysis Services and you want a bit of free publicity on this blog then please send me details…
DDD4
For DDD4 we are organising Grok Talk and Park Bench lunch time activities at which we hope to encourage delegates to participate. You can help!
The Grok Talks are open to anyone, a wiki has been setup where people can put down their name and the talk and/or suggest topics for the Park Bench. If you are attending DDD4 we would like to invite you to submit a Grok Talk. As we have had a low response so far we would appreciate it if you could blog about this opportunity or mention it to anyone you know who might be interested. As a reward on the day a MSDN subscription will be given for the best Grok Talk.
Wiki link – http://www.thehughpage.com/DDD4LunchTime
Processing Dimensions By Attribute and By Table
It was recently pointed out to me (by Hugo Lopes and Paulo Faria of Enabler in Portugal, who I had the pleasure of working with recently) that setting the ProcessingGroup property of a dimension can have quite a big impact on the amount of time it takes to process a dimension. By default this property is set to By Attribute, which means that each attribute in the dimension is processed separately using its own SQL Select statement which returns the distinct values for each member on the attribute. Here’s an example query used to process an attribute based on the Adventure Works Product dimension:
SELECT
DISTINCT
[dbo_DimProduct].[Color] AS [dbo_DimProductColor0_0]
FROM [dbo].[DimProduct] AS [dbo_DimProduct]
If you set it to By Table, however, in some circumstances (when your dimension is built from a single table?) only one SQL Select statement per table in the dimension structure is issued, reading all the data in, with AS presumably finding the distinct values itself.
When do you use which setting? I suppose it depends on a lot of things (performance of your data source, size and structure of your dimension, whether you’re processing other objects at the same time) so it’s going to be one of those things you’ll have to experiment with. Obviously most dimensions process extremely quickly anyway, but if you’ve got a large dimension which you need to process as quickly as possible this might come in handy.
Optimising Distinct Count
Custom Reports in SP2
Fractal:Edge
What’s new in SP2
Analysis Services
- Microsoft Office 2007 requires the installation of SQL Server 2005 Analysis Services SP2 to support all of its business intelligence features. Features of Microsoft Office 2007 that require SP2 will be disabled when running against an instance of Analysis Services that does not have SP2 installed.
- The performance of local cubes, grouping, and subselects have been substantially improved.
- MDX query performance has been improved with respect to subselects, arbitrary shapes, running sum calculations, visual totals, ROLAP dimensions, cell writeback, many-to-many dimensions, 64-bit NUMA hardware, semi-additive measures and unary operators.
- The functionality of subselects has changed.
- A warning message now appears when a user-defined hierarchy is not defined as a natural hierarchy.
- The MDX Drilldown* functions have a new argument that allows you to specify drilldown on specified tuples only.
- The SCOPE_ISOLATION property has been added to the MDX CREATE MEMBER function.
This property enables session-scoped and query-defined calculations to be resolved before calculations in the cube rather than after.
- Numerous functionality and performance-related bugs have been incorporated.
Specifically, improvements have been made to incremental processing, usage-based aggregation design algorithms, backward and forward compatibility, parent-child security, partition query scalability, cell writeback and the Time Intelligence Wizard.
- The performance and functionality of the neural network viewer has been improved and support for multiple nested tables has been added.
- The performance of naïve bayes predictions have been improved through caching of commonly used attributes.
- Neural network training has been improved through better utilization of memory with sparse training data sets and better utilization of multiple threads during error computation (SQL Server 2005 Enterprise Edition feature).
- Limited support for data mining viewers with local mining models has been added.
- The redistribution of data mining viewer controls is now dependent upon ADOMD.NET.