Output Options - Kuali Wiki

bloatdecorumSoftware and s/w Development

Oct 30, 2013 (3 years and 9 months ago)

90 views

DATA WAREHOUSING AND REPORTING

Kuali

Financial

Date Warehousing and Reporting


Bill Overman


Indiana University


Dylan Cooper


University of Arizona


Susan Moore


University of California, Davis

Date Warehousing and Reporting

Agenda


Overview of approach taken by each school


Hints on important data


Technical hints


Demonstrations


Note: These applications do NOT come with KFS

Indiana University


Datagroups


Both GL and reference tables


Denormalized


Important fields to consider


Returns data to spreadsheet


Datagroups

from GL tables


General Ledger Entry


Transaction level detail


Includes a description


General Ledger Balance


Summary by accounting string

General Ledger Entry


Balance Type Code


budget, actual, encumbrance


University Fiscal Period Code denotes month


Object Type, Amount and Debit/Credit Flag work
together to determine positive/negative sign


General Ledger Balance


Balance Type Code


Buckets for monthly balances


Other buckets


Account Line Annual Balance Amount


Financial Beginning Balance Amount


Contract and Grant Beginning Balance Amount

Denormalizing


Account


Name


Fiscal Officer


Organization


Expiration Date, Closed Indicator


Sub
-
Fund Group


Fund Group


Higher Education Function Code

Denormalizing


Object Code


Object Level Code


Object Consolidation Code


Reports to Object Code


Datagroups

from Reference Tables


Account/Organization
Datagroup


Object Code
Datagroup


Sub Accounts


Sub Object Codes


Year End Snapshots

The University of Arizona



Dylan Cooper

520
-
626
-
5214

dacooper@email.arizona.edu

-
Display PowerPoint Notes
-

KFS DW/BI Team

Doug Hester


Senior Technical

Dylan Cooper


Senior Technical

Nargis

Memon



Junior Technical

Ned
Tomsheck



Accountant

Nevil

Pozholiparambil



Masters Student/Junior Technical


UAccess

Analytics


The University of Arizona Basics


Front end: Oracle Business Intelligence EE Plus


ETL: IBM
DataStage


Back end: Oracle (PeopleSoft EPM)


Data


Star schemas


Data Marts (in development)
-

General Ledger, Chart of
Accounts, Labor Ledger, Capital Assets and Purchasing

Trial Balance Report

Trial Balance Report

Trial Balance Report

Trial Balance Report

OBIEE Administration Tool

Summary Account Activity Star

Account Activity Fact

Account

Dimension

Object Code
Dimension

Fiscal Period
Dimension

Account Activity Fact

Keys for Dimensions

Month Net Measures

YTD Measures

Prior Month Measures

Prior Year Measures

One row for each
account, object code
and fiscal period

combination

Summary Table

Account Activity Fact

Account

Income Amt

Expense Amt

12345

20.00

200.00

ABCDE

100.00

500.00

Account

Acct Category

Amount

Income

Expense

Income

Expense

10.00

100.00

50.00

500.00

12345

12345

ABCDE

ABCDE

Transaction Data

Pivoted Data

Account Dimension

Chart Info

Organizational Unit Info

Tracks changes in
account information

Slowly Changing Dimension

Account Type Info

Fund Info

Account Info

Sub
-
Account Info

KFS Staging Area

CHANGES

History Tables

FACTS &
DIMENSIONS

Report Tables

Staging Tables

COPY OF KFS

Conclusions

Dimensional Warehouse vs. Traditional Warehouse


Specialized Tools vs. General Purpose Tools

KFS Decision Support SIG

Tuesday: 6:15


6:45 Conference Room 17

Wednesday: 11:00


noon Conference Room 12


James Singleton

Cornell University

jim.singleton@cornell.edu


Tina
Golini

Jessee

Indiana University

cgolini@indiana.edu

University of California, Davis

UC Davis Decision Support


Decision Support Basics


Front end: Cold Fusion


Bank end: Oracle


Data


Source
-

General Ledger and reference tables


Denormalized

data


Month end snapshot


ensures reports are
reproducible

UC Davis Decision Support


Access


Broad campus access


Few access restrictions


Account type limits budget provisions


Payroll detail limited to user’s organization

UC Davis Decision Support

Output

Options

UC Davis Decision Support

Demonstration