Dimensional Model Tips

[RM7]

Hi

We are trying to implement Dimensional Modelling for the report requirement for the user group. The overall source system can be represented as:

I have transactional information / measures available for different services and there are different attributes available for each service and the same applies to Orders, Privileges, Publishes & Reviews. Do we need to have fact table for each service and each for order or to create one big fact at the lowest grain available? 

 
We are building this to accommodate Operational reporting. So I can’t aggregate any information and need to bring in the same grain of information that is available in source.
 
Any suggestions would be appreciated.


Thanks
RM

[rathjeevesh]

For operational reporting I would suggest not to go for star schema. You can adopt a denormalized version (optimized for reporting) of ER model and bring the concept of generalization and specialization for data storage, based upon what you have shared regarding the source data.

-JR