BI Blogs

There’s lots of talk about real time, right time, period batch, message based in the Data Warehousing and BI circles these days. I think this is driven by quite a few reasons. Need for fresh data, need for unified reporting interfaces for users, etc. Mostly, I think it comes down to a TCO for IT assets. As the EAI/EII/ETL tools start to converge along with increased SOA-ee-ness of databases and middleware products there becomes quite a bit of overlap between the different product sets. Managing “one product” that does this data integration, calcuation, and movement between systems costs less to maintain than “multiple products.” Truthfully, I see little strategic (ie, warehouse and marts) data that needs to be computed in real time. Those cases do exist, and OWB 10gR2 has some new features for those that do have some Real Time DW/BI needs.

There are two major flavors of mappings in support of Real Time Data Warehousing in OWB:

• PERIODIC BATCH: This is basically a batch process that runs frequently (say every minute or so) that reads data from a QUEUE or STREAM. While the data is pushed into the DW (real time), the system only processes when run (batch). These are regular mappings that use a Stream or Queue as a source instead traditional Tables/Views/etc.
• TRICKLE FEED: This is much closer to what most people think of when we refer to real time data warehouse. Trickle feeds involve processing each individual record as it arrives, instead of waiting for them to collect. These are a special kind of OWB mapping called Real Time Mappings that run continuously and process records as they arrive.

Truthfully I’ve only kicked the tires with both of these types of mappings limitedly. I tested some of the features back in OWB Beta2 and built a conceptual mockup of how it would work for a customer of mine. What I’m presenting is a conceptual partially working mock up built using an early beta release. In other words, do not use it as reference or consider it a blueprint for how you should proceed. If there is enough interest I might submit an article to OTN on the subject. Anyone like the idea? Better yet, if you’re not one of my customers please do consider contacting me! I’d love to help build a Real Time DW solution with OWB!

OWB now includes the ability to define, deploy, and setup Streams, Queues, Queue Tables, UserDefinedTypes, and propogations within the GUI. There’s a whole set of screens that you’ll see when the community preview hits the shelves. Unlike regular OWB deployments there are some additional requirements around streams administration locations, permissions, etc, but they are easily surmountable. Also, if you’re going to be doing real time DW you need to understand a bit about the underlying technology anyhow (not tons, but enough to know why you need to have Archive Logging turned on, etc).

Refer to the following PDF for some greater details on the conceptual, but here’s a not so good screenshot:

I’ve created a mockup of a BI solution that is fed by a CRM (Customer Data Hub perhaps) and a Subscription Management Application for this example. You can see that conceptually this involves both systems sending messages either from the APPLICATION LEVEL (JMS or some other messaging technology) or the DATABASE LEVEL (with DML Stream Captures running in Oracle). In other words, we have multiple places we can get different pieces of data and the application doesn’t necessarily have to be “REAL TIME ENABLED” to send real time data. Oracle can do that on it’s behalf using the Streams technology!

Overall what this looks like is we setup the various Streams, Capture Processes (DML), Queue Tables, and Types (based on our source tables) to support our real time system. Note that the screenshot does not include the Streams on the source system or the Capture Process definitions. This only includes the DW side Streams, Queues, Dimensions, etc.

I’ve built three real time mappings (TRICKLE FEED) which in concert receive messages to add Dimension records (SCD2) and insert new Cube records (transactions). Notice this is a greatly simplified example entirely ignoring what I consider a best practice of loading into a normalized warehouse, then updating marts based on the warehouse (a la CIF methodology). Also these are all assuming to changes (ie, record corrections) just straight clean data! We should all be so lucky!

One receives updates from the CRM application and performs SCD on the appropriate Dimension objects.

The others receives event messages from a transaction based system and inserts records into Cubes.

This isn’t quite as much detail as I would like to have gone into, and I’ll quickly repeat my warning… This is just some mockups and conceptual work so don’t expect it to be accurate come OWB 10gR2 production time! I have some more thoughts on how to use this with Partition Exchange Loading to get a days “Cubes” built realtime throughout the day, and then at the end of the day move them over to the full history but that’s a whole nother article.

This blog is part of the OWB Paris Early Review series which reviews and comments on several new Paris features.

Time for a quick update.

If you read my posting a couple of weeks ago, you’ll have seen that I’ve been
working away on a few articles recently, hence the lack of updates. It’s all a
bit embarrasing actually as the Oracle Magazine ACE of the Year 2005 article was
recently published, and I also just got an email through from SearchCRM.com to
say I’d got their "BI Blog of the Year 2005" award, and if you take a look at
the site there’s been precious few updates to the blog now for several weeks. So
if you’re a new reader - it’s not always like this, I’m just a bit busy at the
moment…

The main thing that I’ve been working on (apart from proper work, of course)
is a couple of articles for OTN and Oracle Magazine. The OTN one is on the data
profiling and cleansing features in OWB10gR2 "Paris" and is due for publication
when the product comes out, whilst the Oracle Magazine one is on the DML Error
Logging feature that’s in Oracle 10.2 and should be out in the first half of
2006. Both technologies are going to have a big impact on how we do ETL going on
from now; the data profiling and cleansing feature in Paris allows you to
automatically profile your data, derive data rules and then auto-generate
mappings to correct your data, avoiding the need to write loads of SQL*Plus
scripts, put lots of spreadsheets together and work out your own mappings. The
DML Error Logging feature in Oracle 10.2 means that you can direct-path insert
data into tables and not worry about the data load erroring and rolling back -
the error logging feature just moves the "dirty" rows into a logging table, with
details of the error, and lets the load continue. All good stuff and you should
be able to read the articles next year.

I’ve been working with the UKOUG BIRT SIG committee on the

next SIG meeting in January and it’s shaping up to be a good one. The last
few meetings have been quite business and tools focussed and so for the January
session, we’re going to have a bit more of a data warehousing and methodology focus to the meeting. So far we’ve got the
following speakers lined up:

• Jeff Moss, on Five Tips
  for Tuning your Data Warehouse,
• Kevin Lancaster from Oracle EMEA on Oracle OLAP Tips & Techniques
• myself, on
  
  XML Publisher - a talk and a demo
• Donna Kelly,
  on Running a Successful OWB Project
• David Walker, on Process Neutral
  Data Modelling

Not a bad line up if I say so myself. If you’re a UKOUG member book up soon
as I think we’re going to sell out this time round.

Now I’ve finished the two Oracle articles I’ve got a bit more free time,
although I should really be starting my sample chapter for the book I’m
collaborating on. I’m doing the talk on XML Publisher at the next BIRT SIG so
I’ll probably make a start on working out how that works, and I’ll try and post
some updates and findings on the blog. I’ve also got the article I promised to
write on using OLAP_TABLE with analytic workspaces, it’s a good one actually so
I’ll try and wrap it up next week. Until then, bye for now.

Insightful article… sometimes the focus on BI in SQL 2005 has reduced the focus on SSIS, a legitimate ETL package that comes with SQL 2005. It will take a couple of years, but SQL is going to cause some big waves in a number of areas.

0 Comments

With its better-than-spreadsheet-spreadsheet marketing strategy, Actuate could find an eager audience for its FPM tool.

Will BPM and ETL ride to mainstream success on the coattails of Microsoft?s SQL Server 2005 juggernaut?

What really happened?and where does Firstlogic go from here?

Many organisations spend considerable time and effort in the implementation of the ERP system SAP R/3 and its Business Intelligence tool BW. The implementation of SAP and BW is often used to drive process improvements and efficiencies throughout the organisation. The reality is that the majority of organizations are not getting the most out of SAP and BW and are not maximising their ROI because of the time and effort it normally takes to get data into and out of SAP and BW. Microsoft Excel is a widely used and distributed business support tool - it is often seen as the tool of choice to manipulate and manage data.

Creating business driven database level aggregates enables a certain level of client / toolset independence, data storage and protection within a specialized RDBMS and lower overall Total Cost of Ownership. Without minimizing the role of the client systems in a Data Warehousing environment, it is quite obvious that the first candidate for aggregates deployment is the Data Warehouse Database, regardless of the type of client system that will ultimately present the data to the end-user.

This paper presents a method of applying the order defined by Zachman’s Framework to build the most complex components of the Corporate Information Factory. The same method can be applied for the structured definition of other systems that can be part of the CIF, like Data Marts, Metadata Repository, MOLAP cubes and so on. There are, of course, other methods and methodologies to define a Corporate Information Factory. But the Zachman’s Framework implementation was successfully tested by Open Data Systems’ consultants and proved to shorten the time and project costs with more then 10% over other methodologies.