Star schema -- modeling a company hierarchy

Hi Daniel,

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

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!

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 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?

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

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

Thanks!

B-) Glad we could help. Thx for the grade and points!