Want to read Slashdot from your mobile device? Point it at m.slashdot.org and keep reading!

 



Forgot your password?
typodupeerror
Check out the new SourceForge HTML5 internet speed test! No Flash necessary and runs on all devices. ×

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"
This discussion has been archived. No new comments can be posted.

Information on OLAP Databases?

Comments Filter:
  • Wikipedia: OLAP (Score:2, Insightful)

    by Anonymous Coward
    Wikipedia entry for OLAP [wikipedia.org]
  • by MaxQuordlepleen ( 236397 ) <el_duggio@hotmail.com> on Wednesday March 31, 2004 @10:53AM (#8725588) Homepage
    If you happen to have a Microsoft SQL Server lying around, try the Analysis Services tutorial. Takes about 4 hours to do all the tutorials...
    • 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
    • If, on the other hand, you don't have MS SQL Server on hand, you can go here [microsoft.com].
  • by Bravo_Two_Zero ( 516479 ) on Wednesday March 31, 2004 @10:59AM (#8725632)
    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.

    http://www.cio.com/research/data/data_mining.htm l

    http://www.datawarehousingonline.com/rdetail_dw. as p?ID=857&sub_category=OLAP

    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)

    by Zardoz44 ( 687730 ) on Wednesday March 31, 2004 @11:08AM (#8725715) Homepage
    Have you looked into MDX, the SQL for OLAP?

    MSDN MDX [microsoft.com]

    There's also some books being sold on the major websites like Amazon.

    • Watch out though, coming from a traditional SQL 2D set perspective to nD MDX can seriously warp your mind. It took me a couple weeks just to completely conceptualize what was going on. Also, the MSDN docs/example are a little thin, at least for what we were trying to accomplish. Of course, now that I've learned it, I can't seem to find someone to hire me to use it.
  • Ralph Kimball (Score:5, Informative)

    by lal ( 29527 ) * on Wednesday March 31, 2004 @11:14AM (#8725774)
    Check out Ralph Kimball's site [ralphkimball.com]. 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.
  • expert here (Score:5, Informative)

    by NumLk ( 709027 ) on Wednesday March 31, 2004 @11:25AM (#8725870)
    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 [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.
  • by frooyo ( 583600 ) on Wednesday March 31, 2004 @11:26AM (#8725884)
    Microsoft is implementing a lot of important functionality that is heavily used in OLAP into Yukon.

    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-4 630-B92E-C41CB7C5088F.scuk [sqljunkies.com]
    • That is a bit simplistic. Probably the biggest advantage of OLAP over SQL servers (any of them) is SPEED! OLAP does agregation querys hundreds of times faster than SQL servers.

      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.
  • For all this stuff. An OLAP cube is just one way of implementing the DW. As a previous post mentions Ralph Kimball is *the* guy here and his books are very readable.
  • Check out Cognos (Score:4, Informative)

    by illusion_2K ( 187951 ) <.ac.evlossid. .ta. .todhsals.> on Wednesday March 31, 2004 @11:37AM (#8725987) Homepage

    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)

      by Anonymous Coward
      I'm going to clarify your statements, keeping in mind that I work at Cognos.

      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

      • 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

  • by Circuit Breaker ( 114482 ) on Wednesday March 31, 2004 @11:50AM (#8726142)
    Recommended reading:

    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.
  • One relatively new topic in computing that hasn't seen much mainstream press (yet) is OLAP or multidimensional databases

    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

  • New as of 1968
    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
  • OLAP is not a new concept. I evaluated OLAP products back in 1996 as part of a project for a customer's data warehouse.

    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
  • The driver behind the "data warehouse" concept is reporting and analysis. OLAP like capabilities have been available to end users for some time via tools like:

    * 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
    • Not to forget the biggest platey around Business Objects reporter/developer...

      And newish on the scene is Siebel Analytics (hmmm still a little iffy about this one)

      • Siebel Analytics (hmmm still a little iffy about this one)

        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.
  • Java, Open Source OLAP. Mondrian [sourceforge.net]
  • 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

  • Although both Oracle 9i Release 2 and Microsoft SQL Server 2000 have OLAP features, they differ greatly in their approach.

    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
  • Hi, if you don't like the complexities and/or prices or installation hazzles of "the big" OLAP enabled servers, try instantOLAP [instantolap.net] with a low footprint and easy web access to your data.
    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.

Take an astronaut to launch.

Working...