+34 671518985 info@primecubeit.com

If you are removing or renaming one of your logical tables, you always want to make sure that all objects that use this table in manual queries are listed so you can either remove them or change the query to contain new name of a table. We came up with some tricks to identify these objects that can be easily used.

  1. Freeform SQL

These are the “easiest” to be identified. Freeform reports are objects that among other dependent objects have a logical table.

There are two ways to list them: using a classic search – looking for reports with logical tables in them or query them in MD. We decided to go with the “classic search” example for this post, because rarely any MicroStrategy developer has access to the Metadata.

  • Activate “Contains” tab in Search options

 

  • In “Contains” tab in “Contains any object of this type” select “Logical Table” and in “Object Types” tab select “Report” and then click “Find now” 

 

  • The list of objects that will be returned are all objects that contain logical tables. Select them all, right click and select “Project documentation”. Follow the wizard to generate a report on these objects.

 

  • Once the report is generated it will have a list with all SQL that each of these reports contains. Now with simple search you will look for the name of the table that needs to be changed and with this you can identify the Freeform SQL report

 

If you are removing or renaming one of your logical tables, you always want to make sure that all objects that use this table in manual queries are listed so you can either remove them or change the query to contain new name of a table. We came up with some tricks to identify these objects that can be easily used.

2. Logical tables

To identify these we needed to use a simple Command Manager script. We noticed that when a table contains the SQL query, when we list its properties with “LIST ALL PROPERTIES FOR TABLE …” CM line, warehouse table name field is empty. The procedure below takes as a parameter project name (sProject), iterates through all project tables and lists all that contain custom query.

If the project contains a lot of tables, the script might take some time. In order to, like in Freeform SQL case, be able to search for the name of the specific table in all of them, instead of going through them one by one, the CM procedure needs to be modified to move all found logical tables into one folder and then generate a project documentation on them from a search tool. If you are interested in this enhancment, please let us know in comments.

           parameters: sProject (String)

ResultSet oTables = executeCapture(“LIST ALL TABLES FOR PROJECT\””+ sProject + “\”;”);

oTables.moveFirst();

while(!oTables.isEof()) {

String sTableName=oTables.getFieldValueString(DisplayPropertyEnum.NAME);
ResultSet oTableProp = executeCapture(“LIST ALL PROPERTIES FOR TABLE \””+ sTableName + “\”FOR PROJECT\””+ sProject + “\”;”);
oTableProp.moveFirst();
String sWT=oTableProp.getFieldValueString(DisplayPropertyEnum.WAREHOUSE_TABLE_NAME);
//printOut(sWT);
if(sWT == null || sWT.isEmpty())

{ String sID=oTableProp.getFieldValueString(DisplayPropertyEnum.ID);_ _printOut(sID); }

oTables.moveNext(); }

3. Datamarts

We wrote an SDK script to be able to list all SQL of all datamarts in one output. The script takes as a parameters following values and their examples:

  • Server name (localhost)
  • user(administrator)
  • password(pass)
  • project name (“MicroStrategy Tutorial”)
  • ID of the search created only with datamarts (A721664D47F2F911A70B53B5BF5380A9)

           Search created only with datamarts corresponds to ID of a following search object:

package standalone;

import com.microstrategy.web.objects.WebFolder;
import com.microstrategy.web.objects.WebIServerSession;
import com.microstrategy.web.objects.WebObjectInfo;
import com.microstrategy.web.objects.WebObjectSource;
import com.microstrategy.web.objects.WebObjectsException;
import com.microstrategy.web.objects.WebObjectsFactory;
import com.microstrategy.web.objects.WebReportData;
import com.microstrategy.web.objects.WebReportGrid;
import com.microstrategy.web.objects.WebReportInstance;
import com.microstrategy.web.objects.WebReportSource;
import com.microstrategy.web.objects.WebSearch;
import com.microstrategy.webapi.EnumDSSXMLExecutionFlags;
import com.microstrategy.webapi.EnumDSSXMLObjectTypes;
import com.microstrategy.webapi.EnumDSSXMLResultFlags;
import com.microstrategy.webapi.EnumDSSXMLStatus;

public class DatamartSQL {

public static WebIServerSession session;
public static WebObjectsFactory factory;

public static void main(String[] args) throws WebObjectsException, IllegalArgumentException{
// TODO Auto-generated method stub

//System.out.println(“Hello word!”);

//Create connection to IS
factory = WebObjectsFactory.getInstance();
session = factory.getIServerSession();
session.setServerName(args[0]);
session.setLogin(args[1]);
session.setPassword(args[2]);
session.setProjectName(args[3]);
System.out.println(args[0]+” “+args[1]+” “+args[2]+” “+args[3]+” “+args[4]);

WebObjectInfo woi;

woi = factory.getObjectSource().getObject(args[4], EnumDSSXMLObjectTypes.DssXmlTypeSearch, true);

WebSearch mySearch = (WebSearch)woi;

System.out.println(“name: ” + mySearch.getDisplayName());

mySearch.setAsync(false);
mySearch.submit();

WebFolder resultsFolder=null;

try {
resultsFolder=mySearch.getResults();

for (int i = 0;i < resultsFolder.size();i++){
WebObjectInfo result = resultsFolder.get(i);
System.out.println(“++ result name: ” + result.getDisplayName());
System.out.println(“++ result ID: ” + result.getID());

WebReportSource oRepSource;
WebObjectSource oObjSource;
WebReportInstance oRWInstance;
//get report source
oRepSource = factory.getReportSource();

//specify report ID for Yearly Sales by Quarter
String strReportID = result.getID();

oRepSource.setResultFlags(EnumDSSXMLResultFlags.DssXmlResultXmlSQL);
oRepSource.setExecutionFlags(EnumDSSXMLExecutionFlags.DssXmlExecutionGenerateSQL);
try {

//create report instance
oRWInstance = oRepSource.getNewInstance(strReportID);
oRWInstance.setAsync(false);
oRWInstance.setMaxWait(120000);
oRWInstance.setPollingFrequency(250);

int rpStatus = 0;
rpStatus = oRWInstance.pollStatus();

if (rpStatus != EnumDSSXMLStatus.DssXmlStatusResult) {
System.out.println(“Error:report status=” + rpStatus);
}

//retrieving report SQL from WebReportData object
WebReportData rpResultData = oRWInstance.getResults();
String strSQL = “”;
strSQL = rpResultData.getSQL();
System.out.println(strSQL);
} catch (WebObjectsException woe) {
System.out.println(woe.getMessage());
}

}

} catch (WebObjectsException ex) {}

}

}

Share This