Where did that join condition come from?

Being a consultant, I see many different MSTR implementations and the following issue pops up ever so often.

A report that always works, suddenly... doesn't.

After checking the change log (no changes were made to the report) and making sure there is data in the warehouse, I find an interesting addition to the report SQL.
Where it was always:

SELECT a1.day
FROM
Fact a1
join LU_Date a2
on a1.day = a2.day

now it is...

SELECT a1.day
FROM
Fact a1
join LU_Date a2
on a1.day = a2.day 
and a1.deliverydate = a2.day

Where did the additional join condition come from?


Inexperienced MSTR architects don't always know that creating an Attribute can change the way MSTR generates SQL for each and every report, even if that Attribute has not been added to the report objects.

In the example above, a developer created a new attribute (probably to test) which linked the Fact table "deliverydate" column to LU_Date, instead of the LU_Delivery_Date table alias. Now MSTR sees that there are 2 keys linking to the same LU_Date table, so it just uses both of them, often resulting in incorrect results.

Because the Attribute does not need to be on the report to effect the SQL, how do we track down the problem Attribute(s).

We need to find all Attributes that uses the "deliverydate" column in the ID form, then see if any of them are linking to the wrong lookup table. I wrote a Command Manager script to go through every Attribute in the project and do the search for me.

  
  DisplayPropertyEnum iProperty = DisplayPropertyEnum.EXPRESSION;     
  ResultSet oAttributes = executeCapture("LIST ALL ATTRIBUTES FOR PROJECT '" + sProjectName + "';");    
  ResultSet oPropertySet = null;   
  String sFormName = "";  
  String sExpr = "";  
 int iCount = 1;  
 int iTotal = oAttributes.getRowCount();  
  oAttributes.moveFirst();    
  while (!oAttributes.isEof()){   
    String sAttributeName = oAttributes.getFieldValueString(DisplayPropertyEnum.NAME);    
    String sAttributePath = oAttributes.getFieldValueString(DisplayPropertyEnum.PATH);  
    oPropertySet = executeCapture("LIST PROPERTIES FOR ATTRIBUTE \"" + sAttributeName + "\" IN FOLDER \"" + sAttributePath + "\" FOR PROJECT \"" + sProjectName + "\";");   
    oPropertySet.moveFirst();   
    printOut(iCount + "/"+ iTotal);   
    iCount++;  
    ResultSet oAttributeForms = (ResultSet)oPropertySet.getFieldValue(DisplayPropertyEnum.ATTRIBUTE_FORM);  
    oAttributeForms.moveFirst();       
    while(!oAttributeForms.isEof()){  
       sFormName = oAttributeForms.getFieldValueString(DisplayPropertyEnum.ATTRIBUTE_FORM_NAME).toLowerCase();  
       if (sFormName.equals("id"))  
       {  
       //get expressions
           ResultSet oExpressions = (ResultSet) oAttributeForms.getFieldValue(DisplayPropertyEnum.ATTRIBUTE_EXPRESSION_LIST);       
           if(oExpressions != null){   
             oExpressions.moveFirst();   
             while(!oExpressions.isEof()){   
                sExpr = oExpressions.getFieldValueString(0).toLowerCase();  
                if (sExpr.contains(sSearchExp.toLowerCase())) {  
                  printOut("\n");  
                  printOut("\n ======ATTRIBUTE =======");   
                  printOut("Name : '" + sAttributeName + "'");   
                  printOut("Path : '" + sAttributePath + "'");   
                  printOut("Expression : " + oExpressions.getFieldValueString(0));   
      execute("LIST PROPERTIES FOR ATTRIBUTE \"" + sAttributeName + "\" IN FOLDER \"" + sAttributePath + "\" FOR PROJECT \"" + sProjectName + "\";");   
                }  
                sExpr = "";  
                oExpressions.moveNext();   
             }         
           }   
       }  
      sFormName = "";  
      oAttributeForms.moveNext();   
    }      
    oAttributes.moveNext();   
  }   

If you call the procedure like this:

 EXECUTE PROCEDURE Search_Attribute_ID_Expr ( "MicroStrategy Tutorial", "order");  

It will return all Attributes, with their path, in the MicroStrategy Tutorial project, where the ID form expression contains the word "order" (case insensitive)

You can download the procedure here.