How mondrian works with VIEW

[suppachartt]

Hi All,

I have a VIEW that was generated from OLAP dimensional database. Generally speaking, I don't have fact table. How can I work with mondrian then? Can I treat this VIEW as a fact table. In this case I need to generate Aggregate table out of this VIEW?

Cheers,
Suppachart

Code:

CREATE OR REPLACE FORCE VIEW "ESADMIN"."FR_CUBEVIEW" ("TIME", "PRODUCT", "GL_ACCOUNT", "CENTRE", "ENTITY", "CUSTOMER", "PROJECT", "SPARE", "CURRENCY", "SCENARIO", "ACTYTD_VS_LYR_FY", "ACT_FULLYR", "ACT_LY_VS_ACT2", "ACT_LY_VS_ACT2_FULLYR", "ACT_LY_VS_ACT2_YTD", "ACT_VS_ACT2", "ACT_VS_ACT2_FULLYR", "ACT_VS_ACT2_YTD", "ACT_VS_LASTYR", "ACT_VS_LASTYR_PCT", "ACT_VS_LASTYR_YTD_PCT", "ACT_VS_LYR_YTD", "ACT_VS_PREV_MTH", "ACT_VS_PREV_QTR_QTD", "ACT_VS_PREV_QTR_QTD_PCT", "ACT_VS_PRMTH_YTD", "BUD_FULLYR", "BUD_VS_ACT2", "BUD_VS_ACT2_FULLYR", "BUD_VS_ACT2_YTD", "BUD_VS_ACT_CURR", "BUD_VS_ACT_CURR_PCT", "BUD_VS_ACT_FULLYR", "BUD_VS_ACT_QTD", "BUD_VS_ACT_QTD_PCT", "BUD_VS_ACT_YTD", "BUD_VS_ACT_YTD_PCT", "BUD_VS_LASTYR", "BUD_VS_LYR_YTD", "BUD_VS_PREV_MTH", "BUD_VS_PREV_QTR_QTD", "BUD_VS_PREV_QTR_QTD_PCT", "BUD_VS_PRMTH_YTD", "CURR_ACT", "CURR_ACT_QTD", "CURR_ACT_YTD", "CURR_BUD", "CURR_BUD_QTD", "CURR_BUD_YTD", "CURR_FCAST", "CURR_FCAST2", "CURR_FCAST2_QTD", "CURR_FCAST2_YTD", "CURR_FCAST_QTD", "CURR_FCAST_YTD", "EXTRAPOLATION", "FCAST2_FULLYR", "FCAST2_VS_ACT2", "FCAST2_VS_ACT2_YTD", "FCAST2_VS_ACT_CURR", "FCAST2_VS_ACT_CURR_PCT", "FCAST2_VS_ACT_FULLYR", "FCAST2_VS_ACT_QTD", "FCAST2_VS_ACT_QTD_PCT", "FCAST2_VS_ACT_YTD", "FCAST2_VS_ACT_YTD_PCT", "FCAST2_VS_BUD_CURR", "FCAST2_VS_BUD_FULLYR", "FCAST2_VS_BUD_YTD", "FCAST2_VS_FCAST_CURR", "FCAST2_VS_FCAST_FULLYR", "FCAST2_VS_FCAST_YTD", "FCAST2_VS_LASTYR", "FCAST2_VS_LYR_YTD", "FCAST2_VS_LY_ACT2_FULLYR", "FCAST2_VS_LY_ACT_FULLYR", "FCAST2_VS_LY_ACT_FULLYR_PCT", "FCAST2_VS_LY_BUD", "FCAST2_VS_LY_BUD_FULLYR", "FCAST2_VS_LY_BUD_YTD", "FCAST2_VS_LY_FCAST_FULLYR", "FCAST2_VS_PREV_MTH", "FCAST2_VS_PREV_QTR_QTD", "FCAST2_VS_PREV_QTR_QTD_PCT", "FCAST2_VS_PRMTH_YTD", "FCAST_FULLYR", "FCAST_VS_ACT2", "FCAST_VS_ACT2_FULLYR", "FCAST_VS_ACT2_YTD", "FCAST_VS_ACT_CURR", "FCAST_VS_ACT_CURR_PCT", "FCAST_VS_ACT_FULLYR", "FCAST_VS_ACT_QTD", "FCAST_VS_ACT_QTD_PCT", "FCAST_VS_ACT_YTD", "FCAST_VS_ACT_YTD_PCT", "FCAST_VS_BUD_CURR", "FCAST_VS_BUD_FULLYR", "FCAST_VS_BUD_YTD", "FCAST_VS_FCAST2_FULLYR", "FCAST_VS_LASTYR", "FCAST_VS_LYR_YTD", "FCAST_VS_LY_ACT_FULLYR", "FCAST_VS_LY_ACT_FULLYR_PCT", "FCAST_VS_PREV_MTH", "FCAST_VS_PREV_QTR_QTD", "FCAST_VS_PREV_QTR_QTD_PCT", "FCAST_VS_PRMTH_YTD", "LASTYR_ACT", "LASTYR_ACT2", "LASTYR_ACT2_FULLYR", "LASTYR_ACT2_YTD", "LASTYR_ACT_FULLYR", "LASTYR_ACT_QTD", "LASTYR_ACT_YTD", "LASTYR_BUD", "LASTYR_BUD_FULLYR", "LASTYR_BUD_QTD", "LASTYR_BUD_YTD", "LASTYR_FCAST", "LASTYR_FCAST2", "LASTYR_FCAST2_QTD", "LASTYR_FCAST2_YTD", "LASTYR_FCAST_FULLYR", "LASTYR_FCAST_QTD", "LASTYR_FCAST_YTD", "PREV_MTH_ACT", "PREV_MTH_ACT2", "PREV_MTH_ACT_YTD", "PREV_MTH_BUD", "PREV_MTH_BUD_YTD", "PREV_MTH_FCAST", "PREV_MTH_FCAST2", "PREV_MTH_FCAST2_YTD", "PREV_MTH_FCAST_YTD", "PREV_QTR_ACT", "PREV_QTR_BUD", "PREV_QTR_FCAST", "PREV_QTR_FCAST2", "OLAP_CALC") AS   SELECT "TIME","PRODUCT","GL_ACCOUNT","CENTRE","ENTITY","CUSTOMER","PROJECT","SPARE","CURRENCY","SCENARIO","ACTYTD_VS_LYR_FY","ACT_FULLYR","ACT_LY_VS_ACT2","ACT_LY_VS_ACT2_FULLYR","ACT_LY_VS_ACT2_YTD","ACT_VS_ACT2","ACT_VS_ACT2_FULLYR","ACT_VS_ACT2_YTD","ACT_VS_LASTYR","ACT_VS_LASTYR_PCT","ACT_VS_LASTYR_YTD_PCT","ACT_VS_LYR_YTD","ACT_VS_PREV_MTH","ACT_VS_PREV_QTR_QTD","ACT_VS_PREV_QTR_QTD_PCT","ACT_VS_PRMTH_YTD","BUD_FULLYR","BUD_VS_ACT2","BUD_VS_ACT2_FULLYR","BUD_VS_ACT2_YTD","BUD_VS_ACT_CURR","BUD_VS_ACT_CURR_PCT","BUD_VS_ACT_FULLYR","BUD_VS_ACT_QTD","BUD_VS_ACT_QTD_PCT","BUD_VS_ACT_YTD","BUD_VS_ACT_YTD_PCT","BUD_VS_LASTYR","BUD_VS_LYR_YTD","BUD_VS_PREV_MTH","BUD_VS_PREV_QTR_QTD","BUD_VS_PREV_QTR_QTD_PCT","BUD_VS_PRMTH_YTD","CURR_ACT","CURR_ACT_QTD","CURR_ACT_YTD","CURR_BUD","CURR_BUD_QTD","CURR_BUD_YTD","CURR_FCAST","CURR_FCAST2","CURR_FCAST2_QTD","CURR_FCAST2_YTD","CURR_FCAST_QTD","CURR_FCAST_YTD","EXTRAPOLATION","FCAST2_FULLYR","FCAST2_VS_ACT2","FCAST2_VS_ACT2_YTD","FCAST2_VS_ACT_CURR","FCAST2_VS_ACT_CURR_PCT","FCAST2_VS_ACT_FULLYR","FCAST2_VS_ACT_QTD","FCAST2_VS_ACT_QTD_PCT","FCAST2_VS_ACT_YTD","FCAST2_VS_ACT_YTD_PCT","FCAST2_VS_BUD_CURR","FCAST2_VS_BUD_FULLYR","FCAST2_VS_BUD_YTD","FCAST2_VS_FCAST_CURR","FCAST2_VS_FCAST_FULLYR","FCAST2_VS_FCAST_YTD","FCAST2_VS_LASTYR","FCAST2_VS_LYR_YTD","FCAST2_VS_LY_ACT2_FULLYR","FCAST2_VS_LY_ACT_FULLYR","FCAST2_VS_LY_ACT_FULLYR_PCT","FCAST2_VS_LY_BUD","FCAST2_VS_LY_BUD_FULLYR","FCAST2_VS_LY_BUD_YTD","FCAST2_VS_LY_FCAST_FULLYR","FCAST2_VS_PREV_MTH","FCAST2_VS_PREV_QTR_QTD","FCAST2_VS_PREV_QTR_QTD_PCT","FCAST2_VS_PRMTH_YTD","FCAST_FULLYR","FCAST_VS_ACT2","FCAST_VS_ACT2_FULLYR","FCAST_VS_ACT2_YTD","FCAST_VS_ACT_CURR","FCAST_VS_ACT_CURR_PCT","FCAST_VS_ACT_FULLYR","FCAST_VS_ACT_QTD","FCAST_VS_ACT_QTD_PCT","FCAST_VS_ACT_YTD","FCAST_VS_ACT_YTD_PCT","FCAST_VS_BUD_CURR","FCAST_VS_BUD_FULLYR","FCAST_VS_BUD_YTD","FCAST_VS_FCAST2_FULLYR","FCAST_VS_LASTYR","FCAST_VS_LYR_YTD","FCAST_VS_LY_ACT_FULLYR","FCAST_VS_LY_ACT_FULLYR_PCT","FCAST_VS_PREV_MTH","FCAST_VS_PREV_QTR_QTD","FCAST_VS_PREV_QTR_QTD_PCT","FCAST_VS_PRMTH_YTD","LASTYR_ACT","LASTYR_ACT2","LASTYR_ACT2_FULLYR","LASTYR_ACT2_YTD","LASTYR_ACT_FULLYR","LASTYR_ACT_QTD","LASTYR_ACT_YTD","LASTYR_BUD","LASTYR_BUD_FULLYR","LASTYR_BUD_QTD","LASTYR_BUD_YTD","LASTYR_FCAST","LASTYR_FCAST2","LASTYR_FCAST2_QTD","LASTYR_FCAST2_YTD","LASTYR_FCAST_FULLYR","LASTYR_FCAST_QTD","LASTYR_FCAST_YTD","PREV_MTH_ACT","PREV_MTH_ACT2","PREV_MTH_ACT_YTD","PREV_MTH_BUD","PREV_MTH_BUD_YTD","PREV_MTH_FCAST","PREV_MTH_FCAST2","PREV_MTH_FCAST2_YTD","PREV_MTH_FCAST_YTD","PREV_QTR_ACT","PREV_QTR_BUD","PREV_QTR_FCAST","PREV_QTR_FCAST2","OLAP_CALC" FROM table(OLAP_TABLE ('ESADMIN.DATA_ESSATTO duration query','','','&(FR_CUBE_LIMITMAP)'))MODEL DIMENSION BY ( 	TIME, 	PRODUCT, 	GL_ACCOUNT, 	CENTRE, 	ENTITY, 	CUSTOMER, 	PROJECT, 	SPARE, 	CURRENCY, 	SCENARIO)MEASURES ( 	ACTYTD_VS_LYR_FY, 	ACT_FULLYR, 	ACT_LY_VS_ACT2, 	ACT_LY_VS_ACT2_FULLYR, 	ACT_LY_VS_ACT2_YTD, 	ACT_VS_ACT2, 	ACT_VS_ACT2_FULLYR, 	ACT_VS_ACT2_YTD, 	ACT_VS_LASTYR, 	ACT_VS_LASTYR_PCT, 	ACT_VS_LASTYR_YTD_PCT, 	ACT_VS_LYR_YTD, 	ACT_VS_PREV_MTH, 	ACT_VS_PREV_QTR_QTD, 	ACT_VS_PREV_QTR_QTD_PCT, 	ACT_VS_PRMTH_YTD, 	BUD_FULLYR, 	BUD_VS_ACT2, 	BUD_VS_ACT2_FULLYR, 	BUD_VS_ACT2_YTD, 	BUD_VS_ACT_CURR, 	BUD_VS_ACT_CURR_PCT, 	BUD_VS_ACT_FULLYR, 	BUD_VS_ACT_QTD, 	BUD_VS_ACT_QTD_PCT, 	BUD_VS_ACT_YTD, 	BUD_VS_ACT_YTD_PCT, 	BUD_VS_LASTYR, 	BUD_VS_LYR_YTD, 	BUD_VS_PREV_MTH, 	BUD_VS_PREV_QTR_QTD, 	BUD_VS_PREV_QTR_QTD_PCT, 	BUD_VS_PRMTH_YTD, 	CURR_ACT, 	CURR_ACT_QTD, 	CURR_ACT_YTD, 	CURR_BUD, 	CURR_BUD_QTD, 	CURR_BUD_YTD, 	CURR_FCAST, 	CURR_FCAST2, 	CURR_FCAST2_QTD, 	CURR_FCAST2_YTD, 	CURR_FCAST_QTD, 	CURR_FCAST_YTD, 	EXTRAPOLATION, 	FCAST2_FULLYR, 	FCAST2_VS_ACT2, 	FCAST2_VS_ACT2_YTD, 	FCAST2_VS_ACT_CURR, 	FCAST2_VS_ACT_CURR_PCT, 	FCAST2_VS_ACT_FULLYR, 	FCAST2_VS_ACT_QTD, 	FCAST2_VS_ACT_QTD_PCT, 	FCAST2_VS_ACT_YTD, 	FCAST2_VS_ACT_YTD_PCT, 	FCAST2_VS_BUD_CURR, 	FCAST2_VS_BUD_FULLYR, 	FCAST2_VS_BUD_YTD, 	FCAST2_VS_FCAST_CURR, 	FCAST2_VS_FCAST_FULLYR, 	FCAST2_VS_FCAST_YTD, 	FCAST2_VS_LASTYR, 	FCAST2_VS_LYR_YTD, 	FCAST2_VS_LY_ACT2_FULLYR, 	FCAST2_VS_LY_ACT_FULLYR, 	FCAST2_VS_LY_ACT_FULLYR_PCT, 	FCAST2_VS_LY_BUD, 	FCAST2_VS_LY_BUD_FULLYR, 	FCAST2_VS_LY_BUD_YTD, 	FCAST2_VS_LY_FCAST_FULLYR, 	FCAST2_VS_PREV_MTH, 	FCAST2_VS_PREV_QTR_QTD, 	FCAST2_VS_PREV_QTR_QTD_PCT, 	FCAST2_VS_PRMTH_YTD, 	FCAST_FULLYR, 	FCAST_VS_ACT2, 	FCAST_VS_ACT2_FULLYR, 	FCAST_VS_ACT2_YTD, 	FCAST_VS_ACT_CURR, 	FCAST_VS_ACT_CURR_PCT, 	FCAST_VS_ACT_FULLYR, 	FCAST_VS_ACT_QTD, 	FCAST_VS_ACT_QTD_PCT, 	FCAST_VS_ACT_YTD, 	FCAST_VS_ACT_YTD_PCT, 	FCAST_VS_BUD_CURR, 	FCAST_VS_BUD_FULLYR, 	FCAST_VS_BUD_YTD, 	FCAST_VS_FCAST2_FULLYR, 	FCAST_VS_LASTYR, 	FCAST_VS_LYR_YTD, 	FCAST_VS_LY_ACT_FULLYR, 	FCAST_VS_LY_ACT_FULLYR_PCT, 	FCAST_VS_PREV_MTH, 	FCAST_VS_PREV_QTR_QTD, 	FCAST_VS_PREV_QTR_QTD_PCT, 	FCAST_VS_PRMTH_YTD, 	LASTYR_ACT, 	LASTYR_ACT2, 	LASTYR_ACT2_FULLYR, 	LASTYR_ACT2_YTD, 	LASTYR_ACT_FULLYR, 	LASTYR_ACT_QTD, 	LASTYR_ACT_YTD, 	LASTYR_BUD, 	LASTYR_BUD_FULLYR, 	LASTYR_BUD_QTD, 	LASTYR_BUD_YTD, 	LASTYR_FCAST, 	LASTYR_FCAST2, 	LASTYR_FCAST2_QTD, 	LASTYR_FCAST2_YTD, 	LASTYR_FCAST_FULLYR, 	LASTYR_FCAST_QTD, 	LASTYR_FCAST_YTD, 	PREV_MTH_ACT, 	PREV_MTH_ACT2, 	PREV_MTH_ACT_YTD, 	PREV_MTH_BUD, 	PREV_MTH_BUD_YTD, 	PREV_MTH_FCAST, 	PREV_MTH_FCAST2, 	PREV_MTH_FCAST2_YTD, 	PREV_MTH_FCAST_YTD, 	PREV_QTR_ACT, 	PREV_QTR_BUD, 	PREV_QTR_FCAST, 	PREV_QTR_FCAST2, 	OLAP_CALC)  RULES UPDATE SEQUENTIAL ORDER();

[swood]

See http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=107&topicid=59437
 

Sherman

Jaspersoft