title: what is Dim, what is Fact?
body: <p>I have an application that I know would make a great cube and would be useful for more than the standard flat Reporting Services report.  We're about to jump into BI stuff with a consultant, but I'd like to give it a shot before we do, mostly so I know something of what we're going to do.</p>
  
  <p>The application tracks surveys in nursing homes across the country.  They can be annual, complaint, or several other type of survey, they have penalties associated with tags given, and have documentation associated with them.</p>
  
  <p>What I'd like to do is come up with a way that will allow us to leverage the data we have - how many tags in florida for the month of June?  How many facilities were on time delivering their documentation?  How many annual(surprise) surveys happened in the 1st quarter of this year compared to last year?</p>
  
  <p>I'm including the schemas in hopes that someone will be able to tell me not only what is dim and what is fact, but what data goes where.  I figure that'll be a great start.</p>
  
  <p>Anything would be really helpful.  I'm trying to get a small data mart set up while I'm pouring through the Data Warehouse Lifecycle Toolkit by Kimball.</p>
  
  <p>Thanks!
  M@</p>
  
  <p>The Entity table - a list of all of our facilities:  Primary key is a five letter code denoting the building</p>
  
  <pre><code>CREATE TABLE [dbo].[Entity](
   [entID] [varchar](10) NOT NULL,
   [entShortName] [varchar](150) NULL,
   [entNumericID] [int] NOT NULL,
   [orgID] [int] NOT NULL,
   [regionID] [int] NOT NULL,
   [portID] [int] NOT NULL,
   [busTypeID] [int] NOT NULL,
   [adpID] [varchar](50) NULL,
   [eHealthDataID] [varchar](50) NULL,
   [updateDate] [datetime] NULL CONSTRAINT [DF_Entity_updateDate]  DEFAULT (getdate()),
   [powProID] [int] NULL,
   [regionReportingID] [int] NULL,
   [regionPresEmail] [varchar](300) NULL,
   [regionClinDirEmail] [varchar](300) NULL,
   CONSTRAINT [PK_EntityNEW] PRIMARY KEY CLUSTERED 
  (
   [entID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Survey Main</p>
  
  <pre><code>CREATE TABLE [dbo].[surveyMain](
   [surveyID] [int] IDENTITY(1,1) NOT NULL,
   [surveyDateFac]  AS (([facility]+'-')+CONVERT([varchar],[surveyDate],(101))),
   [surveyDate] [datetime] NOT NULL,
   [surveyType] [int] NOT NULL,
   [surveyBy] [int] NULL,
   [facility] [varchar](10) NOT NULL,
   [originalSurvey] [int] NULL,
   [exitDate] [datetime] NULL,
   [dpnaDate]  AS (dateadd(month,(3),[exitDate])),
   [clearedTags] [varchar](1) NULL,
   [substantiated] [varchar](1) NULL,
   [firstRevisit] [int] NULL,
   [secondRevisit] [int] NULL,
   [thirdRevisit] [int] NULL,
   [fourthRevisit] [int] NULL,
   [updated] [datetime] NULL CONSTRAINT [DF_surveyMain_updated]  DEFAULT (getdate()),
   CONSTRAINT [PK_tagSurvey] PRIMARY KEY CLUSTERED 
  (
   [surveyID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p><strong>Survey Types:</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[surveyTypes](
   [surveyTypeID] [int] IDENTITY(1,1) NOT NULL,
   [surveyTypeDesc] [varchar](100) NOT NULL,
   CONSTRAINT [PK_surveyTypes] PRIMARY KEY CLUSTERED 
  (
   [surveyTypeID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Survey Files</p>
  
  <pre><code>CREATE TABLE [dbo].[surveyFiles](
   [surveyFileID] [int] IDENTITY(1,1) NOT NULL,
   [surveyID] [int] NOT NULL,
   [surveyFilesTypeID] [int] NOT NULL,
   [documentDate] [datetime] NOT NULL,
   [responseDate] [datetime] NULL,
   [receiptDate] [datetime] NULL,
   [dateCertain] [datetime] NULL,
   [fileName] [varchar](250) NULL,
   [fileUpload] [image] NULL,
   [fileDesc] [varchar](100) NULL,
   [updated] [datetime] NOT NULL CONSTRAINT [DF_surveyFiles_updated]  DEFAULT (getdate()),
   CONSTRAINT [PK_surveyFiles] PRIMARY KEY CLUSTERED 
  (
   [surveyFileID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  </code></pre>
  
  <p>Survey Fines</p>
  
  <pre><code>CREATE TABLE [dbo].[surveyFines](
   [surveyFinesID] [int] IDENTITY(1,1) NOT NULL,
   [surveyID] [int] NULL,
   [surveyFinesTypeID] [int] NULL,
   [dateRecommended] [datetime] NULL,
   [dateImposed] [datetime] NULL,
   [totalFineAmt] [varchar](100) NULL,
   [wasImposed] [varchar](3) NULL,
   [dateCleared] [datetime] NULL,
   [comments] [varchar](500) NULL,
   [updated] [datetime] NOT NULL CONSTRAINT [DF_surveyFines_updated]  DEFAULT (getdate()),
   CONSTRAINT [PK_surveyFines] PRIMARY KEY CLUSTERED 
  (
   [surveyFinesID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 75) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>Survey Tags</p>
  
  <pre><code>CREATE TABLE [dbo].[surveyTags](
   [seq] [int] IDENTITY(1,1) NOT NULL,
   [surveyID] [int] NOT NULL,
   [tagDescID] [int] NOT NULL,
   [tagStatus] [int] NULL,
   [scopesev] [varchar](5) NOT NULL,
   [comments] [varchar](1000) NULL,
   [clearedDate] [datetime] NULL,
   [updated] [datetime] NULL CONSTRAINT [DF_surveyTags_updated]  DEFAULT (getdate()),
   CONSTRAINT [PK_tagMain] PRIMARY KEY CLUSTERED 
  (
   [seq] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>