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:
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.