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.

https://ucsdcollab.atlassian.net/wiki/spaces/ACP/pages/11174953

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