0 Replies Latest reply: Jan 10, 2013 9:16 AM by jkoradba RSS

    dim Business Identifiers - use source table primary key (surrogate) or not?

    jkoradba
      The OWB doco states:

      "The *_business identifier_* must be unique across the level and is always derived from the *_natural key_* of the data source." I included the full definition lower.

      For most part I have been using the primary key (surrogate) from the source table. A numeric key, non-business use. However, we do use these in our other backend development work, e.g. for reporting/dashboarding. I go back to the OWB definition and think that in fact the recommendation is not to use such keys. Is this correct or not? Certainly, there are other attributes that are certainly more 'natural', but with some there is always the possibility of some change - e.g. a change or mistake in name that should be unique. Consider a company name that can change, or a company can merge or be acquired.

      What do you tend to use for dimension level business identifiers? What is the right way, if there is such?


      "A business identifier consists of a user-selected list of attributes. The business identifier must be unique across the level and is always derived from the natural key of the data source. The business identifier uniquely identifies the member. For example, the business identifier of a Product level can be its Universal Product Code (UPC), which is a unique code for each product.

      Note: For a dimension that has a MOLAP implementation, the business identifier can consist of only one attribute.


      The business identifier does the following:

      Identifies a record in business terms

      Provides a logical link between the fact and the dimension or between two levels

      Enables the lookup of a surrogate key

      When you populate a child level in a dimension, you must specify the business identifier of its parent level. When you populate a cube, you must specify the business identifier of the dimension level to which the cube refers."

      Edited by: jkoradba on Jan 10, 2013 7:16 AM

      Edited by: jkoradba on Jan 10, 2013 7:16 AM