Datamart User Group Meeting

1
Datamart User Group Meeting

• July 26, 2007

2
Agenda

• Update on Daily Updates
• New Datamart Server
• Proposed Table Changes/Additions
• ORBITS Budget Actuals Table
• Datamart Purge/Rollover
• Repository Reports
• Hyperion Quick Tips
• Round Table

3
Daily Updates Business Case
Mr. Owlhow many changes happen in SFMA each day?
Well need to install a trigger to count the
changesa one, a two, a three
4
Daily Updates Business Case
Crunch
Too many to count!
5
Propagator vs. Incremental
Propagator
Incremental

• Included in current Datamart software
• Loads bottom line transactional changes
• Lower load costs
• Lower interim storage costs
• Requires a full-time trigger in SFMA

• Requires a software purchase
• Loads all daily instances of change
• Higher load costs
• Higher interim storage costs
• Doesnt require a full-time trigger in SFMA

6
Trigger?

• We need to test the trigger in SFMA to track the
  following
• How many SFMA transactional changes happen each
  day
• This will give us an estimate for daily load
  costs for both Propagator or Incremental
• How much does it cost to install and run the
  trigger in SFMA
• Is it more economical to install Propagator
  software or utilize Incremental Changes?

7
Have you heard about the new Datamart server?
Why do we have to change?
If you have a special connection to the Datamart
server youll need to work with SARS to parallel
test your connection.
8
New Datamart Server

• Anticipate Datamart users/usage to increase by
  21 over the next year
• General increased usage
• Addition of ORBITS table
• Users like greater date/data ranges
• Break your queries down into smaller date/data
  ranges
• Use the GL Detail or GL Summary tables instead of
  the All Acct Event if possible

9
What are Special Connections?

• Hyperion Version
• What version of Hyperion do you use?
• Do you have any Service Packs installed?
• Do you access Hyperion through your desktop or a
  server (like Citrix)?

10
Hyperion Version?
11
Older Versions of Brio

• There is a possibility older versions of Brio
  (Version 5 6) may not be compatible with new
  DB2 software on the new Datamart server.
• Reports will need to be re-written if created in
  Brio Version 5 or earlier to be utilized in
  Hyperion Version 8.

12
Special Connections cont

• Microsoft Access
• Do you use Microsoft Access to pull data from the
  Datamart?
• If so, what version of Access do you use?
• We only need to know about Access if you use it
  to directly access the Datamart tables.

13
Special Connections cont

• OCEs
• Do you use only the Datamart and OSPA OCEs?
• Do you use an OCE that might be different from
  other agencies and/or users?

14
Special Connections cont

• Other Report Writers
• Do you use a report writer other than Hyperion
  (Crystal Reports, Cognos, etc) to directly access
  the Datamart?

15
Special Connections cont

• Brio Version 6 On-Demand Server
• Do you schedule reports on the Version 6 server?
• Both Version 6 and Version 8 servers will need to
  be tested for compatibility.

16
Special Connections cont

• Combining Agency Specific Databases with the
  Datamart
• Does your agency combine any systems (like an
  Accounts Receivable Subsidiary System) with the
  Datamart?

17
New Server Proposed Timeline

• August 10th
• All connection information received by SARS
• August 11th September 14th
• Specific users contacted to test unique
  connections
• September 24th
• Transfer to new server

18
Proposed Table Changes/Additions

• Agency Object
• Efficient way to see if agency object is usable
• Add Effective Beginning Date Field
• Add Effective End Date Field

Need to investigate the Effective Beginning
Date and Effective End Date fields. Due to
profile roll-ups when information comes into the
Datamart, the user may not get accurate
information in these fields.
19
Proposed TableChanges/Additions cont

• Cost Allocation Profile
• Easier to make changes in the mainframe with a
  user-friendly electronic version
• Please see reference list for fields to add
• About 3 hours of programming time

Would like to implement once we get over to the
new server due to space limitations on existing
server.
20
ORBITS Budget Actuals Table

• Added to the SCD Accounting Datamart on July 23,
  2007
• Target date of October 1, 2007 to update
  Legislatively Approved Budget info from ORBITS
• Table info on the web at
• http//egov.oregon.gov/DAS/SCD/SARS/docs/datamart/
  Pgm-Code-DM-table_User-Doc.doc

21
ORBITS Budget Actuals Reports

• I heard this focus group plans to meet during
  October 2007.

• Would you like to help create Repository Reports
  using this table?

22
OSPA Datamart Purge/Rollover

• August 13, 2007
• Regular Tables
• 2007-2009
• B Tables
• 2005-2007 2007-2009
• PB Tables
• 2005-2007
• PP Tables
• 2001-2003 2003-2005

23
SCD Accounting Datamart Purge

• August 31, 2007
• FY 2001 (July 1, 2000 to June 30, 2001) purged
  from SCD Accounting Datamart
• Oldest data available will date back to July 1,
  2001
• Capital Construction will remain if appropriation
  is still active

24
Repository Reports

• Upcoming for August
• D23 Balances
• DAFR 9210-Cash and Accrual Expenditures

• Recently Added
• LX Usage
• PEBB Reconciliation
• Comp Time Ending Balance

25

• Hyperion Quick Tips

26
Connections to the Datamart
This user wont be able to access the Datamart
until someone breaks a connection!
27
Ignore Button
Have you ever noticed a gray Limit?
28
Ignore Button cont
1. Double-click to open the Limit.
2. Click on the Ignore button.
29
Ignore Button cont
To make the Limit active- Double-click
to open the Limit and click OK.
30
Variable Limits
Once a Limit is created in the Query Section, you
can construct it to prompt the end user for
values when the query is processed.
31
Variable Limits cont

• 3 Ways to do Variables
• Menu BarSelect your Limit first.Then,
  select?Query?Variable Limit
• Limit LineSelect your Limit first.Then, select
  arrow on?then, Var
• Right-clickSelect your Limit first.Right-click?
  Variable Limit

32
Size of Dialog Box
Pause on the diagonal lines in the lower right
corner and get a double-headed arrow. Use this
arrow to expand or collapse the dialog box.
33
Sort Table Field Names
1.

• Double-click onthe table title bar.This opens
  theTopics Propertiesdialog box.
• Click?Sort,then, OK.
• Your table fieldnames will be inascending order.

3.
2.
34
Floating and Docking Boxes
You can grab these boxes by the grey
vertical line on the left-hand side and make
them float on your screen. This enables you to
expand the boxes so you can see more information.
35
Floating and Docking Boxes cont...
This box is floating. Notice the colored title
bar?
Double-click on the colored title bar to make
the box dock back to the original position.
36

• Round Table

37

• Thanks for Attending!