You have to start the SQL BI Studio and open the old solution SlnAdventureWorks.

1.    Open the Data Source View called Adventure Works.

2.    Figure 1 shows almost a complete view of the DSV.

3.    (learn to add tables) Right click in the yellow area and choose Add/Remove Tables.

Figure 1 - A complete view of DSV Adventure Works

4.    The add remove table shows a list of tables to select from.

Figure 2 - The new table collection

5.    Figure 2 shows the newly added table started by the customer. Click OK.

6.    (How to zoom) Use the zoom tool icon situated under the Start Page word in Figure 1.

7.    (How to navigate) Use the 4 arrow navigation icon in the bottom right as shown in Figure 1.

8.    (How To Subset) You can always subset your data model by right clicking in the Diagram Organizer (Figure 1) and choosing New Diagram.

9.    Rename the new diagram to Employee.

10. From The Tables panes, drag and drop all tables that start with employee as a word. Voila.

11. Click All Tables again to get a complete view of your data model.

12. (Creating or Deleting New Relation Ships) To delete a relationship go to the Employee Diagram View. Right click the relationship between the employee pay history and employee table and choose delete. You can see that the relationship in the All Tables diagram has been deleted since the employee diagram is a subset. You can even proceed with deleting all the relationships in the graph.

13. To create a relationship you click and drag a field in table to another. This is simple. Another interesting way is to use the properties window. Left click the yellow area and go to the properties window. You can see the option Name Matching Criteria (Click the ellipses next to option). Figure 3 shows the options.

14. If you choose the same name as the primary key, it is enough that the source foreign key column has the same name as the primary table key column (and same type) to create the connection automatically (therefore, a standard naming convention is helpful here) i.e. Order.CustomerId = Customer.CustomerId.

15. If you choose the same name as the destination table, the source foreign key column name should be the same name as the primary table name and the join would be on the primary key of the primary table, i.e. Order.Customer = Customer.CustomerId.

16. The last option is destination table name + primary key name, the source foreign key column name should be a combination between the primary key table name as well as the primary key column name, i.e. Order.CustomerID = Customer.ID.

Figure 3 - Picture that shows the option in creating relationships

17.  (Create A New Calculation) In the UDM you are allowed to add some functionality, such as creating a new calculated field. In the creation of this calculated field you can use any T-SQL expression. Right click the SalesPerson field and choose New Named Calculation. In the column name type Sales Ratio. Feel free to enter the description. In the expression enter the following statement written in bold, SalesLastYear/(Case When SalesYTD=0 Then 1 Else SalesYTD End). In the last statement we are dividing the Sales of Last Year by the sales year to date to get whether the sales person has outperformed himself to the current date of the current year. However, the sales person may not have performed any sales transaction; therefore, SalesYTD may be zero. This is why I am using the Case statement of T-SQL. Please note that you cannot create a Named calculation in a named query (you create the calculation directly in the named query itself).

18. (Create a New Named Query) In the UDM you can also have the functionality of creating a new query (likewise of a view in the database engine). To create a new one, right click in the yellow are and choose New Named Query.

19. In the Name part type SalesPersonVSSalesTerritory. In the description feel free to enter any text you like. In the select box use the T-SQL listing 1.

20. You can see that I have joined two tables and created a kind of comparison between the sales person and the territory.

21. Please note that in the named query you can use tables that are found in the database and not in your diagram.

Listing 1 - T-SQL to create a new named query

SELECT Sales.SalesPerson.SalesPersonID, Sales.SalesPerson.TerritoryID, 
Sales.SalesTerritory.Name, Sales.SalesPerson.SalesYTD,                          
Sales.SalesTerritory.SalesYTD AS TerritorySalesYTD
FROM Sales.SalesPerson INNER JOIN
Sales.SalesTerritory ON Sales.SalesPerson.TerritoryID = 
Sales.SalesTerritory.TerritoryID