Unpivot Source Table Before Loading as Fact Table

My question touches on defining the grain for a fact table given a source table that uses a common spreadsheet layout. First, credit to Clinton Daniel’s excellent videos on data mart design that inspired the examples here.

In Clinton Daniel’s videos, he uses a data set from the FBI showing crime data. The first few columns are for City, State and Year. Then the rest of the columns are labeled with certain crime categories (Murder, Assault, Robbery, etc.) and each row indicates the number of crimes of that type reported by the city in the given year.

 +-----------+----------+------+--------+---------+---------+   |   CITY    |  STATE   | YEAR | MURDER | ASSAULT | ROBBERY |   +-----------+----------+------+--------+---------+---------+   | Albany    | New York | 2015 |      5 |      19 |     300 |   | Albany    | New York | 2016 |      6 |      30 |     275 |   | Annapolis | Maryland | 2015 |     12 |      66 |     197 |   | Annapolis | Maryland | 2016 |     16 |      47 |     189 |   +-----------+----------+------+--------+---------+---------+   

What are the arguments for or against “unpivoting” this data before loading it to the data warehouse? Would your fact table keep each crime category in its own column like it is in the source? Or would you see that the Crime Category is more granular, meaning that you’d transform and load the data as below:

 +-----------+----------+------+----------------+--------+ |   CITY    |  STATE   | YEAR | CRIME_CATEGORY | NUMBER | +-----------+----------+------+----------------+--------+ | Albany    | New York | 2015 | Murder         |      5 | | Albany    | New York | 2015 | Assault        |     19 | | Albany    | New York | 2015 | Robbery        |    300 | | Albany    | New York | 2016 | Murder         |      6 | | Albany    | New York | 2016 | Assault        |     30 | | Albany    | New York | 2016 | Robbery        |    275 | | Annapolis | Maryland | 2015 | Murder         |     12 | | ...       | ...      |  ... | ...            |    ... | +-----------+----------+------+----------------+--------+ 

Are there guidelines on how to approach this? Or any resources (books, blogs) that address this?

A couple of other quick thoughts — I understand that the preferable grain in this case would be an individual crime itself, not already aggregated as this source data is. Would you handle it differently if the source table is already aggregated versus not aggregated?

Lastly, as information, my specific application has more to do with invoices. The columns in my source tables are Cost Categories (Parts, Labor, Fuel) and I contend my organization should be capturing each of those as individual rows of the fact table (i.e., unpivoted).

Thanks.