In another installment of the SAP HANA Academy’s series on modeling enhancements in SPS 08, Tahir Hussain “Bob” Babar examines the new Order By feature in Parent-Child hierarchies within an attribute views. Bob gives an overview on how to build a attribute view with a hierarchy and demonstrates how to build an analytic view that utilizes the hierarchy. Also, Bob detail how the hierarchy's results can be displayed within an MDX provider.
Building a Parent-Child Hierarchy When Modeling (0:25 – 3:38)
First, open SAP HANA Studio, enter the content folder and create a new package. After creating an attribute view for the package, add data to the data foundation and then select the objects you wish to output.
Nex,t click on the semantics icon and ensure that you have the proper label columns chosen for your objects in the column window before clicking on the green plus sign in the hierarchies window to create a new hierarchy. After naming the hierarchy, choose Parent-Child hierarchy as the hierarchy type. Next, choose the object for the child and the object for the parent. In Bob’s example the employee id is the child and the manger id is the parent.
A new feature of SPS 08 is the Order By option in the advance tab. In SPS 07 and prior revisions the parent-child hierarchies were ordered according to the leaf node in the child column which in turn dictated the natural ordering of their ancestors. Initially in this example, Bob doesn’t order his hierarchy by leaving the Order By option blank before proceeding to save and activate his hierarchy.
Building an Analytic View to Utilize the Hierarchy (3:38 - 5:54)
Now build an analytic view in the package and add a new data table to another data foundation that is linked the prior one. Bob uses a facts table he has create and then selects the objects (employee ID and net sales) to be added to the output. Next, use a logical join to link the attribute view that contains the hierarchy to the data foundation with a line linking the same objects (employee ID in this case). After selecting the cardinality (many to one (n..1)) define the appropriate object (net sales) as a measure before saving and activating.
Viewing the Hierarchy in MDX Client (5:54 – 8:12)
In this example Bob opens Microsoft Excel and under the data tab he connects to the SAP HANA MDX Provider and logs into his machine. Now he selects the recently built analytic view, which outputs his data into the pivot table field listed on the right hand side. By choosing to display both net sales and employee hierarchy Bob sees the total net sales for everyone who reports to the top manager.
Now Bob can drill down through the manager's organization to see the individual net sales figures for each employee. However, it is noticeable that there is no distinguished order for the employees who don’t manage anyone.
How to use Order By in Hierarchies (8:12 – 10:05)
Back in the attribute view in SAP HANA Studio select to edit the hierarchy and in the Order By option in the advanced tab use the drop down menu for order by column to select an object (employee ID). Then chooses to sort the object (in this case ascending) before saving and activating the new hierarchy.
Now after refreshing the data back in Excel the sort order will change. In Bob's example the previously disorganized names are now in alphabetical order.
Check out Bob’s video on using the new Order By feature of a Parent-Child hierarchy in SAP HANA SPS08.
View more tutorial videos about SAP HANA SPS08 at the SAP HANA Academy.
SAP HANA Academy - over 500 free tutorial technical videos on using SAP HANA.
-Tom Flanagan
SAP HANA Academy
- SAP Managed Tags:
