BI Data Analytics

With Tableau 10, native Google Sheet connector is available. Below are some of the simple use cases for day to day work/events to handle: Personal Spending Tracker: Visualize , Track Daily Expenses Vs Earnings with different categorization stored in Google sheet No need to maintain multiple copies of same data to create new charts or dependent on other spend tracking apps Consolidate Information from Web: Import Tablular information in google sheets using function IMPORTDATA(url) and display or use on Tableau Use as Trip Planner like tracking/Survey Analysis: Use Google Form to store survey data into spreadsheet and analyze/visualize attendance/surveys/invitation in Tableau for an event

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: Ol

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 paren

Below are few tips to set up Excel for Data Analysis: How to create table and name it? In Excel Tab, click on any cell with data in it and press Ctrl +T. Excel will automatically select all columns and rows that have data. Click OK to create Table. In Tab, click on any cell that have data. In Top Menu Bar, select Design Tab. In top left hand side of screen, find table name option to change name from Table1 to Name you want to change to. How to activate Excel Solver add-in? Select File - Options - "Add-Ins" Option and click GO button Check the box labeled "Solver Add-in" and click OK Navigate to Data Tab and see new button for Solver Add-in How to enable Developer Tab to view macros? Select File - Options - Click "Customize Ribbon" On the right-hand side of the Excel Options dialog box, check the Developer checkbox and click OK Check that  DEVELOPER  menu has been added to the ribbon. Shortcuts: # ALT+F11 - To invoke VBA Edi

Requirement: Grant  Access permission to Library Tab only to RAVE developers Solution: By Default, Library Administrators Members can access, import, and administer the contents of the Library tab in IBM Cognos Administration.  However. User should already  be part of Portal Administrator. Go to Admin - Security tab to make below changes: Add required group/member to Cognos Predefined role of Library Administrator Add required group/member  to Cognos Predefined role of Portal Administrator Now Developer should be able to see Library Tab in Admin Console.

Below are some of tips on how Cognos 10.2.2 behaves to resolve issues quickly:- In Native SQL used in RS, it doesn't accept ";" as select terminator. When you copy main report and drill through report together and paste in same folder, drill through link starts using new links drill through report During deployment, if report is imported in different location and copied to target location, it can break report view links. This is changed behavior in Cognos 10 so best practice will be to keep source and target location same during export and import of deployment package. When we use Native SQL in RS, and in source query we apply aggregation functions, report can fail after migration. To circumvent this issue, use native sql based query as source query (without applying any aggregation). Other way is to push query to FM so it becomes virtual source query for further aggregations. When pulling a field in FM, if it is put as Fact, it can't be used for Chart x axis d

Problem: Setup HTML link on email Solution: Create Email ID data item in report query [Report Query].[Email ID]  Drag singleton with Email ID field and set property visible to NO. In Singleton, drag Hyperlink object and change properties Text Source:  Source Type = Report expression  Report Expression: Email ID field URL Source Source Type = Report expression Report Expression: 'mailto:'+[Report Query].[Email ID] 

This Post contains quick tips for various tools used in Project work. Database:  DB: Oracle 11g How to Extract Year from Time Stamp: Select   extract(YEAR FROM A.DW_CRTE_TS) from  table  A How to Extract year of previous day from Time Stamp: Select   EXTRACT( YEAR FROM A.DW_CRTE_TS -1) from  Table A Note: The page will be updated for more tips based to help in BI implementation and Data Analysis

Steps: 1. On report page create Prompt Button with Type as "Back". 2. Add HTML item with below script before button --< script type="text/javascript"> function  ReturnLink() {     y=getFormWarpRequest().elements["cv.id"];     // if the report is running from reportStudio or the Portal objects are different     if(y.value == "RS")         oCVRS.rvMainWnd.executePreviousReport(-2);     else         oCV_NS_.rvMainWnd.executePreviousReport(-1); } --< /script> --< A HREF="#" onClick="ReturnLink()"> 3. Add HTML item with --< /a> after Back Button. Result: Now using Button, you can go back to previous page in all cases.

Requirement: In IBM Cognos 10.2.2 summary Bubble Rave Chart is created to show distribution across regions. There is need to open Intranet Web page by passing document number directly. Please note that opening webpage in iframe is denied by security set up. Solution: Normal drill through functionality can pass data item value to another report and create URL. Using JavaScript, we can concatenate strings to form complete URL containing Document Number. At the same time, we can close drill through Report automatically so that the solution looks transparent to end users. Steps: 1. Create Drill through report with prompt value to pass from summary page. Create query to parse parameter. 2. Create Value prompt and add HTML item with below code. String for URL depends on destination URL structure. var form = getFormWarpRequest(); var dropDownL  = form._oLstChoicespromptOper; dropDownL.remove(1); dropDownL.remove(0); var jsObj = dropDownL.options[0].value; document.write(

Requirement: Users request to provide all report name with Report URL and possible prompt default selection so that they can embed in their external web page for user publishing in the way they want to organize. Report Search path that can be obtained from Audit database: /content/folder[@name=’Samples’]/folder[@name=’Models’]/package[@name=’GO Data Warehouse (analysis)’]/folder[@name=’Report Studio Report Samples’]/report[@name=’Revenue by Year’] Case I: When XSS checking is not enabled, below Cognos Proven Practice document can be used to understand how URL is formed: http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/scripting_techniques/using_urls_in_cognos_8.pdf Case II: When XSS checking is enabled for highly secured intranet environments. Below link provides what needs to be done to encode URL to be compatible:- http://queryvision.com/custom-cognos-url-support-and-cross-site-scripting/ Design: You can use Cognos to create a report to do the encoding

In Excel, there is funciton to remove duplicates; however, didn't find direct method to identifying duplicates based on specific columns. Below is step created to find out duplicate ID columns for further analysis: Column A contains all Customer IDs. We need to identify how many rows are generated for a customer id. Step 1: Sort based on Column A Step 2: Add 3 Columns with below calculations =COUNT(MATCH(A10,A11,0)) as B10 =COUNT(MATCH(A10,A9,0))  as C10 =IF(B10=1,1,IF(C10=1,1,0)) or B10+C10  as D10 Now You can use D10 to filter on 1 to list down all duplicate records for further analysis for reason of what values are different or any other purpose.