All Quarter Display

system · January 6, 2009, 5:48pm

I have a Quarter Dimension Table joined to a Fact Table . Irrespective of what the quarter user selects in the prompt and whether there is data in fact table for that quarter or not , I need to display all the Four quarters of the year in the table( with zero values if data is not there) . How can I design this in universe . The Database envirorment is SQL server and BO version is BOXI R2 .

arindam_bhattach (BOB member since 2006-08-15)

system · January 6, 2009, 5:57pm

It’s not possible to display data that doesn’t exist … period. The trick of course is how to “create” the missing data. A few possibilities:

At the universe level, a creative outer join to a table (maybe an alias or derived table) that has all possibilities.
At the query panel level, a union that concatenates all possibilities (with zero amounts for measures) to the “real” answer set.
At the report level, link to a personal data provider (Excel or similar) that has all possibilities … similar to this technique.

Hopefully that will nudge you in the right direction.

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · January 7, 2009, 10:45am

An elaborative example would help more.

For eg. Lets consider the Efashion universe and its three tables. Shop_facts, Article_lookup and Calender_Year_lookup. I need to display in the report Shop_facts.Quantity_Sold for one particular item for the next 12 quarters for the year selected irrespective of whether I have data or no data in the sales table

for e.g. in the prompt if I had selected 2004 and Item as SWEATERS then the output should be :

YEAR QUARTER SWEATERS

2004 Q1 0
2004 Q2 50
2004 Q3 0
2004 Q4 20
2005 Q1 0
2005 Q2 10
2005 Q3 0
2006 Q4 0
2006 Q1 0
2007 Q2 0
2008 Q3 0
2009 Q4 0

Need to create this at Universe level as editing the SQL at report level is not a feasible option for us. Please help.

Thanks

arindam_bhattach (BOB member since 2006-08-15)

system · January 7, 2009, 10:57am

I think the best solution would be, like Dwayne Hoffpauir pointed out, to create an outer join between the Quarter dimension and the fact table.

This will get you the result you want.

Hans1963 (BOB member since 2008-10-02)

system · January 7, 2009, 11:56am

Provided that your quarters exist in a calendar table somewhere, create two data providers in the report. One DP is just to return all years/quarters. The second DP returns the data you actually want, plus years/quarters. Then merge the year/quarter dimensions and display the merged dimenions for year/quarter on the report.

This works for me as I do something similar.

Debbie

Debbie (BOB member since 2005-03-01)

system · January 7, 2009, 12:01pm

I created an outer join between Calender table and Fact table but its not working. I tried with left outer join and also right outer join. It only displays the combination which is having data. Anybody who can try this out and give a step by step guidance will be much appreciated.
Thanks.

arindam_bhattach (BOB member since 2006-08-15)

system · January 7, 2009, 12:03pm

Can you give the code you tried/generated? I tried it myself and it worked fine.

Hans1963 (BOB member since 2008-10-02)

system · January 7, 2009, 12:20pm

This is the code generated for me . We are using SQL Server Database. Did you create an alias and used outer join on that or directly created an outer join on Calender table with Fact table ? Please could you let me know the SQL generated.

Select DIM_QUARTER.QUARTER_YYYYQQ,FACT_INW_PREMIUM_QUARTER.DIM_SYNDICATE_KEY,
FACT_INW_PREMIUM_QUARTER.DIM_UNDERWRITING_DIVISION_KEY,
FACT_INW_PREMIUM_QUARTER.DIM_DURG_CLASS_KEY,FACT_INW_PREMIUM_QUARTER.DIM_CURRENCY_KEY,
FACT_INW_PREMIUM_QUARTER.DIM_YEAR_OF_ACCOUNT_KEY,
FACT_INW_PREMIUM_QUARTER.DIM_QUARTER_KEY,EARNED_PURE_PREMIUM FROM
FACT_INW_PREMIUM_QUARTER
LEFT OUTER JOIN DIM_QUARTER ON
DIM_QUARTER.DIM_QUARTER_KEY=FACT_INW_PREMIUM_QUARTER.DIM_QUARTER_KEY,
DIM_SYNDICATE,DIM_UNDERWRITING_DIVISION UNDERWRITING_DIVISION,DIM_DURG_CLASS DURG_CLASS, DIM_CURRENCY CURRENCY
WHERE
DIM_QUARTER.YEAR_YYYY IN ( ‘2008’ ) AND CURRENCY.DIM_CURRENCY_KEY=FACT_INW_PREMIUM_QUARTER.DIM_CURRENCY_KEY
AND
DIM_SYNDICATE.DIM_SYNDICATE_KEY=FACT_INW_PREMIUM_QUARTER.DIM_SYNDICATE_KEY
AND
UNDERWRITING_DIVISION.DIM_UNDERWRITING_DIVISION_KEY

arindam_bhattach (BOB member since 2006-08-15)

system · January 7, 2009, 12:31pm

Thanks Debbie,

Your solution has worked fantasic for me !

arindam_bhattach (BOB member since 2006-08-15)

system · January 7, 2009, 1:55pm

For the Given code to work, you need an outer join to all other dimensions as well.


SELECT 	DIM_QUARTER.QUARTER_YYYYQQ,
	FACT_INW_PREMIUM_QUARTER.DIM_SYNDICATE_KEY, 
	FACT_INW_PREMIUM_QUARTER.DIM_UNDERWRITING_DIVISION_KEY, 
	FACT_INW_PREMIUM_QUARTER.DIM_DURG_CLASS_KEY,
	FACT_INW_PREMIUM_QUARTER.DIM_CURRENCY_KEY, 
	FACT_INW_PREMIUM_QUARTER.DIM_YEAR_OF_ACCOUNT_KEY, 
	FACT_INW_PREMIUM_QUARTER.DIM_QUARTER_KEY,
	EARNED_PURE_PREMIUM 
FROM 
	FACT_INW_PREMIUM_QUARTER 
LEFT OUTER JOIN DIM_QUARTER 
  ON DIM_QUARTER.DIM_QUARTER_KEY=FACT_INW_PREMIUM_QUARTER.DIM_QUARTER_KEY	
LEFT OUTER JOIN DIM_SYNDICATE
  ON DIM_SYNDICATE.DIM_SYNDICATE_KEY=FACT_INW_PREMIUM_QUARTER.DIM_SYNDICATE_KEY 
LEFT OUTER JOIN DIM_UNDERWRITING_DIVISION UNDERWRITING_DIVISION
  ON UNDERWRITING_DIVISION.DIM_UNDERWRITING_DIVISION_KEY = ...
, DIM_DURG_CLASS DURG_CLASS
, DIM_CURRENCY CURRENCY 
WHERE 
    DIM_QUARTER.YEAR_YYYY IN ( '2008' ) 
AND ...

Hans1963 (BOB member since 2008-10-02)