• Article

Lesson 9: Specifying a Lookup Entity

Some entities might contain only one attribute of interest to individuals creating reports. For example, the Name attribute is probably the only interesting attribute to Report Builder users within the Product SubCategory entity. Instead of requiring model users to navigate to the entity that contains the one attribute every time they want to add the attribute to their report, you can change the role to a lookup entity. When a role is turned into a lookup entity, the identifying attribute appears as a field within the parent entity. For example, if the Product SubCategory entity is changed to a lookup entity, the Product SubCategory Name attribute appears as a field of the Product entity to Report Builder users instead of a role.

In general, it is useful to change a role to a lookup entity when your information is in a hierarchical format. For example, if you have separate entities for City and State/Province, and State/Province is a role of the City entity, you might want to make the State/Province role a lookup entity so that it appears as a field within the City entity. In addition, if the Country entity is a role to the State/Province entity, you might want to mark the Country role as a lookup entity and then promote it so that it too is treated as a lookup entity and appears in the City entity as a field to Report Builder users too. Denormalize a hierarchy is a common modeling term for this is action.

Entities that you want to use as lookup entities can have only one attribute specified in the IdentifyingAttribute property. An identifying attribute is an attribute that uniquely identifies the entity, for example, the Product Category Name attribute uniquely identifies each product category. The attribute specified as the identifying attribute is the attribute that appears in the parent entity when the entity is made into a lookup entity.

To turn an entity into a lookup entity, change the IsLookup property to True. To turn a hierarchical chain of entities into lookup entities, you need to also set the PromoteLookup property to True. For example, suppose the Country role is a lookup entity within the State/Province entity and now you want the Country role to appear as a field in the City entity, you would specify True for the PromoteLookup property on the Country role from the State entity as well. In this lesson, you will change the Product Subcategory role so that it is a lookup entity for the Product entity and then you will make the Product Category role a lookup of the Product Subcategory entity and then promote the role so that it appears as a field in the Product entity as well.

To specify a lookup entity

  1. Return to the AdventureWorks model in the SQL Server Business Intelligence Development Studio window.

  2. In the Tree view, expand the Product Details folder.

  3. Select the Product Subcategory entity.

    The Product Subcategory entity has only one IdentifyingAttribute: the Name field. A lookup entity can have only one IdentifyingAttribute.

  4. In the Properties window, locate the IsLookup property, and then select True.

    When the report model is deployed, the Product Subcategory entity will appear as a field within the Product entity.

To promote an entity to a lookup entity

  1. In the Tree view, select the Product Category entity.

  2. In the Properties window, locate the IsLookup property, and then select True.

  3. In the Tree view, select the Product Subcategory entity.

  4. In the List view, select the Product Category role.

  5. In the Properties window, locate the PromoteLookup property and then select True.

    Setting this property promotes the Product Category lookup entity so it also appears as an attribute in the Product entity.

To reorganize the attributes within an entity

  1. In the Tree view, select the Product entity.

  2. In the List view, right-click the Product Subcategory entity, and then select Move up.

  3. Click Move up until the Product Subcategory entity appears below the Name attribute.

  4. On the File menu, click Save All.

Next Steps

Now, users do not need to navigate to the Product Category or Product Subcategory entities to locate the category or subcategory names. Instead, each entity appears as a field in the Product entity, which the user can add to their report just like any other field.

In the next lesson, you will change the properties of one entity so that it inherits the fields from another entity. See Lesson 10: Inheriting Properties from Other Entities.