Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 115 of 212

103 C H A P T E R 6 | More analytics Creating small data sets to satisfy simple requirements without adding a lot of overhead to your technical infrastructure. Prototyping a solution before building a complete solution. Creating a simple date table by using the new CALENDAR() or CALENDARAUTO() functions. Separating a role-playing dimension into multiple tables for simpler modeling. When you create a calculated table, you can choose to use only a few columns from a source table, combine columns from multiple tables, or apply complex expressions to filter and transform existing data into a new table. As a simple example, the FactInternetSales table in the AdventureWorksDW sample database contains the following three columns: OrderDateKey, ShipDateKey, and OrderDateKey. These three columns have a foreign-key relationship to a single role-playing dimension, DimDate. Instead of activating a relationship to change the context of a query from Order Date to Ship Date or Due Date, as required in earlier versions, you can now create one calculated table for ShipDate and another for DueDate. To create a calculated table, you must set your model to compatibility level 1200. Select New Calculated Table from the Table menu or click the Create A New Table Calculated From A DAX Formula tab at the bottom of the model designer, as shown in Figure 6-6. The designer displays a new empty table in the model. In the formula bar above the table, enter a DAX expression or query that returns a table. Figure 6-6: Adding a new calculated table. To continue with our example, you can use a simple expression such as =Date to copy an existing role-playing dimension table, Date (renamed from DimDate). The model evaluates the expression and displays the results. You can then rename the calculated table, add relationships, add calculated columns, and perform any other activity that you normally would with a regular table. The tab at the bottom of the model designer includes an icon that identifies the table as a calculated table, as shown in Figure 6-7.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016