data warehouse overview (financial analysis) warehouse overview (financial analysis) may 02, 2002. 2...

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.