Open, Web-Based OLAP Clients?

Zoloft asks: "I'm looking for a web-based OLAP client; something with lots of nifty features that PHBs find appealing. The normal available offerings are proprietary, expensive, and closed, closed, closed. Open-source would be nice but not required. Money is not an issue. What's important to me is: it's UNIX native - not NT native and ported with one of those bloated NT-to-UNIX layers - and its data formats are *open*, *readable* and *programmable* - whether it sits in files or a database. I have been beaten down for 10 months with this product that was forced upon me, which shall remain nameless of course. The only way to "develop" a custom app was through its piggish graphical front-end binary, obfuscated file formats and no programming or scripting hooks. I could go on and on, but you know the deal."

Comments

What's your feature wishlist?

[KMSelf]

I've been programming SAS for seven years, and have begun exploring open source alternatives. I don't know of any general solutions, but components exist.

What specific features do you need in an OLAP tool? Among those I can think of:

• Raw data parser -- equivalent to the SAS data step, awk, or Perl -- something to turn raw ASCII (or EBSIDC, or binary) to structured data.

• Procedural data language -- a general-use language for string manipulation, numeric conversion, arithetic, date and time manipulation and conversion, statistical functions, probability and statistical functions, data and flow control, etc. Similar to Perl, the SAS data step, PL/I.

• Statistical and summarization functions -- at a minimum, frequencies, univariate statistics, ranking, and correlation procedures. R, Perl, and other solutions can provide these.

• Performance, scalability, and efficiency options -- basic data manipulation should be quick and efficient, the solution should scale to terabyte or petabyte size data stores, and system resources should be used efficiently with respect to memory, CPU, storage, and bandwidth.

• Graphics capability -- ability to generate business and scientific plots and charts.

• Report-generating capability -- XML, HTML, postscript, monospaced output.

• A data store, or data stores, or interfaces to existing data stores (eg: RDBMSs).

• A multidimensional data model.

• Metadata management, including descriptions, validation rules, and business rules.

• Task control and scheduling.

• Front-end development tools.

• Interactive data exploration and analysis tools.

I'm not saying that a solution should have all of these features, but they are, in rough ranking, the ones I'd be looking for. My preferred model is to build a solution from existing components, or at least structure it from multiple modules, rather than look for a single integrated system. One thing SAS has taught me is that this isn't the best way to fly.

Anyone else have thoughts on relative importance, unnecessary items, or other features they would want to see?

Definitions, Definitions...

[Christopher B. Brown]

It's always useful to present some definitions when using a TLA or other acronym that everyone is not expected to be familiar with...

OLAP usually stands for On Line Analytical Processing. (Footnote: the OLAP Council website claims to intend to provide common definitions, but do not actually provide a definition for OLAP...)

Datamation describes it thus:

On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.

OLAP is pretty strongly associated with the common buzzword, "Data Warehousing."

More precisely, what it is about is the notion of taking the data created by an online transaction processing system, and collecting this into a big database that you then want to do "analysis" on.

The point here is that the analysts that are looking for patterns need to have a separate copy, as the things they do may hit a DB server hard, and are probably not friendly to the transaction-oriented operations of "Entering Invoices," "Processing Sales," "Paying Bills," and such.

SAS is pretty big on OLAP, as they have been building powerful statistical software for many years now.

Depends on what you need. (NT post, sorry)

[CodeMunch]

Disclaimer: Yes, I do realize the question was for Unix...So if you don't wanna hear about some NT stuff, stop reading this message.

Some OLAP (On-Line Analytical Processing - i.e. reporting) stuff off the top of my head that I've had contact with:

Cognos

CrystalReports/Info

ActiveReports/ActiveCube by DataDynamics

Cyberprise

My company tried Cognos and it seems to be a heavy hitter to satisfy the PHB's. It's got data mining/driling down, stored static cubes so you don't need to go back to the DB (makes it fast) and when you drill down till you are out of data, you can go into the DB off the cubes. You can run reports right off the cube data. Unfortunately I wasn't a part of that venture but from what my co-worker(developer) says, it was pretty slick... YOU DO PAY through the nose for it.

Crystal Reports: Very slick & easy to use. Almost idiot proof to run them off the web BUT the web engine is single threaded and you can only run one at a time on the web server (useless!). If your DB is slow and 5 people ask for a report each at the same time and they all take 5 minutes, the last person will be waiting 25 minutes...you know that by then they've already clicked refresh 15 gazillion times (or the default install of IE has given up). The ActiveX and JAVA controls that come with Crystal 7 that allow you to view reports through the browser are sweeeeet. You can export reports to RTF and a couple other formats right from the browser. Oh ya, it's also VERY easy to design reports and the COM interface makes it easy to work with. I demo'd CrystalInfo but select boxes confuse our users enough that we didn't want to give them the ability to create reports on a whim if they dont' understand the underlying tables. You can pay some comapny 10k U.S. for a multithreaded Crystal Print engine. Crystal7 is reasonably priced.

On the same lines, ActiveReports (and ActiveCube) by Datadynamics is quite a bit more useful although no where as easy to use as Crystal & doesn't come with the handy pre-built functions to manipulate/shape data (but I like doing stuff from scratch ;). It is an ActiveX Designer plugin for VB. You crateall your reports in VB and then create a generic report object to wrap around those activeX designers. And the best thing of all was that you could run multiple reports simultaneously which beats the pants off of Crystal. The export controls aren't as full featured though and there aren't many export options but there are enough that you can get data into pretty much any app. Besides, too many options confuse users ;) ActiveReports is reasonably priced.

Cyrberprise was another thing my company tried but I can't say much about it as our interest leaned more torwards COGNOS.

Anyway, currently we are using a crappy buggy 16-bit Helpdesk software by Applix (transitioning to a home-brew Oracle Forms app instead) and the reporting was buggy and useless..not to mention they save the users password in a text file in the root of the system drive...but I digress.
This Oracle thing (started out on web...too flaky, going client/server unfortunately) needed web reporting so all the Crystal Reports we had suited it perfect to run via ASP (Active Server Pages). I designed a system that allowed me to put all sorts of dynamic web selection forms in front of the crystal engine and pretty much run any report we had. I can add options to the selection form just by inserting into the DB and it pops up on the web page.
This allowed users to run pre-defined report templates against the system to extract the stuff the needed. All in all, it works great (except for the slow DB and single threaded Crystal report engine) and I'm in the midst of modifying it to be able to run Crystal and ActiveReports (so I can port everything to ActiveReports).

As was previously mentioned, you could use PHP script or PERL or C/C++ on Linux to do your stuff but that would require a lot of work.

Sorry I couldn't give you any Linux info. Perhaps these companies have something coming down the pipe.

--Clay