Adventure Works DW 2008 views for Data mining are empty

Question

Hi all

I was preparing my BI certification with the Training Kit Business Intelligence Development and Maintenance book (Exam 70-448) by Erik Veeman.

So far so good, until I reached Chapter nine. All the previous exercises worked fine but in Chapter 9 - Page 384 Item number 4

when trying to select any of the views under the AdventureWorksDW2008 I get no data returned on any of them. The views (vTargetMail, dbo.vTimeSeries, vAssocSeqOrders, and vAssocSeqLineItems)are properly created in the database but no data is shown.

Does anyone else see this? any solution to continue studying?

 

Thanks in advance

Answer 1

Have you made any changes to the database?

firstly check:

SELECT count(*) FROM FactInternetSales

as all the views reference vDMPrep which is driven by FactInternetSales.

Possibly the simplest solution would be to restore the AdventureWorksDW database.

 

Answer 2

Hi Karl

Thanks for the quick response

The changes are requested by the previous exercises (from chapter 1 to 9) but all went fine. Having said that, chapter 9 is a whole new topic (Datamining) using new views and Datasources barely related to the previous database.

Yes, that SQL was the first thing I've tried. Surprisingly throw me an amount of 60399 rows. However, dbo.vDMPrep is empty.

any suggestion?

Answer 3

Hi Karl

 

You were right. A fresh install solved the issue. now the views are full.

 

thanks

Answer 4

I thought I would just update this as I have just come across the same problem.

I stepped through each of the joins on vDMPrep and it works until you join the DimGeography table with DimCustomer.

It looks like DimCustomer is missing the GeographyKey which was overwritten in Chapter 3 Exercise 2. 

To fix it I went back and amended the ETL package as follows;

1. Exercise 2 Step 3 - Add in Sales.Customer.TerritoryID to the SQL Command Text
2. Exercise 2 Step 10.b - Reselect All on Customer Source
3. Exercise 2 Step 17.b - Map TerritoryID to GeographyKey
4. Exercise 2 Step 18 - Execute the package.

I have assumed that GeographyKey is Sales.Customer.TerritoryID from AdventureWorks2008. 

After executing the package the view vDMPrep returns the data.

Hopefully that will resolve the problem as I really don't want to run a fresh install in case some of the amendments are used in the SSRS chapters.

I hope that helps someone and it can be updated for future releases.

Answer 5

I thought I would just update this as I have just come across the same problem.

I stepped through each of the joins on vDMPrep and it works until you join the DimGeography table with DimCustomer.

It looks like DimCustomer is missing the GeographyKey which was overwritten in Chapter 3 Exercise 2. 

To fix it I went back and amended the ETL package as follows;

1. Exercise 2 Step 3 - Add in Sales.Customer.TerritoryID to the SQL Command Text
2. Exercise 2 Step 10.b - Reselect All on Customer Source
3. Exercise 2 Step 17.b - Map TerritoryID to GeographyKey
4. Exercise 2 Step 18 - Execute the package.

I have assumed that GeographyKey is Sales.Customer.TerritoryID from AdventureWorks2008. 

After executing the package the view vDMPrep returns the data.

Hopefully that will resolve the problem as I really don't want to run a fresh install in case some of the amendments are used in the SSRS chapters.

I hope that helps someone and it can be updated for future releases.

That is the issue indeed.

As stated GeographyKey was NULL on table dimCustomer.

This is what I did:

- opened the ETL project containing the dimCustomer dimension;

- opened the "Data Flow Task" . (It shall open the view that contains "Customer Source" and "Customer Dim" at the top, then two sorts trasformation, a merge trasformation and a conditrional split transformation to decide if the record is new or need only to be updated);

- double click on "Customer Source" task, clicked on "Build query" button and then select the "TerritoryID" field on the "SC" table. The result query should look like:

SELECT     CONVERT(nvarchar(15), SC.AccountNumber) AS CustomerAlternateKey, C.Title, C.FirstName, C.MiddleName, C.LastName, C.Suffix, C.EmailAddress, C.AddressLine1,
                      C.AddressLine2, D.BirthDate, D.MaritalStatus, D.YearlyIncome, D.DateFirstPurchase, D.Gender, D.TotalChildren, D.NumberChildrenAtHome, D.Education,
                      D.Occupation, D.HomeOwnerFlag, D.NumberCarsOwned, SC.TerritoryID
FROM         Sales.vIndividualCustomer AS C INNER JOIN
                      Sales.Customer AS SC ON C.BusinessEntityID = SC.PersonID INNER JOIN
                      Sales.vPersonDemographics AS D ON C.BusinessEntityID = D.BusinessEntityID

- then clicked on "Columns" tab in the "OLE DB Source editor" window and at the botton of the list of columns, I renamed "TerritoryID" to "GeographyKey" in the "Ouput column" side (right one) and I close the window;

- I then modified all the subsequent task to include or map the new column "GeographyKey";

- clicked on the left "Sort" transformation, scrolled down the list of columns and selected "GeographyKey" on the "Pass through" column;

- clicked then on the "Merge Join" transformation and on the left "Sort" windows just clicked on "GeographyKey";

- I then clicked on the the "DimCustomer table" task (the one on the left for new records), clicked on "mappings" tab, and dragged the left "GeographyKey" column to the right "GeographyKey" column to match them, if you want you can also scroll the list of "Destination column" to find that "GeographyKey" on the right is initially mapped to <Ignore> on the left, just click on <ignore> and from the drop down list select "GeographyKey" to link the two columns);

- repeated the previous step for the "DimCustomer Update Table" task (the right one, forthe updatd records);

-saved the project and executed it, now the vDMPrep view is filled with data and you can continue to practice data mining exercises!

Cheers

Gabriele

P.s. Don't forget to rebuild the project, delete the DimCustomer.dtsx package on the SSIS  server and reimport the new one, this will prevent any scheduled task to rebuild the DimCustomer table with empty records... ;-)

Answer 6

You guys are geniuses for spotting that... I was about to start hacking the views!  Great work.  It's people like you that are a godsend for people that don't have strong technical skills to figure these things out on their own and are stumped when the authors leave silly mistakes in their books...

Answer 7

I'm assuming this works as well so marked as answered!

Answer 8

You can also do this to update the DimCustomer.GeographyKey.  (Presuming AdventureWorksDW..DimCustomer table is same as AdvventureWorksDw2008..DimCustomer table, I think it is, same number of records):

UPDATE
    dc1
SET
    dc1.GeographyKey = dc2.GeographyKey
FROM
    AdventureWorksDW2008.dbo.DimCustomer dc1
INNER JOIN
    AdventureWorksDW.dbo.DimCustomer dc2
ON
    dc1.CustomerKey = dc2.CustomerKey

The other approaches work as well, but this is quick and easy.  I had the blank views and fixed mine this way.

Hope helps (I don't care about getting credit for answer).

Answer 9

Actually, this is more correct after looking at SSIS package:

UPDATE

    dc1
SET
    dc1.GeographyKey = dc2.TerritoryID
FROM
    AdventureWorksDW2008.dbo.DimCustomer dc1
INNER JOIN
    AdventureWorksDW.Sales.Customer dc2
ON
    dc1.CustomerKey = dc2.CustomerID

Much easier than modding the SSIS, I think.  Either way, all proposed solution(s) fix problem.

Answer 10

Excellent resolution, thanks! For anyone who's using the 2008R2 sample databases, this works fine:

UPDATE
    dc1
SET
    dc1.GeographyKey = dc2.GeographyKey
FROM
    AdventureWorksDW2008R2.dbo.DimCustomer dc1
INNER JOIN
    AdventureWorksDW.dbo.DimCustomer dc2
ON
    dc1.CustomerKey = dc2.CustomerKey      

Good luck everyone on practicing! :-)