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))
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?
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!
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.
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
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
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))
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?