There are 50 states in the US, and normally when we are working with sales data, those 50 states are usually broken into territories and sales regions. I found that creating a dimension to contain both the state names and their abbreviations to be useful. Keep reading below if you want to see a partial “use case” where this dimension will play a role or skip even further below to grab the SQL code.
Remember, the use case has been created with the collaboration of stakeholders and other interested organizations. From the “use case” the technical documents will be created and delivered to the EDW team.
Use Case (partial insert)–
Physician Spend State: The business intelligence visualization will include a visualization on reported physician states of practice. This visualization will be represented by a US national map, and once a physician is selected, the states that physician practices in will be highlighted.
Granularity will be at the state level and not at the practicing city level.
After reviewing the use case, we can see that the visualization will be driven by state, however, after doing our analysis on the data, we know that the data only includes state as an abbreviation, therefore we will want to create a dimension to hold both the state name and the state abbreviations.
USE [Conformed]
GO
/****** Object: Table [dbo].[DimState] Script Date: 4/17/2019 12:28:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DimState](
[PK_StateKey] [int] IDENTITY(1,1) NOT NULL,
[StatePostalAbbreviation] [nchar](2) NOT NULL,
[StateName] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_StateID] PRIMARY KEY CLUSTERED
(
[PK_StateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT into [DimState] values
('AL','Alabama'),
('AK','Alaska'),
('AZ','Arizona'),
('AR','Arkansas'),
('CA','California'),
('CO','Colorado'),
('CT','Connecticut'),
('DE','Delaware'),
('FL','Florida'),
('GA','Georgia'),
('HI','Hawaii'),
('ID','Idaho'),
('IL','Illinois'),
('IN','Indiana'),
('IA','Iowa'),
('KS','Kansas'),
('KY','Kentucky'),
('LA','Louisiana'),
('ME','Maine'),
('MD','Maryland'),
('MA','Massachusetts'),
('MI','Michigan'),
('MN','Minnesota'),
('MS','Mississippi'),
('MO','Missouri'),
('MT','Montana'),
('NE','Nebraska'),
('NV','Nevada'),
('NH','New Hampshire'),
('NJ','New Jersey'),
('NM','New Mexico'),
('NY','New York'),
('NC','North Carolina'),
('ND','North Dakota'),
('OH','Ohio'),
('OK','Oklahoma'),
('OR','Oregon'),
('PA','Pennsylvania'),
('RI','Rhode Island[F]'),
('SC','South Carolina'),
('SD','South Dakota'),
('TN','Tennessee'),
('TX','Texas'),
('UT','Utah'),
('VT','Vermont'),
('VA','Virginia'),
('WA','Washington'),
('WV','West Virginia'),
('WI','Wisconsin'),
('WY','Wyoming');