Penn Computing

Penn Computing

Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn

 
  UPDATES
Current Load Status
Regular Availability
 
  INFORMATION
FAQs & Tips
Password Changer
Support services
Security
About the Data Warehouse
Data Administration
 
  DATA COLLECTIONS
Advancement
Assets
BBR
BRIM
Express Mail
Facilities
Faculty
GAR
General Ledger
Infrastructure
ISSS-iOffice
Learning Management
Position Inventory
Research-PennERA Proposals
Research Inventory
Salary Management
Student
Travel Expense Management
Tuition Distribution
World Travel
Cross-Collection

Financial Data User Group Meetings

 

October 21, 2013, 10AM
Terrace Room, Claudia Cohen Hall

  • Meeting Slides (Power Point pptx)
  • Agenda
    • In the works: BRIM changes
    • Refresher on Penn/Works Payroll distributions and encumbrances timing
    • Introducing: Travel & Expense Management (TEM) data
    • Tips & Tricks
      • InfoView & Webi reminders for new and not-so-new folks
      • Report show and tell – relating results from multiple queries

 

November 29, 2011, 1pm
Terrace Room, Claudia Cohen Hall

  • Meeting Summary (Microsoft Word docx)
  • Agenda
    • Warehouse website changes - coming soon
    • BusinessObjects InfoView & Webi – report show & tell and tips & tricks
    • For discussion:
      • Your needs regarding Travel & Expense reporting
      • Tips for monitoring PennWorks/Payroll distributions

March 1, 2011, 1pm
Terrace Room, Claudia Cohen Hall

Summary

  1. PennWorks Additional Pay data in the Warehouse: Addition to the PennWorks data available in the Salary Management data collection, containing information about additional pay requests processed via PennWorks (2/22/2011-)
    • Access is granted automatically to users of the Salary Management collection
    • Once granted access, users can see all request data where they have access to the recipient's home ORG, the ORG used in the distribution account, or the responsible ORG for the fund in the distribution account.
    • A new Additional Pay class in the RSALMGT universe provides access to data about requests including current status and routing history, as well as distribution detail.
    • Penn Works Additional Pay Reporting - new Business Objects Corporate document (available in Webi and Deski) can be used to monitor status and metrics related to Additional Pay processing


  2. eForms for Warehouse and BEN Reports access requests: Electronic access forms (Penn eForms - http://www.upenn.edu/computing/da/eforms) are now available for the Warehouse and BEN Reports
    • Easy to use with info (Name, title and contact info) auto populated into the form using Penn ID
    • On form validation of required information
    • Dynamic routing to supervisor via pick list
    • Automated email notification as request passes through the routing chain
    • Ability to view status of in-process and completed requests


  3. Business Objects Web Intelligence (Webi) Migration: It's underway - see the migration website (http://www.upenn.edu/computing/da/bo/webi) for details, including report conversion guidelines, How Tos, and more!

September 22, 2009
Benjamin Franklin Room, Houston Hall

Agenda

  1. Recent happenings:
    • Stimulus reporting - ARRA fund flag: A new universe object flags fund codes which are children of the ARRAUP fund parent; note that this includes all ARRA funds:
      • Federal Non-Letter of Credit (Parent Fund GCB009)
      • NIH Letter of Credit (LOCNIS)
      • NSF Letter of Credit (LOCNSS)
    • SecureShare: Try to refrain from sending confidential data via email, or unnecessarily saving data locally. Desktop Intelligence reports can be purged of data (using Data menu, View Data, Purge button). If you do need to send confidential data, use SecureShare rather than email attachments.
    • Desktop Intelligence Job in Use error (IE7 compatibility): Users of Desktop Intelligence who have Internet Explorer v7 installed may encounter this error, which your LSP can mitigate with a Windows registry adjustment.
    • Password Changer: The Oracle password change application now has an option to "select all", to save on clicking the multiple checkboxes needed to synchronize your database and Business Objects passwords.

  2. Tips and Tricks Real-Life Example: Many thanks to Laurie Fanelly, from Public Safety, for sharing her real life use of a report that combines Balances, GL Detail, and Salary Management snapshots headcount data, making it easier to manage her various sub-budgets all in once place. A similar report, called "Financial User Group 09222009 - combined Balances GL Detail Headcount.rep", in located in the Business Objects repository, and you can tailor it to your own needs. It has prompts for Center, Fund and Fiscal Year, which are used in each of the underlying queries. Click here for instructions on accessing the repository.


  3. FRES Work Requests Collection - NEW!: Data from the FacilityFocus application
    • What is it? The FRES Work Requests data collection is a subset of the University Data Warehouse containing data from the Facilities and Real Estate Services (FRES) Facility Focus system. The Work Requests data collection contains current and historic information about facilities work order requests.
    • Who can access it? Individuals who have access to the General Ledger collection in the Warehouse are granted access automatically to the FRES Work Request data with no access request form required. For those individuals who do not currently have General Ledger collection access, but wish access to the Work Request collection, the FRES Work Requests Data Access Form will need to be completed.
    • The collection, represented by the FRES Work Requests universe, consists of:
      • Work Request header and phase data (status, attributes, dates, extra description), refreshed daily, on Monday through Friday evenings.
      • Phase detail cost and hours data, refreshed weekly, on Thursday evenings, coordinated with Facility Focus load to the General Ledger
    • Reports available:

  4. Coming Soon - PennWorks: In November 2009, the Salary Management collection will be enhanced to include data from PennWorks. We'll have a smaller user group meeting, hopefully before the end of the calendar year, to review the new data and how it integrates with the collection.

    In addition to that, we'll be tidying up the classes and objects within RSALMGT. These changes will not adversely affect existing queries - those will still work, and objects will just be relocated within the universe to more logical locations:

    • Streamline navigation of existing classes
      • Employee Distribution moves to be a subfolder of Employee Job
      • Job Class moves to be a subfolder of Employee Job
      • Job Class General moves to be a subfolder of Employee General
      • Mail Codes moves to be a subfolder of Employee General
      • All lookup tables (COA reference tables, Job Class/General, Mail Codes, PennWorks lookups) will be available to be queried on their own
    • Apply consistent naming to existing object -- there are currently instances where objects or classes are referred to slightly differently, have varying capitalization, are located in inconsistent locations, etc. We'll also try to tidy up some of these inconsistencies so objects are located where and called what users expect.

December 10, 2008
Benjamin Franklin Room, Houston Hall

Agenda

  1. Express Mail Collection - NEW!: Data from the Express Mail application
    • What is it? The Express Mail data collection is a subset of the University Data Warehouse containing data from the Express Mail application. The collection contains current and historic information regarding packages shipped, their associated costs, and the General Ledger account that those costs have been assigned to.
    • Who can access it? Individuals who currently have access to the General Ledger collection in the Warehouse will be granted access automatically to the same organizations in the Express Mail collection and no access request form will be required. For those needing access to the Penn Mail Administration role, University wide data, or for users who do not currently have General Ledger collection access, the Express Mail Data Access Form will need to be completed.
    • The collection consists of:
      • Data refreshed approximately every 6 business days, usually on Monday evenings (transaction date, tracking number, shipment description, shipper PennID, COA account, charge description), represented by the Express Mail universe
    • Reports available:

  2. Coming this winter, to the Salary Management collection:
    • Employee table additions: Additional columns for First Name, Middle Name, Last Name; Exempt/Non-Exempt, Pay Status, Last Action Date, Last Pay Processed Date, Prior Pay Date, Current Job Indicator, Current Distribution Indicator
    • Job Class table additions: Job Class Status, Job Class Employee Type

  3. Recent additions to the Corporate Documents:
    • Petty Cash Balances FY: This report provides the balances and FYTD transaction line details for petty cash accounts within an Org(s). At the prompts, simply enter a fiscal year, accounting period, and an org code. You will only get the petty cash fund account balances and activity for Home Orgs you have access to. This query should be run for closed accounting periods in order to correctly determine the ending balance(s) and any activity that occurred in the accounts.
    • Org Wayward Summary, Org Wayward Detail: The ORG Wayward Summary Query is the first of two queries required to identify wayward transactions. The other query is ORG Wayward Detail. ORG Wayward Summary is a query that is used to alert the user of the ORG/Fund wayward transactions that have occurred and require attention.The ORG Wayward Detail provides the line item detail for the ORG/Fund combination shown in the Org Wayward Summary Query

  4. Desktop Intelligence Tips & Tricks:
    • Outline mode and folding: Breaks in Deski allow you to include subtotals, counts, averages, etc., for groups of data in your report. You can Fold a break to suppress detail (Format menu, Breaks, Fold checkbox) for all breaks. Alternatively, use Outline mode (View menu, Outline) to select specific break areas you'd like to display.
      • Tip - always remember to reset break totals after adding new breaks by going to Slice and Dice and double-clicking the calculator icon.
    • Combining from multiple sources: On a single tab, you can combine data from multiple queries (and multiple universes) in a report, so long they have a dimension in common, and you link then only on that dimension (using the Data Manager), and you use that dimension to create a section in the report. To makes this work smoothly over time, be sure to:
      • Use common prompts for the queries
      • Name the resulting tables or crosstabs so it's easy to tell them apart (Format Table or Format Crosstab)
      • Align the results tables or crosstabs relative to each other within the section (Format Table or Format Crosstab, Appearance tab), so they'll automatically reposition as data changes
      • Use the Map tab of the Report Manager to navigate through the report
      • Keep the results of the individual initial queries on separate tabs until you're comfortable with your results

April 7, 2008
Benjamin Franklin Room, Houston Hall

Agenda

  1. New to the Warehouse:

    • Assets Collection: Data from BEN Assets
      • What is it? The Assets data collection is a subset of the University Data Warehouse containing data from the BEN Assets module of BEN Financials. It contains current and historic information about building, fixed, movable and land assets at Penn.
      • Who can access it? In general, individuals must have BEN Assets access before they can be granted access to the Assets collection in the Data Warehouse. Those individuals who do not already have access to the source system (BEN Assets), but who need ad hoc access to the Assets collection in the Data Warehouse, must complete BEN Assets training along with the Assets Data Access Form.
      • The collection consists of:
        • Data refreshed nightly (assets, locations, categories, retirements, invoice data, change history), represented by the BEN Assets universe
        • Monthly snapshots (assets, locations, categories), represented by the BEN Assets Snapshot universe
      • Reports available:

    • Tuition Distribution
      • What is it? The Tuition Distribution Collection is a subset of the University Data Warehouse containing data from the Billing and Receivables System (BRS), Student, and Financial. Tuition Distribution is a detailed collection of data used to explain how tuition is distributed at Penn.
      • Who can access it? Those individuals who do not already have access to the Tuition Distribution data collection must complete Student Data Access Request Form and obtain supervisor, school access administrator and Student Financial Services (SFS) signatures.
      • There is a Knowledge Building class about Tuition Distribution data.

    • Planning Salary Detail: To help folks report on detailed salary budget/forecasts entered in Hyperion Planning, we've loaded that data to the Warehouse, where it's now available via the RSALMGT universe. Existing Salary Management collection access applies to this table as well. Further documentation will be coming soon.

  2. Corporate Document Changes changes: Rich Snyder from School of Medicine Research Services demonstrated the updated Fundsummary reports, and a new Payroll Grid report. These can be accessed via InfoView or Desktop Intelligence. Brief report descriptions:
    • Fundsummary: a summary of the entire grant fund similar to the BEN Financials 115 report (listing key attributes) and also lists totals by object code for Budget, Month, Fiscal Year, PJTD Actual Expenditures, Encumbrances and BBA. A separate tab is provided to break down expenditures further by ORG/CREF. Another tab is used to project what the unobligated balance as a percentage of the current year budget will be at the end of the current budget year. The calculation is based on the average expenditures per day. This report is useful for administrators preparing NIH noncompeting continuations since NIH will want to know if an unobligated balance greater than 25% exists. If the projection shows a balance greater than 25% of the current year's budget, the administrator needs to investigate further before answering "no" to the question about whether an unobligated balance greater than 25% exists. This is the quickest way to look at any fund in its entirety.

    • Payroll Grid (WESD Format): This query mimics the Web Enabled Salary Distribution (WESD) report that combines both future Fiscal Year payroll distributions with the actual FYTD salary activity for every monthly paid employee in a range of prompted Home Dept ORGs.

  3. Tips and Tricks:
    • Using Report Templates to apply consistent formatting (e.g., font size, page numbers, last run date): Format your report by adjusting font size, colors, wrapping, etc., and then save it as a Desktop Intelligence Template (.ret) file. You can then apply it to other reports, and also use it when prompted at the beginning of the New Report Wizard, when asked if you'd like to "select a template". In addition, you can put fields at in your header or footer to show report name and last refresh, along with page numbers. These use:
      • =DocumentName()
      • =LastExecutionDate(DataProvider([object]))
      • Special field for page numbering

    • Putting it all together: BEN Assets, report template, and variables too! --- see the "BEN Assets Taggers and Custodians" report in the BEN Assets folder in the Business Objects Corporate Documents. This report retrieves all assets for the selected center, and displays employees and counts of the number of assets for which the employees are custodian or tagging contact.

October 23, 2007
Benjamin Franklin Room, Houston Hall

Agenda

  1. Recent happenings:
    • Business Objects XIR2: We've completed our upgrade to Busienss Objects XIR2! If have any questions, or run into any difficulties in using the new version, upgrading old queries, etc., please contact Data Administration.


    • SSN access changes in the Warehouse: Effective 10/15/2007, most end users will no longer have access to SSNs in the Salary Management and Student data collections in the Warehouse. Business Objects universes now use Penn ID to join tables, and any attempts to display SSNs will return nulls.


    • EMPLOYEE_GENERAL: unsecured access to Full_Part_Time, Reg_Or_Temp, and Minimum_Record columns is now available

  2. Coming attractions - Fixed Assets: When the BEN Assets addition to BEN goes live next year, Assets data will be available in the Data Warehouse to assist in asset tracking, management and stewardship. The new data collection will likely be accessible via both Business Objects and BEN Reports.

  3. Tips & Tricks:

    Desktop Intelligence – tips:
    • Password Changer : When resetting your password, please be sure to check at least the following boxes, so that Desktop Intelligence/InfoView can run your queries successfully:
      1. Data Warehouse
      2. Business Objects
      3. Business Objects DB Credentials

    • Dates: When responding to calendar data prompts, be sure to use 4-digit years (e.g., 10/23/2007, not 10/23/07). Note, this does not affect accountig period formats, which appear as MON-YY (e.g., OCT-07).


    • Corporate Docs: Corporate documents are now accessible from both a category and folder view. Please be sure to import new copies of the documents, rather than trying to upgrade your old ones from version 5.


    • Default file save locations: By default, Desktop Intelligence will save files to \My Documents\My Business Objects Documents\userDocs on your hard drive. Note that this is a change from version 5, which used C:\Program Files\Business Objects\BusinessObjects 5.0\UserDocs as its default location.


    • Contexts: When running queries in FINQUERY, you may see a context box pop up, prompting you to select Balances or GL Detail. This appears because all of the reference tables (e.g., ORG Codes, Fund Codes, etc) are joined in the universe to both the Balances and GL Detail tables. Just think for a moment about whether you wish to retrieve account balance (Balances) or journal detail (GL Detail) information, and select the appropriate option. If your report contains multiple queries, the box may appear for each one.


    Desktop Intelligence – new features:
    • Lists of values: When refreshing queries that were built using prompts, you may now see friendlier lists of values from which to select your response. Or, you can of course still enter a value if you know what it is.


    • Save as Excel: Desktop Intelligence allows you to use the "Save As" feature to save documents in Excel, Adobe Acrobat PDF or CSV formats. Once you've refreshed a report, click on the File menu and select Save As. Select the file format you'd like from the "Save as type" drop-down list. Desktop Intelligence will then save your file with the data as it appears on the screen. This means the output will be saved and reflect section breaks, filters, special formatting, etc. The results of any variables or calculations will be saved as text, not underlying formulae. If your report has multiple tabs, and you choose to save as Excel, each tab will appear as a separate worksheet within one workbook. Similarly, multiple tabs will be accessible indidually in Adobe Acrobat (PDF) files. InfoView users can perform a similar action. If you experience undesired changes in Excel color formatting when overwritting a Excel file previoulsy saved from Deski, save it instead to different file name.


    • Find in results; in data provider: The Deski toolbar has a new icon for Search  (located on the Standard Toolbar), which allows you to seach for text within your report results. This acts like a "find in page", and can also be activated using the Ctrl-F key combination. There's a similar icon at the bottom left of the Query Panel (Edit Data Provider) window, which allows you to find an object to then use when building your query.


    • Query using a previous query: When inserting a new table into an existing report, you now have the option to "Use an existing query to build a new one." This allows you to keep the original query intact, but then still benefit from all the logic already created and modify a copy to use elsewhere in the report.


    • Subquery using a previous query: You can also use results already existing in a report to find records to use in another query. When in the Query Panel creating conditions for your new query, choose "Select Query Results", and then pick the appropriate data provider and object to use.


    Old Favorites:
    • Previous function in variables: The Previous function lets you retrieve previous value of a variable. You can then use this in calculations, such as finding the difference between this month and last month's balance, counts, etc.


    • Take advantage of Fiscal Month Sequence: Fiscal Month Seq (available in Balances, GL Detail, Employee Payment, and other tables) is very handy to use for sorting periods. Also, since the Balances table contains data for future periods in the current fiscal year, you could you Fiscal Month Seq to ensure that you only retrieve current months (e.g., to retrieve months up through October, set your condition for Fiscal Month Seq Less than or equal to 04).


    • Variables – Grouping: Used to easily group objects to create alternate rollups, etc. From the Data menu, select Variables, click on the object you'd like to group, and then the Group button.


    • Conditional Formatting: Alerters in Deski let you format cells based on certain criteria - you can highlight when values for a certain variable pass a threshold, when other conditons are met, etc.

March 20, 2007
Benjamin Franklin Room, Houston Hall

Agenda

  1. Recent happenings:
    • New password changer -- unlock your own accounts!
      • We've updated the password change application so users can now unlock and reset their accounts, without having to contact Data Administration for help. So, if you get the DA0005, "SQL or sentence error" with account locked on invalid password details, go to the new password changer to reset your account.

    • SPONPROJ universe access disabled (use PennERA Proposals instead)
      • Because the data accessed by the SPONPROJ Universe has not been updated since PennERA went live in Oct., 2003, almost no one should be running queries using that universe. We've recently revoked everyone's access to SPONPROJ, to prevent people from inadvertently running reports with outdated data. If you get "cannot find SPONPROJ universe" errors for your queries, it's your call as to whether to delete the query (because you don't use it any more) or to revise it to use the PennERA Proposals Universe instead. Contact Data Administration if you need help with this.

    • GL Detail - new attribute and context columns (February 2007 and on). [Refer to Business Objects Corporate Document "GL Detail Attributes.rep" to retrieve this data.]
      • We've recently added some columns to the GL_DETAIL table in the Warehouse, to provide the additional attribute information available for feeder transactions. This is the sort of data you can navigate to from a BEN journal inquiry screen. There are 20 attribute columns (ATTRIBUTE1, ATTRIBUTE2, etc.) and four context columns (CONTEXT, CONTEXT2, etc); users of the FINQUERY universe will find these in a subfolder of GL Detail, called "GL Detail Attributes." We've added all the attribute and context columns to GL_DETAIL, even though only a subset are actually in use at this time. A few other notes:
        • These columns are populated for transactions from 2/7/2006 and
          forward. Any population of historical records will occur as a separate project.
        • Depending on the journal context (in the CONTEXT column), a variety of data may be concatenated and stored in a single attribute column. The data is fed to the Warehouse in exactly this format, and *is not* parsed out in any fashion, as the format of the data varies by source. For example, for Bookstore feeder transactions, attributes contain Request number, Transaction Date, Contact Person; for BEN Deposits, attributes contain
          Tracking number, Center/ORG, Line Number, Preparer; for FedEx, attributes contain Legacy Account, HUP Cost Center, Invoice number, Airbill number, and so on for other feeders.
        • Note that attribute usage may vary over time, and history will not be updated accordingly. For instance, Gifts and Physical Plant feeders will soon be replaced by feeders from new systems, and data from those new system may be mapped to GL attributes differently from attribute usage by previous feeders.
        • Attribute and context columns may be populated for more than just Feeder transactions. In many cases, you'll find that information in these columns is redundant with other GL_DETAIL fields, although this will vary by journal source.
    • JOB_CLASS changes for Faculty: New Faculty Classes and Academic Category. [Refer to Business Objects Corporate Document "Jan 2007 Job Class Changes for Faculty.rep" to get a quick view of these changes. ]
      • In order to facilitate reporting on faculty, we've made a few changes to
        faculty class values:
        • Added two new faculty classes: ACN for Academic Clinicians, and FER for Faculty Early Retirement
        • Handling of 'B' appointments: A third new faculty class of SEC makes it easier to group secondary appointments (typically to jobs with the 'B' appended to the title). SEC is not included in the primary academic appointment logic, which ensures that these jobs can't be accidentally counted as primary academic appointments.
        • Discontinued use of the 'UNC' Faculty Class: All 2xxxxx job classes that are not otherwise classified should have no value for Faculty Class, from this point forward.
      • In addition, we've introduced a new column in the JOB_CLASS table, called ACADEMIC_CATEGORY. This allows for a higher-level sorting of faculty classes into groups such as Standing Faculty, Standing Faculty C-E (Clinician-Educator), Associated Faculty, Academic Support Staff, and Emeritus.
      • You'll see the current job class/faculty class mapping and academic category column in the JOB_CLASS table and JOB_CLASS_GENERAL_V view. The academic category column has also been added to relevant Business Objects universes. The snapshot version of this data, available in JOB_CLASS_SNAP and JOB_CLASS_GENERAL_SNAP_V, will show the job class/faculty class mapping as it was at the point in time of the snapshot. You'll also be able to see former mapping in historical snapshot periods. Academic Category values will be populated going forward (from the JAN-07 snapshot and on).
    • Budget Office Financial Management reports updated to reflect last year's RCM changes. You can retrieve these from the Business Objects "Financial Management" Corporate Documents category.


    • Faculty Information System data is now available in the Warehouse. Refer to the web documentation for table descriptions, universe diagram, and access request form.


    • ISC Mainframe Chargeback detail is also now available via Business Objects, (July 2006 and on), and coming soon to BEN Reports.

  2. Coming attractions:
    • EMPLOYEE_GENERAL: unsecured access to Full_Part_Time and Reg_Or_Temp so that you can see whether someone's full/part time or regular or temp without having access to their Home Dept ORG.


    • SSN cleanup: We'll soon be restricting access to SSN data via the Warehouse. Please review your queries to make sure you're not retrieving SSNs or using them in prompts; contact Data Administration if you need help converting your queries, or feel you have a business reason for using SSN.


    • Business Objects XIR2: We're hoping to upgrade our Business Objects server from version 5.1.4 to XIR2 this calendar year. Stay turned for more information on query conversion, program changes and so on.

  3. Given upcoming SSN and Business Objects changes, please start to inventory and clean up your queries:
    • Typically stored in C:\Program Files\Business Objects\BusinessObjects 5.0\UserDocs, or maybe on your LAN
    • Get a sense of how many you have
    • Delete duplicates, reports no longer needed
    • Eliminate use of SSNs in result objects or conditions
    • Contact Data Administration with questions on using prompts, making Corporate Documents, etc.

  4. Tips & Tricks:
    • Multiple queries in one report -- see Business Objects Corporate Document "Current Expense and ISC Detail.rep" as an example of combing Balances and ISC billing detail and resuing prompts among the queries.
    • GL Batches - retrieving both sides of a transaction -- see Business Objects Corporate Document "GL Detail Batches - both sides.rep" to retrieve both sides of batches containing ORG and Fund based on a prompt. Note: you'll only be able to retrieve data for ORGs to which you have access.

November 18, 2005
Benjamin Franklin Room, Houston Hall

Agenda

  1. Coming attractions and general reminders:
    • Warehouse maintenance Sunday, 11/20/2005, 9am-Noon
    • Telecommunications Detail (BEN Reports and Business Objects): Telecom feeder data, including invoice summary and call detail information, will be coming to the Warehouse very soon. Stay tuned to the listserv and your Telecom billing statement for futher information.
    • Killing queries: When you have a runaway query, or have used Ctrl-Alt-Del to stop Business Objects, please email da-staff@isc.upenn.edu so we can check to make sure your query isn't still running the database.

  2. Remember these resources:

  3. The Financial Systems landscape


  4. General Ledger collection:

  5. Salary Management collection:

March 25, 2005
Benjamin Franklin Room, Houston Hall

Agenda

  1. Happenings in the Warehouse financial collections

    • Warehouse information on U@Penn
      • Go to the Business tab, and find "Data Warehouse" in the Business Administration channel on the left. This opens a page with links to Warehouse resources (e.g. load status updates).
      • Be sure to check the load status page to see when jobs are complete. Although we strive to have the collection available by 8am, this page will give you details on the latest status.
    • Telecomm detail coming later this year
    • SSN visibility
      • We'll be continuing to work on protecting SSNs in the Warehouse - stay tuned for details on best practices, universe changes, etc.

  2. PAR data in the Warehouse

    • Salary Management universe review
    • New tables: PAR_MAIN and PAR_EARNINGS: Data are currently available from March 2005 on - we'll be backfilling the rest of fiscal year 2005 in coming weeks.
    • Sample query: 1000 Hours Report (retrieve this from the Corporate Documents, Salary Management category, using the Business Objects File... Retrieve from menu path)

  3. Business Objects Tips and Tricks

    Sample query: "Breaks Sections and Totals" (retrieve this from the Corporate Documents, User Group Examples category, using the Business Objects File... Retrieve from menu path)

    • Combined breaks (More on break options in User Guide, on p.284.)
    • Report grand totals (Refer to the User Guide, pp.337-347 for a discussion of report contexts.)
    • Pros and cons of making your own rollups
    • Hiding sections with $0 totals (Refer to the User Guide, pp.561-569, for information on hiding report components.)

 


October 22, 2004
Benjamin Franklin Room, Houston Hall

Agenda

  1. Happenings in the Warehouse financial collections
    • Gift Detail - Camille Turnier

    • Which gift attributes would be of use to you in the GL collection? Please complete this spreadsheet and forward it to Camille.

    • Research data - Lori Ratajczak:
      Which universe should one use for grant reporting?

      FINQUERY: to look at detailed expenditures for a grant
      PennERA Proposals: to look at information about the award itself; information about PIs for the proposal, originating sponsor, etc.
      PennERA Balances: look at Balances and Proposals data together, also fund adjustments


    • ISC Billing detail (email, outlet, and IP charges) is now available via Business Objects and BEN Reports.


    • BRS: Student Billing and Receivables data is now available in the Warehouse. If you don't already have access to the Student data collection, you'll need to submit a form for access to the BRS detail.


    • SSN IT Privacy Initiative: we're in the midst of analysing how SSN is used in the Warehouse and local systems. If you haven't already been surveyed by your school/center privacy liasion, and have a lot of local SSN data and/or queries or reports dependent upon SSN, please let us know.

  2. Query Tips and Tricks
    • Reporting by fiscal quarter
    • Result objects - how do I decide which to use?
    • Employee history - tracking folks on leave, etc.

    Business Objects tips:

      • Hide columns (Right-click on table or crosstab and, on the Pivot tab, select object and click hide.)
      • Funky Filtering:
        • Defining filters (Used to limit the data displayed in your report. Click anywhere on a report and then on the Format menu; select Filters, click the Add button to add a filter, and the Define button to define a formula for that filter. More on Filters in the User Guide, pp. 252-259.)
        • NoFilter function (Used to ignore filters - see User's Guide, p. 258)
      • Match function: Can be used in variables to match strings, using *. For example, If Match("SMITH"; "S*") Then "true" Else "false".
      • Subqueries: Use to find a set of data on which to base another query. (For more on subqueries, see the p. 101 of the User Guide.)

  3. Financial Corporate Documents

March 19, 2004
Irvine G7, Irvine Auditorium (34th & Spruce Streets)

Agenda

  1. Coming Soon: (further information to come via listservs, as dates approach)


    • Data Warehouse upgrade (outages May 2, 23; June 5 - 6)
      • The Data Warehouse hardware and software will be upgraded over 3 weekends. The Warehouse will be unavailable much of the day on May 2 and 23 (both Sundays), and over the entire weekend of June 5 and 6.


    • Networking billing detail
      • ISC Networking is launching a new billing system in July. Feeder billing detail for email, IP addresses and outlet charges will be available in BEN Reports, and also in the Warehouse. The data will be available in early Fall 2004 to anyone with General Ledger collection access, but will likely be accessible in a new universe (rather than FINQUERY).


    • PAR (Payroll Audit Record) detail
      • The Salary Management collection will be expanded to include the payroll audit record and additional employee elements. This project will occur in three phases, beginning Fall 2004, as follows:
        • Phase A will create tables for the information currently stored within the Payroll Audit Record (PAR). This information is based on a single payroll run, and contains details on each payroll transaction including hours and gross-to-net information.
        • Phase B will provide information which is extracted from the employee database each payroll, and is currently located on the PAR Print CD. This information deals with employee balances (dollar balances, GTN balances and hour balances).
        • Phase C deals with adding additional fields from the employee profile to the exising EMPLOYEE table.


    • Bursar (BRS) detail
      • A project has begun to move select student billing data from the Bursar (BRS) system to the Warehouse to make this data more available for reporting, and to allow for joins to the existing General Ledger and Student data collections (Spring-Fall 2004).


    • Business Objects Enterprise 6
      • Data Administration is beginning to test the next release of Business Objects (version 6).

  2. Reminders:

  3. Tips/Tricks:



  4. Query & Formatting Tips - Refer to the Business Objects 5.1 User Guide for documentation on and examples of these features (particular pages are noted below). The User Guide is available from the Business Objects Help menu. The actual pdf file can likely be found on your hard drive in C:\Program Files\Business Objects\Online Guidles\EN\BusinessObjects51EN.pdf

    • Variables
      • Grouping: Used to easily group objects to create alternate rollups, etc. From the Data menu, select Variables, click on the object you'd like to group, and then the Group button. Additional documentation in the User's Guide, pp. 319-322.
      • Filters: Used to limit the data displayed in your report. Click anywhere on a report and then on the Format menu; select Filters, click the Add button to add a fitler, and the Define button to define a formula for that filter. More on Filters in the User Guide, pp. 252-259.
      • Alerters: Used to apply conditional formatting to report cells/rows. Accessible from the Format menu, Alerters. Examples in the User Guide on pp.273-278.
      • Ranking: Used to limit data displayed to specific value ranges; the ranking formula stays constant as the report is refreshed. Determine which Variable you'd like to rank, and then apply the Rank icon to it in Slice & Dice, or from the Format menu. Documentation and exmples in the User Guide, pp.267-271.
      • Special Field: Provide an easy way to insert date/time, prompt values, etc. without having to create variables. From the Insert menu, click Special Field. More information in the User Guide, pp.584-588.

    • Tables
      • Hide Columns: Sometimes you need to use objects to sort data in your report, but don't want them to appear in your report. You can hide these by right-clicking on the table and picking Format Table, and then go to the Pivot tab and hide the object. More information in the User Guide, on p.272.
      • Alignment: The Alignment tab on the Format Table window helps you make sure that multiple tables on one report don't overlap. Right-click on table, Format Table, Appearance tab. Refer to the User Guide pp.553-555.
      • Page Breaks: To ensure that report headers continue across multiple pages, Right-click on table, Format Table, Page Layout. Other examples of managing page breaks in the User Guide, pp.575-577.


    • Finding reports
      • Corporate Documents (aka repository reports) are documented on the Warehouse website
      • You can refresh reports on the web via InfoView

 


April 11, 2003
Bits and Pieces Room - Sansom West, 3rd floor

Agenda

  1. Coming Soon :
    • Oracle Password expiration
    • DW website changes


  2. Reminders:
    • GL month-end timing
    • "Killing" queries


  3. Questions and Answers

Composing Queries:

What are some tips for starting a new query project?

Of course, it depends on what your goal is - what kind of data you're querying, whether you're building a one-time query or something you'd like to reuse, to whom you're giving the data and in what format. Here are some very high-level tips:

  • Start small: start by looking at just an org, or a fund, etc, rather than everything at once.


  • Check your data against source system reports, other queries, etc.


  • Work iteratively, gradually adding more elements and/or broadening your scope.


  • Consider building upon something that works: use existing queries of your own, or those in the repository.


  • Write the user groups (FINQUERY-WH@lists, SALMGT-WH@lists, SPONPROJ-WH@lists) with questions, or to see if anyone else has done what you're trying to do.

What objects should come from which classes? It almost seems as if there are too many objects for the same account segment in the data warehouse, or at the very least, redundancies which make the warehouse non-intuitive.

If you look at the FINQUERY diagram, you'll see that the universe is comprised of a lot of reference tables, along with Balances and GL Detail, which provide the historical financical data. These reference tables serve to decode the values of the account segments in Balances/GL Detail, and also provide information about the attributes of the different segments (i.e., the numerous attributes in FUND CODES). The reference tables are represented in their own folders in the universes, to allow users to look up reference information without necessarily having to query the balances/detail data.

When building a FINQUERY query (or in the FINQUERY template universe, or even RSALMGT), think first about what financial data you'd like to retrieve. Are you looking for data for a specific ORG, FUND, OBJECT, PROGRAM? A specific 26-digit combination? A parent segment? If so, set your conditions on the COA segment in BALANCES or GL_DETAIL, since that will help the database navigate more quickly through those large tables. Then consider what descriptive information you'd like to see for the segments you're retrieving (the description? enabled flag? sponsor code?), and select those elements from the appropriate reference table folder.

If it seems like your query is taking a long time, or you're not sure whether you've selected the right elements from the right folders, go back to basics, and select your results objects and base your conditions just from Balances or GL Detail. You can also refer to the table documentation to see which columns are indexed -- taking advantage of indexes can help query performance. Once you're satisfied with those results, start to add in reference or rollup information.


General Ledger data:

What are the differences between Balances and GL Detail? How and why do folks use one vs. the other?

BALANCES:

  • Contains one row per 26-digit account per accounting period; no PO, invoice, vendor or batch information.
  • Also stores F & A rate by account by period
  • Stores monthly activity (actual_month, oper_budget_month, etc), fiscal year to date (actual_fytd, oper_budget_fytd, etc), project to date (_pjtd), year total budgets (orig_budget_fy_total, oper_budget_fy_total), begin/end balances, and encumbrances (by encumbrance type) as of each period.
  • Once a row is created for a COA_ACCOUNT, there will be a row for that account in every period in the future, even if there is no other activity (in that case, balances will display zeros).
  • Since it's already aggregated, it's more efficient to combine Balances with parent segments (object, org, fund) for more summarized reporting (i.e., by parent object, similar to BEN Reports).

    Sample query: FINQRCM - Budget and Actuals

GL DETAIL:

  • Contains monthly activity at the journal line level (similar to BEN Financials 15x report series). Includes batch name, source, category, user, PO/Invoice numbers.
  • No invoice encumbrances, no PO or Invoice detail (just summary amounts by PO or invoice number).
  • No summarized fytd, pjtd, etc. balances
  • Be patient when querying on vendor, invoice, batch name, transaction_date columns, since they are not yet indexed.

    Sample queries: Checks Received, Encumbrances by Org

 

Salary Management data:

Reporting on "my" ORG(s): If I want to run a query and get historical compensation data for my department, how do I choose the result objects and conditions so that I capture those individuals who worked in my ORGs in past fiscal years, but now work in other parts of the university?

We use ORG-based security for GL and Salary Management data in the Warehouse. This means that the data you can see is controlled by the orgs to which you have access, and how you've structured your query. When composing a Salary Management query, consider the type of data you want to see, and the accompanying security:

Data Home_Dept_Org
Job_Dept_Org
COA_Org
Responsible_Org

EMPLOYEE (confidential)

yes
EMPLOYEE JOB
yes
yes
EMPLOYEE DISTRIBUTION
yes
yes
Distribution_COA_Org
yes
EMPLOYEE PAYMENT
yes
yes
Posted_COA_Org
yes

Keep in mind that since security is at play in all tables, a query combining objects from different tables can yield fewer results than one based against just one of the tables. For example, in the question above, as long as you don't need confidential employee data, you can base your ORG condition on the home_dept_org in EMPLOYEE_PAYMENT, to retrieve payments people received when they were under your ORG. If you add in objects from EMPLOYEE (assuming they don't exist in EMPLOYEE_GENERAL - always look there first), you'll be restricted to only seeing data for people in your home_dept_org now. If you do need to see historical EMPLOYEE data, you can use the RSALMGT snapshot universe to retrieve EMPLOYEE PAYMENT data and its contemporaneous EMPLOYEE data.

 

Business Objects formatting/features:

When should I use conditions on objects in a query vs. report formatting techniques (i.e., filters, variables)?

Conditions, which are defined in the query panel, limit the data retrieved by your query. You can use standard operators, such as Between, Equals, In List, Matches Pattern, Is Null (and the negative versions thereof), and combine conditions to futher restrict your results.

Filters and Variables are applied once your results have been returned, and further determine how your data is displayed on screen. You can filter your data to display only a subset, but entire data set remains available for use in variables or other reports. Variables allow you to perform more complex logic on your results, such as mathematical formulae, or if/then/else logic, etc. Keep in mind that if you choose to export your data (Data/View Data/Export), all your underlying data will be exported, regardless of filters, variables, or other formatting.

When might I use a Crosstab?

Similar to an Excel pivot table, a Business Objects crosstab is a particular kind of table where data is displayed in columns and in rows. Corresponding data appears at the intersection of the columns and rows; this part of the crosstab is called the body. The body typically displays numerical data. A crosstab can display both row totals and column totals as well as a grand total. You need at least three variables to display data in a crosstab including one measure. The measure is placed in the body of the crosstab.

Crosstabs provide convenient ways of looking at similar data across years or periods, and even using variables to calculate changes from year to year (column to column). The FINQRCM-Previous Comparison query is an example of this technique.

You can create a crosstab by clicking the Insert Crosstab icon, using Slice and Dice, or dragging variables from the Report Manager into the report to create a table and then re-organizing the table into a crosstab.

How can I use report headers more effectively?

A header can be a simple text cell that appears on the first page of your report (the default "Report Title", at its most basic), or it can be more useful:

  • You can repeat a header on each page of your report:
    • Right-click on the header cell, and choose "Format Cell".
    • On the Appearance tab, check the box next to Repeat on Every Page
    • Click OK


  • You can have your report title header update automatically, based on prompts or other data in your report. You can do this by creating a variable and using the UserResponse functions and static text. The & character lets you concatenate text (surrounded by quotation marks) with functions. For example, in the FINQUERY Encumbrance by Org report, the header is updated automatically based on the year and org prompt inputs. This cell was constructed as follows:

    ="Encumberance Report by ORG"&"for fiscal year "&UserResponse("Query 1 with FINQUERY" ,"Enter Fiscal Year:")&", Org(s): "&UserResponse("Query 1 with FINQUERY" ,"Orgs in list (separate by ;):")

What can I do with charts in Business Objects?

Business Objects has five basic chart types: Column, Line, Pie, Area and Scatter. Some also have 3-D variations. You can insert a chart just as you can insert a new table in a report. It's also very easy to turn an existing table into a chart: simply right-click on the tab, and select Turn to Chart. You can then select a chart type, and the table will be automatically converted. Alternatively, duplicate a table (by selecting the entire table, and then copying/pasting), and turn the duplicate to a chart, leaving the original table for comparison purposes.

Chapters 17 & 18 of the Business Objects User's Guide (available from the Help menu) provide in-depth explanation of Business Objects' charting capabilities.

How do I export data from Business Objects?

You can choose to export all the data retrieved by your query, using the Export (Data menu, View Data, Export) function, or save the formatted results using Save As (File menu, Save As). Some basic differences between these methods include:

  • Export:
    • Exports all data retrieved, regardless of filters, formatting, etc.
    • Does not export variables or calculations
    • Formats: text, Excel


  • Save As:
    • Exports only data displayed in a report
    • Does save output of variables
    • Formats: text, .pdf

You can also use Copy All (from the Edit menu) to copy and paste your on-screen results directly into Excel. Be sure to use "Paste Special" in Excel to paste the data, rather than an image.

Refer to the tips page for more on exporting possibilities.

What are my options in combining data from different universes? Linking to Excel?

As you become more advanced in querying with Business Objects, you may want to combine queries together - whether from the same universe, different universes, non-Warehouse data, etc. Here are some basic options for combining data:

  • Union/Intersect/Minus: Using the Combined Queries icon (two intersecting circles) in the Query panel, you can create up to eight combined queries in one data provider. The data is returned in one table, with a single columns of data from possibly different objects. You can also obtain data common to two sets of results, and exclude the results of one query from the results of another. Results of this query can be exported easily. Examples of union queries include the FINQRCM Budget and Actuals and RCM 210 reports.


  • Linking Queries: You can also link separate queries within one report, as long as they have data elements in common. This works best when the queries have all dimensions in common, but different measures. It's also a great way to combine data from different universes. Once you have the two (or more) distinct queries, use the Data Manager (Data, View Data, Definition tab) to link the related objects in the different data providers. Business Objects will automatically link objects of the same name. Examples of linked queries include the FINQUERY Year over Year report, and RSALMGT Payments and Encumbrances.


  • External Data: Joining in external data, such as data in an Excel spreadsheet, is very similar to linking queries. Insert a new table, and choose "Access new data in a different way" from the wizard, and then "Personal Data Files". Browse to your file, and determine which objects you want to be measures or dimensions. You can then link those elements to others from other queries. Keep in mind that this report can not be easily shared, unless the linked file is in a common location.


  • Combining universes: You can use the linking technique above, to combine queries from different universes, if the elements are in common. You may also want to consider using the AWARDBAL or GLBALSAL universes, which join Sponsored Projects/General Ledger and Salary Management/General Ledger data, respectively. Contact Data Administration if you're not sure whether you have access to those universes.

 

 


October 18, 2002
Bits and Pieces Room - Sansom West, 3rd floor

Agenda

  1. Updates:
  2. Business Objects upgrade information and demo


  3. Query show-and-tell

 


March 22, 2002
Bits and Pieces Room - Sansom West, 3rd floor

Agenda

  1. Updates and Coming Soon:
    • GL mid-week month-end loads
    • Salary Management snapshots


  2. Query show-and-tell
    • Using RCM Categories, variables and report formatting (Tom Giandinoto, SOM)

      Summary: Tom showed us several reports that he users to manage Pharmacology's finances. Although these were customized to his needs, we can learn some things that are applicable to anyone's work:

      • If you're going to be reformatting/breaking/sorting/filtering the data
        you retrieve within Business Objects, be sure to save a tab that shows the raw data. Once you run your query and retrieve the data in the standard Business Objects default table, copy the report tab and continue your formatting on the new one. The version 5 Map tab makes it easy to navigate from one tab to another.
      • You can have multiple tables showing different views or subsets (using filters, breaks and sorts) all on one tab - just choose to a insert a new table, using data from the current document, and select the elements you want. You can adjust filters/sorts/breaks from the Slice and Dice window, and also from the Format menu.
      • You can join in Excel data. To do this, choose to create a new query, but, when you usually pick "Universe" as the way you want to access data, click "Others", and "Personal Data Provider". Browse to your Excel file, designate which sheet you want to appear (and whether the first row has column headings), and click Run to return the data to Business Objects. You can then link the elements to those returned from a universe query, and so on.


    • Using Excel to query the Warehouse (Kelly Ardis, VET)

      Summary: Kelly presented her Excel workbook that she uses to help manage the Vet school's finances, looking at history and doing projections tailored to different line items and organizations. She retrieves all the data in one query (copy and pasted from Business Objects into Microsoft Query),
      and then creates pivot tables and other spreadsheets linked to that data. Kelly's spreadsheets are of course customized to her needs, but if you have Microsoft Office and SQL*Net installed, you can retrieve your own data directly into Excel. The exact steps may vary between versions of Office, but the basics are:

      • Make sure Microsoft Query is installed. From the Excel Data menu, pick "Get External Data", and then "Create New Query". Since Microsoft Query is not part of the typical installation of Office, you may get an error message at this point. If you do, you (or your LSP) will need to add this from your original install media. Once it's installed, you should see the "Choose Data Source" window after you click "Create New Query".
      • Create a data source for ODBC access to the Warehouse (if you already use MS Access directly against the Warehouse you can probably skip this step). Find where the control panel for ODBC data sources is on your pc - it may be right in the Control Panel, or tucked away under another grouping (like Administrative Tools in Windows XP). Create a System DSN, using a "Microsoft ODBC for Oracle driver) for with

        Data Source Name: whse
        Description: Data Warehouse (or whatever you want to call it)
        User Name: leave blank (it will prompt you for that and your password)
        Server: whse (this assumes that you have SQL*Net installed, and have a setting for whse already set up, if you don't see
        http://www.upenn.edu/computing/da/bo/instBO5.html, and refer to the section about setting up SQL*Net)

      • Back in the Microsoft Query "Choose Data Source" window, select "whse" from the list of data sources. Be sure to uncheck "Use the Query Wizard to create/edit queries". Click OK. Enter your Warehouse user ID and password, and "whse" as the server in the next dialog box.
      • You can try to create your own query using the dialog boxes and window that follows, or, just click "Close" to the Add Tables window. You'll see an SQL button in the Microsoft Query toolbar - click this to open a window into which you can paste the SQL from the Business Objects query panel. Once you've pasted in your SQL, click OK. You'll see Microsoft Query try to represent your query visually, and return some data. Click the Return Data button (looks like a little door with an arrow) to send the data back to your Excel spreadsheet.


  3. Q & A, Business Objects tips (Previous, Retrieve Into)

    Amy Miller showed a new repository query which uses the Business Objects "Previous" function in a variable to compare data in crosstab columns (period to period). Note that the Previous function allows for comparison to the previous row (in a standard table format), or the previous column (in a crosstab format). So, the comparison could be between anything that occurs in consecutive rows or columns - this query just uses it to compare accounting periods year to year.

    In retrieving this query from the repository during the meeting, I showed that by right-clicking on the file in the Retrieve window, I can choose to retrieve the file into the place I designate, rather than the default UserDocs folder. This is a big help if you store your queries on a network, or in folders by universe, and so on.


August 2, 2001
Bits and Pieces Room - Sansom West, 3rd floor

Agenda

  1. Updates and Coming Soon:
    • PennNames FinMIS & DW UserName changes - Saturday, 8/4/2001
    • Grant & Contract (F & A and ICR) rates back to 1996
    • Fund Date Established
    • Warehouse database upgrade
  2. Cash Effect parent object (K112) in the Warehouse and BEN Reports
  3. Query show-and-tell (Grants Management queries)

    See http://www.upenn.edu/computing/da/dw/financial_queries.html for documentation

    To import these for your own use:

    1. Log on to Business Objects.
    2. From the File menu, click "Retrieve From", and then "Corporate Documents" (or, if you're still using version 4, choose to retrieve from "Repository").
    3. Click on the name of the file you'd like to retrieve, and then click the Retrieve button.
    4. If you are using Business Objects version 5, you may choose whether to open the document upon retrieval.
    5. Retrieved files are saved in your UserDocs directory by default. You may wish to relocate and/or rename them.
  4. Q & A, Business Objects tips

February 20, 2001
Bits and Pieces Room - Sansom West, 3rd floor

Agenda

  1. Introductions
  2. Overview of available on-line resources
  3. Query demonstration:

    · FINQUERY - Year over year comparison
    · FINQUERY Template Universe - RCM 210
    · RSALMGT - Payments and Encumbrances

    To import these for your own use:

    1. Log on to Business Objects.
    2. From the File menu, click "Retrieve From", and then "Corporate Documents" (or, if you're still using version 4, choose to retrieve from "Repository").
    3. Click on the name of the file you'd like to retrieve, and then click the Retrieve button.
    4. If you are using Business Objects version 5, you may choose whether to open the document upon retrieval.
    5. Retrieved files are saved in your UserDocs directory by default. You may wish to relocate and/or rename them.
 
top

Information Systems and Computing
University of Pennsylvania
Comments & Questions


Penn Computing University of Pennsylvania
Information Systems and Computing, University of Pennsylvania