Monday, May 19, 2008

DIMENSION Objects

One of the very first things I did when I got my current job was to go through the Datawarehousing guide in the Oracle documentation.

Analytics, check.
Materialized Views, check.
OLAP, interesting, but not applicable yet.
Dimensions...hmm, dimensions.

I looked at all the objects in the database but couldn't find any of the type DIMENSION.

I searched AskTom, nothing (or at least nothing I could find).

I can't remember finding any articles on the DIMENSION objects either. Can that be right? Does any one our there use them? Mr. Rittman? Mr. Aldridge? Mr. Scott?

From my brief perusal of the documentation, you can set up the DIMENSION like this:

CREATE TABLE test_dim
(
test_key NUMBER(10,0) PRIMARY KEY,
source VARCHAR2(10) NOT NULL,
source_subcategory VARCHAR2(10)
);

INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 1, 'MEDICAID', NULL );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 2, 'MEDICARE', NULL );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 3, 'SSA', 'MEDICARE' );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 4, 'SPAP', 'MEDICARE' );
INSERT INTO test_dim( test_key, source, source_subcategory )
VALUES ( 5, 'MAPD', 'MEDICARE' );

CREATE DIMENSION test_dim
LEVEL source IS ( test_dim.source )
LEVEL source_subcategory IS ( test_dim.source_subcategory );
My best guess is that it's sort of like the heirarchical table format (FK referencing PK from the same table).

Does anyone out there have any experience using them?

5 comments:

Narendra said...

Hey,

Not sure what exactly are you looking for (which you could not find on AskTom), but here is what I found on searching AskTom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47464735113741

And

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:366194700346997787

There are som more as well.

oraclenerd said...

Thanks Narendra.

I can't say I looked too hard, but those look promising.

chet

Pete Scott said...

Mr Scott does use them :-)

If you use materialized views and query rewrite they are almost a given.

I thought I had written about them in the past, but I could always be tempted to write again!

oraclenerd said...

You may have, but I will admit I was not the most adept searcher on datawarehouse specific stuff back then.

I would be most grateful for a new (or old) article!

oraclenerd said...

Narendra,

Thanks so much for those links. I probably didn't know what I was reading the first time or I was just being lazy (I'd opt for the latter). That's a good snippet from his book on Dimensions. It's kind of what I thought (parent/child relationship)...now I just need to try one out for myself!

Thanks.

chet