EDW Design Decision

View previous topic View next topic Go down

EDW Design Decision

Post  cia0417 on 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.

cia0417

Posts : 1
Join date : 2009-07-13

View user profile

Back to top Go down

Re: EDW Design Decision

Post  BoxesAndLines on 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?
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: EDW Design Decision

Post  caderoux on 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...

caderoux

Posts : 8
Join date : 2009-02-03

View user profile

Back to top Go down

Re: EDW Design Decision

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: EDW Design Decision

Post  rjp73 on 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.

rjp73

Posts : 4
Join date : 2009-07-24

View user profile

Back to top Go down

Re: EDW Design Decision

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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