If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Resolved:Distinct in Cognos Generated SQL

Started by mrcool, 31 Aug 2010 08:45:02 PM

Previous topic - Next topic

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.