Archive for the ‘oracle’ Category

Since I've spent some time working with Oracle Business Intelligence last year, I think I'd write a simple comparison list of strengths\weaknesess of OraBI EE vs Cognos (see post on the same topic by Venkatakrishnan). All of this is imho, of course.

Oracle BI EE Pro's:

1) Aggregate navigation

-- ability to set up aggregate tables in BI EE itself, which gives it 'aggregate awareness' while generating SQL. So if you have monthly sales aggregate you just set up BI metadata accordingly and all reports will target this table for monthly data.
There's no such feature in Cognos metadata setting.
But using this feature arises some questions:
a) whether aggregate definition should be BI-tool specific. A lot of practioners, including Kimball, insist that aggregate navigator should reside at database level so that every tool querying datawarehouse will benefit from aggregate avalaibility. This seems rather reasonable, since every datawarehouse is usually queried by more than one analytical application (BI, datamining, more BI) so you either direct all applications to BI EE, or agree to performance degradation.
b) aggregate tables desynch. When you just update the base fact table, aggregate tables become "out of synch", providing incorrect query results untill they're recalculated. This means that you either can guarantee that nobody will access reports in this period, or you can have incorrect data. Since more&more datawarehouses squezee load windows to reach real-time this problem gains priority.
c) choosing which agregate to use for answering questions. That's what statistics is all about in dbms, knowing number of rows and value distribution (lol). Oracle BI EE has the "table row count" feature (although I haven't seen it affect SQL generation yet, need more examples). But there's no value distribution analysis in there, so it's just one side of the coin (not talking about I\O device speed and other characteristics).

In general, it's recommended to use database-specific aggregate table functionality (Oracle Materialized Views or DB2 MQT) since it solves all the 3 questions given above and usually simplifies ETL process (and sometimes even speeds it up, since databases use their own transaction logs to detect what data has changed and what aggregates should be rebuilt). Too bad indexed views in Ms SQL do not work with aggregate dimensions (there's no way to define dimension hierachies there).

2) Cache management

-- OraBI has really profound cache management facility. You can "cache" any database query to OraBI specific storage structure (cache file), wich will allow subsequent queries to the same table\query to run without actual database request being made. This can greatly speed things up. I especially like the Event Polling Table feature: you add a table, which records when dwh table was last updated. OraBI then reads at given intervals and automatically invalidates old cache entries, based on this table records.
Moreover, if you have OraBI cluster this cache can be shared among servers.

There isn't anything even close in Cognos.

Although I greatly like this feature, I just want to warn about overusing it. It's easy to imagine BI developers boosting performance by adding more&more cache untill OraBI becomes a fully blown aggregate system. And sometimes it's just about 1 aggregate table at the dwh level ) Or about introducing OLAP server in the enviroment )))

3) SQL generation.

It's tricky subject, but for now I like OraBI generated SQL more. But it's "apples to oranges" for sure, since I usually use DMR's in Cognos which encumbers SQL greatly and there's nothing identical in OraBI EE.

Oracle BI EE Con's:

1) Multidimensionality

Cognos has Analysis studio and the ability to navigate hierarchies in both directions (you won't believe it, but in OraBI there's no 'Drill-Up', only'Drill-Down'). And DMR's and analytical functions (but their usage is a bit annoying, as it seems now, hope to write about it later). Anyway, Cognos is way much more 'multidimensionally-ready' than OraBI.

OraBI + Essbase is a work in progress and has a huge number of caveats (some fixed by patches, some not, some introduced). And the only way to use all Essbase functionality is to write direct MDX via Evaluate functions. That's a big problem, since it's hard for us to suggest OraBI on top of Essbase for now (till 11g once again). The only alternative is Visual Explorer, which is a very good tool, but it for top-analysts only (thick client, costly).

2) Metadata model development

Instead of OraBI's 'only-star schema', '3 layers of model' Cognos FM Manager doesn't impose any design principles, which allows more mistakes, but it makes some things way more simple. One of the first things I wanted to do in OraBI was a report using just a single table.  Well that's a really funny exercise (see posts over here) if a star schema is a must.

But the main problem is the lack of API for metadata changing and browsing. There's udml, but it's not supported officially. Therefore all current integration scripts (like adding users, merging repositories and working with hierarchy depth changes) are out of the law. Which doesn't stop anyone, but is pretty annoying.

3) Pixel-perfect reports

Oracle BI Publisher is a specific tool, aimed at generating a huge number of formatted reports, based on XML format definition files. It wasn't a part of Siebel BI, so 'integration stitches' still stand out. It's a nice tool, but it certainly lacks web-interface ) Therefore in Oracle BI there's a deep distinction between a simple formatted report (with lots of possible logic in it) and making this report 'printer-friendly' since for the latter you basically have to start from scratch by opening Ms Word )  This will change in 11g as they say )

Having said all that, I really wait for Oracle BI 11g edition to start using it with Essbase and I kinda like the product as it is for "relational-only" reporting.

I surely wanted to write a simple bullet point list at first )