I am building a business view using the Synonym Editor. I am joining two DB2 tables in the master file.
The first table contains payment information and the second is a calendar dimension.
Each row in the data table has a key to the calendar dimension.
The calendar dimension contains dates from 2000 to 2030. The payment data just has data for 2013 and 2014.
I am creating an active dashboard off of the business view and adding a year dropdown.
All years from 2000 to 2030 are showing up in the dropdown not just the years 2013 and 2014.
I am doing an INNER JOIN in the master file. It appears that I have to use columns from both segments to force the join to happen.
Is there a setting to make the first table request behave like the second table request?

PMT_TABLE
==============================
PMT_DATE_ID  PMT_AMT      PMT_CNT
176454       1338541.22   41
176354       14158541.24  16
176484       135541.97    84
176254       1348541.34   12
184664       1158541.55   4


CALENDAR_DIM
=============================
CALENDAR_DIM_ID  YEAR_NBR  MNTH_NBR  DATE
176454           2014      11        11/24/2014
176354           2013      11        11/13/2013
176484           2014      10        10/17/2014
176254           2014      9         9/22/2014
184664           2013      8         8/14/2013


-* Gives me 2000 to 2030
TABLE FILE PMT_DATA_VW
SUM
   FST.YEAR_NBR AS YEAR_NBR
BY YEAR_NBR 
END


-* Gives me 2013 and 2014
TABLE FILE PMT_DATA_VW
SUM
   FST.YEAR_NBR AS YEAR_NBR
   PMT_CNT NOPRINT
BY YEAR_NBR 
END

This message has been edited. Last edited by: <Kathryn Henning>,

WHERE YEAR_NBR GE 2013 AND YEAR_NBR LE 2014 maybe ???

This perhaps?

JOIN INNER
 CALENDAR_DIM.CALENDAR_DIM.CALENDAR_DIM_ID TO UNIQUE
 PMT_TABLE.PMT_TABLE.PMT_DATE_ID AS J0
END
TABLE FILE CALENDAR_DIM
SUM
   FST.YEAR_NBR AS YEAR_NBR
BY YEAR_NBR
WHERE PMT_TABLE.PMT_TABLE.PMT_DATE_ID IS NOT MISSING;
END

Or if that join is already in your view(?) PMT_DATA_VW, you may be able to get away with just:

WHERE PMT_CNT GT 0;

Mighty Max,

Which table is the parent table? From what you are describing I would assume that the Calendar Dim is your parent table. If you make PMT_TABLE your parent in the Synonym based join, you will always have to invoke that join when getting your data. WF will optimize your query so that it will only use the tables necessary to the fields requested starting with the parent table. If CALENDAR_DIM is your parent table, then the PMT_TABLE information is unnecessary and it won't write the sql to include that table. If PMT_TABLE is your parent then in order to get to the CALENDAR_DIM table from the PMT_TABLE it will need to invoke your join.

If you wanted to keep the CALENDAR_DIM as your parent table, and still wanted to have the inner join invoked, you would then have to use a technique like what you have done with your second fex

quote:

-* Gives me 2013 and 2014
TABLE FILE PMT_DATA_VW
SUM
FST.YEAR_NBR AS YEAR_NBR
PMT_CNT NOPRINT
BY YEAR_NBR
END

PMT_TABLE is the parent and CALENDAR_DIM is the child. I made a simpler view with just one join and getting the same behavior.

  
FILENAME=pmt_data_vw, $
  SEGMENT=PMT_TABLE, CRFILE=PMT_TABLE, CRINCLUDE=ALL, $
  SEGMENT=CALENDAR_DIM, SEGTYPE=KU, PARENT=PMT_TABLE, CRFILE=CALENDAR_DIM, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=PMT_DATE_ID EQ CALENDAR_DIM_ID;, $

Business Views (and more generally any multi-segment MFD that points to an RDBMS) behave differently than generic RDBMS views. An RDBMS view with an inner join will always run that inner join. A Business View will only issue the SQL against those tables for which you are requesting data. In your case, the table file you are issuing is only requesting data from CALENDAR_DIM. Since you are not selecting any data from PMT_TABLE, WebFOCUS issues the most efficient SQL statement it can - which is what you are seeing.

Normally this behavior is a good thing. It allows you to create highly complex reporting structures with the understanding that the actual SQL which gets generated by your table file will only grab the information you want and will not issue unnecessary joins. In your situation you will need to do some extra work.

The most obvious solution is to simply limit the data in your calendar table to the years for which you have payment data. This would require another process to insure that when you add data for a new year into the payment table the calendar table stays in synch. If this is not possible (perhaps your calendar table is also used in another context where the full range of dates is required) then there are always other solutions.

EricH

Mighty Max,

I tried this on some of the metadata that I've created and found one Synonym that worked as you described and another that worked as I had originally expected. In looking at the difference between the two, I think I have come up with a work around. If you add a filter to your join statement that requires only the first table, it should invoke that inner join every time. So for instance, try writing your master file as :

FILENAME=pmt_data_vw, $
  SEGMENT=PMT_TABLE, CRFILE=PMT_TABLE, CRINCLUDE=ALL, $
  SEGMENT=CALENDAR_DIM, SEGTYPE=KU, PARENT=PMT_TABLE, CRFILE=CALENDAR_DIM, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=PMT_DATE_ID EQ CALENDAR_DIM_ID AND PMT_DATE_ID GE 0;, $  

Let me know if this works for you.