Peter Scott - Rittman Mead (Page 9)

More on summaries

One of my customers runs a chain of supermarkets. The bottom three levels of their product hierarchy are: * Saleable unit: Product as sold - could be price marked, promotion pack, "buy on get one for free" (BOGOFF) or whatever was provided by their suppliers. Each has it own

Data Warehousing

Redefining a M-VIEW query

Occasionally, people ask me why I prefer to use pre-built tables for materialized views rather than use the syntax that creates the view and allocates its storage. One reason I give is that it gives me the option of changing the MV query to improve the chances of rewrite. And

Data Warehousing

New features

One of the paragraphs in Powell's DW tuning book [https://www.rittmanmead.com/blog/2006/08/06/sitting-in-the-garden-and-reading/] was something on the lines of (and the book is not with me right now) ...features such as bitmap indexes, bitmap join indexes, index organised tables and cluster tables were

Data Warehousing

Re-summarise versus fast Mview refresh - part 2

The other day [https://www.rittmanmead.com/blog/2006/07/31/re-summarise-versus-fast-mview-refresh-part-1/] I talked about on commit refresh of m-views; of the other options for refresh, on demand is probably most useful for data warehouse users. Mviews can also be refreshed at fixed intervals, but this has the possible pitfall

Data Warehousing

Re-summarise versus fast Mview refresh - part 1

A couple of days ago I wrote [https://www.rittmanmead.com/blog/2006/07/23/re-summarisation/] that we may need re-build summary tables when items change parents. More exactly, it is the tables at the parent level (and above) that need to be refreshed when the parents adopt new offspring

End of life

Overnight in my email I received a desupport notice for Oracle Warehouse Builders 9.2 and 10.1 One of my customers has DW built on OWB 9.2 and is normally 'new release adverse' that if there are no significant problems and it does the job then

Data Warehousing

Re-summarisation

Re-summarization, re-statement or re-summarisation, call it (or spell it) as you will, is a periodic maintenance task in some data warehouses. Most fact tables contain just the measures and keys to the dimension tables. This design is flexible in that it supports both non-changing and slowly changing dimensions, and for

Data Warehousing

A weekend hotchpotch or is it hodgepodge?

A while back I wrote about using the row_number() [https://www.rittmanmead.com/blog/2006/03/23/row_number-rules/] analytic function and analytics in general. At the time I said that I did not use analytics often, perhaps rank() when people wanted to find their top 46789 customers -

Data Warehousing

Yet more on summaries

In addition to the conventional > Select D1,D2,SUM(M1) from base group by D1,D2 type of summary Oracle offers two other approaches: Oracle OLAP cubes and the various extensions to GROUP BY. Oracle OLAP has it roots in the Oracle Express technology - that is the the

Data Warehousing

More on summary tables

Summary tables are often very suitable candidates for partitioning. They are usually date organised and as we generally receive data for a limited range of dates we are able to apply the changes to a subset of partitions of a table. In particular we can get away from the use

Data Warehousing

One for the Oracle WTF guys perhaps

If you have been reading my current series on data warehouse design you will have read (or perhaps soon will, as there is also a new piece in preparation!) that a good technique in the batch process is to disable bitmap indexes during an insert and then rebuild them after

DW Design (4) - summary tables

I suppose that if we had unlimited computing power and the ability to read our entire fact table in a single, instant, read then there would be no need to summarise. But in the real world this is not the case. Summaries are used to speed queries by pre-calculating aggregations

Data Warehousing

Techniques to reduce IO - partitioning and compression

Last time [https://www.rittmanmead.com/blog/2006/07/03/dw-design-3-%e2%80%93-raw-facts-and-reference/] I wrote a few general notes about the storage of fact and reference data. I now will look at partitioning and compression in slightly more detail Partitioning is a way to divide a large table into

DW Design (3) – raw facts and reference

At regular intervals, be they monthly, weekly, daily or even every two minutes, additional fact information arrives into a data warehouse. After validation in the staging layer this information needs to be combined with the existing historical fact. Similarly new and changed reference records needs to be assimilated with existing

DW Design (2) – staging data

As mentioned yesterday, the staging area of the data warehouse has three functional uses: * It is the initial target for data loads from source systems * It validates the incoming data for integrity * It is the data source for information to be published the ‘user visible” layers of the data warehouse

DW Design (part 1)

“Which comes first, the physical or logical design for an Oracle data warehouse?” has always been a question that I have struggled to come up with a convincing answer. To my thinking they both develop simultaneously. True, when designing a data warehouse there will be a set of design criteria

DW Wisdom (4)

The final part of this small series on data warehouse wisdom looks at the enterprise strands. Enterprise DW moves away from the tactical departmental “point solutions” and into something that fits with strategic aspirations of the enterprise. On the face of it having a single solution across the enterprise as

DW Wisdom (3) - departmental business

The first two parts of this discussion covered the physical aspects of conventional DW wisdom. Today I will cover some of the business drivers for data warehouses. When people found that their transactional systems were unsuited for BI reporting (perhaps because of the performance impact of running BI on a

DW Wisdom (2) - more of the physical

Keep all the OLTP tables separate from the DW systems: In the olden days, the database ran on a single machine with one or (probably) more processors and direct connection to the disk. Enterprise storage and server clustering has moved things on a lot since then but there are still

DW Wisdom

Over the years there has been a collective set of received wisdom about Business Intelligence in general and about data warehouses in particular. Some of these ideas are lead by the technology involved: * Use as many small disks as possible – a 1TB disk would be a bad idea for a

BI Suite demonstration

Last week I ran my first customer facing demonstration of Oracle Business Intelligence Suite Enterprise Edition. The demonstration had several objectives * It allowed me to get hands-on experience of setting up a BI suite system * It allowed me to demonstrate how much web-based BI reporting tools had improved since our

Data Warehousing

Time series revisted (2)

Yesterday I wrote about the challenge of calculating YTD [https://www.rittmanmead.com/blog/2006/06/14/time-series-revisited/] on a sparse dataset. Even in these days of low cost storage I can not justify the vast amount of additional storage required to persist a YDT figure for each sale of

Time Series Revisited

One of the perverse things about BI is that what sounds difficult is often easy and what sounds simple is hard. If someone asked me how many bunches of flowers were sold on Mother’s day 2006 or how many people bought both ‘item A’ and ‘item B’ in Seattle

BI Suite (3)

I was really pleased with the Oracle BI suite demo I put together this week. Although my demo was restricted to the Answers and Dashboard components there is enough going on to impress people, myself included: Drilldown, it was a simple to mark any dimension attribute as drillable. For one

BI Suite (2)

As I posted earlier [https://www.rittmanmead.com/blog/2006/05/31/bi-suite-first-impression/], I went for the brave approach and imported a customer’s 3NF based DW design in to the administration tool and then created a denormalized business layer within the BI tool. This was a slightly more convoluted