Welcome to the Datamart User Group

1
Welcome to theDatamart User Group

• January 31, 2007

2
Agenda

• Introductions
• Survey Results
• ORBITS Cross Reference Number
• Training Desires
• Daily Updates
• Repository Reports
• On Demand Server
• Newsletter Name Winner
• Round Table

3
ORBITS and SFMS Survey

• Quality Plus Engineering conducted a survey in
  September 2006
• Surveys were distributed to 651 Datamart users

4
Survey Objectives

• Determine if state management has the required
  tools available to monitor budget to actual
  expenditures
• Review the reliability of SFMA and Datamart
  applications and the availability of the updated
  weekly Datamart information to state agencies
• Review the reliability of ORBITS and its
  interface with SFMA and the availability of the
  updated monthly budget to actual information to
  state agencies
• Report on the completeness, reliability, and
  availability of the information to state agencies

5
Datamart Challenges?33 Responding
6
Comments

• Not understanding what the tables are and when
  to use themLike getting information from a black
  hole. No way to know if you have the correct
  information. Quality of the information is
  dependent on users query skills.
• Would be very helpful to have the daily
  download. Takes a long time to run each query.
• First, the chance of, and the extremely high
  cost of, runaway queries is a detriment to using
  the system. Second, we believe it saves money in
  the long run to have our own data warehouse both
  in shorter time attached to the DAS system and in
  more rapid

7
Additional Comments

• Just need information that is more current than
  the weekly download can provide.
• Budget data at lower level. Expenditure and
  revenue data is great.
• ORBITS Cross Reference Number would be useful,
  but in the near future that too will be
  available.
• It is difficult to train new staff or occasional
  users.
• The data that is missing is confidential and
  cannot be placed on the Datamart.

8
Satisfaction, Reliability Accessibility?

• How would you rate the accessibility of the
  weekly data in the Datamart?
• How would you rate the reliability (Data Quality
  coming into the system) of the information in the
  Datamart to support your agency?
• What is your level of satisfaction with the
  Datamart as a business support tool?

9
Satisfaction, Reliability Accessibility Results
10
Datamart Missing Information?28 Responding
11
Data Completeness
12
Data Organization
13
ORBITS Cross-Reference Number

• One-to-one relationship between ORBITS
  Cross-Reference and the RStars Program Code
• 1/09/07 ORBITS Cross-Reference added to the
  RStars Program Code (D04) Profile
• 1/15/07 Three new fields are available to
  Datamart users in the Program Code table
• Please contact your SFMS Agency Support Analyst
  regarding your structure realignment

14
Datamart ORBITS Extract

• On a weekly basis, the program code table is
  completely reloaded, matched against ORBITS, and
  then uploaded to the Datamart.

15
Datamart Program Code Table

• Three new fields were added to the table
• Reports To Program Code
• Already existing in the RStars table, this field
  was added to the Datamart to allow for possible
  future reporting needs.
• ORBITS Cross-Reference Number
• The 17-digit cross-reference number as entered in
  RStars, is reformatted with dashes to make it
  easier to read. The 1st five digits represent
  the ORBITS Agency number in most cases, this is
  the RStars Agency number appended by 2 zeroes.
• ORBITS Cross-Reference Title
• When the ORBITS cross-reference number is valid
  the corresponding title is extracted from ORBITS
  and displayed in this field.

16
What makes my ORBITS Xref number invalid in the
Datamart?

• The ORBITS cross-reference is formatted with
  dashes and edited for legitimacy this means if
  the number entered on the D04 profile in RStars
  is determined to be invalid a blank value will
  result in the Datamart.
• Why might this happen?

17

• 1. Cross-Reference number is not found in
  ORBITS for the Bien/Agency
• Bien is not found on ORBITS (pre-2005)
• Agency is not found on ORBITS (leading 5-digits
  of ORBITS cross-reference field on Program Code
  table)
• Cross-Reference number is not found on ORBITS
• 2. Cross-Reference number has a status of I
  (inactive) for the Bien/Agency
• 3. Cross-Reference is considered a suspense
  structure for the Bien/Agency

18
Future Additions to the Datamart from ORBITS and
RStars

• Watch for a new Budget Actuals table to be added
  Summer 2007
• What sets this table apart from others is all
  monthly buckets will be recalculated with actuals
  each time the Datamart is updated
• This table will connect to a new screen in
  ORBITS, uploading data from the Datamart to
  ORBITS
• SARS will conduct trainings on best practices for
  this table

19
Datamart User Levels
20
How would you like your training

• Divided by User Level
• Basic
• Intermediate
• Advanced Topics
• Developers/Designers
• Repository Developers
• Schedulers

• Divided by Datamart Tables
• Grants
• Budget
• PCA
• Payroll/Personnel
• All Accounting Event
• Etc.

21
How would you like your training

• Divided by Topic
• Designing Custom Reports
• Simple Queries
• Limits/Sorts
• Computed Items
• Functions Simple Advanced
• Grouping Columns Dates
• Pivots Charts

• Custom Trainings
• Let me come to your agency for a customized
  training exclusive to your agencys questions and
  needs.

22
Daily Updates

• Investigating different propagation tools
• A propagation tool enables only new information
  to be uploaded to the Datamart each day rather
  than reloading all data
• Estimating the expected increase in CPU run time
  to run the propagation tool
• Estimation of daily updates/changes is around
  330,000 transactions
• We will need to install a trigger to give us
  actual counts of deletes (any edits to a
  transaction)
• The above transaction figure coincides with
  increased server storage costs

23
Repository Reports

• Develop and distribute common reports for use by
  multiple agencies
• Utilize favorite reports already in existence
• Rather than re-creating reports, tweak existing
  reports so it is specific to your agency
• Ability to share these reports with others while
  maintaining controlled access
• Deliver Repository Report Training to end users

24
OnDemand, Broadcast, Insight Server Update

• OnDemand Broadcast
• Brio Version 6
• OnDemand web access to reports
• Broadcast for scheduling reports
• Notifications are out for currently scheduled
  reports for keeping or deleting
• Working on getting security access to web reports
  for notification clean-up

• Insight Server
• Hyperion Version 8
• OnDemand Broadcast Server rolled into one
  platform
• Test reporting processes for accuracy
• Provide initial training
• Transfer only pertinent reports over to this
  server
• Coordinate on-going training
• Maintain information/traffic on the server

25
Datamart Website

• Check us out at
• http//egov.oregon.gov/das/scd/sars
• What youll find
• Newsletter Archive
• User Group PowerPoint Archive
• Hyperion Info
• OCE Documentation
• Training Dates Materials

26
Round Table
27
Thanks for Attending!

• Please dont forget to fill out your forms and
  turn them in as you exit!