Hi All,
I have a question regarding the universe integrity check for IDT 
.
Any measure object when it doesn’t have any aggregation function (sum, average, count…etc) set, it does not pass the integrity check. But seems like the report runs fine.
Could someone please help me understand if its fine to have a measure object without any aggregation function?
Thanks!!
Nivi s (BOB member since 2012-05-11)
Hi,
You must have a very good reason to do that. Do you have one? 
Measures have to be set with the aggregation function. Otherwise the generated SQL would not have the GROUP BY clause and a query could bring potentially thousands if not millions detailed records into a report…
Marek Chladny 
(BOB member since 2003-11-27)
Technicaly it can be done but isnt wise to do so, you’re not taking advantage of the additional aggregation.(as Marek says)
I did use it one time but then it was more an administrative trickery to let it look like measures but not act like it.
The intergrety check isnt always 100% ok, doens’t have to be.
If you use a field with sysdate for instance the check will report it but your report-sql works fine with it.
IngeH 
(BOB member since 2004-06-22)
Thanks for your thoughts.
The reason I was asking this question was, I have a typical scenario.
I have a requirement where the users need to filter on measure objects. For example I am looking for the total count of members who had sales>5000 in a particular month.
In this case I need to filter on a measure. But I know we cannot use aggregated measures as filter as the sql counts the members first, groups by period and then uses HAVING sales>5000 condition which results in wrong counts.
I understand that using variable at report level is a good workthrough. But I cant use this option for 2 reasons
I dont see any other options except for removing the aggregate function and just have the projection function.
Please let me know if you have any suggestions experts. 
:?
Nivi s (BOB member since 2012-05-11)
Can you not use ETL logic to persist data in the fashion you like or employ derived tables? This is a suggestion based on my initial understanding of your requirement!
BOB_DW (BOB member since 2004-08-26)
What version of BO are you using?
In 4.0 you can make a condition apply on univers, maybe that will do the trick. [Object]> 4000, and the [object] is sum(…)
Otherwise you need to make a derived table that looks at this for you but you will loose out in the performance i think.
Can you post you defintion of the object, maybe we’re missing something there.
IngeH (BOB member since 2004-06-22)
Please see my post about aggregaton, projection, and the proper definition of measures: diff between Select SUM (Table. Field) & Select Table. F
Andreas 
(BOB member since 2002-06-20)
@BOB_DW
Yes, I could do that. But the problem is the logic is not persistent for example the user might try to filter for sales>5000 or >10000 or> 5500, it is a very flexible scenario so I dont think I can use a ETL Logic or derived table in this case.
@IngeH
Yes, I am using version 4.0. Can you please elaborate? would it be applicable in a scenario like above where my condition is not constant.
@Andreas
Thanks for the link Andreas. I went through the post. I understand the difference between using aggregation and projection function.
But in my scenario I cant using a aggregate function because my users are trying to analyse various scenarios using measure for example sales> 10000 or productsold> 5000 and many other scenarios.
I understand we cant use a measure with aggregate function in filter because BO first GROUPS BY and then uses HAVING to filter the condition which results in wrong counts,
select count(Guest_ids), period_id from facttable
group by period_id having sum(sales) > 5000
I have a requirement where I need to give the users the flexibilty to use any measure as a condition (example, sales> 10000 or productsold> 5000 and many other scenarios). The only way I see i could do it is to remove aggregation function and use projection function alone in which case i select count(Guest_ids), period_id from facttable where sales > 5000.
Please help me what is the best way I could crank this out…
I appreciate everyone’s time and suggestions. Thanks for the support!
Nivi s (BOB member since 2012-05-11)
Hi,
Keep the measures with aggregation and projection functions as they are.
Then for every measure which you need ot use in a condition create a new dimension object that will point to the exactly same table column as its measure. Give the dimension a meaningful name and in its properties, in the Advance tab, set that it will be possible to be used only in conditions. Then educate users what’s the difference between a measure and its dimensional “counterparty” and how to use them in report queries.
Marek Chladny (BOB member since 2003-11-27)
Thanks Marek…I think that would work good for one universe as I do not have too many measures…But my other universe has almost 300 measures… 
Nivi s (BOB member since 2012-05-11)
Consider:
SELECT
Year, Month, Person, Sum(SalesValue)
FROM
DenormalisedSalesFact -- to stop the purists jumping on the surrogate key bandwagon!
WHERE
Year = 2012
GROUP BY
Year, Month, Person
HAVING
Sum(SalesValue) > 5000This brings back all rows where there is a monthly sales for a person greater than 5000. It’s not saying total sales for the month > 5000 across all people.
Then you would have to create that many dimensions if you wanted to use this approach.
Marek Chladny (BOB member since 2003-11-27)
Marek,
As suggested I build the measures as dimensions with conditions only.
Just a quick clarification, the dimension object does not pass the integrity check when I use the measure as dimension with condition only…please let me know if it is fine.?
Appreciate all your suggestions and input! 
Nivi s (BOB member since 2012-05-11)
Hi,
Can you please give us more details of how you defined the dimensions? What did you use for their select part of the definitions? Did you use also the where parts?
The thing is that the dimensions should parse.
Marek Chladny (BOB member since 2003-11-27)