EDW Design Decision

There is currently a difference of opinion on how the EDW should be structured for ad-hoc reporting. One camp suggests that the dimensional design can easily support both ad-hoc and analytical reporting. While the other suggests that the dimensional model should not be used for ad-hoc purposes since it is not easily understood by end-users. This other camp wants to create a flat structure for ad-hoc reporting. Any opinions on what should be done? Thanks in advance.

The dimensional model is pretty flat already. I run ad-hocs on a weekly basis against my dimensional model. Who is this other camp?

I would say that the dimensional model (within a single star), is the easiest for users to do ad hoc reports on (assuming you expose the star as a flattened view).

They are less likely to multiply results with mistaken joins without understanding the many-to-one or many-to-many relationships as they would in a normalized transactional database.

Of course, as they combine stars, the same old problems can creep back in...

If you are using a front end tool, it shouldn't matter that the underlying DB is dimensional... the end users won't see it nor should they care.

If they are accessing it directly using SQL then you are seeing the effect of BFF syndrome (i.e. Big Flat File) common among typical spreadsheet users. Don't get me wrong... when it comes to dimensional analysis, almost any business user fully understands the concepts, in fact, dimensional analysis's roots have been firmly entrenched in business analysis for a very, very long time. It's just that they don't want to or understand joining tables.

If you don't provide a front end tool, the best approach is to provide views with all the joins predefined and let they access the views. Most databases will pare the view query down and only perform joins necessary to resolve the query against the view.

a clean star schema is far and away the easiest data model to interpret - unless you have been writing queries against relational sources for a long time, or have spent your career in a mainframe shop.

that said, for everyone else, a star schema is an excellent platform for adhoc reporting purposes. to appease the other side, you can offer to build a view that consolidates all of the attributes and measurements into a single entity. this a a common practice anyway to give users a leg up who want to browse data but don't want to bother joining tables or learning a tool.