I'm in the process of creating a data warehouse. My dimensions are fairly well-defined: Member, Provider, Claim and Date. My fact table on the other hand is causing me to doubt my design. FactClaimDetail is currently designed as follows:
CREATE TABLE [dbo].[FactClaimDetail](
[ClaimNo] [varchar](25) NOT NULL,
[ClaimCreatedDateKey] [int] NOT NULL,
[ServiceFromDateKey] [int] NULL,
[ServiceToDateKey] [int] NULL,
[PaidDateKey] [int] NULL,
[AttestationDateKey] [int] NULL,
[CheckNumber] [int] NULL,
[Quantity] [numeric](18, 0) NULL,
[BilledAmount] [numeric](15, 2) NULL,
[PaidAmount] [numeric](15, 2) NULL,
[InterestAmount] [numeric](15, 2) NULL,
[ContractualAllowance] [numeric](15, 2) NULL,
[OutOfPocketAmount] [numeric](15, 2) NULL,
[DeductibleAmount] [numeric](15, 2) NULL,
[CopayAmount] [numeric](15, 2) NULL,
[CoinsuranceAmount] [numeric](15, 2) NULL,
[RowNumber] [int] NOT NULL,
[Sequence] [smallint] NOT NULL,
[ProcedureCode] [varchar](20) NULL,
[Modifier1] [varchar](2) NULL,
[Modifier2] [varchar](2) NULL,
[Modifier3] [varchar](3) NULL,
[Modifier4] [varchar](3) NULL,
[ServiceDescription] [varchar](30) NULL,
[ClaimStatus] [int] NULL,
[AdjustmentCode] [varchar](7) NULL,
[AdjustmentAmount] [numeric](15, 2) NULL,
[PHCode] [varchar](1) NULL,
[CompanyID] [varchar](10) NULL,
[FinancialResponsibility] [varchar](6) NULL,
[IsCurrent] [bit] NULL,
[EffectiveDate] [datetime] NULL,
[ExpirationDate] [datetime] NULL,
CONSTRAINT [PK_FactClaimDetail] PRIMARY KEY CLUSTERED
(
[ClaimNo] ASC,
[ClaimCreatedDateKey] ASC,
[RowNumber] ASC,
[Sequence] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I intend to implement Slowly Changing Dimensions type 2 on my dimension table. However, my fact table will also need SCD2 implemented as well.
Since the granularity of claim details is focused on the line item level, things like adjustment codes, adjustment amounts and date paid will change from one day to the next. For reference, I've included a diagram of the data warehouse schema at this point.
Am I better off designing this as a dimension table? If so, what would be the suggested guidance to structure an optimized fact table based on the dimensions thus far?
