PDA

View Full Version : Calculated column not working in Pentaho 3.5



pthoutam
11-09-2009, 05:44 PM
Hi, I have created few Calculated columns and deployed the metadata ad-hoc model onto Server, this works fine in 3.0 version of Pentaho Enterprise. When I tried deployed the same ad-hoc model onto new 3.5 the calculated column in not working and instead its just grouping by. IS this a known issue or if somebody knew how to fix this please let me know.

Thanks,

Praneeth

wgorman
11-10-2009, 12:11 AM
Hi Praneeth,

Could you provide an example XMI file or describe the formula? There were a couple of subtle changes from 3.0 to 3.5 related to formulas but it should still all be backwards compatible.

Thanks,

Will

pthoutam
11-10-2009, 11:05 AM
Thanks for your reply! Basically I am new to this tool. So correct me if I am wrong.

The formula is pretty basic. The table schema is like this

ProductName RequestCount RequestDate

Apple 3 2009-10-01
Apple 4 2009-10-02
Apple 6 2009-10-09

I have created SUM(RequestCount) column in the ad-hoc model. So When in create an adhoc report in the user console, I can select product name and total requests for a certain time range. This works pretty fine in 3.0, but when I try the same in 3.5, it is grouping on product name but not doing the sum. So, in this instance, when I ask total request Count, it returns 1 . Makes Sense?

wgorman
11-10-2009, 11:14 AM
Do you have Aggregation Type set to SUM? If you provide me with your XMI file, I can debug it more fully.

Thanks!

Will

pthoutam
11-10-2009, 11:44 AM
Yes - I did set the aggregation type to SUM, its working in 3.0. Here's the xmi file. I actually gave an example scenario in the previous post. Here in this xmi file please find the business model Price requests.

wgorman
11-10-2009, 12:02 PM
I took a look at your model. The physical column PC_Total_Price_Requests should have a formula that looks like "[price_request_cnt]" versus "price_request_cnt". You should see a parsing error in the log that looks like this:

org.pentaho.pms.core.exception.PentahoMetadataException: SqlOpenFormula.ERROR_0005 - Failed to parse formula price_request_cnt

The current behavior of Pentaho Metadata is to pass the exact formula string down into SQL if the formula parsing failed. That is why you are seeing the field without the aggregation. Now you should see SQL that looks like this:

SUM( BT_PRICE_REQUESTS_PRICE_REQUESTS.price_request_cnt ) AS COL0

Hope that helps!

Will

pthoutam
11-10-2009, 12:19 PM
Hello,

I had changed the formula, but still the sql query shows up the same way.

SELECT
BT_DOMAINS_DOMAINS.domain_name AS COL0
, BT_PRICE_REQUESTS_PRICE_REQUESTS.price_request_cnt AS COL1
FROM
DOMAINS BT_DOMAINS_DOMAINS
,PRICE_REQUESTS BT_PRICE_REQUESTS_PRICE_REQUESTS
WHERE
( BT_PRICE_REQUESTS_PRICE_REQUESTS.domain_id = BT_DOMAINS_DOMAINS.domain_id )
AND
(
BT_DOMAINS_DOMAINS.domain_name = 'indianrailway.com'
)
GROUP BY
BT_DOMAINS_DOMAINS.domain_name

Any idea why ?

wgorman
11-10-2009, 12:31 PM
Using the model you provided, I get the following SQL output in Metadata Editor:

SELECT
BT_DOMAINS_DOMAINS.domain_name AS COL0
,SUM( BT_PRICE_REQUESTS_PRICE_REQUESTS.price_request_cnt ) AS COL1
FROM
DOMAINS BT_DOMAINS_DOMAINS
,PRICE_REQUESTS BT_PRICE_REQUESTS_PRICE_REQUESTS
WHERE
( BT_PRICE_REQUESTS_PRICE_REQUESTS.domain_id = BT_DOMAINS_DOMAINS.domain_id )
GROUP BY
BT_DOMAINS_DOMAINS.domain_name


Where are you seeing the SQL generation problem? Maybe you are deploying to an older server?

Will

pthoutam
11-10-2009, 01:42 PM
Hi,

Instead of going back n forth, I have attached 4 screenshots. The first one in the doc is where I created the aggregate column in the way you asked "[price_request_cnt]" and when I select the formula exact I am seeing a different query and when I un-select it I see a different query. In both cases when I deploy the model to 3.0 it works fine, but 3.5 doesn't. It also throws me an error if I do not select the formula exact.

I am using Pentaho MetaData Editor 3.0.0 version just in case if you need it.

Thank you so much for your help.

wgorman
11-10-2009, 01:58 PM
I recommend using Pentaho Metadata Editor 3.5, Pentaho Metadata Editor 3.0's query editor won't add the aggregation unless it is explicitly specified.

In 3.0, in order to use aggregations, the syntax of the formula should be:
SUM([price_request_cnt])

In 3.5 we support multiple aggregation types in the MQL Editor, so your formula should not explicitly specify the sum:
[price_request_cnt]

3.5 should still work with the older formula expression that references the SUM function, but those legacy models won't allow aggregation selection in the MQL Editor until the funciton is removed.

Will

pthoutam
11-10-2009, 03:02 PM
I downloaded and tried with editor 3.5 and it works. Thanks for your help.

Praneeth