Get the list of applicable properties for a specific datamart through Modeling service

GET {{baseUrl}}/api/model/datamarts/:datamartId/applicableVldbProperties

StartFragment

Get the list of applicable properties for a specific datamart. The list is returned based on datamart's subtype. The datamart's subtype must be normal or customsqlfree_form, an error is thrown otherwise. Internationalized fields will be based on user's displayLocale from MicroStrategy Library session.

EndFragmen

HEADERS

RESPONSES

status: OK

{"propertyCount":30,"applicableProperties":{"[VLDB Select].[Allow Cartesian Join]":{"name":"Allow Cartesian Join","groupId":1,"groupName":"Joins","maxValue":2,"displayType":"options","displayName":"Cartesian Join Governing","showSqlPreview":true,"options":[{"value":"Choosing this option keeps the backward compatibility with MicroStrategy 2019 where SQL queries are governed by \"Cartesian Join Warning\" VLDB setting.","sqlPreview":"OBSOLETE - Keep backward compatibility with MicroStrategy 2019."},{"value":"Cancel execution of cartesian join for SQL, MDX, In-Memory queries.","sqlPreview":"This VLDB setting only works for Data Engine Version 2020 or above. Choosing this option disallows cross join execution for SQL, MDX and In-Memory queries."},{"value":"Execute cartesian join for SQL, MDX, In-Memory queries.","sqlPreview":"This VLDB setting only works for Data Engine Version 2020 or above. Choosing this option allows cross join execution for SQL, MDX and In-Memory queries."}],"type":"int32","defaultValue":0},"[VLDB Report].[Drop Database Connection]":{"name":"Drop Database Connection","groupId":3,"groupName":"Pre/Post Statements","maxValue":1,"displayType":"options","displayName":"Drop Database Connection after running user defined SQL","showSqlPreview":false,"options":[{"value":"Enable","sqlPreview":""},{"value":"Disable","sqlPreview":""}],"type":"int32","defaultValue":0},"[VLDB Report].[Report PostStatement 1]":{"name":"Report PostStatement 1","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Post Statement 1","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PostStatement 2]":{"name":"Report PostStatement 2","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Post Statement 2","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PostStatement 3]":{"name":"Report PostStatement 3","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Post Statement 3","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PostStatement 4]":{"name":"Report PostStatement 4","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Post Statement 4","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PostStatement 5]":{"name":"Report PostStatement 5","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Post Statement 5","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PreStatement 1]":{"name":"Report PreStatement 1","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Pre Statement 1","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PreStatement 2]":{"name":"Report PreStatement 2","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Pre Statement 2","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PreStatement 3]":{"name":"Report PreStatement 3","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Pre Statement 3","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PreStatement 4]":{"name":"Report PreStatement 4","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Pre Statement 4","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Report PreStatement 5]":{"name":"Report PreStatement 5","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Report Pre Statement 5","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Session Based PostStatement]":{"name":"Session Based PostStatement","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Session Based PostStatement","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Report].[Session Based PreStatement]":{"name":"Session Based PreStatement","groupId":3,"groupName":"Pre/Post Statements","displayType":"n_line_text_box","displayName":"Session Based PreStatement","showSqlPreview":true,"options":[{"value":"","sqlPreview":"SQL BLOCK"}],"type":"string","defaultValue":""},"[VLDB Select].[Normalization For Normal Report]":{"name":"Normalization For Normal Report","groupId":4,"groupName":"Query Optimizations","maxValue":4,"displayType":"options","displayName":"Data population for reports","showSqlPreview":false,"options":[{"value":"Do not normalize report data","sqlPreview":""},{"value":"Normalize report data in Intelligence Server","sqlPreview":""},{"value":"OBSOLETE - Normalize report data in database using Intermediate Table Type (may be slower, but uses less memory in Intelligence Server. Switching to the next option is recommended in case user has table creation privileges in the warehouse database, else switch to the previous option)","sqlPreview":""},{"value":"Normalize report data in the database (can provide improved performance in scenarios where Intelligent Cube data includes a large ratio of repeating data, dimensions include a large number of attributes, and fact tables have been used to attribute lookup tables as well)","sqlPreview":""},{"value":"Normalize report data in the database using relationship tables (can provide improved performance in scenarios where Intelligent Cube data includes a large ratio of repeating data, dimensions include a large number of attributes, and attribute lookup tables are much smaller than fact tables)","sqlPreview":""}],"type":"int32","defaultValue":0},"[VLDB Select].[Parameterized Query for Text Input]":{"name":"Parameterized Query for Text Input","groupId":4,"groupName":"Query Optimizations","maxValue":1,"displayType":"options","displayName":"Parameterized Query Options","showSqlPreview":true,"options":[{"value":"Disable usage of parameterized query (this option should overwrite the parameterization setting defined on database connection)","sqlPreview":"\n                \n                    select a11.SUBCAT_ID SUBCAT_ID_INT,\n                    a12.SUBCAT_DESC SUBCAT_DESC,\n                    sum(a11.TOT_DOLLAR_SALES) Revenue\n                    from CITY_SUBCATEG_SLS a11,\n                    join LU_SUBCATEG a12\n                    on (a11.SUBCAT_ID = a12.SUBCAT_ID)\n                    where a12.SUBCAT_DESC = N'Book'\n                    group by a11.SUBCAT_ID,\n                    a12.SUBCAT_DESC\n                \n            "},{"value":"Allow usage of parameterized query according to database connection setting","sqlPreview":"\n                \n                    select a11.SUBCAT_ID SUBCAT_ID_INT,\n                    a12.SUBCAT_DESC SUBCAT_DESC,\n                    sum(a11.TOT_DOLLAR_SALES) Revenue\n                    from CITY_SUBCATEG_SLS a11,\n                    join LU_SUBCATEG a12\n                    on (a11.SUBCAT_ID = a12.SUBCAT_ID)\n                    where a12.SUBCAT_DESC = ?\n                    group by a11.SUBCAT_ID,\n                    a12.SUBCAT_DESC\n                    with parameters:\n                    Book\n                \n            "}],"type":"int32","defaultValue":0},"[VLDB Data Type].[Date Format]":{"name":"Date Format","groupId":7,"groupName":"Select/Insert","displayType":"one_line_text_box","displayName":"Date Format","showSqlPreview":false,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":"mm/dd/yyyy"},"[VLDB Data Type].[Date Pattern]":{"name":"Date Pattern","groupId":7,"groupName":"Select/Insert","displayType":"one_line_text_box","displayName":"Date Pattern","showSqlPreview":false,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":"'#0'"},"[VLDB Data Type].[Time Format]":{"name":"Time Format","groupId":7,"groupName":"Select/Insert","displayType":"one_line_text_box","displayName":"SQL Time Format","showSqlPreview":false,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":"hh:nn:ss"},"[VLDB Data Type].[TimeStamp Format]":{"name":"TimeStamp Format","groupId":7,"groupName":"Select/Insert","displayType":"one_line_text_box","displayName":"Timestamp Format","showSqlPreview":true,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":"yyyy-mm-dd hh:nn:ss"},"[VLDB Select].[Data Retrieval Mode]":{"name":"Data Retrieval Mode","groupId":7,"groupName":"Select/Insert","maxValue":1,"displayType":"options","displayName":"Data Retrieval Mode","showSqlPreview":false,"options":[{"value":"Only ODBC","sqlPreview":""},{"value":"Allow Native API","sqlPreview":""}],"type":"int32","defaultValue":0},"[VLDB Select].[Data Retrieval Parameters]":{"name":"Data Retrieval Parameters","groupId":7,"groupName":"Select/Insert","displayType":"one_line_text_box","displayName":"Data Retrieval Parameters","showSqlPreview":false,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":""},"[VLDB Select].[Decimal Separator]":{"name":"Decimal Separator","groupId":7,"groupName":"Select/Insert","maxValue":1,"displayType":"options","displayName":"Decimal Separator","showSqlPreview":false,"options":[{"value":"Use '.' as decimal separator (ANSI standard)","sqlPreview":"\n                \n                    insert into tablename values (3.5, 2.6, 1.5, 2)\n                \n            "},{"value":"Use ',' as decimal separator","sqlPreview":"\n                \n                    insert into tablename values (3,5, 2,6, 1,5, 2)\n                \n            "}],"type":"int32","defaultValue":0},"[VLDB Report].[Autocommit]":{"name":"Autocommit","groupId":8,"groupName":"Governing","maxValue":1,"displayType":"options","displayName":"Autocommit","showSqlPreview":false,"options":[{"value":"Enable","sqlPreview":""},{"value":"Disable","sqlPreview":""}],"type":"int32","defaultValue":0},"[VLDB Report].[Report Time Out]":{"name":"Report Time Out","groupId":8,"groupName":"Governing","displayType":"long","displayName":"SQL Time Out Per Pass (Seconds)","showSqlPreview":false,"options":[],"type":"int32","defaultValue":0},"[VLDB Select].[Results Set Row Limit]":{"name":"Results Set Row Limit","groupId":8,"groupName":"Governing","displayType":"long","displayName":"Results Set Row Limit","showSqlPreview":false,"options":[],"type":"int32","defaultValue":-1},"[AnalyticalEngineProperties].[EvaluationOrdering]":{"name":"EvaluationOrdering","groupId":9,"groupName":"Analytical Engine","maxValue":2,"displayType":"options","displayName":"Evaluation ordering","showSqlPreview":false,"options":[{"value":"Calculate derived metric/smart compound metric before derived elements/consolidation and all subtotals as smart - (formerly known as 6.x order)","sqlPreview":""},{"value":"Calculate derived metric/smart compound metric before derived elements/consolidation and all subtotals as non-smart - (formerly known as 7.x order)","sqlPreview":""},{"value":"Calculate derived elements/consolidation before derived metric/smart compound metric,\"total\" subtotal as smart and other subtotals as non-smart - (formerly known as 9.x order)","sqlPreview":""}],"type":"int32","defaultValue":2},"[AnalyticalEngineProperties].[NullCheckingForAnalyticalEngine]":{"name":"NullCheckingForAnalyticalEngine","groupId":9,"groupName":"Analytical Engine","maxValue":2,"displayType":"options","displayName":"Null checking for Analytical Engine","showSqlPreview":false,"options":[{"value":"Scalar calculations return NULL if any operand is NULL; aggregation calculations ignore NULLs.","sqlPreview":""},{"value":"Scalar and aggregation calculations treat NULLs as zero (Deprecated)","sqlPreview":""},{"value":"Scalar calculations treat NULLs as zero; aggregation calculations ignore NULLs (metadata upgrade required for this to take effect for versions 10.7 or later).","sqlPreview":""}],"type":"int32","defaultValue":2},"[MDX Syntax].[FF Ignore Empty Result]":{"name":"FF Ignore Empty Result","groupId":12,"groupName":"Freeform SQL","maxValue":2,"displayType":"options","displayName":"Ignore Empty Result for Freeform SQL","showSqlPreview":false,"options":[{"value":"Do not turn off warnings for freeform SQL statements with empty results, such as updates","sqlPreview":""},{"value":"Turn off warnings for freeform SQL statements with empty results, such as updates","sqlPreview":""},{"value":"Turn off warnings for freeform SQL statements that return multiple result sets with an empty first result set and return second result set, such as stored procedures","sqlPreview":""}],"type":"int32","defaultValue":0},"[MDX Syntax].[XQuery Success Code]":{"name":"XQuery Success Code","groupId":12,"groupName":"Freeform SQL","displayType":"one_line_text_box","displayName":"XQuery Success Code","showSqlPreview":true,"options":[{"value":"","sqlPreview":""}],"type":"string","defaultValue":"false"}},"sqlDynamicPreview":"[VLDB Report].[Session Based PreStatement]\n[VLDB Report].[Report PreStatement 1]\n[VLDB Report].[Report PreStatement 2]\n[VLDB Report].[Report PreStatement 3]\n[VLDB Report].[Report PreStatement 4]\n[VLDB Report].[Report PreStatement 5]\n\n[VLDB Create].[Table PreStatement 1]\n[VLDB Create].[Table PreStatement 2]\n[VLDB Create].[Table PreStatement 3]\n[VLDB Create].[Table PreStatement 4]\n[VLDB Create].[Table PreStatement 5]\n\ncreate [VLDB Create].[Table Qualifier] table [VLDB Create].[Table Descriptor] [VLDB Create].[Table Prefix] TABLENAME [VLDB Create].[Table\nOption] (ATTRIBUTE_COL1 VARCHAR(20) [VLDB Create].[Attribute ID Constraint] , FORM_COL2 CHAR(20), [Attribute non-ID Constraint] FACT_COL3\nFLOAT) [VLDB Create].[Table Space]\nprimary index (ATTRIBUTE_COL1, FORM_COL2) [VLDB Create].[Create PostString]\n\n[VLDB Insert].[Insert PreStatement 1]\n[VLDB Insert].[Insert PreStatement 2]\n[VLDB Insert].[Insert PreStatement 3]\n[VLDB Insert].[Insert PreStatement 4]\n[VLDB Insert].[Insert PreStatement 5]\n\n[VLDB Insert].[Bulk Insert String] insert into TABLENAME [VLDB Insert].[Insert Table Option] select [VLDB Select].[SQL Hint] A1.COL1 [VLDB\nSyntax].[Mid Name Alias Pattern] COLUMN1ALIAS, A2.COL2 [VLDB Syntax].[Mid Name Alias Pattern] COLUMN2ALIAS, A3.COL3 [VLDB Syntax].[Mid Name\nAlias Pattern] COLUMN3ALIAS from TABLE1 A1, TABLE2 A2, TABLE3 A3 where A1.COL1 = A2.COL1 and A2.COL4=A3.COL5 [VLDB Insert].[Post Insert\nString]\n\n[VLDB Insert].[Insert MidStatement 1]\n[VLDB Insert].[Insert MidStatement 2]\n[VLDB Insert].[Insert MidStatement 3]\n[VLDB Insert].[Insert MidStatement 4]\n[VLDB Insert].[Insert MidStatement 5]\n\n[VLDB Insert].[Bulk Insert String] insert into TABLENAME [VLDB Insert].[Insert Table Option] select [VLDB Select].[SQL Hint] A1.COL1 [VLDB\nSyntax].[Mid Name Alias Pattern] COLUMN1ALIAS, A2.COL2 [VLDB Syntax].[Mid Name Alias Pattern] COLUMN2ALIAS, A3.COL3 [VLDB Syntax].[Mid Name\nAlias Pattern] COLUMN3ALIAS from TABLE4 A1, TABLE5 A2, TABLE6 A3 where A1.COL1 = A2.COL1 and A2.COL4=A3.COL5 [VLDB Insert].[Post Insert\nString]\n\n[VLDB Insert].[Insert PostStatement 1]\n[VLDB Insert].[Insert PostStatement 2]\n[VLDB Insert].[Insert PostStatement 3]\n[VLDB Insert].[Insert PostStatement 4]\n[VLDB Insert].[Insert PostStatement 5]\n\ncreate [VLDB Create].[Index Qualifier] index [VLDB Create].[Index Prefix] IDX_TEMP1(STORE_ID, STORE_DESC) [VLDB Create].[Index PostString]\n\n[VLDB Create].[Table PostStatement 1]\n[VLDB Create].[Table PostStatement 2]\n[VLDB Create].[Table PostStatement 3]\n[VLDB Create].[Table PostStatement 4]\n[VLDB Create].[Table PostStatement 5]\n\nselect [VLDB Select].[SQL Hint] A1.STORE_NBR [VLDB Syntax].[Mid Name Alias Pattern] STORE_NBR, max(A1.STORE_DESC) [VLDB Syntax].[Mid Name\nAlias Pattern] METRICALIAS from LOOKUP_STORE A1 A1.STORE_NBR = 1 group by A1.STORE_NBR [VLDB Select].[SELECT PostString]\n\n[VLDB Report].[Report PostStatement 1]\n[VLDB Report].[Report PostStatement 2]\n[VLDB Report].[Report PostStatement 3]\n[VLDB Report].[Report PostStatement 4]\n[VLDB Report].[Report PostStatement 5]\n[Drop Table PreStatement 1]\n\ndrop table TABLENAME\n\n[Drop Table PostStatement 1]\n[VLDB Drop].[Post Cleanup Statement 1]\n[VLDB Drop].[Post Cleanup Statement 2]\n[VLDB Drop].[Post Cleanup Statement 3]\n[VLDB Drop].[Post Cleanup Statement 4]\n[VLDB Drop].[Post Cleanup Statement 5]\n[VLDB Report].[Session Based PostStatement]"}