XSUM Issue

[Tony]

Hi All,
I had created a simple model in Framework manager using below two tables
F1 fact table and D1 dimension table
Join: C1.F1=C1.D1
Query Item:Sales Revenue from F1 and State Name from D1.

WhenI ran the Query in Report Studio, below Cognos Sql is generated:

select
       D1.State_Name  as  State_Name,
       XSUM(F1.Sales_Revenue )  as  Sales Revenue,
       XSUM(XSUM(F1.Sales_Revenue  )  at XSUM(F1.Sales_Revenue ) )  as  Total_Sales_Revenue
from
       F1,D1
where
       (F1.C1 = D1.C1)
group by
       D1.C1[/font]
I am getting Cartesian product on report.
My Question is why Cognos is generating extra sql, XSUM(XSUM(F1.Sales_Revenue  )  at XSUM(F1.Sales_Revenue ) )  as  Total_Sales_Revenue? Any idea how to fixe this issue?

[Tony]

Hi All,
I had created a simple model in Framework manager using below two tables
F1 fact table and D1 dimension table
Join: C1.F1=C1.D1
Query Item:Sales Revenue from F1 and State Name from D1.

WhenI ran the Query in Report Studio, below Cognos Sql is generated:

select
       D1.State_Name  as  State_Name,
       XSUM(F1.Sales_Revenue )  as  Sales Revenue,
       XSUM(XSUM(F1.Sales_Revenue  )  at XSUM(F1.Sales_Revenue ) )  as  Total_Sales_Revenue
from
       F1,D1
where
       (F1.C1 = D1.C1)
group by
       D1.C1
I am getting Cartesian product on report.
My Question is why Cognos is generating extra sql, XSUM(XSUM(F1.Sales_Revenue  )  at XSUM(F1.Sales_Revenue ) )  as  Total_Sales_Revenue? Any idea how to fixe this issue?

[MFGF]

Hi Tony,

Query Studio automatically groups and summarises values in the report, and adds summary footings too.

"Group by D1.C1" is presumably grouping on State Name,
XSUM(F1.Sales_Revenue) is totalling the Revenue value for each State Name in the list,
XSUM(XSUM(F1.Sales_Revenue) is totalling the Revenue value for the overall footing at the bottom of the report.

I don't see how this is creating a cartesian product, though?  Can you explain what you mean?

Regards,

MF.

[Tony]

Hi Tony,

Query Studio automatically groups and summarises values in the report, and adds summary footings too.

"Group by D1.C1" is presumably grouping on State Name,
XSUM(F1.Sales_Revenue) is totalling the Revenue value for each State Name in the list,
XSUM(XSUM(F1.Sales_Revenue) is totalling the Revenue value for the overall footing at the bottom of the report.

I don't see how this is creating a cartesian product, though?  Can you explain what you mean?

Regards,

MF.

Hi MF,
Thanks for your reply. Here is the problem:
I am getting 'Grand Total sales Revenue' for each state value. Actually this is wrong:
Here are my expected values for each country:
USA = $100M
CANADA= $200M
ENGLAND=$300M
Grand Total=$600M

Report Studio display data as below:

USA=$600M
CANADA=$600M
ENGLAND=$600M
Grand Total=$600M

I am not sure why data is displaying like above. Any idea?

[MFGF]

Sounds like you have an issue with the package your report is based on.  If I were a betting man, I'd put my money on the problem being missing or incorrectly defined determinants.  Second choice would be a "blind spot" in the model and/or a spurious relationship.

Do you have access to the underlying model in Framework Manager?  I'm convinced this is where the issue lies.

Regards,

MF.

[Tony]

Sounds like you have an issue with the package your report is based on.  If I were a betting man, I'd put my money on the problem being missing or incorrectly defined determinants.  Second choice would be a "blind spot" in the model and/or a spurious relationship.

Do you have access to the underlying model in Framework Manager?  I'm convinced this is where the issue lies.

Regards,

MF.

Yes. I have access to the model. Actually I am the one who designed it. Please let me know where it went wrong.

[jfitzgerald]

Group the state column

[MFGF]

Yes. I have access to the model. Actually I am the one who designed it. Please let me know where it went wrong.

Sorry - I missed this reply.

I can't say for definite how your model is wrong without knowing more about how things are modelled currently - just a gut feel as above that it's probably incorrect or missing determinants, or a blind spot causing incorrect aggregation.

A good place to start is to launch Model Advisor, and see what gets reported.

What query subjects are being used behind the scenes, and how are they related (in particular the cardinalities)?
What determinants are currently defined?

Sorry - loads of questions!

MF.

[Sep2013]

Hi all,

I am having the same issue.

Did we get a solution for this one?

[blom0344]

A 2 table model with just one fact and one dimension table should work , even with no determinants set.   The 'at' in the SQL should point I think to a faulty set determinant.  Check what is set at the tables