- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 280字
- 2021-08-13 17:55:28
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:
- Go to Diagram View, maximize the Date table (with a maximize icon in the top right-hand side of the Date table).
- Create a hierarchy (the second icon from the top right-hand side of the
datetable shown in the next screenshot), name the hierarchy asCalendar, and drag-and-drop CalendarYear, CalendarQuarter, EnglishMonthName, and FullDateAlternateKey as levels of this hierarchy.
- Save the changes and view the tabular model in Excel. Select Calendar under Date. Also, select Sum of SaleSamount from measures. You will see that the calendar hierarchy shows the hierarchy of years, quarters, months, and dates.
- We observe that the order of month in the calendar is not correct; it is already ordered based on the month name, but it should be ordered based on the month number as well.
- Go to Grid View of the Date table, select the EnglishMonthName column, and then from the tabular toolbox icons on the top, click on the Sort By Column icon. Choose MonthNumberOfYear as the Sort By Column.
- Save the changes and refresh PivotTable; now you will see that the order of the month shows correctly as January, February, and so on.
What just happened?
Creating hierarchies from a single table is as simple as dragging-and-dropping columns (step 1 to 4). Also, in this example, you saw how you can change the sort order of a column based on other columns (step 5).