Why So Many Latin Surnames at AdventureWorks Cycles?

Question

Supposedly the fictional company sells all across USA on the internet and based in the Seattle area, yet the top surnames are Latin.

SELECT LastName, 
       NameCount = count(* ) 
FROM     Person.Person 
GROUP BY LastName 
ORDER BY NameCount DESC 
/*
LastName	NameCount
Diaz	211
Hernandez	188
Sanchez	175
Martinez	173
Torres	172
Martin	171
Perez	170
Gonzalez	169
Lopez	168
Rodriguez	166
*/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 1

Hi Kalman,

It's purely coincidental.  We made no attempt to match the list of names in Person.Person to any of the fictional Adventure Works Cycles business scenarios.   For the vast majority of the rows in that table, we simply created a cartesian product of ~100 common last names and ~100 common first names. 

Gail

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Hi Gail,

I see the Latin surnames on the top 100 list at this link:

http://genealogy.about.com/library/weekly/aa_common_surnames.htm

What is puzzling though that the top 10 American do not show up as top 10 in Person.Person.

1	Smith	2,772,200
2	Johnson	2,232,100
3	Williams	1,926,200
4	Jones	1,711,200
5	Brown	1,711,200
6	Davis	1,322,700
7	Miller	1,168,400
8	Wilson	934,200
9	Moore	859,800
10	Taylor	857,000

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 3

No, but those names are certainly in the table.   It's been over 10 years since we orginally derived the names.  I remember checking a few web sites for common names, but I don't remember which ones. The best I can tell you is that we tried to get a random selection of common names (not just common American names) to meet our requirements of approximately 20,000 rows.  There was no attempt  to force any specific data patterns in that table, unlike some of the product sales data, which we did intentionally manipulate in order to highlight come data mining scenarios.

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Hi Gail,

Perhaps for the next SS version your team can consider more representative names?

Here is a query you can use to research the names:

USE AdventureWorks2012;

SELECT DISTINCT person.person.lastname, 
                person.countryregion.name AS country 
FROM   person.address 
       INNER JOIN person.stateprovince 
               ON person.address.stateprovinceid = 
                  person.stateprovince.stateprovinceid 
       INNER JOIN person.countryregion 
               ON person.stateprovince.countryregioncode = 
                  person.countryregion.countryregioncode 
       INNER JOIN person.businessentityaddress 
               ON person.address.addressid = 
                  person.businessentityaddress.addressid 
       INNER JOIN person.person 
               ON person.person.businessentityid = 
                  person.businessentityaddress.businessentityid 
WHERE  person.countryregion.name IN ( 'Germany', 'France' ) 
ORDER  BY country, 
          lastname  

You can also research the FirstName by country just changing the lastname to firstname in the query.

Germany & France can be improved as well with more German and French surnames respectively.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 5

Okay, I can see the value in doing that. I'll pass your suggestion along to the samples team.

Gail

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights