Skip to main content
Enterprise Data WarehousesSQL

Modeling Open Payments Data into a Data Warehouse

Here at On Point BI, we find modeling data can be done by following four steps.

1

Identify the business process you want to track

This step should be done by collaborating with the organization leaders or subject matter experts (SME) and other interested organizations. We normally create an analytical road map along with a use case as a result of our meeting with the key stakeholders. You should have identified the data, where it comes from, the frequency of the data extracts and loads, and finally what metrics you are going to create.
2

Determine the granularity of the fact data

This will depend on the total volume of transaction-level data. It’s usually a good idea to start with the finest grain of data and store each transaction as line item (row).
3

Strip out the dimensions

Identify the attributes involved in each transaction and create separate dimension tables for them. Each record in the dimension table should be unique and have a numeric primary key associated with it.
4

Consolidate the facts

The remaining metrics like quantity and sales amount are your measures and belong in a fact table. Alongside each measure, you should have foreign keys that reference all dimensions involved in the process like product, customer, and store.

Working the above four steps into a real example – Follow along below

Step 1

Identify the business process you want to track

While working at a Life Science company, the compliance organization had asked the BI team to provide expenditure data related to payments and other transfers of value given to physicians and teaching hospitals. Supplying this data would keep the company in compliance with the governments Sun Shine Act. Since they had been supplying the Sun Shine Act data,  it was time to also start using the data other companies were reporting to get an idea of where they stood against in-kind companies.

For the purpose of this article, we have identified through collaboration with key stakeholders and other interested parties the following items:

  • The source of the data – It can be downloaded from https://www.cms.gov/OpenPayments
  • Three data sets will be extracted and loaded into the EDW – Research Data, General Payments Data, and Physician Data
  • The data will be extracted and loaded once on an annual basis
  • The reports from the data will allow our analysts to view spending data from the Sun Shine Act

At this point, with the knowledge we gained from those meetings, we were able to produce a data road map and an use case documentation. We then received sign off on these documents which verified that all interested parties were on the same page as to what is being delivered.

Step 2

Determine the granularity of the fact data

In step one above, we identified the metrics that are required in the business intelligence (BI) solution that will be provided to the customer. This will also include an analysis of the source data to verify that the data can support the requirements of the solution.

The source data is similar to what you would expect from an eCommerce transactional model. We kept the data at this grain for the fact tables. Two fact tables will be generated. One table will be the General Payments Facts and the other table will be the Research Payments Facts.

Step 3

Strip out the dimension

We determined that we would have two sprints to modeling this data into the EDW. The first sprint will cover the Research Data. Below we have an SQL statement (see primary tier tables) that will generate the staging table on our Bottom Tier of our three-tier architect data warehouse.

The goal here is to identify the attributes involved in each transaction and create separate dimension tables for them as needed. So let’s take a crack at it. Below we define the dimensions we will be adding to our middle tier known as our Conformed tier:

  • Covered Recipient Type
  • Teaching Hospital
  • Physician
  • Applicable Manufacturer or Applicable GPO Making Payment

Primary tier tables

USE [Primary]
GO



SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OP_DTL_RSRCH_PGYR2017_P01182019](
	[Change_Type] [nvarchar](255) NULL,
	[Covered_Recipient_Type] [nvarchar](255) NULL,
	[Noncovered_Recipient_Entity_Name] [nvarchar](255) NULL,
	[Teaching_Hospital_CCN] [int] NULL,
	[Teaching_Hospital_ID] [int] NULL,
	[Teaching_Hospital_Name] [nvarchar](255) NULL,
	[Physician_Profile_ID] [int] NULL,
	[Physician_First_Name] [nvarchar](255) NULL,
	[Physician_Middle_Name] [nvarchar](255) NULL,
	[Physician_Last_Name] [nvarchar](255) NULL,
	[Physician_Name_Suffix] [nvarchar](255) NULL,
	[Recipient_Primary_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Recipient_Primary_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Recipient_City] [nvarchar](255) NULL,
	[Recipient_State] [nvarchar](255) NULL,
	[Recipient_Zip_Code] [nvarchar](255) NULL,
	[Recipient_Country] [nvarchar](255) NULL,
	[Recipient_Province] [nvarchar](255) NULL,
	[Recipient_Postal_Code] [nvarchar](255) NULL,
	[Physician_Primary_Type] [nvarchar](255) NULL,
	[Physician_Specialty] [nvarchar](255) NULL,
	[Physician_License_State_code1] [nvarchar](255) NULL,
	[Physician_License_State_code2] [nvarchar](255) NULL,
	[Physician_License_State_code3] [nvarchar](255) NULL,
	[Physician_License_State_code4] [nvarchar](255) NULL,
	[Physician_License_State_code5] [nvarchar](255) NULL,
	[Principal_Investigator_1_Profile_ID]  [int] NULL,
	[Principal_Investigator_1_First_Name] [nvarchar](255) NULL,
	[Principal_Investigator_1_Middle_Name] [nvarchar](255) NULL,
	[Principal_Investigator_1_Last_Name] [nvarchar](255) NULL,
	[Principal_Investigator_1_Name_Suffix] [nvarchar](255) NULL,
	[Principal_Investigator_1_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Principal_Investigator_1_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Principal_Investigator_1_City] [nvarchar](255) NULL,
	[Principal_Investigator_1_State] [nvarchar](255) NULL,
	[Principal_Investigator_1_Zip_Code] [nvarchar](255) NULL,
	[Principal_Investigator_1_Country] [nvarchar](255) NULL,
	[Principal_Investigator_1_Province] [nvarchar](255) NULL,
	[Principal_Investigator_1_Postal_Code] [nvarchar](255) NULL,
	[Principal_Investigator_1_Primary_Type] [nvarchar](255) NULL,
	[Principal_Investigator_1_Specialty] [nvarchar](500) NULL,
	[Principal_Investigator_1_License_State_code1] [nvarchar](255) NULL,
	[Principal_Investigator_1_License_State_code2] [nvarchar](255) NULL,
	[Principal_Investigator_1_License_State_code3] [nvarchar](255) NULL,
	[Principal_Investigator_1_License_State_code4] [nvarchar](255) NULL,
	[Principal_Investigator_1_License_State_code5] [nvarchar](255) NULL,
	[Principal_Investigator_2_Profile_ID]  [int] NULL,
	[Principal_Investigator_2_First_Name] [nvarchar](255) NULL,
	[Principal_Investigator_2_Middle_Name] [nvarchar](255) NULL,
	[Principal_Investigator_2_Last_Name] [nvarchar](255) NULL,
	[Principal_Investigator_2_Name_Suffix] [nvarchar](255) NULL,
	[Principal_Investigator_2_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Principal_Investigator_2_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Principal_Investigator_2_City] [nvarchar](255) NULL,
	[Principal_Investigator_2_State] [nvarchar](255) NULL,
	[Principal_Investigator_2_Zip_Code] [nvarchar](255) NULL,
	[Principal_Investigator_2_Country] [nvarchar](255) NULL,
	[Principal_Investigator_2_Province] [nvarchar](255) NULL,
	[Principal_Investigator_2_Postal_Code] [nvarchar](255) NULL,
	[Principal_Investigator_2_Primary_Type] [nvarchar](255) NULL,
	[Principal_Investigator_2_Specialty] [nvarchar](500) NULL,
	[Principal_Investigator_2_License_State_code1] [nvarchar](255) NULL,
	[Principal_Investigator_2_License_State_code2] [nvarchar](255) NULL,
	[Principal_Investigator_2_License_State_code3] [nvarchar](255) NULL,
	[Principal_Investigator_2_License_State_code4] [nvarchar](255) NULL,
	[Principal_Investigator_2_License_State_code5] [nvarchar](255) NULL,
	[Principal_Investigator_3_Profile_ID]  [int] NULL,
	[Principal_Investigator_3_First_Name] [nvarchar](255) NULL,
	[Principal_Investigator_3_Middle_Name] [nvarchar](255) NULL,
	[Principal_Investigator_3_Last_Name] [nvarchar](255) NULL,
	[Principal_Investigator_3_Name_Suffix] [nvarchar](255) NULL,
	[Principal_Investigator_3_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Principal_Investigator_3_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Principal_Investigator_3_City] [nvarchar](255) NULL,
	[Principal_Investigator_3_State] [nvarchar](255) NULL,
	[Principal_Investigator_3_Zip_Code] [nvarchar](255) NULL,
	[Principal_Investigator_3_Country] [nvarchar](255) NULL,
	[Principal_Investigator_3_Province] [nvarchar](255) NULL,
	[Principal_Investigator_3_Postal_Code] [nvarchar](255) NULL,
	[Principal_Investigator_3_Primary_Type] [nvarchar](255) NULL,
	[Principal_Investigator_3_Specialty] [nvarchar](500) NULL,
	[Principal_Investigator_3_License_State_code1] [nvarchar](255) NULL,
	[Principal_Investigator_3_License_State_code2] [nvarchar](255) NULL,
	[Principal_Investigator_3_License_State_code3] [nvarchar](255) NULL,
	[Principal_Investigator_3_License_State_code4] [nvarchar](255) NULL,
	[Principal_Investigator_3_License_State_code5] [nvarchar](255) NULL,
	[Principal_Investigator_4_Profile_ID]  [int] NULL,
	[Principal_Investigator_4_First_Name] [nvarchar](255) NULL,
	[Principal_Investigator_4_Middle_Name] [nvarchar](255) NULL,
	[Principal_Investigator_4_Last_Name] [nvarchar](255) NULL,
	[Principal_Investigator_4_Name_Suffix] [nvarchar](255) NULL,
	[Principal_Investigator_4_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Principal_Investigator_4_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Principal_Investigator_4_City] [nvarchar](255) NULL,
	[Principal_Investigator_4_State] [nvarchar](255) NULL,
	[Principal_Investigator_4_Zip_Code] [nvarchar](255) NULL,
	[Principal_Investigator_4_Country] [nvarchar](255) NULL,
	[Principal_Investigator_4_Province] [nvarchar](255) NULL,
	[Principal_Investigator_4_Postal_Code] [nvarchar](255) NULL,
	[Principal_Investigator_4_Primary_Type] [nvarchar](255) NULL,
	[Principal_Investigator_4_Specialty] [nvarchar](255) NULL,
	[Principal_Investigator_4_License_State_code1] [nvarchar](255) NULL,
	[Principal_Investigator_4_License_State_code2] [nvarchar](255) NULL,
	[Principal_Investigator_4_License_State_code3] [nvarchar](255) NULL,
	[Principal_Investigator_4_License_State_code4] [nvarchar](255) NULL,
	[Principal_Investigator_4_License_State_code5] [nvarchar](255) NULL,
	[Principal_Investigator_5_Profile_ID]  [int] NULL,
	[Principal_Investigator_5_First_Name] [nvarchar](255) NULL,
	[Principal_Investigator_5_Middle_Name] [nvarchar](255) NULL,
	[Principal_Investigator_5_Last_Name] [nvarchar](255) NULL,
	[Principal_Investigator_5_Name_Suffix] [nvarchar](255) NULL,
	[Principal_Investigator_5_Business_Street_Address_Line1] [nvarchar](255) NULL,
	[Principal_Investigator_5_Business_Street_Address_Line2] [nvarchar](255) NULL,
	[Principal_Investigator_5_City] [nvarchar](255) NULL,
	[Principal_Investigator_5_State] [nvarchar](255) NULL,
	[Principal_Investigator_5_Zip_Code] [nvarchar](255) NULL,
	[Principal_Investigator_5_Country] [nvarchar](255) NULL,
	[Principal_Investigator_5_Province] [nvarchar](255) NULL,
	[Principal_Investigator_5_Postal_Code] [nvarchar](255) NULL,
	[Principal_Investigator_5_Primary_Type] [nvarchar](255) NULL,
	[Principal_Investigator_5_Specialty] [nvarchar](500) NULL,
	[Principal_Investigator_5_License_State_code1] [nvarchar](255) NULL,
	[Principal_Investigator_5_License_State_code2] [nvarchar](255) NULL,
	[Principal_Investigator_5_License_State_code3] [nvarchar](255) NULL,
	[Principal_Investigator_5_License_State_code4] [nvarchar](255) NULL,
	[Principal_Investigator_5_License_State_code5] [nvarchar](255) NULL,
	[Submitting_Applicable_Manufacturer_or_Applicable_GPO_Name] [nvarchar](255) NULL,
	[Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_ID] [BIGINT] NULL,
	[Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Name] [nvarchar](255) NULL,
	[Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_State] [nvarchar](255) NULL,
	[Applicable_Manufacturer_or_Applicable_GPO_Making_Payment_Country] [nvarchar](255) NULL,
	[Related_Product_Indicator] [nvarchar](255) NULL,
	[Covered_or_Noncovered_Indicator_1] [nvarchar](255) NULL,
	[Indicate_Drug_or_Biological_or_Device_or_Medical_Supply_1] [nvarchar](255) NULL,
	[Product_Category_or_Therapeutic_Area_1] [nvarchar](255) NULL,
	[Name_of_Drug_or_Biological_or_Device_or_Medical_Supply_1] [nvarchar](255) NULL,
	[Associated_Drug_or_Biological_NDC_1] [nvarchar](255) NULL,
	[Covered_or_Noncovered_Indicator_2] [nvarchar](255) NULL,
	[Indicate_Drug_or_Biological_or_Device_or_Medical_Supply_2] [nvarchar](255) NULL,
	[Product_Category_or_Therapeutic_Area_2] [nvarchar](255) NULL,
	[Name_of_Drug_or_Biological_or_Device_or_Medical_Supply_2] [nvarchar](255) NULL,
	[Associated_Drug_or_Biological_NDC_2] [nvarchar](255) NULL,
	[Covered_or_Noncovered_Indicator_3] [nvarchar](255) NULL,
	[Indicate_Drug_or_Biological_or_Device_or_Medical_Supply_3] [nvarchar](255) NULL,
	[Product_Category_or_Therapeutic_Area_3] [nvarchar](255) NULL,
	[Name_of_Drug_or_Biological_or_Device_or_Medical_Supply_3] [nvarchar](255) NULL,
	[Associated_Drug_or_Biological_NDC_3] [nvarchar](255) NULL,
	[Covered_or_Noncovered_Indicator_4] [nvarchar](255) NULL,
	[Indicate_Drug_or_Biological_or_Device_or_Medical_Supply_4] [nvarchar](255) NULL,
	[Product_Category_or_Therapeutic_Area_4] [nvarchar](255) NULL,
	[Name_of_Drug_or_Biological_or_Device_or_Medical_Supply_4] [nvarchar](255) NULL,
	[Associated_Drug_or_Biological_NDC_4] [nvarchar](255) NULL,
	[Covered_or_Noncovered_Indicator_5] [nvarchar](255) NULL,
	[Indicate_Drug_or_Biological_or_Device_or_Medical_Supply_5] [nvarchar](255) NULL,
	[Product_Category_or_Therapeutic_Area_5] [nvarchar](255) NULL,
	[Name_of_Drug_or_Biological_or_Device_or_Medical_Supply_5] [nvarchar](255) NULL,
	[Associated_Drug_or_Biological_NDC_5] [nvarchar](255) NULL,
	[Total_Amount_of_Payment_USDollars] [Money] NULL,
	[Date_of_Payment] [DATETIME] null,
	[Form_of_Payment_or_Transfer_of_Value] [nvarchar](255) NULL,
	[Expenditure_Category1] [nvarchar](255) NULL,
	[Expenditure_Category2] [nvarchar](255) NULL,
	[Expenditure_Category3] [nvarchar](255) NULL,
	[Expenditure_Category4] [nvarchar](255) NULL,
	[Expenditure_Category5] [nvarchar](255) NULL,
	[Expenditure_Category6] [nvarchar](255) NULL,
	[Preclinical_Research_Indicator] [nvarchar](255) NULL,
	[Delay_in_Publication_Indicator] [nvarchar](255) NULL,
	[Name_of_Study] [nvarchar](255) NULL,
	[Dispute_Status_for_Publication] [nvarchar](255) NULL,
	[Record_ID]  [int] NULL,
	[Program_Year] [nvarchar](255) NULL,
	[Payment_Publication_Date] [DATETIME] NULL,
	[ClinicalTrials_Gov_Identifier] [nvarchar](255) NULL,
	[Research_Information_Link] [nvarchar](255) NULL,
	[Context_of_Research] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Conformed tier dimension Covered Recipient Type

USE [Conformed]
GO

/****** Object:  Table [SunShineAct].[DimCoveredRecipientType]    Script Date: 4/17/2019 12:17:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimCoveredRecipientType](
	[PK_CoveredRecipientTypeKey] [int] IDENTITY(1,1) NOT NULL,
	[NaturalKey] [varchar](8000) NULL,
	[CoveredRecipientType] [nvarchar](255) NULL,
	[DeletedDate] [datetime] NULL,
	[CCB] [varchar](100) NULL,
	[CCD] [datetime] NULL,
	[CMB] [varchar](100) NULL,
	[CMD] [datetime] NULL,
	[CEFD] [datetime] NULL,
	[CETD] [datetime] NULL,
	[InsertAuditKey] [int] NULL,
	[UpdateAuditKey] [int] NULL,
	[CheckSumValue] [int] NULL,
 CONSTRAINT [PK_CoveredRecipientTypeKey] PRIMARY KEY CLUSTERED 
(
	[PK_CoveredRecipientTypeKey] 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

ALTER TABLE [SunShineAct].[DimCoveredRecipientType] ADD  CONSTRAINT [DF_SunShineAct_CoveredRecipientType_CCB]  DEFAULT (suser_sname()) FOR [CCB]
GO

ALTER TABLE [SunShineAct].[DimCoveredRecipientType] ADD  CONSTRAINT [DF_SunShineAct_CoveredRecipientType_CCD]  DEFAULT (getdate()) FOR [CCD]
GO

ALTER TABLE [SunShineAct].[DimCoveredRecipientType] ADD  CONSTRAINT [DF_SunShineAct_CoveredRecipientType_CMB]  DEFAULT (suser_sname()) FOR [CMB]
GO

ALTER TABLE [SunShineAct].[DimCoveredRecipientType] ADD  CONSTRAINT [DF_SunShineAct_CoveredRecipientType_CMD]  DEFAULT (getdate()) FOR [CMD]
GO

ALTER TABLE [SunShineAct].[DimCoveredRecipientType] ADD  CONSTRAINT [DF_SunShineAct_CoveredRecipientType_CETD]  DEFAULT (NULL) FOR [CETD]
GO

Conformed tier dimenstion Teaching Hospital

  
USE [Conformed]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] DROP CONSTRAINT [DF_SunShineAct_TeachingHospital_CETD]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] DROP CONSTRAINT [DF_SunShineAct_TeachingHospital_CMD]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] DROP CONSTRAINT [DF_SunShineAct_TeachingHospital_CMB]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] DROP CONSTRAINT [DF_SunShineAct_TeachingHospital_CCD]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] DROP CONSTRAINT [DF_SunShineAct_TeachingHospital_CCB]
GO

/****** Object:  Table [SunShineAct].[DimTeachingHospital]    Script Date: 4/18/2019 7:39:15 AM ******/
DROP TABLE [SunShineAct].[DimTeachingHospital]
GO

/****** Object:  Table [SunShineAct].[DimTeachingHospital]    Script Date: 4/18/2019 7:39:15 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimTeachingHospital](
	[PK_TeachingHospitalKey] [int] IDENTITY(1,1) NOT NULL,
	[NaturalKey] [varchar](8000) NULL,
	[TeachingHospitalCCN] [int] NULL,
	[TeachingHospitalID] [int] NULL,
	[TeachingHospitalName] [nvarchar](255) NULL,
	[DeletedDate] [datetime] NULL,
	[CCB] [varchar](100) NULL,
	[CCD] [datetime] NULL,
	[CMB] [varchar](100) NULL,
	[CMD] [datetime] NULL,
	[CEFD] [datetime] NULL,
	[CETD] [datetime] NULL,
	[InsertAuditKey] [int] NULL,
	[UpdateAuditKey] [int] NULL,
	[CheckSumValue] [int] NULL,
 CONSTRAINT [PK_TeachingHospitalKey] PRIMARY KEY CLUSTERED 
(
	[PK_TeachingHospitalKey] 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

ALTER TABLE [SunShineAct].[DimTeachingHospital] ADD  CONSTRAINT [DF_SunShineAct_TeachingHospital_CCB]  DEFAULT (suser_sname()) FOR [CCB]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] ADD  CONSTRAINT [DF_SunShineAct_TeachingHospital_CCD]  DEFAULT (getdate()) FOR [CCD]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] ADD  CONSTRAINT [DF_SunShineAct_TeachingHospital_CMB]  DEFAULT (suser_sname()) FOR [CMB]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] ADD  CONSTRAINT [DF_SunShineAct_TeachingHospital_CMD]  DEFAULT (getdate()) FOR [CMD]
GO

ALTER TABLE [SunShineAct].[DimTeachingHospital] ADD  CONSTRAINT [DF_SunShineAct_TeachingHospital_CETD]  DEFAULT (NULL) FOR [CETD]
GO

-- Create dummy record for no match lookups

INSERT INTO [Conformed].[SunShineAct].[DimTeachingHospital]
(
[NaturalKey]
      ,[TeachingHospitalCCN]
      ,[TeachingHospitalID]
      ,[TeachingHospitalName]
      ,[CEFD]
      ,[InsertAuditKey]
      ,[UpdateAuditKey]
      ,[CheckSumValue]
	  )
 VALUES ('-1',-1,-1,'',GETDATE(),0,0,0)

Conformed tier dimension Physician Profile

USE [Conformed]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimPhysicianProfile](
	[PK_PhysicianProfileKey] [int] IDENTITY(1,1) NOT NULL,
	[NaturalKey] [varchar](8000) NULL,
	[PhysicianProfileID] [int] NULL,
	[PhysicianProfileFirstName] [nvarchar](255) NULL,
	[PhysicianProfileMiddleName] [nvarchar](255) NULL,
	[PhysicianProfileLastName] [nvarchar](255) NULL,
	[PhysicianProfileSuffix] [nvarchar](255) NULL,
	[PhysicianProfileAddressLine1] [nvarchar](255) NULL,
	[PhysicianProfileAddressLine2] [nvarchar](255) NULL,
	[PhysicianProfileCity] [nvarchar](255) NULL,
	[PhysicianProfileState] [nvarchar](255) NULL,
	[PhysicianProfileZipcode] [nvarchar](255) NULL,
	[PhysicianProfileCountryName] [nvarchar](255) NULL,
	[PhysicianProfileProvinceName] [nvarchar](255) NULL,
	[PhysicianProfilePrimaryCode] [nvarchar](255) NULL,
	[PhysicianProfilePrimaryGrouping] [nvarchar](500) NULL,
	[PhysicianProfilePrimaryClassification] [nvarchar](500) NULL,
	[PhysicianProfilePrimarySpecialization] [nvarchar](500) NULL,
	[DeletedDate] [datetime] NULL,
	[CCB] [varchar](100) NULL,
	[CCD] [datetime] NULL,
	[CMB] [varchar](100) NULL,
	[CMD] [datetime] NULL,
	[CEFD] [datetime] NULL,
	[CETD] [datetime] NULL,
	[InsertAuditKey] [int] NULL,
	[UpdateAuditKey] [int] NULL,
	[CheckSumValue] [int] NULL,
 CONSTRAINT [PK_PhysicianProfileKey] PRIMARY KEY CLUSTERED 
(
	[PK_PhysicianProfileKey] 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

ALTER TABLE [SunShineAct].[DimPhysicianProfile] ADD  CONSTRAINT [DF_SunShineAct_Physician_Profile_CCB]  DEFAULT (suser_sname()) FOR [CCB]
GO

ALTER TABLE [SunShineAct].[DimPhysicianProfile] ADD  CONSTRAINT [DF_SunShineAct_Physician_Profile_CCD]  DEFAULT (getdate()) FOR [CCD]
GO

ALTER TABLE [SunShineAct].[DimPhysicianProfile] ADD  CONSTRAINT [DF_SunShineAct_Physician_Profile_CMB]  DEFAULT (suser_sname()) FOR [CMB]
GO

ALTER TABLE [SunShineAct].[DimPhysicianProfile] ADD  CONSTRAINT [DF_SunShineAct_Physician_Profile_CMD]  DEFAULT (getdate()) FOR [CMD]
GO

ALTER TABLE [SunShineAct].[DimPhysicianProfile] ADD  CONSTRAINT [DF_SunShineAct_Physician_Profile_CETD]  DEFAULT (NULL) FOR [CETD]
GO

-- Create dummy record for no match lookups
INSERT INTO [Conformed].[SunShineAct].[DimPhysicianProfile]
 ([NaturalKey]
      ,[PhysicianProfileID]
      ,[PhysicianProfileFirstName]
      ,[PhysicianProfileMiddleName]
      ,[PhysicianProfileLastName]
      ,[PhysicianProfileSuffix]
      ,[PhysicianProfileAddressLine1]
      ,[PhysicianProfileAddressLine2]
      ,[PhysicianProfileCity]
      ,[PhysicianProfileState]
      ,[PhysicianProfileZipcode]
      ,[PhysicianProfileCountryName]
      ,[PhysicianProfileProvinceName]
      ,[PhysicianProfilePrimaryCode]
      ,[PhysicianProfilePrimaryGrouping]
      ,[PhysicianProfilePrimaryClassification]
      ,[PhysicianProfilePrimarySpecialization]
      ,[CEFD]
      ,[InsertAuditKey]
      ,[UpdateAuditKey]
      ,[CheckSumValue])
VALUES
('-1',-1,'','','','','','','','','','','','','','','',GETDATE(),0,0,0)

Conformed tier dimension Applicable Manufacturer or Applicable GPO Making Payment

USE [Conformed]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] DROP CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CETD]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] DROP CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CMD]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] DROP CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CMB]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] DROP CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CCD]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] DROP CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CCB]
GO

/****** Object:  Table [SunShineAct].[DimApplicableManufacturerOrApplicableGPO]    Script Date: 4/18/2019 7:46:52 AM ******/
DROP TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO]
GO

/****** Object:  Table [SunShineAct].[DimApplicableManufacturerOrApplicableGPO]    Script Date: 4/18/2019 7:46:52 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO](
	[PK_ApplicableManufacturerOrApplicableGPOKey] [int] IDENTITY(1,1) NOT NULL,
	[NaturalKey] [varchar](8000) NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentID] [BIGINT] NULL,
	[SubmittingApplicable_ManufacturerOrApplicableGPOName] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOName] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOState] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOCountry] [nvarchar](255) NULL,
	[DeletedDate] [datetime] NULL,
	[CCB] [varchar](100) NULL,
	[CCD] [datetime] NULL,
	[CMB] [varchar](100) NULL,
	[CMD] [datetime] NULL,
	[CEFD] [datetime] NULL,
	[CETD] [datetime] NULL,
	[InsertAuditKey] [int] NULL,
	[UpdateAuditKey] [int] NULL,
	[CheckSumValue] [int] NULL,
 CONSTRAINT [PK_ApplicableManufacturerOrApplicableGPOKey] PRIMARY KEY CLUSTERED 
(
	[PK_ApplicableManufacturerOrApplicableGPOKey] 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

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ADD  CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CCB]  DEFAULT (suser_sname()) FOR [CCB]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ADD  CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CCD]  DEFAULT (getdate()) FOR [CCD]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ADD  CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CMB]  DEFAULT (suser_sname()) FOR [CMB]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ADD  CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CMD]  DEFAULT (getdate()) FOR [CMD]
GO

ALTER TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ADD  CONSTRAINT [DF_SunShineAct_DimApplicableManufacturerOrApplicableGPO_CETD]  DEFAULT (NULL) FOR [CETD]
GO

--Create dummy record for no match lookup
  INSERT INTO [Conformed].[SunShineAct].[DimApplicableManufacturerOrApplicableGPO]
(
       [NaturalKey]
      ,[ApplicableManufacturerOrApplicableGPOMakingPaymentID]
      ,[SubmittingApplicable_ManufacturerOrApplicableGPOName]
      ,[ApplicableManufacturerOrApplicableGPOName]
      ,[ApplicableManufacturerOrApplicableGPOState]
      ,[ApplicableManufacturerOrApplicableGPOCountry]
      ,[CEFD]
      ,[InsertAuditKey]
      ,[UpdateAuditKey]
      ,[CheckSumValue]
	  )
  VALUES
  ('-1',-1,'','','','',GETDATE(),0,0,0)

Step 4

Consolidate the facts

To consolidate the facts, we start by revisiting the “Use Case” document that was created in step one above, we are now going to take another look at the source data and pick out those facts that we will be using in our presentation tier for consumption by our QlikView and Power BI platforms. That being said, the focus of this step is to only build the fact table in our middle tier (conformed tier) and we will also bring in the raw data for easy access if changes are required.

We determined that along with the dimensions we created earlier, we will also include the following for facts:

  • Total Amount of Payment USDollars
  • Date of Payment
  • Form of Payment or Transfer of Value
  • Name of Study
  • Dispute Status for Publication
  • Record ID
  • Program Year
  • Payment Publication Date
  • ClinicalTrials Gov Identifier
  • Research Information Link
  • Context of Research

Conformed tier ResearchDataPGYR2017

USE [Conformed]
GO

/****** Object:  Table [SunShineAct].[ResearchDataPGYR2017]    Script Date: 4/21/2019 3:26:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[ResearchDataPGYR2017](
	[PK_ResearchDataPGYR2017Key] [int] IDENTITY(1,1) NOT NULL,
	[NaturalKey] [varchar](8000) NULL,
	[RecordID] [int] NULL,
	[ChangeType] [nvarchar](255) NULL,
	[CoveredRecipientType] [nvarchar](255) NULL,
	[NoncoveredRecipientEntityName] [nvarchar](255) NULL,
	[TeachingHospitalCCN] [int] NULL,
	[TeachingHospitalID] [int] NULL,
	[TeachingHospitalName] [nvarchar](255) NULL,
	[PhysicianProfileID] [int] NULL,
	[PhysicianFirstName] [nvarchar](255) NULL,
	[PhysicianMiddleName] [nvarchar](255) NULL,
	[PhysicianLastName] [nvarchar](255) NULL,
	[PhysicianNameSuffix] [nvarchar](255) NULL,
	[RecipientPrimaryBusinessStreetAddressLine1] [nvarchar](255) NULL,
	[RecipientPrimaryBusinessStreetAddressLine2] [nvarchar](255) NULL,
	[RecipientCity] [nvarchar](255) NULL,
	[RecipientState] [nvarchar](255) NULL,
	[RecipientZipCode] [nvarchar](255) NULL,
	[RecipientCountry] [nvarchar](255) NULL,
	[RecipientProvince] [nvarchar](255) NULL,
	[RecipientPostalCode] [nvarchar](255) NULL,
	[PhysicianPrimaryType] [nvarchar](255) NULL,
	[PhysicianSpecialty] [nvarchar](255) NULL,
	[PhysicianLicenseStatecode1] [nvarchar](255) NULL,
	[PhysicianLicenseStatecode2] [nvarchar](255) NULL,
	[PhysicianLicenseStatecode3] [nvarchar](255) NULL,
	[PhysicianLicenseStatecode4] [nvarchar](255) NULL,
	[PhysicianLicenseStatecode5] [nvarchar](255) NULL,
	[PrincipalInvestigator1ProfileID] [int] NULL,
	[PrincipalInvestigator1FirstName] [nvarchar](255) NULL,
	[PrincipalInvestigator1MiddleName] [nvarchar](255) NULL,
	[PrincipalInvestigator1LastName] [nvarchar](255) NULL,
	[PrincipalInvestigator1NameSuffix] [nvarchar](255) NULL,
	[PrincipalInvestigator1BusinessStreetAddressLine1] [nvarchar](255) NULL,
	[PrincipalInvestigator1BusinessStreetAddressLine2] [nvarchar](255) NULL,
	[PrincipalInvestigator1City] [nvarchar](255) NULL,
	[PrincipalInvestigator1State] [nvarchar](255) NULL,
	[PrincipalInvestigator1ZipCode] [nvarchar](255) NULL,
	[PrincipalInvestigator1Country] [nvarchar](255) NULL,
	[PrincipalInvestigator1Province] [nvarchar](255) NULL,
	[PrincipalInvestigator1PostalCode] [nvarchar](255) NULL,
	[PrincipalInvestigator1PrimaryType] [nvarchar](255) NULL,
	[PrincipalInvestigator1Specialty] [nvarchar](500) NULL,
	[PrincipalInvestigator1LicenseStatecode1] [nvarchar](255) NULL,
	[PrincipalInvestigator1LicenseStatecode2] [nvarchar](255) NULL,
	[PrincipalInvestigator1LicenseStatecode3] [nvarchar](255) NULL,
	[PrincipalInvestigator1LicenseStatecode4] [nvarchar](255) NULL,
	[PrincipalInvestigator1LicenseStatecode5] [nvarchar](255) NULL,
	[PrincipalInvestigator2ProfileID] [int] NULL,
	[PrincipalInvestigator2FirstName] [nvarchar](255) NULL,
	[PrincipalInvestigator2MiddleName] [nvarchar](255) NULL,
	[PrincipalInvestigator2LastName] [nvarchar](255) NULL,
	[PrincipalInvestigator2NameSuffix] [nvarchar](255) NULL,
	[PrincipalInvestigator2BusinessStreetAddressLine1] [nvarchar](255) NULL,
	[PrincipalInvestigator2BusinessStreetAddressLine2] [nvarchar](255) NULL,
	[PrincipalInvestigator2City] [nvarchar](255) NULL,
	[PrincipalInvestigator2State] [nvarchar](255) NULL,
	[PrincipalInvestigator2ZipCode] [nvarchar](255) NULL,
	[PrincipalInvestigator2Country] [nvarchar](255) NULL,
	[PrincipalInvestigator2Province] [nvarchar](255) NULL,
	[PrincipalInvestigator2PostalCode] [nvarchar](255) NULL,
	[PrincipalInvestigator2PrimaryType] [nvarchar](255) NULL,
	[PrincipalInvestigator2Specialty] [nvarchar](500) NULL,
	[PrincipalInvestigator2LicenseStatecode1] [nvarchar](255) NULL,
	[PrincipalInvestigator2LicenseStatecode2] [nvarchar](255) NULL,
	[PrincipalInvestigator2LicenseStatecode3] [nvarchar](255) NULL,
	[PrincipalInvestigator2LicenseStatecode4] [nvarchar](255) NULL,
	[PrincipalInvestigator2LicenseStatecode5] [nvarchar](255) NULL,
	[PrincipalInvestigator3ProfileID] [int] NULL,
	[PrincipalInvestigator3FirstName] [nvarchar](255) NULL,
	[PrincipalInvestigator3MiddleName] [nvarchar](255) NULL,
	[PrincipalInvestigator3LastName] [nvarchar](255) NULL,
	[PrincipalInvestigator3NameSuffix] [nvarchar](255) NULL,
	[PrincipalInvestigator3BusinessStreetAddressLine1] [nvarchar](255) NULL,
	[PrincipalInvestigator3BusinessStreetAddressLine2] [nvarchar](255) NULL,
	[PrincipalInvestigator3City] [nvarchar](255) NULL,
	[PrincipalInvestigator3State] [nvarchar](255) NULL,
	[PrincipalInvestigator3ZipCode] [nvarchar](255) NULL,
	[PrincipalInvestigator3Country] [nvarchar](255) NULL,
	[PrincipalInvestigator3Province] [nvarchar](255) NULL,
	[PrincipalInvestigator3PostalCode] [nvarchar](255) NULL,
	[PrincipalInvestigator3PrimaryType] [nvarchar](255) NULL,
	[PrincipalInvestigator3Specialty] [nvarchar](500) NULL,
	[PrincipalInvestigator3LicenseStatecode1] [nvarchar](255) NULL,
	[PrincipalInvestigator3LicenseStatecode2] [nvarchar](255) NULL,
	[PrincipalInvestigator3LicenseStatecode3] [nvarchar](255) NULL,
	[PrincipalInvestigator3LicenseStatecode4] [nvarchar](255) NULL,
	[PrincipalInvestigator3LicenseStatecode5] [nvarchar](255) NULL,
	[PrincipalInvestigator4ProfileID] [int] NULL,
	[PrincipalInvestigator4FirstName] [nvarchar](255) NULL,
	[PrincipalInvestigator4MiddleName] [nvarchar](255) NULL,
	[PrincipalInvestigator4LastName] [nvarchar](255) NULL,
	[PrincipalInvestigator4NameSuffix] [nvarchar](255) NULL,
	[PrincipalInvestigator4BusinessStreetAddressLine1] [nvarchar](255) NULL,
	[PrincipalInvestigator4BusinessStreetAddressLine2] [nvarchar](255) NULL,
	[PrincipalInvestigator4City] [nvarchar](255) NULL,
	[PrincipalInvestigator4State] [nvarchar](255) NULL,
	[PrincipalInvestigator4ZipCode] [nvarchar](255) NULL,
	[PrincipalInvestigator4Country] [nvarchar](255) NULL,
	[PrincipalInvestigator4Province] [nvarchar](255) NULL,
	[PrincipalInvestigator4PostalCode] [nvarchar](255) NULL,
	[PrincipalInvestigator4PrimaryType] [nvarchar](255) NULL,
	[PrincipalInvestigator4Specialty] [nvarchar](255) NULL,
	[PrincipalInvestigator4LicenseStatecode1] [nvarchar](255) NULL,
	[PrincipalInvestigator4LicenseStatecode2] [nvarchar](255) NULL,
	[PrincipalInvestigator4LicenseStatecode3] [nvarchar](255) NULL,
	[PrincipalInvestigator4LicenseStatecode4] [nvarchar](255) NULL,
	[PrincipalInvestigator4LicenseStatecode5] [nvarchar](255) NULL,
	[PrincipalInvestigator5ProfileID] [int] NULL,
	[PrincipalInvestigator5FirstName] [nvarchar](255) NULL,
	[PrincipalInvestigator5MiddleName] [nvarchar](255) NULL,
	[PrincipalInvestigator5LastName] [nvarchar](255) NULL,
	[PrincipalInvestigator5NameSuffix] [nvarchar](255) NULL,
	[PrincipalInvestigator5BusinessStreetAddressLine1] [nvarchar](255) NULL,
	[PrincipalInvestigator5BusinessStreetAddressLine2] [nvarchar](255) NULL,
	[PrincipalInvestigator5City] [nvarchar](255) NULL,
	[PrincipalInvestigator5State] [nvarchar](255) NULL,
	[PrincipalInvestigator5ZipCode] [nvarchar](255) NULL,
	[PrincipalInvestigator5Country] [nvarchar](255) NULL,
	[PrincipalInvestigator5Province] [nvarchar](255) NULL,
	[PrincipalInvestigator5PostalCode] [nvarchar](255) NULL,
	[PrincipalInvestigator5PrimaryType] [nvarchar](255) NULL,
	[PrincipalInvestigator5Specialty] [nvarchar](500) NULL,
	[PrincipalInvestigator5LicenseStatecode1] [nvarchar](255) NULL,
	[PrincipalInvestigator5LicenseStatecode2] [nvarchar](255) NULL,
	[PrincipalInvestigator5LicenseStatecode3] [nvarchar](255) NULL,
	[PrincipalInvestigator5LicenseStatecode4] [nvarchar](255) NULL,
	[PrincipalInvestigator5LicenseStatecode5] [nvarchar](255) NULL,
	[SubmittingApplicableManufacturerOrApplicableGPOName] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentID] [bigint] NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentName] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentState] [nvarchar](255) NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentCountry] [nvarchar](255) NULL,
	[RelatedProductIndicator] [nvarchar](255) NULL,
	[CoveredOrNoncoveredIndicator1] [nvarchar](255) NULL,
	[IndicateDrugOrBiologicalOrDeviceOrMedicalSupply1] [nvarchar](255) NULL,
	[ProductCategoryOrTherapeuticArea1] [nvarchar](255) NULL,
	[NameofDrugOrBiologicalOrDeviceOrMedicalSupply1] [nvarchar](255) NULL,
	[AssociatedDrugOrBiologicalNDC1] [nvarchar](255) NULL,
	[CoveredOrNoncoveredIndicator2] [nvarchar](255) NULL,
	[IndicateDrugOrBiologicalOrDeviceOrMedicalSupply2] [nvarchar](255) NULL,
	[ProductCategoryOrTherapeuticArea2] [nvarchar](255) NULL,
	[NameofDrugOrBiologicalOrDeviceOrMedicalSupply2] [nvarchar](255) NULL,
	[AssociatedDrugOrBiologicalNDC2] [nvarchar](255) NULL,
	[CoveredOrNoncoveredIndicator3] [nvarchar](255) NULL,
	[IndicateDrugOrBiologicalOrDeviceOrMedicalSupply3] [nvarchar](255) NULL,
	[ProductCategoryOrTherapeuticArea3] [nvarchar](255) NULL,
	[NameofDrugOrBiologicalOrDeviceOrMedicalSupply3] [nvarchar](255) NULL,
	[AssociatedDrugOrBiologicalNDC3] [nvarchar](255) NULL,
	[CoveredOrNoncoveredIndicator4] [nvarchar](255) NULL,
	[IndicateDrugOrBiologicalOrDeviceOrMedicalSupply4] [nvarchar](255) NULL,
	[ProductCategoryOrTherapeuticArea4] [nvarchar](255) NULL,
	[NameofDrugOrBiologicalOrDeviceOrMedicalSupply4] [nvarchar](255) NULL,
	[AssociatedDrugOrBiologicalNDC4] [nvarchar](255) NULL,
	[CoveredOrNoncoveredIndicator5] [nvarchar](255) NULL,
	[IndicateDrugOrBiologicalOrDeviceOrMedicalSupply5] [nvarchar](255) NULL,
	[ProductCategoryOrTherapeuticArea5] [nvarchar](255) NULL,
	[NameofDrugOrBiologicalOrDeviceOrMedicalSupply5] [nvarchar](255) NULL,
	[AssociatedDrugOrBiologicalNDC5] [nvarchar](255) NULL,
	[TotalAmountofPaymentUSDollars] [money] NULL,
	[DateofPayment] [datetime] NULL,
	[FormofPaymentOrTransferofValue] [nvarchar](255) NULL,
	[ExpenditureCategory1] [nvarchar](255) NULL,
	[ExpenditureCategory2] [nvarchar](255) NULL,
	[ExpenditureCategory3] [nvarchar](255) NULL,
	[ExpenditureCategory4] [nvarchar](255) NULL,
	[ExpenditureCategory5] [nvarchar](255) NULL,
	[ExpenditureCategory6] [nvarchar](255) NULL,
	[PreclinicalResearchIndicator] [nvarchar](255) NULL,
	[DelayinPublicationIndicator] [nvarchar](255) NULL,
	[NameofStudy] [nvarchar](255) NULL,
	[DisputeStatusforPublication] [nvarchar](255) NULL,
	[ProgramYear] [nvarchar](255) NULL,
	[PaymentPublicationDate] [datetime] NULL,
	[ClinicalTrialsGovIdentifier] [nvarchar](255) NULL,
	[ResearchInformationLink] [nvarchar](255) NULL,
	[ContextofResearch] [nvarchar](255) NULL,
	[DeletedDate] [datetime] NULL,
	[CCB] [varchar](100) NULL,
	[CCD] [datetime] NULL,
	[CMB] [varchar](100) NULL,
	[CMD] [datetime] NULL,
	[CEFD] [datetime] NULL,
	[CETD] [datetime] NULL,
	[InsertAuditKey] [int] NULL,
	[UpdateAuditKey] [int] NULL,
	[CheckSumValue] [int] NULL,
 CONSTRAINT [PKResearchDataPGYR2017Key] PRIMARY KEY CLUSTERED 
(
	[PK_ResearchDataPGYR2017Key] 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

ALTER TABLE [SunShineAct].[ResearchDataPGYR2017] ADD  CONSTRAINT [DF_SunShineAct_ResearchDataPGYR2017_CCB]  DEFAULT (suser_sname()) FOR [CCB]
GO

ALTER TABLE [SunShineAct].[ResearchDataPGYR2017] ADD  CONSTRAINT [DF_SunShineAct_ResearchDataPGYR2017_CCD]  DEFAULT (getdate()) FOR [CCD]
GO

ALTER TABLE [SunShineAct].[ResearchDataPGYR2017] ADD  CONSTRAINT [DF_SunShineAct_ResearchDataPGYR2017_CMB]  DEFAULT (suser_sname()) FOR [CMB]
GO

ALTER TABLE [SunShineAct].[ResearchDataPGYR2017] ADD  CONSTRAINT [DF_SunShineAct_ResearchDataPGYR2017_CMD]  DEFAULT (getdate()) FOR [CMD]
GO

ALTER TABLE [SunShineAct].[ResearchDataPGYR2017] ADD  CONSTRAINT [DF_SunShineAct_ResearchDataPGYR2017_CETD]  DEFAULT (NULL) FOR [CETD]
GO

Conformed tier fact ResearchDataPGYR2017

USE [Conformed]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimTeachingHospital]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimPhysicianProfile]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimDate1]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimDate]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimCoveredRecipientType]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] DROP CONSTRAINT [FK_FactSunShinePGY2017_DimApplicableManufacturerOrApplicableGPO]
GO

/****** Object:  Table [SunShineAct].[FactSunShinePGY2017]    Script Date: 4/21/2019 11:00:35 AM ******/
DROP TABLE [SunShineAct].[FactSunShinePGY2017]
GO

/****** Object:  Table [SunShineAct].[FactSunShinePGY2017]    Script Date: 4/21/2019 11:00:35 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[FactSunShinePGY2017](
	[CoveredRecipientTypeKey] [int] NOT NULL,
	[TeachingHospitalKey] [int] NOT NULL,
	[PhysicianProfileKey] [int] NOT NULL,
	[HCPTaxonomyCodeKey] [int] NOT NULL,
	[DateOfPayemntKey] [datetime] NOT NULL,
	[DateOfPayemntPublicationKey] [datetime] NOT NULL,
	[ApplicableManufacturerOrApplicableGPOKey] [int] NOT NULL,
	[Total_Amount_of_Payment_USDollars] [money] NOT NULL,
	[Form_of_Payment_or_Transfer_of_Value] [nvarchar](255) NOT NULL,
	[Record_ID] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimApplicableManufacturerOrApplicableGPO] FOREIGN KEY([ApplicableManufacturerOrApplicableGPOKey])
REFERENCES [SunShineAct].[DimApplicableManufacturerOrApplicableGPO] ([PK_ApplicableManufacturerOrApplicableGPOKey])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimApplicableManufacturerOrApplicableGPO]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimCoveredRecipientType] FOREIGN KEY([CoveredRecipientTypeKey])
REFERENCES [SunShineAct].[DimCoveredRecipientType] ([PK_CoveredRecipientTypeKey])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimCoveredRecipientType]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimDate] FOREIGN KEY([DateOfPayemntKey])
REFERENCES [dbo].[DimDate] ([PK_Date])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimDate]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimDate1] FOREIGN KEY([DateOfPayemntPublicationKey])
REFERENCES [dbo].[DimDate] ([PK_Date])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimDate1]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimPhysicianProfile] FOREIGN KEY([PhysicianProfileKey])
REFERENCES [SunShineAct].[DimPhysicianProfile] ([PK_PhysicianProfileKey])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimPhysicianProfile]
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017]  WITH CHECK ADD  CONSTRAINT [FK_FactSunShinePGY2017_DimTeachingHospital] FOREIGN KEY([TeachingHospitalKey])
REFERENCES [SunShineAct].[DimTeachingHospital] ([PK_TeachingHospitalKey])
GO

ALTER TABLE [SunShineAct].[FactSunShinePGY2017] CHECK CONSTRAINT [FK_FactSunShinePGY2017_DimTeachingHospital]
GO

After all the dimension tables have been added and the fact table has been created, we will have an entity relationship diagram like the one below.

Data Diagram Sun Shine Act Data

Our data modeling of the Open Payments Data supplied by CMS has now been completed. The next step is to make the newly added data available to our top tier applications. We want to point out that the dimensions and fact tables in this layer will not have any indexes or key constraints.

Below we are including our table creation statements.

Presentation tier DimApplicableManufacturerOrApplicableGPO

USE [Presentation]
GO

/****** Object:  Table [SunShineAct].[DimApplicableManufacturerOrApplicableGPO]    Script Date: 4/24/2019 10:24:49 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimApplicableManufacturerOrApplicableGPO](
	[ApplicableManufacturerOrApplicableGPOKey] [int] NOT NULL,
	[ApplicableManufacturerOrApplicableGPOMakingPaymentID] [bigint] NOT NULL,
	[SubmittingApplicable_ManufacturerOrApplicableGPOName] [nvarchar](255) NOT NULL,
	[ApplicableManufacturerOrApplicableGPOName] [nvarchar](255) NOT NULL,
	[ApplicableManufacturerOrApplicableGPOState] [nvarchar](255) NOT NULL,
	[ApplicableManufacturerOrApplicableGPOCountry] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

Presentation tier DimCoveredRecipientType

USE [Presentation]
GO

/****** Object:  Table [SunShineAct].[DimCoveredRecipientType]    Script Date: 4/24/2019 10:26:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimCoveredRecipientType](
	[CoveredRecipientTypeKey] [int] NOT NULL,
	[CoveredRecipientType] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Presentation tier DimPhysicianProfile

USE [Presentation]
GO

/****** Object:  Table [SunShineAct].[DimPhysicianProfile]    Script Date: 4/24/2019 10:28:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimPhysicianProfile](
	[PhysicianProfileKey] [int] NOT NULL,
	[PhysicianProfileID] [int] NULL,
	[PhysicianProfileFirstName] [nvarchar](255) NULL,
	[PhysicianProfileMiddleName] [nvarchar](255) NULL,
	[PhysicianProfileLastName] [nvarchar](255) NULL,
	[PhysicianProfileSuffix] [nvarchar](255) NULL,
	[PhysicianProfileFullName] [nvarchar](255) NULL,
	[PhysicianProfileAddressLine1] [nvarchar](255) NULL,
	[PhysicianProfileAddressLine2] [nvarchar](255) NULL,
	[PhysicianProfileCity] [nvarchar](255) NULL,
	[PhysicianProfileState] [nvarchar](255) NULL,
	[PhysicianProfileZipcode] [nvarchar](255) NULL,
	[PhysicianProfileCountryName] [nvarchar](255) NULL,
	[PhysicianProfileProvinceName] [nvarchar](255) NULL,
	[PhysicianProfilePrimaryCode] [nvarchar](255) NULL,
	[PhysicianProfilePrimaryGrouping] [nvarchar](500) NULL,
	[PhysicianProfilePrimaryClassification] [nvarchar](500) NULL,
	[PhysicianProfilePrimarySpecialization] [nvarchar](500) NULL
) ON [PRIMARY]
GO

Presentation tier DimTeachingHospital

USE [Presentation]
GO

/****** Object:  Table [SunShineAct].[DimTeachingHospital]    Script Date: 4/24/2019 10:29:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[DimTeachingHospital](
	[TeachingHospitalKey] [int] NOT NULL,
	[TeachingHospitalCCN] [int] NULL,
	[TeachingHospitalID] [int] NULL,
	[TeachingHospitalName] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Presentation tier FactSunShinePGY2017

USE [Presentation]
GO

/****** Object:  Table [SunShineAct].[FactSunShinePGY2017]    Script Date: 4/24/2019 10:30:42 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [SunShineAct].[FactSunShinePGY2017](
	[CoveredRecipientTypeKey] [int] NOT NULL,
	[TeachingHospitalKey] [int] NOT NULL,
	[PhysicianProfileKey] [int] NOT NULL,
	[DateOfPayemntKey] [datetime] NOT NULL,
	[ApplicableManufacturerOrApplicableGPOKey] [int] NOT NULL,
	[TotalAmountofPaymentUSDollars] [money] NOT NULL,
	[FormofPaymentorTransferofValue] [nvarchar](255) NOT NULL,
	[RecordID] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO

Ready To Have Us Model Your Data?

Contact Us Today For Our Data Modeling Consulting Services

Get In Touch