MSTR won't use fact table to join 2 dims (for metric or attribute) 1

[dlutzker]

Here is my situation:
Running v.7.2.2 on Oracle 9i

Warehouse Tables:
Course_Dim has the name of the course
Student_Dim has the student's names, date of birth
Start_Date_Vw - a view of the Time dimension for date student started the course
End_Date_Vw - a view of the time dimension for date student finished the course
Course_Pass_Fact has FK's of all the dimensions and one fact: Days_to_complete (the course). We also are counting the number of students for a course as a fact.

I want a report that shows the average age of the students in each program at the time they started their course.

Course Name | # of Students | Avg Age of Student at Start |
----------------------------------------------------------
Course A 1000 37
Course B 1500 29

I can get a list of students and their current ages. I have the numbers of students. I am having trouble with the age of the student at the start of the course. First I am trying to get a list of students, with their age at the start of the course.

I tried to created an attribute using ApplySimple:

ApplySimple( "Months_between(#0, TO_DATE(#1, 'YYYYMM'))/12", [Completer Start Date]@Date, [Completer Birth Date]@ID)

Error: Object (of type: Attribute) not allowed in this place.

When I put underscores in place of the spaces in the attribute names, I get:
Error - Searching for COMPLETER_START_DATE@Date resulted in no matches.

I tried using columns instead of attribute@Form, and got:

ApplySimple( "Months_between(#0, TO_DATE(#1, 'YYYYMM'))/12", TIME_DATE, CONTACT_BIRTH_DATE)

Error: YOu have selected columns from two different tables. Select columns from the same table before continuing.

I've also tried a different approach - a compound metric, going straight for the avg age. In the metric I tried relationship filters, and dimensionality in various combinations, and I kept ending up with one of the SQL passes querying the two dimension tables without the fact table nor any join, and the resulting metric was identical for all rows. That's when I went back to this simpler approach.

I have spent two days researching how to do this on MSTR KB and other sites.....please tell me about the proverbial "missing semicolon"!
Thanks.
Dave

 

[entaroadun]

Create a fact called "Student Age at Course Start". For the expression:

ApplySimple("(MONTHS_BETWEEN(#0, TO_DATE((SELECT S.CONTACT_BIRTH_DATE FROM STUDENT_DIM AS S WHERE S.STUDENT_ID = #1), 'YYYYMM'))/12)", TIME_DATE, STUDENT_ID)

Attribute and fact expressions have to use columns from the same logical table. In the fact above, we use a correlated subquery to "JOIN IN" the CONTACT_BIRTH_DATE from the STUDENT_DIM table. This way, you get around the limitation.

Have fun!

 

[entaroadun]

BTW, on Oracle, this has the added advantage of forcing the CBO to use nested loops. Oracle will not do hash joins with a correlated subquery. For most DWs, hash joins are miserable. This helps with performance tuning.

 

[dlutzker]

entaroadun - I appreciate the response, and I like the idea. Unfortunately, I get the error message in the fact editor:

"Some of the columns defining the expression are not present in the same table. Please define an expression containing columns from the same table before continuing."

It won't let you out of the fact editor, even if you don't validate it. Any other ideas??

 

[entaroadun]

OK, I'm confused. What table is the TIME_DATE column on? Is it on the fact table? If not, what is the key field used to join to the Start_Date_Vw?

 

[dlutzker]

entaroadun - The TIME_DATE columns is in the Start_Date_Vw, and Start_Time_Dim_ID is the primary key.
The fact table has the FK relationship from the column Start_Time_Dim_ID to the primary key of the view (identical column name). Student_ID is the PK of the Student_dim, and the column Student_ID is in the fact table, with FK relationship. It is a basic star schema, and the two dates I need to do arithmetic on are in the dimensions. I have not gotten MSTR to use the fact table to join the two.
I want to help you to help me, so tell me what would make this easier...ddl code, diagram, you tell me.
Thanks.
Dave

 

[entaroadun]

You can't get the fact table to join to the dim tables in an attribute form expression or a fact expression. MSTR doesn't support it, for the simple reason that there may be multiple join paths between a fact table and its dim tables. MSTR simply doesn't allow it.

You have to find some way to get the columns you want onto the same table. Since in this case, the age of the student will be used as a fact in an average metric, I would get all of the columns onto the fact table.

There are 2 solutions: a DB view that joins the fields you need onto the fact table, or ApplySimple correlated subquery. The view hides any complexity from MSTR; MSTR thinks that all of the columns you need are on one table. Correlated subqueries do the same thing, except you don't need to maintain a view. You need to maintain ApplySimple SQL on MSTR.

It depends on your org which is better. I'll give you the ApplySimple solution.

ApplySimple("(MONTHS_BETWEEN((SELECT T.TIME_DATE FROM START_DATE_VW AS T WHERE T.START_TIME_DIM_ID #0), TO_DATE((SELECT S.CONTACT_BIRTH_DATE FROM STUDENT_DIM AS S WHERE S.STUDENT_ID = #1), 'YYYYMM'))/12)", START_TIME_DIM_ID, STUDENT_ID)

To understand this solution, you need to understand Apply statements and fact expressions and entry levels.

First of all, MSTR doesn't examine what's in the double quotes. It simply passes through the text you provide. It could be nonsense, and MSTR won't stop you.

MSTR just looks at the column objects that you provide for arguments. The biggest requirement is that all columns can be found on the same logical table. In the expression above, both columns can be found on the fact table. Therefore, MSTR will accept the expression.

MSTR then looks at the attributes attached to the fact table to determine the fact entry level.

Because you are using columns from the fact table, MSTR treats this ApplySimple fact the same way it would treat your Days_to_Complete fact. Both facts are attached to columns on the same table.

I should probably write a FAQ...

 

[dlutzker]

entaroadun - you have done it again.
It works, but even better than that you explained how it works. Yes, you should write an FAQ. If I am not mistaken, relating two dimension values without the fact table is not that strange a thing to want to do.
Thanks again.
Dave