The Customer dimension with hierarchy

We would like to create a dimension based on the Customer dimension table. When exploring the Customer table we can see that this table holds multiple fields of information per entry, such as Name, Address and City. Each field can become a useful attribute in the new dimension, but, as we shall see, they can also be combined into a hierarchy with increasing levels of detail.

Furthermore, when exploring the related Country_Region table we can see that this table holds information about the country location of the customer. The two tables are related to each other by the foreign key Country_Region Code from the Customer table and the primary key Code from the Country_Region table.

As with the other dimensions we will create this dimension as a new dimension in the Dimensions folder of the current Analysis Services project by going through the Dimension Wizard.

This time, since we are going to use information from both tables, the Customer table itself and the related Country_Region, to build our dimension, we must ensure that we include the related table in the Dimension Wizard.

As usual, the primary key column, the No column, is a mandatory attribute. But apart from that, we may notice that the Code column is also selected as an attribute by default. Remember that the Code column is the foreign key that relates to Country_Region table.

Furthermore, when working with attributes from multiple dimension tables, you actually gain a small advantage if you select the desired attributes through the wizard.

Without selecting further attributes at this point we will end up with a dimension with just two attributes: No and Code.

When we complete the wizard, we will see that the new dimension’s Data Source View includes the two related tables, and now we want to create a Hierarchy on basis of these attributes. The purpose of a hierarchical dimension is to provide end-users with a user-friendly structure of naturally related attributes.

At the top level of a hierarchy you will see data summarized by its most general category, like countries, and the further you move down through the levels of the hierarchy the more detailed data you will see, like cities, zip codes and eventually customer names.

Create a hierarchy by dragging attributes to the central Hierarchies panel of the dimension design window. Place them in the order you would like the levels of the hierarchy to appear.

The yellow warning regarding missing attribute relationships, we will ignore for now. Attribute relationships, or Natural Hierarchies, are addressed in the TARGIT Data Warehouse Developer Advanced course.

After having selected a number of attributes to constitute our new hierarchy we might want to hide some or all of these attributes in order to encourage end-users to use the newly created hierarchy.

The choice not to hide the CustomerName attribute, even though it is now part of the hierarchy, will in this case give the end-users the possibility to create a simple list of customers by their name, without forcing them into the structure determined by the pre-defined hierarchy.

After having processed the dimension, we can now browse the newly created hierarchy.