Errata for Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012

The specified link for the source code zip TK70463_CodeLabSolutions.zip is displayed on front page but the link does not provide access to the file, just access to the sample databases.

Note from the Author or Editor:
O'Reilly is informed about this issue and it should be resolved soon. Thank you for reporting. Dejan Sarka You should be able to download the companion content from http://examples.oreilly.com/9780735666092-files/. Thank you for your patience, Dejan Sarka

"However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar year." There is a functional dependency between month and quarter. This is NOT true for quarter and year. For example, knowing it is the 2nd quarter does not identify the year, as each and every year has a 2nd quarter.

Note from the Author or Editor:
The last statement in th e4th paragraph reads: However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar year. It should be: However, in the DimDate dimension, if you know the month, you obviously know the calendar quarter, and if you know the calendar quarter, you know the calendar semester.

The text refers to a column in the DimSalesReason dimension table named "SalesReasonType", but the column name is actually "SalesReasonReasonType" in the AdventureWorksDW2012 database (and is correctly displayed in the screenshot on p25).

Note from the Author or Editor:
The last sentence of the fourth bullet of Exercise 2 (top of the page) should be modified from "In the DimSalesReason dimension, it seems that there is a natural hierarchy: SalesReasonType ➝ SalesReasonName." to "In the DimSalesReason dimension, it seems that there is a natural hierarchy: SalesReasonReasonType ➝ SalesReasonName.".

This is a minor tweak to a great exercise, in a very helpful introductory chapter. The calculation for Age Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END does not really deal with NULL values for BirthDate. Regardless of whether there are NULL values currently in the data, the BirthDate field allows them, so the code should be prepared to deal with them. Age AS CASE WHEN BirthDate IS NULL THEN NULL WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 50 THEN 'Older' WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 40 THEN 'Middle Age' ELSE 'Younger' END

Note from the Author or Editor:
I agree with the reader. Just to make clear what needs the change: the code starts on page 49, at the bottom, bullet 2. The complete code for creating the dbo.Customers table, which spans over page 51 as well, is now: CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(1) NULL, Gender NCHAR(1) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) <= 40 THEN 'Younger' WHEN DATEDIFF(yy, BirthDate, CURRENT_TIMESTAMP) > 50 THEN 'Older' ELSE 'Middle Age' END, CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); and it should be changed to: CREATE TABLE dbo.Customers ( CustomerDwKey INT NOT NULL, CustomerKey INT NOT NULL, FullName NVARCHAR(150) NULL, EmailAddress NVARCHAR(50) NULL, BirthDate DATE NULL, MaritalStatus NCHAR(1) NULL, Gender NCHAR(1) NULL, Education NVARCHAR(40) NULL, Occupation NVARCHAR(100) NULL, City NVARCHAR(30) NULL, StateProvince NVARCHAR(50) NULL, CountryRegion NVARCHAR(50) NULL, Age AS CASE WHEN BirthDate IS NULL THEN NULL WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 50 THEN 'Older' WHEN DATEDIFF(yy,BirthDate,CURRENT_TIMESTAMP) > 40 THEN 'Middle Age' ELSE 'Younger' END CurrentFlag BIT NOT NULL DEFAULT 1, CONSTRAINT PK_Customers PRIMARY KEY (CustomerDwKey) ); GO

The text has: "In this example, you cannot use all foreign keys together as a composite primary key ... and the primary key would be duplicated. You could use ... however, in order to show how you can autonumber a column with the IDENTITY property, this exercise has you add your own integer column with this property. This will be your surrogate key. CREATE TABLE dbo.InternetSales ( InternetSalesKey INT NOT NULL IDENTITY(1,1), CustomerDwKey INT NOT NULL, ProductKey INT NOT NULL, DateKey INT NOT NULL, OrderQuantity SMALLINT NOT NULL DEFAULT 0, SalesAmount MONEY NOT NULL DEFAULT 0, UnitPrice MONEY NOT NULL DEFAULT 0, DiscountAmount FLOAT NOT NULL DEFAULT 0, CONSTRAINT PK_InternetSales PRIMARY KEY (InternetSalesKey) );" But FIGURE 2-1 does not have InternetSalesKey as a column of the InternetSale table and shows CustomerDwKey, ProductKey, and DateKey as the primary key.

Note from the Author or Editor:
I already sent the corrected figure to the publisher.

The first sentence starts out with "The query returns 6343 rows..." Actually it returns 6434. This can be confirmed by running the following: Select count(*) from dbo.FactInternetSales where CustomerKey <= 12000; The result of that query is 6434. Also not that it really matters but my logical read counts are significanty different than yours: FactInternetSales logical reads - 4534 DimCustomer logical reads - 196 Worktable logical reads - 21314 What could cause such big differences?

Note from the Author or Editor:
The beginning of the first sentence of the last paragraph before the note should be modified from "The query returns 6,343 rows and performs..." to "The query returns 6,434 rows and performs...". About different numbers for logical IO: you cannot guarantee that you would always get the same execution plan. This depends on your HW, database settings, indexes and more. There is actually a note at the bottom of the page: "Note Number of Logical Reads You might get slightly different numbers for logical reads; nevertheless, you should get many more logical reads from the first query than from the second query." The important thing here is that you definitely should get less logical reads from the second query, which uses the Window functions.

The @updateusage parameter of the system stored procedure sp_spacedused is a varchar datatype not nvarchar so all references to @updateusage = N'TRUE' should be changed to @updateusage='TRUE'

Note from the Author or Editor:
I checked, and this is correct. The sys.sp_spaceused system procedure's second parameter, the @updateusage parameter, is VARCHAR(5) data type. However, it seems to me that this is an inconsistency from MS. Nevertheless, the error is really minor, so I guess it makes no sense to change it all over the chapter.

The name of the parent package variable must match the name of the corresponding variable in the parent package. should be The name of the parent package variable must match the name of the corresponding variable in the child package.

Note from the Author or Editor:
The last sentence in paragraph 6 (under Package Configurations) should read: "The name of the parent package variable must match the name of the corresponding variable in the child package." ML

Step4 needs an extra first step: Set "Evaluation operation" to "Expression". Click the ellipsis (...)

Note from the Author or Editor:
The text for Step 4 needs to be replaced with the following paragraph: Change the Evaluation operation constraint option from “Constraint” to “Expression and Constraint”, leave “Success” as the evaluation value, then click the ellipsis (…) at the right of the Expression text box to open the Expression Builder dialog box, and build the expression shown in Listing 6-5.

Please retract my earlier report of this error. The error is actually in the Lesson Review section, not the Answers section. 'The Lookup task cache mode property' should read 'The Lookup transformation SqlCommand property'

Note from the Author or Editor:
Agree. The question 3, answer D should be changed from: "The Lookup task cache mode property" to "The Lookup transformation SqlCommand property". GJ

"After the package has been created from a user-defined template, the value of its ID property will be the same as the one in the template." While this was true in previous versions of SSIS, this is no longer true in the current version. New Package ID's are generated automatically now. Also, in Exercise 2 (page 410), steps 6 and 7 are no longer required for this same reason.

Note from the Author or Editor:
We need to check this behavior with the vendor. ML

There isn't an SSISDB stored proc called "catalog.configure_server". Perhaps you mean "catalog.configure_catalog"

Note from the Author or Editor:
True. Any and all references to "catalog.configure_server" should be replaced with "catalog.configure_catalog" (that is, once on page 481 and once in the Index, on page 772). ML

Quick Check Answer reads "On the SSISDB, project, and package levels" It should read "On the SSISDB, Folder, and Package levels" Predefined reports are not available on the Project level.

Note from the Author or Editor:
Unable to check at the moment, but I suspect the reader is correct. Will respond with a more definite answer ASAP. ML UPDATE: The reader is only partially correct. The text of the Quick Check Answer should be changed to: "On the SSISDB catalog, folder, project, and package levels." ML

In addition to re-running the script from chapter 5 code folder you also need to open the TK463Chapter13.sql file and run the first section to create the stg.SalesTerritory table. This step is missing from the instructions.

Note from the Author or Editor:
Exercise 1, Step 1 on page 508 should have an additional statement after the existing text "If you are missing the database objects from Chapter 5, “Designing and Implementing Data Flow,” execute the needed SQL code from that chapter to have all the stage and dimension tables available in the TK463DW database. " Should have an additional sentence at the end: Start SSMS and connect to your SQL Server instance. Open a new query window by clicking the New Query button. Select the TK463DW database in the database drop-down list. Create the stg.SalesTerritory staging table by executing the following SQL statement. CREATE TABLE stg.SalesTerritory ( TerritoryID INT NULL, Name NVARCHAR(50) NULL, CountryRegionCode NVARCHAR(10) NULL, [Group] NVARCHAR(50) NULL, ModifiedDate DATETIME NULL );

Excel 2012 should be Excel 2010.

Note from the Author or Editor:
The Quick Check answer should be corrected from "Quick Check Answer &#9632;&#9632; Use Excel 2012 with MDS Add-in for Excel to update batches of data in an MDS model." to "Quick Check Answer &#9632;&#9632; Use Excel 2010 with MDS Add-in for Excel to update batches of data in an MDS model."

gacutil fails with "Failure adding assembly to the cache: Attempt to install an assembly without a strong name." There should be a reference to instructions for signing the assembly.

Note from the Author or Editor:
Additional steps must be added to Exercise 2 of Lesson 3, Chapter 19, instructing the student how to digitally sign the assembly.