data warehouse overview (financial analysis) warehouse overview (financial analysis) may 02, 2002. 2...
TRANSCRIPT
Data Warehouse Overview(Financial Analysis)
May 02, 2002
2
Agenda
Data Warehouse ReviewRensselaer DW OverviewIntro. to Financial AnalysisHomework!
3
Data Warehouse Review
A data warehouse is a subject-oriented, integrated, time-varying, non-volatilecollection of data in support of the management's decision-making process."
--- Bill Inmon
4
Data Warehouse Review
General CharacteristicsSubject OrientedIntegratedTime VaryingConsistentNon-volatile (usually)Used in management decision making process
5
Data Warehouse Review
Key ConceptsData Warehouse vs. Data Mart
Data Mart – Single subject areaData Warehouse – Integrated Data Marts
Star SchemaDimensions
HierarchiesDescriptive Attributes
Fact TablesMetadata
6
Definitions – cont.Star Schema
Common method of organizing and relating dimensions and facts to support end user reporting and analytical needs.DimensionTable of related attributes typically organized in a hierarchy used to describe business facts.Fact TableTable of common data elements used to measure a business. Typically numeric values.MetadataRepository of business information describing the data elements, business rules, processes and sources systems of the warehouse.
7
Data Warehouse/Data Mart
Operating Ledger Financial Transactions
General Ledger Monthly Snapshot
General Ledger Financial Transactions (Future)
Operating Ledger Monthly Snapshot R
ensselaer
Data W
arehouse
Metad
ataBrio End User Query and Reporting,
Dashboards
Data Mart
Rensselaer Data Warehouse
8
Star SchemaFiscal Period Dim
•Fiscal Period Key
•Fiscal Year
•Fiscal Year Desc.
Organization Dim
•Organization Key
•Chart Code
•Chart Desc
•Portfolio Code
•Portfolio Desc.
•Fund Key
•Chart Code
•Chart Desc
•Fund Group_1
•Fund Code
•Fund Code Desc.
Operating Monthly Snapshot Facts•Fiscal Period Key
•Organization Key
•Fund Key
•Budget Amt
•Expenditure Amt
•Commitment Amt
Fund Dim
9
Star Schema – Reporting
Portfolio Desc Fund Group Desc Budget
Fiscal Year: FY-2001
Finance
Administration
School of Science
Student Life
Restricted Funds
Unrestricted Funds
Restricted Funds
Unrestricted Funds
Restricted Funds
Unrestricted Funds
Restricted Funds
Unrestricted Funds
$150,000.00
$75,000.00
$100,00.00
$25,000.00
$250,000.00
$100,000.00
$200,000.00
$100,000.00
Expenditure
$50,000.00
$15,000.00
$20,00.00
$5,000.00
$150,000.00
$50,000.00
$125,000.00
$75,000.00
Commitment
$10,000.00
$5,000.00
$30,00.00
$2,000.00
$20,000.00
$15,000.00
$25,000.00
$7,500.00
Fiscal Period Dimension
Org
aniz
atio
n
Dim
ensi
on
Operating Monthly Snapshot Facts
Fund Dimension
10
Rensselaer DW Overview
Source Data•Banner Finance
•Banner Grants
Potential External Data Sources•Budgeting Data
•Alumni
Informatica -ETL Process•Gather data from operational sources
•Data cleansing and integration
•Populate and Q/A Data Warehouse
Operating Ledger Transactions
General Ledger Monthly Snapshot
Operating Ledger Monthly Snapshot
Oracle Database
Metadata Brio - End User Reporting and Analysis
11
Rensselaer DW OverviewThree fiscal years (FY2000 – 2002)
Operating Ledger transaction detailOperating Ledger monthly snapshotsGeneral Ledger monthly snapshots
Refreshed dailyTroy and Hartford informationKey Data Hierarchies
Organization (current & month end)Fund, Program, Account (current & FY end)
ITD summaries for Research Grants
12
Intro. to Financial Data Marts
Operating Ledger Financial TransactionsOperating Ledger Monthly SnapshotGeneral Ledger Monthly Snapshot
13
Operating Ledger Financial Budget Transactions
F in a n c e B u d g e tT r a n s a c t io n F a c tsG r a in :E a c h O p e r a t in g L e d g e r B u d g e tT ra n s a c t io n fo r th e lo w e s t le v e lF O A P A L
T r a n s a c t io n D a teD im
(V ie w )
P r o g r a m D im
A u d it D im
A c t iv ity D im
T r a n s a c t io n T y p eD im
F in a n c ia lT r a n s a c t io n D im
O r g a n iz a t io n D im
O r g a n iz a t io nH is to r y D im
H o m eO r g a n iz a t io n D im
(V ie w )
F u n d D im
A c c o u n t D im
R e n s s e la e r D a ta W a r e h o u s e P r o je c tF in a n c e B u d g e t T r a n s a c t io n F a c ts
M o n d a y , M a rc h 0 4 , 2 0 0 2 1V :\D a ta _ W a re h o u s e \1 -F in a n c ia l D W \D a ta M o d e l\C u r r e n t \F in a n c e O p e ra t in g F a c t D ia g r a m V e r 2 .v s d
A c t iv i ty D a te D im(V ie w )
G r a n t D im
14
Operating Ledger Financial Commitment Transactions
F i n a n c eE n c u m b r a n c eT r a n s a c t i o n F a c t sG r a in :E a c h O p e r a t in g L e d g e rE n c u m b r a n c e T r a n s a c t io n f o rt h e lo w e s t le v e l F O A P A L
T r a n s a c t i o n D a t eD i m
( V i e w )
P r o g r a m D i m
A u d i t D i m
F i n a n c i a lT r a n s a c t i o n D i m
A c t i v i t y D i m
T r a n s a c t i o n T y p eD i m
O r g a n i z a t i o n D i m
O r g a n i z a t i o nH i s t o r y D i m
H o m eO r g a n i z a t i o n D i m
( V i e w )
F u n d D i m
A c c o u n t D i m
R e n s s e l a e r D a t a W a r e h o u s e P r o j e c tF i n a n c e E n c u m b r a n c e T r a n s a c t i o n F a c t s
M o n d a y , M a r c h 0 4 , 2 0 0 2 1V : \ D a t a _ W a r e h o u s e \ 1 - F in a n c ia l D W \ D a t a M o d e l \C u r r e n t \ F in a n c e O p e r a t in g F a c t D ia g r a m V e r 2 .v s d
A c t i v i t y D a t e D i m( V i e w )
G r a n t D i m
15
Operating Ledger Financial Actual Transactions
F in a n c e A c t u a lT r a n s a c t io n F a c t sG r a in :E a c h O p e r a t in g L e d g e r A c tu a lT r a n s a c t io n fo r t h e lo w e s t le v e lF O A P A L
T r a n s a c t io n D a t eD i m
( V ie w )
P r o g r a m D i m
A u d i t D im
A c t iv i t y D im
T r a n s a c t i o n T y p eD i m
F in a n c ia lT r a n s a c t io n D im
O r g a n i z a t io n D im
O r g a n i z a t io nH is t o r y D im
H o m eO r g a n i z a t io n D im
( V ie w )
F u n d D i m
A c c o u n t D i m
R e n s s e la e r D a t a W a r e h o u s e P r o je c tF in a n c e A c t u a l T r a n s a c t io n F a c t s
M o n d a y , M a r c h 0 4 , 2 0 0 2 1V : \D a ta _ W a r e h o u s e \1 - F in a n c ia l D W \D a ta M o d e l \C u r r e n t \ F in a n c e O p e r a t in g F a c t D ia g r a m V e r 2 .v s d
A c t iv i t y D a t e D im( V ie w )
G r a n t D im
16
Operating Ledger Monthly Snapshot
O p e r a t i n g L e d g e rM o n t h l y S n a p s h o t
F i s c a l P e r i o dD i m
P r o g r a m D i m
A u d i t D i m
A c t i v i t y D i m
O r g a n i z a t i o n D i m
O r g a n i z a t i o nH i s t o r y D i m
H o m eO r g a n i z a t i o n D i m
( V i e w )
F u n d D i m A c c o u n t D i m
R e n s s e l a e r D a t a W a r e h o u s e P r o j e c tO p e r a t i n g L e d g e r M o n t h l y S n a p s h o t s
M o n d a y , M a r c h 0 4 , 2 0 0 2 1V : \ D a t a _ W a r e h o u s e \ 1 - F in a n c ia l D W \ D a t a M o d e l \C u r r e n t \ F in a n c e O p e r a t in g F a c t D ia g r a m V e r 2 .v s d
G r a n t D i m
F i n a n c i a lS t a t e m e n tC a t e g o r i e s
( F u t u r e )
17
General Ledger Monthly SnapshotR e n s s e l a e r D a t a W a r e h o u s e P r o j e c t
G e n e r a l L e d g e r M o n t h l y B a l a n c e s
M o n d a y , M a r c h 0 4 , 2 0 0 2 1V : \ D a t a _ W a r e h o u s e \ 1 - F in a n c ia l D W \ D a t a M o d e l \ C u r r e n t \ F i n a n c e O p e r a t i n g F a c t D ia g r a m V e r 2 . v s d
G e n e r a l L e d g e rM o n t h l y B a l a n c e
S n a p s h o t
A u d i t D i m
G L F u n dD i m e n s i o n
( V i e w )
A c c o u n tD i m e n s i o n
F i s c a l P e r i o dD i m e n s i o n
F i n a n c i a lS t a t e m e n t
C a t e g o r y D i m( F u t u r e )
18
References- http://www.rpi.edu/datawarehouse/
19
Wrap-up
QuestionsHomework
Please review data modelsProvide feedback on table and column descriptions.