ODS First Look at Discoverer Plus Where to start… What you see Create a report Conditions/Parameters/Calculations Sorting/Formatting Totals Tips and Tricks
ODS Overview ODS – – – – –
Operational Data Store OLAP Database - Online Analytical Processing Tables/Views Designed For Reporting Day Old Data Incrementally Refreshed Nightly
ODS Functionality Banner
ODS
Banner INB/SSB
Discoverer ADMISSIONS APPLICATION
RECRUITMENT INFORMATION COURSE ATTRIBUTE
AS_COURSE _ATTRIBUTE AS_ADMISSIONS _APPLICATION
AS_ENROLLMENT
MST_RECRUITMENT _INFORMATION
AS_CURRICULUM
MST_ADMISSIONS _APPLICATION
AS_RECRUITMENT _INFORMATION
Nightly ETL Process
ENROLLMENT
CURRICULUM
MST_ENROLLMENT
MST_COURSE _ATTRIBUTE
MST_CURRICULUM
Finding Your Data In The ODS ODS Metadata – Available From “Blue Screen” http://banods0.ivytech.edu:9040/metadata/ODS_index.html
Discoverer Names and Definitions
Workbook/ Worksheet Table/Crosstab Page Items Business Area Folders/Views Joins
Items – – – –
Page Axis Top Axis Side Axis Data Point
List of Values Conditions Parameters Calculations
Where to Start… Available at: http://banods0.ivytech.edu:7779/discoverer/plus
Username/Password as assigned Database: – ODWP for data from PROD (day old) – ODWT for test data
Open or create new report
What you see
When a report opens, if there are parameters set, you will need to input these before report will query
What you see
Creating a report Using the Wizard – Walk through to add necessary parts of report
Start with ‘known’ items Add advanced conditions/calculations after successfully running Use metadata lookup to find fields if you don’t know where they are
Item Types Strings and Dates Numbers Pre-defined calculation – Will add item with a calculation on it – Bold value is the default
Conditions Filters query results based on what report writer sets – Ex: only show results where campus=A1
Can be combined with parameters to allow user to set values for conditions Using % as a wildcard requires a ‘LIKE’ condition instead of ‘=’
Parameters Conditions that users define value before query runs each time Options – Required or Optional – Multiple values or single value
If allowing users to use a ‘%’ wildcard, the LIKE condition must be used
Sorting Group Sort – Shows only the 1st occurrence of a value
Normal Sort Sort Order – Group sorts must always precede normal sorts
Breaks – You can add lines, blank rows or page breaks at breaks in group sorts
Formatting 3 ways to format data – Icons in toolbar – Format -> Data – Right-click -> Format Data
Conditional Formatting – Assigns format when pre-set conditions are met
Setting default formats – Tools->Options->Formats – Will assign formats set to all future sheets created
Totals Quick and Dirty totals – Select column then choose function from dropdown menu ‘Normal’ total – Click ‘New Total’ button – Select Item, total type, grand/subtotal
Tips and Tricks Smaller Datasets=shorter run times – Apply conditions to limit results – While building, restrict to only 1 result to test
Get data from one place if possible – Fewer joins will mean shorter run times
Addresses – Apply condition to ‘Address Rule’ (ex:STDNADDR) or report may never return
Tips and Tricks Avoid huge folders – Student, Government Student – Transaction History, Chart of Accounts, Fund
To close Discoverer: File -> Exit – Do not use the ‘Big Red X’ – That will leave session open on server
Close Discoverer when you leave your desk – Discoverer will timeout when not in use – This will also leave your session open
Putting It All Together Create A Report In Discoverer – – – –
Start with a form in Banner Find the data elements for a report Use the metadata to find the elements in the ODS Use Discoverer Plus to create the report
Questions? If you have issues writing a report, please create a HIM ticket and assign to ‘ITS Report Support’