PDA

View Full Version : Data constraint in Physical Table Properties



afutschik
10-27-2009, 12:33 PM
Hy !

I am working on Pentaho Metadata Editor 3.5 CE. I am facing a problem regarding Data Constraints properties.

I am trying to add a column to calculate the number of rows from an other column(COL2) with a specific data constraint(on COL1). (COL3)

I therefore :
1°) set Data constraints in the new column to :
PT_TABLE.COL1='R' (Where, PT_TABLE is the ID of the Physical Column and COL1, the ID of the Column I am trying to filter)

2°) set Default Aggregation type to :
Count

3°) set Formula to :
COL2

When using the new calculated field in Business VIEW, the data constraint is not applied to the MQL/SQL query. The result is the same as without setting 1°)

Here is an extract of the MQL generated :

<mql>
<domain_type>relational</domain_type>
<domain_id>DOMAIN</domain_id>
<model_id>MODEL1</model_id>
<model_name>My Model</model_name>
<options>
<disable_distinct>false</disable_distinct>
</options>
<selections>
<selection>
<view>my View</view>
<column>BC_TABLE_COL3</column>
<aggregation>count_distinct</aggregation>
</selection>
</selections>
<constraints/>
<orders/>
</mql>
SELECT
COUNT(DISTINCT BT_TABLE_TABLE.COL2) AS COL3
FROM
TABLE BT_TABLE_TABLE

What am I missing ?

Thanks in advance,

Adrien

wgorman
10-30-2009, 09:23 PM
Hi Adrien,

Data Constraints only apply to Business Models, and are used for row level security. Defining them on an individual physical column will have no effect. If you define a data constraint on a model, that constraint is incorporated into every query executed by the system.

To apply a simple global constraint on a column, your best bet at the moment is to define a view that encapsulates that behavior in your database. You could also file a JIRA case at http://jira.pentaho.com/browse/PMD for this functionality.

Thanks!

Will

afutschik
10-31-2009, 02:24 PM
Thanks for the reply. I have filed-in a JIRA case regarding this functionality.

Adrien