Authoring for Performance Sandeep Mekala IRM UIS Cognos

Authoring for Performance Sandeep Mekala IRM, UIS

Cognos Structure for Analysis Cognos Server Architecture Presentation Cognos Connection Cognos Studios Framework Manager Data Warehouse (Dimensions and Facts) ETL/ELT Source Data (ICS, HCM, FIN, AD, DOM, ERA, DATC) 2

What happens when you run the report? 3

Level of performance • Database Tier – Table stats – Indexes – Summary Vs Detail Tables. • Framework Tier – Governors – Joins – Single schema • Report Tier – – – Report query Prompt query Query hints Query processing Filter functions 4

Query Hints Goal Child Element to Add to the query. Hints Element Overrides this Governor, if Set in the Model Warn about, prevent, or allow cross-product joins Warn about, prevent, or allow outer joins Specify whether and how to automatically sort level keys Specify that a request continues or terminates when a calculation or expression encounters a divide by zero error Retrieve either all rows or the first row when rendering the output Specify whether any of the query processing is performed by the database server cross. Product. Allowed outer. Join. Allowed auto. Sort avoid. Zero. Div Cross-Product joins Outer joins execution. Optimization query. Processing Specify whether rollup aggregates are computed by the database server, and if not, whether extended or running aggregates are computed Specify whether to allow the query to run concurrently or to run the query sequentially. rollup. Processing execution. Method Specify whether this query is given priority in determining parameter information. use. For. Parameter. Info Allow or prevent local caching local. Cache Allow usage of local cache Generate WITH clauses in the SQL, or generate derived tables instead use. SQLWith. Clause Stop a query from continuing if the length of time it runs exceeds the number of seconds that you specify Stop a query from continuing if the number of rows retrieved exceeds the value you specify max. Query. Execution. Time Use WITH clause when generating SQL Query execution time limits max. Rows. Retrieved Data retrieval limits Stop a query from continuing if the number of tables queried exceeds the value you specify max. Tables. Per. Query Report table limits Stop a query from continuing if the size of any text BLOB exceeds the number of characters that you max. Text. Blob. Characters specify Specify whether to suppress null values suppress Specifies whether to use explicit or implicit join syntax in IBM Cognos SQL. use. SQLJoin. Syntax Large text items limit Specifies whether to use the complete SAP MUN as the business key. use. SAPMUNAs. Business. Key Specifies whether to use the cache server for dimensions specified in the query. cache. Server. Query Specifies whether generated SQL uses parameter markers or literal values. use. SQLParameters SQL Parameter Syntax SQL Join Syntax 5