Star schema -- modeling a company hierarchy
I am pretty rusty on star schemas and OLAP. I haven't worked on something like this in about 5 years, so please excuse a newbie question.
My fact table will come from income statements from a company with a hierarchy of companies. The piece giving me trouble is modeling that company hierarchy.
Parent Company has X Asset Groups:
Naturally, my analysis is required to be able to roll the figures up at any level.
How should I model the company dimension?
The approach I've sketched is:
I have the feeling that's very wrong ... but can someone steer me straight?
Thanks!
My fact table will come from income statements from a company with a hierarchy of companies. The piece giving me trouble is modeling that company hierarchy.
Parent Company has X Asset Groups:
Alpha Assets
Bravo Assets
Charlie Assets
Each Asset Group has 2 subsidiaries:
Alpha East
Alpha West
Bravo North
Bravo South
Charlie Legal
Charlie Financial
Naturally, my analysis is required to be able to roll the figures up at any level.
How should I model the company dimension?
The approach I've sketched is:
dim_Company
CompanyID
Name
TypeOfCompany (e.g. Asset Group, Subsidiary, Parent
ParentID (references CompanyID)
I have the feeling that's very wrong ... but can someone steer me straight?
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I probably don't need to list the parent company. I think that's what you're telling me, Fibo, and it makes sense.
Kent, you're certainly right that I have a time dimension, with granularity down to the month. My dimension table for that looks like
On the company structure, I think change will be sufficiently infrequent that I can get by without a slowly changing dimension. I worked with those in the past and found them kind of tricky -- probably due to inexperience.
Does this structure look better for the company dimension table?
Thanks!
Kent, you're certainly right that I have a time dimension, with granularity down to the month. My dimension table for that looks like
FullDate -- the only entries being the 1st of every month as that's what I'm loading in my fact table
Month
Quarter
Year
On the company structure, I think change will be sufficiently infrequent that I can get by without a slowly changing dimension. I worked with those in the past and found them kind of tricky -- probably due to inexperience.
Does this structure look better for the company dimension table?
CompanyID
Name
AssetGroupName
Thanks!
Hi Daniel,
I don't know enough about your model to say "yes" and instinctively want to say "no". The granularity just seems wrong.
Can you elaborate on the relationship between the company, asset group, and asset items?
I don't know enough about your model to say "yes" and instinctively want to say "no". The granularity just seems wrong.
Can you elaborate on the relationship between the company, asset group, and asset items?
ASKER
I tried to explain the relationship between the companies in the original post. Maybe if I fill in my new proposed table, what I'm meaning will be more clear.
ID Name AssetGroupName
1 Alpha East Alpha Asset Group
2 Alpha West Alpha Asset Group
3 Bravo North Bravo Asset Group
4 Bravo South Bravo Asset Group
5 Charlie Legal Charlie Asset Group
6 Charlie Financial Charlie Asset Group
This, I think, allows me to roll up all the Bravo asset Group expenses, roll up all expenses for the entire company, or drill down to a single subsidiary like Bravo North.
Am I still missing it?
ID Name AssetGroupName
1 Alpha East Alpha Asset Group
2 Alpha West Alpha Asset Group
3 Bravo North Bravo Asset Group
4 Bravo South Bravo Asset Group
5 Charlie Legal Charlie Asset Group
6 Charlie Financial Charlie Asset Group
This, I think, allows me to roll up all the Bravo asset Group expenses, roll up all expenses for the entire company, or drill down to a single subsidiary like Bravo North.
Am I still missing it?
Ok, thanks. That's pretty much what I was expecting to see.
The finest granularity in the example is the asset group. Each one is controlled by 1 or more companies. In a star schema, that's two dimensions. In a pseudo-star (please forgive the term) you almost have a dimension of a dimension. (Yucko....)
You've got a granularity where an asset group is owned or controlled by multiple companies. The database really should have a dimension on the asset group and another on the company. It's much more flexible. I've added three lines to your example. If Company and Assets are separate dimensions, the addition 3 items are trivial to incorporate. If Company and Assets are tightly coupled in the same table, this is a lot tougher and you actually lose some of the performance benefits of the start schema.
ID Name AssetGroupName
1 Alpha East Alpha Asset Group
2 Alpha West Alpha Asset Group
3 Bravo North Bravo Asset Group
4 Bravo South Bravo Asset Group
5 Charlie Legal Charlie Asset Group
6 Charlie Financial Charlie Asset Group
7 Charlie Legal Charlie Retirement Assets
8 Charlie Legal Alpha Charlie joint Assets
9 Alpha East Alpha Charlie joint Assets
The finest granularity in the example is the asset group. Each one is controlled by 1 or more companies. In a star schema, that's two dimensions. In a pseudo-star (please forgive the term) you almost have a dimension of a dimension. (Yucko....)
You've got a granularity where an asset group is owned or controlled by multiple companies. The database really should have a dimension on the asset group and another on the company. It's much more flexible. I've added three lines to your example. If Company and Assets are separate dimensions, the addition 3 items are trivial to incorporate. If Company and Assets are tightly coupled in the same table, this is a lot tougher and you actually lose some of the performance benefits of the start schema.
ID Name AssetGroupName
1 Alpha East Alpha Asset Group
2 Alpha West Alpha Asset Group
3 Bravo North Bravo Asset Group
4 Bravo South Bravo Asset Group
5 Charlie Legal Charlie Asset Group
6 Charlie Financial Charlie Asset Group
7 Charlie Legal Charlie Retirement Assets
8 Charlie Legal Alpha Charlie joint Assets
9 Alpha East Alpha Charlie joint Assets
ASKER
OK, so 2 tables? A bit more like snowflake than star?
Subsidiaries
ID Name AssetGroupID
1 Alpha East 1
...
6 Charlie Legal 3
AssetGroups
ID Name
1 Alpha Asset Group
2 Bravo Asset Group
3 Charlie Asset Group
Subsidiaries
ID Name AssetGroupID
1 Alpha East 1
...
6 Charlie Legal 3
AssetGroups
ID Name
1 Alpha Asset Group
2 Bravo Asset Group
3 Charlie Asset Group
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
B-) Glad we could help. Thx for the grade and points!
A Star Schema usually models "activity over time". It's key component is a time dimension that allows you to report "status" at any point in the data's history.
Based just on what you've posted, you should probably have 3 dimensions. (And more as other requirements are revealed.)
Your fact table will be the asset information. Monthly statements, daily changes, etc. Whatever granularity works for you. Then you'll have the time dimension, incremented at the granularity of the fact table. The last two dimensions are Company and Asset. The Company dimension contains all of the searchable Company items, and the Asset dimension contains all of the searchable asset items. The Asset dimension may be as simple as asset type and asset name.
You may be tempted to include Asset Owner or Asset Holder in the Asset table, but that's really just Company information, already contained in another table.
Good Luck,
Kent