How to change rpd metric

ForSly

I am trying to get a count of some active flag for an event  at the rpd level, currently  I am using SUM as the default aggregation rule, but I wanted to use something like this (

SELECT DISTINCT JOB_NAME, INT_ID, A_CAN,O_NAME, P_NUM FROM WC_XYZ

GROUP BY JOB_NAME, INT_ID, A_CAN,O_NAME, P_NUM

)  the reason Is that the source code has changed and  modified the grain of the fact table so in order  to get a correct count which I am able to do at database level  I need to have something like this in the rpd. So that it minimizes my changes to original rpd. any suggestions are welcome.

Gianni Ceresa

Didn't you miss something in your "sample" query? There isn't any aggregation, not sure why you even have a group by there as it's just a bunch of attributes and the distinct return you unique combinations only.

With the provided info I really don't see the link between your mesure with a SUM aggregation and the sample query.

ForSly

Let me back up a little bit to explain whats happening : Originally  This (A_CAN) is the flag that is using the sum ,  changed the fact table to capture some additional information  and by doing that causes this flag to have repeating values,   to get the count correctly and eliminate duplicate I had  done two steps  - 1 ran this code

CREATE TABLE  T1 AS  SELECT DISTINCT JOB_NAME, INT_ID, A_CAN,O_NAME, P_NUM FROM WC_XYZ

GROUP BY JOB_NAME, INT_ID, A_CAN,O_NAME, P_NUM

  against the new table (THE modified fact table) then  ran a sum  of the above as shown below SELECT SUM( A_CAN)  FROM  T1. This gives me the correct count. I just don't know how I could do the same at the rpd ?

Thomas Dodds

A_CAN is what in terms of data ... you say count but aggregate with a sum -- that's inconsistent and confusing.With your SQL example ... selecting distinct with a group by of the same is redundant ... in any case ... A_CAN doesn't seem to match the new grain -- thus you actually are driving at having 2 fact tables (2 grains).  Just because you can achieve what you want in SQL (which in effect is 2 tables) -- you have to approach the modeling in a totally different mindset than SQL.  Your table changed - thus your model must as well to match ... your easiest route is to create your new grained object as close to the physical DB as possible.

USE of information drives data model design.

Were risks to the model from this change ever discussed, documented, vetted and approved?

ForSly

A_CAN is a flag, its either 1  or 0.   so the sum basically give me the sum of 1's in a region or by job.  and Yes the risk to the model  discussed and approved by management

Thomas Dodds

So why was the grain of the rows changed if the fact was already additive?  a new fact table at a different grain should have been built.  OBIEE logically could have handled it and the change would have been much easier to implement and the end users largely unaffected by the incoming change.

I'd argue that it's not a flag - it's a row indicator ... and now it isn't either due to the change in the grain.

[Deleted User]

Thomas Dodds wrote:So why was the grain of the rows changed if the fact was already additive? a new fact table at a different grain should have been built. OBIEE logically could have handled it and the change would have been much easier to implement and the end users largely unaffected by the incoming change.I'd argue that it's not a flag - it's a row indicator ... and now it isn't either due to the change in the grain.

Amen. OP - listen to this guy.

ForSly

The grin changed because I added some additional columns to capture   information that was not available when the original table was created.  Basically  when I added the new column  it causes the  above flag to repeat itself in multiple rows since the column I am adding has multiple rows.

Thomas Dodds

I understand that ... but ramming it into the original table is what has caused you the issue.  The original grain is still valid AND separate from the new grain - hence a new physical structure is needed.  Logically you can put it back together and present it as if it were all coupled nicely (thanks to the capabilities of OBIEE).