Julian Hyde on Streaming Data, Open Source OLAP. And stuff.

Changes to Mondrian's caching architecture

2012-01-14T16:05:00.000-08:00

I checked in some architectural changes to Mondrian's cache this week.

First the executive summary:

1. Mondrian should do the same thing as it did before, but scale up better to more concurrent queries and more cores.

2. Since this is a fairly significant change in the architecture, I'd appreciate if you kicked the tires, to make sure I didn't break anything.

Now the longer version.

Since we introduced external caches in Mondrian 3.3, we were aware that we were putting a strain on the caching architecture. The caching architecture has needed modernization for a while, but external caches made it worse. First, a call to an external cache can take a significant amount of time: depending on the cache, it might do a network I/O, and so take several orders of magnitude longer than a memory access. Second, we introduced external caching and introduced in-cache rollup, and for both of these we had to beef up the in-memory indexes needed to organize the cache segments.

Previously we'd used a critical section approach: any thread that wanted to access an object in the cache locked out the entire cache. As the cache data structures became more complex, those operations were taking longer. To improve scalability, we adopted a radically different architectural pattern, called the Actor Model. Basically, one thread, called the Cache Manager is dedicated to looking after the cache index. Any query thread that wants to find a segment in the cache, or to add a segment to the cache, or create a segment by rolling up existing segments, or flush the cache sends a message to the Cache Manager.

Ironically, the cache manager does not get segments from external caches. As I said earlier, external cache accesses can take a while, and the cache manager is super-busy. The cache manager tells the client the segment key to ask the external cache for, and the client does the asking. When a client gets a segment, it stores it in its private storage (good for the duration of a query) so it doesn't need to ask the cache manager again. Since a segment can contain thousands of cells, even large queries typically only make a few requests to the cache manager.

The external cache isn't just slow; it is also porous. It can have a segment one minute, and forget it the next. The Mondrian query thread that gets the cache miss will tell the cache manager to remove the segment from its index (so Mondrian doesn't ask for it again), and formulate an alternative strategy to find it. Maybe the required cell exists in another cached segment; maybe it can be obtained by rolling up other segments in cache (but they, too, could have gone missing without notice). If all else fails, we can generate SQL to populate the required segment from the database (a fact table, or if possible, an aggregate table).

Since the cache manager is too busy to talk to the external cache, it is certainly too busy to execute SQL statements. From the cache manager's perspective, SQL queries take an eternity (several million CPU cycles each), so it farms out SQL queries to a pool of worker threads. The cache manager marks that segment as 'loading'. If another query thread asks the cache manager for a cell that would be in that segment, it receives a Future<SegmentBody> that will be populated as soon as the segment arrives. When that segment returns, the query thread pushes the segment into the cache, and tells the cache manager to update the state of that segment from 'loading' to 'ready'.

The Actor Model is a radically different architecture. First, let's look at the benefits. Since one thread is managing an entire subsystem, you can just remove all locking. This is liberating. Within the subsystem, you can code things very simply, rather than perverting your data structures for thread-safety. You don't even need to use concurrency-safe data structures like CopyOnWriteArrayList, you can just use the fastest data structure that does the job. Once you remove concurrency controls such as 'synchronized' blocks, and access from only one thread, the data structure becomes miraculously faster. How can that be? The data structure now resides in the thread's cache, and when you removed the concurrency controls, you were also removing memory barriers that forced changes to be written through L1 and L2 cache to RAM, which is up to 200 times slower.

Migrating to the Actor Model wasn't without its challenges. First of all, you need to decide which data structures and actions should be owned by the actor. I believe we got that one right. I found that most of the same things needed to be done, but by different threads than previously; so the task we mainly about moving code around. We needed to refine the data structures that were passed between "query", "cache manager" and "worker" threads, to make sure that they were immutable. If, for instance, you want the query thread to find other useful work to do while it is waiting for a segment, it shouldn't be modifying a data structure that it put into the cache manager's request queue. In a future blog post, I'll describe in more detail the challenges & benefits of migrating one component of a complex software system to the Actor Model.

Not all caches are equal. Some, like JBoss Infinispan, are able to share cache items (in our case, segments containing cell values) between nodes in a cluster, and to use redundancy to ensure that cache items are never lost. Infinispan calls itself a "data grid", which first I dismissed as mere marketing, but I became convinced that it is genuinely a different kind of beast than a regular cache. To support data grids, we added hooks so that a cache can tell Mondrian about segments that have been added to other nodes in a cluster. This way, Mondrian becomes a genuine cluster. If I execute query X on node 1, it will put segments into the data grid that will make the query you are about to submit, query Y on node 2, execute faster.

As you can tell by the enthusiastic length of this post, I am very excited about this change to Mondrian's architecture. Outwardly, Mondrian executes the same MDX queries the same as it ever did. But the internal engine can scale better when running on a modern CPU with many cores; due to the external caches, the cache behave much more predictably; and you can create clusters of Mondrian nodes that share their work and memory.

The changes will be released soon as Mondrian version ~~3.3.1~~ 3.4, but you can help by downloading from the main line (or from CI), kicking the tires, and letting us know if you find any problems.

[Edited 2011/1/16, to fix version number.]

olap4j moves to Apache License

2012-01-04T11:22:00.000-08:00

We've decided to change olap4j's license to the Apache Software License, Version 2.0 (ASL).

I hope to find time to write a post describing in more detail the rationale for the license change and the choice of license. For now, suffice it to say that the license change will be good for olap4j's adoption and long-term success, and good for its users. ASL is a very permissive license, and is arguably becoming the standard license for open source enterprise software.

The committers have agreed to the license change, and we will be making the changes to the code base over the next few days and making a point release under the new license shortly.

Happy new year everyone.

Julian

How Mondrian names hierarchies

2011-08-24T10:40:00.000-07:00

You may or may not be aware of the property mondrian.olap.SsasCompatibleNaming. It controls the naming of elements, in particular how Mondrian names hierarchies when there are multiple hierarchies in the same dimension.

Let's suppose that there is a dimension called 'Time', and it contains hierarchies called 'Time' and 'Weekly'.

If SsasCompatibleNaming is false, the dimension and the first hierarchy will both be called '[Time]', and the other hierarchy will be called '[Time.Weekly]'.

If SsasCompatibleNaming is true, the dimension will be called '[Time]', the first hierarchy be called '[Time].[Time]', and the other hierarchy will be called '[Time].[Weekly]'.

As you can see, SsasCompatibleNaming makes life simpler, if slightly more verbose, because it gives each element a distinct name. There are knock-on effects, beyond the naming of hierarchies. The most subtle and confusing effect is in the naming of levels when the dimension, hierarchy and level all have the same name. If SsasCompatibleNaming is false, then [Gender].[Gender].Members is asking for the members of the gender level, and yields two members. If SsasCompatibleNaming is true, then [Gender].[Gender].Members is asking for the members of the gender hierarchy, and yields three members (all, F and M).

Usually, however, Mondrian is forgiving in how it resolves names, and if elements have different names, it will usually find the element you intend.

The default value is false. However, that leads to naming behavior which is not compatible with other MDX implementations, in particular Microsoft SQL Server Analysis Services (versions 2005 and later).

From mondrian-4 onwards, the property will be set to true. (You won't be able to set it to false.) This makes sense, because in mondrian-4, with attribute-hierarchies, there will typically be several hierarchies in each dimension. We will really need to get our naming straight.

What do we recommend? If you are using Pentaho Analyzer, Saiku or JPivot today, we recommend that you use the default value, false. But if you are writing your own MDX (or have built your own client), try setting the value to true. The new naming convention actually makes more sense, and moving to it now will minimize the disruption when you move to mondrian-4.

I am just about to check in a change that uses a new, and better name resolution algorithm. It will be more forgiving, and standards-compliant, in how it resolves the names of calculated members. However, it might break compatibility, so it will only be enabled if SsasCompatibleNaming is true.

Are you using this property today? Let us know how it's working for you.

Real-Time Seismic Monitoring

2011-07-22T10:51:00.000-07:00

Marc Berkowitz wrote a blog post describing an application of SQLstream to power a seismic monitoring project that is a collaboration between several leading research institutions.

The project is interesting in several respects:

The project involves signal processing. Unlike the "event-processing" application that we see most often at SQLstream, events arrive at a regular rate (generally 40 readings every second, per sensor). In signal processing, events are more likely to be processed using complex mathematical formulas (such as Fourier transforms) than by boolean logic (event A happened, then event B happened). Using SQLstream's user-defined function framework, we were easily able to accommodate this form of processing.
It illustrates how a stream-computing "fabric" can be created, connecting multiple SQLstream processing nodes using RabbitMQ.
One of the reasons for building a distributed system was to allow an agile approach. Researchers can easily deploy new algorithms without affecting the performance or correctness of other algorithms running in the cloud.
Another goal of the distributed system was performance and scalability. Nodes can easily be added to accommodate greater numbers of sensors. The system is not embarassingly parallel, but we were still able to parallelize the solution effectively.
Lastly, the system needs to be both continuous and real-time. "Continuous" meaning that data is processed as it arrives; a smoother, more predictable and more efficient mode of operation than ETL. "Real-time" because some of the potential outputs of the system, such as tsunami alerts, need to be delivered as soon as possible in order to be useful.

In all, a very interesting case study of what SQLstream is capable of. Marc plans to make follow-up posts describing the solution in more detail, so stay tuned.

Yellowfin BI release 5.2 moves to olap4j

2011-06-09T10:25:00.000-07:00

According to their press release, Yellowfin BI version 5.2 "includes a significant OLAP overhaul, with the introduction of OLAP4j and support for PALO, BW as well as enhanced connectivity for SQL Server 2005+".

Nice to see olap4j gaining wider adoption. Though not too surprising, given connectivity options that it opens up. And bear in mind that because olap4j is open source, for every product that mentions olap4j in a press release, there may be dozens or hundreds of others that are using it and not talking about it publicly.

Increased adoption is good, whether or not vendors choose to announce it. We know if vendors run into issues, they will log them and someone would fix them. It makes olap4j better for everyone.

Roll your own high-performance Java collections classes

2011-06-03T12:58:00.000-07:00

The Java collections framework is great. You can create maps, sets, lists with various element types, various performance characteristics (e.g. if you want O(1) insert, use a linked list), iterate over them, and you can decorate them to give them other behaviors.

But suppose that you want to create a high-performance, memory efficient immutable list of integers? You'd write

List<Integer> list =
  Collections.unmodifiableList(
    new ArrayList(
      Arrays.asList(1000, 1001, 1002)));

There will be 6 objects allocated in the JVM: three Integer objects, an array Object[3] to hold the Integers, an ArrayList, and an UnmodifiableRandomAccessList. Not to mention the Arrays.ArrayList and Integer[3] used to construct the list and quickly thrown away.

The resulting list is no longer high-performance. A call to say 'int n = list.get(2)' requires 3 method calls (UnmodifiableRandomAccessList.get, ArrayList.get, Integer.intValue) and 3 indirections. And the sheer number of objects created reduces the chance that a given stretch of code will be able to operate solely from the contents of L1 cache.

So, what next? Should I write my own class, like this?

public class UnmodifiableNativeIntArrayList
  implements List<Integer>
{
  ...
}

Well, maybe. But there are rather a lot of variations to cover, and each one needs to be hand-coded and tested.

Do I use library code? I searched and turned up Apache Commons Primitives, Primitive Collections for Java (PCJ), and GNU Trove (trove4j). Of these, only GNU Trove is still active.

None of the libraries supports features such as maps with two or more keys, unmodifiable collections, synchronized collections, flat collections (similar to Apache Flat3Map). It's not surprising that they don't: each combination of features would require its own class, so the size of the jar file would grow exponentially.

So, I'd like to propose an alternate approach. You configure a factory, specifying the precise kind of collection you would like, and the factory generates the collection class in bytecode. You can use the factory to quickly create as many instances of the collection as you wish. The collection implements the Java collections interfaces, plus additional interfaces that allow you to efficiently access the collection without boxing/unboxing.

The above example would be written as follows:

// Initialize the factory when the program is loaded.
// Then the bytecode gets generated just once.
static final Factory factory =
  new FactoryBuilder()
    .list()
    .elementType(Integer.TYPE)
    .modifiable(false)
    .factory();

int[] ints = {1000, 1001, 1002};
IntList list = factory.createIntList(ints);

Variants are expressed as FactoryBuilder methods:

FactoryBuilder FactoryBuilder.list()
FactoryBuilder FactoryBuilder.map()
FactoryBuilder FactoryBuilder.set()
FactoryBuilder FactoryBuilder.keyType(Class...) (for maps only)
FactoryBuilder FactoryBuilder.valueType(Class...) (for maps only)
FactoryBuilder FactoryBuilder.elementType(Class...) (for list and set only)
FactoryBuilder FactoryBuilder.sorted(boolean) (cf. the difference between Set and SortedSet)
FactoryBuilder FactoryBuilder.deterministic(boolean) (cf. the difference between HashMap and LinkedHashMap)
FactoryBuilder FactoryBuilder.modifiable(boolean)
FactoryBuilder FactoryBuilder.fixedSize(boolean) (cf. the difference between Flat3Map and Map)
FactoryBuilder FactoryBuilder.synchronized(boolean)

And so forth. Additional variants could be added as the project evolved. Templates could be fine-tuned for particular combinations of variants.

The projects I mentioned above clearly use a template system, and we could use and extend those templates. The janino facility can easily convert the generated java code into bytecode. And the JVM would be able to apply JIT (just-in-time compilation) to these classes; in fact, these classes would be more amenable to compilation, because they would be compact and final.

The existing projects have invested a lot of effort designing high-performance collections. I'd like to build on that work; this project could even be an extension to those projects.

I'd like to hear if you're interested in working with me on this.

Removing Mondrian's 'high cardinality dimension' feature

2011-06-01T16:26:00.000-07:00

I would like to remove the 'high cardinality dimension' feature in mondrian 4.0.

To specify that a dimension is high-cardinality, you set the highCardinality attribute of the Dimension element to true. This will cause mondrian to scan over the dimension, rather than trying to load all of the children of a given parent member into memory.

The goal is a worthy one, but the implementation — making iterators look like lists — has a number of architectural problems: it duplicates code; because it allows backtracking for a fixed amount, it works with small dimensions but unpredictably fails with larger ones; and because lists are based on iterators, re-starting an iteration multiple times (e.g. from within a crossjoin) can re-execute complex SQL statements.

There are other architectural features designed to help with large dimensions. Many functions can operate in an 'iterable' mode (except that here the iterators are explicit). And for many of the most data-intensive operators, such as crossjoin, filter, semijoin (non-empty), and topcount, we can push down the operator to SQL, and thereby reduce the number of records coming out of the RDBMS.

It's always hard to remove a feature. But over the years we have seen numerous inconsistencies, and if we removed this feature in mondrian 4.0, we could better focus our resources.

If you are using this feature and getting significant performance benefit, I would like to hear from you. I would like to understand about your use case, and either direct you to another feature that solves the problem, or try to develop an alternative solution in mondrian 4.0. The best place to make comments about these use cases is on the Jira case MONDRIAN-949.

Scripted plug-ins in LucidDB and Mondrian

2011-05-31T00:03:00.000-07:00

I saw a demo last week of scripted user-defined functions in LucidDB, and was inspired this weekend to add them to Mondrian.

Kevin Secretan of DynamoBI has just contributed some extensions to LucidDB to allow you to call script code (such as JavaScript or Python) in any place where you can have a user-defined function, procedure, or transform. This feature builds on a JVM feature introduced in Java 1.6, scripting engines.

Scripted functions may be a little slower than Java user-defined functions, but what they lose in performance they more than make up in flexibility. Writing user-defined functions in Java has always been laborious: you need to write a Java class, compile it, put it in a jar, put the jar on the server's class path, and restart the server. Each time you find a bug, you need to repeat that process, and that can easily take a number of minutes each cycle. Because scripted functions are compiled on the fly, you can cycle faster, and spend more of your valuable time working on the actual application.

I am speaking about LucidDB (and SQLstream) here, but the same problems exist for Mondrian plug-ins. Scripting is an opportunity to radically speed up development of application extensions, because everything can be done in the schema file. (Or via the workbench... but that part isn't implemented yet.)

Mondrian has several plug-in types, all today implemented using a Java SPI. I chose to make scriptable those plug-ins that are defined in a mondrian schema file: user-defined function, member formatter, property formatter, and cell formatter. A small syntax change to the schema file allowed you to chose whether to implement these plug-ins by specifying the name of a Java class (as before) or an inline script.

As an example, here is the factorial function defined in JavaScript:

<UserDefinedFunction name="Factorial">
  <Script language="JavaScript">
    function getParameterTypes() {
      return new Array(new mondrian.olap.type.NumericType());
    }
    function getReturnType(parameterTypes) {
      return new mondrian.olap.type.NumericType();
    }
    function execute(evaluator, arguments) {
      var n = arguments[0].evaluateScalar(evaluator);
      return factorial(n);
    }
    function factorial(n) {
      return n <= 1 ? 1 : n * factorial(n - 1);
    }
  </Script>
</UserDefinedFunction>

A user-defined function ironically requires several functions in order to provide the metadata needed by the MDX type system. The member, property and cell formatters are simpler. They require just one function, so mondrian dispenses with the function header, and requires just the 'return' expression inside the Script element. For example, here is a member formatter:

<Level name="name" column="column">
  <MemberFormatter>
    <Script language="JavaScript">
      return member.getName().toUpperCase();
    </Script>
  </MemberFormatter>
</Level>

You can of course write multiple statements, if you wish. Since JavaScript is embedded in the JVM, your code can call back into Java methods, and use the full runtime Java library.

There are examples of cell formatters and property formatters in the latest schema guide.

If you are concerned about performance, you could always translate this code back to a Java UDF when it is fully debugged. However, you might be pleasantly surprised by the performance of JavaScript: I was able to invoke a script function about 20,000 times per second. And I hear that there is a Janino "scripting engine" that compiles Java code into bytecode on the fly. In principle, it should be as fast as a real Java UDF.

I'd love to hear about Janino, or in fact any other scripting engine, with the Mondrian or LucidDB scripted functions.

By the way, you can expect to see scripted functions in a release of SQLstream not too far in the future. The Eigenbase project makes it easy to propagate features between projects, and this feature is too good not to share.

olap4j version 1.0 released

2011-04-12T02:21:00.000-07:00

Today we launched version 1.0 of olap4j, the open standard API for accessing analytic databases.

It's worth mentioning that version 1.0 is a big deal for an open source project. The tag implies maturity and stability, both of which are true for olap4j. The project is over 4 years old, has two robust driver implementations, and many applications in production.

The olap4j driver for Mondrian has been the official way to access Mondrian since version 3.0, and the olap4j driver for XML/A allows access to many XML/A-compliant analytic engines, including Microsoft SQL Server Analysis Services, Mondrian, Palo, and SAP BW.

olap4j was created to address the lack of an open standard API for Java access to OLAP servers. Microsoft had created APIs for the Windows platform (OLE DB for OLAP, and later ADOMD.NET) and for web services (XML for Analysis) and in due course other vendors adopted those APIs as standards, but on Java, the main platform for enterprise applications, you were always tied to the API provided by your OLAP server vendor.

There had been previous attempts to create Java APIs for OLAP, but they foundered because the main vendors could not — or would not — overcome the technical differences between their products. Since OLAP is concerned with constructing dynamic queries to assist an end-user in interactively exploring a data set, most vendors constructed queries using a complex proprietary API to "build" a query using a sequence of transforms.

Relational database APIs such as ODBC and JDBC take a different approach: the query is a string in the SQL language. This allowed the APIs to be simpler, because the semantics of the query language need to be understood by the SQL parser and validator on the server, not by the API itself. And it has allowed the query language to be standardized without affecting the API too much. But the OLAP vendors maintained that such a simplifying approach could not be applied to OLAP.

Microsoft started to prove them wrong when in 1998 they launched SQL Server OLAP Services, the OLE DB for OLAP API, and the MDX query language. This was the first time (to my knowledge) that an OLAP vendor had built its API around a query language as opposed to a set of transforms. MDX played a major role in the success of XML/A: a web services API would have been much harder to use if the queries had built using an object model. Other vendors started to adopt OLE DB for OLAP and XML/A, leaving a void on the one platform Microsoft had no interest in: Java.

Those of us in the open source world felt that void most acutely. Open source projects are organized into discrete components, each talking a standard API, and able to replace a proprietary component by being better, cheaper, faster. If there are no standard APIs, the product stacks sprawl across many components, from client-side to server-side, all made by the same vendor; there is nowhere for open source to get a foothold, and the customer has no choice but to accept the whole hog sold by the vendor.

To redress this, I decided to create a new API. The software would be developed as an open source project, but perhaps more importantly, the specification would be created using an open standards process. As a result, the participants in olap4j read as a who's who of open source BI. Barry Klawans, then chief architect of JasperSoft, co-authored the original draft; Pentaho's chief geek, James Dixon, authored the query model; Luc Boudreau, first with the University of Montreal, then with SQL Power, and now at Pentaho, is the XMLA driver's most active committer and co-leads the project; Paul Stoellberger and Tom Barber have proven and showcased olap4j by developing the first graphical client, Saiku. Paul has also got the XMLA driver working against SAP BW. And we've worked closely with Palo developers: Michael Raue worked with us on the spec, and Vladislav Malicevic has gotten the XMLA driver working against Palo.

I knew that to be successful, olap4j needed to be simple and familiar, so I mandated that it would be an extension to JDBC and would use MDX as its query language. The other participants in the specification process took it from there.

Because olap4j is an extension to JDBC, any developer who has accessed databases from Java can easily pick it up. And it can leverage standard JDBC services such as connection pools and driver managers.

Microsoft had proven that an API could be built around the MDX language; there were differences between servers, but these would be mostly in the dialect of MDX supported; just about any server could support the basic metamodel of catalogs, cubes, dimensions, and measures. Some clients would want to build their own queries, and parse existing MDX queries; for these, we added a query model and an MDX parser to olap4j. Use of the query model and MDX parser is optional: if you have an MDX query string, you can just execute it.

We have recently added more advanced features such as scenarios (write-back) and notifications. These features are still experimental (unlike the rest of the API, they may change post-1.0) and are optional for any olap4j provider. But we hope to see more providers implementing them, and clients making use of them. And we hope to see more features added to olap4j in future versions.

The goal of olap4j was to foster development of analytic clients, servers, and integrated analytic apps by providing an open standard for connectivity. That goal has been realized. There is a native driver for mondrian and an XMLA driver that works against Microsoft SQL Server Analysis Services, SAP BW, Jedox Palo. There are several clients, both open and closed source: several components in Pentaho's own suite, the Community Dashboard Framework (CDF), Saiku, ADANS, SQL Power Wabit, and more.

People are using olap4j in ways that I couldn't imagine when I started the project four years ago. That's the exciting thing about an open source project becomes successful and starts to gain momentum: you can expect the unexpected.

Thank you to everyone who helped us get to this milestone.

Visit www.olap4j.org, and download the release 1.0 of the specification and the software.

Scalable caching in Mondrian

2011-02-04T13:13:00.000-08:00

Wouldn't it be great if Mondrian's cache could be shared between several Mondrian instances, use memory outside the JVM or even across several machines, and scale as the data size or computation effort increases? That is the vision of Pentaho's "enterprise cache" initiative.

Mondrian cell-caching architecture, including pluggable external cache.

Luc Boudreau has been leading this effort, has just checked in the first revision of the new mondrian.rolap.agg.SegmentCache interface, and has written a blog post describing how it will work. (Note: This SPI is likely to change before we release it.)

Pluggable caching will be in Mondrian release 3.3, probably Q2 or Q3 this year.In the community edition will be the SPI and a default implementation that uses JVM memory. Of course the community will be able to contribute alternative implementations. In the enterprise edition of Mondrian 3.3, there will be scalable, highly manageable implementation based on something like Terracotta BigMemory, ehCache or JBoss Infinispan.

In future releases, you can expect to see further work in the area. Maybe alternative implementations of the caching SPI, and certainly tuning of Mondrian's caching and evaluation strategies, as we apply Mondrian to some of the biggest data sets out there.

Oracle, Hudson and Jenkins

2011-01-25T23:42:00.000-08:00

I've been following the furore about the Hudson open source project with some interest and amusement. Oracle owns the trademark on the name Hudson (because the original developer worked for Sun at the time the project was created) and the community is spooked by the possibility that Oracle will enforce its trademark rights in future.

Trademark rights are indeed a big deal for an open source project, just as they are for a commercial product. An open source project builds its brand by several years of high-quality releases and effective support in its community. Whoever owns the trademark of a project controls that brand.

Here is Oracle's proposal for the future of the project, and the response of one of the project's lead developers.

It's an interesting study in the fragile dynamics of an open source project's community. Oracle clearly don't understand how fragile the power balance is. The community is spooked; not so much by Oracle's ability to enforce its trademark (they claim they would never do that) but by their presumption that they have more of a say in the project than anyone else.

My two cents? Oracle are not evil, but they are being naive and are coming across as complete dicks. If I were a member of the active Hudson community (I'm a happy user of Hudson, since it powers Pentaho's continuous integration site, but I wouldn't say that makes me a community member) I'd certainly give my +1 to fork and change the name of the project to Jenkins. There's little reason not to.

"Just another big pile of data"

2011-01-06T15:08:00.000-08:00

Jeff Jonas writes about the challenges of managing data privacy when the data concerned is Big Data. He advocates taking a real-time approach to auditing user behavior:

Real-time active audits. It is now going to be essential that user activity be more rigorously analyzed, in real-time, for inappropriate behavior. Audit logs have actually been part of the problem – just another big pile of data – evidence of misuse hiding in plain sight against the backdrop of millions and millions of benign audit records.

I must say, it hadn't occurred to me that privacy management could be seen as a real-time data problem. But he's right that large data sets, when not acted upon immediately, can become part of the problem. In his words, "just another big pile of data".

An experiment with the Linux scheduler

2010-12-19T21:26:00.000-08:00

I was curious to see how the Linux scheduler would manifest from a program's perspective, so today I did an experiment.

I wrote a single-threaded program running a simple loop. All the loop does is to compute the number of milliseconds since the last iteration, and store the result in a histogram. We are not so much interested in the performance of the loop (it does about a million iterations per second) but in the variations in the intervals between loop iterations. These variations are presumably caused by the Linux scheduler.

Here are the numbers I achieved, and the same numbers in a chart (with a logarithmic y-axis).

Interval (milliseconds)	Frequency
0	450,080,302
1	909,044
2	4,642
3	1,696
4	853
5	561
6	557
7	335
8	1,098
9	152
10	86
11	52
12	98
13	17
14	13
15	6
16	21
17	5
19	3
20	1
21	2
22	2
23	0
24	2
25	0
26	0
27	0
28	1
29	0

The vast majority of iterations occur zero milliseconds after the previous iteration. No surprise there; Java's clock granularity, 1 millisecond, is coarse enough to execute over a million instructions.

If the thread was never interrupted, one would expect the loop to tick forward 1 millisecond 1,000 times per second, or about 500,000 times in all. It actually ticks 909,044 times, so interrupts are at work: about 400,000 of them.

Longer delays also occur: 2, 3, 4, up to 28 milliseconds, occurring with exponentially decreasing frequency. Only 8 delays of 20 milliseconds or longer occur in the 7.5 minute run. The chart shows the exponential decay clearly. The chart plots log-frequency, and the trend line is indeed flat from 2 milliseconds onwards, so it is accurate to characterize the line as exponential.

The one surprising thing: significant bumps at 8, 12 and 16 milliseconds. Although the trend of the line is pretty consistently down, each of those interval durations has more distinctly occurrences than the previous interval. Does anyone know anything about the Linux scheduler that might explain this?

Architectural shuffling in mondrian's XMLA and olap4j servers

2010-11-22T12:00:00.000-08:00

As a software architect, some of my most interesting work doesn't deliver any additional functionality to end-users, but reorganizes the architecture to make great things possible in future. Since mondrian is an open source project, those great things will, likely as not, be dreamt up by someone else; my job as leader of the mondrian project is to reorganize things to make that possible.

Case in point, my recent check in, change 13929. It contains three new pieces of functionality.

Make mondrian's XMLA server run off the olap4j API

Mondrian's legacy API (mondrian.olap.Connection, etc.) has been deprecated for some time; olap4j is the official API by which applications should speak to mondrian. Mondrian's XMLA server, that takes incoming SOAP requests over HTTP to execute queries or retrieve metadata, processes them using mondrian, and returns the results as SOAP or JSON over HTTP, has not used the olap4j API until now.

As part of this change, I converted the XMLA server to use olap4j. In the process, I achieved some beneficial side effects. First, I discovered and fixed a few bugs in mondrian's olap4j driver; this will make the olap4j driver more stable for everyone.

Second, I discovered a few essential pieces of metadata that the olap4j API does not return. I have not yet extended olap4j to include them: that may happen as we move towards olap4j 1.1 or olap4j 2.0, if they make sense for other olap4j stakeholders. I created the XmlaExtra interface as a loophole, to allow the XMLA server get mondrian's legacy API; this interface serves to document what's missing from olap4j.

Third, and most exciting, the XMLA server should now run against any olap4j driver. It needs to be repackaged a bit — it still lives within the mondrian codebase, in the mondrian.xmla package — but if you are developing an olap4j driver, contact me, and we can consider spinning it out.

Make mondrian into a real server — for those who want one

You'll notice that I tend to refer to mondrian as an OLAP engine. I've always hesitated to call it a 'server', because a server has an independent existence (its own process id, for instance), configuration information, and services such as authentication.

This is no accident: I deliberately architected mondrian as an engine, so that it could be embedded into another application or server and inherit those services from that application. That's why you need to tell mondrian the URI of the catalog, the JDBC information of the data warehouse, and the role that you would like mondrian to use to execute queries. It has no concept of users and passwords, because it assumes that the enclosing application is performing authentication, then mapping authenticated users to roles.

This architecture makes as much sense now as it did when I started, and it isn't going to change. Core mondrian will remain an engine. But the XMLA server, as its name suggests, performs some of the functions that one associates with a server. In particular, it reads a datasources.xml file that contains the name, catalog URI, and JDBC information of multiple catalogs. My idea was to create an alternate olap4j driver, MondrianOlap4jEngineDriver, that extends the default driver MondrianOlap4jDriver, and move the catalog functionality from the XMLA server to the new olap4j driver.

The new driver is added as part of this change, but is not complete. In a later change, I will move the catalog functionality out of the XMLA server. I don't have plans to add other server features, such as mechanisms to authenticate users or map user names to roles. But I've provided the hook where this functionality can be added, and I encourage you in the mondrian community to contribute that functionality.

Lock box

Last, I came up with an elegant (I think) solution to a problem that has been perplexing us for a while. The problem is that the JDBC API requires all parameters to be passed as strings when you are making a connection. If you are creating an olap4j connection to mondrian, and access to the underlying data warehouse is via a javax.sql.DataSource object, not a connect string, then you cannot pass in that DataSource. If you have created your own Role object to do customized access-control, you cannot pass in the object, you have to pass in the name of a role already defined in the mondrian schema (or a comma-separated list of role names).

I invented a LockBox class, that acts as a special kind of map that has some of the characteristics of a directory service. There is one lock box per server. If you have an object you wish to pass in, then you register it with the lock box, and the lock box gives you a string moniker to reference that object. That moniker is unique for the duration of the server, and near impossible for an unauthorized component guess. You can pass it to other components, and they can access the object.

The lock box automatically garbage collects unused objects. When an object is registered, the lock box returns an entry object to the caller that contains both the string moniker and the object itself. The entry is the key to a WeakHashMap, so when the client forgets the entry, the object is eligible to be garbage-collected out of the lock box. This guarantees that the lock box will not fill up over time due to clients forgetting to deregister objects.

LockBox does not purport to be a full directory service — in particular, objects are only accessible within the same JVM — but it carries out a simple purpose, efficiently and elegantly, and may be useful to other applications.

Numbers everyone should know

2010-11-17T15:04:00.000-08:00

Jeffrey Dean recently gave a talk "Building Software Systems at Google and Lessons Learned" at Stanford (video). One of his slides was the following list of numbers:

L1 cache reference	0.5	ns
Branch mispredict	5 ns
L2 cache reference	7 ns
Mutex lock/unlock	25 ns
Main memory reference	100 ns
Compress 1K bytes w/ cheap algorithm	3,000 ns
Send 2K bytes over 1 Gbps network	20,000 ns
Read 1 MB sequentially from memory	250,000 ns
Round trip within same datacenter	500,000 ns
Disk seek	10,000,000 ns
Read 1 MB sequentially from disk	20,000,000 ns
Send packet CA->Netherlands->CA	150,000,000 ns

Everyone who wants to design high-performance, scalable systems should memorize these numbers. There are many, many lessons to be learned.

Concepts in Streaming SQL

2010-10-29T20:21:00.000-07:00

SQLstream's marketing head Rick Saletta just wrote a layman's guide to streaming SQL. It's short, sweet, entirely buzzword-free, and a good introduction to streaming queries. So I thought I'd share the whole post:

A streaming SQL query is a continuous, standing query that executes over streaming data. Data streams are processed using familiar SQL relational operators augmented to handle time sensitive data. Streaming queries are similar to database queries in how they analyze data; they differ by operating continuously on data as they arrive and by updating results in real-time.

Streaming SQL queries process dynamic, flowing data, in contrast to traditional RDBMSs, which process static, stored data with repeated single-shot queries. Streaming SQL is simple to configure using existing IT skills, dramatically reducing integration cost and complexity. Combining the intuitive power of SQL with this simplicity of configuration enables much faster implementation of business ideas, while retaining the scalability and investment protection important for business-critical systems.

By processing transactions continuously, streaming SQL directly addresses the real-time business needs for low latency, high volume, and rapid integration. Complex, time-sensitive transformations and analytics, operating continuously across multiple input data sources, are simple to configure and generate streaming-analytics answers as input data arrive. Sources can include any application inputs or outputs, or any of the data feeds processed or generated within an enterprise. Examples include financial trading data, internet clickstream data, sensor data, and exception events. SQL can process multiple input and output streams of data, for multiple publishers and subscribers.

If you want to learn more, download the Concepts in Streaming SQL white paper.

Setting the current member of the time dimension automatically

2010-10-07T18:39:00.000-07:00

The question came up today, "How do I write my report so that the current member of the Time dimension is the most recent member for which transactional data are loaded?"

It's a good question, and comes up often. Let's look at some ways that you could solve it.

Attempt #1: CurrentDateMember

One might think that 'today' would suffice (using Mondrian's CurrentDateMember MDX function), but since many enterprises only run the ETL process overnight, it isn't always the right answer. Some nights (gasp!) the ETL process fails, so even 'yesterday' may not be right answer.

Attempt #2: defaultMember

The default member of a hierarchy is its 'all' member, or if there is no 'all' member, the first member of the first level. Mondrian allows you to change the default member in the schema file using the defaultMember XML attribute. To do this for the Time hierarchy, you'd write the following:

<Dimension name="Time" type="TimeDimension">
<Hierarchy defaultMember="[Time].[2010].[10].[07]" hasAll="false" primarykey="time_id">
...

You'd have to find some way to re-generate the schema XML file each time a load was successful (or use a DynamicSchemaProcessor to generate a schema on the fly, substituting a template schema that contains a token for the default member). But I wouldn't recommend this approach. Default members of hierarchies don't just affect what appears on the screen; they are the default context for all MDX calculations (where the calculation isn't explicitly set in the formula), and so all calculations will change every time you reload your data warehouse. This probably isn't what your users want.

Attempt #3: Parameter

Define each of your reports with a parameter that holds the initial member of the time hierarchy for that report. Use some kind of scripting (say a custom piece of JavaScript inside Pentaho's CDF, or a Pentaho action sequence) to populate that parameter as the report is launched.

This approach is on the right track, but isn't quite perfect. This will give the your users what they want, but you will have to maintain a piece of script for every report you define.

Attempt #4: Parameter with MDX expression as default value

This improves on attempt #3 by putting the expression to initialize the parameter inside the definition of the parameter. You don't need to provide a value of the parameter when you launch the report (unless you want to), and that means you don't need to write those pesky scripts.

Although the question called for "the most recent [Time] member for which transactional data are loaded", I'm going to drive home the point with an example that qualifies on another dimension as well. This query will launch with the most recent month for which anyone in the town of Bellflower, California bought Good beer.

select [Measures].[Unit Sales] on 0,
[Product].Children on 1
from [Sales]
where Parameter(
  "Time period of interest",
   [Time],
   Tail(
   {
     [Time],
   Filter(
   [Time].[Month].Members,
   0 < ([Customers].[USA].[CA].[Bellflower],
   [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good]))
   },
   1),
   "Time period of interest for current analysis. By default the most recent month for which transactions exist.")

Filter evaluates the sames of Good beer in Bellflower every month and throws out months where no Good beer was sold, and Tail chooses the last. The dummy first element {[Time], ... } is to ensure that if the residents of Bellflower have never bought Good beer, the report still launches with a valid member of the time dimension.

The results are as follows:

|                | Unit Sales |
+----------------+------------+
| Drink          |      2,344 |
| Food           |     18,278 |
| Non-Consumable |      4,648 |

and the member in the slicer is [Time].[1997].[11]. (Yes, it's a long time since the unfortunate residents of Bellflower, CA drank Good beer.) This report doesn't contain a great deal of detail, but it can be used as a starting point for an series of slice, dice and pivot operations to interactively explore the data, and the same Time member will be carried forward until the user decides to switch to another time.

Attempt #5. Schema parametersI stopped looking for a solution when I had written the above query in attempt #4, but schema parameters are potentially even better. Schema parameters are little-known Mondrian feature that allow you define a parameter once in a schema file, then reference it in any report written against that schema.

I haven't tried it, but the solution would look something like the following. To define the parameter, include the following in your schema file:

<Parameter defaultvalue="Tail({ [Time], Filter([Time].[Month].Members, 0 < ([Customers].[USA].[CA].[Bellflower], [Product].[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer].[Good])) }, 1)" name="Time period of interest" type="Member"/>

and reference the parameter in an MDX query using ParamRef:

select [Measures].[Unit Sales] on 0,
[Product].Children on 1
from [Sales]
where ParamRef("Time period of interest")

Adding support for a new database to mondrian

2010-09-19T13:15:00.000-07:00

Mondrian supports a large number of back-end databases. So many in fact, that we rely on contributors to add the support, and we have tried to standardize the steps to support a new database.

I thought it might be worthwhile to reiterate those steps. The steps are:

1. Write a dialect class. The dialect must implement the Dialect interface, and will probably be a subclass of JdbcDialectImpl. The only prerequisites are that your database has a JDBC driver and supports SQL-92: SELECT .. FROM .. JOIN .. GROUP BY. Mondrian will glean as much information as it can, such as how your database quotes identifiers that contain mixed-case or spaces, from the JDBC driver. But you will need to override methods to provide information that the JDBC does not provide (e.g. how to affect whether NULL values sort first or last) or if the JDBC driver lies.

2. Add your dialect to the META-INF/services/mondrian.spi.Dialect file so that mondrian can find it.

3. Modify MondrianFoodMartLoader and (optionally) bin/loadFoodMart.sh so that you can load data from demo/FoodMartData.sql into your database. (We prefer not to add a dump file for each database to the distro. The FoodMart data set is about 5MB compressed, so each dump file would bloat the size of the distro.)

4. Get the test suite to pass. We strongly recommend that you focus on getting DialectTest to pass first; once the dialect is accurate, most other mondrian tests should just pass. If you need help, post your test output to the mondrian developers email list; someone is likely to have seen the problem before, on another database.

5. Add a section to mondrian.properties describing a typical connect string for your database.

When these steps are complete, post the files in a JIRA case. I will add your database to the list of supported databases and mention it in the release notes of the next mondrian release.

Then, please join the email list and stay in touch. We are not able to test all supported databases each release. When we announce a beta of a mondrian release, run the test suite against your database, and let us know if we've broken anything. Supporting a large array of databases is not hard, but it is even easier if we do it as a community.

September world tour

2010-08-17T14:42:00.000-07:00

I'm going to be at several conferences over the next month or so. I always like to meet up with people who are using mondrian, olap4j, Pentaho and LucidDB to do open source BI, so put these on your schedule.

Beer in Barcelona: Thomas Morgner, Matt Casters and others at Pentaho Community Meetup 2009.

On Wednesday September 1, there is an Eigenbase Developers Meetup at SQLstream's offices in San Francisco. I predict that it will be a reunion, of sorts, for people who worked on LucidDB at LucidEra, but you will also hear about new features that Nick Goodman is adding as part of DynamoDB (including closer integration with Pentaho), John Sichi's work on Firewater (a project to build a scalable shared-nothing database based on LucidDB) and contributions SQLstream developers are making to Eigenbase. And there will be beer.

At 9.00pm on Tuesday September 21, Luc Boudreau is giving a talk about olap4j at Java One. I will be in the audience, on hand to answer the questions that Luc considers beneath his dignity. This is a Birds of a Feather (BoF) talk, which means that it will be less formal than a usual conference talk, you can attend even if you have not paid to attend the conference, and yes, there will be beer.

Lastly, I will be attending the 3rd annual Pentaho Community Gathering on September 25-26 in Lisbon, Portugal, and giving a talk about what's new in Mondrian and open-source OLAP. Meetups in previous years have been great fun, a good chance to network, and a great chance to find out all of the great stuff that the Pentaho community is working with and extending the Pentaho BI suite. Even though Portugal is better known for producing wine, I wouldn't be surprised if at some point there was beer.

olap4j now talks to Palo and SAP BW

2010-08-02T17:58:00.000-07:00

As olap4j heads towards release 1.0, there are further signs that it is coming of age, in the form of drivers for the Palo MOLAP engine and SAP BW.

A few days ago Paul Stoellberger announced that olap4j's XMLA driver could connect to SAP BW, and posted pictures of PAT, PRD and PDI to prove it.

And just a week later, Jedox CEO Kristian Raue writes about how to connect to Palo. His post includes a blessedly short Java program to do it. Only one line of Kristian Raue's program — the connect string — would be different if the program were talking to Mondrian, Microsoft SQL Server Analysis Services or SAP BW via XMLA.

This is a success for both open standards and for open source software. Now applications built on olap4j have two open source OLAP engines — Palo and Mondrian — available to them, and can choose which is best according to the characteristics of their OLAP application.

Clarification, 2011/7/20: Palo's engine is open source, but as Christian Warden points out in a comment to this post, their XMLA server is not. I was therefore incorrect to give the impression that olap4j can talk to Palo as part of a 100% open source stack.

Behind those open source projects are companies who need to show a profit. Palo is backed by Jedox, and Mondrian is backed by Pentaho. Are the business people at those companies concerned that their engineers are working with each other, or that their customers now have a choice of OLAP engines? Not at all. The move makes the open source BI ecosystem stronger, and both companies benefit.

Vendors who embrace open source and open standards are effectively saying, "We have built our platform on open standards. We know that if we don't live up to your expectations, you can just walk away. So we know that we have to remain the best platform for your application."

Customers love to have choices, and Pentaho and Jedox are giving customers the greatest choice of all: the choice to walk away.

mondrian heading for 4.0

2010-07-23T17:45:00.000-07:00

I am currently working on mondrian-4.0 and am reworking a lot of mondrian's internals, particularly in how schemas are loaded and validated, and in the mapping of levels and measures onto star schemas. Regression tests for bugs and features are always important, but they are especially important right now.

Regression tests, now more than ever

Usually when I am changing mondrian, if I see a piece of logic in the code, I will try to preserve that logic, and will rework it if necessary when internal APIs change. That goes for other committers too. But I don't propose to do that for mondrian-4.0: the changes to the code are quite widespread, and besides, it's chance to clean out some of the cruft that has built up over the years.

Yes, it's true. Even in mondrian's impeccably clean code — almost 300,000 lines of it — there are some pieces of code that we're not sure are actually used. My natural inclination as a developer is to remove that code, and see whether anything breaks. If that piece of code is a something you contributed but didn't write a test for, then nothing will break, and your code will be on the cutting room floor of history.

So, if you have contributed a feature or bug fix to mondrian over the past years or months, make sure that there is a test case checked in as part of mondrian's regression test suite. I will do my best to make sure that the test case stays working, even if the code underneath is all different. If there isn't a test, your beloved feature may just disappear in mondrian-4.0.

Schema changes, and compatibility

In mondrian-4.0 there will be changes to how schemas are structured. A few examples:

Virtual cubes will be obsolete, or more precisely, any cube can have multiple groups of measures, each group based on a separate fact table.
Linkages between snowflake tables, currently specified using the element, will be specified using new <PhysicalSchema> element that declares table usages, relationships, and derived columns for the whole schema.
The present XML grammar isn't very forgiving if you get things in the wrong order: if you define a <NamedSet> before your <CalculatedMember>s in a cube, mondrian currently ignores all calculated members. The new XML grammar will be more forgiving.

I recently decided that the XML grammar is sufficiently different that I would create a new XML grammar. But I promised that mondrian would be backwards compatible, and I will stand by that. There will be a converter that will recognize an old-style schema, convert it to a new-style schema in memory, and then proceed to load the new-style schema. So, old-style schemas should continue to work. Mondrian-4.0

In a few weeks I will be ready to release the specification for the new-style schemas. I would appreciate review of the new schemas. Since it is a major change, mondrian-4.0 will have a long beta phase. During which time I could use your help testing both new features and backwards compatibility.

olap4j heading for 1.0

2010-07-22T15:49:00.000-07:00

Luc Boudreau this week announced plans to take olap4j to version 1.0.

It is four years since I released the draft of the olap4j API. People have joked that olap4j, like google mail, has been in "perpetual beta" since then. But olap4j's maturity belies its humble version number. It has been in use by production applications, is the foundation of several OLAP clients, and there are at least two drivers. (Mondrian's primary interface is its olap4j driver, and the XMLA driver has variants for Mondrian, Microsoft Analysis Services and SAP BW.)

In software development (and particularly open source) culture, version 1.0 of an API is a symbolic milestone. It means that the API is stable, well tested, and will not be changed except at a major release, and then only with due consultation. So, version 1.0 of olap4j will be something to celebrate, but before then, we need to undertake a review of what is in the API.

Some parts of olap4j (such as the query model, advanced drill through, cell write back and notifications) are still under active development, and it is not in anyone's interests to freeze these parts of the API just yet. So, sections such as these will be marked 'experimental', and likely to change (with consultation of the community, as usual) in future.

Whether you are an olap4j developer, part of the existing olap4j user community, or are just interested in using OLAP from within the Java without being tied to a particular vendor's API, please get involved in the review process.

SQL past and future

2010-06-29T18:08:00.000-07:00

Ken North, writing in Dr. Dobb's Journal, gives a nice overview of the long and storied history of SQL. The piece helps one understand the wave of mergers among the big database vendors, and make sense of current trends in database and database-like software. And I'd like to offer my opinion about where SQL and database management systems are headed.

North looks into the claims that 'the database is dead' and finds that — yet again — reports of its death were greatly exaggerated:

Forrester Research recently estimated the total database market (licenses, support, consulting) would grow from $27 billion in 2009 to $32 billion by 2012. SQL technology is entrenched in many organizations and across millions of web sites. Perhaps that explains why, during the past decade, IBM, Oracle, Sun and SAP made billion-dollar investments in a ‘dead’ technology.

However, I do believe that the relational database is currently in crisis. Relational databases have been the mainstay of data management for over twenty years, but Oracle and its cohorts have no answer for "Big Data", the massive onslaught of information from the web and sensors.

The NoSQL movement is solving these problems by challenging some of the assumptions held by RDBMS vendors. At SQLstream, we regard ourselves as part of the NoSQL movement even though we are huge fans of SQL, because we are challenging the biggest assumption of them all: that you have to put data on disk before you can analyze it.

It's a shame that North doesn't mention streaming SQL, because it fits perfectly into the grand arc of the SQL language: adopt new problems, express them declaratively, and solve them first with special-purpose database engines and finally by adapting the architecture of the big, general-purpose database engines. This last step sometimes takes many years to happen, but it happened for transaction processing, object database, and data warehousing, and I have no doubt that it will happen for streaming relational data.

One of the reasons that SQL has remained relevant is SQL standards process; products built on one database can be run on another database and, perhaps more important, skill sets acquired on one engine can be applied to another. When the dust settles, and the big databases have learned hard architectural lessons, I think a lot of these new problems will be solved in SQL.

Unlike Mike Stonebraker, I do think that organizations will want to put all these different forms of data into one database management system. That database will of course be a facade spread over many servers, disks, data organizations and query processing engines, but will offer centralized management and allow the different forms of data to be combined. They will get their wish because the SQL language is so powerful at hiding differences in underlying data organization.

When the dust has settled, the SQL language will have changed and adapted yet again, and maybe there will be some new names at the top of the roster of database vendors, but we will once again be solving most of our data management problems using declarative queries beginning with the word "SELECT". SQL is dead; long live SQL!

The Data Tsunami: SQLstream at Structure 2010

2010-06-22T21:09:00.000-07:00

At SQLstream, we have no doubt that streaming SQL and stream computing will be a key part of the next-generation enterprise infrastructure, but we are less certain how we fit into trends such as "Big Data" and "NoSQL".

Taking the terms absolutely literally, we aren't either. We can't be "Big Data" because we do out damnedest to process data as fast as we receive it, in memory. I guess "Fast Data" would be a better word for what we do. And we can't be "NoSQL" because we're the biggest cheerleaders for industry-standard SQL you'll find anywhere outside Redwood Shores or Almaden.

But we founded the company because we foresaw the onset of massive quantities of data, requiring efficiently-delivered low-latency results, and the impending failure (or at least faltering) of the classical database architecture to solve them. So, the forces driving us are the same forces driving Big Data and NoSQL.

SQLstream CEO Damian Black will be a appearing on the the "Big Data: Dealing with the Data Tsunami" panel at the Structure 2010 conference this week, so he took the occasion to write a blog post to set out his views on the subject.

Structure 2010 is an excellent conference, distinguished by the quality of its speakers and its attendees, and known for its intimate atmosphere. I will be in the audience both tomorrow and Thursday finding out what's new. Follow me on twitter @julianhyde or even better, come and introduce yourself.

Is Microsoft abandoning XML/A?

2010-06-17T14:46:00.000-07:00

Microsoft are deprecating Native XML Web Services in SQL Server 2008, and if I understand the productmanagerese correctly, that means that they are abandoning XML for Analysis (XML/A) as an interface to Microsoft Analysis Services.

(I may be mistaken. Can someone who is closer to Microsoft's roadmap clarify how OLAP applications on non-Windows systems are supposed to access Microsoft Analysis Services?)

UPDATE, 2010/6/18 09:50 PDT: It turns out that I am mistaken, and I received several comments pointing this out. The real story is that Microsoft is deprecating native XML access to SQL Server (the relational database, not the OLAP server). I have left the rest of the blog post as I originally wrote it, but please read it in the light of the new evidence.

But if true, that would be a worrying development for those of us who want to build interoperable OLAP applications. (In particular, those with clients on non-Windows platforms such as Linux.)

It's funny; Microsoft was the leading company pushing web services back in 2000. Everyone thought that it was too verbose a protocol for passing large data sets around, but Microsoft had a big problem to solve — its aging DCOM infrastructure — and pushed it through.

I was at a meeting in Redmond (in 1999, if memory serves) when Microsoft launched XMLA. The SQL Server OLAP Services team demoed the interoperability by showing a Java program running on Solaris (or possibly Linux... my memory is fading...) connecting to a Microsoft OLAP Services server. They joked that they could have been fired for having a non-Windows machine in the building. But nevertheless they made their point: XMLA was interoperable, and that was unprecedented among OLAP applications at the time.

Soon afterwards Microsoft started using compressed XML for its XMLA calls and responses, thereby reducing the problem. Unfortunately their compression technology was proprietary, so the rest of us had to carry on using uncompressed SOAP calls. It gave Microsoft's drivers an unfair advantage over other drivers attempting to talk to Analysis Services.

It's ironic that Microsoft should abandon a standard that they created, and which has been astoundingly successful. I suspect that they have gotten tired of maintaining it when their own drivers use more efficient, proprietary wire protocols.

If Microsoft is deprecating XMLA, I doubt that it will disappear for some years to come, but it is bound to be a concern for people building applications now that they intend to be running for several years.

Of course, one thing people can do to insulate themselves from the future is to build their OLAP applications in Java using olap4j. Whatever net protocol Microsoft adopts to replace XMLA, we will keep the olap4j driver for Analysis Services working, so you shouldn't need to change your application.

Likewise, if you are building your application in JavaScript, consider using Roland Bouman's excellent xmla4js library.

OLAP change notification, and the CellSetListener API

2010-06-15T23:21:00.000-07:00

There has been an interesting design discussion on the olap4j forums about how an OLAP server could notify its client that the data set has changed. It is exciting because it would allow us to efficiently update OLAP displays in real-time.

We came up with an API, at the center of which is the new interface CellSetListener, which I have just checked into olap4j's subversion repository. (The API is experimental. That means you shouldn't expect to find a working implementation just yet, or assume that the API won't change radically before it is finalized, but it does mean we are still very much open to suggestions for improvements.)

Of course, OLAP notifications are a subject close to my heart, because they bring together my interests in SQLstream and mondrian. 'Push-based' computing is challenging, because every link in the chain needs to propagate the events to the next link. In a previous post I described how SQLstream could do continuous ETL, populate fact and aggregate tables incrementally, and notify mondrian that data items in its cache were out of date.

A mondrian implementation of the CellSetListener API would cause mondrian to internally re-evaluate all queries that have listeners and cover an affected area of the cache. If the results of those queries changed, mondrian would transmit those notifications to OLAP client applications such as Pentaho Analyzer or PAT. The client application would then change the value of the cell on the screen, and maybe change the cell's background color momentarily to attract the user's attention.

Getting data to change on the screen, in front of the end-user's eyes, within seconds of the data changing in the operational system, would be truly spectacular.

There are several links in the chain to make that happen. Two of the links, SQLstream and mondrian's cache control API, are already complete. We've just begun forging the next link.

Adding profiling and query plans to mondrian

2010-06-01T11:27:00.000-07:00

I've long wanted to add query plans and profiling to mondrian's execution engine.

I just logged jira case MONDRIAN-754 with my ideas for how it would work, and I thought it would be good to share it as a blog post so I can gather opinions. Most of the rest of this post is taken straight from the jira case. Note that we are not committing to implement this feature in any particular release. It's just an idea we are kicking around.

Mondrian currently does not help you find out where the time is spent in executing a query, except for time spent in SQL. While most Mondrian queries are SQL-heavy, it would help to see the breakdown.

Specifically: (a) it would increase understanding of the engine by showing the physical plan (Calc nodes) that mondrian has chosen (including caching of results, choice of iterator versus list representation of sets), (b) it would help identify problems where a particular leaf expression that generates SQL is executed repeatedly and generates very similar SQL statements (see e.g. MONDRIAN-723); and (c) it would help developers identify MDX functions taking longer than expected, or sub-optimal plans, and thereby tune mondrian.

I propose to add a profiling mode with two levels.

At the lower level, mondrian would print the plan of each MDX query as it executed it:

Select(cube="[Cube]" mdx="with set [Foo xxx] AS [Foo].Children select non empty [Foo xxx] on 0, [Bar] * [Baz] on 1 from [Cube] where [Gender].[M]")
= CalculatedSets
=== CalculatedSet(name="Foo xxx", format="iterable")
===== Children(format="list")
======= HierarchyExpr(uniqueName="[Foo]")
= FilterAxis
=== MemberExpr(uniqueName="[Gender].[M]")
= Axes
=== Axis(ordinal="0", nonEmpty="true")
===== SetExpr(name="[Foo xxx]", format="iterable")
=== Axis(ordinal="1", nonEmpty="false")
===== CrossJoin
======= Call(function="{}")
========= Call(function="CURRENTMEMBER")
=========== HierarchyExpr(uniqueName="[Bar]")
======= Call(function="{}")
========= Call(function="CURRENTMEMBER")
=========== HierarchyExpr(uniqueName="[Baz]")

Format. I've used leading '=' to preserve indentation in this bug report. I would use spaces in this bug report. I'd use spaces in the actual feature. Or we could use XML.

There isn't much difference between the physical plan and the MDX query because this is a simple example. Note that '[Foo]' has been expanded as if the user had written '{[Foo].CurrentMember}'. Differences in more complex plans include: constant reduction; introduction of Cache operator; choice of physical format (list, mutable list, iterator); adapters to change physical format (e.g. copy a list to make it mutable); pushdown of non-empty and other constraints to native SQL; strategies for evaluating named sets (first time, each time).

Optionally each node could contain extra static information: the type (e.g. Integer, String, Numeric, Member(hierarchy='Store'), Set(Tuple(Member(hierarchy=[Store]), Member(level=[Time].[Year])))); format (list, mutable list, iterator); list of hierarchies an expression is dependent on (important for cached expressions).

With the higher level of profiling, mondrian would gather information while the plan is running. The number of times a node is executed, and amount of time in that node and its children. From that we can also compute the amount of time in the node alone. At the end of execution, mondrian would print the plan tree again, with "count", "self" and "self+children" values attached to each node.

Of course there is always an overhead to collecting profiling info. We would not recommend that people run production applications with profiling enabled. The question is always whether the numbers gathered from the profiled system are representative of the system running in its normal mode. Call count would be 100% accurate, and elapsed time should be within a few microseconds per call, so the profiling would serve its purpose.

Bite Club

2010-05-27T14:30:00.000-07:00

A couple of our friends (actually former colleagues of Pamela's from Organic Exchange) have started a fabulous new business. It's modeled on the "veggie box" schemes used by Community Supported Agriculture farms, except that rather than vegetables, the each week's box contains home-baked goods.

We received the first box yesterday. It contained potato and spring pea pasties, a ginger and black pepper cake, millet muffins, and a dozen sesame cookies with Hawaiian sea salt.

Everything was delicious. We're especially pleased with the savory items, because it's always a challenge to create simple, healthy family meals we can enjoy with our son. We're hoping that in future weeks the box will contain pot pies, quichen, samosas, and the like.

Cindy and Terry don't have a web site yet. If you're in South Berkeley, drop me a line and I'll tell you how to sign up.

Update: I was wrong; they do have a site: it's http://freshbitebaking.com/bite-club.

Intelligent Transportation in Houston

2010-04-30T11:26:00.000-07:00

I'm off to Houston for the annual meeting of the Intelligent Transportation Society of America (ITSA).

On Monday, I will be speaking on a panel on next-generation standards and architecture for Intelligent Transportation systems. Also present on that panel will be senior members of the U.S. Department of Transportation.

SQLstream is helping to simplify how traffic information is collected, analyzed, and acted upon in real time. In one application, SQLstream is gathering information from a fleet of 7,000 vehicles with GPS sensors and using that information to build a real-time picture of a state-wide freeway system. That information allows government agencies to manage traffic flow, respond to incidents, and plan capacity.

MySQL user conference 2010

2010-04-07T17:24:00.000-07:00

Are you at the MySQL conference this year? I'm giving a talk about SQLstream at 5.15pm on Wednesday 14th (a week from today), but as ever, I'll be wearing both my hats. I'm always ready & willing to chat about mondrian, Pentaho, streaming SQL, open source BI, and the future of intelligent life in the universe.

If you're going to be at the show, let's see if we can hook up. Email me directly, or reach me on twitter @julianhyde. If you want to try to randomly bump into me, first try the Pentaho booth, then try the bar...

Improved collections classes for Mondrian's query execution process

2010-02-16T18:24:00.000-08:00

Mondrian calculations work predominantly over lists of members and tuples. Internally, Mondrian represents lists of members as List, and represents lists of tuples as List. (And similarly for iterators over members and tuples.)

There are two problems with this. First, the representation of tuple lists requires an array to be allocated for each element of the list. Allocations cost time and memory. (Granted, we could allocate temporary arrays only when tuples are accessed, which would cost only time. But according to my latest round of profiling, the effort of allocating lots small arrays is significant.)

Second, the code to deal with members and tuples has to be different. The most extreme example of this found in the implementation of CrossJoin. There are over 30 inner classes in CrossJoinFunDef.java, to deal with the permutations of iterator vs. list, mutable vs. immutable, and tuple vs. member.

In short, the java standard List and Iterator classes are not serving us well. I think it's appropriate to introduce classes/interfaces that handle members and tuples more uniformly, and can store, access, and iterate over collections without lots of small arrays being created.

Here are some collection classes that I think would serve the purpose:

interface TupleList {
    int size();
    int arity();
    Member getMember(int index, int ordinal);
    TupleIterator tupleIterator();
}

interface TupleIterator {
    int arity();
    boolean hasNext();
    // writes the members of the next tuple into given array
    void next(Member[] members);
    // appends members of the next tuple to given list
    void next(List<Member> members);
}

If arity = 1 (i.e. if the list is just a collection of members) then TupleList could easily be implemented using java.util.ArrayList.

For other arities, a list of tuples could be represented as a set of members end-to-end. For instance, the list with two 3-tuple elements {(A1, B1, C1), (A2, B2, C2)} would be held in a list {A1, B1, C1, A2, B2, C2} and getMember(index, ordinal) would read element index * arity + ordinal of the list.

Introducing these would require quite a few code changes, mostly in the mondrian.olap.fun package, which is where the builtin functions are implemented. There should be no changes to the user API or olap4j.

I am still debating whether this change makes sense. Usually this kind of penny-pinching architectural change doesn't pay off. But some of them pay off big. I've learned in Oracle, Broadbase, and SQLstream that for high-performance data processing you shouldn't be doing any memory allocations in an inner loop that is executed once per row. That isn't quite practical in Java, but it's a goal to strive for. In today's CPU architectures, where memory is slow and last-level-cache is fast, it pays to keep data contiguous.

If you are a Mondrian developer, I'd be interested to hear what you think about this proposed change.

Data in Flight

2010-01-19T13:20:00.000-08:00

An article of mine, "Data in Flight," is published in this month's Communications of the ACM. In it, I took the time to explain, in layman's terms, why I think streaming database technology is a game-changer.

Many pundits have latched on to the term CEP (Complex Event Processing) to describe this technology. CEP is a legitimate and important application, and I believe that streaming SQL is a good way to solve it, but the article tries to put a bit of space between the two concepts. There are so many problems that benefit from the declarative, relational approach but where the data arrives incrementally and the problem can be solved much more efficiently by a streaming engine working (mainly) in memory than a database, and CEP is just one application area. My article describes a few of those problems.

I'm all fired up about streaming databases, just as I was when I co-founded SQLstream. I've worked in the database field for over 20 years, and I think it's the most exciting thing to happen in databases in a generation. (Yes, it's more important than data warehousing and, cough, object databases.)

Streaming SQL technology is rapidly becoming part of the standard toolkit for solving data management problems. If you're not familiar with the technology, reading the article is a good way to come up to speed. Enjoy!

xmla4js

2010-01-14T12:54:00.000-08:00

Yesterday I attended Roland Bouman's webinar introducing xmla4js. Xmla4js is a library for connecting to OLAP servers in JavaScript. All you need is an OLAP server that speaks XMLA (and most of them do).

It's classic web 2.0 technology. It does virtually nothing, yet changes everything. There is very little code (about 20K), an old-school enterprise architect would regard it as a trivial piece of protocol glue, and yet it opens the door to all kinds of mashups. Those mashups will get powerful OLAP data into the hands, and onto the screens, of the business users who care about that data.

Roland is a practical guy and a great communicator, so the presentation (and the download from google code) includes several examples of those mashups. I urge you to take a look at the recorded webinar.

A few issues came up during the webinar that are worth mulling over.

1. Query model

One thing that is missing is a query model. A query model allows you to represent the state of the current query, apply a transformation (say sorting on column #3, or adding a hierarchy to an axis), then generate a new MDX statement to send to the OLAP server. The demo Roland showed had a rudimentary query model, but in order to do more complex analyses, that query model will run out of road very quickly.

It's a problem that xmla4js shares with olap4j (as the PAT developers know only too well). I'd like to find a way to pool resources.

We could create a query model that works both on olap4j (in Java) and on xmla4js (in JavaScript). There would be two implementations, but at least the transformations can be specified in a language-neutral way, and we could write a single test suite that could exercise both implementations.

2. Cube metadata

Roland bemoaned the fact that getting the metadata for a cube (including dimensions, hierarchies, levels, measures) takes several XMLA round-trips.

He has a good point. Those round-trips may make the page load several times slower. We could easily extend Mondrian's cube metadata request so that you can ask for those extra elements.

If it proves useful, other XMLA engines (such as PALO) could do the same, and heck, if the XMLA council is not asleep in their castle, they could add it to the next version of the XMLA spec. (Well, we can hope.)

3. Results in JSON

Roland pointed out that XMLA is a verbose and inconvenient data format for JavaScript to consume. The "industry standard" for that environment is JSON. It has a similar attributes/values/nested sets structure to XML but is easier to parse: because it is syntactically valid JavaScript you just execute the JSON code to get the value.

Mondrian's XMLA servlet is written to generate elements, attributes, and nested collections of elements, and precious little of the code directly generates XML. It wouldn't be too much work to generate JSON instead. The JSON would have the same structure as the XMLA, sans the irritating namespaces that are necessary in XML.

For example, the JSON response from MDSCHEMA_CUBES could look like this:

"DiscoverResponse": {
  "return": {
    "root": {
      "row": [
        {
          "CATALOG_NAME": "FoodMart",
          "SCHEMA_NAME": "FoodMart",
          "CUBE_NAME": "HR",
          "CUBE_TYPE": "CUBE",
          "IS_DRILLTHROUGH_ENABLED": true,
          "IS_WRITE_ENABLED": false,
          "IS_LINKABLE": false,
          "IS_SQL_ENABLED": false,
          "DESCRIPTION": "FoodMart Schema - HR Cube"
        },
        {
          "CATALOG_NAME": "FoodMart",
          "SCHEMA_NAME": "FoodMart",
          "CUBE_NAME": "Sales",
          "CUBE_TYPE": "CUBE",
          "IS_DRILLTHROUGH_ENABLED": true,
          "IS_WRITE_ENABLED": false,
          "IS_LINKABLE": false,
          "IS_SQL_ENABLED": false,
          "DESCRIPTION": "FoodMart Schema - Sales Cube"
        },
      ]
    }
  }

I'd like to hear Roland's (and the Mondrian, Pentaho, olap4j and PAT community's) take on these points. Thanks again Roland for an informative webinar and a great new addition to the open source BI technology stack.

Survey of open source business intelligence adoption

2009-12-08T12:14:00.000-08:00

A new research report published by BeyeNETWORK analyzes people's use of open source BI. It is gratifying to see Pentaho, and Pentaho projects Mondrian and Kettle, at the top of their categories. Weka, another Pentaho project, is a a narrow second to R for statistics/data mining.

With 1,000 respondents in small, medium and large companies, this is not a small survey. Pentaho's dominance across several categories makes Jaspersoft's recent claim to be the most widely deployed BI software pretty difficult to believe.

Pentaho's strategy has been to build a suite of best-of-breed open source components, foster those components and their communities, and integrate them into a design-time and run-time platform. This report shows that this strategy is paying off.

The paper covers the bad stuff (performance problem areas, barriers to adoption) as well as the good, and surveys the information sources that people found useful in troubleshooting and getting to successful deployments. If you are evaluating open source BI products, the paper is well worth a read.

Pentaho Analyzer

2009-10-05T17:37:00.000-07:00

Pentaho today announced a new OLAP viewer, called Pentaho Analyzer Enterprise Edition, based on LucidEra's ClearView component.

This is great news for Pentaho customers, the community, and the BI world at large. While Pentaho Analysis (Mondrian) is one of its strongest components, the current OLAP viewer (based on JPivot) has been one of its weakest.

The new viewer puts Pentaho at the top of the heap, in competition with best-of-breed OLAP viewers. It is designed to be intuitive for business users (yes, those people who don't speak MDX!), is built using the latest web technologies, and integrates seamlessly with Mondrian and the rest of the Pentaho suite.

It is going to revolutionize the experience of using OLAP within the Pentaho suite.

Naturally, there are concerns. First, the new viewer is only part of Pentaho's Enterprise Edition (EE) suite. If Pentaho is committed to open source BI, why not release it open source? Second, what will happen to Pentaho Analysis Tool (PAT), the successor to JPivot being developed by the Pentaho community? I'd like to take the opportunity to answer these concerns, because I think this is news that everyone should be celebrating.

Why is the new Analyzer not open source?

There's been a lot of talk about open source business models, 'open core', good and evil, and all that. Releasing ClearView as part of Enterprise Edition is perfectly in sync with Pentaho's business model and with my intuitions about what makes sense for open source. Here's my rationale.

If you release a piece of software open source out of sheer, 'I love the world!' altruism, you won't necessarily see much benefit. Pentaho is a for-profit business, and they are savvy about leveraging the benefits of open source software. And let's not kid ourselves, there are considerable downsides to releasing something open source. Your competitors can pick up the software and incorporate your hard work into their suite. And your customers may decide that the free version is so good that they aren't going to give you any of their money.

Open source allows you to bring a component to a wider audience, an audience that will test, document and improve the component, and will support each other on the forums. Only the Community Edition (CE) components get that boost. Therefore, Pentaho's strategy is to release the core functionality in CE. That means the high-performance core of the system, the code paths that get run trillions of times an hour, and that means all the components that are necessary to build a functional and useful BI application.

In particular, people ask me whether there is a high-performance 'Mondrian on steroids' in EE. No there isn't. None of us want to maintain alternative code-paths, because the extra complexity would slow down future development. If I were to create a performance optimization in EE, the community would probably replicate that optimization in CE within a few weeks. Improving the core Mondrian system for everyone brings more people into the community, and that brings more people to EE.

And by the way, this doesn't just apply to the Pentaho Analysis part of the suite. Pentaho adds major new functionality to the suite each release, and most of that goes into open source components.

So, what's left to go into EE? Bells and whistles, things that make the product easier to use, easier to manage, and things that make your boss want to reach for his or her checkbook. And of course support, releases that are certified and indemnified, and more regular. I don't think that's a bad deal, however you look at it.

It also helps if the components are delivered under a business-friendly license like LGPL or EPL. Otherwise you will not attract contributions from OEM vendors, who are the companies with the skills to extend components as complex as Mondrian or Pentaho Data Integration (Kettle). Once again, Pentaho is taking a risk by using business-friendly licenses, because there is always a chance that Pentaho's competitors will scoop up the fruits of its labors. (As in fact they do.)

But Pentaho's faith in the open source process pays off. ClearView is proof of that. If Mondrian had not been available under a business-friendly open source license, LucidEra would probably have written it on top of another vendor's engine, and Pentaho would not have been able to use it. Incidentally, LucidEra has contributed many important enhancements to Mondrian in areas of both performance and functionality over the past three years. This has improved Mondrian for everyone, and we know that ClearView performs very well against Mondrian.

What will happen to PAT?

To restate what I said above, there is a network effect when you make a component open source. The more people that use a component, the more people are going to contribute to it. We want as many people to use Mondrian as possible, and in particular we want the right people to use it (the people who are going to make major improvements).

So, for Mondrian's continuing health as an open source component, we need the Community Edition of Mondrian to be good enough to build business applications on. For that, we need to make PAT successful.

I personally have been laying the ground work for PAT for a number of years. I spearheaded the olap4j API, knowing that the community would be more likely to write the next generation OLAP viewer if it was guaranteed to be portable across OLAP engines. Then I kicked off the halogen project, a collaboration between Pentaho developers and the community to build a viewer using olap4j and GWT. Pentaho developers contributed code and user interface design to that project, even working in their spare time when the current Pentaho sprint used up all of their 'official' cycles. And the PAT project used the halogen code, and the knowledge of the halogen developers, as a starting point.

It's not healthy to have too close a relationship between an OLAP server and viewer. There should always be room for competition, an opportunity to use a new viewer or (gasp!) different OLAP server if the 'standard' one isn't ideal. I created olap4j with competition in mind, and the experiment seems to be working: PAT can run against Mondrian's native interface, Mondrian's XMLA server, and against SQL Server Analysis Services via XMLA.

I want to make it easier to build alternative front-ends on top of olap4j, so I have been encouraging PAT developers to contribute to olap4j's query model and library of transforms. I would like to see Analyzer move to olap4j internally (it currently uses Mondrian's native API), and perhaps migrate some of the logic in Analyzer to olap4j so that we can share the costs of maintaining it with the community.

Lastly, as I realized at the recent community meetup in Barcelona, we have a great team, and we need to harness their energy. After a beer or two with PAT developers Tom and Paul, some inspiring demos from Pedro and Daniel, we hatched ideas of incorporating spark lines and writeback into PAT, and I'm sure the ideas will keep on flowing. With this much inspiration and hard work coming from the community, how can we possibly fail?

What API should Facebook and FriendFeed use to publish the social stream?

2009-08-17T04:11:00.000-07:00

Ars Technica reports that "social networking giant Facebook has acquired FriendFeed. This deal reflects Facebook's growing fixation on the social stream, but it's hard to see how the two services will be merged. [...]

[Facebook's] powerful but esoteric SQL-like query system all add up to a steep learning curve. By comparison, FriendFeed has a simple and elegant API that exposes a lot of information and is much more accommodating to developers."

It seems to me that streaming SQL is the correct solution to this problem. Not a SQL-like language, not an API (although you of course have to use an API to execute queries and get the results), and not just traditional SQL on finite relations, but SQL where streams are a first-class construct.

I'm not a big believer in 'SQL-like' languages; they give SQL a bad name. Someone once said that the C programming language combines all the power of assembly language with all the ease-of-use of assembly language. The same could be said for 'SQL-like' languages: they tend offer limited capabilities of a fixed API, but you have to learn a new language to do so.

Full SQL is difficult to implement because it must be possible to combine the relational operators (join, filter, union, project, and so forth), and other language features such as types and built-in operators, in any combination. Implementors often give up on this (what language designers call orthogonality), and what they get to is termed a SQL-like language. The full power of SQL only accrues when the implementor has implemented the whole language, and achieved orthogonality.

Nor can the problem be solved particularly easily or efficiently using regular SQL, because every query is going to be of the form 'tell me what has changed since I last ran the query'. That kind of activity throws a conventional database into a tailspin.

So, streaming SQL could solve this problem. Has anyone tried it?

Twitter makes the realtime web look more like the old web

2009-07-29T00:12:00.000-07:00

Twitter has a new home page, in the time-honored style of a search engine home page. Claire Cain Miller writes in the New York Times:

It has become a cliché that first-time visitors to Twitter respond with some version of: "I don’t get it." [The new home page] tries to solve that problem.

That problem is worth solving, but the home page is also an interesting sign of the melding of the old and the new.

Old web, new web

The old web is that vast repository of content, ranked by how many people reference that content, and navigated by search engines such as Google. The new web is populated by dynamic content, where what happened in the last minute is much more important than what happened yesterday.

The new, real-time web has been a wild frontier. There's a cachet to being a Twitter user; you're among pioneers, one of the elite who 'get it', not one of the ordinary folks. That's a problem for Twitter, because they need those millions of ordinary folks first to 'get it', then to get something useful out of it, come back, and start spending their click-through dollars.

But harnessing the power of the real-time web is no easy problem. First of all, streaming content is a new paradigm. Facebook are doing well at introducing a lot of people to that idea of the ever-changing home page; Twitter's minimalist concept needs more getting used to, but the search engine front-end to the stream of chatter will surely help.

Second, you need different tools to convert the noise in Twitter other social media feeds into information. A search engine is not going to cut it. The new tools cannot work on the streaming data alone; they have to combine the new data with old, organize the data, and cluster the data with other data that is similar based on subject matter, geographical proximity, or proximity of users in the social network. The stream of content hurtling past our eyes looks like chatter, just noise, until we rank it, look for trends, and put it into historical context.

Old analytics, new analytics

I find it interesting because at SQLstream we are dealing with a very similar problems for enterprise data. Business users would like to see the full spectrum of data, from right now to the distant past, but when making decisions, they want more recent data to carry more weight; they also want to take into account similarity of subject matter, geographical proximity, and the structure of social networks.

Traditional analytic solutions use data warehouses, analogous to the old, static, web and its search engine guardians. A data warehouse treats all data equally, regardless of its age. There is so much data that it has to be stored on disk, and it takes several hours to organize that data, so while a typical data warehouse will contain data from five years ago until close of business yesterday, the most important data — what happened today — hasn't reached the data warehouse yet.

SQLstream melds the old (the data warehouse) with the new (streaming events and transactions arriving over the wire), presenting a unified view via the SQL query language. We say that we "query the future", meaning that you can place standing queries that react when events of interest occur. These queries cache their working sets in memory, so the response time is a few milliseconds, and throughput tens or hundreds of thousands of records per second.

The data sources that SQLstream can handle are diverse. Some of the data comes from traditional sources, like corporate transaction processing systems. Some sources are often considered too high-volume to process in a data warehouse, such as click-stream and system monitoring data. And there are new sources like Twitter, social media, Atom and RSS feeds.

The problems of the real-time web and the real-time enterprise are surprisingly similar. Without tools to filter, aggregate, rank, and provide historical context, all of these data sources just look like noise and have little apparent value. At SQLstream, we are providing the tools to convert streams into valuable information.

An unfortunate fellow named Hyde

2009-07-26T10:11:00.000-07:00

A limerick featuring my family name and ending with a pun. What could be better?

An unfortunate fellow named Hyde
fell down an outhouse and died.
By mischance, his brother
fell down another.
And now they're interred side-by-side.

(Reportedly due to Johnny Carson.)

Introduction to Pentaho Analysis

2009-07-23T12:45:00.000-07:00

Joshua Tolley has written a nice step-by-step guide to using Pentaho Analysis. The tutorial is from the end-user's point of view, which of course is the most important perspective.

If you're interested in the back-end stuff, a couple of weeks ago Joshua also wrote a nice MDX primer.

Functional dependency optimizations in Mondrian

2009-07-10T17:45:00.000-07:00

Eric McDermid just checked in a nice new feature into Mondrian which optimizes the SQL generated by MySQL. It takes advantage of the fact that in MySQL, if some of your GROUP BY columns are unique, you can leave the other columns out of the GROUP BY clause, and MySQL does less work.

In some cases, a lot less work. MySQL implements GROUP BY by sorting, and since this reduces the volume of data being sorted, Eric reports significant performance improvements. Unfortunately it only works on MySQL, since MySQL is the database I know which has this feature.

See the latest schema documentation for more details.

I'll note that we reserve the right to change the syntax a little in future versions. In mondrian-4.0 we're adding physical schemas, which will include much more information about tables and relationships, so it would make sense to declare unique keys along with that. But rest assured that even if we do change the syntax, the feature will still be present.

SQLstream powers Firefox 3.5 realtime downloads monitor

2009-06-30T16:04:00.000-07:00

Mozilla launched Firefox 3.5 today, and with it, a neat applet, powered by SQLstream, to monitor downloads in real time.

You can see the results at Mozilla's download stats page.

A few weeks ago, Apple's Hyperwall was awe-inspiring as a piece of visual art, but it was less impressive as a piece of real-time data integration, because the data was delayed five minutes from the app store.

SQLstream gathers data from Mozilla's download centers around the world, assigns each record a latitude and longitude, and summarizes the information in a continuously executing SQL query. Data is read with sub-second latencies, and then aggregated (using SQLstream's streaming GROUP BY operator) into summary records each describing a second of activity.

A server-side Java program reads the data using JDBC, serializes it as JSON, and transmits it to all connected web clients. Clients render the charts using the Canvas tag, newly introduced in HTML 5. The results are very impressive visually, but to a back-end guy like myself, the plumbing is impressive too.

The amazing thing is that SQLstream makes this so easy. Our official company blurb talks about "shortening data integration projects from months to weeks", but this project took just a couple of days of work.

By the way, don't try to view the page in Microsoft's Internet Explorer. Ten years ago, Internet Explorer led the charge to enhance the capabilities of the web browser, introducing dynamic HTML (DHTML), XML handling in the browser, ActiveX controls and other capabilities, but those days are over. With HTML 5 there is a renaissance in web standards; Firefox is leading the pack, with other 'modern' browsers such as Safari, Opera and Chrome not far behind.

Black screen, white pointer on Vista

2009-06-28T13:24:00.000-07:00

Last night I had a problem where Vista gives me a black screen and white pointer. You can move the pointer around, but you can't do anything useful (except, as noted below, press the Shift key 5 times). I tried restarting in safe mode, and I got a black screen with 'Safe mode' in each corner of the screen, but otherwise the same experience.

I had a huge sinking feeling. I've had this problem twice before in the last twelve months. On the other two occasions, Dell technical support asked the usual questions for an hour or so, then told me to re-install Vista. An operating system that needs to be re-installed every 6 months is not a productive operating system, even if the operating system is great in between times. Which Vista isn't, anyway.

Luckily, this time I found this post at Microsoft TechNet. The problem was exactly as described in the post, and so was the cause (corrupted windows event log files) and the solution (rename the directory, or delete the event log files).

I'm pleased to say I discovered the same hack that they did: press the shift key five times, which gives you the 'Do you want to turn on Sticky Keys?' dialog. (Yes, this is literally the ONLY meaningful interaction you can have with what is obviously an instance of Vista which is functioning but just not listening.) Then you click on the 'Go to the Ease of Access Center do disable the keyboard shortcut', and because this brings up an explorer window, you can then type in the address bar to launch all kinds of other commands.

Thanks to towz and others who posted to that forum; proving that even for Microsoft products, the crowd can sometimes provide better technical support than the professionals.

Spatial OLAP using GeoMondrian

2009-06-17T17:35:00.000-07:00

I received an email from Thierry Badard of Laval University, Québec:

After the release of a new version of its open source spatial ETL tool, GeoKettle yesterday (please see the announcement for more details), the GeoSOA research group at Laval University, Quebec, Canada is proud to announce the availibility as new open source projects of GeoMondrian, the first implementation of a Spatial OLAP (SOLAP) server and Spatialytics, a lightweight cartographic component which enables navigation in SOLAP data cubes.

GeoKettle, GeoMondrian and Spatialytics are components of the complete geospatial BI (Business Intelligence) software stack developed by the GeoSOA research group.

For some screenshots of the project, see Fabio D'Ovidio's blog.

This is the kind of news that makes me proud to have gotten involved in open source. I'm not an expert on spatial software, so I could never have written a spatial OLAP engine; I didn't even realize the need existed. I'm delighted that people who are experts in the field could build upon my efforts and all of the other people who have contributed to Mondrian and JPivot over the years.

I hear that they are also involved in a Google Summer of Code (GSoC) project to integrate with Pentaho Community Dashboard Framework (CDF). That makes a lot of sense.

And of course I would be happy to receive contributions back to Mondrian if it makes it easier for them to maintain the code base.

I wish the GeoMondrian, GeoKettle and Spatialytics projects every success, and look forward to them bringing BI to a new audience.

Cell writeback in Mondrian

2009-06-11T18:18:00.000-07:00

Writeback is a feature that allows you to modify OLAP cell values and see the effects ripple through the data set, automatically modifying child and parent cells, and also cells derived using calculations. This allows you to perform 'what if' analysis and applications such as budgeting.

I have added experimental support for writeback to Mondrian.

In Mondrian's case, the term 'writeback' is a bit misleading. In a ROLAP system such as Mondrian, writing back to the database would be difficult, since values are stored in a fact table but we allow cells of any granularity to be modified. One modified cell might contain thousands of fact table rows. So, we don't write cells back to the database, but just retain the modified cells in memory, and propagate the modifications to related cells.

Here's how to use the experimental writeback support. Some of the details may change later as we make the feature more usable.

First, enable writeback for your cube. Create a dimension called 'Scenario', and a measure called 'Atomic Cell Count':

<Cube name='Sales'>
    <Dimension name='Scenario' foreignKey='time_id'>
        <Hierarchy primaryKey='time_id' hasall='true'>
            <InlineTable alias='_dummy'>
                <ColumnDefs>
                    <ColumnDef name='foo' type='Numeric'/>
                </ColumnDefs>
                <Rows/>
            </InlineTable>
            <Level name='Scenario' column='foo'/>
        </Hierarchy>
    </Dimension>
    <!-- Other dimensions... -->
   <Measure name='Atomic' aggregator='count'/>
    <!-- Other measures... -->
</Cube>

(Yes, this is a lot of crud to add to your cube definition, and it's temporary. In future, we will let you flag a cube as 'writeback enabled', and a [Scenario] dimension and [Atomic Cell Count] measure will be created automatically. Also, we will make it easier for you to create dimensions that have only calculated members, without resorting to inline tables.)

Next, create a Scenario:

Connection connection;
Scenario scenario = connection.createScenario();
int scenarioId = scenario.getId();

(The Scenario API will soon move to olap4j: before mondrian-4.0, I hope. This includes the class mondrian.olap.Scenario, the method mondrian.olap.Cell.setValue(), and the method mondrian.olap.Connection.createScenario(). It will be optional for an olap4j driver to support writeback, but Mondrian's olap4j driver will, of course.)

Write a query that uses the scenario. Assuming that scenarioId above was 1, the query

SELECT [Measures].[Unit Sales] ON COLUMNS,    {[Product],
     [Product].Children,
     [Product].[Drink].Children} ON ROWS
FROM [Sales]
WHERE [Scenario].[1]

returns

[Product]                 [Unit Sales]
========================= ============
(All)                          266,773
 + Drink                        24,597
 +--+ Alcoholic Beverages        6,838
 +--+ Beverages                 13,573
 +--+ Dairy                      4,186
 + Food                        191,940
 + Non-Consumable               50,236

Choose one of the cells returned from the query and modify its value. For example, let's reduce the sales of Drink by 1,000 from 24,597 to 23,597:

Result result = connection.executeQuery(...);
Cell cell = result.getCell(new int[] {0, 1});
cell.setValue(23597, AllocationPolicy.EQUAL_ALLOCATION);

Execute the query again, and it returns

[Product]                 [Unit Sales]
========================= ============
(All)                          265,773
 + Drink                        23,597
 +--+ Alcoholic Beverages        6,563
 +--+ Beverages                 12,990
 +--+ Dairy                      4,043
 + Food                        191,940
 + Non-Consumable               50,236

The value for Drink is 23,597, as expected, and the values of its children have been correspondingly reduced.

How the value is allocated to the children (and in fact all descendants) is decided by the allocation policy. In this case, we specified EQUAL_ALLOCATION, which means that all atomic cells have the same value.

An atomic cell is the finest grained value that can be viewed multidimensionally; for this cube, it is an instance of a particular customer buying a particular product, on a particular promotion, on a particular day, in a particular store. That makes for an awful lot of of atomic cells, but there may be fewer atomic cells than fact table rows. If the fact table does not have a primary key on (customer, product, time, promotion, store) some cells may have more than one fact table row.

If instead we had written

cell.setValue(23597, AllocationPolicy.EQUAL_INCREMENT);

the query would have returned

[Product]                 [Unit Sales]
========================= ============
(All)                          265,773
 + Drink                        23,597
 +--+ Alcoholic Beverages        6,560
 +--+ Beverages                 13,022
 +--+ Dairy                      4,015
 + Food                        191,940
 + Non-Consumable               50,236

We notice that Beverages has not been reduced as much under EQUAL_INCREMENT policy than EQUAL_ALLOCATION policy; the average value for atomic cells of Beverages must be greater than for Drink as a whole.

Allocation policies are defined consistent with Analysis Services' UPDATE CUBE statement. Mondrian does not currently implement WEIGHTED_ALLOCATION or WEIGHTED_INCREMENT policies.

Treating scenarios as a dimension is an elegant and powerful idea. Using the Scenario dimension, you can easily switch from one scenario to another, or you can compare scenarios side-by-side.

Note that you can also set a connection's current scenario. This effectively becomes the default value for the Scenario dimension in that connection, so you do not need to specify Scenario in the slicer. However, there still needs to be an explicit scenario in the context when you call Cell.setValue(). I'm not sure whether the benefit of having a scenario for a connection outweighs the benefit/confusion, and we may discontinue this feature.

Remember, this is still an experimental feature. There is some cleanup to be done, some performance tuning, and the API needs to be moved into olap4j. But most importantly, it's not useful until a user interface, such as PAT or JPivot, supports scenarios and modifying cell values.

Webinar: Eliminating MySQL Bottlenecks and Replication Issues using Real-Time Queries & Continuous ETL

2009-05-26T16:12:00.000-07:00

Would you like to find out how to build a continuous ETL process integrating source systems, MySQL data warehouse, and Mondrian OLAP engine?

I'm going to be hosting a webinar tomorrow describing how to do this using SQLstream. (Basically a repeat of the webinar I gave at the MySQL conference this year, but many of you missed it.)

Join me and Damian Black, CEO of SQLstream, on the webinar at 11am PDT/2pm EDT tomorrow, Wednesday 27th May. To register for the webinar, visit https://www2.gotomeeting.com/register/668399275.

Explaining the structure of Mondrian schemas

2009-05-19T17:42:00.000-07:00

There are some major schema changes coming in Mondrian 4.0, and I'm writing up specifications for these so that everyone knows what's coming and has chance to influence it.

But before I do that, I thought I'd try to improve how we describe the structure of XML schemas in the present release, just a bit. I have tried a couple of things. First, I created an XML skeleton that shows which elements can occur inside which other elements:

<Schema> <Cube> <Table> <AggName> aggElements <AggPattern> aggElements <Dimension> <Hierarchy> relation <Closure/> <Level> <KeyExpression> <SQL/> <NameExpression> <SQL/> <CaptionExpression> <SQL/> <OrdinalExpression> <SQL/> <ParentExpression> <SQL/> <Property> <PropertyExpression> <SQL/> <DimensionUsage> <Measure> <MeasureExpression> <SQL/> <CalculatedMemberProperty/> <CalculatedMember> <Formula/> <CalculatedMemberProperty/> <NamedSet> <Formula/> <VirtualCube> <CubeUsages> <CubeUsage> <VirtualCubeDimension> <VirtualCubeMeasure> <Role> <SchemaGrant> <CubeGrant> <HierarchyGrant> <MemberGrant/> <Union> <RoleUsage/> <UserDefinedFunction/> <Parameter/> relation ::= <Table> <SQL/> <View> <SQL/> <InlineTable> <ColumnDefs> <ColumnDef> <Rows> <Row> <Value> <Join> relation aggElement ::= <AggExclude> <AggFactCount> <AggIgnoreColumn> <AggForeignKey> <AggMeasure> <AggLevel>

You can see the full version in the Mondrian schema guide.

This approach shows where things are located, but it doesn't show how many of each element can belong to a particular parent element, or the order in which they are required. So, I wrote up a small BNF grammar and used Clapham to generate a railroad diagram. For comparison, the railroad diagram for the work-in-progress mondrian-4.0 schema is here.

Clapham: A railroad diagram generator

2009-05-11T01:29:00.000-07:00

I don't work with the Oracle database very much anymore, and one thing I miss is their server documentation. I still have my old copy of the Oracle 7.3 SQL Language Reference, and sometimes I reach for it when the SQL:2008 standard has fuddled my brain and I want to be reassured that SQL can be simple, powerful and trustworthy. The calming effect is partly due to the authoritative tone, but the railroad diagrams describing the syntax of each command say 'Don't worry'.

For example, here is Oracle 10.2's CREATE TABLE:

Yes, railroad diagrams. You can easily get lost in something as large as the SQL language, with its hundreds of commands, keywords and unexpected clauses, and railroad diagrams are the map.

When it came to writing our documentation for SQLstream, we of course wanted to include railroad diagrams to illustrate our dialect of SQL. It's possible to construct the diagrams by hand, but it's tedious, error prone, and it's difficult to get the diagrams to look consistent. Unbelievably, we couldn't find a tool to generate them, so we ended up writing them by hand.

Now I've gotten a little breathing room after the release of SQLstream 2.0, I took a couple of days to write an open-source railroad diagram generator. I've released it on Sourceforge, and named it Clapham, after the South London town which is home to the most complicated railway junction you ever saw.

This has been a nice return to old-school open source, with its mantras "release early, release often"; and "don't whine: contribute". The diagrams aren't yet as pretty as Oracle's, but we're getting there. Even though this is the very first release, and the project is barely alpha, it has already generated charts for LucidDB's not inconsiderable SQL grammar.

More details at the home page, and you can download release clapham-0.1.003 from SourceForge. Contributions welcome, of course.

How Mondrian evaluates expressions

2009-04-30T10:43:00.000-07:00

When it comes to expression evaluation, Mondrian keeps things simple. It doesn't tend to cache the results of expressions, but calculates them each time they are evaluated. Eventually the calculation tunnels through all calculated members and ends up at an atomic cell. Atomic cells are retrieved from the database, and stored in the cell value cache, so they are only calculated once.

By the way, an atomic cell is not necessarily at the lowest level of the hierarchy; Mondrian would prefer to load cells at a coarse granularity, and leave the hard work of aggregating values to the database, or even better, an aggregate table. And Mondrian does its best to retrieve atomic cells in batches. It gathers together requests for lots of cells of the same granularity and generates a single SQL statement to retrieve them all at once.

Mondrian's 'keep it simple' scheme comes unstuck when a particular calculation is repeated many times over. Nick Goodman came up with a classic example of this in bug MONDRIAN-552. The query is as follows:

with member [Measures].[Profit Change] as
   ([Measures].[Profit], [Time].CurrentMember)
   - ([Measures].[Profit], [Time].PrevMember)
member [Measures].[Running Total] as
   ([Measures].[Profit], [Time].CurrentMember)
   + ([Measures].[Running Total], [Time].PrevMember)
member [Measures].[Average Daily Running Total] as
   Avg(
       Descendants(
           [Time].CurrentMember, [Time.Weekly].[Day])
      [Measures].[Running Total])
select
   {[Measures].[Profit Change],
     [Measures].[Running Total],
     [Measures].[Average Daily Running Total]} ON COLUMNS,
   {[Time.Weekly].[Week].Members} ON ROWS
from [Sales]

Note how [Measures].[Running Total] is recursive. The running total for week 3 is defined as the running total for week 2 plus the profit for week 3. To calculate the average running total for week 99, Mondrian computes profit for the first 99 weeks and to calculate the average running total for week 100, Mondrian computes profit 100 for the first 100 weeks. There's lots of wasted effort: Mondrian has computed profit 50,000 times when it could have done it just 100 times and cached the results.

The solution is simple: wrap the calculation for [Measures].[Running Total] in the Cache() function, and Mondrian will compute the value only once.

You will see that in the bug I come up with a couple of proposals for making Mondrian better. I don't think Mondrian should automatically cache every expression, because caching costs time and memory, and most expressions are only evaluated once or twice. And by the way, you should use the Cache function sparingly, for the same reason.

But it would be nice if Mondrian could automatically detect some cases where expression caching is desirable. The proposed 'cache' property of a calculated member would have three values: 0 (never cache), 1 (always cache) and null (Mondrian should use its best judgment). Most calculated members would leave the caching up to Mondrian, so we would need to come up with a simple, effective rule that governs caching before we implemented this feature. What do you think the rule should be?

PAT 0.2

2009-04-27T15:11:00.000-07:00

PAT (Pentaho Analysis Tool) project renews my faith in open source. A team of folks from Pentaho's community have got together and are cooking up a new UI for Mondrian. Due to the magic of olap4j, it will work against other OLAP engines too.

They just released version 0.2, and Tom's release notes are an amusing and informative history of the project. Download from the project home page.

Formatting MDX as plain text

2009-04-10T17:31:00.000-07:00

When Mondrian tools output MDX results as text, such as in the cmdRunner utility, we've been using the same old crappy format for years. For example, the query

select
  crossjoin(
    {[Time].[1997].[Q1],  [Time].[1997].[Q2].[4]},
    {[Measures].[Unit Sales], [Measures].[Store  Sales]}) on 0,
  {[USA].[CA].[Los Angeles],
   [USA].[WA].[Seattle],
   [USA].[CA].[San Francisco]} on 1
FROM [Sales]

is formatted as

Axis #0:
{}
Axis #1:
{[Time].[1997].[Q1], [Measures].[Unit  Sales]}
{[Time].[1997].[Q1], [Measures].[Store  Sales]}
{[Time].[1997].[Q2].[4], [Measures].[Unit  Sales]}
{[Time].[1997].[Q2].[4], [Measures].[Store Sales]}
Axis #2:
{[Store].[All Stores].[USA].[CA].[Los Angeles]}
{[Store].[All  Stores].[USA].[WA].[Seattle]}
{[Store].[All Stores].[USA].[CA].[San  Francisco]}
Row #0: 6,373
Row #0: 13,736.97
Row #0: 1,865
Row #0: 3,917.49
Row #1: 6,098
Row #1: 12,760.64
Row #1: 2,121
Row #1: 4,444.06
Row #2: 439
Row #2: 936.51
Row #2: 149
Row #2: 327.33

I've just checked in an alternative formatter that makes the result look more like a pivot table. The same query would come out like this:

                     1997       1997        1997        1997
                     Q1         Q1          Q2          Q2
                                            4           4
                     Unit Sales Store Sales Unit Sales Store Sales
=== == ============= ========== =========== ========== ===========
USA CA Los Angeles   6,373      13,736.97   1,865      3,917.49
USA WA Seattle       6,098      12,760.64   2,121      4,444.06
USA CA San Francisco 439        936.51      149        327.33

Two questions:

1. Should we move this code into the olap4j code base? (It would seem to make sense because it doesn't require any mondrian internals to do the job, and the processing requires a 'grid model' similar to query models already part of olap4j. But I don't want to 'dump' code that is not generally useful.)

2. What do people feel is the ideal format for formatting MDX results as text? As a starting point, another couple of possible formats are below.

"Oracle" format

                     1997
                     Q1                      Q2
                                            4
                     Unit Sales Store Sales Unit Sales Store Sales
=== == ============= ========== =========== ========== ===========
USA CA Los Angeles        6,373   13,736.97      1,865    3,917.49
    WA Seattle            6,098   12,760.64      2,121    4,444.06
    CA San Francisco        439      936.51        149      327.33

"MySQL" format

|                          | 1997                                                |
|                          | Q1                       |  Q2                      |
|                          |                          | 4                        |
|                          | Unit Sales | Store Sales | Unit Sales | Store Sales |
+-----+----+---------------+------------+-------------+------------+-------------+
| USA | CA | Los Angeles   |      6,373 |   13,736.97 |      1,865 |   3,917.49  |
|     | WA | Seattle       |      6,098 |   12,760.64 |     2,121  |    4,444.06 |
|     | CA | San Francisco |        439 |     936.51  |        149 |      327.33 |

The last MDX holdout folds, but true OLAP interop is still a long way off

2009-04-07T23:58:00.000-07:00

Oracle, the last major OLAP vendor to embrace MDX, has finally added MDX support to its server. The MDX Provider for Oracle OLAP, developed in partnership with Simba, implements the OLE DB for OLAP API and the MDX query language, and went beta this week.

The most obvious application of this technology, and I'm sure the initial revenue driver, will be to allow end-users to use Excel 2007 as their client for slicing and dicing.

Simba's architecture diagram shows the MDX provider loaded onto the same machine as the Excel client. It wouldn't seem technically difficult to run the MDX provider as a server, and have multiple clients connect via OLE DB for OLAP or via XML for Analysis. (Licensing may be a different matter.)

This announcement means that now it is possible to talk MDX to every major OLAP server. (Are there any OLAP servers that do not speak MDX? I can't think of any.) The OLAP market has moved very slowly towards standardization, but this is a significant moment, even a tipping point. In a conversation five years ago, Oracle executives agreed that MDX was a fine language, but said they would not support it, because that would be to acknowledge that Microsoft was the thought-leader in the OLAP marketplace. It's that old PR strategy: deny in public, agree in private. And in a sense their strategy worked, because without a standard language, the OLAP market could not begin to commoditize.

There is still a long way to go towards OLAP interoperability. Servers differ widely in their support of MDX. Unlike SQL, the MDX language is not in the hands of an independent standards organization; even the originators of the de facto standard, Microsoft, have not released a specification for MDX or XMLA for several years.

A query language is no good without an API to issue queries, and APIs only exist in Microsoft's own technologies: COM (OLE DB for OLAP), .NET (adomd.net) and web services (XMLA).

I have been advocating olap4j as the standard API for Java-based OLAP, but it has yet to receive public backing from vendors outside the open source community. And there are no OLAP APIs for languages such as python, perl, and php.

The final point of concern is the emergence of Simba as virtually the sole supplier of MDX, OLE DB for OLAP and XMLA technology. Simba is an excellent company, who understand MDX very well, and have invested in building a technology stack. But they also benefit from a close relationship with Microsoft. (Remember those specifications for MDX and XMLA I referred to earlier? Though they have not seen public updates for several years, I'm sure those specifications still exist behind the walls of Castle Redmond, and are available to Microsoft's partners.)

As far as I am aware, Simba have been responsible for all of the projects in the last few years to bolt MDX support on to existing servers and applications. (With a sole exception: I was never able to find out where JasperSoft sourced the technology for its ODBO Connect product.)

To summarize, this is a milestone moment in the development of OLAP technology, but there is still cause for concern. OLAP APIs exist only for a small number of languages, vendors show little inclination to provide true interoperability, and the key technology is provided by a small number of players.

You can help. If you are a user of OLAP technology it is in your interests to see the emergence of standards in the OLAP marketplace. So, please ask your vendor what they are doing about interoperability. Ask them whether there are OLAP clients, other than their own, that run on their server. And ask them for APIs to connect to their server from all of the languages you use in your organization. Then, we may move a little closer to the goal of OLAP for all.

A what-if scenario: musing about adding writeback capability to Mondrian

2009-04-05T15:50:00.000-07:00

The Pentaho Partner Summit last week was a great chance to meet people who are using — and being successful — with Mondrian.

As always, people are thinking of using it in ways that I hadn't imagined. A couple of comments got me thinking about adding writeback support, something we'd long talked about, but seriously considered implementing.

Writeback allows the OLAP end-user to modify cell values and see the effects ripple through their spreadsheet. As you can imagine, it is useful for doing what-if analysis, especially budgeting.

If the cell is a sum of finer-grained cells, we need to modify those finer-grained cells also, and all of the totals of other dimensionalities created from those finer-grained cells, otherwise things just don't add up. This is hard to implement, because you sometimes need to modify a lot of cells, and even harder for ROLAP engines like Mondrian, because such engines don't store cells, they read directly from the unaggregated fact table.

First, I went looking for existing APIs for writeback.

Microsoft offers support for writeback via the UPDATE CUBE MDX statement. As always with Microsoft's MDX support, it's difficult to tell whether this is 'standard MDX', but the command seems to be well thought-out. The fact that it is an MDX command rather than an API call allows them to use an MDX expression as the rule by which to pro-rate changes to child cells.

I also looked at the JPalo Java API. (I've always wanted to work more closely with Palo. Although they're an OLAP engine, they have a different architecture (C and MOLAP) and core target audience (Excel users), and they're open source, so I see a lot of benefit to them and us if we pool resources. I invited them to join the olap4j process early on, but they preferred to define their own Palo-specific Java API. I'm still hopeful.)

I downloaded their most recent release from SourceForge and found that it was a release out of date (2.0 versus 2.5) and didn't contain the source code. There is a more up-to-date version in subversion. In DbConnection I found the setDataNumericSplashed method:

/**
  * Sets the given double value at the specified cell.
  * The splashMode paramater is only important for consolidated cells and
  * determines how the value is scattered among the consolidated elements.
  * Please use the defined class constants for valid values. Although more
  * modes are currently defined only three are supported, namely:
  * SPLASH_MODE_DEFAULT, SPLASH_MODE_BASE_SET and SPLASH_MODE_BASE_ADD
  * @param cube {@link CubeInfo} representation
  * @param coordinates {@link ElementInfo} representations which specify the
  * coordinates
  * @param value the new value
  * @param splashMode the splash mode, use defined class constants
  */
public void setDataNumericSplashed(CubeInfo cube, ElementInfo[] coordinate, double value, int splashMode);

I couldn't find any more documentation than that, but 'splash mode' seems to be equivalent to Microsoft's update strategies USE_EQUAL_ALLOCATION etc.

There are several remaining questions. What are the right changes to the olap4j API to support writeback? Support for the UPDATE CUBE statement is the leading contender. I'd love to hear what the olap4j community — especially the folks building the Pentaho Analysis Tool — think of this API, and how they would expose writeback in their UI.

I presume we'll need a scheme for transaction management. End-users will want to save their work, come back another day and continue where they left off. Several end-users might be using Mondrian at the same time, and want to see their numbers, not anyone else's. So, I think we'll need to introduce a concept I'd call a 'scenario', which is a property of a connection and can be persisted.

We'll need to figure out how to implement writeback within a Mondrian's ROLAP-with-caching architecture. Writing to the fact table is not tenable, because the modified cells can be of a multitude of dimensionalities. Neither is writing to an aggregate table, for the same reason. Ideal would be to write to disk a minimal description of the cells the user has modified — in XML, say — and do the other magic in the caching layer.

Lastly, I just need to find time to implement it.

Time crosstab in Mondrian

2009-03-31T14:20:00.001-07:00

The Mondrian/Pentaho community are smarter than I am. Tom Barber wanted a way to display many months on one screen, and Pedro Alves figured out a way to display it as a years x months crosstab.

Being an open source vendor is like being Irish

2009-03-23T11:40:00.000-07:00

There's been a lot of flap recently about the definition of an open source vendor.

Just about every piece of software these days will have some fraction that is based on open source code, so every software company is to some degree an open source vendor. And in the process of developing this code, the company's developers will need to participate in the community of those projects, and possibly fix bugs and contribute features.

So, whether you are an open source vendor, or for that matter, open core vendor or free software vendor is a question of degree. That means that everyone gets to argue what level the bar is set, and it all descends into silliness.

It reminds me of the fact that, especially at this time of year, everyone in the United States gets to be Irish.

In contrast, there is a very clear definition for open source software. The beauty of it is that it doesn't matter whether the software is written by an employee of open source vendor, an anarchist student, or Microsoft-loving independent consultant. If it has an open source license, it's open source software. As simple as that.

Pentaho Partner Summit 2009

2009-03-11T15:27:00.000-07:00

Are you thinking of incorporating Mondrian, or perhaps the entire Pentaho BI Suite, in your application? The Pentaho Partner Summit, on April 2nd and 3rd in Menlo Park, CA, is a good chance to get an overview of the technology.

The first day will contain an executive overview for vendors thinking of using the technology. Chance to hear myself, Matt Casters (Kettle lead), Zack Urlocker (MySQL VP Marketing and general Open Source luminary) and Richard Daley (Pentaho CEO) hold forth, then a cocktail reception where you get to tell us what you think.

The second day will have in depth training for OEM, reseller, and systems integrator partners who are already building apps using Pentaho technology.

Read the agenda, get details, and register at http://www.pentaho.com/summit09/agenda.php.

Golden Gate Raptor Observatory is seeking volunteers

2009-02-27T16:47:00.000-08:00

Every fall, I volunteer with the Golden Gate Raptor Observatory, identifying and counting migrating hawks. The location is spectacular — Hawk Hill overlooks the Golden Gate Bridge — and the hawks even more so.

If you live in the Bay Area and think this might be your thing, attend one of the informational meetings: Wed 29th or Thu 30th April from 7 - 9.30pm, or Sat 2nd May 10am - 12.30pm.

(Can anyone identify the 3 birds at the top of the flyer? Bonus points for age & gender.)

Welcome, Sebastian Hyde!

2009-02-16T15:06:00.000-08:00

Those of you who follow me in Twitter and Facebook will know that my thoughts have been on the birth of my son, Sebastian Hyde, and not on open-source BI or streaming SQL as much as usual.

British fathers often celebrate their baby's arrival by "wetting the baby's head" at a local hostelry. Unlike the Christening ceremony that it is patterned after, this involves lots of beer but no baby. My friend Rhys (who, my American readers may like to note, is British but not English) devised a variation of that tradition he called "baby bingo", and we carried out the ceremony at Barclay's pub on Friday.

As my father well knows, beer and commemorative plaques go together. Rhys, Jacq and Sabine just presented us an excellent framed image listing the beers that we consumed in Sebastian's honor. I wonder what Sebastian will make of it when he's older?

Calculus: it's so basic that even moronic headline writers should know about it

2009-02-07T14:53:00.000-08:00

The following headline in the Daily Telegraph struck me as really odd:

"Facebook at five: Ten times more traffic to Twitter website than Facebook in last year"

The actual facts in the article show that the headline was patently false:

"Over the last year, traffic to Twitter [...] has increased by 1191 per cent, while traffic to Facebook has grown just 110 per cent"
"Facebook [...] received 133 times more UK internet visits than Twitter"

If the headline had read "Ten times more growth in traffic to Twitter website than Facebook in last year" or "133 times more traffic to Twitter website than Facebook in last year", it would have been correct. I'm guessing that the headline writer omitted the word 'growth', presumably to save an inch of headline space, and turned the truth on its head: by a factor of a thousand.

The problem is that journalists are confusing a quantity and the time derivative of that quantity, and it bugs the heck out of me. Journalists who have a professional respect for punctuation, grammar and fact-checking seem to have a disdain for basic numeracy concepts like time derivatives. Do they not understand the difference, or do they think that we're too dumb to notice?

I hear financial journalists trotting out that such and such tax would "raise 15 million pounds". What, on the very morning it is introduced? No; we, the reader, are supposed to insert "per year" to compensate for the journalistic shorthand.

And here in the U.S., the terms "deficit" and "debt" are often used synonymously in the public discourse, where in fact, one is the derivative of the other. With record deficits and national debt looming, and astronomical numbers that we have a duty as citizens to try to comprehand, we need the help of journalists more than ever to help us make sense of the world.

Michael Cote on SQLstream

2009-02-01T14:45:00.000-08:00

In their weekly podcast John M. Willis discusses SQLstream with Redmonk's Michael Coté.

[Edit: This originally read "In his weekly podcast, Redmonk's Michael Coté discusses SQLstream with John M. Willis."]

"Is anybody foolish enough to think that one screen is going to be able to tell you what is going on?

I've actually seen customers try [...] to do ETLs every 15 minutes in order to look at what's goin' on, aggregating your data and doing analytics on your data, [...] which is at least a 15-fold [improvement over] using a single-pane-of-glass. [If you do that,] you can avert major disasters because you're watching the trend happen.

I think that something like SQLstream, or something like that, that is watching it as it goes [...] has brilliant potential."

The action starts at around 1:10:00 in the podcast.

Recursive event-driven demo

2009-01-30T15:32:00.000-08:00

I just gave a demo of SQLstream to Joe di Paolantonio. I gave Joe a couple of the usual demos, including the one where we read feeds from Twitter, Google news and various RSS feeds. Joe sent a real-time commentary as a series of tweets, and these duly showed up in the demo.

A bit of a stunt, but it was cool, in a geeky Hofstadter-esque recursion kind of way. Then we had lunch; good chinese food, and blissfully offline.

Event-driven marketing

2009-01-29T18:19:00.000-08:00

David Raab writes a great piece on SQLstream, its internals, and how it can be applied to event-driven marketing.

SQLstream 2.0

2009-01-26T09:19:00.000-08:00

SQLstream release 2.0 hit the streets today. It's actually our third release, but it's the first one we've made a fuss about. Since release 1.1, we have hardened the product at customer deployments, introduced 64 bit support to allow larger working memory, and added two major SQL extensions: streaming aggregation and user-defined transforms.

Streaming aggregation adds support for the GROUP BY construct to streaming SQL, and allows us to compute totals on a periodic basis, for example computing hourly subtotals. SQLstream acts as a continuously operating conduit between operational systems and the data warehouse, replacing the traditional batch-based ETL process and populating the fact table and aggregate tables simultaneously. This gives us a natural synergy with my other project, the Mondrian OLAP engine, and we have been getting good take-up among Pentaho's customers.

Streaming aggregation builds on relational operators in SQLstream 1.1 such as joins, windowed aggregations and unions. Those relational operators allowed you to build queries such as fraud detection, looking for anamolous rows in real time. Those were 'needle in the haystack' kinds of problem, and the new features also allow you to do build a high-performance 'water main' between your operational system and data warehouse.

User-defined transforms allow you to define new relational operators in Java and incorporate them into streaming SQL statements. SQLstream 1.1 had user-defined functions, to allow you to compute scalar quantities in Java, and syntactically, user-defined transforms are simply functions that have JDBC ResultSets and PreparedStatements as parameters. If a function has a PreparedStatement as a parameter, SQLstream lets you include it in the FROM clause of a SQL statement as a data source, alongside regular streams, tables, and views. Similarly, if a function has a ResultSet as a parameter, then you can pass in a cursor based on a SELECT statement as an argument.

User-defined transforms are an excellent example of our ongoing collaboration with the open-source Eigenbase project. User-defined transforms were originally developed for LucidDB to operate on traditional stored relational data, with SQL:2003-compliant syntax, and we extended them to handle streaming relational data, but keeping the syntax the same. For more about user-defined transforms in LucidDB, see the excellent documentation at Eigenbase.

Release 2.0 is a major milestone for SQLstream, and is the culmination of several years of development. It allows you to tackle in industry-standard SQL some application areas that previously required guile and custom coding. Go to www.sqlstream.com and see whether there is a fit with your real-time BI application.

Pentaho Analysis Tool

2009-01-22T10:08:00.000-08:00

Some folks are working on an olap4j-based viewer as an alternative to JPivot, called Pentaho Analysis Tool.

The key developers Tom and Luc tell me that they noticed that the halogen project hadn't changed in a few months, so they took the halogen source code (based on GWT, by the way) and started to take it in the direction of the OLAP viewer they'd like to see.

(Edit: There are actually three key developers. I forgot to mention Paul Stöllberger. Sorry Paul!)

No hard feelings! I, and some other key Pentaho folks, are delighted that this project is happening, and will support it any way we can.

It's ironic that when Pentaho seeded a project to build an olap4j-based viewer, they chose an organic, open-sourcey name 'halogen', yet these folks (none of whom works for Pentaho) chose a name that whiffs of corporate branding.

A rose by any other name, as they say. And despite the name, the viewer should work on top of any olap4j data source (which today means Mondrian and any OLAP engine with an XMLA interface).

Gartner releases Magic Quadrant for Business Intelligence

2009-01-22T08:35:00.000-08:00

Gartner have released their 2009 Magic Quadrant for Business Intelligence (via DBMS2).

Pentaho has not made it onto the quadrant diagram yet (I suppose because they have not crossed the $20M revenue threshold), but earns its own paragraph in the accompanying commentary:

Pentaho, after just four years in existence, has put together a comprehensive open-source BI platform that includes data integration and data mining capabilities. In 2008, Pentaho was noticeably more aggressive, openly competing against traditional BI platform vendors. Like Jaspersoft, Pentaho is affordable and also offers a subscription-based model that avoids an initial large payment for the software license. Some of the significant features Pentaho introduced in 2008 include an automatic table designer that analyzes relational schemas and data patterns, performs a cost-benefit analysis of aggregation at different levels, and generates and populates those aggregate tables. Despite a handful of large customers, Pentaho reference survey respondents more frequently indicated that they had more departmental deployments (versus enterprisewide) and smaller data volumes compared with the other vendors.

Nice that the aggregate table designer gets a call-out. It's very important in helping Mondrian scale to enterprise-scale data warehouses. (And besides, it was a lot of work to write!)

The report calls out the quality of Pentaho's customer support:

This was in evidence in the MQ reference survey, as both Jaspersoft and particularly Pentaho scored strongly on the customer support question — higher than any of the megavendors.

Hard-won lessons in Mondrian query optimization

2009-01-07T17:00:00.000-08:00

Mondrian is generally very smart in how it chooses to implement queries. Over the last month or so, I have learned some lessons about how hard can be to make Mondrian smarter.

As a ROLAP engine (I prefer to call it 'ROLAP with caching'), Mondrian's evaluation strategy has always been a blend of in-memory processing, caching, and native SQL execution. Naturally there is always SQL involved, because Mondrian doesn't store any of its own data, but the question is how much of the processing Mondrian pushes down to the DBMS and how much it does itself, based on data in its cache.

The trends are towards native SQL execution. Data volumes are growing across the board, Mondrian is being deployed to larger enterprises with large data sets (in some cases displacing more established, and expensive, engines). Mondrian cannot keep up with the growth by simply pulling more data into memory and throwing one or two more CPU cores at the problem.

Luckily a new breed of database engines, including Aster Data, Greenplum, Infobright, Kickfire, LucidDB, Netezza and Vertica, are helping to solve the data problem with innovative architectures and algorithms. To exploit the power of the database engine, Mondrian's ability to generate native SQL is more important than ever.

I have spent the last few weeks struggling to make Mondrian handle a particular case more efficiently. It was ultimately unsuccessful, but it was a case where defeat teaches you more than victory.

Here is the actual MDX query:

WITH
SET [COG_OQP_INT_s9] AS
  'CROSSJOIN({[Store Size in SQFT].[Store Sqft].MEMBERS},[COG_OQP_INT_s8])'
SET [COG_OQP_INT_s8] AS
  'CROSSJOIN({[Yearly Income].[Yearly Income].MEMBERS},[COG_OQP_INT_s7])'
SET [COG_OQP_INT_s7] AS
  'CROSSJOIN({[Time].[Time].MEMBERS}, [COG_OQP_INT_s6])'
SET [COG_OQP_INT_s6] AS
  'CROSSJOIN({[Store].[Store Country].MEMBERS},[COG_OQP_INT_s5])'
SET [COG_OQP_INT_s5] AS
  'CROSSJOIN({[Promotions].[Promotions].MEMBERS}, [COG_OQP_INT_s4])'
SET [COG_OQP_INT_s4] AS
  'CROSSJOIN({[Promotion Media].[Promotion Media].MEMBERS},[COG_OQP_INT_s3])'
SET [COG_OQP_INT_s3] AS
  'CROSSJOIN({[Store Type].[Store Type].MEMBERS}, [COG_OQP_INT_s2])'
SET [COG_OQP_INT_s2] AS
  'CROSSJOIN({[Marital Status].[Marital Status].MEMBERS}, [COG_OQP_INT_s1])'
SET [COG_OQP_INT_s1] AS
  'CROSSJOIN({[Gender].[Gender].MEMBERS},
    {[Education Level].[Education Level].MEMBERS})'
SELECT {[Measures].[Unit Sales]} ON AXIS(0),
  NON EMPTY [COG_OQP_INT_s9] ON AXIS(1)
FROM [Sales]
WHERE ([Customers].[All Customers].[USA].[CA].[San Francisco].[Karen Moreland])

The query looks a bit fearsome, but is quite likely to occur in practice as a business user slices and dices on several attributes simultaneously. The rows axis is a CrossJoin of ten dimensions, but because of the filtering effect of the slicer (combined with NON EMPTY) the query evaluates to a single row. The goal is to make Mondrian generate a SQL statement to evaluate the axis.

Each way that I tried to write the logic, I ended up making decisions that made other optimizations invalid. It was difficult to make Mondrian see the big picture: that, although named sets are not supposed to inherit the context where they evaluated, in this case it was OK; and to recognize a complex expression (many nested CrossJoin operators, slicer, and implicit non-empty context), and convert the whole thing into a single SQL statement. For instance, in one attempt I succeeded in generating a SQL statement which evaluates very efficiently, but in so doing I had to let the non-empty context of the evaluator leak into places that it shouldn't... which broke quite a few existing queries, in particular queries involving calculated sets.

There are several conclusions for Mondrian's architecture. One conclusion is that we need to deal with filtering non-empty tuples as part of the expression, not as a flag in the evaluator (the data structure that contains, among other things, the set of members that form the context for evaluating an expression).

MDX has an operator, EXISTS, that specifies that empty tuples should be removed from a set. Then we can reason about queries by applying logic-preserving transformations (just the way that an RDBMS query optimizer works), which should be safer than today's ad hoc reasoning. For example, if I am a developer implementing an MDX function and the evaluator has nonEmpty=true, am I required to eliminate non-empty tuples or am I merely allowed to eliminate them? (In other words, will my caller return the wrong result if I forget to check the evaluator flag?) I often forget, so I suspect that filtering of empty tuples is performed inconsistently throughout the Mondrian code base; which is a shame, because eliminating empty tuples early can do a lot for performance.

I'd also like to use the same model for native SQL generation as for other forms of expression compilation. Native SQL generation currently happens at query execution time: when the function is evaluated, it figures out whether it can possibly translate the logic (and the constraints inherited from the evaluation context) into SQL. That is currently unavoidable, because the nonEmpty flag is only available in the evaluator, at query execution time. And we need to do some work at query execution time, if only to plug in the keys of the members in the current context as predicates in the SQL statement. But I've seen several cases where we need to be smarter.

One example is 'NON EMPTY [Level].Members' that always gets translated into SQL even though the level only has two members and they are in cache. Cost-based optimization would help there.

Another example is where there are many layers of MDX functions — say Filter on top of CrossJoin on top of Filter — and these could be rolled into a single SQL statement. The right approach is to build a SQL statement by accretion, but it is too expensive to do every time the expression is evaluated.

Further, as we add more rules for recognizing MDX constructs that can turn into SQL, we will reach decision points where we choose to have to choose whether to apply rule A or rule B. Solutions are (a) using costing to decide which rule to apply, and (b) applying both rules and seeing which ultimately generates a better outcome. Neither of these solutions are suitable for query execution time: they need an optimization stage, as part of query preparation.

It's ironic, considering I've been building SQL optimizers for years (the first at Broadbase, and the second the optimizer for the Eigenbase project, which is used by both LucidDB and SQLstream) that I have avoided giving Mondrian a true query optimizer for so long. I know it's a lot of work to build an optimizer, and it's foolish to start before you know what problem you need to solve.

Don't expect to see any changes in the short term; this kind of architectural change doesn't happen fast. My struggle over the past few weeks has been a big step in seeing the big picture, and realize that the considerable pain and effort of unifying Mondrian's query planning system is justified by the potential benefits in performance.

Streaming content feeds part 2: forging the Streaming Web

2008-12-15T02:48:00.000-08:00

My previous blog post "Streaming analytics over content feeds (and how content feeds could be better)" drew some excellent comments, so I thought I'd follow up with some more thoughts about a protocol for streaming web content, and a vision that I'll dub the "Streaming Web".

To John Kalucki's points first. I absolutely agree that the driver for this protocol is latency. But it is difficult to answer the question "what latency is necessary?", because we don't yet know what applications people will devise.

(An illustration of how latency changes everything, from a very different business: when my wife worked for Niman Ranch, I was amazed to hear that they dispatch steaks via FedEx (packed in ice and insulation, and sent overnight); this would be out of the question using the USPS and a three day delivery time.)

I believe that real-time web content feeds are a game changer. I call it the Streaming Web — a web where every piece of content is accessible via a URL and you can subscribe to be alerted immediately if a piece of content changes. Every page would become a potential feed, and there would be agents that allow us to collect and filter content we are interested in: be it a friend's photo album or the price of a plane ticket.

A huge effort is required to make the Streaming Web a reality. The first steps, the web content formats such as RSS and Atom, are already in place. The next step is to introduce a protocols so that subscribers are notified of changes as soon as they happen.

John says:

What experience can you offer with feeds at a 50ms push latency vs a 180,000ms pull latency? If a machine is consuming the feed, not much. If a human is immediately consuming a feed, perhaps a great deal.

I agree that a human can benefit from low-latency content, although there is little benefit for content arriving faster than the human's think time — say 5,000ms. But if a computer is the consumer, ideal latencies span a broad spectrum: a mail server would operate more efficiently if it is allowed latencies in the minutes or hours, whereas an automated stock trading system needs information to arrive within 50ms.

Today, not much web content is of interest to automated stock trading systems. Most web content feeds today are textual — written by humans, and consumed by humans — but I believe that once we remove the latency constraints and introduce some standard protocols, we will start to see more structured data in feeds. Also, we will see algorithms for extracting information from textual feeds.

As for the right protocol for the job, I am not really the best judge, so I am going to punt for now, and focus on the architecture. Richard Taylor suggests XMPP. It seems to have the right qualifications, and I'm sure that it could be made to work technically. (And I see that XMPP is already a central part of the Twitter ecosystem.) It comes down to power versus simplicity: the power of an established standard versus the simplicity required to reach a new audience of developers.

I've been around long enough to see new approaches overturn "over-complex" existing technologies and then, in time, acquire the features that made their predecessors complicated. Take for example SOAP overturning CORBA, or PCs overturning minicomputers. I'm not going to take sides: these revolutions are part of the process of how technology moves forward. But it does seem that each revolution will only be successful if the new technology serves a new audience. And, to borrow Einstein's words, a protocol should be as simple as possible, but no simpler; otherwise, even if the technology finds its initial audience, it will not survive its growing pains.

I'm not a big fan of XML as a protocol for transmitting data over a network, mainly because it is bulky, and that makes it expensive to produce and consume at high data rates. But for this protocol, I would choose XML over a binary format. If you're a developer learning a new protocol, it's a lot easier to debug your code if you can read the messages being sent over the nextwork as text.

Which brings us to the audience for this protocol. I do agree with Stefan Tilkov that "[f]or the majority of use cases, [the polling] approach is vastly superior to a push model". That majority is already well served, so I'm focusing on the minority that need low latency. I think those use cases are important, and we'll all be using them if the "streaming web" thing catches on.

To achieve low latency feeds, push is more efficient than high-frequency polling, but it is still more expensive than low-frequency polling, which is what people are doing today. So, if every web content aggregator and RSS reader switched to a low-latency push protocol overnight, the system would collapse.

But luckily, there is no need for those millions of clients who would like to receive low-latency feed updates to connect using this new protocol. If those clients are humans, they will be happy to receive their updates via XMPP or SMS, or slower protocols like email. A single server could speak the streaming web feed protocol to various source feeds, and route the results to thousands of end users via XMPP or SMS. This approach means that each source feed is serving a modest number of downstream servers.

I'd describe it as a 'wholesale' architecture. A food producer has a central depot, where it loads its goods onto the trucks of several client stores. The food company allows consumers to buy from the depot, if they are prepared to buy their goods in bulk, but most consumers opt for the convenience of visiting a local store and buying their goods in smaller quantities.

(If you're Twitter, no problem is ever small, so that 'modest number' is probably in the tens of thousands. But I suppose that problem can be solved using multiple tiers of servers and fanning out streams between one tier and the next.)

The next step in the evolution of the architecture would be to introduce a query language. Queries present a more convenient interface for clients, but they would have architectural advantages. For example, using a query, a client can specify more precisely which content it is interested in. It would save CPU effort on the client and possibly the server, and bandwidth for everyone, so there would be a strong incentive to use queries rather than raw feeds.

Queries would also allow feeds to be virtualized: rather than talk directly to blogger and typepad, a client could talk to a third party that aggregates the content into a single feed.

Streaming SQL would be a good candidate for expressing these queries, but is by no means the only choice. And in fact the architecture and protocol would work well enough for clients that did not use queries and wanted to consume only raw feeds.

The resulting system, the Streaming Web, would enable applications yet to be imagined.

Streaming analytics over content feeds (and how content feeds could be better)

2008-12-12T14:57:00.000-08:00

We have been experimenting with different web-based data sources for SQLstream. Seth Grimes saw the demo, and wrote a piece "BI on Content Feeds, a.k.a. Continuous (Twitter) Transformation" in Intelligent Enterprise.

Social networks and web content feeds such as RSS have, in a few short years, added a dynamic component to the vast static content on the web. As less-sophisticated users have become more accustomed to consuming them, these feeds have become a ubiquitous part of the web experience.

Web feeds have an information content that is at present untapped. In the same way that a radical new approach — the search engine — was needed to harness the static information content of the web, a streaming analytics solution in this area becomes important sooner rather than later.

The SQLstream prototype illustrates how several data formats (tweets from Twitter, USGS quake data in RSS format, news from Google's Atom feed, and so forth) can be integrated into SQLstream.

For each data format we built an adapter that implemented the SQL/MED specification, and using these adapters we mapped each feed into SQLstream as a foreign stream. Once data is in SQL format, you can build views on top of these streams to filter, join and aggregate records.

Now we've done the hard part — getting the data feeds into a common format — there are plenty of ways to extract information from the feeds. For instance, it would be easy to find out which Twitter users are the most active over the last hour or the last seven days.

Or you could pull apart messages to discover word frequencies, and write a stream that detects words that are being used more frequently than usual (similar to Google zeitgeist but in real time).

But the prototype has some limitations: news items tend to arrive in bursts every couple of minutes, and many Twitter messages are missing. These are all limitations of the data sources with respect to latency (how soon messages arrive) and throughput (how many messages per second the system can handle), and the limitations stem from the inefficiencies of the web feed protocols.

You would think that something called a 'feed' would push content to subscribers as soon as it arrives, but in fact RSS and the other feed types in the prototype use a pull protocol. With a pull protocol, the subscriber needs to continually poll the feed to get the content (typically an XML document a few kilobytes long), parse the content, and figure out what, if anything, is new since the last time we polled.

This process soaks up a lot of network bandwidth and resources for both the provider and the subscriber, and the cost goes up the more regularly we poll. Typically the provider has to throttle the feed to prevent their servers from being overwhelmed. For example, Twitter updates its feed only once per minute and limits the number of tweets on the page. At times of high volume, only a small percentage of tweets make it into the feed.

This may not sound that serious if the content is a twitter conversation between friends, or a blog with one or two posts a week. But web feed protocols are becoming part of the IT infrastructure, and business users require lower latency, higher throughput and higher availability. (The existence of services like Gnip is evidence of the need to control the web content chaos.)

I would like to see the emergence of a genuine 'push' protocol for web-based content. It doesn't have to be particularly complicated. To illustrate what I have in mind, here is an example of a simple, stateless protocol, built using XML over HTTP, like the current feed formats. A subscriber sends a request

<readRequest>
  <minimumRowtime>2008-12-04 18:00:46.000</minimumRowtime>
  <maximumCount>1000</maximumCount>
  <maximumWait>10s</maximumWait>
</readRequest>

over HTTP, and the provider replies with a set of content records

<rows>
  <row>
    <rowtime>2008-12-04 18:00:46.217</rowtime>
  <category>U.S.</category>
  <title>Ex-FBI agent faces 30 years to life for mob hit - CNN</title>
  </row>
  <row>
    <rowtime>2008-12-04 18:00:46.714</rowtime>
    <category>More Top Stories</category>
    <title>Bill Richardson chalks up another Cabinet job for the resume - Los Angeles Times</title>
  </row>
  <row>
    <rowtime>2008-12-04 18:00:48.104</rowtime>
    <category>More Top Stories</category>
    <title>Showdown in Hebron as settlers evicted - Jewish Telegraphic Agency</title>
  </row>
</rows>

According to the protocol, the provider sends the results after 10 seconds, or when there are 1000 records to return, whichever occurs sooner. After it has received a result, the subscriber will typically ask for the next set of rows with a higher rowtime threshold.

Even though it is simple, the protocol ensures that data flows efficiently for feeds of all data rates. For a high volume feed, the 1000 record limit will be reached before the 10 second timeout, so latency naturally decreases. For a low volume feed, many requests may time out and return an empty result; but the 10 second wait limits the number of requests per minute that the server has to handle.

Naturally, I have in mind an even better protocol that allows subscribers to submit SQL queries, and of course every web would have a SQLstream server behind the curtain. But seriously folks... I would be satisfied with a lot less than that. A simple, open protocol for streaming content syndication would unlock the web and make it the medium of choice for streaming as well as static content.

Upgrading to ubuntu 8.10 Intrepid Ibex

2008-11-25T11:41:00.000-08:00

Someone had to take the plunge. I upgraded one of my development environments, my laptop (a Dell D630 which dual-boots to Vista) to Ubuntu 8.10 last night just to kick the tires.

Since 8.04 was a LTS (long-term support) release, 8.04 users will not get automatically upgraded. You have to explicitly ask for it, as described on the Ubuntu site.

(If you are surprised that 8.10 is the successor to 8.04, you need to know that Ubuntu releases occur approximately six months apart and are numbered {year}.{month}. So 8.10 was released in October 2008. As for where the {adjective} {animal} release names come from, I have never been bold enough to ask.)

The upgrade went smoothly. Everything worked right out of the box. Kudos to the Ubuntu folks, yet again: I have been able to do distribution upgrades since Ubuntu 7.06.

As for features, I haven't noticed anything different. I like to stay up to date, and for now I'm pleased that everything still works and looks the same. I'm sure I'll come across the good stuff in due course.

Not so great for Eigenbase developers, though. Fennel has problems building, which is not unsurprising considering its dependencies on C++ libraries and build tools. Ubuntu 8.10 installs libtool-2.2 (8.04 was libtool-1.5.26). I got some syntax errors in what looked like a generated bash script, possibly related to libtool.

I'm not going to attempt to track down and solve the problems here. I will do that on the fennel-dev list, in the next few weeks. Eigenbase developers should note that 8.10 is not yet a viable development environment; for everyone else, it's just fine.

Tutorial video for Kettle and Mondrian

2008-11-17T08:09:00.000-08:00

There's a nice video which shows how you can use Kettle together with the Mondrian Schema Workbench to populate a star schema and build cubes on top of it.

See "Pentaho Data Integration and Schema Workbench Tutorial" on the Pentaho web site (registration required).

Trend arrows in JPivot

2008-11-11T22:37:00.000-08:00

Nick Goodman has a great tip how to make trend arrows appear in JPivot cells.

To: Rest of World

2008-11-04T21:40:00.000-08:00

Normal service has resumed.

Sorry about the last eight years.

OpenMRS coverage

2008-11-03T14:48:00.000-08:00

A few months ago I wrote about OpenMRS, an excellent open-source project that is enabling hospitals and medical practices in developing countries to automate medical record-keeping, built using Pentaho technology including mondrian.

So it was good to hear a radio segment about OpenMRS on PRI's "The World" this afternoon, including an interview about how it is helping a hospital in Haiti. You can listen to it via Clark Boyd's blog.

Lost in translation

2008-11-02T01:50:00.000-07:00

Someone had a bad day at the office. The Welsh part of this road sign reads "I am not in the office at the moment. Please send any work to be translated."

Academia meets Open Source

2008-10-27T16:12:00.000-07:00

There is a natural fit between university students and researchers and the open source community. They are smart, educated, short of cash, and want to make the world a better place; and some of them, at least, have plenty of spare time.

More seriously, open source projects are a great platform for software research. By starting with a mature software platform, the researchers can spend less time recreating existing functionality, and get to the new, interesting stuff faster. The findings of such projects are more applicable to the real world because the new ideas have been tested in realistic architectures and on data sets of a reasonable size. In the area of spatial (GIS) applications alone, there are several projects, including the work of Joel da Silva and others at Pernambuco, Brazil, the GeWOlap project, and GeoMondrian.

Still, the majority of the mondrian and Pentaho communities are from industry. I would love to get more committers and active community members from academia.

Open Source Boot Camp (OSBOOTCAMP) is a mini-conference which is trying to put that right. I will be participating in a panel on open-source database development at Berkeley this Thursday 30th October, along with open-source advocates Bill Maimone (Ingres), Josh Berkus (PostgreSQL), Mark Atwood (MySQL) and John Sichi (LucidDB).

Pentaho 2.0 brings good things

2008-10-22T03:35:00.000-07:00

This week Pentaho released version 2.0 of its BI Suite, and it contains two major features that the mondrian community will love.

First, the Pentaho User Console, a web-based environment where end users can create, view, save, and share BI content. Content is arranged into folders, and includes operational reports created with Pentaho Reports, and dimensional analytics created with Pentaho Analysis (mondrian). Users can also create subscriptions to receive reports regularly by email. PUC is simple and elegant. I predict that it will quickly become the face of Pentaho for end users.

Second, the Pentaho Aggregate Designer, which automatically creates a set of aggregate tables to accelerate a mondrian schema. I believe that the Aggregate Designer takes mondrian to a new level of scalability. Let me explain why.

There are several architectures possible for multidimensional analysis engines, and they each have their strengths and weaknesses. Mondrian's architecture is best described as 'ROLAP with caching'. A ROLAP engine, short for 'relational online analytical processing', stores its data in a relational database (RDBMS) and accesses it via SQL. It follows that the RDBMS does most of the heavy-duty processing, such as JOIN and GROUP BY operations, while the ROLAP engine deals with presentation, caching, and calculations that can be expressed in a multidimensional model but cannot easily be converted into SQL.

The chief advantage of ROLAP is its simplicity. A ROLAP engine does not have its own storage engine: everything is in the RDBMS. In particular, the load process is simply a matter of loading the RDBMS, and if the contents of the RDBMS change you just need to flush mondrian's cache to see the up to date contents. Provided that the RDBMS scales, you can scale mondrian to greater numbers of concurrent users by having multiple instances of mondrian in a farm of web servers.

This great strength is also a great weakness. It means that mondrian is beholden to the RDBMS for performance. In particular, that first query of the day, the one that scans all 100 million rows in the fact table to generate a three segment pie chart on the CEO's dashboard:

SELECT
customer.region,
sum(fact.store_sales)
FROM sales AS fact
JOIN customer ON fact.cust_id = sales.cust_id
GROUP BY customer.region

Without aggregate tables, that query takes however long the RDBMS takes to scan 100 million rows -- perhaps 1 minute, or ten minutes -- but the CEO is not prepared to wait that long. Aggregate tables are the answer. They contain the pre-computed result of such queries, and are declared in mondrian's schema so that mondrian knows how to generate SQL to make use of them.

The problem is that aggregate tables are hard to use. Mondrian has supported aggregate tables for several releases, but very few people have made effective use of them. The steps are as follows.

First of all, choose an effective set of aggregate tables. The possibilities are literally exponential: in a schema with N attributes (hierarchy levels) there are 2^N possible aggregate tables. If you choose too many, you will use too much disk space and spend too long loading them every night. If you choose too few, many queries will fall through the net and end up using a full scan of the fact table. Many aggregates can be derived from other aggregates, so it is possible to economize, but there are pitfalls if you do it by hand. (I will write further about the algorithm the Aggregate Designer uses to choose a near-optimal set of aggregate tables in a future post.)

Next, create the aggregate tables in the RDBMS and add mapping elements such as to mondrian's schema. Last, write SQL statements to populate the aggregate tables as part of your ETL process.

These steps are possible by hand, but very difficult for mere mortals to get right. The Aggregate Designer automates all of these steps. Once you have chosen a mondrian schema, and a particular cube in that schema to optimize, the algorithm analyzes the data in the star schema underlying that cube, and generates a set of aggregate tables. If you have a particular set of aggregate tables in mind, you can create these before running the algorithm, and the algorithm will create additional aggregate tables, taking yours into account.

Each aggregate table is categorized according to its cost (a combination of the number of bytes on disk and the time it will take to populate) and its benefit (the effort that will be saved at run time, over a typical query load, by having the aggregate table). The Aggregate Designer displays the set of tables it has chosen as a graph: usually convex, reflecting the fact that the first tables suggested are the ones with the most favorable cost/benefit ratios.

When the algorithm has run, Aggregate Designer can add the definitions of the aggregate tables into the mondrian schema. You can either create and populate the tables immediately, or save a scripts of 'CREATE TABLE' and 'INSERT INTO {aggregate table} SELECT ...' statements. You can even generate Pentaho Data Integration (Kettle) steps to perform the ETL process.

Pentaho User Console and Pentaho Aggregate Designer are both available in the Pentaho open source BI suite version 2.0. Download the suite, or check out the live demo. They are compatible with mondrian 3.0.4.11371, which is available as part of Pentaho 2.0 or for separate download.

The only pie chart you can really trust

2008-09-26T10:45:00.000-07:00

[From logicnest via infosthetics.]

Don't worry, this isn't turning into a humor site. Normal service will resume shortly. The last couple of days, I just needed a laugh.

GOTO considered harmful

2008-09-26T01:35:00.001-07:00

Chain blog

2008-09-23T10:25:00.000-07:00

From Mr. Goodman this morning. A blogger equivalent of “send this mail to 10 people you know”.

1. Take a picture of yourself right now.
2. Don’t change your clothes, don’t fix your hair... just take a picture.
3. Post that picture with NO editing.
4. Post these instructions with your picture.

PS I'm on workation a friend's cottage in Pacific Grove for a few days, so don't have my camera to hand. I had to learn how to send a picture from my phone to my PC via Bluetooth. Cool.

Is Oracle about to embrace MPP?

2008-09-21T19:16:00.000-07:00

Oracle's Larry Ellison has some major announcements to make during Oracle's OpenWorld conference this coming week in San Francisco. A few months ago he was promising to announce a "major database innovation", but declined to give further details, so the Oracle community has been speculating furiously.

With a keynote entitled "Extreme Performance," and product announcements coming in areas of grid computing and database acceleration, all the indications are that Oracle is getting serious about problems that require massive scalability, massive throughput, and low latency.

This is an area where Oracle has been falling behind. In Oracle's approach, which independent database analyst Curt Monash calls a "shared everything" architecture, multiple servers belong to the same Oracle Real Application Cluster (RAC) and share a common pool of memory and disk storage. But this approach does not allow Oracle to be run on hundreds or thousands of servers, which is how companies such as Google are solving problems which require large amounts of storage and processing. That sort of massively parallel processing (MPP) requires a "shared nothing" architecture, and internet companies have been rolling their own architectures out of simpler components.

The result is that Oracle "is way behind in the 'scale-out' world," said Paul Vallee, CEO of The Pythian Group, an Ottawa, Ontario-based database services provider. "MySQL is eating its lunch in terms of Internet-scaled deployments."

Oracle's own experts seem to agree. In the abstract for a talk "Oracle's New Database Accelerator: A Technical Overview", Ron Weiss writes:

"New and revolutionary solutions and methodologies are coming together to handle the exploding data volumes real-world systems are being required to store and serve up. Supporting ever-larger databases, with ever-increasing demands for getting "answers" faster, requires a new way to approach the problem."

Weiss's solution uses improvements to storage management, but I doubt that it would satisfy Google's requirements, or even the price/performance requirements of a medium-sized internet media company.

Meanwhile, those who have adopted shared-nothing architectures are feeling the pain too. Having stitched together hundreds or thousands of databases, the problem is how to populate and coordinate them. For example, internet companies' transaction rates are so high that it is not possible to load the day's data during an eight hour nightly load window, and besides, business owners want to see data in near real time. Organizations are adapting a 'trickle ETL' process to populate the data warehouse continuously and with low latency.

So data architects seem to be caught between a rock and a hard place. Either stick with Oracle's shared-everything (or indeed IBM DB2 or Microsoft SQL Server - they have the same approach) and live with the scalability limitations, or move to the wild frontier of shared-nothing, and be prepared to spend a lot of effort managing, populating and coordinating your farm of databases.

Ironically, the answer, as Larry Ellison and his cohorts taught us thirty years ago, is in the relational model. By extending the relational model beyond stored data to include streaming data, SQL can be used to efficiently manage data flowing into and between multiple databases, as well as storage and retrieval within those databases. This creates a scalable shared-nothing system, with databases decoupled from each other, but because the data flow is managed by declarative SQL, it is as manageable as a shared-everything system such as Oracle.

SQLstream is an implementation of this new SQL, and can be applied to continuous ETL, real-time BI and monitoring problems. For example, if there are many data sources for your ETL process, and many servers to be populated, SQLstream can act as a cross-hatch, load-balancing the data, aggregating, and routing each row to the correct database engine with low latency. And because SQLstream's SQL encompasses both data at rest and data in flight, it can correlate data in the warehouse with arriving data.

SQLstream is partnering with companies that are building next-generation data warehousing architectures on Oracle and on other databases. Aeturnum is an exciting new delivery partner for SQLstream with extensive expertise in data warehousing (Netezza) and business intelligence (Pentaho).

Come and see SQLstream at Oracle OpenWorld. We will be at the Aeturnum stand (2716 Moscone South) all this week.

Fall migration kicks in...

2008-09-15T23:42:00.000-07:00

You can't fault nature.

We've been counting raptors for almost a month now, and the numbers have been really, really low. I've been promising everyone that the peak of raptor migration, will be within a day or two of the equinox, because that's how it always goes, but I was getting worried.

We weren't seeing enough birds. In particular, the accipiters (the Cooper's Hawk and Sharp-shinned Hawk), which form the bulk of the equinox peak, were nowhere to be seen. The counts consisted mainly of the Turkey Vultures and Red-tailed Hawks which are ubiquitous around Hawk Hill. Had something gone wrong? Had this summer's forest fires disrupted the breeding season and delayed the migration?

Look at the accipiter numbers for last week:

Sun 8: 2 sharpies, no coops
Mon 9: no birds (fog)
Tue 10: no sharpies, 1 coop
Wed 12: 1 sharpie, 1 coop
Thu 11: 8 sharpies, 3 coops
Fri 12: 5 sharpies, 3 coops

Then came my day on the hill, Saturday. It was a slow start, foggy at first, and overcast for most of the day, but the birds started coming. We had 40 sharpies, 8 coops. We also got a juvenile Golden Eagle (at 10.30am even -- conventional wisdom has it that eagles are late risers, like the thermals they soar upon), and a couple of Broad-winged Hawks. (According to the books, you won't see a broadie west of Kansas, but the Marin Headlands are very effective at channeling the few we do have into a narrow stream.)

Sat 13: 40 sharpies, 8 coops

And in the last couple of days, the trend has accelerated:

Sun 14: 111 sharpies, 29 coops
Mon 15: 133 sharpies, 44 coops

Yes folks, it looks like we have a fall migration after all. As sure as clockwork, the changing day-length is telling those birds to head south. Check the counts over the next week or two, you should see the spectacle continue.

(Note that all statistics quoted are copyright of the GGRO and may not be reproduced without permission.)

SQL extension to allow FIRST_VALUE and LAST_VALUE in GROUP BY query

2008-09-03T17:27:00.000-07:00

At SQLstream, we have come across an interesting query pattern that seems to be difficult to express in standard SQL (SQL:2003 or SQL:2008). It turns out to be applicable to regular SQL as well as streaming SQL, and therefore it would make sense as an extension to the SQL standard.

First some background, for those of you who don't fall asleep every night reading the SQL standard. There are two kinds of aggregation in standard SQL: windowed aggregation, of the form

function(arg {, arg}) OVER window-specification

and grouped aggregation, which is of the form

function(arg {, arg})

and requires a GROUP BY clause. (If the GROUP BY clause is not present, 'GROUP BY ()' is assumed.)

According to the standard, these two forms should never meet. It is illegal to use a windowed aggregation in a SELECT that has a GROUP BY, or to mix grouped aggregation and windowed aggregation in the same SELECT. (It's OK to use one in a sub-query and another in an enclosing query.)

However, here is a very reasonable query that is difficult to express in standard SQL: Given a record of every trade on a stock exchange, give me the volume and closing price of each ticker symbol. You might try

SELECT
    day,
    ticker,
    SUM(shares) AS volume,
    LAST_VALUE(price) AS closingPrice
FROM Trades
GROUP BY day, ticker

but this is illegal SQL. Why is it illegal? Because the LAST_VALUE function (like FIRST_VALUE and RANK) is a windowed aggregate function and is only meaningful on an ordered set.

To introduce the notion of ordering, I propose that the following query should be valid:

SELECT
    day,
    ticker,
    SUM(shares) AS volume,
    LAST_VALUE(price) OVER (ORDER BY timeOfDay) AS closingPrice
FROM Trades
GROUP BY day, ticker

With the OVER clause, LAST_VALUE is now a windowed aggregate function within the context of a GROUP BY query, which was previously illegal. Every windowed aggregate is applied to a window, so what is the window in this case? We want the window to contain all of the rows with the same day and ticker value, and to be sorted by timeOfDay. In other words, the window inherits the GROUP BY columns as its implicit PARTITION BY clause. It is as if they had written

LAST_VALUE(price) OVER (PARTITION BY day, ticker ORDER BY timeOfDay)

Now, if you know that I work for SQLstream, you will guess that I am motivated to make this work for streaming queries. A streaming aggregation query over the Trades stream would look like this:

SELECT STREAM
    day,
    ticker,
    SUM(shares) AS volume,
    LAST_VALUE(price) OVER (ORDER BY timeOfDay) AS closingPrice
FROM Trades
GROUP BY day, ticker

This is identical to the traditional, non-streaming SQL above, except for the STREAM keyword that tells SQLstream that the result of the query should be a stream.

In idiomatic SQLstream SQL, we would typically express the query as follows:

SELECT STREAM
    FLOOR(t.ROWTIME TO DAY),
    ticker,
    SUM(shares) AS volume,
    LAST_VALUE(price) OVER () AS closingPrice
FROM Trades AS t
GROUP BY FLOOR(t.ROWTIME TO DAY), ticker

This form uses SQLstream's system ROWTIME column and the 'FLOOR(datetime expression TO time unit)' operator, and so can dispense with the day and timeOfDay columns. Also, streams are ordered by ROWTIME by default, so we can abbreviate 'OVER (ORDER BY ROWTIME)' to 'OVER ()'. This form is more terse, and more typical of how the query would be written in a SQLstream application, but the previous form works also.

The end result is powerful and, I think, consistent with the spirit of standard SQL.

Database virtualization, distributed caching and streaming SQL

2008-08-27T12:14:00.000-07:00

James Kobelius writes in Network World how the need for scalable real-time business intelligence will create a convergence of technologies centered on database virtualization:

"Real-time is the most exciting new frontier in business intelligence, and virtualization will facilitate low-latency analytics more powerfully than traditional approaches. Database virtualization will enable real-time business intelligence through a policy-driven, latency-agile, distributed-caching memory grid that permeates an infrastructure at all levels.

As this new approach takes hold, it will provide a convergence architecture for diverse approaches to real-time business intelligence, such as trickle-feed extract transform load (ETL), changed-data capture (CDC), event-stream processing and data federation. Traditionally deployed as stovepipe infrastructures, these approaches will become alternative integration patterns in a virtualized information fabric for real-time business intelligence."

Kobelius makes it clear that this "virtualized information fabric" is an ambitious program that will be accomplished only over a number of years, but the underlying trends are visible now: for example, the convergence of distributed caches with databases, as evidenced by Oracle's acquisition of Tangosol, and Microsoft's recently announced Project Velocity.

This envisioned system contains so many moving parts that a new paradigm will be needed to link them together. I don't think that databases are the answer. They elegantly handle stored data, but founder when dealing with change, caching, and the kind of replication problems you encounter when implementing virtualized and distributed systems. For example, database triggers are the standard way of managing change in a database, and are still clunky fifteen years after they were introduced; and Enterprise Information Integration (EII) systems were an attempt to extend the database model to handle federated data, but only work well for a proscribed set of distribution patterns.

I wrote recently about how SQLstream can implement trickle-feed ETL and use the knowledge it gleans from the passing data to proactively manage the mondrian OLAP engine's cache. SQLstream also has adapters to implement change-data capture (CDC) and to manage data federation.

In SQLstream, the lingua franca for all of these integration patterns is SQL, whereas ironically, if you tried to achieve these things in Oracle or Microsoft SQL Server, you would end up writing procedural code: PL/SQL or Transact SQL. Therefore streaming SQL - a variant of what Kobelius calls event-stream processing where, crucially, the language for event-processing language is SQL - seems the best candidate for that unifying paradigm.

Hawkwatch

2008-08-27T10:33:00.000-07:00

It's that time of year when the days are getting imperceptibly shorter, birds start thinking of heading south, and a couple of hundred volunteer birders of the Golden Gate Raptor Observatory (GGRO) head to Hawk Hill to watch them.

Hawk Hill is at the southern tip of the Marin Headlands overlooking the Golden Gate Bridge, which naturally funnels migrating raptors from a fifty mile stretch into less than a mile. The result is a huge concentration of raptors. During peak season — which, not coincidentally, is usually within a day or two of the autumn solstice — you will typically see over 100 birds an hour from 12 or 13 species of raptors, including eagles, falcons, accipiters and some of the rarer buteo hawks.

It has to be seen to be believed, so if you're curious, come up to Hawk Hill and see for yourself. When you get there, on any day between late August and early December, as long as Hawk Hill isn't shrouded in fog, you'll find about a dozen volunteers with binoculars counting hawks.

The GGRO has been counting and banding raptors at this site for over twenty years. I have been volunteering for 6 years, and this year I have stepped up to be day leader of the Saturday II hawkwatch team. With the depth of hawk-watching experience on the team, it's not too onerous a job. The main responsibility is to ensure that the numbers are recorded systematically — this is a scientific study, after all — and, when the fog rolls in, to tell the team that it's time to hang up the binoculars for the day.

To help me keep up with the action, I built an RSS feed that contains a summary of each day's hawk watching, including a count of each species of raptor. The first week was a wash — fog every day — but we can expect to see numbers, particularly of the accipiters (Cooper's hawk and Sharp-shinned hawk), climbing rapidly over the next two or three weeks. Subscribe to that feed and you can get daily updates too; or even better, join us on the hill!

Mondrian on TimesTen

2008-08-19T09:44:00.000-07:00

Funny what you find while googling for error messages: apparently mondrian runs on TimesTen.

One of the bizarre things about open source is that you have no way of knowing who is using your project, and on what platform. (Until they find something wrong, that is.)

Really urgent analytics

2008-08-18T09:48:00.000-07:00

A Forrester report entitled "Really Urgent Analytics: The Sweet Spot for Real-Time Data Warehousing" makes the connection between event-stream processing (ESP) and data warehousing, and Intelligent Enterprise published a nice summary.

A traditional data warehouse contained huge amounts of data but was loaded infrequently: say monthly, or nightly at best. Modern businesses demand actions at lower latencies, and data warehousing professionals have been able tune the traditional data warehouse load process to reduce latency.

But even when cranked up to the maximum, the load process cannot achieve latencies of less than a few seconds, whereas many business processes need their answers faster than that. And this performance comes at the cost of higher complexity, so it takes more time and effort to modify the load process to incorporate new data or ask new questions.

To solve the squeeze between lower latency and increasing complexity -- and, I would mention, ever-increasing data volumes and a trend towards distributed systems -- data warehousing needs a new architectural component, and Forrester rightly point to Event Stream Processing to fill that gap. I would add that, given the skill set of data warehousing professionals, it makes a lot of sense for that Event Stream Processing to be in SQL.

At SQLstream, we saw this need four years ago, and are dedicated to solving the latency-complexity problem using SQL.

The process of database research

2008-07-14T11:28:00.000-07:00

Jennifer Widom just received the ACM SIGMOD Edgar F. Codd Innovations Award, and spoke about the process of database research:

"[I]t's imperative to think about all three of the critical components -- data model, query language, and system -- and in that order! We in research have a rare luxury, compared to those in industry, that we can mull over a data model for a long time before we move on to think about how we'll query it, and we can nail down a solid syntax and semantics for a query language before we implement it."

I've designed languages before, and I know how hard it is to do it right, so when I was designing SQLstream's extensions to SQL I looked at the research, and Jennifer's team's work was easily the best in the field.

Some of my colleagues balked at the paper's formal approach, but it was just what we needed to build a language for combining streaming and stored relational data, and the optimizer rules and execution objects to implement it.

She is correct that it is a rare luxury for industry to have a sound foundation to build next-generation technology on. Congratulations on the award, Jennifer, and thanks for helping to build that foundation.

Mondrian, Flex and Openbravo

2008-07-07T11:56:00.000-07:00

This guy is using Adobe Flex as an OLAP client against Openbravo ERP data. He now plans to "connect Flex to [the Mondrian] OLAP server and let the OLAP sever do all the hard work".

Sounds like a great idea: keep the big data on the server side, send just the multidimensional, aggregated results over SOAP (XML for Analysis), and let Flex do what it does best: fast, rich client.

More from my mother's funeral service

2008-06-27T15:21:00.000-07:00

Barbara Webb, the extraordinary reader from Wombourne church who worked with me to develop the service in accordance with Mum's wishes, has sent me the words that she spoke on the day. Barbara knew my mother well, and her words are a fine tribute.

I am not a Christian, but I have a lot of time for the spirit of philosophical inquiry that Barbara brings to her religion, and indeed her outlook on life. Barbara helped us give our mother a wonderful send-off; thank you, Barbara.

Introduction

This service has been built around Judy’s well-thought out wishes, to such an extent that I feel that she has invited us to celebrate her life by sharing much of what she found good in it. Even during the last weeks in Compton Hospice Judy was very much in charge.

The manner of her illness has meant that she was able to talk with and share many thoughts with Bethan, Julian and Justin, drawing the family closer together which was, I am sure, her dearest wish. Those bonds have been strengthened and will not break, even though the Atlantic separates her sons from her daughter much of the time.

As most of you here know, music played an enormous part in Judy’s life, especially over the last years. That is reflected in the service sheet which contains the full details she requested. Music, I know, touched Judy at the deepest level, and in her choice of music for this service she has chosen two pieces and asked us to sit quietly as they are played. So let us do just that, not only remembering Judy but allowing the music speak to deeply to our hearts, freeing emotions that words simply don’t touch.

Address

Judy had a lot of time to decide the details of her funeral. She asked me over a year ago to co-operate with our Rector Paul. And she knew that the tone of the service would be 100% Christian because it was only a couple of months ago that, being Judy who tackled things head on she asked to be sidesperson at a local funeral in this church … which it so happened I took. Yes, she knew this would be a 100% Christian funeral.

Judy was a thinking person, who didn’t take what she was taught for granted, and there were aspects of the Christian faith which she found difficult. She questioned, and I believe that God will welcome her with open arms because of that. Some of us come to him as unquestioning simple believers, others seek more concrete answers, but fortunately God loves us all. just as we are.

For her own reasons Judy struggled with the concept of resurrection, which is the bedrock of the Christian faith. In fact most faiths allow for some form of existence before, outside or beyond the span and confines of a human life, so there must be a widespread, deep–seated, instinctive belief in humankind that death is not the end but the start of something new. Our problem is, of course, that only one Man came back from the dead to prove it and tell us about it, Jesus Christ himself. Apostle Paul tackled this head on in his letter to the Corinthians. “If the dead are not raised, it follows that Christ was not raised; and if Christ was not raised, your faith has nothing to it. If it is for this life only that Christ has given us hope, we Christians of all people are most to be pitied.”

But Paul didn’t stop there. He must have been a tremendous orator and I can hear him thundering “But the truth is, Christ was raised from the dead”. Paul believed totally in the resurrection, and so do I. The Christian hope of life after death is the heart of our service today, and from what I have heard from her family, I believe that, as Judy drew nearer to death, her life story made sense to her, and she was finding the peace which God alone can bring.

God doesn’t ask us to understand; he asks us to trust in him, and live our lives in hope, inspired by his love, the love he expressed in Jesus Christ, the love he lavishes on us every day. Our job is simple; we are to accept that love and use it to love our families and all those around us, as agents for God, spreading his peace in the world.

Judy, as we say after most of our services, may you rest in peace and rise in glory. Amen

Eulogy for Judy Hyde

2008-06-26T13:19:00.000-07:00

It was my mother's funeral today, and I gave following eulogy. As you will see from the order of service, the piece of music I was referring to, which immediately followed my address, was the Officium defunctorum (Requiem) by Tomás Luis de Victoria.

Thank you all for coming.

Many, many people have helped out in the months that Judy has been ill. I’d like to thank a few people in particular.

Gill and Robert Green, my brother Justin’s in-laws, for putting up with a full house for the past few months;
Pamela’s mother Barbara, who has been sending a card containing a few words and a pasted Snoopy cartoon, three times a week for two years;
Elaine, who was like a sister to my mother;
Adrian and Judy, quietly and selflessly helping out;
All of the staff at Compton Hospice.

Lots of people here! In addition to her family, there are:

Friends from her young married life with Adrian, in particular from Dudley Kingswinford Rugby club;
Teachers from Westfield and Bobbington;
People she knows from courses at U3A (University of the Third Age) Latin, Italian, history;
People she knows from music courses at Bromsgrove and Wolverhampton University;
Members of Wombourne & District Choral Society;
Friends from her passion for Early Music, there are recorder players, harpsichord players, and for all I know, players of crumhorns, racketts, and hurdy gurdies;
And of course members of the church and the community in and around Wombourne, that Judy was an enthusiastic part of.

So many people! I had always thought of Judy as a quiet woman, and I think she did too. But that reserve was balanced by a drive to do what she loved, to get out there and play music, or do what she needed to do.

I think everyone who has been with her over her struggle with cancer over the past months knows about that determination. She just kept on going, kept on doing the things she loved.

I’ll give a couple more examples.

When I was 5, Judy and Adrian got divorced. It must have been very hard for her, bringing up two young children, but of course we were too young to notice. We moved into a new house, and of course she was working full time to pay the mortgage. She wanted her children to have swimming lessons and piano lessons, play rugby, and join the scouts, so she needed to learn to drive. She created a mantra ‘learn to drive in 75’. She had driving lessons from a friend with her two children sitting on the back seat. She was such a nervous driver, and she devised a way to calm her nerves before her driving test: have a swift gin and tonic, and cover the smell by eating mint imperials.

And by the way, she still keeps mint imperials in her car. Justin found a Tupperware container in the driver’s door the other day. Even after all her struggles to eat high calorie food and gain weight, they still had a hand-written label on the lid: “Mint imperials, bought 26th September 2007, 11 kilocalories each”.

Another example of that determination. In the mid 90s, Mum announced that she was going to Uganda. The reason for that visit was my sister Bethan. We never knew that we had a sister. In those days, if you were a young unmarried mother, you weren’t given much choice. Judy’s mother’s first concern was to save the family from embarrassment. I think Mum was shaken up by this. She didn’t think she would ever see Bethan again. When Bethan contacted Mum, she and her family were doing development work in Uganda. I had been to Uganda as a student, and I knew that it was a difficult place to travel in, let alone for a sixty year old. OK, said Mum, and jumped on a plane to meet her daughter.

In the last few years of her life, Judy had a flowering. She had always clashed wills with her mother, and she said that when her mother died, she ‘came out from under a cloud’. These last ten or fifteen years were the best times of her life. After she retired, she took a music ‘A’ level, then a music degree, and got involved in all kinds of activities, including early music, but also other studies with U3A. Her sons, Justin and I, completed our studies with good degrees, and embarked on careers in computer software that she never totally understood, but was nevertheless very proud of. We both moved to California, but the family remained in close touch. Bethan and her family became part of Judy’s family, and she saw me and my brother settle down with women she liked. Just after my wedding to Pamela in California two years ago, Justin and Caroline had Zachary, and that gave her great pleasure.

I’m proud of everything that she achieved in those years. It was nice to be on the receiving end of all that love. I love you too, Mum.

The final healing happened this April. Judy took my father Adrian and his partner Judy to a weekend course to Benslow in South Wales. She lived for these courses, and knew that this was going to be her last. And she was pleased to be introducing her music, which she loved so much, to new people.

I’m going to leave you with a piece of music. Music was of course a passion of Judy’s life. She felt kinship with people who shared her passion for music. When she found a perfect piece of music, she could of course tell you intellectually why it was perfect. This particular piece, the first movement of a requiem written over 400 years ago for a Spanish princess, was the apex of polyphonic Renaissance choral music. But it’s quite simply a beautiful piece of music. That’s what she would like to leave us with. Please, sit back, reflect, and enjoy it.

Mainz community meet up is just one week away

2008-06-05T11:07:00.001-07:00

Just one week til the Pentaho community meet-up in Mainz, Germany, that I blogged about earlier.

If you're coming, please register. (Just so we know how many beer glasses to have on hand!)

Plenty of other information about the event on the Pentaho wiki.

Maven and Ivy

2008-05-11T13:59:00.000-07:00

We experimented a few years ago using Apache Maven to manage the Java libraries that mondrian depends on. Maven looked great on paper, but it was tricky to set up, and since simplicity was the goal in the first place, we gave up.

Since then Maven2 has been released. Version 2 has a new architecture and is by all accounts a great improvement. I have recently been using Maven for a project with a lot of dependencies on other projects and it helps a lot. It imposes a structure on the dependencies by forcing you to name and version your projects in a certain way, and provides a central repository to put them in. You can provide local repositories for projects only you or your project team are using.

Still, Maven seems to be a one-trick pony, even though it's a great trick. Maven generates distributions, javadoc, project pages, code coverage, and so forth, but for mondrian the only job I want it to do is dependency management. I don't want to throw away the considerable investment we've made in mondrian's ant scripts. I'd rather have something that adds dependency management to my existing framework.

I've just come across Ivy, and it seems to be just the ticket. Ivy aims to do dependency-management within an ant framework, and it uses Maven's repository and metadata protocols to manage its libraries. It's just been accepted by Apache as a sub-project of Ant, so we know that its integration with ant will continue to get better.

I went through Ivy's tutorial and was impressed that Ivy could bootstrap itself using just ant 1.6, JDK 1.4 (or higher) and a build.xml file. (Try it! Just download that file and type 'ant'.)

So, I'll be looking to add Ivy support to mondrian in the next week or so. The big benefit will be smaller distributions. If you download a source distribution, it will no longer contain libraries such as olap4j.jar, javacup.jar, commons-pool.jar, and so forth. The build process will download these libraries the first time you build. It takes quite a lot of effort, each release, to make sure that a source distribution contains all dependencies, so we hope to same some time there. We'll be able to delete these libraries from our source control system -- always a strange place for libraries, I thought.

And, for those of you who use mondrian with different libraries than we ship with (say you use a different version of log4j or apache commons than we do) you should be able to easily modify your dependencies and recompile the source distribution.

Mondrian in Mainz

2008-04-15T09:27:00.000-07:00

Pentaho is hosting its first community meet-up in Mainz, Germany, on 13th and 14th June, 2008.

I will be there, and so will the leaders of the other Pentaho projects: Thomas Morgner (Pentaho Reporting), Matt Casters (Kettle), Mark Hall (Weka).

The format of the meeting will be along the lines of a BarCamp: no PowerPoint, lots of demos, audience participation, beer/wine on hand, and fun afterwards in the form of a cruise on the river Rhine. (Mainz is in the heart of Germany's wine country, so it would be difficult not to have fun!)

Are you going to join us? What would you like to see at the meet-up? What application/technology could you demo?

And by the way, if you can't wait until June, I will be giving a talk entitled "Creating Interactive OLAP Applications with MySQL Enterprise and Mondrian" at the MySQL conference tomorrow in Santa Clara, California. Join me there... or find me on the conference floor/in the bar afterwards.

Streaming SQL meets OLAP

2008-02-27T06:21:00.000-08:00

Streaming SQL and OLAP are two of the most interesting and powerful paradigms in data processing. OLAP is a well-established technique for analyzing large databases of historic data. Streaming SQL is a more recent innovation, that applies the declarative power of the SQL language to the problem of managing data in motion.

So, what happens when you combine OLAP with Streaming SQL? The combination is capable of solving some business problems that can't be solved any other way. OLAP is usually hampered by conventional ETL techniques: it is difficult to keep the data warehouse up to date, because batch-based ETL processes are only efficient when dealing with a few hours or days of data. OLAP engines excel at comparisons between time periods (say, this quarter compared to the same quarter last year) or comparable data sets (say, this brand versus that brand); when powered by a streaming SQL engine, an OLAP engine can also include the most current data in its analysis (say, this hour compared to the average for this hour of the day over the last 6 months).

The highest value data in the enterprise is that which represents what is happening to the business right now. This data includes various kinds of remote procedure calls, state changes of critical systems, and all kinds of business events. This data isn't stored on disk - we call it data in flight as opposed to conventional data at rest - and conventional ETL has difficulty accessing it. Streaming SQL allows you to bring this data into the same format as other enterprise data, but retain the ability to analyze and act on it in real time.

I'm going to look at how you could combine the mondrian OLAP engine with the SQLstream streaming SQL engine.

Mondrian requires its data to be stored in a relational database. To ensure high performance on a large data set, mondrian caches query results in memory, and also uses aggregate tables which have been populated with summaries of the data. Mondrian's cache and aggregate tables both require careful management if mondrian is to give the correct answers on a rapidly changing data set.

SQLstream helps mondrian do this by providing a continuous, real-time ETL process. As we shall see, the steps are: acquire the real-time data and expose it as a common relational format; transform into an organization suitable for OLAP and data warehousing; load into the data warehouse, including aggregate tables; and notify mondrian of changes to its cache.

First of all, SQLstream can help to acquire the data. As we said earlier, traditional ETL processes are limited to reading data at rest: from databases, mainframes, and files extracted from other operational systems. Data in flight exists in other formats: messages on message-oriented middleware, web service calls, TCP network packets, and so forth. SQLstream can subscribe to these sources of data, and tap into the traditional data warehouse sources too: it can monitor a database table and generate events as new transactions occur, and tail a log file to read rows as they are appended to the log file.

One of SQLstream's core concepts is a stream. A stream is analogous to a table in a relational database; but whereas a table contains a finite set of rows which have been inserted at some time in the past and stored on disk, a stream contains an infinite sequence of rows that arrive whenever the producer decides to send them. (SQLstream in fact supports tables too, so that you can combine historical or reference data with event data.)

What streams and tables have in common is the fact that you can manipulate them using SQL queries. Not just the simple operations like filtering and routing, but operations which combine multiple rows such as join and aggregation. You can combine rows with other rows in the same stream (often demarcated by a time window of interest), with rows from other streams, and with historical and reference data.

Next, you need to prepare the data and convert it into a form suitable for large-scale analysis. In SQLstream, you can use SQL to perform a real-time, continuous ETL process. For example:

You can apply standard SQL operators to cleanse and convert the data fields
You can calculate trends such as moving averages using SQLstream's windowed aggregation operations.
If your data warehouse schema contains slowly-changing dimensions, SQLstream can help the loading process by identifying transactions which represent a new member of a dimension. For example, when an order is received from an existing customer, SQLstream can find that customer's id, whereas if the customer is new, it can generate a new surrogate key value.
If your data warehouse schema contains aggregate tables, they need to be populated with records which represent multiple fact table records. It is often cheaper to compute these aggregate records in memory.

On the subject of aggregate tables, note that if you have many aggregate tables and data rates are extremely high, eventually the I/O capacity of the DBMS makes it impossible to keep the aggregate tables 100% up to date. You should reduce the number or granularity of the aggregate tables, and partition each aggregate table by time to ensure that only one block per is being actively written to and therefore the active block of aggregate tables can fit into the DBMS's buffer cache.

Loading the data warehouse is straightforward. SQLstream has a database adapter that makes DBMS tables appear as foreign streams; writing to these streams makes an insert, update or delete occur in the data warehouse.

As data is loaded into the data warehouse, it becomes inconsistent with the state of mondrian's cache. Mondrian's cache is necessary for performance if mondrian has many concurrent users or if the data warehouse is so large that SQL queries take a long time, but flushing the entire cache every time there is an update negates the value of the cache.

Fortunately mondrian has an API to let you notify mondrian of changes that affect its cache contents. You can tell mondrian specifically which data changed; for example, you can say 'there was just a sale of beer in Texas', and mondrian will mark precisely these entries in the cache as invalid, so they will be re-read from the database next time an OLAP query requests them.

Once again, the problem can easily be solved using a foreign stream. The foreign stream should call mondrian's cache control API for each row it receives; a SQLstream pump object ensures that every record written into the fact table is mirrored into the foreign stream and therefore mondrian's cache is kept in sync with the DBMS.

In conclusion, there is a synergy between OLAP and streaming SQL techniques that allows new business problems to be solved and existing problems to be solved much more efficiently. SQLstream provides a platform for all manner of continuous ETL operations, and mondrian with its open-source license and extensible Java architecture is a natural fit.

30% less bad

2008-02-13T00:33:00.000-08:00

Arrowhead, distributors of mountain spring water, are running a high-profile TV campaign about how they are using 30% less plastic:

"Less plastic. Less impact. A little natural does a lot of good^TM".

They should be careful; they might give their consumers ideas about how bad those plastic bottles are. Who knows, some of those consumers might come up with a solution that uses 100% less plastic.

Democracy, caucus-style

2008-02-10T11:03:00.000-08:00

I've heard a lot about caucuses since I moved to the United States fifteen years ago, and particularly over the past few months. Caucuses are like a living fossil, recalling the days when the Founding Fathers were experimenting with this dangerous new idea called Democracy.

Since I'm not a US citizen, and I live in a state (California) which does not have a caucus system, I'm not likely to experience a caucus any time soon. So I was pleased to hear about one first-hand from my friend Eric in Colorado. In Eric's words:

First time I've ever caucused. Heck, first time I've ever registered with a party. First caucus for most everyone else, too.

7 precincts in one elementary school cafeteria. Good thing the fire marshal didn't show. Typical turnout is 15 to 20 people combined in all 7 precincts, but last night the turnout for my precinct alone was 49, plus a handful of observers, and the total turnout for all 7 precincts was 355 voters.

As the only one who had actually read the rules, I wound up chairing. That'll teach me to read the documentation.

What was the experience like?

Well, you could simulate the experience pretty easily.

Just get 40 software developers together plus 10 random people off the street and pack them in a room meant for 20. Make sure any available lavatory facilities are sized for eight-year-olds.

To simulate the presidential poll, ask them to discuss the merits of vi versus emacs. To ensure a proper level of passion, make sure they are all believe that Microsoft will dump Word and replace it with whichever editor they pick. When they're finally ready to vote, do it by having them gather into separate groups and walk by you so that you can count them off like sheep. Once that's done, search through seventy pages of random government forms until you find the right reporting form and fill it out.

To simulate the senate poll, ask them to choose what they would like for dessert: apple pie ala mode, or an incredibly obscure dish from Mozambique which nobody has ever heard of or tasted (and which you can't even find the name of in your packet). Counting these results should be easy, but make sure to account for people who have already left. Search the random forms again for the proper form.

For the party platform, read aloud 3 random paragraphs each from the EU constitution, War and Peace, the Federalist Papers, Marvel Superhero Comics #37, and the Unabomber manifesto. Have them vote yea or nay on approving each for discussion at the state convention.

Finally, pass a donation envelope to help do this all again next election, and adjourn the meeting (but be warned, you'll need to stick around to sort out and sign 20 more forms before you get to leave).

I still wish I'd been there. As Winston Churchill said, Democracy is the second worst form of government; the only worse form is all of the others.

A nice little OLAP viewer

2008-02-06T15:01:00.000-08:00

Bill Seyler and Will Gorman from Pentaho have put together a nice little OLAP viewer in their spare time, called Halogen. It isn't fully baked (by a long stretch) but it shows what you can do if you pair up GWT with olap4j.

I think it shows off the strengths of both GWT and olap4j nicely. Both technologies have a strong portability message. Because of GWT, Halogen has really nice AJAX usability and can run in any browser. Because of olap4j, it can run against mondrian and an XMLA provider without changing a line of code. (I haven't tried it against against Microsoft SQL Server Analysis Services, for instance, but it shouldn't be hard to get it working.)

This isn't an official Pentaho product, more of a proof of concept with the potential to grow into an alternative to JPivot if the community thinks it is cool and we get some momentum behind it. To make it easier for people to contribute, we made a point of releasing it under the commercial-friendly license, namely the Mozilla Public License.

Check it out.

Vista Service Pack 1 Release Candidate

2007-12-19T22:18:00.000-08:00

I'm one of those folks who runs Windows on my laptop out of habit, but I'm not passionate about it. I run Ubuntu on my server, and am happy with that, and have considered moving to Mac OS just so I can have one of those cool, shiny Apple Powerbook notebooks.

I hadn't been thinking upgrading from XP to Vista, but I bought a new Dell laptop earlier this year, and it came with Vista, so I said 'Why not?'. Six months later, I am among the legions of people who are unimpressed with Vista. It's not that it's worse than XP, but Microsoft have changed a lot of things, and virtually none of them are for the better.

For example, XP used to give you the choice whether to sleep, turn off, or restart; and if you pressed the shift key, the sleep option would change to hibernate. In Vista, the corresponding menu has no hibernate option. This is a nuisance, because I dual boot my laptop, and I use hibernate a lot.

And so on. The whole thing felt more sluggish than it should, given that I'd moved to a laptop 3x more powerful; and there were other niggling things, nothing broken exactly.

I thought I'd give them chance to redeem themselves with Vista Service Pack 1 (SP1). I resisted the temptation to try SP1 Beta in September, but when they released the Release Candidate.

I just installed the Release Candidate (RC) of Vista SP1. The upgrade took about an hour, as they warned, but otherwise went smoothly. Vista and most apps work fine, but in two hours I have had problems with the usually impeccable PerfectDisk (offline defrag doesn't work) and Google Desktop (has crashed twice so far).

Given that post-SP1 Vista isn't noticeably different (they may have shuffled the fields of the wireless connection dialog around, I'm not sure) and a couple of apps have problems, I'd recommend that you don't install SP1 until it's officially released.

I can't blame Microsoft when two apps that they don't control have problems in a release which isn't production. But over the past ten years, we have grown accustomed to the new release always being better than the last. I'll have to revisit that assumption. And for future Microsoft products, I'm downgrading my rating from 'sure, let's give it a try' to 'skeptical'.

olap4j support for scrolling result sets

2007-11-18T18:13:00.000-08:00

olap4j version 0.9 (beta) is almost ready for release. The specification has been expanded and clarified considerably (latest spec). There is now a beta-quality mondrian driver for olap4j, and a comprehensive set of tests in the TCK (technology compatibility kit). It will be released before the end of the month.

But before we release 0.9, I need to solve a dilemma regarding how olap4j should handle scrolling result sets.

We have already established that olap4j will allow clients to access the positions on an axis both via a random-access list and via a bi-directional iterator. Hence CellSetAxis implements Iterable<Position> and has methods

List<Position> getPositions()
ListIterator<Position> iterator()

With these two methods, I have no doubt that client applications will find it easy to navigate a CellSet with concise, clear code. The issue is more whether the writer of an olap4j driver can write an efficient implementation. To do that, the client needs a clear way to signal their intended access pattern to the driver, or conversely, the driver needs to infer the client's access pattern from the methods it calls.

Now, one might say, let's use a smart list which does paging and generally behaves like an iterator behind the scenes. The problem is that the driver can't infer the client's intended access pattern. The smart list would not know whether to page out previous positions (and have to ask the server for them again, at considerable cost to the server and communication cost) or to try to keep them in memory. So, I reject the smart list approach. Let's stick with the dual list and iterator, and see what the driver could infer from the client.

Consider these cases:

1. Small number of columns and rows. No memory or performance issues here. Client may prefer the convenience of the list, but the iterator will work fine too.

2. Small number of columns, large number of rows. Client will probably want to use a list for columns, so that they can scan the list repeatedly, but use an iterator for rows, to signal that earlier rows can be released from memory.

3. Large number of columns and rows. Client will probably want to use an iterator for both columns and rows. Certainly the number of cells will be so large that they will have to be paged, probably in blocks. The driver can infer the access pattern from the iterators and be reasonably intelligent how to manage cell values.

4. Unknown number of columns and rows. Not as common as you would think, because if the client is going to receive many thousands of rows, the application writer generally knows that this is characteristic of their application (say managing mailing lists) and chooses an appropriate client. I suppose the client could test the water using an iterator to find the size of the axis before switching to a list access method; or the driver writer could implement a smart list which reads large blocks of positions (say 1,000) at a time but for small-to-moderate sized cell sets behaves essentially the same as a dumb list.

Problem #1: Cell ordinals

In case #3, it is not possible to access cells via an integer cell ordinal computed using the formula cellOrdinal == columnOrdinal + columnCount * rowOrdinal, because columnCount is not known until the entire columns axis has been evaluated.

I propose that you can open a cell set in two modes, random-access-mode and cursor-mode. Cursor-mode is for very large result sets. In cursor-mode, the driver would not attempt to find the length of the columns axis, and therefore you could not call Cell.getOrdinal() or CellSet.getCell(int ordinal) or Cell.getProperty(StandardCellProperty.CELL_ORDINAL): you would get a runtime error if you called any of these methods. You could still call CellSet.getCell(List coords), CellSet.getCell(Position... positions), List Cell.getCoordinates(), and Cell.getProperty(Property) for any other Property than CELL_ORDINAL.

Random-access mode is for regular cell sets. If you attempted to call one of the above methods, it would find the length of the columns axis, scanning to the end if necessary.

Problem #2: Backwards iteration

If you're writing an OLAP client application, it's really nice that the ListIterator can go backwards. You can even go back to the start and iterate over the list, as many times as you like. But if you're writing an olap4j driver which is intended to be network and memory efficient, backwards iterators are sheer hell. You have to buffer values just in case the client wants to go back.

So, we need a way for the client to declare (or the driver to infer) that the client will not go backwards. My preferred option would be to change the CellSetAxis method ListIterator<Position> iterate() to Iterator<Position> iterate(), and throw a runtime error if this method is called more than once.

Another option would be to open the CellSet with an option where the client promises not to drive iterators backwards; but this would not support case #2, where you might want to restart the iterator over columns but not the iterator over rows.

Let me know your thoughts at the olap4j Open Discussion forum.

SDForum BI SIG

2007-09-30T15:56:00.000-07:00

I shall be giving a talk entitled "Building Scalable OLAP applications with Mondrian and Pentaho" to the Business Intelligence SIG of SDForum. The talk will be at 7pm on October 16th 2007 in the Cubberley Community Center in Palo Alto, CA. All are welcome; details at the SDForum site.

OpenMRS

2007-09-27T10:20:00.000-07:00

OpenMRS stands for 'Open Medical Records System', and is a major open-source project aiming to build medical information systems for developing countries. Their general goal is to provide a low-cost (free) solution for tracking patient conditions. Their specific goal is to fight AIDS in Africa.

Their website is http://openmrs.org/wiki/OpenMRS.

The project is gaining a lot of momentum. They have strong links with organizations such as the World Health Organization (WHO), corporate sponsorship from companies such as Google, and are already deployed in 12 countries.

The OpenMRS architects are are in the process of adding analytics using mondrian. I have committed to helping them out, and Matt Casters (architect of Pentaho Data Integration aka Kettle) is also involved. They are looking for committers do build the mondrian schema and analytics, and that's why I'm reaching out to the mondrian community.

Maybe you have used mondrian in a commercial project, and are looking to use those skills in a project which makes the world a better place. Maybe you have some experience with databases and data modeling, but want to hone those skills on a real project and improve your CV. If you are student or have limited income, they have some stipends available.

I urge you to get involved with this project. Go to the website and sign up! If you know anyone who would be interested in this project, please forward this to them.