To create a DIM table or not
I have 3 fields from our transactional source table (Status, Abnormal, Type) which are all coded values. All of them do not have a code table to link to. The description for each code is only defined in their data dictionary.
I am sure that these codes are rarely moving at all.
In a scenario like this, is it still worth to create a DIM table for each field in order to have a proper FK in the Fact table?
Some thoughts Im not sure of is the added requirement to handle reloading or patching of keys when new codes become available. Any issues in just storing the actual codes in the Fact tables and have the reporting tool do the decoding of description instead
data-modeling data-warehouse
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
add a comment |
I have 3 fields from our transactional source table (Status, Abnormal, Type) which are all coded values. All of them do not have a code table to link to. The description for each code is only defined in their data dictionary.
I am sure that these codes are rarely moving at all.
In a scenario like this, is it still worth to create a DIM table for each field in order to have a proper FK in the Fact table?
Some thoughts Im not sure of is the added requirement to handle reloading or patching of keys when new codes become available. Any issues in just storing the actual codes in the Fact tables and have the reporting tool do the decoding of description instead
data-modeling data-warehouse
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
FK aside, do you want those values to be easily available to end users? Your database probably won't likeSELECT DISTINCT(Status) FROM FactTable...
– Jeroen Mostert
Nov 15 '18 at 5:44
3
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26
add a comment |
I have 3 fields from our transactional source table (Status, Abnormal, Type) which are all coded values. All of them do not have a code table to link to. The description for each code is only defined in their data dictionary.
I am sure that these codes are rarely moving at all.
In a scenario like this, is it still worth to create a DIM table for each field in order to have a proper FK in the Fact table?
Some thoughts Im not sure of is the added requirement to handle reloading or patching of keys when new codes become available. Any issues in just storing the actual codes in the Fact tables and have the reporting tool do the decoding of description instead
data-modeling data-warehouse
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
I have 3 fields from our transactional source table (Status, Abnormal, Type) which are all coded values. All of them do not have a code table to link to. The description for each code is only defined in their data dictionary.
I am sure that these codes are rarely moving at all.
In a scenario like this, is it still worth to create a DIM table for each field in order to have a proper FK in the Fact table?
Some thoughts Im not sure of is the added requirement to handle reloading or patching of keys when new codes become available. Any issues in just storing the actual codes in the Fact tables and have the reporting tool do the decoding of description instead
data-modeling data-warehouse
data-modeling data-warehouse
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
asked Nov 15 '18 at 5:40
FatCharlieFatCharlie
11
11
FK aside, do you want those values to be easily available to end users? Your database probably won't likeSELECT DISTINCT(Status) FROM FactTable...
– Jeroen Mostert
Nov 15 '18 at 5:44
3
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26
add a comment |
FK aside, do you want those values to be easily available to end users? Your database probably won't likeSELECT DISTINCT(Status) FROM FactTable...
– Jeroen Mostert
Nov 15 '18 at 5:44
3
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26
FK aside, do you want those values to be easily available to end users? Your database probably won't like
SELECT DISTINCT(Status) FROM FactTable...– Jeroen Mostert
Nov 15 '18 at 5:44
FK aside, do you want those values to be easily available to end users? Your database probably won't like
SELECT DISTINCT(Status) FROM FactTable...– Jeroen Mostert
Nov 15 '18 at 5:44
3
3
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53313090%2fto-create-a-dim-table-or-not%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53313090%2fto-create-a-dim-table-or-not%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
FK aside, do you want those values to be easily available to end users? Your database probably won't like
SELECT DISTINCT(Status) FROM FactTable...– Jeroen Mostert
Nov 15 '18 at 5:44
3
I would consider storing them in a junk dimension: kimballgroup.com/2009/06/…
– RADO
Nov 15 '18 at 6:16
Junk dimension was my thought also
– Nick.McDermaid
Nov 15 '18 at 7:40
Thanks for the insights guys appreciate it
– FatCharlie
Nov 16 '18 at 1:26