Ralph Kimball

Ralph Kimball’s approach to data warehousing and dimensional modeling is typically known as a bottom-up approach. This name signifies a bus architecture, in which a number of datamarts are combined using similar dimensions to create an enterprise data warehouse. Each data mart (and in turn, the data warehouse) is created used a star or snowflake schema, which contains one or more fact tables, linked to many dimensions.

To create the bus architecture, datamarts are created that are subject oriented and contain business logic for a particular department. Over time, new datamarts are created, using some of the same entities or dimensions as the original datamart. Eventually, the datamarts grow to create a full enterprise data warehouse.

For more information on Ralph Kimball’s approach, see http://www.kimballgroup.com .

Bill Inmon

The other leading data warehouse methodology is proponed by Bill Inmon and is typically known as a top-down approach. In this scenario, “top-down” means starting with an enterprise warehouse and creating subject area datamarts from that data warehouse. The enterprise data warehouse is typically in a third-normal form, which was covered in Chapter 5, while data marts are typically in a dimensional format.

Inmon also created the concept of a corporate information factory, which combines all organization systems, including applications and data storage, into one cohesive machine. Operational data stores, enterprise data warehouses, and data management systems are prevalent in these systems.

See http://www.inmoncif.com for additional information on Bill Inmon’s approach.

Date Dimension

The date dimension is the most common dimension, as almost every business wants to see how things change over time. Sometimes, the business even wants to see information at a lower granularity than date, such as at an hour or minute level. While it is tempting to combine the time granularity with the date dimension, don’t do it! Analysis is typically done at either the time or date level, so a rollup is not necessary, and you will inflate the number of rows in your dimension (365 days * 1440 minutes = 525,600 rows in your table). Let’s walk through the thought process of creating the date dimension, and then, you can use a similar thought process to create a separate time dimension.

You can always start with a base date dimension and modify it to suit your needs. The base date dimension should have the following features:

• An integer key that uniquely identifies that row: Sometimes, a surrogate key is used, which has absolutely no business value and can be implemented using an IDENTITY column. My recommendation is to use a smart key, which combines the year and date in an eight-digit number, such as 20110928, for September 28, 2011 for the United States folks. Smart keys make sense for date and time dimensions, because you know the entity itself will never change. In other words, September 28, 2011 will always be in the month of September and will always be in the year 2011.
• The business key that represents the entity : For a date dimension, this value is very simple; it is just the date. This business key should always be the lowest granularity of information stored in your dimension.
• Additional standard attributes : These attributes can include information about whether this date is a holiday, whether a promotion is running during this time, or additional fields describing a fiscal calendar

The code to create a base date dimension table is listed here:

 -- Create schema for all dimension tables

 CREATE SCHEMA dim

 GO

 -- Create Date Dimension

 CREATE TABLE dim.Date

 (

  DateKey INTEGER NOT NULL,

  DateValue DATE NOT NULL,

  DayValue INTEGER NOT NULL,

  WeekValue INTEGER NOT NULL,

  MonthValue INTEGER NOT NULL,

  YearValue INTEGER NOT NULL

 CONSTRAINT PK_Date PRIMARY KEY CLUSTERED

 (

  DateKey ASC

 ))

 GO

To populate the dimension, you can use a stored procedure or script that will automatically add additional days as needed. Such a stored procedure follows. Note the initial insert statement that adds the unknown row if it doesn’t exist.

Unknown rows are used to symbolize that the relationship between a metric and a particular dimension entity does not exist. The relationship may not exist for a number of reasons:

• The relationship does not exist in the business process.
• The incoming feed does not yet know what the relationship should be.
• The information does not appear to be valid or applicable to the scenario.

By tying the fact table to the unknown row, it is very simple to see where the relationship is missing. The surrogate key for each unknown row is –1, and the descriptors contain variations of “unknown,” such as UN, -1, and UNK. Each dimension that we will create will contain an unknown row to highlight this use case.

In some cases, it may make sense to create multiple unknown rows to distinguish between the three reasons for having an unknown row. One reason why you may want to do this is if you have many late-arriving dimensions, where you are very concerned with the rows that have not yet been linked to a dimension. Another reason you may want to do this is if you have poor data quality, and it is important to distinguish between acceptable and unacceptable unknown rows.

The following stored procedure uses only one unknown row:

 -- Create Date Dimension Load Stored Precedure

 CREATE PROCEDURE dim.LoadDate (@startDate DATETIME, @endDate DATETIME)

 AS

 BEGIN

 IF NOT EXISTS (SELECT * FROM dim.Date WHERE DateKey = -1)

 BEGIN

 INSERT INTO dim.Date

 SELECT -1, '01/01/1900', -1, -1, -1, -1

 END

 WHILE @startdate <= @enddate

  BEGIN

  IF NOT EXISTS (SELECT * FROM dim.Date WHERE DateValue = @startdate)

  INSERT INTO dim.Date

  SELECT CONVERT(CHAR(8), @startdate, 112) AS DateKey

  ,@startdate AS DateValue

  ,DAY(@startdate) AS DayValue

  ,DATEPART(wk, @startdate) AS WeekValue

  ,MONTH(@startdate) AS MonthValue

  ,YEAR(@startdate) AS YearValue

  SET @startdate = DATEADD(dd, 1, @startdate)

  END

 END

 GO

The outcome of this stored procedure is to load any date values that are not yet in the date dimension. Running the following query will insert two years of date:

 EXECUTE dim.LoadDate '01/01/2011', '12/31/2012'

 GO

A sample of the data in the table is shown here:

DateKey	DateValue	DayValue	WeekValue	MonthValue	YearValue
--------	----------	--------	---------	----------	---------
-1	1900-01-01	-1	-1	-1	-1
20111230	2011-12-30	30	53	12	2011
20111231	2011-12-31	31	53	12	2011
20120101	2012-01-01	1	1	1	2012
20120102	2012-01-02	2	1	1	2012

This dimension and stored procedure may be exactly what you need, and if so, move onto the next section on slowly changing dimensions. On the other hand, there’s a good chance that you’ll need to go further with this dimension. To start, many organizations have fiscal calendars. You will use the same base date dimension, but add additional columns for FiscalDayValue, FiscalWeekValue, and so on. Along the same vein, you may also have date-specific descriptors that should be included. These could be as simple as calling out the federal holidays or as complicated as highlighting the various sales periods throughout the year. To include any of those additional items, you’ll want to modify the stored procedure to use the logic specific to your organization.

Slowly Changing Dimension

The date dimension is about as simple as a dimension can get. The information can be populated once or over time and is never modified. The rest of the dimensions won’t be that simple. To begin, we need to discuss the concept of slowly changing dimensions. Here are the most common types of slowly changing dimensions:

• Type 1, or changing: A type 1 change overwrites the value that was previously assigned to that attribute. A type 1 change is typically used when keeping the original value is not important. Example attributes include product category and company name.
• Type 2, or historical: A type 2 change is more in-depth than a type 1 change. It tracks any changes that occurred to that attribute over time so seeing the original, final, and any intermediate values is possible. Example historical attributes in which you may be interested include customer state and employee manager.
• Type 3, or hybrid: Finally, a type 3 change is a hybrid of both the type 1 and type 2 changes. It keeps track of the intermediate and final values on every row. This allows reporting to compare the current value with the latest value for any intermediate value or to be able to see the alternate view, as though the change didn’t happen. This approach is not often used, but one good example is comparing the average temperature for a location at that time versus the final average temperature over the life of the warehouse.

As this book has hammered over and over again, uniqueness is so very important when dealing with data. And I’m not going to contradict that now, except to say that it is possibly even more important when dealing with reporting. You need to determine the unique identifier, also known as the business key, for each entity. This key is how you will know how to link a dimension to a fact and how to create additional rows in the case of a slowly changing dimension.

In the case of a health insurance company, a member can change personal information on a regular basis, and seeing how that information tracks over time may be important. Let’s walk through the steps of creating the member dimension.

As with every dimension, we begin by determining the business key for the entity. For an insured member, the business key is the insurance number. Additional attributes for personal information that are analyzed include first name, last name, primary care physician, county, state, and membership duration. Our initial dimension can be created using the following script:

 -- Create the Member dimension table

 CREATE TABLE dim.Member

 (

  MemberKey INTEGER NOT NULL IDENTITY(1,1),

  InsuranceNumber VARCHAR(12) NOT NULL,

  FirstName VARCHAR(50) NOT NULL,

  LastName VARCHAR(50) NOT NULL,

  PrimaryCarePhysician VARCHAR(100) NOT NULL,

  County VARCHAR(40) NOT NULL,

  StateCode CHAR(2) NOT NULL,

  MembershipLength VARCHAR(15) NOT NULL

 CONSTRAINT PK_Member PRIMARY KEY CLUSTERED

 (

  MemberKey ASC

 ))

 GO

For demonstration’s sake, load the member dimension with the following INSERT script:

 -- Load Member dimension table

 SET IDENTITY_INSERT [dim].[Member] ON

 GO

 INSERT INTO [dim].[Member]

 ([MemberKey],[InsuranceNumber],[FirstName],[LastName],[PrimaryCarePhysician]

  ,[County],[StateCode],[MembershipLength])

 VALUES

 (-1, 'UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UN','UNKNOWN')

 GO

 SET IDENTITY_INSERT [dim].[Member] OFF

 GO

 INSERT INTO [dim].[Member]

 ([InsuranceNumber],[FirstName],[LastName],[PrimaryCarePhysician]

  ,[County],[StateCode],[MembershipLength])

 VALUES

 ('IN438973','Brandi','Jones','Dr. Keiser & Associates','Henrico','VA','<1 year')

 GO

 INSERT INTO [dim].[Member]

 ([InsuranceNumber],[FirstName],[LastName],[PrimaryCarePhysician]

  ,[County],[StateCode],[MembershipLength])

 VALUES

 ('IN958394','Neil','Gomez','Healthy Lifestyles','Henrico','VA','1-2 year')

 GO

 INSERT INTO [dim].[Member]

 ([InsuranceNumber],[FirstName],[LastName],[PrimaryCarePhysician]

  ,[County],[StateCode],[MembershipLength])

 VALUES

 ('IN3867910','Catherine','Patten','Dr. Jenny Stevens','Spotsylvania','VA','<1 year')

 GO

Use the following query to retrieve the sample data that follows:

 select MemberKey, InsuranceNumber, FirstName, LastName, PrimaryCarePhysician

 from dim.Member

 GO

Here are the results:

MemberKey	Insurance	FirstName	LastName	PrimaryCarePhysician
-----------	-----------	-----------	----------	---------------------
-1	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN
1	IN438973	Brandi	Jones	Dr. Keiser & Associates
2	IN958394	Neil	Gomez	Healthy Lifestyles
3	IN3867910	Catherine	Patten	Dr. Jenny Stevens

What do we do when a piece of information changes? Well, if it’s something that we want to track, we use a type 2 change. If it’s something we don’t care about, we use type 1. We must know which type we want to track at modeling time to make some adjustments. In the case of the member dimension, we want to use a type 2 attribute. We know this because we want to track if a member’s primary care physician (PCP) changes to verify that we are only paying out for visits to a PCP. To track this information as the type 2 attribute, we need to add a flag called isCurrent to indicate which row contains the most current information. The code to add this field is listed here:

 ALTER TABLE dim.Member

 ADD isCurrent INTEGER NOT NULL DEFAULT 1

 GO

When a load process runs to update the member dimension, it will check to see if there is a match on the business key of InsuranceNumber, and if so, it will add an additional row but flip the isCurrent bit on the old row to 0. Use the following code to emulate Brandi changing her primary care physician and updating the old record:

 INSERT INTO [dim].[Member]

 ([InsuranceNumber],[FirstName],[LastName],[PrimaryCarePhysician]

  ,[County],[StateCode],[MembershipLength])

 VALUES

 ('IN438973','Brandi','Jones','Dr. Jenny Stevens','Henrico','VA','<1 year')

 GO

 UPDATE [dim].[Member] SET isCurrent = 0

 WHERE InsuranceNumber = 'IN438973' AND PrimaryCarePhysician = 'Dr. Keiser & Associates'

 GO

If we use a similar query as before, but add the new isCurrent column as follows, we see the full change of the dimension:

 select MemberKey, InsuranceNumber, FirstName, LastName, PrimaryCarePhysician, isCurrent

 from dim.Member

And here are the results:

MemberKey	Insurance	FirstName	LastName	PrimaryCarePhysician	isCurrent
---------	---------	---------	--------	-----------------------	---------
-1	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	1
1	IN438973	Brandi	Jones	Dr. Keiser & Associates	0
2	IN958394	Neil	Gomez	Healthy Lifestyles	1
3	IN3867910	Catherine	Patten	Dr. Jenny Stevens	1
4	IN438973	Brandi	Jones	Dr. Jenny Stevens	1

An alternative to using the isCurrent flag is to use two columns: start date and end date. The start date describes when the row first became active, and the end date is when that row has expired. If the row has not yet expired, the value is null. If you wanted to see all current values, you can query all rows where the end date is null. In addition to that information, you can also see when each dimension row changed without having to look at the fact table.

You may ask yourself why you need any flag or date range. Isn’t it possible to just find the latest row based on the growing identity key, where the maximum value is the current one? You would be absolutely correct that you can get the same information; however, the query to pull that information is more complicated and difficult to pull. As a case in point, would you rather write the query in Listing 14-1 or 14-2 on a regular basis?

Listing 14-1. Preferred Query to Pull the Latest Dimension Row

select * from dim.Member where isCurrent = 1

GO

Listing 14-2. Rejected Query to Pull the Latest Dimension Row

select * from (

   select m.*, ROW_NUMBER() OVER (PARTITION BY m.InsuranceNumber

   ORDER BY m.MemberKey DESC) As Latest

   from dim.Member m ) LatestMembers

WHERE LatestMembers.Latest = 1

GO

The provider dimension is similar to the member dimension, as it can change over time. The information in this information is based on a standard through the National Provider Registry, which can be found at http://www.cms.gov/NationalProvIdentStand/downloads/Data_Dissemination_File-Readme.pdf . By using a limited set of information, we can create a dimension as described here:

 -- Create the Provider dimension table

 CREATE TABLE dim.Provider (

  ProviderKey INTEGER IDENTITY(1,1) NOT NULL,

  NPI VARCHAR(10) NOT NULL,

  EntityTypeCode INTEGER NOT NULL,

  EntityTypeDesc VARCHAR(12) NOT NULL, -- (1:Individual,2:Organization)

  OrganizationName VARCHAR(70) NOT NULL,

  DoingBusinessAsName VARCHAR(70) NOT NULL,

  Street VARCHAR(55) NOT NULL,

  City VARCHAR(40) NOT NULL,

  State VARCHAR(40) NOT NULL,

  Zip VARCHAR(20) NOT NULL,

  Phone VARCHAR(20) NOT NULL,

  isCurrent INTEGER NOT NULL DEFAULT 1

  CONSTRAINT PK_Provider PRIMARY KEY CLUSTERED

 (

  ProviderKey ASC

 ))

 GO

Add an initial set of data using the following query:

 -- Insert sample data into Provider dimension table

 SET IDENTITY_INSERT [dim].[Provider] ON

 GO

 INSERT INTO [dim].[Provider]

 ([ProviderKey],[NPI],[EntityTypeCode],[EntityTypeDesc],[OrganizationName],

  [DoingBusinessAsName],[Street],[City],[State],[Zip],[Phone])

 VALUES

 (-1, 'UNKNOWN',-1,'UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN','UNKNOWN')

 GO

 SET IDENTITY_INSERT [dim].[Provider] OFF

 GO

 INSERT INTO [dim].[Provider]

 ([NPI],[EntityTypeCode],[EntityTypeDesc],[OrganizationName],

 [DoingBusinessAsName],[Street],[City],[State],[Zip],[Phone])

 VALUES

 ('1234567',1,'Individual','Patrick Lyons','Patrick Lyons','80 Park St.',

 'Boston','Massachusetts','55555','555-123-1234')

 GO

 INSERT INTO [dim].[Provider]

 ([NPI],[EntityTypeCode],[EntityTypeDesc],[OrganizationName],

  [DoingBusinessAsName],[Street],[City],[State],[Zip],[Phone])

 VALUES

 ('2345678',1,'Individual','Lianna White, LLC','Dr. White & Associates','74 West Pine Ave.',

 'Waltham','Massachusetts','55542','555-123-0012')

 GO

 INSERT INTO [dim].[Provider]

 ([NPI],[EntityTypeCode],[EntityTypeDesc],[OrganizationName],

  [DoingBusinessAsName],[Street],[City],[State],[Zip],[Phone])

 VALUES

 ('76543210',2,'Organization','Doctors Conglomerate, Inc','Family Doctors','25 Main Street Suite 108',

 'Boston','Massachusetts','55555','555-321-4321')

 GO

 INSERT INTO [dim].[Provider]

 ([NPI],[EntityTypeCode],[EntityTypeDesc],[OrganizationName],

  [DoingBusinessAsName],[Street],[City],[State],[Zip],[Phone])

 VALUES

 ('3456789',1,'Individual','Dr. Drew Adams','Dr. Drew Adams','1207 Corporate Center',

 'Peabody','Massachusetts','55554','555-234-1234')

 GO

A dimension can have attributes of multiple types, so the last name can be type 1 while the primary care physician is type 2. I’ve never understood why they’re called slowly changing dimensions when they exist at an attribute level, but that’s the way it is.

Snowflake Dimension

In our discussion of modeling methodologies, we talked about star schemas and snowflake schemas. The key differentiator between those two methodologies is the snowflake dimension. A snowflake dimension takes the information from one dimension and splits it into two dimensions. The second dimension contains a subset of information from the first dimension and is linked to the first dimension, rather than the fact, through a surrogate key.

A great example of this is when one entity can be broken into two subentities that change on their own. By grouping the subentities’ properties together in their own dimension, they can change on their own or be linked directly to the fact on its own, instead of using the intermediary dimension, if the grain applies. However, breaking out an entity into a snowflake dimension is not without its downfalls. Never, ever, ever link both the first and second dimension to the same fact table. You’d create a circular relationship that could potentially cause discrepancies if your loads are not kept up to date. I also recommend never snowflaking your dimensions to more than one level, if at all. The added complexity and maintenance needed to support such a structure is often not worthwhile when alternative methods can be found. Let’s talk through a possible snowflake dimension, when you would want to use it, and alternative methods.

To continue our healthcare example, we will look at the types of insurance coverage provided by the company. There are multiple types of plans that each contains a set of benefits. Sometimes, we look at the metrics at the benefit level and other times, at the plan level. Even though we sometimes look at benefits and plans differently, there is a direct relationship between them.

Let’s begin by looking at each of the tables separately. The benefit table creation script is listed here:

 -- Create the Benefit dimension table

 CREATE TABLE dim.Benefit(

  BenefitKey INTEGER IDENTITY(1,1) NOT NULL,

  BenefitCode INTEGER NOT NULL,

  BenefitName VARCHAR(35) NOT NULL,

  BenefitSubtype VARCHAR(20) NOT NULL,

  BenefitType VARCHAR(20) NOT NULL

 CONSTRAINT PK_Benefit PRIMARY KEY CLUSTERED

 (

  BenefitKey ASC

 ))

 GO

The health plan creation script is shown here:

 -- Create the Health Plan dimension table

 CREATE TABLE dim.HealthPlan(

  HealthPlanKey INTEGER IDENTITY(1,1) NOT NULL,

  HealthPlanIdentifier CHAR(4) NOT NULL,

  HealthPlanName VARCHAR(35) NOT NULL

 CONSTRAINT PK_HealthPlan PRIMARY KEY CLUSTERED

 (

  HealthPlanKey ASC

 ))

 GO

I’ve already explained that a set of benefits can be tied to a plan, so we need to add a key from the benefit dimension to the plan dimension. Use an ALTER statement to do this, as shown:

 ALTER TABLE dim.Benefit

 ADD HealthPlanKey INTEGER

 GO

 ALTER TABLE dim.Benefit WITH CHECK

 ADD CONSTRAINT FK_Benefit_HealthPlan

 FOREIGN KEY(HealthPlanKey) REFERENCES dim.HealthPlan (HealthPlanKey)

 GO

When the benefit key is added to a fact, the plan key does not also need to be added but will still be linked and can be used to aggregate or filter the information. In addition, if the grain of one of the fact tables is only at the plan level, the plan key can be used directly without having to use the benefit key.

We will not use the Benefit dimension in our model, but we will use the HealthPlan dimension. You can populate the data using the following script:

 -- Insert sample data into Health plan dimension

 INSERT INTO [dim].[HealthPlan]

  ([HealthPlanIdentifier],[HealthPlanName])

 VALUES ('BRON','Bronze Plan')

 GO

 INSERT INTO [dim].[HealthPlan]

  ([HealthPlanIdentifier],[HealthPlanName])

 VALUES ('SILV','Silver Plan')

 GO

 INSERT INTO [dim].[HealthPlan]

  ([HealthPlanIdentifier],[HealthPlanName])

 VALUES ('GOLD','Gold Plan')

 GO

An important concept that we have not yet covered is how to handle hierarchies in your data structure. Hierarchies are an important concept that you will hear about over and over again. Each level of the hierarchy is included in the same dimension, and the value of each parent level is repeated for every child value. In the Benefit dimension, BenefitSubtype and BenefitType create a hierarchy. Ensure that all subtypes have the same type in the load process to help later reporting.

Type Dimension

Another common dimension type that often appears is what I like to call a type dimension, because it is often simple to identify due to its name. This dimension is a very simple one that typically contains a few type 1 changing attributes. Not all entities have to contain the word “type,” but it’s a nice indicator. Examples include transaction type, location type, and sale type.

In our health care example, the adjudication type is a perfect example of this dimension. The adjudication type dimension contains two columns and four rows, and the full creation script is shown here:

 -- Create the AdjudicationType dimension table

 CREATE TABLE dim.AdjudicationType (

  AdjudicationTypeKey INTEGER IDENTITY(1,1) NOT NULL,

  AdjudicationType VARCHAR(6) NOT NULL,

  AdjudicationCategory VARCHAR(8) NOT NULL

 CONSTRAINT PK_AdjudicationType PRIMARY KEY CLUSTERED

 (

  AdjudicationTypeKey ASC

 ))

 GO

The values for the adjudication type dimension are static, so they can be loaded once, instead of on a recurring or nightly basis. The following INSERT script will insert the three business value rows and the one unknown row:

 -- Insert values for the AdjudicationType dimension

 SET IDENTITY_INSERT dim.AdjudicationType ON

 GO

 INSERT INTO dim.AdjudicationType

  (AdjudicationTypeKey, AdjudicationType, AdjudicationCategory)

 VALUES (-1, 'UNKNWN', 'UNKNOWN')

 INSERT INTO dim.AdjudicationType

  (AdjudicationTypeKey, AdjudicationType, AdjudicationCategory)

 VALUES (1, 'AUTO', 'ACCEPTED')

 INSERT INTO dim.AdjudicationType

  (AdjudicationTypeKey, AdjudicationType, AdjudicationCategory)

 VALUES (2, 'MANUAL', 'ACCEPTED')

 INSERT INTO dim.AdjudicationType

  (AdjudicationTypeKey, AdjudicationType, AdjudicationCategory)

 VALUES (3, 'DENIED', 'DENIED')

 GO

 SET IDENTITY_INSERT dim.AdjudicationType OFF

 GO

The values in this table can then be shown as follows:

AdjudicationTypeKey	AdjudicationType	AdjudicationCategory
-------------------	----------------	-------------------
-1	UNKNWN	UNKNOWN
1	AUTO	ACCEPTED
2	MANUAL	ACCEPTED
3	DENIED	DENIED

This provides an easy way to filter any claim that you have on either the actual type of adjudication that occurred or the higher-level category.

Our final two dimensions also fit into this category: Diagnosis and HCPCSProcedure. Diagnosis and procedure codes contain a standard set of data fields. Diagnoses and procedure codes are known as International Classification of Diseases, Tenth Revision, Clinical Modification (ICD10-CM) and Healthcare Common Procedure Coding System (HCPCS) codes, respectively. We can access this set of information through the Centers for Medicare and Medicaid Services web site ( http://www.cms.gov ). The creation script for these dimensions follows:

 -- Create Diagnosis dimension table

 CREATE TABLE dim.Diagnosis(

  DiagnosisKey int IDENTITY(1,1) NOT NULL,

  DiagnosisCode char(7) NULL,

  ShortDesc varchar(60) NULL,

  LongDesc varchar(322) NULL,

  OrderNumber int NULL,

  CONSTRAINT PK_Diagnosis PRIMARY KEY CLUSTERED

 (

  DiagnosisKey ASC

 ))

 GO

 -- Create HCPCSProcedure dimension table

 CREATE TABLE dim.HCPCSProcedure (

  ProcedureKey INTEGER IDENTITY(1,1) NOT NULL,

  ProcedureCode CHAR(5) NOT NULL,

  ShortDesc VARCHAR(28) NOT NULL,

  LongDesc VARCHAR(80) NOT NULL

  CONSTRAINT PK_HCPCSProcedure PRIMARY KEY CLUSTERED

 (

  ProcedureKey ASC

 ))

 GO

Depending on how in-depth your business needs are, you can add more fields to the diagnosis and procedure dimensions. For a full list of all available diagnosis fields, see http://www.cms.gov/ICD10/downloads/ICD10OrderFiles.zip , and for all available procedure fields, see https://www.cms.gov/HCPCSReleaseCodeSets/Downloads/contr_recordlayout_2011.pdf . You can also use the files provided to load the set of static rows (for each year that the code set is released). Sample Integration Services packages are provided with the code for this chapter to load these values.

Transaction Fact

The fact table that most people know is the transaction fact. This table relates the metrics and dimensions together very simply: every action that occurs in the business process, there is one row in the table. It is important to understand the grain of the table, which is the combination of the dimensions that describes what the metric is used for. For example, if you were to look at a bank transaction, the grain of the table could consist of a day, bank location, bank teller, customer, and transaction type. This is in comparison to a bank promotion where the grain could be hour, bank location, customer, and promotion type.

Along with the dimension grain, metrics make up the fact table. The metrics are typically numeric values, such as currencies, quantities, or counts. These values can be aggregated in some fashion: by summing, averaging, or using the maximum or minimum values.

To continue our healthcare example, one transaction fact would be the payouts for insurance claims. Since we’ve already created our dimensions, there is a good chance we already know what our grain should be, but let’s walk through the business process to make sure we have everything we need.

For this example, we have talked to the business owners and understand the business process down to the smallest detail. We know that our grain is day, insurance number, adjudication value, ICD-10 code, and HCPC. Once we have the grain, we are very close to having a complete transaction fact.

Now that we have the dimensional grain of the fact, the next step is to describe the metrics related to those dimensions. We will use the same process we followed to determine our dimensions. Look at the business process description that started our dimension discussion, and determine the aggregatable values that become the metrics, and pull out the desired values. An example of this process with metrics italicized can be seen in the following paragraph:

When an insurance company receives a claim for a member, the claim goes through an adjudication process. The claim can either be automatically adjudicated, manually adjudicated, or denied. As an insurance company, we need to know whether we adjudicated the claim, how much the physician requested based on the member’s diagnosis, and how much we are willing to pay for that procedure.

Based on the italicized phrases, the desired metrics are adjudication counts, claim amount, and payout amount. It is important to look at how the business uses these metrics. Existing reports are a great place to start, but you also want to talk to the business analysts to understand what they do with that information. If they always take the information from the report and then perform manipulations in Excel, you want to try to replicate the manipulations they are doing directly into your dimensional model.

Our completed fact table is shown here:

 -- Create schema for all fact tables

 CREATE SCHEMA fact

 GO

 -- Create Claim Payment transaction fact table

 CREATE TABLE fact.ClaimPayment

 (

  DateKey INTEGER NOT NULL,

  MemberKey INTEGER NOT NULL,

  AdjudicationTypeKey INTEGER NOT NULL,

  ProviderKey INTEGER NOT NULL,

  DiagnosisKey INTEGER NOT NULL,

  ProcedureKey INTEGER NOT NULL,

  ClaimID VARCHAR(8) NOT NULL,

  ClaimAmount DECIMAL(10,2) NOT NULL,

  AutoPayoutAmount DECIMAL(10,2) NOT NULL,

  ManualPayoutAmount DECIMAL(10,2) NOT NULL,

  AutoAdjudicatedCount INTEGER NOT NULL,

  ManualAdjudicatedCount INTEGER NOT NULL,

  DeniedCount INTEGER NOT NULL

 )

 GO

Of course, we need to link our fact table to the dimensions that we’ve already created, using foreign keys. When loading your data, it may make sense to disable or remove the constraints and enable them afterward to increase the speed of the load. I like to have the relationships after the fact to double check that the load of information was successful and that the fact doesn’t reference dimensions keys that do not exist. To set up the foreign keys, use the following script:

 -- Add foreign keys from ClaimPayment fact to dimensions

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_AdjudicationType

 FOREIGN KEY(AdjudicationTypeKey) REFERENCES dim.AdjudicationType (AdjudicationTypeKey)

 GO

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_Date

 FOREIGN KEY(DateKey) REFERENCES dim.Date (DateKey)

 GO

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_Diagnosis

 FOREIGN KEY(DiagnosisKey) REFERENCES dim.Diagnosis (DiagnosisKey)

 GO

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_HCPCSProcedure

 FOREIGN KEY(ProcedureKey) REFERENCES dim.HCPCSProcedure (ProcedureKey)

 GO

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_Member

 FOREIGN KEY(MemberKey) REFERENCES dim.Member (MemberKey)

 GO

 ALTER TABLE fact.ClaimPayment WITH CHECK

 ADD CONSTRAINT FK_ClaimPayment_Provider

 FOREIGN KEY(ProviderKey) REFERENCES dim.Provider (ProviderKey)

 GO

How did we come up with six metrics from the three underlined descriptions from our business process? In this case, we talked to the business owners and found out that they always look at payout amounts in a split fashion: either by the automatically adjudicated or manually adjudicated amounts. We will model this business scenario by splitting the value into two separate columns based on whether the adjudicated flag is set to AUTO or MANUAL. This means that one column will always have 0 for every row. While this approach may seem counterintuitive, it will help us to query the end result without having to always filter on the adjudication type dimension. Since we know they will always look at one or the other, consider the queries in Listings 14-3 and 14-4. Which would you rather have to write every time you pull payment information from the database?

Listing 14-3. Preferred Query to Pull Amount Information

select AutoPayoutAmount, ManualPayoutAmount

from fact.ClaimPayment

GO

Listing 14-4. Rejected Query to Pull Amount Information

select CASE dat.AdjudicationType

        WHEN 'AUTO'

        THEN fp.ClaimAmount

        ELSE 0

        END as AutoPayoutAmount

  , CASE dat.AdjudicationType

    WHEN 'MANUAL'

    THEN fp.ClaimAmount

    ELSE 0

    END as ManualPayoutAmount

from fact.ClaimPayment fp

left join dim.AdjudicationType dat on fp.AdjudicationTypeKey=dat.AdjudicationTypeKey

GO

The next metric we want to discuss deeper is the adjudication counts, which help the business in figuring out how many of their claims are adjudicated (manually versus automatically) and denied. Rather than force the business to use the adjudication flag again, we will add three columns for each adjudication type. This set of columns will have a value of 1 in one of the columns and 0 in the other two. Some people prefer to think of these as flag columns with bit columns that are flipped if the value fits that row, but I prefer to think of and use numeric values that are easily aggregated.

When creating your SQL Server tables, always use the smallest sized data type that you can. This is another scenario where understanding the business is essential. The data type should be large enough to handle any data value that comes in but small enough that it doesn’t waste server space.

You can use a query similar to the following to create a set of random rows for your fact tables:

 -- Insert sample data into ClaimPayment fact table

 DECLARE @i INT

 SET @i = 0

 WHILE @i < 1000

 BEGIN

 INSERT INTO fact.ClaimPayment

 (

  DateKey, MemberKey, AdjudicationTypeKey, ProviderKey, DiagnosisKey,

  ProcedureKey, ClaimID, ClaimAmount, AutoPayoutAmount, ManualPayoutAmount,

  AutoAdjudicatedCount, ManualAdjudicatedCount, DeniedCount

 )

 SELECT

  CONVERT(CHAR(8), DATEADD(dd, RAND() * -100, getdate()), 112),

  (SELECT CEILING((COUNT(*) - 1) * RAND()) from dim.Member),

  (SELECT CEILING((COUNT(*) - 1) * RAND()) from dim.AdjudicationType),

  (SELECT CEILING((COUNT(*) - 1) * RAND()) from dim.Provider),

  (SELECT CEILING((COUNT(*) - 1) * RAND()) from dim.Diagnosis),

  (SELECT CEILING((COUNT(*) - 1) * RAND()) from dim.HCPCSProcedure),

  'CL' + CAST(@i AS VARCHAR(6)),

  RAND() * 100000,

  RAND() * 100000 * (@i % 2),

  RAND() * 100000 * ((@i+1) % 2),

  @i % 2,

  (@i+1) % 2,

  0

 SET @i = @i + 1

 END

 GO

Finally, the remaining column in the claim payment fact to discuss is the ClaimID. This column is the odd man out, in that it looks like a dimensional attribute but lives in the fact. These columns are known as degenerate dimensions and are typically identifiers or transaction numbers. In scenarios where an entity has an almost one-to-one relationship with the fact, putting the entity in its own dimension would just waste space. These columns are typically used for more detailed or drill-down reporting, so we can include the data in the fact for ease of reporting.

Snapshot Fact

The snapshot fact is not always intuitive when thinking through a business process or trying to query your model, but it is extremely powerful. As the name suggests, the snapshot fact records a set of information as a snapshot in time. Typical snapshot facts include financial balances on a daily basis and monthly membership counts.

The healthcare data model we will complete in this section is shown in Figure 14-3.

In our healthcare example, daily member count is a perfect example of a snapshot fact. We begin by following a similar process to determine our dimensions and metrics. Our business process is listed below with dimensions in bold and metrics italicized:

An insurance company’s core business depends on its members. It is important to know how many members the company has over time, whether the number of members is increasing or decreasing, and which health plan is doing well over time.

We don’t have many dimensions attached to this fact based on the business process, but that’s OK because it supports our reporting questions. We have two main dimensions: the date and health plan. Our metrics are the number of members that we have. Note that we don’t have a member dimension because we are looking at an aggregation of the number of members over time, not individual members. It is important to provide this data on a time basis so that people can report historical information. Our final snapshot table can be created with the following script:

 -- Create Membership snapshot fact table

 CREATE TABLE fact.Membership (

  DateKey INTEGER NOT NULL,

  HealthPlanKey INTEGER NOT NULL,

  MemberAmount INTEGER NOT NULL

 )

 GO

In addition, we need to add the foreign keys to link the fact table to our dimensions, using the following script:

 -- Add foreign keys from Membership fact to dimensions

 ALTER TABLE fact.Membership WITH CHECK

 ADD CONSTRAINT FK_Membership_Date

 FOREIGN KEY(DateKey) REFERENCES dim.Date (DateKey)

 GO

 ALTER TABLE fact.Membership WITH CHECK

 ADD CONSTRAINT FK_Membership_HealthPlan

 FOREIGN KEY(HealthPlanKey) REFERENCES dim.HealthPlan (HealthPlanKey)

 GO

To load sample data into this table, use the following query:

 -- Insert sample data into the Membership fact table

 DECLARE @startdate DATE

 DECLARE @enddate DATE

 SET @startdate = '1/1/2011'

 SET @enddate = '12/31/2011'

 WHILE @startdate <= @enddate

 BEGIN

 INSERT INTO fact.Membership

 SELECT CONVERT(CHAR(8), @startdate, 112) AS DateKey

  ,1 AS HPKey

  ,RAND() * 1000 AS MemberAmount

 INSERT INTO fact.Membership

 SELECT CONVERT(CHAR(8), @startdate, 112) AS DateKey

  ,2 AS HPKey

  ,RAND() * 1000 AS MemberAmount

 INSERT INTO fact.Membership

 SELECT CONVERT(CHAR(8), @startdate, 112) AS DateKey

  ,3 AS HPKey

  ,RAND() * 1000 AS MemberAmount

 SET @startdate = DATEADD(dd, 1, @startdate)

 END

 GO

This type of fact table can get very large, because it contains data for every combination of keys. Although, in this case, the key combination is just day and health plan, imaging adding a few more dimensions, and see how quickly that would grow. What we lose in storage, however, we gain in power. As an example, the following query quickly illustrates how many members plan 2 had over time. We can use this information to show a line chart or a dashboard that shows trends.

 select dd.DateValue, fm.MemberAmount

 from fact.Membership fm

 left join dim.Date dd on fm.DateKey = dd.DateKey

 left join dim.HealthPlan dp on fm.HealthPlanKey = dp.HealthPlanKey

 where dd.DateValue IN ('2011-09-01', '2011-09-02', '2011-09-03')

 and fm.HealthPlanKey = 2

 GO

This snapshot fact table is based on a daily grain, but showing snapshots on only a monthly grain instead is also common. This coarser grain can help the storage issue but should only be an alternative if the business process looks at the metrics on the monthly basis.