Thoughts on Open Source, Analytics

Lengthy Emailed Questions

Like other bloggers who post their email, I regularly get emails from people asking about topics which I blog about… I’m not really all that much of an expert on it all, just publish what I know. Usually it’s a brief:

Obscure question here: How can I do this little thing X with OWB because it’s not readily apparent in the product and the docs/forums/web searches haven’t yielded anything.

Ocassionally, people email with a whole BUNCH of question(s), that would, if answered properly, chew up a many hours of time. While I enjoy solving technology problems I do so in the context of charging people money to do it. I’d like to for free, but that would leave my paying customers behind schedule. So, when it becomes a sizable chunk of time I usually don’t answer. I’ve received three such emails in the past couple of weeks, and I figured what the heck… why not post them to the blog and see if the community as a whole wants to have a go at them. So… if you know the answer and feel like posting it please do!

Here goes:EMAIL 1:

hello,

I am looking at upgrading our current out-dated Oracle 8.0.5 database to 10G (eventually),
but for now upgrading to 8.0.6 before the next phase. I want to be able to test the 8.0.6
database migration first, I have a few questions…

Can I upgrade a test database first for testing purposes, and once satisfied upgrade the production database ?
Is this just done by setting the correct Oracle_SID and running the upgrade process?
Do I have to run multiple listeners (8.0.5 and 8.0.6 )?
To upgrade each instance do I have to run the upgrade procedure for each instance?

Regards,

EMAIL 2

Hi Nicholas.

I’m a big fan of your blog and read it a lot for getting information regarding the BI. I have a few questions regarding OLAP cubes in Oracle and am hoping that you would be able to help out. We want to use MOLAP cubes in Oracle to run against historical data compromising more than 50M records. With 1M records our cubes (comprising of 6-8 dimensions) get refreshed in about 4 minutes with limited aggregation. Using incremental refresh the next 1M records take about 10 minutes while the next 1M takes around 30 minutes. This times keeps on increasing with additional refreshes. At this rate we fear that in a production environment with 50M+ records the incremental refresh time would be way too much. We want to do a one time build of the cube (with about 40M records) and then a weekly/monthly incremental refresh (of about 1M records).

We are using Oracle 10.1.0.4 on Windows 2000 on a HP Proliant DL380 (3.4GHz*2 and 6GB RAM). SGA Target is 890Mb and PGA Target is 370MB.

My questions are:

What is your opinion/experience about running OLAP cubes against high volume data (50M+ records). Please comment on both the cube maintenance time and the drill time at runtime in the reports. Do you know of any production systems that use cubes with this volume or higher? Are MOLAP cubes meant to operate against such a data volume or should we switch to ROLAP? Do you have any white paper that would help us in designing our cubes and configuring our hardware/oracle?

If we want to keep our daily incremental refresh time (for 1M records) for one cube to say 30 minutes then what do you think should be the minimum recommended machine specs for our environment? How many processors? How much RAM?

Thanks in advance.

EMAIL 3

Hi Nicholas,

My name is XXXX and I’m working as Product Analyst with XXXXXX Inc. XXXXXX is providing software solutions to XXXXX and XXXXXX industries.

I have been reading your blogs and I like them very much.

I have a real quick question. We want to develop a data warehousing solution that requires almost real-time warehousing. Is there a way we can do it with Oracle’s Asynchronous Change Data Capture and Oracle Warehouse Builder R1?

I’m not sure when OWB R2 will be out and how long it is going to take to stabilize. Is there a way to get hold of a beta version?

Post navigation

One thought on “Lengthy Emailed Questions”

[[I am looking at upgrading our current out-dated Oracle 8.0.5 database to 10G (eventually),
but for now upgrading to 8.0.6 before the next phase. I want to be able to test the 8.0.6
database migration first, I have a few
questions…]]

The terminal release for Oracle8 was 8.0.6, ensure you upgrade to the terminal patch release 8.0.6.3. The next step would be to Oracle 8i, the terminal release for that was 8.1.7.4
This info comes from OTN http://www.oracle.com/technology/products/index.html – select ‘Database Standard/Enterprise Editions’ from the section ‘Database’. That will lead you to Metalink links for which you will need a login id.

[[Can I upgrade a test database first for testing purposes, and once satisfied upgrade the production database ?]]

This is a good idea to give you a feel for the work and time involved. All the better if your test database conforms to your production database, then you can test functionality. Going from 8.0.5 to 8.0.6 is not a big step. I would advise reading the release notes and info about the new release to find out about new and deprecated features.

[[Is this just done by setting the correct Oracle_SID and running the upgrade process?]]

This is the question which would take all the time to answer – read the documentation. 8.0.5 > 8.0.6 is not a big step (more or less a patch in fact) but there will be documentation to support it with the software.

[[Do I have to run multiple listeners (8.0.5 and 8.0.6 )?]]

In my experience, no. A later version of a listener will work for earlier database versions.

[[To upgrade each instance do I have to run the upgrade procedure for each instance?]]

Generally yes. Upgrades often include Data Dictionary changes. If you have to run code from SQL*Plus or Enterprise Manager, this indicates that the code does something to the instance, so will be required for each instance.

Sorry for the format of this reply, but there is not a lot I can do about it!!