KB442295 : New SQL Generation Wildcard (!i) for Job Priority

• Strategy

Starting MicroStrategy 11.0, an additional SQL generation wildcard is available which the displays the job priority of the report being executed.

SUMMARY:
Starting Strategy 11.0, an additional SQL generation wildcard is available which the displays the job priority of the report being executed:

Certified
ProductStrategy Support CaseStrategy Internal CaseDescription MSTR-Architect582972651745WH Catalog issues after renaming a WH Table via Architect as per KB39859: How to update warehouse table name in Strategy Architect 9.2.1MSTR-ArchitectNo Support Case747751Getting "Automation error" while updating schema and opening Architect wizard.MSTR-COM610163743373Transaction Services not working after the transport with Object ManagerMSTR-COMNo Support Case730318Error occurs in object prompt window after trying to select a different incremental fetch page when running a report in Web 9.3.0MSTR-COMNo Support Case732979Receiving error "Report 'XXX' can not be executed with its current structure. A report should not be defined in terms of itself" when creating a view filter after upgrading to 9.3.0 HF 2MSTR-COMNo Support Case742933Win64 9.3.1 | Win64 -Release- 9.3.155.056 I-server hit breakpoint in MHOBJSRV_64.DLL!CDSSSQLJoinTreeNode::hGetTables() Line 67 + 0x16 bytes C++MSTR-COMNo Support Case746331IServer hit an assertion in MHOBJSRV_64.dll!CDSSTemplateSubtotal::GetUnitsItemKeyMSTR-Desktop585079743256Metric definition: smart-function is always automatically activated if the metric-formula is changedMSTR-DesktopNo Support Case745342Error occurs while login to MSTR Desktop.MSTR-DesktopNo Support Case747759Derived metrics created in a report in Desktop are set as non-smart.MSTR-Engine615893744557"Why is an "Invalid Column Mapping Info SOAP" error thrown when running an MDX report in MSTR 9.3.0 while the MDX runs successfully directly in the cube?"MSTR-Engine625540754151Unable to execute a Multisource report if the report has on its template more than 2 MDX metrics from the same SAP Query/DSSTable with SAP VariablesMSTR-EngineNo Support Case731213performance on Analytical Engine when calculating non-aggregate metrics over i-cubesMSTR-EngineNo Support Case740294Not re-execute SQL when delete one DE element and check another DE element.MSTR-EngineNo Support Case750276Metric Format changes automatically in 921m 3-tier.MSTR-Flash Dashboards619803742088create derived metrics using IF and Case functions from Derived Metric Freeform DialogMSTR-Flash DashboardsNo Support Case743626The row height is smaller than others if its bullet value is negative.MSTR-Flash DashboardsNo Support Case746338Change visualization between normal graph types (not graph matrix) does not workMSTR-InstallationNo Support Case745290Error Could not load file or assembly 'MLJAPPCT' or one of its dependencies when open webserverMSTR-InstallationNo Support Case748687After uninstalling the Hotfix from 9.3.1 GA build Desktop and Web do not work.MSTR-InstallationNo Support Case752339Incorrect Web/Mobile server version displayed after applying 931 HF1MSTR-Intelligence Server626449746902Enhancement Request: Certify MSTR Platform on OS Red Hat 6.4MSTR-Intelligence ServerNo Support Case718382MemTest 9.3.1 | Win32 -Debug -9.3.134.056 - Object LeakMSTR-Intelligence ServerNo Support Case734588WIN64 9.3.1 | WIN64 - DEBUG - 9.3.151.056 - Memory LeakMSTR-Intelligence ServerNo Support Case740919If the parent folder of installer contain parenthesis, the installer cannot launch.MSTR-Intelligence ServerNo Support Case742455IServer crashes when “delete HL message” schedule admin task is running while a mobile user logs out of its sessionMSTR-Intelligence ServerNo Support Case745693931 (Mobile Prod WER dump) (iPad-3120)MSTR-Intelligence ServerNo Support Case746348Solaris server crash while opening mobile App with pre caching enabledMSTR-MobileNo Support Case740530change the logic of or remove entirely the mobile version check on the server sideMSTR-Object Manager (Admin)619616744531Multisource reports that contain MDX objects are not migrated correctly through Object ManagerMSTR-SDK620771750709Extended Property Editor doesn't work with version 9.3.0, an error occurs: Unexpected error. Error is: 0x80042457. Description: The definition of object with type "Property Set" (ID : 64C03A70419A216CBMSTR-Server Administration615779746125Use command line to launch repositoru translation wizard (using xml)MSTR-System ManagerNo Support Case740496System Manager can't send email without specifying an outgoing SMTP server.MSTR-ToolsNo Support Case731126MSTR 9.3.0 Unable to configure iserver, Configuration Wizard keeps crashing.MSTR-Web595187679392MSTR 9.3 Web - Distribution Service Send Now From Folder Browsing Only Shows the 'expand page by' check box.MSTR-Web614760679392MSTR 9.3 Web - Distribution Service Send Now From Folder Browsing Only Shows the 'expand page by' check box.MSTR-Web617821738907The processing animation or aka wait (or busy) icon is not displayed when running a Document or Report in IE9 with HTTPS in an iFrame in version 9.3 it just appears and then disappears before the applicable page loads.MSTR-Web620417744482bootstrap sequence failedMSTR-Web623532679392MSTR 9.3 Web - Distribution Service Send Now From Folder Browsing Only Shows the 'expand page by' check box.MSTR-Web630557755340Would like to get Strategy certification for Firefox version 21.MSTR-WebNo Support Case730761Marketing Programs - Intro Workshop Exercise. “The Length of the unitkeyContext is different from that of the elemList”MSTR-WebNo Support Case733611Unable to click export icons in flash mode due to positioning of the icons on the grid.MSTR-WebNo Support Case735529In 9.3.1, we bring back by default all DB instances instead of only the ones marked as “available data mart, QB, FF and non-primary WH DB instances” for that projectMSTR-WebNo Support Case737888: No indication that another page is loading when using Edit Links between dashboardsMSTR-WebNo Support Case745388Processing icon is not visible clearly.MSTR-WebNo Support Case745666Can not reuse the catalog cache in web when click the same DB instance multiple timesMSTR-WebNo Support Case748815Reprompt button does not function after maximum row limit error or cross join error.MSTR-Web Services617059731587'com/Strategy/web/tasks/TaskInternalException' error connecting with trusted authentication from Office with J2EE MWSMSTR-Web ServicesNo Support Case747818dashboardviewer.swf deployed with Web Services needs network privileges removed!iJob priority of the report

This wildcard can be used in all Pre/Post SQL statements. Note that both lower case (!i) and upper case (!I) can be used in this SQL generation wildcard just like all other letter based SQL generation wildcards. Also note that this SQL generation wildcard is resolved in the Query Engine (after report execution), so the !i will not be replaced until after the report has been executed.
ACTION/RESOLUTION:
Here is an example of how this wild card can be used.
1. Use “/* !i */ select * from lu_year” as a Report Pre Statement1 for a report with Category on template:

2. When the report is run in SQL view only, the SQL is the following:
/* !i */ select * from lu_year
select a11.CATEGORY_ID CATEGORY_ID_2,
a11.CATEGORY_DESC CATEGORY_DESC
from LU_CATEGORY a11
This is expected as the !i SQL generation wildcard is not resolved in SQL Engine (after SQL generation, but before SQL execution).
3. When the report is executed against the database and the SQL view is seen then following SQL is observed:
Pass0 - Query Pass Start Time: 4/17/2018 4:36:17 PM
Query Pass End Time: 4/17/2018 4:36:17 PM
Query Execution: 0:00:00.01
Data Fetching and Processing: 0:00:00.00
Data Transfer from Datasource(s): 0:00:00.00
Other Processing: 0:00:00.20
/* 332 */ select * from lu_year
Pass1 - Query Pass Start Time: 4/17/2018 4:36:17 PM
Query Pass End Time: 4/17/2018 4:36:17 PM
Query Execution: 0:00:00.03
Data Fetching and Processing: 0:00:00.00
Data Transfer from Datasource(s): 0:00:00.00
Other Processing: 0:00:00.03
Rows selected: 4
select a11.CATEGORY_ID CATEGORY_ID_2,
a11.CATEGORY_DESC CATEGORY_DESC
from LU_CATEGORY a11
This is expected as the Query Engine resolved the Job Priority and replaced the !i SQL generation wildcard by the actual Job Priority (332 in this case).
Also see: KB10940: What SQL generation wildcards are implemented in MicroStrategy Engine?
Strategy reference numbers for this technical note are KB442295 and DE84254.

Comment

0 comments

Details

Knowledge Article

Published:

November 20, 2018

Last Updated:

December 31, 2018