Single fact table vs multiple fact tables - what is the right way in a dimensional model?

Hi

Had a discussion yesterday around wheater dimensional modeling á lá Ralph Kimball is about having star schema only in the sense that each model must only have one fact table, or can we put multiple fact tables, with diffrent grain regaring the same business process, utilizing some of the same dimensions?

I mostly worked with MS cubes, so the risk of end users joining a fact table on the wrong grain was never a problem, as the cube definition solevs this problem. but now I work for a company that will utilized both cubes and relational based data marts.

Appriciate your input.

Regards
Rafi Asraf

rafi asraf wrote:Had a discussion yesterday around wheater dimensional modeling á lá Ralph Kimball is about having star schema only in the sense that each model must only have one fact table, or can we put multiple fact tables, with diffrent grain regaring the same business process, utilizing some of the same dimensions?

Well, yes. A 'star schema' by definition is a single fact table and its dimensions. But, a dimensional data warehouse is a collection of star schema sharing common (conformed) dimensions. This creates an environment that allows modular expansion of the scope of the data warehouse by adding new facts and occasionally new dimensions as needed.