Information on OLAP Databases? 34
oh-lap asks: "One relatively new topic in computing that hasn't seen much mainstream press (yet) is OLAP or multidimensional databases. OLAP is based on the principle of organizing your data along a number of dimensions which can be pivoted and drilled into. OLAP sounds like a neat idea and represent an abstraction of several problems which I've seen tackled by custom coding jobs. The biggest problem with OLAP right now, seems to be a dire lack of expert knowledge in the area and no standardized API or query language for OLAP servers. But the lack of decent introductory reading material is particularly bothersome. If there are any experts (or even dabblers) in OLAP out there, what are the sources of information (books, websites, forums, webblogs, etc) that you go to for info about OLAP? The issues I'm interested in are wide ins scope and include everything from querying and data modeling to actual design of an OLAP server. Any pointers"
Wikipedia: OLAP (Score:2, Insightful)
MS SQL Analysis Services (Score:4, Insightful)
Re:MS SQL Analysis Services (Score:3, Informative)
It comes with some sample cubes to check out, and you can browse the data using Excel.
As for books: Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step
and MDX Solutions: With Microsoft SQL Server Analysis Services
Re:MS SQL Analysis Services (Score:3, Informative)
Mostly Product-Specific (Score:4, Informative)
http://www.cio.com/research/data/data_mining.ht
http://www.datawarehousingonline.com/rdetail_dw
They aren't technical links, but they might point you to something useful. ORA has a book on accessing OLAP servers from Java that might have some useful background information.
For us, we've more or less settled on MSSQL and Crystal Analysis as our most-likely-to-be-viable-and-affordable platform. It's not best of breed, but it is the cheapest. We've looked for OSS/OLAP resources, but they aren't plentiful at this point (as far as we've found... someone please prove me wrong!).
We're mostly concerned with analysis of a data warehouse, so we may differ from what you want to do with OLAP. I'm sure an Oracle or DB2 shop would have more to say on OLAP as it pertains to their products.
MDX (Score:3, Informative)
MSDN MDX [microsoft.com]
There's also some books being sold on the major websites like Amazon.
Re:MDX (Score:2)
Ralph Kimball (Score:5, Informative)
expert here (Score:5, Informative)
OLAP concepts have been around for quite some time, although as a mainstream product only since the early '90s. Today the two major players in the market are Microsoft [microsoft.com] and Hyperion Solutions [hyperion.com], with Cognos [cognos.com] a distant third. (To be fair, there are plenty of other players, these are just the three largest). I personally have worked extensively with both Essbase and Analysis Services, and can honestly say that both have very strong points, but generally speaking I have found Hyperion's Essbase to be superior as a whole to Microsoft's Analysis Services.
The original thread commented on how little mainstream press OLAP has received, which I wholeheartedly agree with. However, it is worth noting that many, if not most, large businesses have OLAP implementations (I can't find a source, but if I remember correctly, 499 of the Fortune 500 companies have an implementation.). Considering the market is about $3.5 billion [olapreport.com], the products are definately being used extensively. Granted that is a drop in the bucket of the total relational marketplace, but it is hardly insignificant.
Personally, I strongly believe the lack of knowledge outside of the Finance departments of the world about the concepts is directly tied to the lack of exposure the concepts receive at the collegiate level, which continues to focus on relational databases as the primary storage of enterprise data (I am not implying that OLAP database can replace relational databases, they are truely a complimentary product tailored for specific use). The lack of a standardized API & querying languages is definately a problem for the industry, one which the large players have tried to address, with limited success. On the flip side, the industry has pushed (rightly or wrongly) toward selling the total package, with both the backend database and a suite of front end data entry & reporting tools all in one. This makes for an easy sell to many customers, but doesn't encourage much innovation from the outside (IMHO, the front ends to OLAP products are the weakest link, and really do need the most development).
Ironically, one of the best online resources I've found is IBM's [ibm.com] web site. They repackage Hyperion's Essbase as DB2/OLAP, so their Redbooks on the concepts are slanted toward Essbase, but they have the most extensive source of free information on the concepts I've seen from a commercial vendor.
Yukon - MS SQL Server 2003 new features (Score:4, Informative)
Probably, most important - Yukon t-sql will have the ability to PIVOT. This is a huge time savor for anyone doing reports, which is where most of the functionality of OLAP comes into play.
For more info on the new features of Yukon, see the link below:
http://www.sqljunkies.com/Tutorial/F73E1FFE-0577-
Re:Yukon - MS SQL Server 2003 new features (Score:2, Interesting)
I've seen SQL querys that took hours to compute only take a matter of seconds for an olap cube.
But yes, OLAP is used for reporting, but really its strength lies in analysis, which means you are not relying on predefined reports.
Re:Yukon - MS SQL Server 2003 new features (Score:2)
Datawarehouse is the generic term (Score:2)
Check out Cognos (Score:4, Informative)
While I personally work in a Cognos/SQL Server shop and am therefore probably a bit biased, I seriously think you should take a look at Cognos' offerings. Their software is great and probably about as leading edge as you can get in the OLAP world. While we're using their PowerPlay [cognos.com] and Impromptu [cognos.com] products, the direction they're going at the moment is towards ReportNet [cognos.com] which is a new product they just released a couple of months ago. Browing their website I came across this online demo [cognos.com] that might help you in coming up with a justification for such a system (registration required, but worth it IMHO).
Anyway, if you're looking for help from someone who deals with OLAP systems - drop me a line. ;-)
Re:Check out Cognos (Score:2, Informative)
PowerPlay is the OLAP analysis tool. It came originally in a desktop application, and now in a web application; both use the same OLAP engine. We have our own proprietary cube format, but we support Essbase and MSAS OLAP as well.
Impromptu is the relational database reporting tool, which can report on data from all the major DBs, but does no OLAP.
Likewise for ReportNet. There is some OLAP technology built into ReportNet, but th
Re:Check out Cognos (Score:2)
Thanks for the clarification - I probably should have mentioned those specifics rather than just linking to the product webpage, but what are you going to do?
The reason I mentioned ReportNet specifically is because, according to a Cognos rep. I spoke to a while ago, the long-term vision over there (as I understood it) was to replace PowerPlay, Impromptu, possibly Visualizer and whatever other applicable applications over to ReportNet after version 8 or 9. If that's indeed the truth then it seems like a goo
OLAP, ROLAP, MOLAP, HOLAP, Shmolap .... (Score:4, Informative)
Data Warehousing for Cavemen [greenspun.com] by Phil Greenspun (of ArsDigita fame) -- some background, and implementation using SQL.
A dimensional modelling manifesto [dbmsmag.com].
Wikipedia has good coverage [wikipedia.org].
MDX is the query language (look it up in MSDN). Personally, I don't like the syntax but who cares.
And then, when you're convinced OLAP is complex, have a look at Stevan Apter's Drilldown [nsl.com] example. The source code is here [nsl.com], all of one printed page, including the GUI and generation of random data.
Some historical perspective (Score:2)
I am by no means an OLAP expert, but back in 1995 (I think) I worked on a project where we analyzed huge amounts of antimicrobial resistance data, and I looked at a lot of multidimentional databases, read a lot of magazine articles on the asubject, etc. I remember that back then, the traditional database was considered obsolete. I remember Informix went on a shopping spree acquiring Red
This is actually a very old database idea. (Score:2, Interesting)
Can anyone say MUMPS?
Massachusetts General Hospital Utility Multi-Programming System.
MUMPS origin [erols.com]
The latest version of the MUMPS language/database is Cache [intersystems.com]
There is even a free version called GT/M
Sanchez GT/M [sourceforge.net]
The story of MUMPS is actually a sad tale of a bad language with a great Database.
Origin in 1967
ISO standard 11756 (1991).
ANSI standard: "MUMPS Language Standard", X11.1 (1977, 1984, 1990)
Effectivly killed in the late 90's by Intersystems.
The hallmarks of M were the very te
What has not been mentioned so far ... (Score:2)
As you say, there is no standard, and different vendors advocate different solutions.
The main issue is that the Cube (as OLAP databases are often called) has to be refreshed every day from the data source (the main system). It becomes a nightmare to sync the data, specially when the OLAP was started by departments, without IT involvement in the first place.
ROLAP, the relational version h
Re:What has not been mentioned so far ... (Score:1)
OLAP is old news and useful (Score:2)
* Crystal Reports
* MS Excel Pivot Tables
* Cognos
* Brio Enterprise
They usually have little appeal to software developers but are incredibly useful to managers. I've always wondered why as OLAP lets end users quickly pivot and drill through data without having to bug IT people to get reports written...
Adding OLAP type analysis is a great idea for a
Re:OLAP is old news and useful (Score:2)
And newish on the scene is Siebel Analytics (hmmm still a little iffy about this one)
Re:OLAP is old news and useful (Score:2)
DEVELOPERS LISTEN UP: The trend in OTS software is to add OLAP capabilities and call it analytics. From a technical standpoint, it's whoop-de-doo. From the end user standpoint it's what they want. If you have a VAR channel, they want nothing of it because they will lose revenue writing custom reports.
Siebel's product is a case study in this trend.
Open source OLAP (Score:1)
TDWI (Score:1)
For coverage on OLAP and more generally, business intelligence, visit The Data Warehousing Institute [dw-institute.com]. In addition to their online articles, you can subscribe to a weekly email newsletter [101com.com] which provides timely info about BI and OLAP developments. If you really want to get into it, they have some hands-on courses [dw-institute.com] offered fairly regularly around the US. I haven't taken any of them, but based on the course outline the OLAP course looks pretty informative and I think TDWI has a good reputation.
Finally, you
Oracle and MS SQL Server OLAP (Score:2)
Microsoft has a separate analysis engine (Analysis Services), with separate storage for the cubes. This has the benefit of offloading processing onto another server, but carries with it the burden of transferring the data, usually with the Data Transformation Services (DTS) utility. But DTS doesn't scale well in the current version, and is being totally rewritten in Yukon to correct thi
instantOLAP (Score:1)
This is "low footprint" in terms of installation issues and requirements for backend databases: The engine enables you to talk OLAP to any relational database without first needing to create cubes in your dedicated OLAP server. This way you can issue OLAP queries to your production database instead of the one month old cube.