How to get rid of RSUM

[farlein]

I am working on a data model with 2 fact tables and 2 conformed dimension tables. The two fact tables are connected by the two dimension table. Fact table 1 contains students' enrollment information such as Unit taken for GPA, and fact table 2 contains students' transfer credit information such as Unit transfer. The two fact tables have some common fields, such as student ID and academic term.

Below is SQL I got. Does anyone know how to get rid of RSUM, or why does RSUM appear even though I already have two keys to join (STUDENT_DATA_SID and ACADEMIC_TERM_SID) ? I compared my case to the case from page 338 to page 341 in IBM Cognos Framework Manager Version 10.2.0: User Guide, and the SQL generated there does not have a RSUM statement. Thank you very much for your help in advance!


Cognos SQL:

with
D as
    (select
           STUDENT_DATA_DM.STUDENT_DATA_SID  as  sc,
           ACADEMIC_TERM_DM.ACADEMIC_TERM_SID  as  sc2,
           STUDENT_DATA_DM.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM.PRIMARY_NAME  as  PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM  as  ACADEMIC_TERM,
           STUDENT_TERM_DETAIL_FC.UNT_TAKEN_GPA  as  UNT_TAKEN_GPA,
           RSUM(1  for STUDENT_DATA_DM.STUDENT_DATA_SID,ACADEMIC_TERM_DM.ACADEMIC_TERM_SID  order by

STUDENT_DATA_DM.STUDENT_DATA_SID asc,ACADEMIC_TERM_DM.ACADEMIC_TERM_SID asc  local)  as  sc7
     from
           SA_DW..SA_DATAMART.STUDENT_DATA_DM STUDENT_DATA_DM,
           SA_DW..SA_DATAMART.ACADEMIC_TERM_DM ACADEMIC_TERM_DM,
           SA_DW..SA_DATAMART.STUDENT_TERM_DETAIL_FC STUDENT_TERM_DETAIL_FC
     where
           (STUDENT_TERM_DETAIL_FC.STUDENT_DATA_SID = STUDENT_DATA_DM.STUDENT_DATA_SID) and
           (STUDENT_TERM_DETAIL_FC.ACADEMIC_TERM_SID = ACADEMIC_TERM_DM.ACADEMIC_TERM_SID)
     order by
           sc asc,
           sc2 asc
    ),
D3 as
    (select
           STUDENT_DATA_DM.STUDENT_DATA_SID  as  sc,
           ACADEMIC_TERM_DM.ACADEMIC_TERM_SID  as  sc2,
           STUDENT_DATA_DM.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM.PRIMARY_NAME  as  PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM  as  ACADEMIC_TERM,
           COURSE_TRANSFER_CREDIT_FC.UNT_TRNSFR  as  UNT_TRNSFR,
           RSUM(1  for STUDENT_DATA_DM.STUDENT_DATA_SID,ACADEMIC_TERM_DM.ACADEMIC_TERM_SID  order by

STUDENT_DATA_DM.STUDENT_DATA_SID asc,ACADEMIC_TERM_DM.ACADEMIC_TERM_SID asc  local)  as  sc7
     from
           SA_DW..SA_DATAMART.STUDENT_DATA_DM STUDENT_DATA_DM,
           SA_DW..SA_DATAMART.ACADEMIC_TERM_DM ACADEMIC_TERM_DM,
           SA_DW..SA_DATAMART.COURSE_TRANSFER_CREDIT_FC COURSE_TRANSFER_CREDIT_FC
     where
           (COURSE_TRANSFER_CREDIT_FC.STUDENT_DATA_SID = STUDENT_DATA_DM.STUDENT_DATA_SID) and
           (COURSE_TRANSFER_CREDIT_FC.ARTICULATION_TERM_SID = ACADEMIC_TERM_DM.ACADEMIC_TERM_SID)
     order by
           sc asc,
           sc2 asc
    )
select
       coalesce(D.EMPLOYEE_ID,D3.EMPLOYEE_ID)  as  EMPLOYEE_ID,
       coalesce(D.PRIMARY_NAME,D3.PRIMARY_NAME)  as  PRIMARY_NAME,
       coalesce(D.ACADEMIC_TERM,D3.ACADEMIC_TERM)  as  ACADEMIC_TERM,
       D.UNT_TAKEN_GPA  as  UNT_TAKEN_GPA,
       D3.UNT_TRNSFR  as  UNT_TRNSFR
from
       D
        full outer join
       D3
        on (((D.sc = D3.sc) and (D.sc2 = D3.sc2)) and (D.sc7 = D3.sc7))

[bus_pass_man]

 Just to confirm
i. The fact tables are on the many end of the relationships.
STUDENT_DATA_DM STUDENT_DATA_DM,      1.n  STUDENT_TERM_DETAIL_FC 
                                                                      1.n  COURSE_TRANSFER_CREDIT_FC COURSE_TRANSFER_CREDIT_FC


ACADEMIC_TERM_DM ACADEMIC_TERM_DM   1.n  STUDENT_TERM_DETAIL_FC 
                                                                      1.n  COURSE_TRANSFER_CREDIT_FC COURSE_TRANSFER_CREDIT_FC

ii. What's the usage of the query items?

[farlein]

For i, yes. The relationships are defined like that.

For ii, I am not very sure what your question is, but I try to answer here. STUDENT_TERM_DETAIL_FC contains students' enrollment information such as Unit taken for GPA and other keys (student demographic data key, acedemic term key, etc.), and COURSE_TRANSFER_CREDIT_FC contains students' transfer information such as Unit transfer and other keys (student demographic data key, acedemic term key, etc.). STUDENT_DATA_DM contains students' demographic information such as name, and ACADEMIC_TERM_DM contains university term information such as academic term, term beginning time.

Any idea why Cognos wants RSUM here for a full outer join? Thanks!

[Lynn]

There is a usage property associated with each data item that can be set to Fact or Attribute or Identifier. Do your fact tables have any "Fact" usage query items?

Seems to me you would want to be able to show counts of students or courses or transfers or whatever based on your facts and perhaps money if there is any kind of tuition or fee information on your fact table. These would be the things set to usage "Fact" with the appropriate aggregation properties set, such as sum or count.

[farlein]

Thank you for your reply, Lynn.

Here are the usages:
EMPLOYEE_ID: identifier
PRIMARY_NAME: attribute
ACADEMIC_TERM: identifier
UNT_TAKEN_GPA: fact
UNT_TRNSFR: fact

[FabianGaussling]

Hi,

what I think is strange, is that your two sub-queries don't have any aggregation over your facts? Have you switched off AUTO-SUM in the Query-Test-Register? I think cognos generates the RSUM in the case you are querying two fact tables on detail level, that is without aggregation. Cognos does so in order to build a unique key to do the join. Maybe you could try to run the same query with AUTO-SUM

Fabian

[farlein]

Thank you for your reply, Fabian! Checking the AUTO-SUM box makes my test results more reasonable. For the current simplified case, RSUM is replaced by XSUM, and the process is accelerated. Thank you very much!

However, when applying a more complex case, RSUM appears again. In addition to the two facts tables and two conformed dimension tables, there are 2 more non-conformed dimension tables. One is Academic_Plan_DM which is only linked to Student_Term_Detail_FC, and the other one is Course_Offering_ID which is only linked to Course_Transfer_FC. The Cognos SQL code is shown below. I guess I now have a feeling why RSUM appears. Even though AUOT-SUM is used, for a special student, he could have only one record for how many units taken for GPA in a term, but he could have eight records for units transferred in the same term because he transferred in eight courses. Now Cognos sees 1 record in the first "to be stitiched" part, and 8 records in the second "to be stitiched" part, so Cognos wants to use RSUM to create a unique key to join. But in my case, I do not need that unique key, and I just want Cognos to join the two parts using Student ID and academic term, although it is a 1:8 join. Am I right? If so, how to let Cognos know not to generate a unique key (sc)?

Cognos SQL:

with
STUDENT_DATA_DM7 as
    (select
           STUDENT_DATA_DM.STUDENT_DATA_SID  as  STUDENT_DATA_SID,
           STUDENT_DATA_DM.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM.PRIMARY_NAME  as  PRIMARY_NAME
     from
           SA_DW..SA_DATAMART.STUDENT_DATA_DM STUDENT_DATA_DM
     where
           (STUDENT_DATA_DM.EMPLOYEE_ID = '700000000')
    ),
D5 as
    (select
           STUDENT_DATA_DM7.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM7.PRIMARY_NAME  as  PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM  as  ACADEMIC_TERM,
           ACADEMIC_PLAN_DM.ACADEMIC_PLAN  as  ACADEMIC_PLAN,
           XSUM(STUDENT_TERM_DETAIL_FC.UNT_TAKEN_GPA  for STUDENT_DATA_DM7.EMPLOYEE_ID,STUDENT_DATA_DM7.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,ACADEMIC_PLAN_DM.ACADEMIC_PLAN )  as  UNT_TAKEN_GPA,
           RSUM(1  at STUDENT_DATA_DM7.EMPLOYEE_ID,STUDENT_DATA_DM7.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,ACADEMIC_PLAN_DM.ACADEMIC_PLAN  for STUDENT_DATA_DM7.EMPLOYEE_ID,STUDENT_DATA_DM7.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM  order by STUDENT_DATA_DM7.EMPLOYEE_ID asc,STUDENT_DATA_DM7.PRIMARY_NAME asc,ACADEMIC_TERM_DM.ACADEMIC_TERM asc,ACADEMIC_PLAN_DM.ACADEMIC_PLAN asc  local)  as  sc
     from
           STUDENT_DATA_DM7,
           SA_DW..SA_DATAMART.ACADEMIC_TERM_DM ACADEMIC_TERM_DM,
           SA_DW..SA_DATAMART.ACADEMIC_PLAN_DM ACADEMIC_PLAN_DM,
           SA_DW..SA_DATAMART.STUDENT_TERM_DETAIL_FC STUDENT_TERM_DETAIL_FC
     where
           (STUDENT_TERM_DETAIL_FC.STUDENT_DATA_SID = STUDENT_DATA_DM7.STUDENT_DATA_SID) and
           (STUDENT_TERM_DETAIL_FC.ACADEMIC_TERM_SID = ACADEMIC_TERM_DM.ACADEMIC_TERM_SID) and
           (STUDENT_TERM_DETAIL_FC.ACADEMIC_PLAN_SID = ACADEMIC_PLAN_DM.ACADEMIC_PLAN_SID)
     group by
           STUDENT_DATA_DM7.EMPLOYEE_ID,
           STUDENT_DATA_DM7.PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM,
           ACADEMIC_PLAN_DM.ACADEMIC_PLAN
     order by
           EMPLOYEE_ID asc,
           PRIMARY_NAME asc,
           ACADEMIC_TERM asc,
           ACADEMIC_PLAN asc
    ),
STUDENT_DATA_DM8 as
    (select
           STUDENT_DATA_DM.STUDENT_DATA_SID  as  STUDENT_DATA_SID,
           STUDENT_DATA_DM.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM.PRIMARY_NAME  as  PRIMARY_NAME
     from
           SA_DW..SA_DATAMART.STUDENT_DATA_DM STUDENT_DATA_DM
     where
           (STUDENT_DATA_DM.EMPLOYEE_ID = '700000000')
    ),
D6 as
    (select
           STUDENT_DATA_DM8.EMPLOYEE_ID  as  EMPLOYEE_ID,
           STUDENT_DATA_DM8.PRIMARY_NAME  as  PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM  as  ACADEMIC_TERM,
           XSUM(COURSE_TRANSFER_CREDIT_FC.UNT_TRNSFR  for STUDENT_DATA_DM8.EMPLOYEE_ID,STUDENT_DATA_DM8.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,COURSE_OFFERING_DM.COURSE_ID )  as  UNT_TRNSFR,
           COURSE_OFFERING_DM.COURSE_ID  as  COURSE_ID,
           RSUM(1  at STUDENT_DATA_DM8.EMPLOYEE_ID,STUDENT_DATA_DM8.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,COURSE_OFFERING_DM.COURSE_ID  for STUDENT_DATA_DM8.EMPLOYEE_ID,STUDENT_DATA_DM8.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM  order by STUDENT_DATA_DM8.EMPLOYEE_ID asc,STUDENT_DATA_DM8.PRIMARY_NAME asc,ACADEMIC_TERM_DM.ACADEMIC_TERM asc,COURSE_OFFERING_DM.COURSE_ID asc  local)  as  sc
     from
           STUDENT_DATA_DM8,
           SA_DW..SA_DATAMART.ACADEMIC_TERM_DM ACADEMIC_TERM_DM,
           SA_DW..SA_DATAMART.COURSE_TRANSFER_CREDIT_FC COURSE_TRANSFER_CREDIT_FC,
           SA_DW..SA_DATAMART.COURSE_OFFERING_DM COURSE_OFFERING_DM
     where
           (COURSE_TRANSFER_CREDIT_FC.STUDENT_DATA_SID = STUDENT_DATA_DM8.STUDENT_DATA_SID) and
           (COURSE_TRANSFER_CREDIT_FC.ARTICULATION_TERM_SID = ACADEMIC_TERM_DM.ACADEMIC_TERM_SID) and
           (COURSE_OFFERING_DM.COURSE_OFFERING_SID = COURSE_TRANSFER_CREDIT_FC.COURSE_OFFERING_SID)
     group by
           STUDENT_DATA_DM8.EMPLOYEE_ID,
           STUDENT_DATA_DM8.PRIMARY_NAME,
           ACADEMIC_TERM_DM.ACADEMIC_TERM,
           COURSE_OFFERING_DM.COURSE_ID
     order by
           EMPLOYEE_ID asc,
           PRIMARY_NAME asc,
           ACADEMIC_TERM asc,
           COURSE_ID asc
    )
select
       coalesce(D5.EMPLOYEE_ID,D6.EMPLOYEE_ID)  as  EMPLOYEE_ID,
       coalesce(D5.PRIMARY_NAME,D6.PRIMARY_NAME)  as  PRIMARY_NAME,
       coalesce(D5.ACADEMIC_TERM,D6.ACADEMIC_TERM)  as  ACADEMIC_TERM,
       D5.UNT_TAKEN_GPA  as  UNT_TAKEN_GPA,
       D6.UNT_TRNSFR  as  UNT_TRNSFR,
       D5.ACADEMIC_PLAN  as  ACADEMIC_PLAN,
       D6.COURSE_ID  as  COURSE_ID
from
       D5
        full outer join
       D6
        on ((((D5.EMPLOYEE_ID = D6.EMPLOYEE_ID) and (D5.PRIMARY_NAME = D6.PRIMARY_NAME)) and (D5.ACADEMIC_TERM = D6.ACADEMIC_TERM)) and (D5.sc = D6.sc))

[farlein]

I did some experiments. My previous feeling is wrong. Cognos can handle 1 to n join without RSUM. It is the two non-confirm dimension tables which triggers RSUM.

In each stitched part, I guess Cognos first does XSUM. For the first part and the second part, respectively,

1st : XSUM(STUDENT_TERM_DETAIL_FC.UNT_TAKEN_GPA  for STUDENT_DATA_DM7.EMPLOYEE_ID,STUDENT_DATA_DM7.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,ACADEMIC_PLAN_DM.ACADEMIC_PLAN )  as  UNT_TAKEN_GPA

2nd: XSUM(COURSE_TRANSFER_CREDIT_FC.UNT_TRNSFR  for STUDENT_DATA_DM8.EMPLOYEE_ID,STUDENT_DATA_DM8.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,COURSE_OFFERING_DM.COURSE_ID )  as  UNT_TRNSFR

And then Cognos see different granularity. The first part has an extra ACADEMIC_PLAN and the second part has an extra COURSE_ID, so Cognos decide to run two RSUM statements in order to generate a unique key.

However, if only one non-conformed dimension table is involved, it is fine. For example, if there is only one non-conformed field COURSE_OFFERING_DM.COURSE_ID.

The XSUM statements will be
1st : XSUM(STUDENT_TERM_DETAIL_FC.UNT_TAKEN_GPA  for STUDENT_DATA_DM7.EMPLOYEE_ID,STUDENT_DATA_DM7.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM )  as  UNT_TAKEN_GPA
2nd: XSUM(COURSE_TRANSFER_CREDIT_FC.UNT_TRNSFR  for STUDENT_DATA_DM8.EMPLOYEE_ID,STUDENT_DATA_DM8.PRIMARY_NAME,ACADEMIC_TERM_DM.ACADEMIC_TERM,COURSE_OFFERING_DM.COURSE_ID )  as  UNT_TRNSFR

Can anyone let me know whether my current finding is correct? Or, can anyone tell me how to deal with two non-conformed dimension tables except merging the two fact tables together?