|
Using Synonyms and Views to better handle the database level changes
This tip is equally applicable for OLTP scenario.
Synonyms or Alias
Synonym is like an alias for a table name at a physical level. The idea is that the query which an end-user tool fires to access the data, should be ideally using a synonym than the actual table name. A synonym (or alias) is just like the name of the book-mark in your internet browser, while the underlying link is pointing to the actual URL. You can maintain the same name of the book mark, while changing the underlying hyperlink.
Similarly, if you use synonym for providing access to the users and end-user tools, the advantage is that you can rename the underlying tables (as your dimensional model evolves), while still maintaining the same aliases or synonym. You can create synonyms for tables, views, stored procedure, function and some other objects. The idea is to have user-friendly synonyms and also to have a better change management.
Views
A view is a virtual table created on which the end-user tools or users can run queries. The views are created to make it easy and user-friendly access to the database. Views are typically created to provide a single table made out of multi-table, complex queries. For example, you may have a snow-flake schema, which you may convert into a star-schema by combining the dimensional table and its extension. OR you may like to create a single virtual table combining the fact table and dimensional table. |
