Peter Scott - Rittman Mead (Page 10)

BI Suite first impression

Last week I downloaded and installed the Enterprise Edition BI suite from Oracle’s e-delivery website. Installation of the components that I wanted to try was a breeze and getting the admin tool up a running and pointing at a real database was straight forward. I had decided to go

forum

Mark Rittman has been hosting a BI & DW forum [http://www.rittman.net/forum/] since the beginning of the year. But from a recent post from Mark there is a strong possibility that forum will fold; not because there is a lack of posts but because it is starting

Data Warehousing

Learn by play

Today was a playday. I spent the day having a go with the new Enterprise Edition of Oracle’s BI suite. I found some of the tools very easy use and as a person with a database rather than a user-tool background I didn’t have that baggage of saying

Data Warehousing

Rollup the sub partitions

In data warehouse there is often the need to summarise, aggregate, or roll-up (the three terms often being used interchangeably) fact data. This is either achieved at query time within the query or in advance by the use of pre-built summaries. As I blogged yesterday [https://www.rittmanmead.com/blog/

Summaries

David Aldridge, aka the OracleSponge [http://oraclesponge.blogspot.com/] has recently blogged [http://oraclesponge.blogspot.com/2006/05/three-pillars-of-oracle-data.html] on the three ‘P’s of Data Warehousing. David commented that the “pseudo P”, summarisation, was perhaps the most important. Well, I must say that I agree with him there.

Technical

An interesting case

Tuning data warehouse queries is not always easy; when you take into account complications such as parallel query, bitmap indexes, star transforms, and query rewrite then there is a lot going on to confuse the picture. This is further complicated by the sort of SQL generated by query tools, especially

Data Warehousing

I like SQL Developer

One of our customers sent in some SQL that took ages to run. On the face of it was not doing anything difficult, it just inserted the results of an aggregate query into another table. The select part of the query joined two large partitioned tables on the partition key

Data Warehousing

Debating

Earlier this year Mark Rittman and I briefly discussed the concept of a matching pair of conference papers on aggregation in Business Intelligence. Mark would look at the use of Oracle OLAP as an aggregation technique and I would do something similar on conventional (and maybe less conventional) relational techniques.

Honest consulting

On a follow-up to my last post Beth [http://www.blogger.com/profile/20798882] wrote: It's been my experience that clients appreciate honest (yet tactful) discussions with the techies when making their decisions. She is absolutely right. I can remember at least two occasions where customers specifically asked

Big, bad disk.

Over on Doug Burn’s blog there is a link [http://www.pythian.com/blogs/170/750g-disks-are-bahd-for-dbs-a-call-to-arms] to an interesting piece on large disks. Some people would think that the data warehousing community would welcome large disks. But probably for the majority (those of us that use conventional relational databases)

Data Warehousing

Big game hunting

The last DW post talked about dates and times within a data warehouse. The time that a sale or some other transaction occurs can usually be recorded by an Oracle DATE value in the data warehouse; some DW may need to use timestamps, but to-the-second times would normally be adequate.

Data Warehousing

Time for more dates

For most data warehouses the time (date) dimension appears, or at least should appear, in every query. I can think of a few DWs where time is not always used; one example being an asset (infrastructure) maintenance reporting system for a utility company, each pipe, inspection chamber, valve, or whatever

Real Time Reporting

Earlier this week, I represented our global BI group in a review of a whitepaper on enterprise portals being written by another division in our company. The paper covered real-time reporting of operational performance, which of course is very similar to some of our BI work. My review was needed

I must get out and write more

I had my monthly session with my business mentor today. Within my division mentors (or as some might say, coaches) are chosen to offer differing perspectives on the world. My first mentor was our former director of outsourcing; on his retirement I moved to guidance of the head of our

Business Intelligence Suite

Recently, Oracle announced [http://www.oracle.com/appserver/business-intelligence/index.html] their Business Intelligence Suite with the immediate availability of the Enterprise and Standard editions. The Standard edition contains much of the original Oracle toolset and the Enterprise edition owes much of its heritage to Siebel Analytic Server. A third

ROW_NUMBER() rules!

Despite working with DW systems for some while, the use of Oracle Analytic functions on a production system seems to have passed me by. Not because they don’t work or are slow – far from it, but because the business questions I usually get asked to look at typically can

Should I, shouldn't I

Browsing the UKOUG website [http://www.ukoug.org/] I see that the deadline for paper abstracts for the November conference is 1st May. As I had thoughts of pitching up with a DW presentation of some sort I better get my thinking cap on. I have a few ideas which

Data Warehousing

Rewrite problems

One of the Oracle 9.2 data warehouses my team manages uses nested materialized views for the aggregation strategy. Nesting of aggregates can reduce the workload on the batch as ‘expensive’ roll-ups only need happen once. The downside to nesting is that in order to make query re-write work for

Hunting Deadlocks part 3

In part two [https://www.rittmanmead.com/blog/2006/02/28/hunting-deadlocks-part-2/], I mentioned that large files could take tens of seconds to process and the processing of a file is wrapped into a single transaction that either fails or succeeds. I also said this problem seemed to occur with

Hunting deadlocks part 2

I never like finding why a deadlock happens, all too often the solution is embedded in the application code where some error in logic has caused multiple sessions to do things in different orders. The text of the error message provided by Oracle implies “heap all blame at the developers’

Hunting deadlocks (part 1)

Occasionally, just occasionally, I am asked to leave my sane, safe data warehouse world (OK, not many DBAs out there would agree with that) and take a look at some other systems. For the past couple of days I have been looking at ‘deadlock’ problems on a legacy application and

Data Warehousing

More slow cases

Yesterday [https://www.rittmanmead.com/blog/2006/02/16/what%e2%80%99s-that-about-then/] I had a small rant about the use of case statements in where clauses. In a reply to this parkerpg suggested this was perhaps generated by the use of query environment such as Cognos. In this case

Data Warehousing

What’s that about then?

At the risk of Tom Kyte telling me that I have given up thinking… Any ideas why this appeared as part of a WHERE predicate (or more to the point why write it!) > … AND ( case when T.V_STATUS = 'A' then 'Active' when T.V_

Data Warehousing

Real-life parallels

If you read AskTom you will probably know that there is no such thing as a universal go-faster switch in Oracle. Everything is “maybe faster, maybe slower and your mileage may vary”. Over the past week or so we have been user-testing a revised parallelism setting on a 3 TB

Data Warehousing

Collecting statistics

For a while we have used DBMS_STATS.GATHER_SCHEMA_STATS with the gather stale option to collect statistics on one of our Oracle 9.2 data warehouses. It was perhaps a little slower than our old approach but it did the job and was also code not written (and