BI Data Analytics

   Below are some finding while testing done for Tableau against IBM Db2 on cloud: Tableau seems to work better when using standard model instead of custom sql. Union is having performance issues. In some cases using cross join gives better performance results  Multi-language label changes solution is not extensive and need to create multiple worksheet and formatting compared to Cognos Analytics where you can assign query at Page level to place calculated columns anywhere.

Since now IBM Cloud OPS team supports Cognos Environment and there is need to open ticket and have delays in response times to detect issues, we can set up Job to run some quick reports to run every 5 minutes. While scheduling, need to keep in mind Reports total tun time covers 3-4 minutes run time so that Job will keep running all the time. Exclude times in schedule where maintenance/backup going on.  Reports to be run in sequence so that it doesn't impact actual reports run by users. You can set Job run history to more time to see it on Page itself  You can set up to send an email via even after job completion to include run time. When you stop getting emails, it means there was an outage/issue in the environment. You can create Audit report to check mean, median and mode for Job run time performance to compare run times against.   This method can test dashdb and report run times consistency; however, actual navigational/interactive report run times can't b...

There are multiple factors which come in place when we look at performance tuning. Below are two broad categories to investigate:   Environment: Cognos, Database, OS, Network. Application - Report, FM, Database, Data model, Data   Depending on how old application/changes happened, you can target specific area to find out issues quicker.   Quick things to check Environment compatibility Client Drivers are higher version than DB version. Generally Client Drivers are backward compatible and have new features as well if DB is up to date O/S Patches, Application Server are compatible with Cognos Applications Queries running from DB client vs Cognos - may be Cognos is doing more local processing Enable IPF trace to see how queries are being sent and getting responses

Problem: When a large queries fired from Cognos to Application database,  there is always question or confusion who is client running the query ( Adhoc run) or identify which one of scheduled jobs  running on same database. Solution: DB2 is backend database for reporting queries, WLM_SET_CLIENT_INFO procedure can be added to Cognos Data source Command line parameter. In Cognos, this procedure command with user account parameter can be added in data source so that with each query, Client account name is sent as well. Now DBA can create script to capture information at DB end: Server ID Client ID DB ID Volume Read Rows Returned Cost of Query Cognos Team will get email for any volume breach happens and it is easy to detect and engage user to take further for fine tuning queries/model to prevent issues and improve performance.