Power BI Fun with a Baby Names Database

The United States’ Social Security Administration publishes counts of the names of newborns.  Kendra Little has loaded these counts for the years 1880 to 2014 (which are provided in separate files per year) into a SQL Server database that she has made available here for download.  I had some Power BI fun with it exploring the most common names throughout the years.

Word cloud for the top 10 female names between 1900 and 1949:

Female Names

Sample of animation for the top 5 male names from 1880 to 2014:
Baby Names

Download the Power BI Desktop file here (6MB).  The Power BI version is 2.30.4246.181 64-bit (December 2015).  It uses version 1.0.2 the WordCloud custom visual available in the Power BI Visuals Gallery.  For a live version of the report, scroll to the end of this article.  It was embedded using the new “publish to web” feature in Power BI.

The visuals use a ranking number assigned per year, per gender.  My first version calculated the rank in DAX using RANKX but the performance was poor, especially for the animated scatter chart; it took a long time to load and refresh.  I solved the problem by calculating the rank in T-SQL like this:

SELECT a.[FirstName] as [First Name]
 , [ReportYear] as [Report Year]
 , [Gender]
 , [NameCount] as [Name Count]
 , DENSE_RANK() OVER (PARTITION BY [ReportYear],[Gender] ORDER BY [NameCount] DESC) as Ranking
 FROM [ref].[FirstName] a inner join [agg].[FirstNameByYear] b
 on a.FirstNameId = b.FirstNameId;

Table agg.FirstNamesByYear has about 1.8 million records.

0 comments:

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>