URGENT SQL generating issue - join two tables on multiple columns PLEA

[dlutzker]

I have been grappling with a problem joining two tables that need to be joined on multiple columns. One metric is joining correctly (the second pass), but the first one insists on remaining joined on one field only. In general, how does the SQL engine decide when to join on multiple columns?

create table ZZMD002joins nologging as
select a12.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
	count(a12.EXAM_FACT_ID)  WJXBFS1
from SDW_ORDERED_EXAM_FACT_SS a12, 
	SDW_COMPLETER_EXAM_SEQ_SS a13
where a12.CONTACT_LU_ID = a13.CONTACT_LU_ID 
 and	a12.TAKEN_SEQUENCE = 1
group by a12.PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end 


create table ZZMD01 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a12.TAKEN_EXAM_SEQUENCE, ',')=0 then a12.TAKEN_EXAM_SEQUENCE else substr(a12.TAKEN_EXAM_SEQUENCE, 1, instr(a12.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
 	count(distinct a11.COMPLETER_FACT_ID)  WJXBFS1
from SDW_COMPLETER_FACT a11, 
	SDW_COMPLETER_EXAM_SEQ_SS a12, 
	SDW_COMPLETER_END_TIME_DIM_VW a13
where a11.CONTACT_LU_ID = a12.CONTACT_LU_ID and 
	a11.PROGRAM_DIM_ID = a12.PROGRAM_DIM_ID and 
	a11.END_TIME_DIM_ID = a13.END_TIME_DIM_ID
 and	a13.YEAR_LU_ID in (54)
group by a11.PROGRAM_DIM_ID,
	case when instr(a12.TAKEN_EXAM_SEQUENCE, ',')=0 then a12.TAKEN_EXAM_SEQUENCE else substr(a12.TAKEN_EXAM_SEQUENCE, 1, instr(a12.TAKEN_EXAM_SEQUENCE, ',')-1) end

What I need the first pass to do is as follows:

create table ZZMD002joins nologging as
select a12.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
	count(a12.EXAM_FACT_ID)  WJXBFS1
from SDW_ORDERED_EXAM_FACT_SS a12, 
	SDW_COMPLETER_EXAM_SEQ_SS a13
where a12.CONTACT_LU_ID = a13.CONTACT_LU_ID 
[b]and a12.program_dim_id = a13.program_dim_id[/b]
 and	a12.TAKEN_SEQUENCE = 1
group by a12.PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end

I have checked the results from this last SQL, and they are correct, but I can't get the MSTR engine to do it!!

Please, I have never posted an urgent request, but I am in need this time.
Thanks.
Dave

 

[nlim]

Again you should check your attribute Program, make sure the 2 tables in your first pass are in the attribute definition.

SDW_ORDERED_EXAM_FACT_SS a12,
SDW_COMPLETER_EXAM_SEQ_SS a13

if the software cannot identify that the attribute is found in a specific table, it won't do the joins.

 

[dlutzker]

nlim - I appreciate your taking a look, but the Program attribute, which does mean the same thing across the entire data warehouse, has all the related tables defined, including the two from that first pass.

Any other ideas? Anybody else? I am stumped!
Dave

 

[nlim]

what is this attribute?

case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end

 

[dlutzker]

For better or worse, this is the way I get the first exam taken by a student who completed the program. The TAKEN_EXAM_SEQUENCE field is a text string of the exam names taken, separated by commas. When there is no comma, there is only one exam listed. Otherwise, I take the substring up to the first comma.
It is a field in the SDW_COMPLETER_EXAM_SEQ_SS (a snapshot or materialized view). I have not noticed any behavioral problems with this attribute yet, and it has gotten some use.
Dave

 

[nlim]

cool, it's easier to troubleshoot SQL queries with the actual query requirement....so your report query is

for each programID and first_exam_taken_by_student_that_completed_program, show me
2 metrics, what are these 2 metrics?

 

[dlutzker]

Okay - I am attempting to get numbers and the ratio between them for the following two metrics:
[ol]
[li]The number of students who took that exam first, within the specific program (regardless of whether they passed the exam, or completed the program. Hence, the use of the SDW_ORDERED_EXAM_FACT_SS, which has a record for every exam taken by every student.[/li]
[li]The number of Completers (students that have completed the program by passing all the required exams) that began with that exam, within the specific program.[/li]
[li]The ratio of the two should give some kind of success rate broken down by the first program taken.[/li]
[/ol]
I hope that sheds some light on the matter. I am online, and can answer any additional questions you may have.
Dave

 

[nlim]

in your metrics definition, are you counting an attributes (eg. student), or are you counting an facts?

 

[dlutzker]

Metric #1 is a count of a fact, which is actually a flag. It has levels for "First Completers Exam Taken", Program and Report. The condition is the sequence (rank) = 1 to ensure it is the first exam taken.
Metric #2 is a Count of a fact, but the fact is the Completer_Fact_ID. No level or condition.

I have tried numerous other combinations, too many to recall.
Dave

 

[nlim]

Dave, try looking at the "entry levels" for the 2 facts. I would guess that they are different. In the fact editor, under tools you should get a "Show fact entry level".

 

[dlutzker]

I am looking at the fact entry levels, and I may need to make some changes here. I assume that the entry levels are determined only in the hierarchy editor, and that to make changes, I will need to make sure that the attributes I am using in this report are in the appropriate hierarchy, with the fact-entry allowed.
Is there more I should know? I am checking documentation now on fact-entry levels.
Dave

 

[dlutzker]

Metric #1: What seems to be missing is an entry point for Program. As I mentioned earlier, Program sees all the relevant fact tables, including this one. While I am not adverse to creating a fact extension for this fact, I don't see why the entry level shouldn't be there. Program_Dim_ID is a column in the fact table, and Program is the lowest level in the hierarchy, and it has an entry point defined. I don't know what the real problem is here.

Metric #2 has Program as an entry level, as well as other relevant attributes.

Any suggestions? Any risks in defining a fact extension for the fact in Metric #1? Can you suggest why the entry level isn't there, when it seems that it should be?

Thanks.
Dave

 

[nlim]

you can extend the fact entry levels using the fact extensions. But in general they should be the same for both facts...

 

[dlutzker]

Well, I extended the fact in question to Program using a table relation (since there is a PK-FK relationship there anyway). I updated the schema, and checked the SQL for the report. Unfortunately, it gave me the same results. I got the same SQL that I had in that first pass in my first post of this thread.

Sooooo, if you have any brilliant ideas, please let me know. I will be wrapping this project up at the end of next week, with or without this fix, so anytime before that will still be a huge help.

Regardless, you have certainly invested the time to understand my issue, and given it your best try. For that I am grateful.

All the best.
Dave

 

[entaroadun]

Check to confirm that the Program attribute is actually on the SDW_ORDERED_EXAM_FACT_SS logical table. This includes checking from both the attribute editor and the logical table editor.

If it's on both, then check to see if Program is set as a key. Attributes have to be a key on two tables in order for the engine to join on it.

 

[nlim]

Dave, there is another way to get the same query that you are looking for. It takes a slightly different approach and assumes that you have an "EXAM" attribute mapped into your ordered fact table. This approach uses sequences to limit to first exam taken instead of the first_exam attribute.

To get your denominator of your ratio you need a metric like this
- create M1 defined as count(EXAM_FACT_ID) filtered by sequence=1
- if you put program, exam and M1 on your report, you should get count of students who first took the exam, grouped by exam and program.

Next you need the numerator of your ratio.
The metric is count(completer fact table),
where year=55,
and contact_Id=SDW_ORDERED_EXAM_FACT.contactID
where SDW_ORDERED_EXAM_FACT.taken_sequence=1
group by program ID
and SDW_ORDERED_EXAM_FACT.exam

I'm not sure what else you have on the completer fact table, but I would try creating this metric M2 defined as follows

count(completer fact table fact) where taken_sequence=1

if you create a report with program, exam and M2, your SQL should look something like

select count(completer fact table),
a1.programid,
a1.exam
from SDW_ORDERED_EXAM_FACT a1
completer fact table a2
where a1.programid=a2.programid
and a1.contactid=a2.contactid
and a1.taken_sequence=1
group by a1.programid,
a1.exam

this gives you all the people in completer fact table, linked by the first exam they took.

If these 2 metrics look right, you should be able to get the rest of your ratio easily. This is the extent of my contribution, good luck

 

[dlutzker]

Thanks to both nlim and entaroadun!
Before I try nlim's suggestion I will work on entaroadun's idea, because in fact the SDW_ORDERED_EXAM_FACT_SS logical table has an incorrect table key defined. It is not a unique definition, and it uses a pass-through SQL attribute, and excludes Program_Dim_ID.

I have not figured out how to change the key in the logical table view! Since it is an Oracle Snapshot, there isn't really a primary key. Still, it's no excuse that MSTR miscalculates the key (it uses 5 attributes which provide 710,425 out of 711,939 records in the table). Since this is neither horseshoes, nor a hand grenade, that just doesn't cut it. There are three ID's that do uniquely identify all rows. My next step is to have a unique index put on those three columns in Oracle.

Will that help? Is there a way to manually tell MSTR what the key fields are in a logical table?? I haven't found it.

Thanks. If I can't get this to work, I will try nlim's idea. I'll let you know.
Thanks.
Dave

 

[entaroadun]

The lowest level attribute in any hierarchy on a given logical table should be in the key. MSTR calculates the key that way.

If MSTR isn't calculating the key properly (and I have run into many instances of this), try removing the attribute from the logical table (by disconnecting the attribute ID form expression from the table), saving the attribute, updating the schema, reconnecting the table, saving, and updating the schema again. This usually fixes it.

If this doesn't work, you need to delete the entire attribute, update schema, and recreate it.

If that doesn't work, you need to delete the attribute and the logical table, and recreate everything attached to the table.

The latter two are instances of MD corruption, which ScanMD and MD Doctor will uncover, but cannot fix. The two courses of action outlined are the only ways of fixing it (that I know of).

Good luck! I hope the first two work, since the last one is truly a pain in the a$$.

Oh, BTW, this attribute doesn't have multiple expressions in its ID form, does it?

 

[dlutzker]

entaroadun - If it is a case of corrupt metadata, than there is still hope. I am working in the development/test environment, and the attribute in question ("Program", and BTW it does not have multiple expressions in it's ID form), is in the current production system. The logical table, and some of the other attriubutes that it erroneously grabbed as components to the key are new. I will be testing the project merge in development today.

I did try various modifications to several attributes, without going so far as deleting the Program attribute. Since it is currently in production there are a few metrics, filters and prompts that depend on it, but even worse, there are a number of canned and user reports that use it.

I will post the result of my test, and share with you the final result.

Thanks for your help!
Dave

 

[entaroadun]

Try this for a low-impact way to replace an attribute:

Create a new attribute from scratch, identical to the one you are replacing. This SHOULD work, since the attribute forms won't be corrupt. Use this new one in your testing.

If it works, go into each dependent object and add this attribute, removing the old one.

Once you are done replacing, delete the old attribute completely.

Remember to back up your MD and be liberal with your schema updates.

Someone really should put together a widget to do this...