Reporting strictly on a dimension

[ryno1234]

Dimensions offer a lot of information to report on and with the inclusion of effective / expiration dates, they also offer a view of events over time. This makes it possible to offer interesting information without the use of a fact table.

As I'm actively dealing with "employees" right now, I'll use them as an example: An employee's job title may change over time, thus we stamp a new dimension record and update the previous records effective / expiration dates.

In this situation, I could figure out how many employees were in each job title for any given day without the need of a fact table.

What is the general stance on this?

[ngalemmo]

Yeah you could, but that is not dimensional design. And maybe you can answer that particular question, but what about other analysis? How difficult would it be to construct any query relating to an employee's tenure or staff turnover?

The basic design principle is to separate things into business events or states (facts) and their contexts (dimensions). A properly designed star schema allows a user to perform all manner of analysis of the data using a simple, repeatable query pattern.