0

In creating data warehouses, I'm used to creating views on top of the tables so that users are querying those views and not the tables. They're never given access to the tables anyways.

Now in regards to the current data warehouse in question, there are going to be joins among several tables to create the 'end-table' or report. If we go the route of using views, should those joins be done at the view-level, or should we create another set of tables with the joins and just build basic views on top of that?

simplycoding
  • 2,770
  • 9
  • 46
  • 91
  • 2
    The joins should be done at the view level. You're trying to make database access easier for users, not just following a bureaucratic rule about views. – Gilbert Le Blanc Sep 22 '15 at 22:12

1 Answers1

0

Two things I would consider while making the decision:

  1. Performance: If you have joins populating physical tables and have just views on top of these tables, your queries (on the views) will be fast as there are no joins happening on the fly.
  2. Scalability and Maintenance: If you start creating a view with joins for each report users need, in few months you will have a lot of views (some might even be similar in logic but noone would know which one to reuse). This will become a nightmare very soon for the DBA folks if your DWH is going to have a lot of reports running on it and you have a view for each report.

Well, like in most problems in database management, there is no "one correct" answer. But yeah, in general a DWH will have a lot of reports running on top. So, creating views just doesn't cut it in the long run.

Paladin
  • 570
  • 3
  • 13