Using Tabular Models - SQL Server Analysis Services 2012 Cube Development - page 321

Creating a hierarchy
As with multidimensional models, hierarchies can be helpful while defining the rela-
tionship between the fields in a table. The Tabular Model allows you to add hierarch-
ies that can be used by client tools.
How to do it…
You will need to open the diagram view ( MODEL | Model View | Diagram View ) in
order to work with hierarchies. In this recipe, we will be using the Date table and cre-
ating a calendar and fiscal hierarchy, which are used pretty commonly, by performing
the following steps:
1. In the diagram view, you need to click on the Create Hierarchy button on the
Date table. This will add a hierarchy section to the bottom of the table. You
will assign a name to the hierarchy here and then drag fields to add to the
hierarchy.
2. Our hierarchy will be fairly simple. Drag the columns down to the hierarchy.
You need to drag the first column directly on to the hierarchy name.
3. Each column thereafter, you will drag and place in the preferred order of the
hierarchy. I would recommend that you drag the columns by always adding
new columns to the bottom of the field list; for example, CalendarYear ,
CalendarQuarter , EnglishMonthName , FullDateAlternateKey .
4. Now that you have the fields, you can rename them in the hierarchy in the
Properties page or from the context menu. This can be helpful if you plan to
re-use columns in other hierarchies. In our example, we will be reusing Eng-
lishMonthName and FullDateAlternateKey , and we will rename them
to Calendar Month and Calendar Day , respectively.
5. Now, let's add the Fiscal hierarchy. To create this hierarchy, right-click on
the FiscalYear column, and select Create Hierarchy . This will add Fis-
calYear as your first level in a new hierarchy, which you can name Fiscal .
Next, you drag the fields you want in this hierarchy. Once you have completed
these activities, your Date table should look like the following screenshot: