Re: Star Schema with Multiple Outer Joins - Help!

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 2 Jul 2002 21:25:57 -0400
Message-ID: <8ssU8.8780$68.275518@news4.srv.hcvlny.cv.net>

I must admit that it took me a while to figure out what you might be trying to do.
Its always prudent to also provide a simplification of your problem (next to the actual
problem) .. and provide data specific details as to what you are trying to achieve.

Essentially you are asking for a cartesian join between the dimension tables and then want to join it (outer) with the fact table. You want to do this to find out which combination does not exist in the fact table. If I am correct in my assumption, read on.

I have simplified the problem ... but I guess you will get the idea ....

avarma> select * from x1; -- Fact Table

         A B C
---------- ---------- ----------

         1         19          1
         2         20          1

avarma> select * from f1; -- Dimension Table

         A A_DESC
---------- ----------

         1 1_desc
         2 2_desc
         3 3_desc

avarma> select * from f2; -- Dimension Table

         B B_DESC
---------- ----------

        19 19_desc
        20 20_desc

avarma> select a_desc, b_desc, c from x1, f1, f2   2 where f1.a = x1.a(+) and f2.b = x1.b(+); where f1.a = x1.a(+) and f2.b = x1.b(+)

           *
ERROR at line 2:
ORA-01417: a table may be outer joined to at most one other table

^^^^^ You are trying this

avarma> select a_desc, b_desc, nvl(c,0)    from
avarma>      (select a, a_desc, b, b_desc from f1, f2) dimen, -- Do
cartesian join for all combinations
avarma>      x1

avarma> where dimen.a = x1.a(+) and dimen.b = x1.b(+);

A_DESC B_DESC NVL(C,0)
---------- ---------- ----------

1_desc     19_desc             1
1_desc     20_desc             0
2_desc     19_desc             0
2_desc     20_desc             1
3_desc     19_desc             0
3_desc     20_desc             0

6 rows selected.

Though it beats me as to why you would want to do this (unless of course, if I'm wrong in my assumptions) ... a cartesian join of 6 dimension
tables might be huge!

Anurag

"Gary Karpoff" <gary-k_at_vips.com> wrote in message news:aft03v$2g1$1_at_news.umbc.edu...
> How's this for a conundrum! I'm fairly new to Oracle and far from a SQL
> expert and I will be forever grateful to any Oracle wizard out there who
can
> help with this challenge. My data model is a star schema with
> MSM_TEST_STATE_SMRY_FACT as the fact table and 17 related dimension
tables.
> Each row in the fact table has the key values for all 17 dimensions
> (STATE_ID, PRD_ID, AGE_GRP_ID, RACE_ETHNCTY_ID, SEX_ID, MAS_ID, BOE_ID,
> SCHIP_ID, DUAL_ELGBL_ID, SRVC_CTGRY_ID, SRVC_TYPE_ID, CLM_TYPE_ID,
> PGM_TYPE_ID, PLAN_TYPE_ID, MDCR_XOVER_ID, ADJSTMT_IND_ID, AND CNTY_ID)
along
> with various facts (UNIQ_ELGBLS_CNT, UNIQ_BENE_CNT, etc.). The only fact I
> am concerned about is UNIQ_ELGBLS_CNT. The fact table rows consist of only
> those combinations of dimensions that have related fact values (for
example,
> if the combination where all dimensions [STATE_ID through CNTY_ID] are
equal
> to 1 would result in a null value for UNIQ_ELGBLS_CNT, there is simply no
> row containing this combination of dimension values in the fact table). I
> need to create a view that contains every possible combination of 6
> dimension values from the dimension tables along with corresponding values
> for UNIQ_ELGBLS_CNT from the fact table. In those cases where the
> combination of dimension IDs does not exist in the fact table, I need to
> create a row containing the dimension values from the dimension tables
along
> with a zero for the UNIQ_ELGBLS_CNT fact.

>

> So, in the SQL below, I try to select the needed ID values into the temp
> table TMP2 and outer join this table to the 5 appropriate dimension tables
> to create the view. Of course, this query produces the "you can only outer
> join a table to one other table" error. I've heard that there is a way
> around this but I haven't been able to generate the correct syntax. I need
> to create a number of similar views using various subsets of the data (or
> one monster view including everything), so any help will be MOST
> appreciated. Thanks!

>

> CREATE VIEW MSM_STATE_SMRY_RPTS_QTRLY_V AS
> SELECT STATE_ABRVTN_CD, FY_NUM, FSCL_QTR_NUM, AGE_GRP_CD_DESC,
> RACE_ETHNCTY_CD_DESC,
> SEX_CD_DESC, nvl(UNIQ_ELGBLS_CNT, 0)
> FROM
> (SELECT STATE_ID, PRD_ID, AGE_GRP_ID, RACE_ETHNCTY_ID,
> SEX_ID, UNIQ_ELGBLS_CNT
> FROM MSM_TEST_STATE_SMRY_FACT
> WHERE
> CNTY_ID IN (SELECT MSM_TEST_STATE_SMRY_FACT.CNTY_ID
> FROM MSM_TEST_STATE_SMRY_FACT, MSM_CNTY
> WHERE MSM_TEST_STATE_SMRY_FACT.CNTY_ID = MSM_CNTY.CNTY_ID
> AND MSM_CNTY.CNTY_CD = '***')
> AND MDCR_XOVR_ID = 3
> AND CLM_TYPE_ID = 7
> AND ADJSTMT_IND_ID = 7
> AND MAS_ID = 8
> AND BOE_ID = 12
> AND SRVC_CTGRY_ID = 19
> AND SRVC_TYPE_ID = 31
> AND PGM_TYPE_ID = 10
> AND PLAN_TYPE_ID = 12
> AND DUAL_ELGBL_ID = 12
> AND SCHIP_ID = 6 )
> TMP2,
> MSM_STATE SD,
> MSM_PRD PD,
> MSM_AGE_GRP AD,
> MSM_RACE_ETHNCTY RD,
> MSM_SEX XD
> WHERE SD.STATE_ID = TMP2.STATE_ID (+)
> AND PD.PRD_ID = TMP2.PRD_ID (+)
> AND AD.AGE_GRP_ID = TMP2.AGE_GRP_ID (+)
> AND RD.RACE_ETHNCTY_ID = TMP2.RACE_ETHNCTY_ID (+)
> AND XD.SEX_ID = TMP2.SEX_ID (+)

>
> Received on Tue Jul 02 2002 - 20:25:57 CDT