BI Data Analytics

Environment: Cognos 10.2.2 Database: Oracle Issue: When report is run in html or excel 2007 format, dates appear to be fine whereas when run in Excel 2007 data or csv format, date appears to be in time stamp format. Cause & Resolution: Oracle dates are interpreted as Time Stamp in Cognos. You need to cast them at model or report level to date. On layout, date formatting was applied hence Date format appeared fine on Excel 2007 and HTML ouptuts.

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

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.