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:
Each Asset Group has 2 subsidiaries:
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:
TypeOfCompany (e.g. Asset Group, Subsidiary, Parent
ParentID (references CompanyID)
I have the feeling that's very wrong ... but can someone steer me straight?
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.