data warehouse overview (financial analysis) may 02, 2002
TRANSCRIPT
![Page 1: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/1.jpg)
Data Warehouse Overview (Financial Analysis)
May 02, 2002
![Page 2: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/2.jpg)
2
Agenda Data Warehouse Review Rensselaer DW Overview Intro. to Financial Analysis Homework!
![Page 3: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/3.jpg)
3
Data Warehouse Review
A data warehouse is a subject-oriented, integrated, time-varying, non-volatile collection of data in support of the management's decision-making process."
--- Bill Inmon
![Page 4: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/4.jpg)
4
Data Warehouse Review
General Characteristics Subject Oriented Integrated Time Varying Consistent Non-volatile (usually) Used in management decision making
process
![Page 5: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/5.jpg)
5
Data Warehouse Review Key Concepts
Data Warehouse vs. Data Mart Data Mart – Single subject area Data Warehouse – Integrated Data Marts
Star Schema Dimensions
Hierarchies Descriptive Attributes
Fact Tables Metadata
![Page 6: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/6.jpg)
6
Definitions – cont. Star Schema Common method of organizing and relating dimensions and
facts to support end user reporting and analytical needs. Dimension Table of related attributes typically organized in a hierarchy
used to describe business facts. Fact Table Table of common data elements used to measure a business.
Typically numeric values. Metadata Repository of business information describing the data
elements, business rules, processes and sources systems of the warehouse.
![Page 7: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/7.jpg)
7
Data Warehouse/Data Mart
Operating Ledger Financial Transactions
General Ledger Monthly Snapshot
General Ledger Financial Transactions (Future)
Operating Ledger Monthly Snapshot R
ensse
laer
Data
Ware
house
Meta
data
Brio End User Query and Reporting,
Dashboards
Data Mart
Rensselaer Data Warehouse
![Page 8: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/8.jpg)
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 Dim
•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
![Page 9: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/9.jpg)
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
Operating Monthly Snapshot Facts
Fund DimensionFiscal Period Dimension
Org
aniz
atio
n
Dim
ensi
on
![Page 10: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/10.jpg)
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
Brio - End User Reporting and Analysis
Metadata
Operating Ledger Monthly Snapshot
Oracle Database
![Page 11: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/11.jpg)
11
Rensselaer DW Overview Three fiscal years (FY2000 – 2002)
Operating Ledger transaction detail Operating Ledger monthly snapshots General Ledger monthly snapshots
Refreshed daily Troy and Hartford information Key Data Hierarchies
Organization (current & month end) Fund, Program, Account (current & FY end)
ITD summaries for Research Grants
![Page 12: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/12.jpg)
12
Intro. to Financial Data Marts
Operating Ledger Financial Transactions Operating Ledger Monthly Snapshot General Ledger Monthly Snapshot
![Page 13: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/13.jpg)
13
Operating Ledger Financial Budget Transactions
Finance BudgetTransaction Facts
Grain:Each Operating Ledger BudgetTransaction for the lowest levelFOAPAL
Transaction DateDim
(View)
Program Dim
Audit Dim
Activity Dim
Transaction TypeDim
FinancialTransaction Dim
Organization Dim
OrganizationHistory Dim
HomeOrganization Dim
(View)
Fund Dim
Account Dim
Rensselaer Data Warehouse ProjectFinance Budget Transaction Facts
Monday, March 04, 2002 1V:\Data_Warehouse\1-Financial DW\Data Model\Current\Finance Operating Fact Diagram Ver 2.vsd
Activity Date Dim(View)
Grant Dim
![Page 14: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/14.jpg)
14
Operating Ledger Financial Commitment Transactions
FinanceEncumbranceTransaction Facts
Grain:Each Operating LedgerEncumbrance Transaction forthe lowest level FOAPAL
Transaction DateDim
(View)
Program Dim
Audit Dim
FinancialTransaction Dim
Activity Dim
Transaction TypeDim
Organization Dim
OrganizationHistory Dim
HomeOrganization Dim
(View)
Fund Dim
Account Dim
Rensselaer Data Warehouse ProjectFinance Encumbrance Transaction Facts
Monday, March 04, 2002 1V:\Data_Warehouse\1-Financial DW\Data Model\Current\Finance Operating Fact Diagram Ver 2.vsd
Activity Date Dim(View)
Grant Dim
![Page 15: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/15.jpg)
15
Operating Ledger Financial Actual Transactions
Finance ActualTransaction Facts
Grain:Each Operating Ledger ActualTransaction for the lowest levelFOAPAL
Transaction DateDim
(View)
Program Dim
Audit Dim
Activity Dim
Transaction TypeDim
FinancialTransaction Dim
Organization Dim
OrganizationHistory Dim
HomeOrganization Dim
(View)
Fund Dim
Account Dim
Rensselaer Data Warehouse ProjectFinance Actual Transaction Facts
Monday, March 04, 2002 1V:\Data_Warehouse\1-Financial DW\Data Model\Current\Finance Operating Fact Diagram Ver 2.vsd
Activity Date Dim(View)
Grant Dim
![Page 16: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/16.jpg)
16
Operating Ledger Monthly Snapshot
Operating LedgerMonthly Snapshot
Fiscal PeriodDim
Program Dim
Audit Dim
Activity Dim
Organization Dim
OrganizationHistory Dim
HomeOrganization Dim
(View)
Fund Dim Account Dim
Rensselaer Data Warehouse ProjectOperating Ledger Monthly Snapshots
Monday, March 04, 2002 1V:\Data_Warehouse\1-Financial DW\Data Model\Current\Finance Operating Fact Diagram Ver 2.vsd
Grant Dim
FinancialStatementCategories
(Future)
![Page 17: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/17.jpg)
17
General Ledger Monthly SnapshotRensselaer Data Warehouse Project
General Ledger Monthly Balances
Monday, March 04, 2002 1V:\Data_Warehouse\1-Financial DW\Data Model\Current\Finance Operating Fact Diagram Ver 2.vsd
General LedgerMonthly Balance
Snapshot
Audit Dim
GL FundDimension
(View)
AccountDimension
Fiscal PeriodDimension
FinancialStatement
Category Dim(Future)
![Page 18: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/18.jpg)
18
References- http://www.rpi.edu/datawarehouse/
![Page 19: Data Warehouse Overview (Financial Analysis) May 02, 2002](https://reader035.vdocuments.us/reader035/viewer/2022062620/551a94fa550346e0158b532f/html5/thumbnails/19.jpg)
19
Wrap-up
Questions Homework
Please review data models Provide feedback on table and column
descriptions.