group5 accounting
TRANSCRIPT
Chapter 7 : Journey to Accounting
Prepared by: Akshay Kotalwar
Arunesh Soni Mitali Barhanpurkar
Rachit MishraTao Zhang
JSOM, UT Dallas
2
Types of HierarchiesJSOM, UT Dallas
2
1) Fixed Depth Hierarchy
JSOM, UT Dallas
3
• SLIGHTLY RAGGED • RAGGED
JSOM, UT Dallas
3
2) Variable Depth Hierarchy
JSOM, UT Dallas
4
RECURSIVE POINTER
• Way to represent parent/child tree structure.• Points from child node to parent node.
1
2
4 5
3
6 7
8 9Recursive Pointer
JSOM, UT Dallas
5
1
2
4 5
3
6 7
8 9
PROBLEM 1 : ROLL UP ISSUE
•Roll- ups don’t end up at same level•They are complex and slow.
JSOM, UT Dallas
6
PROBLEM 2 : SHARED OWNERSHIP
1
2
4 5
3
6 7
8 9
• Consider 60% ownership of 5 and 40% ownership of 6.
• Paths have to be modified.
7
JSOM, UT DallasT
1
2
4 5
3
6 7
8 9
PROBLEM 3 : ORGANISATIONAL CHANGE
• Major changes in the paths have to be made.
• Reporting of past and future becomes an issue.
The need to solve these problems brings us to the solution- the Bridge table!
JSOM, UT Dallas
8
Bridge Table• Essentially ‘bridges’ a dimension and fact table. • Preserves contents of dimensions and facts in raw
form.• Solving the previously discussed problems of the
recursive solution.
Organization Dimension
Organization Key (PK)Organization NameOrganization Location…
Organization Map Bridge
Parent Organization Key (FK)Child Organization Key (FK)Depth from ParentHighest Parent FlagLowest Child Flag
General Ledger FactAccount Key (FK)Organization Key (FK)……AmountBalance
JSOM, UT Dallas
9
Bridge Table - Structure• Record whole rollup structure• Define its grain as each path in the tree
1
2 5
3 4
Parent Key
Child Key
Depth from parent
Highest parent flag
Lowest child flag
1 1 0 Y N1 2 1 Y N
1 3 2 Y Y1 4 2 Y Y
1 5 1 Y Y2 2 0 N N
2 3 1 N Y2 4 1 N Y
3 3 0 N Y
4 4 0 N Y5 5 0 N Y
JSOM, UT Dallas
10
Bridge Table – Shared ownership variant• Department 4 outsources 60% of its work to another
department 5.
• Example of a shared ownership.
1
2 5
3 4
Parent Key
Child Key
Depth from parent
Highest parent flag
Lowest child flag
Ownership Percent
1 1 0 Y N1 2 1 Y N1 3 2 Y Y1 4 2 Y Y1 5 1 Y Y2 2 0 N N2 3 1 N Y2 4 1 N Y 40%3 3 0 N Y4 4 0 N Y5 5 0 N Y5 4 1 N Y 60%
JSOM, UT Dallas
11
Bridge Table – Shared ownership variant• Department 4 outsources 60% of its work to another
department 5.
• Example of a shared ownership.
JSOM, UT Dallas
11
1
2 5
3 4
Parent Key
Child Key
Depth from parent
Highest parent flag
Lowest child flag
Ownership Percent
1 1 0 Y N1 2 1 Y N1 3 2 Y Y1 4 2 Y Y1 5 1 Y Y2 2 0 N N2 3 1 N Y2 4 1 N Y 40%3 3 0 N Y4 4 0 N Y5 5 0 N Y5 4 1 N Y 60%
JSOM, UT Dallas
12
JSOM, UT Dallas
12
Bridge Table – Shared ownership variant• Department 4 outsources 60% of its work to another
department 5.
• Example of a shared ownership.
JSOM, UT Dallas
12
1
2 5
3 4
Parent Key
Child Key
Depth from parent
Highest parent flag
Lowest child flag
Ownership Percent
1 1 0 Y N1 2 1 Y N1 3 2 Y Y1 4 2 Y Y1 5 1 Y Y2 2 0 N N2 3 1 N Y2 4 1 N Y 40%3 3 0 N Y4 4 0 N Y5 5 0 N Y5 4 1 N Y 60%
JSOM, UT Dallas
13
Bridge Table - Advanced•Handle time varying ragged hierarchies
JSOM, UT Dallas
13
1
2 5
3 4
1
2 5
3 4
Before 11/01/16
After 11/01/16
JSOM, UT Dallas
14
Bridge Table – Advantages • Offers exceptional flexibility
• Accommodates SCD type 2 changes
• Accommodates tree structure changes
JSOM, UT Dallas
15
A quick revisit! Problems Solution
1. Slow rollup issues Percent ownership column
2. Multiple parents Begin and end dates
3. Organizational changes Lowest child-flag
JSOM, UT Dallas
16
THANK YOU!