BI Data Analytics

How to check comments for columns in table/View in Oracle It's important to maintain column meta data so that Business Analyst can understand source of column and business meaning. To get metadata for columns.  select * from all_tab_columns where table_name = 'ViewName' To check comments for columns: select  * from ALL_COL_COMMENTS a where A.TABLE_NAME = 'ViewName'  Validate each column have comments. The step is simple but important in migration of table/views. To get date of Last month for comparison SELECT  TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) AS LAST_DAY_LAST_MONTH FROM DUAL;   Use in where clause:   TRUNC (A.CALENDAR_DT) <  TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) To create timestamp select 'ora.EDW_DT' as txt, to_timestamp('10-Sep-02 1:10:10.123000') as DW_UPDT_TS from ora.D_DT

Below are steps to ensure access are granted: Identify template for DBA if any exist and use to send information Identify which role you need access for specific select access Make sure user get connect accesss to avoid below error ORA-01045: user ... lacks CREATE SESSION privilege; logon denied Get temp password and change using Toad Test for select access for requested tables/views

Problem: While running report, logo images are visible in html and when try to download in pdf/excel output, logo image is not available. Solution: Logos are copied to only gateway web contents. Below steps were performed on Cognos Cloud Images were copied to dispatchers. URL path used is relative in original report working in windows but not on Linux. Change is from “\” to “/”. “..\pathtoimage\image.gif” For Linux, it needs to be changed to : “../pathtoimage/image.gif”

The  recommended approach would be to cast date in model specifically instead of relying on default behavior. If Oracle is reporting database at back end for Cognos, Cognos reads Oracle date as Timestamp.  Therefore, in FM, all dates columns need to be cast as date to match format.   In Cognos 10.2.2, cast works locally not at database level. It should be done for all other query subjects to bring consistency and predictability even if we go to different database or higher version of Cognos.   

We can pass dates as number for drill through report if there is  range filter is involved from in built date prompt: Steps: 1.        In Detail report, Create data item where filter is applied on date range [IntegerDate] extract(year,[Date1]) *10000 + extract(month,[Date1])*100+extract(day,[Date1]) 2.        In Detail report, Apply filter - [IntegerDate] between ?date1? and ?date2? 3.        In Summary report list query add below data items: Start Date: extract(year,[date1]) *10000 + extract(month,[date1])*100+extract(day,[date1]) End Date: extract(year,[date2]) *10000 + extract(month,[date2])*100+extract(day,[date2]) 4.        Update Drill through to pass new data items for ?date1? and ?date2? For single date passing via value prompt, it can be passed as To_Char/cast to compare and pass.

Below are few observations from day to day working on IBM Cloud Environment. Features: IBM using saml namespace to provide single sign on for users. It is done using script in back end. Cognos is using Apache Mode gateway for connections Cognos Framework Manager settings uses additional encryption settings to make connection secure Users are required to be added in Roles for both saml namespace as well as AD group ( in case they want to continue to use AD group). This means My folder of both ids are separate. Report performances are faster on Production environment and caching helps. Session parameter for user account includes complete email id instead of user name and hence scripts/functionality related to it needs to be changed. With Encrypted security enabled and Soft Token usage, Cognos can be accessed like any other website to consume data instead of going via remote logon to system.  Have Common deployment folder across environments Issues/Work around: ...

I tried to implement below solution to achieve multi cascading prompt requirement when there are more than 2 dependent prompts:- http://www.ibm.com/developerworks/library/ba-pp-reporting-scripting_techniques-page673/ It worked beautifully on IE with Cognos 10.2.2 FP3 along with sorting function. There is one issue users came back with in this approach. Issue # 1 1. It was not working on Report page. To solve this, created a small lookup table for prompts and use back button on report page to go back to Prompt Page.  In Query itself, created a dummy query to get ALL combinations. Used it to union with prompt data query to get it in multi cascading selection. Issue # 2 Second issue popped up as we press Back button, JavaScript runs again and populate all possible values in child prompts though selected values are still highlighted. This results in situation when user can select a combination from child prompt to get zero records. As soon as User clicks on any p...