SQL Generated is Inconsistent 1

[dlutzker]

I am writing a report that combines metrics from two fact tables, but both are on the same level:
[li]From SDW_Exam_Fact I am counting contact_lu_id's at the program level[/li]
[li]From SDW_Completer_Fact I am counting Fact_ID's at the program level (the fact table itself is the unique
combination of programs and contacts who have completed the programs).[/li]
The filter on Year is not working the same, even though both fact tables have the same type of relationships
with the time dimension, and the attributes and filters are built the same way. Here is the code:

create table ZZSP00 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
 a12.YEAR_LU_ID  YEAR_LU_ID,
 count(a11.CONTACT_LU_ID)  WJXBFS1
from SDW_EXAM_FACT a11, 
 SDW_EXAM_TIME_DIM_VW a12
where a11.EXAM_TIME_DIM_ID = a12.EXAM_TIME_DIM_ID
 and a12.YEAR_LU_ID in (55)
group by a11.PROGRAM_DIM_ID,
 a12.YEAR_LU_ID 

create table ZZSP01 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
 a12.YEAR_LU_ID  YEAR_LU_ID,
 count(distinct a11.COMPLETER_FACT_ID)  WJXBFS1
from SDW_COMPLETER_FACT a11, 
 SDW_YEAR_LU a12
where (a12.YEAR_LU_ID in (55)
 and a12.YEAR_LU_ID in (55))
group by a11.PROGRAM_DIM_ID,
 a12.YEAR_LU_ID 

select distinct pa1.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
 a12.PROGRAM_SHORT_LABEL  PROGRAM_SHORT_LABEL,
 a12.PROGRAM_LONG_LABEL  PROGRAM_LONG_LABEL,
 pa1.YEAR_LU_ID  YEAR_LU_ID,
 a11.YEAR  YEAR,
 pa1.WJXBFS1  WJXBFS1,
 pa2.WJXBFS1  WJXBFS2
from ZZSP00 pa1, 
 ZZSP01 pa2, 
 SDW_YEAR_LU a11, 
 SDW_PROGRAM_DIM a12
where pa1.PROGRAM_DIM_ID = pa2.PROGRAM_DIM_ID and 
 pa1.YEAR_LU_ID = pa2.YEAR_LU_ID and 
 pa1.YEAR_LU_ID = a11.YEAR_LU_ID and 
 pa1.PROGRAM_DIM_ID = a12.PROGRAM_DIM_ID

drop table ZZSP00
drop table ZZSP01

The second pass is filtering differently, and ineffectually (it is not filtering at all).
Any ideas??
Thanks.
Dave

 

[nlim]

the 2nd pass is lacking a join from the fact table to the lookup.

My guess is that if you look in the attribute EXAM_TIME_DIM_ID, the fact table SDW_COMPLETER_FACT is not showing up. Just add this table SDW_COMPLETER_FACT to this attribute.

Update schema and you should be all set.

 

[dlutzker]

nlim - you are right! I put the Exam_Year on the report, which sees only the view SDW_EXAM_TIME_DIM_VW, which sees only SDW_EXAM_FACT, not SDW_COMPLETER_FACT.
But......
When I built these attributes I understood MSTR would want to keep the Time_Dim's separate - so I have two views (SELECT * FROM SDW_TIME_DIM): one for the Exam Time, and a separate view for the Completer Time.

I would really like to know the best way to do this. I have had trouble before when I created an attribute that seemed like a good idea for a specific report, and then the MSTR engine goes and uses it whenever it wants to, in innappropriate places. I felt like I lost total control. I built the attribute intending it only to be used for one specific report.

So should I have a separate attribute that sees both the Completer Time view and the Exam Time view?
Should I just make the Completer Time attribute recognize the SDW)EXAM_FACT table? Could this possibly screw up some other existing reports?

Thanks for your help!
Dave

 

[nlim]

I'm not sure why you would need 2 time dims for the 2 fact tables. Unless the times refer to different times like grade date versus test date. In which case you should have them as separate anyways cos they mean different things.

So I suggest you do the following change impact analysis...

RMC on the Time attribute A1 that you have linked to SDW_COMPLETER_FACT. Find the dependent child objects. This will pick up any reports, filters, templates and other stuff that uses this attribute. If there are few, then I would just delete this attribute and connect the other time attribute A2 to this table.

If there are many reports affected, then I would probably hesitate from making the change. Perhaps a review of your data model would be more productive, in the long run.

Since you can easily "uncheck" the table, you can "undo" the linking of the A2 to the SDW_COMPLETER_FACT table. So you could make the link while unhooking the A1 by removing the table from the A1 definition. Then in the reports that used the A1 attribute, replace A1 with A2. Quite a pain really, I feel for your inconvenience

 

[dlutzker]

nlim and others,
No such luck. The two times are different: time of completion of a program, and time of taking an exam. They should be separate. But I would think I could report on data from each, while filtering on the same year in both fact tables.

I tried to add the second year attribute, such that both the exam year and the completion year are on the template and displaying in the report. The result was worse, but perhaps informative:

Report: Get Completion Rate
Cache Used: No

create table ZZSP00 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	a12.YEAR_LU_ID  YEAR_LU_ID,
	a12.YEAR_LU_ID  Completer_End_Year_ID,
	count(a11.CONTACT_LU_ID)  WJXBFS1
from SDW_EXAM_FACT a11, 
	SDW_YEAR_LU a12
where (a12.YEAR_LU_ID in (54)
 and a12.YEAR_LU_ID in (54))
group by a11.PROGRAM_DIM_ID,
	a12.YEAR_LU_ID,
	a12.YEAR_LU_ID 

create table ZZSP01 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	a12.YEAR_LU_ID  YEAR_LU_ID,
	a12.YEAR_LU_ID  Completer_End_Year_ID,
	count(distinct a11.COMPLETER_FACT_ID)  WJXBFS1
from SDW_COMPLETER_FACT a11, 
	SDW_YEAR_LU a12
where (a12.YEAR_LU_ID in (54)
 and a12.YEAR_LU_ID in (54))
group by a11.PROGRAM_DIM_ID,
	a12.YEAR_LU_ID,
	a12.YEAR_LU_ID 

select distinct pa1.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	a12.PROGRAM_SHORT_LABEL  PROGRAM_SHORT_LABEL,
	a12.PROGRAM_LONG_LABEL  PROGRAM_LONG_LABEL,
	pa1.YEAR_LU_ID  YEAR_LU_ID,
	a11.YEAR  YEAR,
	pa1.Completer_End_Year_ID  Completer_End_Year_ID,
	a11.YEAR  YEAR0,
	pa1.WJXBFS1  WJXBFS1,
	pa2.WJXBFS1  WJXBFS2
from ZZSP00 pa1, 
	ZZSP01 pa2, 
	SDW_YEAR_LU a11, 
	SDW_PROGRAM_DIM a12
where pa1.Completer_End_Year_ID = pa2.Completer_End_Year_ID and 
	pa1.PROGRAM_DIM_ID = pa2.PROGRAM_DIM_ID and 
	pa1.YEAR_LU_ID = pa2.YEAR_LU_ID and 
	pa1.Completer_End_Year_ID = a11.YEAR_LU_ID and 
	pa1.YEAR_LU_ID = a11.YEAR_LU_ID and 
	pa1.PROGRAM_DIM_ID = a12.PROGRAM_DIM_ID


drop table ZZSP00

drop table ZZSP01

Now neither pass is filtering. Any ideas?
Thanks.
Dave

 

[nlim]

Perfect example of attribute aliasing....hear me out.

So on your report, should it say

program Program_Year count_program_students count_exam_students
OR
program Exam_year count_program_studenst count_exam_students

Here's where the confusion comes in. I think your real question is something like this

"Count the number of students in each program in program_year 55; and count the number of students in each program who took the exam in exam_year 55.

The YEAR column on your report is a shortcut and combination for the 2 types of years. MicroStrategy is not happy with shortcuts. Since the 2 times are actually different things you need to explicitly specify your filter in both attributes. For example, you could count program students in 2003, but took exams in 2004. These types of queries require them to be separate.

So what to do in your situation...So I think if you put a filter for program_year=55 and exam_year=55, and you remove the year attribute from your report, you should be ok.

so your report will look like this

program count_program_students Count_exam_students

Your year information will have to be in the filter, not on the grid.

 

[dlutzker]

nlim - wanted to thank you for sticking with me to the end. You are right, and your solution worked, so first of all, KUDOS!
I hope it is appropriate to give you a star for this, even though I don't think this thread will be particularly helpful to anybody else, but it was to me.

Note to Tek-tips: If I could change the title of the thread at this time, it would be far more useful to other users. Now that I know where the real problem lies, I can appropriately name the thread, which I couldn't prior to learning how to resolve the issue.

Thanks nlim - keep up the good work!