Slashdot Mirror
Information on OLAP Databases?
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"
We've had similar issues wrapping our minds around OLAP and justifying a budget for the analysis services. Most of the documentation is, as you noted, product-specific.
m l
. as p?ID=857&sub_category=OLAP
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.
Amateurs discuss tactics. Professionals discuss logistics.
MSDN MDX
There's also some books being sold on the major websites like Amazon.
Check out Ralph Kimball's site. A good place to start is his book, The Data Warehouse Toolkit. I haven't read the 2nd edition, but the first edition is full of good practical examples of dimensional modeling.
I'm going to preface this by saying I'm an expert in this field, so my comments may be biased.
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 and Hyperion Solutions, with Cognos 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, 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 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.
Children in the backseats don't cause accidents. Accidents in the back seats cause children.
Microsoft is implementing a lot of important functionality that is heavily used in OLAP into Yukon.
4 630-B92E-C41CB7C5088F.scuk
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-
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 and Impromptu products, the direction they're going at the moment is towards ReportNet which is a new product they just released a couple of months ago. Browing their website I came across this online demo 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. ;-)
Recommended reading:
Data Warehousing for Cavemen by Phil Greenspun (of ArsDigita fame) -- some background, and implementation using SQL.
A dimensional modelling manifesto.
Wikipedia has good coverage.
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 example. The source code is here, all of one printed page, including the GUI and generation of random data.
That is the nice thing about Analysis Services. It is "Free" (as anything is from Microsoft). Basically, if you own a copy of MS SQL Server (a non-MSDE version) then you have Analysis Services. Note: you can buy the Developer Edition of SQL Server for $50.
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
Bad User. No biscuit!
If, on the other hand, you don't have MS SQL Server on hand, you can go here.