How to group a dimension in Universe

[Former Member]

Hi,

I have requirement where i need to a dimension to be grouped in Universe.So that dimensions can be used as a query filter when doing Adhoc reporting.

For Example

We have a "Age" Dimension" that needs to be grouped as 0-5,6-10,11-18 so that when a users selects age dimension in Query filter it should display the grouped values.

Thanks.

[mhmohammed · Accepted solution]

Hi Kalyan,

Create a Dimension object in the universe with below SQL, and use that in the result objects.

Case

When Age between 0 and 5 then '0-5'

When Age between 6 and 10 then '6-10'

When Age between 11 and 18 then '11-18'

Else '>18'

End

Question: Your question is not clear, do you want to use this object as filter as well? or just for grouping?

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Hi Mahboob,

Thanks for your reply.Apart from grouping i want to dispaly them as filter as well.

For Example.

When we select that dimension object as query filter in Webi Report and click on List of Values(LOV's) they should see 0-5,6-10,10-15 like that. And if they select 0-5 people from age 0 to 5 needs to be filtered.

Thanks.

[Former Member · Accepted solution]

Just apply a filter on the dimension that Mahboob suggested you create then.

[Former Member · Accepted solution]

Thanks a lot Mahboob and Mark it worked as expected.

I have a question i heard that if we use CASE statement in 'where' clause as right now i using in the filter condition in the report that  it will have poor performance degrade on the query. Is that true?

Can you please provide your inputs on this.

[Former Member · Accepted solution]

Using anything in the where clause is generally bad practice as it will be applied across the query rather than to the specific object. Say you wanted year to date sales. In pseudo code terms (that will vary depending upon your database), instead of:

SELECT CLAUSE:

sum(sales_fact.sales_value)

WHERE CLAUSE:

year(sales_dates.calendar_date)=year(current_date)

You are better writing

SELECT CLAUSE:

sum(CASE WHEN year(sales_dates.calendar_date)=year(current_date) THEN sales_fact.salues_value END)

and leaving the where clause empty.

Otherwise, if you create a YTD Last Year object on the same lines, the two date filters will cancel each other out in the overall SQL's where clause and leave you with no rows returned.

If that doesn't clear it up for you, provide your example and we can discuss that.

Regards,

Mark

[Former Member · Accepted solution]

Hello Mark,

Thanks for your reply i will do some testing on it and let u know.

Thanks

[Former Member · Accepted solution]

Thanks Mahboob.

[Former Member]

Hello Kalyan!

I think that understand what you need. Try the follow:

Create an object in your business layer using the CASE clause:

Object: Age Range

Sintax in select clause:

case when FACT.AGE between 0 and 5 then '0 - 5' when FACT.AGE between 6 and 10 then '6 - 10' when FACT.AGE between 11 and 18 then '11 - 18' end

That's the solution?

Hope it helps.

Regards,

Arthur

[Former Member]

Hi Arthur,

Thanks for the reply. I have already applied this logic and it working. There was some delay in replying it.

Thanks.

[Former Member]

Sorry, didn't saw that the response was there.

Regards.