BI Data Analytics

Requirement: To modify Bullet chart Sample Bullet color is not aligned with color standard. Remove labels from charts Remove Satisfactory and Good Category from charts as want to show only Actual and Target for Time dimension. Make it compact to avoid any large space utilization Before Change: Steps: visJSON Update Update Elements to remove Satisfactory and Good section Update color and fonts for Actual and Target sections in Elements Remove Categories from data section and rows values for removed categories Remove Categories from axis section Chart Area Update and Refresh Bundle Remove references from Chart Type Definition for categories Remove from String Resources Refresh Thumbnails Changed Bundle name to avoid overwriting of existing sample Upgrade Definition and visJSON Generate VizBundle Definition Import in Library Reopen Report Studio and use Convert Visualization or get from Tool Box. Tool Used: IBM Visualization Customizer

Below section in Rave Line chart defines symbol and it's size,                "symbolStyle":                {                   "size":8                }, It can be removed to get Line chart without any marker.

Environment: Cognos 10.2.2 High Level Steps done as follow:- 1. Open given Combination Chart as base in IBM Visualization Customizer Tool 2. Replace Bar Charts from data set sections, Elements sections with Line chart data 3. Change Colors for both data sources to show different colors 4. if needed, remove legend or position to appropriate place. After the change: Use: To show different measures of same dimensions in same chart for comparison.

Prerequisite: Download IBM Visualization Customizer from Analytics Zone corresponding to Cognos version Download Visualization Bundle either from Cognos Admin Console or from Analytics Zone (Free available) Bullet Chart Customization Example Chart Name: az.Bullet_10_2_2   Changes: Open Bundle in IBM Visualization Customizer to access vizJSON Line # 119/120 for measure value font size and make it bold At Line 388, change color for Target Bar chart Around line# 502 and 529, removed blocks for second bullet chart Save as different bundle name and validate Import to Cognos Admin console and validate via Rave Visualization for availability and user

MotioPI is Desktop admin tool for Cognos and can be used independently( MotioCI is separate tool). Below are typical use cases:- Ø   Download report outputs on local machines in bulk; Ø   Model recovery from Content Store Ø   Administration of Environment - o    Data source information o    Create Report/Objects inventory creation for each application o    Content store bulk cleanup of outputs/schedules o    Owner change for reports/schedules o    Dispatcher/Environment settings o    Report execution times o    Studio to view spec/models and make changes Use the following link to launch MotioPI: http://www.motio.com/products/piLaunch.do Before you launch MotioPI, please visit our getting started webpage at https://www.motio.com/products/pi/gettingStarted.do

High Level Set up steps details: 1. Need to register to Analytics Zone 2. Download tool from Downloads section 3. Get all samples for Cognos version from Analytical zone and keep at same place 4. Download Tool's documentation from link ( For Cognos 10.2.1.1) and follow direction:- http://www.ibm.com/support/docview.wss?uid=swg27039892

In Cognos 10.2.1, It is observed that when sorting is defined on series text data item (ascending) , it results in descending order. To achieve ascending sorting, in sorting for data item defined as descending and it worked! Not sure, why it is working just opposite but worth trying if you face same issue.

An issue with Application drill through sorting is observed in Cognos 10.2.1 Active Report. Summary Active Report Rave chart where I defined application drill through to different Active reports. When I see report on ipad for drill through is not in alphabetically sorting order. I checked if it is based on creation date or drill through name/label name or actual content store id; however, it is not sorting based on any of them. It is also not dependent on in between addition/deletion of drill through and there is no sorting options available on Rave charts.

Requirements: Prompt page and report are on same page for dynamic refresh User wants date to be selected for latest month available in Database   Solution steps: Calculated maximum month as latest month for report Sorted Data item in descending order and applied sorting on prompt ( auto submit = yes) Use Prompt API for RS to select latest month referring below link   http://www-01.ibm.com/support/docview.wss?uid=swg21639520     Issues Faced: Page was getting reset to default selection even we select different month. Solution: Add line of code in script using below reference: http://cogblogger.com/2014/10/javascript-running-code-only-once/ Issue Faced: Initial run of report is not filtering out data based on prompt parameter and showing aggregated value. Solution: Use of prompt macro to make default parameter as latest month and made filter "Required" instead of Optional. [Month Year] = #prompt('p_Month_Year', 'st

Version: Cognos 10.2.2 Problem: While running Active Report from Report Studio, sometime we get error in loading chart: 2204 An error occurred. " ActualWidth. Cause: Potential Browser issue in rendering while multiple mhtl files are already opened. Solution: Close Other mht files or, Run in schedule mode and verify that there is no other issue with charts Naming Conventions: In Cognos Active Reports, we can follow below naming convention for easy maintenance 1. Name all Controls - DataItem_ _ControlName 2. Associated Queries - DataItem_ _ControlName_Query 3. Variable - starts with "v" 4. Parameters - starts with "p" Share as Public variable so that CWA can use them. Application Drill Through: For passing parameters from summary Active Report to Detail Active Reports, we can use public variable, application drill through and Data deck in detailed report. This design is useful when volume is high and we need to break

Good mobile App browser doesn't show Cognos Admin Console in Launch Menu. There is need to monitor jobs on the go to monitor and start new as soon as few jobs are complete. To resolve this, we can simply create port let link from Cognos Connection and giving Target URL to admin console URL. Same can be used for easy navigations using portal pages.

Below issues were identified for MotioCI 3.0 upgrade   Content Store corruption --Engage MotioCI Regression Test cases going in Pending State - recycle MotioCI process Not able to sign in URL - IE Cache clearance

Steps for GC logs are as follows: 1) Navigate to /bin folder and locate a file named bootstrap_OSversion.xml file. 2) Make a backup copy of this file. 3) Edit the bootstrap_OSversion.xml file to put the following entry highlighted in bold. This settinng means that Cognos will create upto 10 files with 10MB size. ........ -Xms${dispatcherMaxMemory}m -Xverbosegclog:${install_path}/logs/gc.log,10,10000 -Xgcpolicy:gencon ........ 4) Implement the above setting in all components of the environment(CM, dispatcher) 5) Restart Cognos" Usage: Memory utilization by Cognos to identify patterns and detect rouge reports. Impact: Performance Overhead

Problem:   When Publisher task fails in between for large table due to any issue, it corrupts QVD file. When we try to rerun task after issue resolution, data is lost since QVD was actually incremental load and need to be restored completely.   Solution Best Practices: Set up QVD backups and restoration as Enteprise wide solution Identify QVDs having different types of load Document procedure for support and maintenance Steps: Restore QVD Restart failed task

Error: DPR-ERR-2002 Unable to execute the request because there were no connections to the process available within the configured time limit.   Troubleshooting:   Identify which server error is coming from Identify how frequent it is coming and which service throwing it. Identify type of reports (DQM Vs CQM)   Cause and Solution:   DQM report waiting  for a connection with report service longer than the specified timeout. The successful request flow is as follows   Run the interactive DQM report -> Get a connection with report service -> DQM report runs on Query service -> Query service gives the output to report service for rendering -> user received the output   In this case, the errors are generated at the second step i.e. getting a connection with the report service. This shouldn't be limited only to Query service i.e. your environment seems to be busy interactively which has little to do with the Query service memory. There are two possib

In my view, below are the steps involved in any type of BI project: Planning Prioritization Performing Tasks Iterate based on learning Better we are able to perform a step, subsequent steps become easier to achieve project deliverable and goals.  

  Problem: Even when using Auto generate test cases feature, it may take a while to generate test cases and it may fail for time out. Secondly, this would be frustrating if even after long time, no prompt parameters are imported.   Cause: It's possible its taking so long because there may be a large amount of prompts its going through.   Resolution: Deselect the "Attempt to Import Parameters from Reports, Views and Outputs" option Auto generate and do dry run to see which reports failed for prompts. Update those reports manually or cloning from other test cases to speed up process.      

MotioCI can be helpful to do impact analysis for any Backend DB changes. Steps: 1. Create queries for packages for each query subject. Please remember to put filters to limit data and have good performance. 2. Create test cases for each of them. 3. Run Test Scripts via MotioCI 4. Schedule them to run with comfortable frequency and failure notification. Once a test case fails, you can verify reason for failure (  table lock vs, DB changes) and get them corrected.

To keep up with upcoming Cognos fixes below links are useful: To check overall schedule: https://www-01.ibm.com/software/analytics/support/fixes/pbi_schedule.html Fix Pack lists ( below is latest Cognos BI 10.2.2 FP3): http://www-01.ibm.com/support/docview.wss?uid=swg27042898 Based on current versions, we need to evaluate fix list, open APAR, software version compatibility Environment and plan it. 

  Problem:   Content is spread over all folders and there is chance to keep getting new reports in Environment while upgrade testing is going on. Creating test cases and dividing in multiple scripts may be challenges and have more manual efforts as testing progress even with MotioCI.   Project Test execution approach steps Identify reports heavily used using Audit Reports. Identify reports not used in x months and exclude them from list for testing Move identified reports, queries, jobs, events, portal pages to a base project folders ( no need for application packages) Create test cases for data validation and scripts for each BIO execution Run a dry test and identify issues – prompt manual, report long runs If some reports taking longer time, they need to be parked in subfolder as long running reports and exclude from Reports test cases Identify assertions to be used for testing - typical is execution time stability and output comparison Repeat process to

Is there any change in Query Engine from current version to "To Be" version?  Any new components/services added and what fine tuning would be required for them or can we disable them? What's license cost for separating out roles as needed? How data lineage can be discovered in new solution? Is all Fix Packs/Interim fix included in the "To Be" version Is OS, DB, DB drivers, application server compatible with latest version? Are we focused on first more stability of existing report set or new features? Do we have data available for testing reports in Test environment? Which Tool to be used for Regression Testing and comparison Testing? How you will manage multiple environments and URL changes for any application integrations? Along with this, review fix list and known issues list to be aware and verify if any of the features are potential to be used at your end. Preparation is key and questions/demos required. FAQs should be created based on di

In large BI deployment, it is always challenging to define scope for Upgrade project testing as  new applications may be rolled out while upgrade testing is in progress as 80/20 rule. To define scope, we can follow this approach:- 1. Audit reports to collect all reports run in last 1 year covering different frequencies ( yearly, quarterly etc) 2. Audit reports heavily used by users - define more test cases for these reports 3. Identify key applications for Business and reports to have thorough testing including formatting, data and performance testing. Need to engage Busines stakeholders to identify each area to get test scenarios as well as reports that would have more complex logic and formatting

As number of product increases and provide features almost same but doing things differently, other than cost one critical question comes is skill set. Most of the time you would need to train your employees to work on it as hiring specialist may be costly and short term affair.  One thing I would think, organization or company should start looking is which type of people they have more (Left Brain Vs Right Brain). As top executive, you would need to see if a tool fits in mind set of people who would actually work on it. If a tool is associative ( Example), analysis requires people to be Right Brain oriented whereas if tool is based on relational or require programming, people using it for analysis should be Left Brain and you would see immediately difference. Either people can be trained or hired based on which way company decides to go in terms of tool selection. 

While setting up new data source connection ( SQL Server DB for example), we get errors in connecting with Cognos Framework Manager" Login failed Unable to attach to multi database   Few things required to check to resolve such issue Cognos FM is working fine for other data sources from Local Machine Cognos Data source is working fine User is able to connect from DB Client to DB with id. Local ODBC connection name should match with DSN name set up in Cognos Data source Use right ODBC connection file based on which one is set up in Registry. Examples:   Win 32-bit in Win64:  C:\Windows\SysWOW64\odbcad32.exe Win 32-bit in Win32:  C:\Windows\System32\odbcad32.exe    Create shotcut on local desktop for easy access and set up.

1. Check software OS/DB drivers/server compatibility with version you are installing http://www-969.ibm.com/software/reports/compatibility/clarity-reports/report/html/prereqsForProduct?deliverableId=1267644462529 DB DB2 Client - Only UDB LUW can be connected DB2Connect - can connect Mainframe DB2 as well. Apply Licenses for drivers and test connectivity IIS/Web Server O/S Version App Server version (if Tomcat is not used) 2. Plan to freeze Cognos Version to be installed (FP) http://www-01.ibm.com/software/analytics/support/fixes/pbi_schedule.html 3.  Plan to freeze Code and scope - Have parallel Development environment and after freeze date, all development should be done on. 4. Review Fix list and create test plan based on hot sites and specific issues environment has faced http://www-01.ibm.com/support/docview.wss?uid=swg27042898  5. Create Inventory List  using Audit report to clean up unused reports or define scope with frequent or

Database Migration impact Tasks checklist: 1. Account used should retain access to schemas 2. Verify if there is performance impact due to move 3. Is there any Data copy job need to be decommissioned and set up in new Server/DB 4. Catalog on Cognos servers and Cognos admin console 5. Catalog on Local machine or Citrix Clinets for FM, Transformer 6. Test for report functionality due to DB type change, FM in DQM, CQM mode 7. Validate version compatibility with Cognos for DB and drivers with correct license 

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