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
- 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
- eForms for Warehouse and BEN Reports access requests: Electronic access forms (Penn eForms - //provider.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
- Business Objects Web Intelligence (Webi) Migration: It's underway - see the migration website (//provider.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
- 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.
- 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.
- 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:
- 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
- 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:
- 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
- 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
- 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
- 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.
- 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.
- 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
- 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
- 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.
- 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:
- Data Warehouse
- Business Objects
- 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
- 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.
- 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.
- 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.
- 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
- 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.
- Remember these resources:
- The Financial Systems landscape
- General Ledger collection:
- Salary Management collection:
March 25, 2005
Benjamin Franklin Room, Houston Hall
Agenda
- 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.
- 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)
- 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
- 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.
- 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.)
- Financial Corporate Documents
March 19, 2004
Irvine G7, Irvine Auditorium (34th & Spruce Streets)
Agenda
- 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).
- Reminders:
- Password expiration/lockout
- Business Objects training schedule
- Tips/Tricks:
- Performance
- Common Business Objects errors
- 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
- Coming Soon :
- Oracle Password expiration
- DW website changes
- Reminders:
- GL month-end timing
- "Killing" queries
- 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:
GL
DETAIL:
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
- Updates:
- Business Objects upgrade
information and demo
- Query show-and-tell
March 22, 2002
Bits and Pieces Room - Sansom West, 3rd floor
Agenda
- Updates and Coming Soon:
- GL mid-week month-end loads
- Salary Management snapshots
- Query show-and-tell
- 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
- 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
- Cash Effect parent object (K112) in the Warehouse and BEN Reports
- Query show-and-tell (Grants Management queries)
See //provider.upenn.edu/computing/da/dw/financial_queries.html
for documentation
To import these for your own use:
- Log on to Business Objects.
- From the File menu, click "Retrieve From", and then
"Corporate Documents" (or, if you're still using version 4, choose
to retrieve from "Repository").
- Click on the name of the file you'd like to retrieve, and
then click the Retrieve button.
- If you are using Business Objects version 5, you may choose
whether to open the document upon retrieval.
- Retrieved files are saved in your UserDocs directory by
default. You may wish to relocate and/or rename them.
- Q & A, Business Objects tips
February 20, 2001
Bits and Pieces Room - Sansom West, 3rd floor
Agenda
- Introductions
- Overview of available on-line resources
- Query demonstration:
· FINQUERY
- Year over year comparison
· FINQUERY Template
Universe - RCM 210
· RSALMGT
- Payments and Encumbrances
To import these for your own use:
- Log on to Business Objects.
- From the File menu, click "Retrieve From",
and then "Corporate Documents" (or, if you're still using version
4, choose to retrieve from "Repository").
- Click on the name of the file you'd like to retrieve,
and then click the Retrieve button.
- If you are using Business Objects version 5, you
may choose whether to open the document upon retrieval.
- Retrieved files are saved in your UserDocs directory
by default. You may wish to relocate and/or rename them.
|
