I need a mental process to design an OLAP database...
Essentially for standard relational it'd be (loosely)
Identify Entities Identify Relationships Identify Properties of Entities
For each property ensure property can be related to only one entity ensure property is directly related to entity
For OLAP databases, I understand the terminology, the motivation and the structure, but have no clue as to how to decompose my relational model into an OLAP model.
Identify Dimensions (or By's) These are anything that you may want to analyse/group your report by. Every table in the source database is a potential Dimension. Dimensions should be heirarchical.
Identify Measures These are the KPI's or the actual numerical information your client wants to see, these are usually capable of being aggregated, therefore any non flag, non key int field in the source database is a potential measure.
Arrange in star schema, with Measeures in the center 'Fact' table, and FK relations to applicable Dimension tables. Measures should be stored at the lowest dimension heirachy level.