Time for action – creating a hierarchy from a single table

In this example, we want to create a calendar hierarchy in the Date table. So, this example shows how to create a hierarchy from a single table. A calendar hierarchy will have the year, quarter, month, and date level:

  1. Go to Diagram View, maximize the Date table (with a maximize icon in the top right-hand side of the Date table).
  2. Create a hierarchy (the second icon from the top right-hand side of the date table shown in the next screenshot), name the hierarchy as Calendar, and drag-and-drop CalendarYear, CalendarQuarter, EnglishMonthName, and FullDateAlternateKey as levels of this hierarchy.
  3. Save the changes and view the tabular model in Excel. Select Calendar under Date. Also, select Sum ...

Get Microsoft SQL Server 2014 Business Intelligence Development: Beginner’s Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial