Reminder

All Sessions are Recorded and saved to the OneDrive location listed above.

Cognos FAQ

Add to Cognos FAQ page:

• How do you know what AD groups have what access to what folders in what Cognos environment.

• For customer testing, is it better to send them to Cognos DEV > Business User Development or Cognos QA. Add pros & cons.

• Can I build joins in a data module? Yes. If your team has a specific need that is repeated across multiple reports then go ahead and do the join the the data module.

• Is there a limit to the number of queries, sub-queries, joins and union you can put into a Cognos report. Technically no but the more you put into your report the longer the report will take to run.

• What’s the different between run Excel and run Excel Data? Excel includes formatting and all tabs, Excel Data removes the formatting but only shows for first tab.

• When do I need a data module. When you want to blend multiple data sources. Excel + Excel. Package + Package. Excel + Package.

• How many data sources can I put in a data module? We don’t know yet but we’ve see data modules with up to 5 data sources.

How do I add a blank column to my report?

Add a new column using Query Calculation set to null.

Reminder

All Sessions are Recorded and saved to the OneDrive location listed above.

Open Q&A

Walkthrough of using Style Variables to hide columns on your report. High-level steps are:

1. Prompt: Create a value prompt (multi-select, checkbox, static values). The choices should list out all the columns in your report.
   

2. Variables: Create boolean (true/false) variables for each column that checks to see if the column name is in the checkbox prompt.
   Example calculation below in which “P_Report Fields” is the parameter, “Term” is one of the options in the prompt:
   ParamDisplayValue('P_Report Fields') contains 'Term'

3. Report: Set the Style Variable on each column. Set the variable conditional state to “False”, then set the “Box type” to “None” for both the header and the field.
   

4. To see the hidden field back on the report component (List) clear the conditional state.

Q: Does the user have the ability to save the report with their choices?
A: Yes. They can create a report view in their “My Content” or a to share it, create the view in a “Team Content”

Q: Data Module Error: Query Service internal error has occurred, please see the log for details.”
A: Submit a ticket to BIA (busintel@ucsd.edu)

Reminder

All Sessions are Recorded and saved to the OneDrive location listed above.

Cognos upgrade vs FP

Upgrade: has a larger impact

Fix Pack: address specific issues, must smaller impact

The reason for the FP: Qualys Vulnerability Scan is reporting all the Cognos environments running version 11.2.4 FP3 with a Level 5 vulnerability, need to update to IBM Cognos Analytics 11.2.4 FP4.

Update Cognos DEV with FP4 on Thursday 8/1 at 5pm-6pm

ETA on Cognos QA 8/7 and Cognos Prod 8/9

Performing UAT - This is the most important way to participate in AH growth

How to do UAT in Cognos

1. Logic enhancements

   1. Run the report using Hana Prod, run the report using Hana QA, compare the results

   2. Recommend using 2 browsers, one for using Hana QA and one for using Hana Prod

   3. Recommend running to Excel and use vlookup to compare, if QA = Prod then FALSE (issue found)

   4. Recommend running to Excel and put in same tab then sort same, if QA = Prod then FALSE (issue found)

2. New fields

   1. Will only exist in Hana QA so you have to use a QA/UAT package

   2. Option 1: start a new report, select Hana QA

   3. Option 2: add to an existing report that is built from 1 package, point report at UAT package, add the field, run using Hana QA

   4. Recommend - check the formatting, check the data type, check the auto-summation, check the math, does it match the original data source

Recommendation: have a list of know exceptions that you can test and some simple

AH User Acceptance Testing (UAT)

Recommend: after reports are migrated to production delete old reports or QA reports and schedules that are no longer needed

Monthly Releases

1st Thursday of each month = EAH/FINAH/FACAH enhancements go to UAT

2nd Thursday of each month = EAH/FINAH/FACAH enhancements go to Prod

3rd Thursday of each month = RAH/SAH/PAH/COMPAH enhancements go to UAT

4th Thursday of the month = RAH/SAH/PAH/COMPAH enhancements go to Prod

Reminder

All Sessions are Recorded and saved to the OneDrive location listed above.

Report Optimization

Cognos Optimization Tips

Reminder

All Sessions are Recorded and saved to the OneDrive location listed above.

Cognos Joins & Unions

Joins: When you take two tables that are related by at least 1 field (the key) and connect them based on that key so that the tables line up horizontally. The end result is a single table with more columns than the original table.

There are different types of joins.

Unions: When you take two tables that are aligned in columns (same number of columns, same data type in the matching column) and combine them so that they are stacked on top of each other. The end result is a single table with more rows but the same number of columns.

Note: You can stack more than 2 tables in a union at a time.

When do I just a join vs a union?

Do I want more columns or more rows in the end result?

Do I have matching column (or can I create them)?

Do I have a key (or can I create it)?

Unions Use Case: Cognos DEV > Team Content > Samples > Reports > Standard reports > Cognos Multi-Query Unions for Burn Down

Cognos FP3

Successfully applied Friday 5/24 with no issues.

Reminder

All Sessions are Recorded

Fields with long text strings

Tip 1: Use No Wrap

Open

Tip 2: Use substring to limit the long text field to 120 characters or less to fit the comment into Excel. Use substring to display the characters in the string from the 1st position to the 120th position.

substring([FieldName], 1, 120)

Is there possibly a way to build so that reports don't break when fields change names?

No. Unfortunately not. This is why BIA multi-month process that we use whenever we have to change a field name.

AH Renaming and Deprecation Process

Padding

Adds white space around a object.

Open

Classes

Cognos formatting that can be applied to objects.

Cognos Audits

Audit package > Run Reports

Looks at what reports are run in each Cognos environment, how long the report took to run, if the run was successful, who ran the report

Audit_P = looks at Cognos Prod

Audit_D = looks at Cognos DEV

Audit_Q = looks at Cognos QA

BIA is looking to hire a student worker to build Audit reports this summer

What Cognos Audit reports do you want to see?

Top users

how many times our reports are run and which areas

Most run Views, Most run Reports

number of views by report, department, and VC area

direct email communications to top report users

Reminder

All Sessions are Recorded

Cognos Health Check

Recommendations include: increase memory, change some server settings, delete the My Content of employees who are no longer at UCSD.

Recommendations: use newly provided IBM tools to monitor more of the Cognos environments.

Recommendation is for all teams to review their Cognos content (DEV, QA, PROD) and delete anything that is no longer in use.

If you are the owner in Cognos then you can move or delete content. If BIA is the owner you can email busintel@ucsd.edu to request the move or deletion.

BIA Services Form

https://support.ucsd.edu/its?id=sc_cat_item_guide&sys_id=e333d2481b67e9d4484f968f034bcbf5

AH Issue Last Week

Issue: Cognos & Tableau reports were running long and/or failing.

Cause: A Tableau report was using all the CPU on Hana.

Resolution: BIA asked the DBAs to cancel the Tableau queries and that caused everything to go back to normal.

1. There is a limit on Hana that limits how much data can be pulled

2. In Cognos BIA can see and cancel reports that use a lot of resources. BIA will then work with the person running the report and the report developer to identify ways run the report without using so many Cognos or Hana resources.

3. In Tableau BIA can only see the queries that are being sent to Tableau. BIA can’t see which report is using those queries. BIA can see what reports were being run on Tableau servers at the time of the issue but cannot see which reports were being run on desktops in development mode. BIA may reach out to key developers based on which Hana Views were being used in the queries. Only DBAs can cancel Tableau reports that are using up resources.

4. Everyone is running against Hana Prod, which has the data everyone wants so we want people to be using Hana Prod.

5. BIA is monitoring the frequency and cause of each of these issue. We will continue to work with report developers and the vendors to optimize the reports and the environments.

Cognos Folders & Security

You can have subfolder in Cognos w/ different security.

Each folder should have a unique audience (a unique set of AD groups).

If you want different AD group security then you need a different folder.

You can have multiple AD groups per folder and multiple folders can use the same AD group.

Reminder

All Sessions are Recorded

Data Modules

Data Module created from 2 Excels.

When the files are in the same folder they unlink after a migration to QA.

When the files are in their own folder they stay linked.

Best Practice: Your migration should include both the data module folder and the file location folder (migration set up by BIA). The Excel should move from Cognos DEV to Cognos QA/Cognos PROD in order to keep the connection.

Best Practice: Uploaded Excel must be moved out of My Content and into Team Content.

Cognos Health Check

IBM is working with BIA to evaluate a mirror of Cognos Prod to provide us with recommendations changes to improve performance.

Goal: Improve the health and speed of Cognos environments. Right-size Cognos to our current user base.

BIA is currently testing the recommendations in our mirror environment to evaluate the impact of these changes.

Recommendations include: increase memory, change some server settings, delete the My Content of employees who are no longer at UCSD.

Recommendations: use newly provided IBM tools to monitor more of the Cognos environments.

Is there a way to freeze the headers?

Not like there was in the past but you can attach the scroll to the list instead of the page and it will act like a frozen header.

Select full list > Properties > Scrollable area height = 600.

Note, this is not an option for cross tabs.

Is there a way to add upper or lower limits to charts?

Chart > Properties > Baseline > + to add a new line based on Query Calculation or Numeric Value or other.

Reminder

All Sessions are Recorded

BIA Extra Services

If you ask BIA by emailing busintel@ucsd.edu you can get

1. A list of all the fields the customer can see in your reports per folder, including the field name your customer sees and the original field name, the calculations used

2. What AD groups have access to your folder including read/run/write setting

3. ?? Can we document data modules like we document reports??

BIA Limitations

1. Automate a lookup for AD groups per folder that report developer can access on their own, including read/run/write setting

Cognos Health Check in January/February

BIA is working with IBM to determine if our Cognos environment is set up correctly to optimized usage

Field Definitions

Creating a demo of how to pass field header to report that will show the definitions.

If you have definitions, email busintel@ucsd.edu.

• If the field and definition has gone through FACAH DDC then they will be added to definition table (aka metadata table)

• If the definition is new then we’ll send it to the Financial data steward for approval and security level

Data Modules

Q: In data modules can you filter using a subquery?

A: Data modules and report use different coding processes, so no, the data module won’t use a filter from subquery. You can create the same output using a join in the data module where the larger list can have 1 or 0 matches. After the join you can create a flag for

if [LongList.Name]=[ShortList.Name] then (1) else (0)

Require one of two prompts

The DOPEs Report makes you pick an option before picking values. Can you have the same results without picking an option?

See recording for brainstorming.

How to add Upper and Lower control limit in Line Graph? Is there any example report I can look at?

Will reply via email.

Gayathri Narayanan