Hi,

Any update on this? We are unable to proceed on this. Hence, need immediate help.

Regards
Guruprasad

Hi Guruprasad,

the steps you took seem correct, you shouldn't need to do anything else to be able to use the cache option. (I assume you successfully tested the connection to the columnar DB when you initially created, and also that when you populated the data into the columnar DB it finished successfully).

We may have to end up getting your YF DB & Datawarehouse over here to replicate the issue so the developers can connect to it and then step through the code. But before we do that could you please try caching the view into a non in-memory db datastore (i.e. a regular database) and let us know whether you can run a report from that.

Also, could you please send us the catalina.out file (Linux) or stdout.log (Windows) because unfortunately there is no stacktrace for the above null pointer which we need to investigate the correct line of source code where the null pointer has come from, however the full stack trace might be available in the catalina.out or stdout.log file.

And also please let us know which version and build you are using.

regards,
Dave

Hi Dave,

Yes. I have successfully tested the DB connection before I created the report. Also, I do not have write access to any other DBs yet as we only work with DBs with read-only option. I can try requesting one.

Meanwhile, I have attached the catalina.out and yellowfin.log at the time the error actually occurred. Hope this helps to troubleshoot further.

Thanks
Guruprasad

Hi Guruprasad,

thanks for the logs, thankfully they had more information than that null pointer error:

[code]net.sf.jsqlparser.parser.ParseException: Encountered " "CASE" "CASE "" at line 4, column 4.
Was expecting one of:
"(" ...
"*" ...
"*" ...
"(" ...
[/code]

this seems to be a very strange error to us, it looks like Yellowfin is generating incorrect report SQL.

We should be able to reproduce such an error over here if we know all of the steps you took to create the view and report. Don't forget to include important details such as is the view a freehand SQL view, and if so please show us the code? does the view contain any freehand SQL calculated fields? if so please send us the code.

Also can you please send us the report SQL and the view SQL. As well as that could you please set the logging level to DEBUG and send us the yellowfin.log file.

And also please let us know which version and build you are using.

Apologies for asking for all of this extra information but hopefully when we have it all then we'll be able to reproduce the error.

regards,
Dave

Hi David,

I have already listed the steps I followed to create the view and report. No, this is not a free-hand-SQL report. This report doesn't have any calculated fields. Infact, even a simple tabular report also doesn't work using cache option. However, the same report works perfectly fine when I disable the cache option or delete the cache data.

Below is the Report SQL:

Export SQL
SELECT DISTINCT
Sales_Transaction_View_cached_for_rolling_m_680975,
Sales_Transaction_View_cached_for_rolling_m_680811,
CASE
WHEN Sales_Transaction_View_cached_for_rolling_m_680770 IS NOT NULL THEN Sales_Transaction_View_cached_for_rolling_m_680770 * Sales_Transaction_View_cached_for_rolling_m_680760 * Sales_Transaction_View_cached_for_rolling_m_680837
ELSE Sales_Transaction_View_cached_for_rolling_m_680736 * Sales_Transaction_View_cached_for_rolling_m_680760 * Sales_Transaction_View_cached_for_rolling_m_680837
END,
Sales_Transaction_View_cached_for_rolling_m_680963,
Sales_Transaction_View_cached_for_rolling_m_680951,
SUM(Sales_Transaction_View_cached_for_rolling_m_680837)
FROM YFTT690055
WHERE (
Sales_Transaction_View_cached_for_rolling_m_680954 IN ('2014')
)


Report SQL:
SELECT
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC2` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICE` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC2` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
END,
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC3` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICE` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC3` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
END,
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC4` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICE` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC4` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
END,
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC5` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICE` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC5` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`
END,
`FACT_SALES_NO_AGG`.`UNITPRICE` * `FACT_SALES_NO_AGG_UDF`.`UDFDEC1` * `FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`,
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFDEC10`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICE`
END,
CASE
WHEN `FACT_SALES_NO_AGG_UDF`.`UDFVCHAR9` IS NOT NULL THEN `FACT_SALES_NO_AGG_UDF`.`UDFVCHAR9`
ELSE `FACT_SALES_NO_AGG`.`SKULISTPRICECURRENCYCODE`
END,
`DIM_TIME`.`YEARNAME`,
`DIM_TIME`.`QUARTERNAME`,
`DIM_TIME`.`MONTHNAME`,
`DIM_TIME`.`WEEKNAME`,
`DIM_TIME`.`YEARNUMBER`,
`DIM_TIME`.`QUARTERNUMBER`,
`DIM_TIME`.`MONTHNUMBER`,
`DIM_TIME`.`WEEKNUMBER`,
`DIM_TIME`.`DATEVAL`,
`FACT_SALES_NO_AGG`.`INVOICEDATE`,
`DIM_TIME`.`FISCALSTARTDATE`,
`DIM_TIME`.`FISCALENDDATE`,
`MDM_PARTNER_MASTER`.`REGION`,
`MDM_PARTNER_MASTER`.`SUB_REGION`,
`MDM_PARTNER_MASTER`.`COUNTRY`,
`MDM_PARTNER_MASTER`.`CLIENT_PARTNER_CODE`,
`MDM_PARTNER_MASTER`.`TIER`,
`MDM_PARTNER_MASTER`.`PARTNER_CLASS_LEVEL1`,
`MDM_PARTNER_MASTER`.`PARTNER_TYPE_NAME`,
`MDM_PARTNER_MASTER`.`PARTNER_NAME`,
`MDM_PARTNER_MASTER`.`PARTNER_CLASS_LEVEL2`,
`MDM_PARTNER_MASTER`.`PARTNER_CLASS_LEVEL3`,
`MDM_PARTNER_MASTER`.`PARTNER_CLASS_LEVEL4`,
`MDM_PARTNER_MASTER`.`LOCATION_NAME`,
`MDM_PARTNER_MASTER`.`ADDRESS_LINE1`,
`MDM_PARTNER_MASTER`.`ADDRESS_LINE2`,
`MDM_PARTNER_MASTER`.`ZIP_CODE`,
`MDM_PARTNER_MASTER`.`CITY`,
`MDM_PARTNER_MASTER`.`STATE`,
`MDM_PARTNER_MASTER`.`POS_FREQUENCY`,
`MDM_PARTNER_MASTER`.`INV_FREQUENCY`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`MASTER_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_NAME`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_ADDRESS_1`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_ADDRESS_2`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_CITY`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_STATE`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_POSTAL_CODE`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_COUNTRY`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`COUNTRY_ISO2_CODE`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`COUNTRY_NAME_STD`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`REGION`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`SUB_REGION`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`GROUP_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`VAT_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`COMPANY_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`COMPANY_NAME`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYNAME`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYADDRESS1`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYADDRESS2`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYCITY`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYSTATE`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYPOSTALCODE`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYCOUNTRYCODE`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYPHONE`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYTYPE`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYEMAIL`,
`FACT_SALES_NO_AGG`.`PROMOTIONTYPE`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CREATED_ON`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`MASTER_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_NAME`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_ADDRESS_1`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_ADDRESS_2`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_CITY`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_STATE`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_POSTAL_CODE`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_COUNTRY`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`COUNTRY_ISO2_CODE`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`COUNTRY_NAME_STD`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`REGION`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`SUB_REGION`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`GROUP_ID`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYNAME`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYADDRESS1`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYADDRESS2`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYCITY`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYSTATE`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYPOSTALCODE`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYCOUNTRYCODE`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYEMAIL`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYTYPE`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYPHONE`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`COMPANY_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`COMPANY_NAME`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`VAT_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CREATED_ON`,
`FACT_SALES_NO_AGG`.`SOURCETRANSACTIONID`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYVENDORPARTYID`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYVENDORPARTYID`,
`FACT_SALES_NO_AGG`.`SKULISTPRICE3`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR4`,
`FACT_SALES_NO_AGG`.`SALESTERRITORYNAME`,
`FACT_SALES_NO_AGG`.`RESALEINVOICENUMBER`,
`FACT_SALES_NO_AGG`.`QUANTITYSHIPPED`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR1`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR2`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR3`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYLEGALID`,
`FACT_SALES_NO_AGG`.`SALEORDERNUMBER`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR24`,
`MDM_SKU_MASTER`.`SKU_DESCRIPTION`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD4`,
`MDM_SKU_MASTER`.`PRODUCT_LINE`,
`MDM_SKU_MASTER`.`PRODUCT_GROUP`,
`MDM_SKU_MASTER`.`PRODUCT_FAMILY`,
`MDM_SKU_MASTER`.`SKU_CODE`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD1`,
`MDM_SKU_MASTER`.`PRODUCT_TYPE`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD2`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD3`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD5`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD6`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD7`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD8`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD9`,
`MDM_SKU_MASTER`.`USERDEFINEDFIELD10`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR10`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR11`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR12`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR13`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR14`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR15`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR16`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR17`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR18`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR19`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR20`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR21`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR22`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR23`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`APC_REWARDS_PROGRAM`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`APJ_JP_TOP_ACCOUNTS`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CHANNEL`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CHANNEL_PARTNER_PROGRAM`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CISCO_VAR`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_CATALOG`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_CATALOG_GROUPS`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_CERTIFIED`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_EVENT_CCF`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_MDF`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_ONLINE_PARTNER`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_PREMIUM_ONLINE_PARTNER`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EMEA_RETAIL_BUYING_GROUP`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`EVERYTHING_CHANNEL_FSM`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`NAMED_ACCOUNT_TERRITORY`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`OEM_NAMED_GROUP`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`PARTNER_ADVANTAGE_NUMBER`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`PELCO_PARTNER_ADV`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`PELCO_REPORTER_2_REPORTER`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`REPORTER_2_REPORTER`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`RETAILER_NAMED_GROUPS`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`CUSTOMER_GLOBAL_ACCOUNT`,
`MDM_CUSTOMER_MASTER_CUSTOMER`.`SUBCHANNEL`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_ID`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`APC_REWARDS_PROGRAM`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`APJ_JP_TOP_ACCOUNTS`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CHANNEL`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CHANNEL_PARTNER_PROGRAM`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CISCO_VAR`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_CATALOG`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_CATALOG_GROUPS`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_CERTIFIED`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_EVENT_CCF`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_MDF`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_ONLINE_PARTNER`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_PREMIUM_ONLINE_PARTNER`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EMEA_RETAIL_BUYING_GROUP`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`EVERYTHING_CHANNEL_FSM`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`NAMED_ACCOUNT_TERRITORY`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`OEM_NAMED_GROUP`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`PARTNER_ADVANTAGE_NUMBER`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`PELCO_PARTNER_ADV`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`PELCO_REPORTER_2_REPORTER`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`REPORTER_2_REPORTER`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`RETAILER_NAMED_GROUPS`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`CUSTOMER_GLOBAL_ACCOUNT`,
`MDM_CUSTOMER_MASTER_CUSTOMER 1`.`SUBCHANNEL`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_SOLUTION_ID`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_PRODUCT_CODE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_PRODUCT_DESCRIPTION`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_INVOICE_QUANTITY`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_UNIT_PRICE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_CURRENCY_CODE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_TRANSACTION_DATE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_LOB`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_PRODUCT_DIVISION`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_PRODUCT_FAMILY`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_EXTENDED_USD`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_EXTENDED_EUR`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_EXTENDED_CNY`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_EXTENDED_JPY`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_USD_EXCHANGE_RATE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_JPY_EXCHANGE_RATE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_EUR_EXCHANGE_RATE`,
`MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_CNY_EXCHANGE_RATE`,
`FACT_SALES_NO_AGG`.`SELLTHRUPOLINENUMBER`,
`FACT_SALES_NO_AGG`.`RESALEINVOICELINENUMBER`,
`FACT_SALES_NO_AGG`.`SKULISTPRICE`,
`FACT_SALES_NO_AGG`.`SKULISTPRICECURRENCYCODE`,
`FACT_SALES_NO_AGG`.`SKULISTPRICE2`,
`FACT_SALES_NO_AGG`.`SKULISTPRICE2CURRENCYCODE`,
`FACT_SALES_NO_AGG`.`UNITPRICE`,
`FACT_SALES_NO_AGG`.`UNITPRICECURRENCYCODE`,
`FACT_SALES_NO_AGG`.`PURCHASEPRICE`,
`FACT_SALES_NO_AGG`.`PURCHASEPRICECURRENCYCODE`,
`FACT_SALES_NO_AGG`.`PURCHASEPRICE`,
`FACT_SALES_NO_AGG`.`PURCHASEPRICECURRENCYCODE`,
`FACT_SALES_NO_AGG`.`QUOTENUMBER`,
`FACT_SALES_NO_AGG`.`VENDORPRODUCTNUMBER`,
`FACT_SALES_NO_AGG`.`VENDORPRODUCTDESCRIPTION`,
`FACT_SALES_NO_AGG`.`PRODUCTUPCCODE`,
`FACT_SALES_NO_AGG`.`GTINNUMBER`,
`FACT_SALES_NO_AGG`.`EANNUMBER`,
`FACT_SALES_NO_AGG`.`SERIALNUMBER`,
`FACT_SALES_NO_AGG`.`SERVICECONTRACTNUMBER`,
`FACT_SALES_NO_AGG`.`DISTICLAIMREFERENCENUMBER`,
`FACT_SALES_NO_AGG`.`SELLTHRUPONUMBER`,
`FACT_SALES_NO_AGG`.`SALESREPCODE`,
`FACT_SALES_NO_AGG`.`DESIGNDEALREGISTRATIONNUMBER`,
`FACT_SALES_NO_AGG`.`SOLDTOPARTYDISTIPARTYID`,
`FACT_SALES_NO_AGG`.`SHIPTOPARTYDISTIPARTYID`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYVENDORPARTYID`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYNAME`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYADDRESS1`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYADDRESS2`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYCITY`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYSTATE`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYPOSTALCODE`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYCOUNTRYCODE`,
`FACT_SALES_NO_AGG`.`BILLTOPARTYPHONE`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYNAME`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYADDRESS1`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYCOUNTRYCODE`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYEMAIL`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYPHONE`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERTYPE`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYDISTIPARTYID`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYVENDORPARTYID`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYADDRESS2`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCID`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYNAME`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYCITY`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYSTATE`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYPOSTALCODE`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYCOUNTRYCODE`,
`FACT_SALES_NO_AGG`.`SALESTERRITORYCODE`,
`FACT_SALES_NO_AGG_UDF`.`UDFINT1`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC1`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC2`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC3`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC4`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC5`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC10`,
`FACT_SALES_NO_AGG_UDF`.`UDFDATE1`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR9`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR25`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR26`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR27`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR28`,
`FACT_SALES_NO_AGG`.`PROMOTIONCODE`,
`FACT_SALES_NO_AGG`.`PROMOTIONDESCRIPTION`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYLEGALID`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYCITY`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYSTATE`,
`FACT_SALES_NO_AGG`.`ENDCUSTOMERPARTYPOSTALCODE`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYADDRESS1`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYADDRESS2`,
`FACT_SALES_NO_AGG`.`SOLDFROMLOCPARTYEMAIL`,
`FACT_SALES_NO_AGG_UDF`.`UDFDEC6`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR7`,
`FACT_SALES_NO_AGG_UDF`.`UDFVCHAR8`
FROM `FACT_SALES_NO_AGG_UDF`
INNER JOIN `MDM_SKU_MASTER`
ON (
`FACT_SALES_NO_AGG_UDF`.`UDFINT9` = `MDM_SKU_MASTER`.`SKU_ID`
)
INNER JOIN `MDM_PARTNER_MASTER`
ON (
`FACT_SALES_NO_AGG_UDF`.`UDFINT10` = `MDM_PARTNER_MASTER`.`PARTNER_ID`
)
INNER JOIN `FACT_SALES_NO_AGG`
ON (
`FACT_SALES_NO_AGG`.`TXID` = `FACT_SALES_NO_AGG_UDF`.`TXID`
)
INNER JOIN `DIM_TIME`
ON (
`FACT_SALES_NO_AGG`.`DIMTXTIMEID` = `DIM_TIME`.`DIMTIMEID`
)
AND (
`DIM_TIME`.`DATEVAL` BETWEEN '2013-09-15' AND '2014-09-17'
)
INNER JOIN `MDM_CUSTOMER_MASTER_CUSTOMER` AS `MDM_CUSTOMER_MASTER_CUSTOMER 1`
ON (
`FACT_SALES_NO_AGG`.`SHIPTOPARTYDISTIPARTYID` = `MDM_CUSTOMER_MASTER_CUSTOMER 1`.`MASTER_ID`
)
INNER JOIN `MDM_CUSTOMER_MASTER_CUSTOMER`
ON (
`FACT_SALES_NO_AGG`.`SOLDTOPARTYDISTIPARTYID` = `MDM_CUSTOMER_MASTER_CUSTOMER`.`MASTER_ID`
)
LEFT OUTER JOIN `MDM_CTO_BREAK_OUT_CUSTOMER`
ON (
`MDM_SKU_MASTER`.`SKU_CODE` = `MDM_CTO_BREAK_OUT_CUSTOMER`.`CTO_SOLUTION_ID`
)


Please let me know as how I can enable the logging level to DEBUG.

We are using YF 7.1 build which was released end of Aug-14

Please let me know what more information you need.

Thanks
Guruprasad

Hi Guruprasad,

please read this forum post for information on how to enable the logging level to DEBUG.

Also, I actually asked whether the view (not the report) was freehand SQL or had any freehand SQL calculated fields, however I can tell from the above code that the view is not freehands SQL. And the view does contain calculated fields although they are not freehand SQL ones, can you please confirm both of these points.

I await the DEBUG log. If the log doesn't throw further light on the issue then we may have to get your YF repository and datawarehouse over here so that we can recreate the issue. Do you think that will be possible?

regards,
Dave

Hi Dave,

Sorry to get back late on this. I have attached the DEBUG logs. However, I noticed that after the App restart, this started working.

But, if I Populate the data to the cache again and then then try, this keeps failing.

It looks like the App is unable to read cache data from the memory unless the App is restarted.

We can reproduce this issue consistently with the same scenarios.

Let me know if you have any questions.

Best Regards
Guru

Hi Guru,

thanks for the debug log files, this time we're in luck as there is a full strace for that null pointer exception:

[code]
YF:2014-09-17 06:38:00:ERROR (DBAction:) - Error occurred when connecting to the database: java.lang.NullPointerException
java.lang.NullPointerException
at com.hof.pool.DBConnectionManager.A(DBConnectionManager.java:440)
at com.hof.pool.DBConnectionManager.getConnection(DBConnectionManager.java:265)
at com.hof.util.DBAction.(DBAction.java:333)
at com.hof.mi.process.MIAdminProcess.runSourceFilterQuery(MIAdminProcess.java:1920)
at com.hof.mi.process.MIAdminProcess.runSourceFilter(MIAdminProcess.java:1768)
at com.hof.mi.servlet.SourceFilterRefreshTask.runTask(SourceFilterRefreshTask.java:431)
at com.hof.mi.servlet.TaskScheduler$_A.A(TaskScheduler.java:106)
at com.hof.mi.servlet.TaskScheduler$_A.run(TaskScheduler.java:153)
at java.lang.Thread.run(Thread.java:619)
[/code]

however, we will not be able to investigate this until you tell us which version and build of yellowfin you are using. This is because the line numbers change from build to build when code is added or removed.

regards,
Dave

Hi David,

It looks like you missed my earlier comment.

"We are using YF 7.1 build which was released end of Aug-14"

This issue has been around for quite some time. As the feature itself is not working, what priority would you assign to this issue?

We need a quick resolution on this one.

Thanks
Guruprasad

Hi,

Any update on this? It has been close to 2 weeks since I raised this issue. This is a severity 1 issue, I would appreciate a quicker response.

Thanks
Guru

Hi Guru,

apologies for missing your earlier comment about the version and build, I think it was because it was under that big block of code. A developer has looked through the code that threw the Null Pointer Exception and unfortunately cannot say exactly where the null pointer is coming from (there are a few possibilities).

However, the stack trace does show that the issue is something to do with source filters, so I set up a view with a source filter over here and cached it into the In-Memory database and then ran a report that uses the source-filtered column and the report didn't work. Then I ran the same report against live data and it ran successfully. Another test I did was to remove the source filter from the report, then run it aginst the In-Memory cached data and in this scenario the report also worked correctly.

So I have raised a product defect (Support Task 173867) for this bug where source filters aren't working for In-Memory DB cached data, and have added this support task to the agenda for the next developers' meeting on Monday for allocation and resolution.

thank you for your patience,
Dave

Hi David,

Thanks for acknowledging the issue. As you see, we have already lost significant time on this one. Hence, I am wondering how soon we can get the fix for this.

This is a severity 1 issue as the In-Memory DB is not working with Report Filters.

Thanks
Guru

Hi Guru,

I have made sure the Support Task 173867 has been marked as HIGH priority. I can tell you it will definitely be fixed for the next patch which is at the end of October. If you would prefer an interim patch as soon as the developer has fixed the issue rather than waiting for the end of October then please let us know, although please keep in mind that interim patches are not subject to the normal QA process.

regards,
Dave

Hi Guruprasad,

a developer has investigated this issue, and has discovered that the reason my report wouldn't run against the in-memory cached view but would run against the live data is because the data-type of the column that the source filter is based on is bpchar(n) which is a PostGres version of char(n). When the data is not cached then when Yellowfin compares a value against the column the column is trimmed and the values can be matched, however when the data is cached then column isn't trimmed and therefore doesn't match.

Could you please tell us what the data-type of the report's source filter(s) is. I am hoping this is the same reason your report isn't running against the cached data, the developer is investigating the best resolution for this issue at the moment.


regards,
Dave

Hi Dhrati,

oh no, this is bad new indeed! That means that when I thought I had reproduced the issue you were experiencing I was incorrect - I had found a different issue. The important thing now is that we must be able to reproduced the same issue as yours over here so that the developers can analyse it, is there any chance you'll be able to upload your Yellowfin repository and Data Warehouse to our FTP site?

regards,
Dave

Dave,

Unfortunately, we will not be able to provide you the DWH and the YellowFin DB due to security reasons.

Let me know if there are any alternatives for you to debug this issue. We can send you all the log files if that helps.

We are able to consistently reproduce this issue in our environment with the same steps. Hence, I doubt if this is just a environment issue. Are you not able to reproduce with the above steps with your own DB? Also, as I mentioned before once I populate the data to the cache and then restart YellowFin App it works fine. But, it starts failing upon next refresh.

Hence, I think the App is unable to read the data from the cache dynamically unless it is restarted.

Let me know if you need any additional information.

Thanks
Guru

Hi Guruprasad,

that's bad luck about not being able to upload the databases, that would have made things easier. Unfortunately when I try to reproduce this issue on my installation of Yellowfin 7.1 it just works. (I have emailed you a video of it working).

What database are you using for the data warehouse? What version of Java?

regards,
Dave

Hi Dave,

We have tried using Cache view option and found the SQL Syntex Error for the reports which involve calculated fields having case statements.

Below is the calculation we are using for one of the field-

CASE WHEN INVOICE_PRICE IS NOT NULL THEN INVOICE_PRICE * USD_EXCHANGE_RATE * INVOICE_QUANTITY ELSE LIST_PRICE * USD_EXCHANGE_RATE * INVOICE_QUANTITY END

If we do not include this calculated field report gives the result.

So we assume the problem with the calculated fields only which involve case statements, for basic calculations report works fine.

Can you please look into the issue on priority basis.

Regards,
Dhrati

Hi Dhrati,

well this one was very easy to reproduce over here so I have raised a product defect (Support Task 180109), marked it as HIGH priority and have added it to the agenda for next Monday's developers' meeting where it will get allocated to a developer and subsequently investigated and fixed.

Apologies for the inconvenience caused.


regards,
Dave

Hi Dhrati,

I've checked the status of the support task and somehow it hasn't been started yet. Unfortunately the developers are on leave at the moment, however when they return to work in early January I will find out what's going on with this task.

Apologies for the delay.

regards,
Dave

Hi Dhrati,

I've checked the status of this support task and it is still OPEN, the priority is definitely marked HIGH so I guess the reason it hasn't been done yet is due to time lost over the christmas break.

Sorry I didn't have better news for you, I'll certainly follow this up to see what's happening.

regards,
Dave

Hi Dhrati,

I'm sorry but the support task still hasn't been allocated to a developer, I'll do what I can to try and expedite things.

Apologies for the delay in fixing this bug.

regards,
Dave

Hi Dhrati,

I will be bringing up this item again at the next dev meeting.

Regards,
David

Hi David,

Hope your doing good. I was reviewing this thread related to cache issues, any update on this?

Regards
Shashi

Hi Shashi,

So sorry this has slipped under the radar.
Unfortunately cached views are still to be looked at as a whole, I do know these are extremely difficult to fix which is why it can stay open for such a long time :(

Apologies on this!

Regards,
David