Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

EDW Design Decision

5 posters

Go down

EDW Design Decision Empty EDW Design Decision

Post  cia0417 Mon Jul 13, 2009 4:45 pm

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.


Posts : 1
Join date : 2009-07-13

Back to top Go down

EDW Design Decision Empty Re: EDW Design Decision

Post  BoxesAndLines Mon Jul 13, 2009 5:07 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

EDW Design Decision Empty Re: EDW Design Decision

Post  caderoux Mon Jul 13, 2009 6:43 pm

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...


Posts : 8
Join date : 2009-02-03

Back to top Go down

EDW Design Decision Empty Re: EDW Design Decision

Post  ngalemmo Mon Jul 13, 2009 8:55 pm

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.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

EDW Design Decision Empty Re: EDW Design Decision

Post  rjp73 Fri Jul 24, 2009 11:35 am

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.


Posts : 4
Join date : 2009-07-24

Back to top Go down

EDW Design Decision Empty Re: EDW Design Decision

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum