Calculated aggregation not working as expected

[Bark]

Hi guys,

I am building a model in FM and I came across some weird behaviour regarding the calculated regular aggregation, it is ignoring it and doing a distinct instead.

I have the following:

Code Select Expand


DATABASE VIEW (i.e. SQL Query subjects)
FACT
M1 (Sum)
M2 (Sum)
M3 = M1/M2 (calculated)
   


If I do a test on M3, with auto sum selected, I get the following query:

Code Select Expand


WITH FACT0 AS
  (SELECT FACT.M1 AS M1,
    FACT.M2 AS M2,
    FACT.M1 / NULLIF(FACT.M2, 0) AS M3
  FROM FACT FACT
  )
SELECT DISTINCT FACT0.M3 AS M3
FROM FACT


Same happens if I create a query subject off the SQL query subject and do the calculation there, I still get a distinct. Also if I do the calculation in the new query subject but from itself:

Code Select Expand


DATABASE VIEW (i.e. SQL Query subjects)
FACT
M1 (Sum)
M2 (Sum)
M3 = M1/M2 (calculated)
LOGICAL VIEW
LOGIC_FACT
M1 = FACT.M1 (Sum)
M2 = FACT.M2 (Sum)
M3 = FACT.M1/FACT.M2 (Calculated)
M4 = LOGIC_FACT.M1/LOGIC_FACT.M2 (Calculated)


In the case above, I get distinct in both M3 and M4.

Then I read here something from 2005 (http://www.cognoise.com/index.php/topic,345.msg1443.html#msg1443) where it says there is a bug and it works with calculated measures so I went ahead and did it. I found that if the calculated measure is off the database layer, it doesn't work (and I get the same distinct); however if it is from the logical the it works and I get the following query:

Code Select Expand


WITH FACT0 AS
  (SELECT SUM(FACT.M1) / NULLIF(SUM(FACT.M2), 0) AS M3
  FROM FACT FACT
  )
SELECT FACT0.M3 AS M3
FROM FACT


Which is what I wanted... The problem is that if I then add this calculated measure to a query subject, I go back to getting the distinct.

I have around 7 facts with 20 ratios each and creating standalone measures seems a bit of a workaround to me (and also I am not sure it would usable when adding them to query studio).

Anyone has faced the same issue? Any ideas on how to make the calculated aggregation type work as expected? I mean, first sum and then divide.

Thanks and regards,

Bark

[cognostechie]

Hi

What you discovered is true and to the best of my knowledge, the standalone calculations are the only way to do any calculations which involve
a division operator. It definitely works well in Query Studio and yes, it is not something that can become part of a query subject so I agree that
it kind of makes the Business Layer less intuitive.  The standalone calculations were introduced to calculate the results of measures from
different query subjects but they also have to be used for any division because the query items do not work well when you set the property to 'calculated'.

[Bark]

Thanks cognostechie,

That's what I feared and what I've been finding online so far. However, this is not what you learn in the courses, is it? I am not that used to relational models (used to work much more with cubes) but I think I never faced this issue before, when doing simple divisions it was working fine... or maybe it is in RS where it works, not sure any more.

Any way, thanks for your input and let's see if someone else have some other ideas :) In the meantime I guess I would need to start implementing it with calculated measures.

Thanks and regards,

Bark