Julian Hyde on Open Source OLAP. And stuff.

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.