Hi,
Here is a scenario: (Table structure, Schema, and query below)
I have 3 tables. 2 Facts and 1 Dimension.
I am playing with multi-fact schema.
The problem I am facing is that when I run the query below I only get the values for 2 Regions.
It doesn't show the Region that exists in 1 Fact (DMF_Billing) but missing from the other (DMF_Booking) .

I was expecting the result set to be:
Region_Name |Billings |Bookings
Asia | $100 | $200
Americas | $100 | $200
Europe | $100 | -



--Query
TABLE FILE multifact
SUM MULTIFACT.DMF_BILLING.Billings
MULTIFACT.DMF_BOOKING.Bookings
BY MULTIFACT.DIM_REGION.REGION_Name
ON TABLE PCHOLD FORMAT HTML
END


--Table Structure:

DMF_BILLING:
Region_Key | Billings
1 |$100
2 |$100
3 |$100

DMF_ BOOKING:
Region_Key | Bookings
1 |$200
2 |$200

DIM_REGION:
Region_Key | Region_Name
1 | Asia
2 | Americas
3 | Europe
4 | Misc


--Cluster Schema:

FILENAME=MULTIFACT, $
SEGMENT=DMF_BILLING, CRFILE=DMF_BILLING, CRINCLUDE=ALL, $
SEGMENT=DMF_BOOKING, PARENT=., CRFILE=DMF_BOOKING, CRINCLUDE=ALL, $
SEGMENT=DIM_REGION, CRFILE=DIM_REGION, CRINCLUDE=ALL, $
PARENT=DMF_BILLING, SEGTYPE=KU,
JOIN_WHERE=DMF_BILLING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $
PARENT=DMF_BOOKING, SEGTYPE=KU,
JOIN_WHERE=DMF_BOOKING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $



Thanks

This message has been edited. Last edited by: <Emily McAllister>,

As far as I can see both BILLING and BOOKING have an INNER JOIN with the REGION, so only what exist in both tables will be retrieve.

Your master file look like this:

BILLING  BOOKING
   |        |
   ---|  |---
      V  V
     REGION

You may need to use the LEFT OUTER JOIN instead or SET PASS = ALL

This message has been edited. Last edited by: MartinY,

Changed to left outer Join.. still the same result.
I had read somewhere that "When WF see's a request against a multiple root master, then it switches on multi support and does the match for you."
But doesnt look like its seeing it properly or I am missing something.

--Edited Cluster Schema
FILENAME=MULTIFACT, $
SEGMENT=DMF_BILLING, CRFILE=DMF_BILLING, CRINCLUDE=ALL, $
SEGMENT=DMF_BOOKING, PARENT=., CRFILE=DMF_BOOKING, CRINCLUDE=ALL, $
SEGMENT=DIM_REGION, CRFILE=DIM_REGION, CRINCLUDE=ALL, $
PARENT=DMF_BILLING, SEGTYPE=KU, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=DMF_BILLING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $
PARENT=DMF_BOOKING, SEGTYPE=KU, JOIN_TYPE=LEFT_OUTER,
JOIN_WHERE=DMF_BOOKING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $

Or try to do it the other way

     REGION
        |
     --- ---
     |     |
     V     V
BILLING  BOOKING

Since it makes more sense to go from the DIMENSION to the FACT

Thanks but I cannot go from DIMENSION to the FACT as I may have multiple DIMESNSION and multiple FACT.

I took a look at the TRACE and its doing a Join. Not reading it as a multiple ROOT master and not doing a MATCH for some reason.

Normally I build a cluster on one FACT and (may have) multiple DIM in a star model.

I don't see any issues including multiple FACT unless you are trying to perform a snowflake which I think, may be a problem. But I may be wrong.

Have you tried to change your SET PASS options ?

SET ALL = PASS didnt work
still the same issue.

Depending on what I SUM first it puts the Left Outer Join on that table.
Basically, in the query below its Bookings lft outer Join Billings.


SET ALL = PASS
TABLE FILE edw_prod/multifact
SUM MULTIFACT.DMF_BOOKING.BOOKINGS
MULTIFACT.DMF_BILLING.BILLINGS

BY MULTIFACT.DIM_REGION.REGION
END

This message has been edited. Last edited by: BM,

quote:

multiple DIMESNSION and multiple FACT

Your sample data shows no scope for multiple dimension values, would you care to provide something more akin to your issue?

IDMS/R used to deal with many-to-many join structures via a junction record which contained the unique common key values. Might this be something that you want to try?

If I use a cluster join of the following, using your sample data, the output looks OK?

FILENAME=MULTIFACT, $
 SEGMENT=DIM_REGION, CRFILE=DIM_REGION, CRINCLUDE=ALL, $
 SEGMENT=DMF_BILLING, PARENT=DIM_REGION, CRFILE=DMF_BILLING, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BILLING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $
 SEGMENT=DMF_BOOKING, PARENT=DIM_REGION, CRFILE=DMF_BOOKING, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BOOKING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $

T

Hi Tony,
Thanks.
We have many Dimension and Fact. here is the table structure with multiple-dimenstions and multiple-facts.
I was able to get the expected result using "MATCH FILE".

Was hoping multi-root cluster schema would take care of it automatically for me as mentioned in this post.

http://forums.informationbuild...377097826#1377097826



--Table Structure:

DMF_BILLING:
Region_Key |SUPPLIER_KEY |UNIT_KEY |Billings
2 |4 |1 |$100
3 |4 |2 |$100
2 |5 |3 |$100
3 |1 |1 |$100
2 |2 |2 |$100
3 |4 |5 |$100



DMF_ BOOKING:
Region_Key |SUPPLIER_KEY |UNIT_KEY |Bookings
2 |4 |1 |$200
1 |3 |2 |$200
2 |5 |3 |$150
3 |1 |1 |$600
2 |2 |2 |$300
3 |4 |5 |$600


DIM_REGION:
Region_Key | Region_Name
1 | Asia
2 | Americas
3 | Europe
4 | Misc


DIM_SUPPLIER:
SUPPLIER_KEY | SUPPLIER_NAME
1 | ABC
2 | CDE
3 | XYZ
4 | RRR
5 | TTT

DIM_UNIT:
UNIT_KEY | UNIT_NAME
1 | ACB123
2 | CED123
3 | XZY123
4 | RSS123
5 | TTR123

I now understand.

Try this code. The first sections create temporary FOCUS files for your sample data and a cluster join that should help you(?).

Have a look at the source to see the CHECK FILE ... PICT output to see the structure that is created.

Good luck!

T

SET PAGE = NOLEAD
EX -LINES * EDAPUT MASTER,DMF_BILLING,CV,MEM
FILE=DMF_BILLING,SUFFIX=FOC
SEGNAME=SEG1
FIELD=REGION_KEY,   ,I11   ,I11  ,FIELDTYPE=I, $
FIELD=SUPPLIER_KEY, ,I11   ,I11  ,$
FIELD=UNIT_KEY,     ,I11   ,I11  ,$
FIELD=BILLINGS,     ,D9M   ,D9   ,$
EDAPUT*
-RUN
CREATE FILE DMF_BILLING
MODIFY FILE DMF_BILLING
FREEFORM REGION_KEY SUPPLIER_KEY UNIT_KEY BILLINGS
DATA
2,4,1,100,$
3,4,2,100,$
2,5,3,100,$
3,1,1,100,$
2,2,2,100,$
3,4,5,100,$
END
-RUN
EX -LINES * EDAPUT MASTER,DMF_BOOKING,CV,MEM
FILE=DMF_BOOKING,SUFFIX=FOC
SEGNAME=SEG1
FIELD=REGION_KEY,   ,I11   ,I11  ,FIELDTYPE=I, $
FIELD=SUPPLIER_KEY, ,I11   ,I11  ,$
FIELD=UNIT_KEY,     ,I11   ,I11  ,$
FIELD=BOOKINGS,     ,D9M   ,D9   ,$
EDAPUT*
-RUN
CREATE FILE DMF_BOOKING
MODIFY FILE DMF_BOOKING
FREEFORM REGION_KEY SUPPLIER_KEY UNIT_KEY BOOKINGS
DATA
2,4,1,200,$
1,3,2,200,$
2,5,3,150,$
3,1,1,600,$
2,2,2,300,$
3,4,5,600,$
END
-RUN
EX -LINES * EDAPUT MASTER,DIM_REGION,CV,MEM
FILE=DIM_REGION,SUFFIX=FOC
SEGNAME=SEG1
FIELD=REGION_KEY,   ,I11   ,I11  ,$
FIELD=REGION_NAME,  ,A15   ,A15  ,$
EDAPUT*
-RUN
CREATE FILE DIM_REGION
MODIFY FILE DIM_REGION
FREEFORM REGION_KEY REGION_NAME
DATA
1,Asia,$
2,Americas,$
3,Europe,$
4,Misc,$
END
-RUN
EX -LINES * EDAPUT MASTER,DIM_SUPPLIER,CV,MEM
FILE=DIM_SUPPLIER,SUFFIX=FOC
SEGNAME=SEG1
FIELD=SUPPLIER_KEY,   ,I11   ,I11  ,$
FIELD=SUPPLIER_NAME,  ,A15   ,A15  ,$
EDAPUT*
-RUN
CREATE FILE DIM_SUPPLIER
MODIFY FILE DIM_SUPPLIER
FREEFORM SUPPLIER_KEY SUPPLIER_NAME
DATA
1,ABC,$
2,CDE,$
3,XYZ,$
4,RRR,$
5,TTT,$
END
-RUN
EX -LINES * EDAPUT MASTER,DIM_UNIT,CV,MEM
FILE=DIM_UNIT,SUFFIX=FOC
SEGNAME=SEG1
FIELD=UNIT_KEY,   ,I11   ,I11  ,$
FIELD=UNIT_NAME,  ,A15   ,A15  ,$
EDAPUT*
-RUN
CREATE FILE DIM_UNIT
MODIFY FILE DIM_UNIT
FREEFORM UNIT_KEY UNIT_NAME
DATA
1,ACB123,$ 
2,CED123,$
3,XZY123,$
4,RSS123,$
5,TTR123,$
END
-RUN
EX -LINES * EDAPUT MASTER,MULTIFACT,CV,MEM
FILENAME=MULTIFACT, $
 SEGMENT=DIM_REGION, CRFILE=DIM_REGION, CRINCLUDE=ALL, $

 SEGMENT=DMF_BILLING, PARENT=DIM_REGION, CRFILE=DMF_BILLING, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BILLING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $

 SEGMENT=DIM_SUPP_1, PARENT=DMF_BILLING, CRFILE=DIM_SUPPLIER, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BILLING.SUPPLIER_KEY EQ DIM_SUPP_1.SUPPLIER_KEY;, $

 SEGMENT=DIM_UNIT_1, PARENT=DMF_BILLING, CRFILE=DIM_UNIT, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BILLING.UNIT_KEY EQ DIM_UNIT_1.UNIT_KEY;, $

 SEGMENT=DMF_BOOKING, PARENT=DIM_REGION, CRFILE=DMF_BOOKING, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BOOKING.REGION_KEY EQ DIM_REGION.REGION_KEY;, $

 SEGMENT=DIM_SUPP_2, PARENT=DMF_BOOKING, CRFILE=DIM_SUPPLIER, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BOOKING.SUPPLIER_KEY EQ DIM_SUPP_2.SUPPLIER_KEY;, $

 SEGMENT=DIM_UNIT_2, PARENT=DMF_BOOKING, CRFILE=DIM_UNIT, CRINCLUDE=ALL, $
 JOIN_WHERE=DMF_BOOKING.UNIT_KEY EQ DIM_UNIT_2.UNIT_KEY;, $

EDAPUT*
-RUN

CHECK FILE MULTIFACT PICT

SET ALL = PASS

TABLE FILE MULTIFACT
  SUM DIM_SUPP_1.SUPPLIER_NAME
      DIM_UNIT_1.UNIT_NAME
      BILLINGS
      DIM_SUPP_2.SUPPLIER_NAME
      DIM_UNIT_2.UNIT_NAME
      BOOKINGS
   BY REGION_KEY
   BY REGION_NAME
END