SQL11UPD06-TSCRIPT-04

This wiki is a transcript of a previously recorded video.

Related content assets:

• Demo: Exploring SQL Server 2012 Data Analysis Expressions Improvements (SQL11UPD06-DEMO-02)
• Video: Exploring SQL Server 2012 Data Analysis Expressions Improvements (SQL11UPD06-REC-04)

Exploring SQL Server 2012 Data Analysis Expressions Improvements

Welcome to this demonstration, Developing a SQL Server 2012 Tabular Project with SQL Server Data Tools. I’m Peter Myers. I’m a SQL Server MVP and a Mentor with SolidQ.

This demonstration continues from the previous demonstration whereby I created a tabular project. Note that while there’s no requirement that you saw the earlier presentation, if you do plan to watch all the presentations in this series, we strongly recommend you watch the tabular project demonstration first. Then you’ll see how we can use the improvements in DAX to extend and enrich the tabular model.

So I switch across to the model that was developed in the previous demonstration. What we see in the design of this, first of all, is a Sales table and a Date table. You may note that there are two relationships between these tables. One is expressed in a solid line, the other is a dashed line. The solid represents the active relationship and there must be one active relationship between two tables. If you do, in your design, include multiple relationships between tables, then all other relationships will be inactive. Something to be aware of is an inactive relationships are not used by default. They can only be used explicitly within calculations and that’s the first part of this demonstration.

The requirement I have in this model is to introduce the year-to-date (YTD) calculation, one that allows us to produce year-to-date by Order Date and one that is by Shipped Date. Note that the latter for Shipped Date is using the inactive relationship. So I navigate across to the Sales table in grid view. I’ve already built and designed and created four measures in the previous demonstration. Here in the calculations area I introduce a new calculation and that will be that the sales year-to-date for ordered will use the TOTALYTD() function. I pass in the Sales measure and I reference where the Date column is in the model, and because I’m using fiscal periods that end on the 30th of June every year, the optional parameter here is to specify 0630. In other words, the financial year finishes on the sixth month on the 30th day. I press Enter and that introduces a new explicit measure into the model. In the Properties Window I go ahead and update the format property to Currency. My intention is to add a second and similar calculation but to allow the year-to-date to accumulate over the Shipped Date values. I have a snippet to support me with this. The snippet is sales year-to-date for Shipped equals the TOTALYTD() function, and this time it uses the calculation function. So calculate the Sales measure, and then we have this new DAX function called USERELATIONSHIP() – it takes two columns that are involved in the relationship – so from the Sales ShipDatekey across to the Date table DateKey – and then the rest of the function is much the same. I copy this entire measure expression and paste it into the formula bar and then I update its format properties to Currency also.

To test this I go ahead and, on the Model menu, I analyze in Excel. Essentially what this does is open up an Office Data Connection (ODC) file that points to the workspace database. What that does is tell Excel to launch, create a PivotTable that connects to that Office Data Connection file and we see that here. Now I can introduce both of those new measures. Then here on the Date dimension I drag the Fiscal hierarchy down to the row levels. I can then go ahead and expand the levels of the hierarchy, and when I right-click this quarter member, I can go ahead and hide that level of the hierarchy. Now it’s clear for us to see all of the months of the Fiscal Year – we get to see the Ordered Date, Sales accumulation – and note, the values are different here because it’s using the inactive relationship to show us what the Shipped Date values look like. So this is how we can work with inactive relationships. They must be explicitly used using the USERELATIONSHIP() function in calculations like the measure I just added.

The second part of this demonstration is to take a closer look at the Salesperson table. When I created this table in the previous demonstration, I included the EmployeeKey which is involved in the relationship to the Sales table, and also the ParentEmployeeKey. If you can appreciate what a self-referencing relationship does, it allows us to produce what’s known as a parent-child hierarchy. In other words, each salesperson has a relationship to another salesperson. Let me point out first of all that this table consists of some 17 rows. They’re all the salespeople that we have at the Adventure Works company. I introduce a new calculated column which will be based on a new function available to DAX in SQL Server 2012. This is the PATH() function. The PATH() function wants to know what the key is and what the parent key column is.

In this demonstration I expect this to actually create an error, and let me explain why. The purpose of the PATH() function is to produce a delimited string, a pipe-delimited string, of all the keys to the current key of the row. Let’s take a look at this. The problem I have is that we have someone like Stephen Jiang here who has a ParentEmployeeKey value of 277 and yet we have no employee that uses that key. In other words, the boss of Stephen Jiang is not part of this table. So what I need to do is come to the table properties, and I go ahead and I modify the query. I’m going to replace this with another snippet. Take a look at this snippet – it says, if it’s a salesperson or if the EmployeeKey equals 277 – so I’ve updated the WHERE clause. Also, I have a calculation here that says, if the EmployeeKey is 277, then return 277. It just so happens that that employee relates to another employee, so I’m ensuring for the purpose of a parent-child hierarchy, this employee will be the root of the hierarchy. If it’s not 277, then just return the ParentEmployeeKey and then alias this column as the ParentEmployeeKey. So I take this and I paste this in, and note the original 17 rows now become 18. Or it should become 18. Let me check what’s going on here. Table properties – it didn’t paste for some reason, or didn’t copy for some reason. OK. That’s much better. Now we see the result of 18 rows and we see that our PATH() function actually works now. That is a requirement – the path function is only going to work if that parent key maps to a key that is found within the table. I now go ahead and rename this column to become Path, and I don’t want end users actually knowing this exists. This is an intermediate calculation that we’ll be using, as you’ll see, so I hide this column from the client tools.

Now I introduce another calculated column and this time, using the PATHITEM() function, another new DAX function that allows me to extract, from a path, at a position. In this case I’m requesting the first position from the path in that row. What we see in this column then is that we get the value of 277 returned. If you take a look at the values in the path column, you’ll see that all rows have key 277 at the top. That’s helpful. It’s telling us that each row, the very first position within the path string, the key value of 277 isn’t so helpful. What I would like to see is the name of the salesperson and that’s where I can introduce another new addition to the DAX function library available in SQL Server 2012. The LOOKUPVALUE() function allows me to look up a value based on a condition. What am I looking up? The corresponding Salesperson value. What is the condition? Where the EmployeeKey matches the result of the path item function. Now when I press Enter, we see that the name Brian Welker appears for all rows. Now I can rename this column and I understand that it represents the director.

For the next calculated column I’m simply going to copy this calculation and paste it in and modify the position used in the PATHITEM() function to 2. Now what we’re getting is the next level down in that path – we can see Stephen Jiang – all have a name except Brian Welker himself. Brian himself, being the actual director, doesn’t have a manager. Let me rename this column to become Manager. I don’t need to do the third level because I already have the Salesperson column here. But in a true parent-child hierarchy you may not actually know how many levels there are. It’s just convenience in this case that I know there are three levels to the organizational structure.

The last part of the demonstration is to create a hierarchy to allow the navigation from director to manager to salesperson. Here in the Salesperson table I already have a Sales Organization hierarchy and I simply drag the Director in to become the first level, and then the Manager to become the next level. By the way, this hierarchy was already there – I created it just prior to the demonstration. Then I go ahead and hide both of these columns and let’s take a look at what this looks like when we analyze in Excel. From the toolbar I can launch the Analyze from Excel. I’ll introduce the Sales measure and then I’ll drag the Sales Organization hierarchy down to the rows. Now I can go ahead and expand down the levels to see who the director is, who the manager is, and who the individual salespeople are.

So while parent-child hierarchies are not natively supported in tabular model development, we can simulate them using the new library or the new family of functions provided in DAX. So path provides a delimited string as well as the keys to the current row. The PATHITEM() function allows us to extract a key at a particular position. Then with the assistance of the LOOKUPVALUE() function, we can look up something more descriptive like the salesperson name to provide a meaningful label for that key.

So there are the two part of DAX I wished to demonstrate in this demonstration that include working with active relationships, and simulating a parent-child hierarchy. Thank you very much for watching this demonstration.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)