Hi all,
i’ve a simple construct in Universe and WebI.
My universe contains only one table with some dimensions and some measures which are set to Sum. If i now create a report in WebI i can see that the measure of the local amount is doubled.
It couldn’t be a chasm or fan trap because i only have single table.
Do you have an idea what could be the reason?
Thanks in advance for your helps!
barthodo 
(BOB member since 2012-04-18)
Are there duplicate rows?
Nick Daniels 
(BOB member since 2002-08-15)
no, there are no duplicate rows…
from the training i can remember that sometimes it could be that there are doubled value and i must set a parameter in universe or a setting in WebI to avoid that…but cannot remember which parameter it was… 
barthodo (BOB member since 2012-04-18)
Try, on the query properties, uncheck the retrieving duplicated lines.
rgoulart 
(BOB member since 2011-08-21)
Perfect…
I’ve unchecked retreiving duplicate lines, and now i get the correct result.
But i don’t understand this…´For example:
i have a customer with 10 unique line items and 10 measures, if i now make a report: customer and measure
i get the doubled result when i don’t deactive this setting…
can someone explain?
barthodo (BOB member since 2012-04-18)
In the query, can you click on the SQL button and post it here. Can you indicate which column is doubling in value?
jemstar 
(BOB member since 2006-03-30)
i think i found the issue…
in the universe when i create a measure, there is a “select”-box in the properties…
in our training we’ve learned, that it’s very helpful to set for every projection function sum also the select box to sum(field).
the course was on xi 4.0,
at the moment i’m developing in xi 3.1
if i do that i get doubled results. if i remove this i get the expected figures…but that means, that i cannot break down the aggreation function to DB. It must collect every single data set and do the summation on project function…which is slower…
With sum around the measure in Universe (delivers wrong figures):
SELECT DISTINCT
CASH_REPORTING.ARE,
CASH_REPORTING.AUSGLEICHSDATUM,
CASH_REPORTING."BUCHUNGSPERIODE AUGBL",
CASH_REPORTING."Buchungsperiode Rechnung",
CASH_REPORTING.BELEGNUMMER,
CASH_REPORTING.KLASSIFIZIERUNG,
CASH_REPORTING.BUCHUNGSZEILE,
CASH_REPORTING.GJ,
CASH_REPORTING.SACHKONTO,
CASH_REPORTING.SAKONTOBEZEICHUNG,
CASH_REPORTING.LIEFERANT,
CASH_REPORTING.LIEFERANTENNAME,
CASH_REPORTING."Soll/Haben",
sum(CASH_REPORTING.BETRAG),
CASH_REPORTING."BELEGWÄHRUNG",
sum(CASH_REPORTING.SKONTO),
sum(CASH_REPORTING."Betrag Hauswährung"),
CASH_REPORTING."HAUSWÄHRUNG",
sum(CASH_REPORTING.BETRAG_STEUER),
CASH_REPORTING.EINKAUFSBELEG,
CASH_REPORTING.MEHRERE_EINKAUFSBELEGE,
CASH_REPORTING.KOSTENSTELLE,
CASH_REPORTING."GESCHÄFTSBEREICH",
CASH_REPORTING.PROFITCENTER,
CASH_REPORTING.AUSGLEICHSBELEG,
CASH_REPORTING.VERANTWORTUNG_EUP
FROM
CASH_REPORTING
WHERE
CASH_REPORTING.ARE IN ( '426K' )
GROUP BY
CASH_REPORTING.ARE,
CASH_REPORTING.AUSGLEICHSDATUM,
CASH_REPORTING."BUCHUNGSPERIODE AUGBL",
CASH_REPORTING."Buchungsperiode Rechnung",
CASH_REPORTING.BELEGNUMMER,
CASH_REPORTING.KLASSIFIZIERUNG,
CASH_REPORTING.BUCHUNGSZEILE,
CASH_REPORTING.GJ,
CASH_REPORTING.SACHKONTO,
CASH_REPORTING.SAKONTOBEZEICHUNG,
CASH_REPORTING.LIEFERANT,
CASH_REPORTING.LIEFERANTENNAME,
CASH_REPORTING."Soll/Haben",
CASH_REPORTING."BELEGWÄHRUNG",
CASH_REPORTING."HAUSWÄHRUNG",
CASH_REPORTING.EINKAUFSBELEG,
CASH_REPORTING.MEHRERE_EINKAUFSBELEGE,
CASH_REPORTING.KOSTENSTELLE,
CASH_REPORTING."GESCHÄFTSBEREICH",
CASH_REPORTING.PROFITCENTER,
CASH_REPORTING.AUSGLEICHSBELEG,
CASH_REPORTING.VERANTWORTUNG_EUPwithout sum around the measure (delivers correct figures but slow due to high amount of data set transfer!) but aggreation is done on project function:
SELECT DISTINCT
CASH_REPORTING.ARE,
CASH_REPORTING.AUSGLEICHSDATUM,
CASH_REPORTING."BUCHUNGSPERIODE AUGBL",
CASH_REPORTING."Buchungsperiode Rechnung",
CASH_REPORTING.BELEGNUMMER,
CASH_REPORTING.KLASSIFIZIERUNG,
CASH_REPORTING.BUCHUNGSZEILE,
CASH_REPORTING.GJ,
CASH_REPORTING.SACHKONTO,
CASH_REPORTING.SAKONTOBEZEICHUNG,
CASH_REPORTING.LIEFERANT,
CASH_REPORTING.LIEFERANTENNAME,
CASH_REPORTING."Soll/Haben",
CASH_REPORTING.BETRAG,
CASH_REPORTING."BELEGWÄHRUNG",
CASH_REPORTING.SKONTO,
CASH_REPORTING."Betrag Hauswährung",
CASH_REPORTING."HAUSWÄHRUNG",
CASH_REPORTING.BETRAG_STEUER,
CASH_REPORTING.EINKAUFSBELEG,
CASH_REPORTING.MEHRERE_EINKAUFSBELEGE,
CASH_REPORTING.KOSTENSTELLE,
CASH_REPORTING."GESCHÄFTSBEREICH",
CASH_REPORTING.PROFITCENTER,
CASH_REPORTING.AUSGLEICHSBELEG,
CASH_REPORTING.VERANTWORTUNG_EUP
FROM
CASH_REPORTING
WHERE
CASH_REPORTING.ARE IN ( '426K' )[Moderator Edit: Added code formatting - Andreas]
barthodo (BOB member since 2012-04-18)
Can you isolate a single measure in a simple query and see if the measure doubles? Use the sum() for the measure and confirm if that the qualification of the object is a measure and the projection is a sum
Also confirm that none of the objects in your query is qualified as s Mesaure without the sum() in the select?
jemstar (BOB member since 2006-03-30)
Hi,
i will try this on weekend or monday 
afterwards i will give feedback regarding this…
barthodo (BOB member since 2012-04-18)
It would also be of use to know a little about your data set. I notice you have a distinct at the top of you Select statement?
Some “measures” should not be additive, e.g. price.
Looks to me that there is actually a problem with differring granularities in your table, but I would need to know more…
Mak 1 (BOB member since 2005-01-06)
Simply put the database modeling of your one fact table is faulty or the data loaded or ETL process is faulty.
Could it be that the problem is caused by the Hauswährung (standardized currency e.g. €)und Belegwährung columns (original currency of receipt)? Thereby doubling your values?
Obviously you have duplicate rows for an event/fact otherwise you would not need a DISTINCT function.
Regarding what you have learned about proper measures I can only urge you to read my post here and bookmark it ,-).
Andreas (BOB member since 2002-06-20)