Versions Compared

Version Old Version 35 New Version 36
Changes made by

Saparnell

Saparnell

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Welcome to the Financial Analytics Community of Practice! Both Cognos,Tableau and other tool users are invited. This Community of Practice is a great place to get your questions answered and see real examples of how financial data is used at UCSD.

Monthly meetings: Every 2nd Friday of the Month at 10:00am.  Email busintel@ucsd.edu for an invite.

Having issues?  email busintel@ucsd.edu.

Burning questions? Teams > UCSD Analytics COP > Financial Analytics Community of Practice

Great enhancement Ideas? email busintel@ucsd.edu

Want to keep up on Financial Analytics news?  Join our mail list: https://mailman.ucsd.edu/mailman/listinfo/financial-analytics-cop-l

Someone you know need access?  FINAH Access for Developers and Consumers

Want to watch a prior session?  OneDrive > Financial Analytics CoP



Ui expand
title2021-05-14 Financial COP


Date, Time, Jira Time Tracking

2021-05-14  10am 

Jira Legacy
serverITS PRO
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb
keyACOP-365

All sessions are recorded.
FINAH Bugs & Enhancements

FINAH Bugs and Enhancements In Progress

May release to Prod yesterday

20210506 FINAH Start UAT Release Notes

Fields being retired - are you getting ready?

FINAH Cognos Case StatementsFINAH Cognos Case Statements.docx

Expenses discussion

General Ledger vs PPM

Equipment is an Asset.  The Managerial Hierarchy only includes revenue and expenses.  It doesn’t include assets or liabilities. 

The Managerial Hierarchy are taking some transfer account codes that are expenses in the Standard Hierarchy (in GL) and reclassifying them as resources in the Managerial Hierarchy.

PPM doesn’t have revenue.

UCSD General Ledger doesn’t have Managerial Hierarchy.

GL vs Managerial.xlsx

Enhancement

3 New fields to go with Managerial Hierarchy

Indirect Cost

Direct Cost

Total Cost

Would these be new fields or a new level?

Reconciliation View

How would the GL to PPM reconciliation view connect GL to PPM?

Karen Gerstoft; Lindsey Root; Daniel Redfern; Heather Sears; Nirvana Navarro; Anna Altman; Hang Phung; Isabella Parkinson; Adefante; Moh

RAH-Award-ViewView that does not contain additional rows past Award+Sequence Number.  Allows for faster reports b/c it contains less data.  We recommend using this view first and whereever you can for Kuali award reporting.



Ui expand
title2021-04-09 Financial COP


Date, Time, Jira Time Tracking

2021-04-09  10am 

Jira Legacy
serverITS PRO
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb
keyACOP-365

All sessions are recorded.
FINAH Bugs & Enhancements

FINAH Bugs and Enhancements In Progress

April release UAT started yesterday

20210408 FINAH Start UAT Release Notes

Renaming & Deprecation

New fields

Process Improvements for FINAH-ProjectCosts-View

Tableau Drill ThroughIrvin did a great presentation this week, check it out here: Tableau Community of Practice#20210406TableauCOP



Ui expand
title2021-03-12 Financial COP


Date, Time, Jira Time Tracking

2021-03-12  10am 

Jira Legacy
serverITS PRO
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb
keyACOP-365

All sessions are recorded.https://ucsdcloud-my.sharepoint.com/:f:/r/personal/jmwhite_ucsd_edu/Documents/ITS%20BIA/Community%20of%20Practice/Financial%20Analytics%20CoP/2021-03-12-Questions_CognosDrillThrough?csf=1&web=1&e=KeQaYc
FINAH Bugs & Enhancements

FINAH Bugs and Enhancements In Progress

20210311 FINAH Start UAT Release Notes

In FINAH-UCSDGeneral Ledger-View data package what does signed mean?

Any measure with 'signed' means that the value is either a positive or negative value.  Similar measures without 'signed' in the label will display the absolute value (always positive).

Could you explain what a null value in CBO Account Hierarchy L2 means?

It means that CBO does not include them in their hierarchy.  Bring in the Account Hierarchy L2 (non CBO) to see what these fields are related to.

Drill through

-Also in the Cognos CoP and Tableau CoP

How to connect two reports so that you can build a (1) summary report using a higher level view with less details and passing parameters to a (2) detail report that is filtered by the parameters so that it performs better.

Cognos filter to ask Target Detail report to listen for parameter from another report = [Project] in (?p_Project?)

Examples = Cognos DEV > Team Content > samples > Reports > Drill-through reports.



Ui expand
title2021-02-12 Financial COP

Attendees: Financial Analytics CoP attendees.xlsx

Date, Time, Jira Time Tracking

2021-02-12  10am 

Jira Legacy
serverITS PRO
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb
keyACOP-365

All sessions are recorded.https://ucsdcloud-my.sharepoint.com/:f:/r/personal/jmwhite_ucsd_edu/Documents/ITS%20BIA/Community%20of%20Practice/Financial%20Analytics%20CoP/20210212-Grainularity-LevelOfDetail?csf=1&web=1&e=WuCbGT
FINAH Bugs & EnhancementsFINAH Bugs and Enhancements In Progress
ProjectCosts Granularity

FINAH-ProjectCost-View Quick Start Guide

Cognos automatically does this for you but you can change the measure aggregation to 'none' in order to see the lowest grain.

Tableau aggregates for all the rows in the data, you have to use a Level of Detail calculations to tell Tableau what to sum and what to ignore.

{FIXED [Award Number],[Project Code], [Task ID], [Expenditure Category], [Expenditure Budget Period ID] : AVG([Task Budget Period Budget Amount])}

Aggregations in Cognos

demo - Cognos automatically does this for you
Level of Detail in Tableau

demo - must be created by the report developer

Top 15 Tableau LOD Expressions:  https://www.tableau.com/about/blog/LOD-expressions

Overview:  How Level of Detail Expressions Work in Tableau: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_overview.htm

Aggregate Functions in Tableau: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_aggregate_create.htm

Tableau's Order of Operations: https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm

Do you really need the detail?

Additional detail has been added to FINAH-ProjectCosts-View in order to reduce the amount of blending needed, but it does require additional report development in order to ensure correct totals.  What grain do you really need?  BIA can add measures to that view so that both tools automatically total at the level you want if you user a higher-grain view.

Send enhancement requests to busintel@ucsd.edu.

Next month: drill through

-Also in the Cognos CoP and Tableau CoP

How to connect two reports so that you can build a (1) summary report using a higher level view with less details and passing parameters to a (2) detail report that is filtered by the parameters so that it performs better.



Ui expand
title2021-01-08 Financial COP

Attendees: Financial Analytics CoP 2021-01-08.xlsx

Date, Time, Jira Time Tracking

2021-01-08  10am 

Jira Legacy
serverITS PRO
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb
keyACOP-365

All sessions are recorded.
Upcoming FinAH Cognos training

/wiki/spaces/ACP/pages/11175625

Tableau training has already completed.  This is the last of the Cognos trainings from the vendor.

ITS-BIA will still provide training for Cognos and Tableau but not in the 3-level, full day sessions.  Please email busintel@ucsd.edu to request training for your team (5-10 people).

You can also find contact information for the vendor on the training page and reach out to the vendor to purchase training or mentorship hours from them directly.

ITS BIA Blink resources

Home: https://blink.ucsd.edu/technology/bi/index.html

BI Users:  https://blink.ucsd.edu/technology/bi/users/index.html

BI Tools:  https://blink.ucsd.edu/technology/bi/tools/index.html

Cognos training:  https://blink.ucsd.edu/technology/bi/tools/cognos/index.html#Training

Tableau training: https://blink.ucsd.edu/technology/bi/tools/tableau/index.html#Training

Community of Practice: https://blink.ucsd.edu/technology/bi/communities/index.html

Oracle Blink resourcesOffice Hours:  https://blink.ucsd.edu/finance/support/training-events/officehours.html
FinAH Collab resources

Analytics Community of Practice (COP) home:  Analytics Community of Practice (ACP) Home

FinAH COP meeting notes: Financial Analytics Community of Practice

FinAH Bugs & Enhancements: FINAH Bugs and Enhancements In Progress

FinAH Quick Start Guide: FINAH Quick Start Guide

Financial Activity Hub home: /wiki/spaces/FinAH/overview

FinAH Enhancements coming next week 1/14

Jira Legacy
serverITS PRO
jqlQuerylabels = FinAH-UAT-Jan14
serverIdebe8394f-6d75-344c-8d9c-717cb778a1eb



Ui expand
title2020-12-11 Financial COP

Attendees:  https://ucsdcloud-my.sharepoint.com/:x:/g/personal/jmwhite_ucsd_edu/EYxlrL8Pkv1KlLrKC-lPcYMBl25cm-Afv6RVS7jPyDwrIQ?e=xNGLx1

Date, Time, Jira Time Tracking

2020-12-11 at 10:00am for ACOP-171
All sessions are recorded.https://ucsdcloud-my.sharepoint.com/:f:/g/personal/jmwhite_ucsd_edu/Et6liaoWHQtAoXQRoCKw-1IBzQ2_n2wRFR6DG3s9BLL5uA?e=AM7RTG
Released for UAT today will be FINAH-ProjectCosts-View enhancements and bug fixed
  • Requestor: Cheryl Kettnich    SNOW ticket: CS0337904
  • Requestor: Heather Sears      SNOW ticket: CS0294930
  • Requestor: Chester Yao         SNOW ticket: CS0333897
Q1 2021 focus for FINAH is Budget information

Budget re-occuring and one-time, plan and resource allocations - Currently in development to be added to FINAH, BIA is working with OFC and FIS

Needed at the project level, available in 2021 data but null for 2020

Oracle reports built by CBO

Oracle > UCSD Budget vs Actual

Oracle > UCSD Net Operating Results and Fund Balance Report

demo by Maria

Oracle Office Hours Support https://blink.ucsd.edu/finance/support/training-events/officehours.html

We are trying to get the "carry forward" amounts to show up in reports with no luck.  It is not on the new EBS report.  Do you have a way to get it on a Cognos report?

Maria:  FINAH-UCSDGeneralLedger-View > Account 1 Code and Name = "3000AA-Net Position/Fund Balance" use with Transaction Signed Amount, matches GL carry forward

Maria:  key field CBO Account Hierarchy L3 Code and Name

Maria: Oracle > UCSD Net Operating Results and Fund Balance Report

Isabella: FINAH-Project-View > Account

Heather:  June Expenses

FINAH-UCSDGeneralLedger-ViewTransaction Amount = absolute value of Transaction Signed Amount
FINAH Bugs and Enhancements

Page name has changed so any bookmarks will need to be updated.

FINAH Bugs and Enhancements In Progress

FINAH Quick Start Guides

FINAH Quick Start Guide

We are always looking to improve our documentation in order to support you, let us know if there is anything we should add.

Funding IssueDefinition:  the level where funding is issued, the individual amounts



Ui expand
title2020-11-13 Financial COP

Attendees:  Sarah Parnell, Sama Hosseini, Monica Mar, Anna Nordeen, Ellen Sanders, Emely Dominguez, Eric Del Rosario, Eva Thiveos, Gina Sunda, Gonzalo Urrutia, Hannah Van Etten, Heather Sears, Isabella Parkinson, Jason Yates, Jillian Kochan, John Hamilton, Kenny Valdivia, Lori Barry, Maria Obtera, Mark Walker, Russia Madden, Steven Lakes, Tami Stein, Tong Yoo

Date, Time, Jira Time Tracking

2020-11-13 at 10:00am for ACOP-171
All sessions are recorded.https://ucsdcloud-my.sharepoint.com/:f:/g/personal/jmwhite_ucsd_edu/EgJypyz6o7ZIno9Lh4jgKzsBwpqAeAJgtCUt_PR3rj5KTA?e=3oCmrX
FINAH Status

Went into Production on Friday 11/6

The Financial analytics mailist received notification that UAT had ended.

If FINAH Bugs and Enhancements In Progress appears blank to you then you haven't used Jira yet.  You can request Jira access here: https://ucsdservicedesk.service-now.com/its?id=sc_cat_item&sys_id=9af5d148db514450008c9837db96190b

FINAH Access
Coming enhancement for FINAH-ProjectCost-View

The FINAH-ProjectCost-View does not contain Awards/Project/Tasks with no expenditures.

We will be updating this with $0 for Awards/Project/Tasks with no expenditures.

Volunteers for FINAH Steering Committee - one per department.

The Financial Activity Hub Steering Committee will help shape the priorities and enhancements of the Financial Activity Hub by performing data quality checks; providing feedback to BIA, and participating in regular FAH Steering Committee meetings.

Details here:  FINAH Steering Committee

Email saparnell@ucsd.edu to volunteer for your group.


Demonstration of multiple layers of FINAH data

See Financial Measures example 20201030 FINAH UAT Start Notes

Tableau Level of Detail examples = https://www.tableau.com/about/blog/LOD-expressions



Ui expand
title2020-10-09 Financial COP


Date, Time, Jira Time Tracking

2020-09-11 at 10:00am for ACOP-171
Recordinghttps://ucsdcloud-my.sharepoint.com/:f:/r/personal/jmwhite_ucsd_edu/Documents/ITS%20BIA/Community%20of%20Practice/Financial%20Analytics%20CoP/20201009-FINAHStatus-OracleValidation?csf=1&web=1&e=vN3QH9
FINAH Status

Going into UAT on Wednesday 10/14: FINAH Bugs and Enhancements In Progress

The Financial analytics mailist will receive notification that UAT has started.

Cognos DEV > Financial Activity Hub > Financial Activity Hub Packages UAT using Hana QA

Tableau QA > Financial Activity Hub > FinAH Data Sources - UAT, each data source will say -QA and be pointed at Hana QA

If FINAH Bugs and Enhancements In Progress appears blank to you then you haven't used Jira yet.  You can request Jira access here: https://ucsdservicedesk.service-now.com/its?id=sc_cat_item&sys_id=9af5d148db514450008c9837db96190b

FINAH Access
How to validate Oracle vs FINAH-UCSDGeneralLedger-View?

BIA recommends you use the UCSD Expanded Budget Summary Report from Oracle directly.

In Oracle go to Reports and Analytics > Browse Catalog > Search for "Expanded Budget" > Look for the one with the report icon and add it to your favorites

How do we do UAT?
  • Cognos: Use the packages in Cognos DEV > Financial Activity Hub > FINAH Data Sources – UAT to build your report and select Hana QA.  Do not select Hana Prod as the report will fail.  Using Hana QA will allow you to see the below enhancements with Oracle Production data. 
  • Tableau: Sign into Tableau QA on your Tableau desktop tool and open your report.  Add a new data source with the same name and -QA at the end of the title.  Using this data source will allow you to see the below enhancements with Oracle Production data.
  • Hana QA:  FINAH data in Hana QA is production Oracle data loaded daily, just like in Hana PROD.
How much does Tableau cost?
Can we have a central location for important reports?  It's hard to navigate Oracle / Cognos / Tableau.Agreed, which is why there is a project to consolidate links to important reports onto one page (for example https://reports.ucsd.edu/).  This way the end users won't know if it is an Oracle report, Cognos report, Tableau report or other.



Ui expand
title2020-09-11 Financial COP


Date, Time, Jira Time Tracking

2020-09-11 at 10:00am for ACOP-171
Recordinghttps://ucsdcloud-my.sharepoint.com/:f:/r/personal/jmwhite_ucsd_edu/Documents/ITS%20BIA/Community%20of%20Practice/Financial%20Analytics%20CoP/20200911-FINAHReleases-ValidationQuestions?csf=1&web=1&e=grO68r
FINAH Status

Going into UAT on Monday 9/14: FINAH-ProjectPersonRole-View, FINAH-Award-View, FINAH-FinancialUnit-View, enhancement to FINAH-UCSDGeneralLedger-View

The Financial analytics mailist will receive notification that UAT has started.

Cognos DEV > Financial Activity Hub > FINAH - UAT

Tableau QA > Financial Activity Hub > FinAH Data Sources - UAT

FINAH Access
  • Must be an Oracle BI user with at least the UCSD BI Consumer JR role.  See details here: /wiki/spaces/FinAH/pages/53609469 and then email busintel@ucsd.edu to request being a FINAH Developer.
  • Working with FIS to automate the access process so that FINAH access is incorporated into their Oracle BI Access request form.
Where to find a list of fields per data source?/wiki/spaces/AH/pages/12487881
More Cognos & Tableau training upcoming:

/wiki/spaces/ACP/pages/11175625

Need access to the ACOP Training page?  Ask for SNOW Confluence access here: https://ucsdservicedesk.service-now.com/its?id=sc_cat_item&sys_id=9af5d148db514450008c9837db96190b

How to validate Oracle vs FINAH-UCSDGeneralLedger-View?

Irvin & Kathy are using Oracle > General Accounting Dashboard > Inquiry and Analyze Balances

In order to see the GeneralLedger data within Oracle you'll need access to General Accounting Dashboard > Inquiry and Analyze Balances



Ui expand
title2020-08-14 Financial COP


Date, Time, Jira Time Tracking

2020-08-14 at 9:30am for ACOP-171
FINAH Status

Going live with Production data in Hana Production / Cognos Production / Tableau Production this weekend for FINAH-UCSDGeneralLedger-View.

Data will be reloaded from Oracle Production daily between 6:30am-6:31am.

Later release will include sub-ledger information.

List of planned FINAH views here: /wiki/spaces/AH/pages/12487881

FINAH Access
  • Must be an Oracle BI user with at least the UCSD BI Consumer JR role.  See details here: /wiki/spaces/FinAH/pages/53609469 and then email busintel@ucsd.edu to request being a FINAH Developer.
  • Working with FIS to automate the access process so that FINAH access is incorporated into their Oracle BI Access request form.
FINAH in Hana QA Question

Do you all want Hana Test to have Oracle Test data or Oracle Production data?

  • Hana Test with Oracle Test data means you can go into Oracle Test and change data and then see those changes in Hana QA the next day.
  • Hana Test with Oracle Prod data means you can do any UAT against Hana Prod data.

Will send out survey to see how the community votes.

FINAH Monthly Release - SeptPPM - no new access requirements when PPM goes live in FINAH
Question:  Who is working with encumbrance ledgers in Oracle?Please email lbarry@ucsd.edu if you are or know an SME in regards to this question.
Where to find Oracle Office Hours for reporting support

https://blink.ucsd.edu/finance/fis-project/fin-training.html#Training-Office-Hours-

Look for Oracle BI & Financial Reporting.  Starting today the BIA will be joining the Oracle BI & Financial Reporting office hours to answer an FINAH questions.



Ui expand
title2020-07-10 Financial COP


Date, Time, Jira Time Tracking

2020-07-10 at 9:30am for ACOP-171
Recordinghttps://ucsdcloud-my.sharepoint.com/:f:/g/personal/jmwhite_ucsd_edu/EmhbuFP_zQREqql4JHV9-58BGtqSSh7dIfFFQ05zomDpSg?e=ZRJpKH
FINAH Status

With the success of the RAH go live and EAH go live, we are disappointed to be behind schedule with FINAH.  After consideration of year end close in IFIS, current stabilization of Oracle and remaining work needed to release the Financial Activity Hub, we estimate that the first wave of Financial Activity Hub will be launched on August 15, 2020.

Our goal is to go live with Wave 1 on August 15th with General Ledger and Advancement (STAR report) data in FINAH and Wave 2 on August 31st with PPM and Budget data.

User acceptance testing (UAT) started on July 9th with a small set of Beta Testers. As part of UAT we are gaining clarity on the needs around Budget data, Account Receivable (AR) and Accounts Payable (AP).  As FINAH development progresses we'll be able to include more users in UAT so that report development can being prior to go live.

Optional Cognos and Tableau training with FINAH data starts July 29 and extends through September 17.  Oracle access with a role of UCSD BI Consumer JR is a per-requsite to signing up for the Cognos / Tableau training.

For more details about FINAH see https://esr.ucsd.edu/projects/activity-hubs/financial/index.html

Beta Testing has started
Where to find Oracle Blink Pagehttps://blink.ucsd.edu/finance/fis-project/index.html
Where to find Oracle Access Blank Page

https://blink.ucsd.edu/finance/fis-project/fin-access.html

On the Oracle & Concur Access Request (Temporary Form) note the following:

  • You can select more than one option for most questions.  You should be able to fill out this form just once and request all roles and departments you need.
  • Use Standard Roles = UCSD BI Consumer JR (w/ 1hr training)
  • Use Elevated roles if you want to build reports in Oracle.
  • The second to last question is where you can indicate departments you need access to that are not your own.
Where to find Oracle Training

https://blink.ucsd.edu/finance/fis-project/fin-training.html#Live-Virtual-Instructor-Led-Cla

Look for Oracle: BI Tools for Report Consumers

Where to find Oracle Office Hours for reporting support

https://blink.ucsd.edu/finance/fis-project/fin-training.html#Training-Office-Hours-

Look for Oracle BI & Financial Reporting

Oracle In System Reports (OOTB)https://blink.ucsd.edu/finance/fis-project/data-reports/index.html#Oracle-In-System-Reports-(OOTB)
Question: where to find an Oracle to FINAH data dictionary?

1) We haven't built that yet.

2) It will be posted to our How to Get AH Data Page for both Report Developers and Data integration developers. Financial Activity Hub FCVs and Fields

FinAH Announcements/wiki/spaces/FinAH/overview



Ui expand
title2020-06-12 Financial COP


Date, Time, Jira Time Tracking

2020-06-12 at 9:30am for ACOP-171
Recordinghttps://ucsdcloud-my.sharepoint.com/:f:/g/personal/jmwhite_ucsd_edu/EvWRKsN9em9Cv2pMMpTohTgBs1-en3p0pT_yI_rf99dr3Q?e=iDuDRn
Where to find Oracle Blink Pagehttps://blink.ucsd.edu/finance/fis-project/index.html
Where to find Oracle Access Blank Page

https://blink.ucsd.edu/finance/fis-project/fin-access.html

On the Oracle & Concur Access Request (Temporary Form) note the following:

  • You can select more than one option for most questions.  You should be able to fill out this form just once and request all roles and departments you need.
  • Use Standard Roles = UCSD BI Consumer JR (w/ 1hr training)
  • Use Elevated roles if you want to build reports in Oracle.
  • The second to last question is where you can indicate departments you need access to that are not your own.
Where to find Oracle Training

https://blink.ucsd.edu/finance/fis-project/fin-training.html#Live-Virtual-Instructor-Led-Cla

Look for Oracle: BI Tools for Report Consumers

Where to find Oracle Office Hours for reporting support

https://blink.ucsd.edu/finance/fis-project/fin-training.html#Training-Office-Hours-

Look for Oracle BI & Financial Reporting

Oracle Roles to FinAH Access


Oracle RolesAccess to FinAH Data?Access to Develop Reports with FinAH?Access to other team's folders?
  • Standard Roles: UCSD BI Consumer JR w/ 1hr training

Automatically get access to FinAH data as a Consumer

Will need to request Report Developer access to

FinAH including Dept/Supervisor approval.

Folder Owner / Report Developer Discretion
  • Elevated Roles: UCSD BI Administrator JR
  • Elevated Roles: UCSD BI Modeler JR
  • Elevated Roles: UCSD BI Author JR
  • with 6 week training

Automatically gets access to FinAH data as a Report Developer

Automatically gets access to FinAH as a Report DeveloperFolder Owner / Report Developer Discretion

More information: Access and Security

FinAH Announcements/wiki/spaces/FinAH/overview
reports.ucsd.edu feedback
  • Users as assuming they have access and clicking on report
  • Cognos error message and redirect is confusing
  • Having to fill out the form multiple times to just get DOPE report is confusing.
Next monthFinancial Analytics Community of Practice will be cancelled for July.  We will instead collaborate with the Oracle Reporting Office Hours: https://blink.ucsd.edu/finance/fis-project/fin-training.html#Training-Office-Hours-



...