BI Adhoc Reporting on OLTP model

[Padmalakshmiravi]

I would like to get all your valuable inputs on building BI Adhoc Reporting solution on top of a OLTP model. Pls share your thoughts and any articles published.

Regards,
Padmalakshmi

[ngalemmo]

Ad hoc reporting over OLTP schema has been done for the last 40 years. It stinks. That's why data warehouses were created.

[Padmalakshmiravi]

If not on OLTP schema, what are the options for Real Time Reporting? Users are not ready to accept any Near Real Time solutions.
How good is Oracle Golden Gate in supporting Real Time DW? Pls suggest.

[ngalemmo]

That depends on what 'real time reporting' entails: What is 'real time'? What do you want to report? Is the business capable of taking action on the report in a timely manner? Is this an operational issue (such as looking at a specific thing, such as a customer's order, a WIP work order, etc...)? There is no one answer or a golden bullet (or gate).

[amosgreg]

How good is Oracle Golden Gate in supporting Real Time DW?

Depending on various systematic things that may or may not be readily quanitifiable(transaction volume, number of replicated tables, network thru-put ....) GoldenGate latency can be measured in subsecond responce times. Transformations into other data structures take time and while Goldengate does support some types of transformations straight table to table replication is extremely fast.

Now depending on the reporting front-end needs(due to the transformation needs), near realtime might not really be an option.
We are currently replicating both Tandem Enscribe files to Oracle tables and SQLMX tables to Oracle tables with both being OLTP systems and both with minimal transformation in near realtime.
We are using Crystal Reports and Oracle Stored Procedures as the reporting engine for stock reports however we are not going to be able to create and use a Business Objects Universe on the existing table structure. We will need to transform the DB into a star to use the Webi and InfoView for the self-service/adhoc reporting engine.

Greg

[Sponsored content]