Having Clause Filter Object in Universe

building semantic layer
1

Hello All :slight_smile:

I was looking to implement a Filter Object in the universe that would appear as a HAVING CLAUSE CONDITION in the SQL that gets generated for the Query.

I tried creating a regular filter object with condition code as:

modify_date = max(modify_date)

In a manually generated SQL, such a condition would appear as:


SELECT       DIM1, DIM2, SUM(MEASURE1)
FROM         TABLE1
GROUP BY     DIM1, DIM2, modify_date
HAVING       modify_date = max(modify_date)

However, by default, all the filter objects in the BO universe get generated as a WHERE condition :frowning: (or in the FROM if set so!)
* do correct me if I am wrong!

Is there any way to ensure that filter objects on aggregated measure values get generated as a HAVING CLAUSE?

Many Thanks in advance!! :slight_smile:
Best Regards,
Rig

rig (BOB member since 2010-10-22)

2

rig,

SELECT       DIM1, DIM2, SUM(MEASURE1) 
FROM         TABLE1 
GROUP BY     DIM1, DIM2, modify_date 
HAVING       modify_date = max(modify_date)

This does not work, not even in (manually) generated SQL :wah:

You can create a predefined condition like

X.modify_date =
	(
	SELECT	MAX(Y.modify_date)
	FROM	TABLE Y
	WHERE	Y.DIM1 = X.DIM1
	AND	Y.DIM2 = X.DIM2
	)

or

X.modify_date =
	(
	SELECT	MAX(Y.modify_date)
	FROM	TABLE Y
	)

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

3

Hi Johan,

Thank u very much for your response! I apologise for the confusing query! (In an attempt to simplify and generalise SQL generated by my query panel I messed it up :oops: )

this time I would give an example of the famous EMP table in Oracle DBMS.
what i want is:


SELECT DEPT, SUM(SAL)
FROM EMP
GROUP BY DEPT
HAVING SUM(SAL) > 1000

Thus, all I wish is, to list only those DEPT with SUM(SAL) > 1000

I tired applying the sub-query condition that you have suggested, but it didn’t give me the results that I expect. Well, I understand that I confused you with my requirements so perhaps that solution may need a bit of a twist to achieve what I actually want!

Similarly, in my reports, I wish to implement the having condition at Universe level to restrict the amount of data that is fetched into the report.

I understand that it can be done at the report level, but being fairly new to the universe designing environment, I could not figure out how can I get this HAVING CLAUSE in the Universe. If I create a simple filter object like
sum(sal) > 1000 it goes in the where condition and the Query Panel pops a SQL error

* the max(modify_date) example that I mentioned previously may not sound similar to what I explain here, I chose a bad example indeed :frowning:
but essentially this is what I wish to achieve…a simple HAVING CONDITION on the AGGREGATED MEASURE at UNIVERSE level

Thanks for your response once again!
Best Regards,
Rig

rig (BOB member since 2010-10-22)

4

rig,

Your predefined condition could look like

X.DEPT IN 
	(
	SELECT Y.DEPT
	FROM EMP Y
	GROUP BY Y.DEPT 
	HAVING SUM(Y.SAL) > 1000
	)

Sorry, that is not possible at universe-level (at the moment).
I think that is not possible because a ‘having’ is always related to a ‘group by’ and a ‘group by’ depends on the objects (dimensions & details) that you pull in your query… :blue:
Maybe someone has a better explanation ? :roll_eyes:

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

5

Thanks a ton Johan!! :smiley:

This filter object condition would perfectly help to achieve what I need!
I will have to create several filter objects depending upon the different combination of Dimensions that are required in the GROUP BY of the Sub-Query.

I got your point…surely thats the reason for it not being there yet!

I just wished that BO gave a kind of smart checkbox for the filter object to mark it as an aggregate filter condition and then while generating the SQL it would put the condition in the HAVING statement. The DEFAULT GROUP BY that gets generated with the Query would be the default scope for this HAVING clause as well! :stuck_out_tongue:
but anyway, that may be just a little too much of wishful thinking…i appreciate its always easier said than done! :slight_smile:

Thanks once again!
Best Regards,
Rig

rig (BOB member since 2010-10-22)

6

My situation is more complex…

Imagine a very simplified Universe with two Calendar Tables (D1 & D2), and four fact tables (F1,F2,F3 & F4). Now, because of the fact tables, there are four Contexts (each Context including joins to both Calendar tables.

I have created a ‘Months Difference’ Object which is defined as:

cast (((D1.LAST_DAY_OF_MONTH-D2.LAST_DAY_OF_MONTH)  Month(4)) as integer)

Because this is needed for grouping, it is defined as a Dimension, and it returns values between -10 and +10. So far, so good, everything works. However, for many reports, the business wants the ‘Months Difference’ restricted to values between 0 and 5. If this is added as a restriction in the WHERE clause, I get a stack overflow error message (Teradata). Even though it doesn’t actually include any aggregation in its definition, the ‘Months Difference’ restriction needs to be added to the HAVING clause to get it to work.

I can obviously do this with freehand SQL, but the Johan’s solution mentioned earlier in the thread will not work, because the definition needed would depend on the Context being used at the time of running the query.

I can’t see a way around this, and think that my only solution is to create report based filters rather than a pre-defined Universe Condition.

Any other suggestions?

anorak :uk: (BOB member since 2002-09-13)

7

Just a thought, not tried it though.

Create a max/sum object that you want to report against
Create a subquery:

DEPT, AGGOBJECT
WHERE
AGGOBJECT >1000

Where Dept in the main query is filtered against DEPT in the subquery or even in a separate query. Saves managing universe changes.

8

Sorry Mark, but I do not understand your solution…

My problem Dimension includes a date difference calculation in its definition. I want to force the following restriction to appear as a HAVING clause not a WHERE clause, but I cannot find any way of doing this. I think that the only solution is to use report filters instead, or get additional calculated columns added to the underlying database tables.

( cast (((BO_EDW_VIEWS.P_CMC_MONTHLY_CALENDAR.LAST_DAY_OF_MONTH-BO_EDW_VIEWS.P_CMC_CALENDAR.LAST_DAY_OF_MONTH)  Month(4)) as integer) ) between 0 and 5

anorak :uk: (BOB member since 2002-09-13)

9

Sorry Anorak, I was proposing an alternative for the OP, not you. I’ll have a look at yours later.

10

couldn’t you do a derived table, which contains a having clause with this condition and uses @prompt functionality to achieve what you want?

erik.stenson :us: (BOB member since 2012-07-30)