Aggregate function on a dimension table column

building semantic layer
1

I applied an aggregate function on a column from a dimension table. The join cardinality between the dim and the fact was 1:N. When I ran a query that had the aggregate object, I had to add every object that was in the filter condition(s) to the select criteria as well. If not my query would fail with a sql parsing error.

Error:Exception: DBD, [Microsoft SQL Server Native Client 10.0] : Must declare the scalar variable “@Aggregate_Aware”.State: 42000

SQL Generated was

SELECT
  min(Claim_Pay_Ctgry_Start_Dt_dim.clndr_date),
  Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end
FROM
  dbo.claim_occurrence_dim  claim_occurrence_dim INNER JOIN dbo.claim_payment_category_fact ON (claim_occurrence_dim.claim_occurrence_dim_id=dbo.claim_payment_category_fact.claim_occurrence_dim_id  AND  claim_occurrence_dim.crrnt_snpsht_flag=1)
   INNER JOIN dbo.calendar_dim  Claim_Pay_Ctgry_Start_Dt_dim ON (dbo.claim_payment_category_fact.claim_pay_ctgry_start_date_id=Claim_Pay_Ctgry_Start_Dt_dim.clndr_id)
  
WHERE
  ( 
  @Aggregate_Aware(Case when hier_claim_occurrence_dim.claim_num !='N/A' Then hier_claim_occurrence_dim.claim_num end,Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end)  IN  (SELECT
  @Aggregate_Aware(Case when hier_claim_occurrence_dim.claim_num !='N/A' Then hier_claim_occurrence_dim.claim_num end,Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end)
FROM
  dbo.policy_dim
WHERE
  ( dbo.policy_dim.crrnt_snpsht_flag=1  )
  AND  (
  ( dbo.policy_dim.pol_sym )+' '+ ( dbo.policy_dim.pol_num ) +'-'+( dbo.policy_dim.pol_mod )  =  '1'
  )
)
  )
GROUP BY
  Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end

As you can see the error is that the sql being generated by the universe is incorrect. After some toiling, I figured out that the problem was the cardinality of the join between the calendar table and the fact table. I switched it to 1:1 and the sql changed as below.

SQL Generated Was:

SELECT DISTINCT
  Min(Claim_Pay_Ctgry_Start_Dt_dim.clndr_date),
  Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end
FROM
  dbo.policy_dim INNER JOIN dbo.claim_payment_category_fact ON (dbo.policy_dim.pol_dim_id=dbo.claim_payment_category_fact.pol_dim_id  AND  dbo.policy_dim.crrnt_snpsht_flag=1)
   INNER JOIN dbo.claim_occurrence_dim  claim_occurrence_dim ON (claim_occurrence_dim.claim_occurrence_dim_id=dbo.claim_payment_category_fact.claim_occurrence_dim_id  AND  claim_occurrence_dim.crrnt_snpsht_flag=1)
   INNER JOIN dbo.calendar_dim  Claim_Pay_Ctgry_Start_Dt_dim ON (dbo.claim_payment_category_fact.claim_pay_ctgry_start_date_id=Claim_Pay_Ctgry_Start_Dt_dim.clndr_id)
  
WHERE
  ( 
  ( dbo.policy_dim.pol_sym ) +( dbo.policy_dim.pol_num )+( dbo.policy_dim.pol_mod )  =  '1'
  )
GROUP BY
  Case when claim_occurrence_dim.claim_num !='N/A' Then claim_occurrence_dim.claim_num end

I hope it helps someone as it helped me to teach a thing about cardinality within a universe. :lol:

[Moderator Edit: Added code formatting - Andreas]

aamirmoh :us: (BOB member since 2006-07-31)

2

Are you sure? As far as I know, Cardinality doesn’t affect the query syntax. To get DISTINCT added to the SELECT, you clear Retrieve duplicate rows in Permissions (in Webi).

charlie :us: (BOB member since 2002-08-20)

3

It wasn’t the distinct that I was trying to show up in my query.

aamirmoh :us: (BOB member since 2006-07-31)

4

Cardinality has nothing to do with SQL generation; it is used primarily for context detection, and for visually identifying fan and chasm traps.

What was it?

charlie :us: (BOB member since 2002-08-20)

5

I agree that a common concept is that cardinality is usually not required.
But, in this case I fixed the specific issue with modifying the cardinality of the join.

I stated what I saw, faced and resolved. :slight_smile:

aamirmoh :us: (BOB member since 2006-07-31)

6

I’m glad your issue was resolved, but, IMHO, your conclusion that changing the cardinality fixed the issue is flawed. In your own words, “after some toiling”, you changed the cardinality and decided that was the solution. The incorrect SQL generated in the 1st query seems more like corruption, and whatever you did while “toiling” smacked your universe upside the head and got it back to normal.

For instance, in your second query, there is no @Aggregate_Aware() usage at all, even though you use the same objects in the result. I’m wondering, what else did you change while “toiling”?

charlie :us: (BOB member since 2002-08-20)

7

I can take the same universe that is in production today…Locally make the change mentioned here and the issue gets resolved. Changes are yet to be deployed.

I may not agree to the abrupt behaviour about things getting fixed like magic. Maybe I don’t own a radio from 1980’s. Slam it and it starts working :slight_smile:

As for the aggregate_aware…Actually that was I guess part of the problem. Our conversation has prompted me to share a little more detail with you.

The universe has 30 dimension tables, 5 facts and over 900 objects…Out of which 700 or over are aggregate aware…I am using aggregate_awareness to force the universe to pick a default context rather than the universe prompt a user for it. So when I introduced the new objects (aggregate) on dimensions I saw the issue pop up for the first time… I checked and recheked my aggregate navigation, joins and context several times but could not resolve it.

My colleague and I , who is also a board member made the change I talk about in this post. It works…

I think you bring in some good points…Maybe we can talk more about this outside the forum.

aamirmoh :us: (BOB member since 2006-07-31)

8

I have to agree with aamirmoh. I participated in this situation and modifying the cardinality did resolve the issue.

nonyup (BOB member since 2008-01-07)

9

Change it back, see if the breaks things again. :slight_smile: That will prove out whether the cardinality change was the one and only thing that updated or not.

Dave Rathbun :us: (BOB member since 2002-06-06)

10

I changed the join cardinality to 1:1 from 1:N and the sql generated was correct. Then I changed it back to 1:N. That generated bad code. Then I changed it back to 1:1 which generated the correct code.

In simple universes maybe cardinality is not an issue but when universes get complex it does play a role in sql generation as it is evident in my case.

Thanks for your inputs: Dave and Charlie.

aamirmoh :us: (BOB member since 2006-07-31)

11

That is really interesting.

  1. What version of Designer are you running?

  2. Which client tool do you see the behavior in?

  3. Is the behavior reproducible? That is, can you create a brand new universe and see the same behavior when you have a 1:N relation, or is it restricted to the universe in question? My guess is it is only in that particular one, since otherwise you would have lots of broken code.

charlie :us: (BOB member since 2002-08-20)

12

Where did the subquery in the first piece of code come from?

You have changed the SQL generation to ANSI, by the looks of things, that will also, obviously, make a difference?
I have seen this sort of behavior before, when I was doing XIr2 beta testing. Setting cardinalities did make a difference to the code generated, see here:-

https://bobj-board.org/t/52392

The problem could also be driver related, IMO.

Mak 1 :uk: (BOB member since 2005-01-06)

13

Thanks Mak 1 for posting the link. At the bottom, “toujays” resolves this by removing the cardinality altogether. I just happened to change it to 1:1.

The subquery was generated by the code. Or is that what you were expecting when you asked "Where did the subquery in the first piece of code come from? "

My DB is Sql Server 2008 and ANSI 92 parameter is set to Yes.
Designer version is 12.1.0
Deski version 12.1.0

aamirmoh :us: (BOB member since 2006-07-31)

14

I just wanted to confirm this :).
Is this the “R2” release of SQL Server 2008?
As far as I know that is not supported in 3.1 SP3, the latest release.
You may wish, also, to check the version of the driver you are using to insure it is supported.
Are you using OLEDB or ODBC?

Mak 1 :uk: (BOB member since 2005-01-06)

15

Just curious - what have you applied in regards to SPs and FPs?

charlie :us: (BOB member since 2002-08-20)