StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As the previous answer states there can be many different meanings attached to Null values for a dimension, unknown, not applicable, unknown etc. If it is useful to be able to distinguish between them in your application adding "pseudo" dimension entries can help.</p> <p>In any case I would avoid having either Null fact foreign keys or dimension fields, having even a single 'unknown' dimension value will help your users define queries that include a catch-all grouping where the data quality isn't 100% (and it never is).</p> <p>One very simple trick I've been using for this and hasn't bitten me yet is to define my dimensions surrogate keys using int IDENTITY(1,1) in T-sql (start at 1 and increment by 1 per row). Pseudo keys ("Unavailable", "Unassigned", "Not applicable") are defined as negative ints and populated by a stored procedure ran at the beginning of the ETL process.</p> <p>For example a table created as </p> <pre><code> CREATE TABLE [dbo].[Location] ( [LocationSK] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Abbreviation] [varchar](4) NOT NULL, [LocationBK] [int] NOT NULL, [EffectiveFromDate] [datetime] NOT NULL, [EffectiveToDate] [datetime] NULL, [Type1Checksum] [int] NOT NULL, [Type2Checksum] [int] NOT NULL, ) ON [PRIMARY] </code></pre> <p>And a stored procedure populating the table with</p> <pre><code> Insert Into dbo.Location (LocationSK, Name, Abbreviation, LocationBK, EffectiveFromDate, Type1Checksum, Type2Checksum) Values (-1, 'Unknown location', 'Unk', -1, '1900-01-01', 0,0) </code></pre> <p>I have made it a rule to have at least one such pseudo row per dimension which is used in cases where the dimension lookup fails and to build exception reports to track the number of facts which are assigned to such rows. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload