Resolved:Distinct in Cognos Generated SQL

[mrcool]

Hi All,

I am facing a serious issue with the cognos generated SQL in Analysis Studio.

I dragged 1 dimension to row and 1 dimension to column with 'Get Data Later' enabled. When I checked the SQL in the back end it was having distinct instead of group. My data volume is too huge because of which it is taking so much because of distinct. How to force cognos to write group by?

Any help would be highly appreciated.

Cheers,
Mrcool

[blom0344]

If you use NO fact, then Cognos assumes you only want to see unique sets of values, hence the distinct clause. Adding a fact will prompt Cognos to perform an aggregate against the dimensions introducing the group by. This is all very standard behavior (at least it should be)

[mrcool]

Hi blom,

Thank you. But this problem I am seeing only with 1 particular package. I have checked same scenario with different package and I don't see this problem.
Even without fact the SQL has group by.
Please find below for the query with group by

SELECT   "Compensation"."Compensation_Plan_ID" (NAMED "Compensation_Plan_Namekey" ) ,
      "Compensation"."Compensation_Plan_Name" (NAMED "Compensation_Plan_Name0" ) ,
      "Customer"."Business_Unit_Code" (NAMED "Business_Unit_Codekey" )
FROM   ( (
SELECT   "COMP_PLAN_ELMNT_RFRNC"."Comp_Plan_Id" (NAMED "Compensation_Plan_ID" ) ,
      "COMP_PLAN_ELMNT_RFRNC"."Comp_Plan_Elmnt_Id" (NAMED "Compensation_Plan_Element_ID" ) ,
      "COMP_PLAN_RFRNC"."Comp_Plan_Name" (NAMED "Compensation_Plan_Name" )
FROM   "EDWIT1E_VW40_DLR_COMP"."COMP_PLAN_ELMNT_RFRNC" "COMP_PLAN_ELMNT_RFRNC" ,
      "EDWIT1E_VW40_DLR_COMP"."COMP_PLAN_RFRNC" "COMP_PLAN_RFRNC" ,
      "EDWIT1E_VW40_DLR_COMP"."COMP_TP_RFRNC" "COMP_TP_RFRNC" , "EDWIT1E_VW40_DLR_COMP"."COMP_CTGRY_RFRNC" "COMP_CTGRY_RFRNC"
WHERE   "COMP_PLAN_RFRNC"."Comp_Plan_Id" = "COMP_PLAN_ELMNT_RFRNC"."Comp_Plan_Id"
   AND   "COMP_TP_RFRNC"."Comp_Tp_Id" = "COMP_PLAN_ELMNT_RFRNC"."Comp_Tp_Id"
   AND   "COMP_TP_RFRNC"."Comp_Ctgry_Id" = "COMP_PLAN_ELMNT_RFRNC"."Comp_Ctgry_Id"
   AND   "COMP_TP_RFRNC"."Comp_Ctgry_Id" = "COMP_CTGRY_RFRNC"."Comp_Ctgry_Id" ) "Compensation" INNER JOIN (
SELECT   "COMP_PYBL_FACT"."Cstmr_Id" (NAMED "Customer_ID" ) , "COMP_PYBL_FACT"."Comp_Plan_Id" (NAMED "Compensation_Plan_ID" ) ,
      "COMP_PYBL_FACT"."Comp_Plan_Elmnt_Id" (NAMED "Compensation_Plan_Element_ID" ) ,
      "COMP_PYBL_FACT"."Comp_Payable_Apprd_Dt" (NAMED "c23" )
FROM   "EDWIT1E_VW40_DLR_COMP"."COMP_PAYABLE_FACT" "COMP_PYBL_FACT" ) "Compensation_Payable_Fact"
   ON   "Compensation"."Compensation_Plan_ID" = "Compensation_Payable_Fact"."Compensation_Plan_ID"
   AND   "Compensation"."Compensation_Plan_Element_ID" = "Compensation_Payable_Fact"."Compensation_Plan_Element_ID" ) LEFT OUTER JOIN (
SELECT   "CSTMR_ROU_HIST"."Cstmr_Id" (NAMED "Customer_Id" ) , "ROU_RFRNC"."BU_Cd" (NAMED "Business_Unit_Code" ) ,
      "CSTMR_ROU_HIST"."Cstmr_ROU_Start_Dt" (NAMED "Customer_ROU_Start_Date" ) ,
      "CSTMR_ROU_HIST"."Cstmr_ROU_End_Dt" (NAMED "Customer_ROU_End_Date" )
FROM   "EDWIT1E_VW40_DLR_COMP"."CSTMR_ROU_HIST" "CSTMR_ROU_HIST" ,
      "EDWIT1E_VW40_DLR_COMP"."ROU_RFRNC" "ROU_RFRNC" , "EDWIT1E_VW40_DLR_COMP"."INTGS_SGMNT" "INTGS_SGMNT"
WHERE   "ROU_RFRNC"."ROU_Id" = "CSTMR_ROU_HIST"."Cstmr_ROU_Id"
   AND   "INTGS_SGMNT"."GL_Cntr_Cd" = "ROU_RFRNC"."GL_Cntr_Cd" ) "Customer"
   ON   "Compensation_Payable_Fact"."Customer_ID" = "Customer"."Customer_Id"
   AND   "Compensation_Payable_Fact"."c23" BETWEEN "Customer"."Customer_ROU_Start_Date"
   AND   "Customer"."Customer_ROU_End_Date"
GROUP   BY "Compensation"."Compensation_Plan_ID" , "Compensation"."Compensation_Plan_Name" ,
      "Customer"."Business_Unit_Code

cheers,
Sudheer

[blom0344]

I've been working with SQL in every possible database for nearly 20 years now, but not yet seen a group by in this way. (Yes, one can add it, but that is pretty pointless)
If you do not fetch a fact in the query you normally are interested in the unique rows returned (which is why Cognos adds the distinct) That is normal behavior. Adding a group by where no fact is found can only be explained by the fact the Cognos expects you to minimally add one measure to build a crosstab in AS. What is the point in just fetching dimension members? 

[mrcool]

Hi Blom,

Yes there is no point in fetching dimension members alone but the problem is I am selecting 'Get Data Later selected' and dragging 1 dimension to row and 1 dimension to column. While dragging a second dimension to row or column because of this distinct it is taking lot of time to display the dimension values alone.

cheers,
Sudheer

[mrcool]

Hi All,

Finally I could figure out where the issue is. In calendar dimension we concatenated Month with year. After removal of this concatenation cognos started writing group by.
Thank you blom for your inputs.

cheers,
Mrcool.