BI Data Analytics

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

Often there are different set of users to perform different activities using Cognos - Consumer of report but should be able to schedule report or use existing reports to build Cognos workspace or modify Cognos workspace. Based on set up of organization, one  user may have to play different roles and there is need to customize Role to suit to this security need. In Cognos 10, Customized Roles can be created and capabilities can be assigned to it. Various Groups or individuals can be added to it. As admin, it's always to have a test account to test the security set up while creating custom roles so that no additional access are provided and at the same time, to check that all functions are working correctly.

There are times when there are huge outputs generated for Compliance Requiement and Everyone doesn't have access to file system where Cognos saves output. Second case sometime we hit that somehow some outputs are not written to File System. If information is time critical to release and available in Cognos Content Store as well, it's manual efforts to download and save outputs for downstream processing. Downloading outputs from multiple Report Views is easily possible with MotioPI. Just select Cognos Output from tool, navigation to saved output location and download at desired LAN path!

In MotioCI, regression testing is organized using three kinds of nodes in the Cognos instance tree: • Projects • Test scripts • Test cases Main Objectives are Ø   To identify any change in SQL ( Data may look same but may cause issue in future if SQL behavior is changed) Ø   To identify any change in formatting Ø   To identify any change in data for predefined prompts selection ( Data should be available) via Output Stability assertion- can be used for same report or reports in different location within or other instance Ø   Any Environment set up issues Ø   Execution Time stability – to baseline performance issues Projects – You can create multiple projects to focus on specific application folders and packages. It’s good to name appropriately to  distinguish  them from other projects and all shown at same place ( projects are arranged in alphabetical order). You can use Project_<App Name>  to create project. Test scripts – To run multiple test cases

Attended MicroStrategy 10 Symposium today held in Atlanta. The new MSTR version is trying to solve BI problem of providing Agility with Governance and Security. The main exciting features for release 10 are: Unified Interface via MicroStrategy Desktop to do everything. This is very desirable to have end to end handling of data and move towards focus on data consumption without IT need.  Most interesting to me were  Data wrangling to clean up data and save it like Excel Macro to rerun for cleaning similar data (Reuse). Reduced (No) data modeling as it can connect to any source and do aggregation/cleanup on fly Connect with Maps (Out of the box ESRI maps to utilize for common cases) and ease of importing D3 maps Native connections and build Mobile application like independent App for external customers Native connections to Hadoop instead of Hive Replacement of Flash with HTML5 Have Trend Line, Time series, data mining functions from R prepackaged Performance is much i

Most of the time Cognos system can be tuned in-house if time is spent in below Failure Analysis by using Audit database Reports performance analysis - schedule vs on demand How much data is read by Users queries Cognos Logs analysis to capture any model errors Scheduled Jobs analysis Packages based error trend Space/RAM utilization to identify peak times With these inputs, first see queries, usages are appropriate and any governance is required. What's being run at peak time and can we distribute schedules (run "all at once" vs in sequence).  This would be start point to configure Cognos advanced settings, Cognos Environment settings, JVM Tuning for memory, concurrent threads, OS Tuning, DB Tuning and need for any regular Maintenance. These are quite involved steps but with these checked, we were able to reduce errors, failures by 100 % and pave way towards working on real issues and stabilize BI Platform.

Problem: Users creates schedules and leave it and as users move out, they start failing. It leaves up to admin to see if they are genuine failure vs just clean up. Solution: One use case used heavily to find out reoccurring failure schedules for specific user and clean up them in one go using MotioPI. Benefits: Give more visibility in hidden issues in system other than having clean administration Help in clean up in schedules done from My Folders.

QlikSense has below main components QMC - to manage everything Hub Contains Streams - To publish a content in classified Streams My workspace - User to store/create their own sheets before sharing QlikSense Desktop Similar to Cognos, now there is provision to have Library to keep limited additional informations users can use to create their own stories. QlikSense document file extension is QVF.

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.

Essentially Cognos Support scope can be classified in two areas for better management:- Reporting Applications Support Cognos Administration Reporting Application supports include Content Management and Version Control Regression Testing Impact Analysis for a change User Access/Query support Data Analysis and Data Provisioning Scheduling and Job monitoring Portal Page  Application support documentation including any integration Audit Reporting and review for performance issues, code errors Cognos Administration mainly includes Security and Access Control Advanced Settings tuning Cognos Errors analysis including DQM memory monitoring Product Compatibility Reviews and installs validation Interim Fix Install Upgrade Review DR/BCP Planning Integrations, File system Mount pints Cleanup, alerting automation Cube Builds System Metrics and Dispatcher monitoring Cognos Logs analysis Enable Traces and open PMRs; raise RFE System Capacity Planning for