Rittman Mead Blog - Data Warehousing

Tagged

Data Warehousing

A collection of 205 posts

Transcend and Index Maintenance

Before joining up with the guys across the pond, I ran my own consulting company based in Atlanta called Transcendent Data. With each new data warehouse I built, I realized I was engineering the same processes over and over again with each client. So I developed a framework of best-practices

Capturing Change (Part 1)

Shortly after I joined Rittman Mead I wrote a small article [http://www.rittmanmead.com/files/DataW_Getting_Real.pdf] on real-time Business Intelligence , there is also a link to it on our "Articles" tab [http://www.rittmanmead.com/articles/]. One of the techniques I mentioned in passing

OLAP 10gR2 and Dense Looping

I'd like to take a moment to express my appreciation to several of the folks at the OLAP product team for their assistance with the project mentioned below, including A.A. Hopeman, Jameson White, Marty Gubar, and especially David Greenfield. The sky's the limit for Oracle

Thoughts on the New DW Features in Oracle Database 11gR2

Well it's been about a week since Oracle Database 11gR2 [http://www.oracledatabase11g.com/] became available for download on Linux, and we've since had a chance to play around with it and see what's new. The Linux release on 32 and 64-bits is available

Slightly Fuzzy Lookups

Recently I had a requirement to selectively "translate" the data in one column of a table before loading into a data warehouse. In this case we had to "standardise" a list of countries. Normally, this is classic use of an outer join to a table containing

Using a Process Flow to loop through Months in a Table

I hate having to maintain the same ETL logic in two different places; it violates everything I know about good coding practices. When using Java, PL/SQL, or even shell script, a good developer will modularize particular bits of his or her code to make sure distinct business logic is

Drilling Down in the Oracle Next-Generation Reference DW Architecture

If you've read this blog over the past year or heard me present at conferences on Oracle data warehouse architecture, you'll probably have seen this architecture diagram used at some point, put together by Doug Cackett, Simon Griffiths, Kevin Lancaster, Andrew Bond and Keith Laker from

Hybrid SCDs using OWB and OBIEE

If you attended my ODTUG Kaleidoscope presentation on Oracle Warehouse Builder (feel free to view the presentation [http://www.rittmanmead.com/files/odtug_owb_considerations.pdf]), then you know that certain aspects of the product leave me scratching my head, especially as a follower of the dimensional modeling approach popularized

Visualizing relational data as Essbase/OLAP cubes – Partition Outer Joins and MODEL Clauses – Part 3

In a prior blog entry here [http://oraclebizint.wordpress.com/2009/05/16/oracle-bi-ee-10-1-3-4-1-%e2%80%93-report-sum-and-pivot-calculations-%e2%80%93-an-alternative-sql-on-oracle-10g-and-above-%e2%80%93-equivalence-to-mdx-%e2%80%93-model-clause-%e2%80%93-part-2/] , i had shown the advantages of using the MODEL clause. I also showed how the MODEL clause can be used to

Cardinality of Rowsources

One of the things I sometimes come across when looking at legacy ETL code for data loading is the misuse of query hints to "improve" data load performance. Sometimes DBAs or developers do not remember that "tuning the select" may not be the same thing as

Materialized View and Partitioning "Gotchas", Part II

If you follow the blog, then you know that Mark recently posted about materialized views for aggregation, located here. [https://www.rittmanmead.com/blog/2009/04/18/materialized-view-and-partitioning-gotchas/] One of the issues he encountered was how to refresh materialized views based on tables loaded with partition exchanges... a common approach

Dynamic SQL

One of my pet hates is the inappropriate use of dynamic SQL within ETL processes, for example building a command as VARCHAR2 string then using it in an "execute immediate" statement. Putting commands into strings whether hard-coded ones within the package or procedure, retrieved from some 'code&

Simple Steps to Sustainable ETL

Recently Stewart posted his first blog post here [https://www.rittmanmead.com/blog/2009/04/22/using-a-partition-by-join-to-fill-in-the-gaps/] on the Rittman Mead Blog, it drew quite a bit of comment about using external techniques to the ETL to provide mechanisms to do things more efficiently or to do things that could

Using a PARTITION BY JOIN to Fill In The Gaps

As a quick introduction, I'm Stewart Bryson, Technical Director for North America. It seems strange to be posting an entry on this blog after so many years of reading it, and learning from it. The momentum it has in the community is something I do not take lightly,

Rittman Mead and Kimball University Event

[http://www.rittmanmead.com/data-warehouse-lifecycle-in-depth/] The data warehouse continues to be one of the most organizationally complex and technically interesting projects in Information Technology. Rittman Mead are proud to announce this unique data warehousing training opportunity, in collaboration with Kimball University and Quest For Knowledge.Learn from Margy Ross and

Bitmap Indexes Redux

I was running a data warehousing training event for a client recently, and in one of the sessions we looked at bitmap indexes and bitmap join indexes. Whilst the session went OK I remember thinking to myself at the end, "you know what, there's a fair bit

Tuning an Oracle 11g Data Warehouse Using the DBConsole Advisors

I'm currently back in Denmark running the first of my Oracle 11g Data Warehousing Masterclasses [https://www.rittmanmead.com/blog/2008/07/15/oracle-11g-data-warehousing-seminar-denmark/], today was the first day and I'm currently back in the hotel working through some material for tomorrow. One of the main

Oracle BI Training Days

Over the past few months the team at Rittman Mead have been busy developing new course content and seeking out suitable venues for our first ever public course, the Oracle BI Training Days [https://www.rittmanmead.com/blog/oracle-bi-training-days-october-22nd-24th-london-uk/] . This course is a fantastic opportunity to learn more about designing,

Testing Advanced (OLTP) Compression in Oracle 11g

Another new feature in Oracle 11g of interest to data warehouse developers is Advanced Compression [http://www.oracle.com/database/advanced-compression.html]. This new feature extends the capabilities of segment compression first introduced with Oracle 9i, but crucially makes it work for all DML operations, not just direct path inserts,

Investigating Oracle 11g Interval Partitioning

If you've kept abreast of what's new in the latest release of the database, you will probably be aware of three major enhancements to partitioning in Oracle 11g. Firstly, there's the new reference (ref) partitioning where you can partition one table based on a

Cube, Rollup, Grouping Sets and Grouping IDs

If you've kept up with the various new data warehousing features in Oracle over the past few years, you might have noticed extensions to the GROUP BY clause such as ROLLUP, CUBE, GROUPING SETS and so on. If like me you've glanced at these features and

Oracle 11g Partitioning

Last time I mentioned [https://www.rittmanmead.com/blog/2008/08/28/bitmap-index-examples/] that I have been helping Mark with some demonstrations of the Data Warehouse features of modern (notably 11g) Oracle databases. Having a few minutes spare I decided to take a look at partitioning. I have long been

Bitmap Index Examples

Between assignments (mostly, OBIEE and BI Applications work) and a family holiday in the Austrian Alps I have been helping Mark with some of the examples he will be demonstrating as part of his 11g Data Warehousing Seminar [https://www.rittmanmead.com/blog/2008/07/15/oracle-11g-data-warehousing-seminar-denmark/]. I must confess

Tuning 11g Data Warehouse Schemas using the SQL Access Advisor

One of the new features in Oracle Database 11g that I've been meaning to try out for a while now is the SQL Access Advisor, part of the 11g Tuning Pack. Given that one of Oracle's key product directions is to make administering the database as

Teaching data warehousing

One of my first tasks on joining Rittman Mead Consulting was to develop a new three-day course on data warehouse design. A few weeks ago I had the opportunity to 'road test' some of the course content in a different presentation format; a tutorial-style workshop. Although I had