Sometimes the same data element carries different names in different dimensional tables, due to the following reasons..

• Inconsistencies in data element names in source systems is so huge (like the same customer ID data element is named in ten different ways in ten different tables- cust_ID, CustomerID, C_ID..).
• There being multiple teams designing different parts of the dimensional models.
• Your dimensional model having evolved over time.

To accelerate the DW project, you may create a mapping table in metadata which can link these different names. You do it as you don't want to disturb your transformation routines, and want to have an alternate method to align your dimensional models.

The reason you should not do this is:

• Data warehouse is storehouse. People may or may not choose to refer to the mapping tables or Meta-data definitions. This may severely impact the potential of DW, especially when you need drill across capabilities.
• With field-names being different, you will end up having more transformation and loading routines to create and run.
• The maintenance of dimensional model becomes more complex over time.
• You end up promoting the culture of inconsistent naming in the DW and source system.

Benefits of same names and definitions across all schemas

• High productivity: Same names mean that there is no confusion and same queries can be used in different schemas.
• Automated joins across schemas: As you might see in the multi-cube architecture, you can join two schemas if they share a common dimension. With same names, it becomes easier to create these joins.

A good naming strategy has to be:

• Intuitive and consistent: the naming approach should be easily understood and should be applied universally.
• Extendible: With new facts and dimensions and attributes, the naming structure should be able to absorb these changes.
• Easy to understand: Names should not be too cryptic. The names should be more in a business like language (Use Customer_ID instead of Cusid)
• Should have identifiers linked to the business process, the dimension, fact table to which it belong etc.

Naming strategy should be defined by the dimensional modeling team as they have a got a wide view of all the dimensions and fact tables involved. Post the first draft, we have to go through the iterative process of fine-tuning given the feedback from business, technical and information managers.