BI Data Analytics

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...

Changes in SQLs while migrating from Oracle to DB2 to improve performance: Instead of nvl or coalesce function use case statement Instead of using select clause within select statement, create joins with sub queries Don't use subqueries in where clause Creating small tables for required columns and joining them results in faster performance Use "AS" to qualify names Define Amount Type to decimal(15,2) to match with DB. They may be dropped while doing aggregations.

Below are issues identified  while working with IBM Cloud team PDF Font sizes are different. Fonts to be installed by IBM Audit DB not configured correctly. Extra set up done to change system Timezone to match with location Drill Through functionality not working. Interim Fix was provided having Multiple Data source Connections causing issue to work with reports having native sqls and based on data modules. Custom Controls path was changed making all reports using them not working.  Performance issues in running report. Environment moved to nearest location from original location. DASHDB ( Db2 Datawarehouse On Cloud)  performance tuning done for reports taking longer time compared to Oracle. Navigation to Cognos Administration is slow. Cache parameter is updated to improve on performance.

To have meta data information for an application available for analysis or troubleshooting,  All tables'metadata information should be filled.  At Cognos end, Framework manager should be single source to house all information with following below structure:- Database Layer: Description – Add data dictionary description from ETL  with example Model Comments – Any changes to types, relationships Screen Tips – N/A Business Layer: Description – Add Tables involved for Query subjects Model Comments – Any joins, data types update for using it, format etc Screen Tips:- What’s Business meaning and anyway to join to get information Presentation layer/Dimension Layer: Mostly Shortcuts. Can contain Report Names, Packages intended to be used in. Adhoc Layer: Hide columns not needed and reorganize based on Business Area

To demonstrate easy of use for quick analysis and spotting issues, you can provide a dashboard with below features: Dashboard running without Navigation and App bar Dashboard running in Expand mode Option to set as Home Drill down /up in a table/chart Show Treemap with Repeat Rows Global Filters and Tab specific Filters Filters on Canvas - Keep them limited Use List as selection and associating information Use of Info-graphics to compare figure across static categories Define calculation for available data set  I think, Cognos dashboard are useful to reuse modeling done in FM and do exploratory analysis to find out answers quickly. This can also be helpful to define which chart will suit for representing data. For Advanced Dashboard with download features, Interactive Mode reports are suitable. So once you define key metrics to keep monitoring, it will be easy for analyst to provide requirement and comparing with detail reporting.

Here are few learning to make  data module work:- It's better to create query subjects in FM with prompts. That way we can create dataset of smaller size for modeling and avoid using bulky packages. This will also enable to reduce  Always create data set names with query subject name so that easy to define data lineage Instead of using package in data module, try to use data set created based on packages. Arrange dataset in folders based on dependencies so that they can be refreshed in order. Handle nulls either FM or Data modules using package ( use clean up) before creating datasets to avoid unexpected data type errors.  It's important to define right data type for data items for specific purpose to avoid incorrect interpretation and aggregation by data module and data sets. You may need to define different layer of data module to rejoin tables so it's best to move aliases and join to FM to keep Data Module levels to 2-3. Will keep adding tips as time pe...

Finally, 1000s of pdfs  created using Cognos for Projects are available on internet for everyone to look at. Go to IADB website and select a project:- https://www.iadb.org/en/project/PR-L1022 Select Documentation Tab and expan Implementation section. All pdfs are generated and published with Cognos. Biggest challenge was to bring all data from front end application with almost no modeling and define in separate pages with conditional and data-centric formatting customization in constrained time limit.  All these were handled at report level and SQL based modeling. Thanks for everyone involved and their efforts to support! This is one of three major solutions delivered for external partners.