Ugly SQL generating with Lookup and FACT joins for homogenous Key 1

[Rambo2292468]

please see the query below :
select a11.EMPLID WFI_PERSON_ID,
max(a16.WFI_PERSON_DESC) WFI_PERSON_DESC,
a11.VC_AWARD_DT VC_AWARD_DT,
a11.IN_GUIDE_CNT CustCol_5,
max( CASE WHEN a11.IN_GUIDE_CNT =1 THEN 'Y' WHEN a11.IN_GUIDE_CNT =0 THEN 'N' ELSE 'UNKNOWN' END) CustCol_17,
a11.PER_DIM PER_DIM,
max(a11.PER_DIM) PER_DIM0,
a12.WFI_FLSA_ID WFI_FLSA_ID,
max(a12.WFI_FLSA_DESC) WFI_FLSA_DESC,
a14.WFI_FDPTLVL3_ID WFI_FDPTLVL3_ID,
max(a14.WFI_FDPTLVL3_DESC) WFI_FDPTLVL3_DESC,
a11.DEPTID WFI_ORG_ID,
max(a18.WFI_ORG_DESC) WFI_ORG_DESC,
a11.IN_COMPANY IN_COMPANY,
max(a17.IN_COMPANY_DESCR) IN_COMPANY_DESCR,
a13.IN_GRT_CD IN_GRT_CD,
max(a13.IN_GRT_CD_DESC_S) IN_GRT_CD_DESC_L,
a13.IN_STOCK_PLAN IN_STOCK_PLAN,
a13.ST_OPTION_TYPE ST_OPTION_TYPE,
max(a13.IN_OPT_TYPE_DESC_S) IN_GRT_CD_DESC_L0,
a13.IN_VEST_SCHED_ID IN_VEST_SCHED_ID,
max(a13.IN_VEST_DESC_S) IN_GRT_CD_DESC_L1,
a11.REHIRE_DT REHIRE_DT,
a11.COUNTRY COUNTRY,
max(a15.IN_COUNTRY_DESCR) IN_COUNTRY_DESCR,
a11.GRADE GRADE,
sum(a11.IN_STK_GUIDE_AMT) WJXBFS1,
sum(a11.IN_GRANT_CNT) WJXBFS2,
sum(a11.IN_STK_TOTAL_AMT) WJXBFS3
from PS_IN_STOCK_FACT a11
join PS_WFI_JCDATTR_DIM a12
on (a11.JOBCODE = a12.WFI_JOBCODE_ID and
a11.PER_DIM = a12.DMT_EFF_ENDDT)
join PS_IN_VC_PLAN_DIM a13
on (a11.PER_DIM = a13.DMT_EFF_ENDDT and
a11.VC_PLAN_ID = a13.VC_PLAN_ID and
a12.DMT_EFF_ENDDT = a13.DMT_EFF_ENDDT)
join PS_IN_FDPT_DIM a14
on (a11.ACCT_CD = a14.WFI_FDPT_ID and
a11.PER_DIM = a14.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a14.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a14.DMT_EFF_ENDDT)
join PS_IN_COUNTRY_DIM a15
on (a11.COUNTRY = a15.COUNTRY and
a11.PER_DIM = a15.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a15.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a15.DMT_EFF_ENDDT)
join PS_WFI_PERSON_DIM a16
on (a11.EMPLID = a16.WFI_PERSON_ID and
a11.PER_DIM = a16.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a16.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a16.DMT_EFF_ENDDT)
join PS_IN_MA_CMPY_DIM a17
on (a11.IN_COMPANY = a17.IN_COMPANY and
a11.PER_DIM = a17.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a17.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a17.DMT_EFF_ENDDT)
join PS_WFI_ORG_DIM a18
on (a11.DEPTID = a18.WFI_ORG_ID and
a11.PER_DIM = a18.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a18.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a18.DMT_EFF_ENDDT)
where (a11.PER_DIM in (CONVERT(datetime, '2003-10-25 00:00:00', 120))
and CASE WHEN a11.GRADE in ('01','02','03','04','05','06') THEN '1 to 6' WHEN a11.GRADE in ('07','08','09') THEN '7 to 9' WHEN a11.GRADE in ('10','11','12','13') THEN '10 to 13' WHEN a11.GRADE in ('14','15','16','17','18','19','20') THEN '14+ (14-20)' WHEN a11.GRADE in ('70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89') THEN 'Sales (70-89)' ELSE 'Non-Exempt' END in ('1 to 6')
and a11.IN_GUIDE_CNT = 1)
group by a11.EMPLID,
a11.VC_AWARD_DT,
a11.IN_GUIDE_CNT,
a11.PER_DIM,
a12.WFI_FLSA_ID,
a14.WFI_FDPTLVL3_ID,
a11.DEPTID,
a11.IN_COMPANY,
a13.IN_GRT_CD,
a13.IN_STOCK_PLAN,
a13.ST_OPTION_TYPE,
a13.IN_VEST_SCHED_ID,
a11.REHIRE_DT,
a11.COUNTRY,
a11.GRADE


Result: if see above All DIM tables (having Homogenous column name) getting Join On each other Instead

individual DIM should have Join only with FACT and not with each other ?

Query: How can we achieve to avoid join between DIM and have Join only On FACT and Each DIM used in report

PS: While defining attribute I have defined hetrogenous key and also checked all required DIM wherevere the Key is used in DIM

 

[entaroadun]

Please help us help you.

Bold or italicize the area you are talking about. Distill the problem down and talk in broad principles instead of providing a long SQL statement with almost no context.

It is unfair to expect anyone to go through your long SQL statement for free.

 

[chael]

rambo - have you adjusted any of the VLDB settings?

Your join mess probably exists because your dimensions are modeled incorrectly against the fact table. I agree with entaroadun -- I have no stinkin' clue what you are really trying to resolve.

Why don't you break the problem down (couple of attributes, couple of metrics on a simplified report), ensure that your data model is correct, play with various VLDB settings, and then try us again.

One other note: have you modeled in your attributes as facts???

Chael

 

[entaroadun]

chael is right. I have no stinkin' clue what the issue is. My gut tells me that there's some logical table mapping issues though. Start by looking at the logical table map that MSTR generates.

 

[Rambo2292468]

Hi Chael and entradun Thansk for prompt reply

About Marking the issues as bold I wanted to but as I am new in using tek-Tip site , dont know how to Bold the Text .

About datamodel we have as follows:

One Fact IN_CUST_FACT Table where We have date Field as Period.
We have multiple DIM table for each Id and description based on Period .

for eg :
1. One DIM as IN_JOB_DIM with Job_Id and DATE (which is hetrogenous key period with Fact)
2.Another DIM as IN_PLAN_DIM with Plan_ID and DATE (which is hetrogenous key Period with Fact)

Problem:My Problem is instead of having join with IN_JOB_DIM and IN_CUSt_FACT
My Query is making some extra join as follows:

IN_CUST_FACT.Period=IN_JOB_DIM and
IN_JOB_DIM .DATE = IN_PLAN_DIM.DATE and
IN_JOB_DIM .DATE = another DIM table .DATE

I hope I am clear now it i sreally important for me resolve this issue anything you need more from my side let me know .

Note:
1. I have not done any VLDB setting .
2.All DIM has DATE column name and FACT has Period which is a hetrogenous relationship


select a11.EMPLID WFI_PERSON_ID,
max(a16.WFI_PERSON_DESC) WFI_PERSON_DESC,
a11.VC_AWARD_DT VC_AWARD_DT,
a11.IN_GUIDE_CNT CustCol_5,
max( CASE WHEN a11.IN_GUIDE_CNT =1 THEN 'Y' WHEN a11.IN_GUIDE_CNT =0 THEN 'N' ELSE 'UNKNOWN' END) CustCol_17,
a11.PER_DIM PER_DIM,
max(a11.PER_DIM) PER_DIM0,
a12.WFI_FLSA_ID WFI_FLSA_ID,
max(a12.WFI_FLSA_DESC) WFI_FLSA_DESC,
a14.WFI_FDPTLVL3_ID WFI_FDPTLVL3_ID,
max(a14.WFI_FDPTLVL3_DESC) WFI_FDPTLVL3_DESC,
a11.DEPTID WFI_ORG_ID,
max(a18.WFI_ORG_DESC) WFI_ORG_DESC,
a11.IN_COMPANY IN_COMPANY,
max(a17.IN_COMPANY_DESCR) IN_COMPANY_DESCR,
a13.IN_GRT_CD IN_GRT_CD,
max(a13.IN_GRT_CD_DESC_S) IN_GRT_CD_DESC_L,
a13.IN_STOCK_PLAN IN_STOCK_PLAN,
a13.ST_OPTION_TYPE ST_OPTION_TYPE,
max(a13.IN_OPT_TYPE_DESC_S) IN_GRT_CD_DESC_L0,
a13.IN_VEST_SCHED_ID IN_VEST_SCHED_ID,
max(a13.IN_VEST_DESC_S) IN_GRT_CD_DESC_L1,
a11.REHIRE_DT REHIRE_DT,
a11.COUNTRY COUNTRY,
max(a15.IN_COUNTRY_DESCR) IN_COUNTRY_DESCR,
a11.GRADE GRADE,
sum(a11.IN_STK_GUIDE_AMT) WJXBFS1,
sum(a11.IN_GRANT_CNT) WJXBFS2,
sum(a11.IN_STK_TOTAL_AMT) WJXBFS3
from PS_IN_STOCK_FACT a11
join PS_WFI_JCDATTR_DIM a12
on (a11.JOBCODE = a12.WFI_JOBCODE_ID and
a11.PER_DIM = a12.DMT_EFF_ENDDT) CORRECT join PS_IN_VC_PLAN_DIM a13
on (a11.PER_DIM = a13.DMT_EFF_ENDDT and Correct
a11.VC_PLAN_ID = a13.VC_PLAN_ID and
a12.DMT_EFF_ENDDT = a13.DMT_EFF_ENDDT)
join PS_IN_FDPT_DIM a14
on (a11.ACCT_CD = a14.WFI_FDPT_ID and
a11.PER_DIM = a14.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a14.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a14.DMT_EFF_ENDDT) WRONG
join PS_IN_COUNTRY_DIM a15
on (a11.COUNTRY = a15.COUNTRY and
a11.PER_DIM = a15.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a15.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a15.DMT_EFF_ENDDT) WRONG
join PS_WFI_PERSON_DIM a16
on (a11.EMPLID = a16.WFI_PERSON_ID and
a11.PER_DIM = a16.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a16.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a16.DMT_EFF_ENDDT)
join PS_IN_MA_CMPY_DIM a17
on (a11.IN_COMPANY = a17.IN_COMPANY and
a11.PER_DIM = a17.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a17.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a17.DMT_EFF_ENDDT)
join PS_WFI_ORG_DIM a18
on (a11.DEPTID = a18.WFI_ORG_ID and
a11.PER_DIM = a18.DMT_EFF_ENDDT and
a12.DMT_BU_ID = a18.DMT_BU_ID and
a12.DMT_EFF_ENDDT = a18.DMT_EFF_ENDDT)
where (a11.PER_DIM in (CONVERT(datetime, '2003-10-25 00:00:00', 120))
and CASE WHEN a11.GRADE in ('01','02','03','04','05','06') THEN '1 to 6' WHEN a11.GRADE in ('07','08','09') THEN '7 to 9' WHEN a11.GRADE in ('10','11','12','13') THEN '10 to 13' WHEN a11.GRADE in ('14','15','16','17','18','19','20') THEN '14+ (14-20)' WHEN a11.GRADE in ('70','71','72','73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89') THEN 'Sales (70-89)' ELSE 'Non-Exempt' END in ('1 to 6')
and a11.IN_GUIDE_CNT = 1)
group by a11.EMPLID,
a11.VC_AWARD_DT,
a11.IN_GUIDE_CNT,
a11.PER_DIM,
a12.WFI_FLSA_ID,
a14.WFI_FDPTLVL3_ID,
a11.DEPTID,
a11.IN_COMPANY,
a13.IN_GRT_CD,
a13.IN_STOCK_PLAN,
a13.ST_OPTION_TYPE,
a13.IN_VEST_SCHED_ID,
a11.REHIRE_DT,
a11.COUNTRY,
a11.GRADE

 

[entaroadun]

I understand the issue, but I don't see the relevance.

In the join between a11 and a12, you have a11.PER_DIM = a12.DMT_EFF_ENDDT.

In the join between a11,a12,a13 and a14, you have a11.PER_DIM = a14.DMT_EFF_ENDDT and a12.DMT_EFF_ENDDT = a14.DMT_EFF_ENDDT.

In the join between a11,a12,a13,a14 and a15, you have a11.PER_DIM = a15.DMT_EFF_ENDDT and a12.DMT_EFF_ENDDT = a15.DMT_EFF_ENDDT.

You say that a12.DMT_EFF_ENDDT = a14.DMT_EFF_ENDDT and a12.DMT_EFF_ENDDT = a15.DMT_EFF_ENDDT are incorrect.

However, the a11.PER_DIM = a14.DMT_EFF_ENDDT and a11.PER_DIM = a15.DMT_EFF_ENDDT are correct?

If a11.PER_DIM = a12.DMT_EFF_ENDDT, then what difference does the extra join condition make? There is no difference in the result set if you drop the a12=a14 and a12=a15 join conditions.

 

[Rambo2292468]

Thanks entaroadun,

Yes You got the issue , there is no difference except runnning time , I mean I did get same result set but time to run with extra join is more than without .

But if . If the warehouse data has good integrity, this logic does not cause any issues.

But it seems it is generated by MSTR SQL engine it does make join this way only as I got note at microstrategy support saying as follows:

As mentioned in point #4 in TN5200-007-0027