DWH SCD type 2 implementation in SQL Server scd2 and scd1Issue with inferred member using SSIS Dimension Merge SCD ComponentWorkaround for no SCD0 in SSIS Dimension Merge SCD Component?T-SQL Syntax for SCD Type 2Type II SCD with entities that merge over timeFact table linked to Slowly Changing DimensionImplementing SCD Type 2 using Pentaho Kettle (Pentaho Data Integeration 5.2)SCD or late arriving Fact?Is it possible to use system-versioned temporal table in the fact-dimension DWH design?T-SQL Type 2 SCD with BINARY_CHECKSUM on the flyHow to identify SCD Type 1 and Type 2 columns in SQL Server tables
What to do with wrong results in talks?
Will it be accepted, if there is no ''Main Character" stereotype?
What's the purpose of "true" in bash "if sudo true; then"
Failed to fetch jessie backports repository
Was Spock the First Vulcan in Starfleet?
Minimal reference content
Valid Badminton Score?
Why does John Bercow say “unlock” after reading out the results of a vote?
Using parameter substitution on a Bash array
Hide Select Output from T-SQL
Best way to store options for panels
Applicability of Single Responsibility Principle
Ways to speed up user implemented RK4
What is the intuitive meaning of having a linear relationship between the logs of two variables?
Why Were Madagascar and New Zealand Discovered So Late?
What defines a dissertation?
Is there an Impartial Brexit Deal comparison site?
Coordinate position not precise
At which point does a character regain all their Hit Dice?
How to be diplomatic in refusing to write code that breaches the privacy of our users
Tiptoe or tiphoof? Adjusting words to better fit fantasy races
The baby cries all morning
Have I saved too much for retirement so far?
I'm in charge of equipment buying but no one's ever happy with what I choose. How to fix this?
DWH SCD type 2 implementation in SQL Server scd2 and scd1
Issue with inferred member using SSIS Dimension Merge SCD ComponentWorkaround for no SCD0 in SSIS Dimension Merge SCD Component?T-SQL Syntax for SCD Type 2Type II SCD with entities that merge over timeFact table linked to Slowly Changing DimensionImplementing SCD Type 2 using Pentaho Kettle (Pentaho Data Integeration 5.2)SCD or late arriving Fact?Is it possible to use system-versioned temporal table in the fact-dimension DWH design?T-SQL Type 2 SCD with BINARY_CHECKSUM on the flyHow to identify SCD Type 1 and Type 2 columns in SQL Server tables
We are implementing a new dwh solution. I have many dimensions that require slowly changing type 2 attributes. I was considering implementing a combination of Type 2 and Type 1 attributes in my dimension. That is for some dimension attributes, we track history by inserting new rows in the dim table (Type2), for other attributes we will just update the existing row for any changes (Type1)
Questions:
Is this a good practice? is it OK to have a combination of SCD 1 and 2 for the same dim?
Is there any limit on the number of SCD 2 attributes in a dimension? My dimension is pretty wide, like 300 cols, out of which one of the users is requesting for about 150 cols to be tracked by scd type 2. Is it OK to have so many scd2 attributes in a dim? Is there going to be any impact on performance of downstream reporting BI solutions like cubes and dashboards because of this?
In the OLTP system, we maintain an "audit" table to log any updates. Though this is not in a very easily queryable format, we get answers to most of our questions related to changes from this. We don't need much reporting on data changes. Of course there are some important columns like
Statusfor which we definitely need SCD2 but rest of the columns, I am not sure having history for lot of other columns in the DWH adds any value. My question is when we have this audit table in OLTP, how do I decide what attributes need SCD 2 in the DWH?
data-warehouse dimensional-modeling scd scd2
edited Mar 21 at 16:02
marc_s
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
We are implementing a new dwh solution. I have many dimensions that require slowly changing type 2 attributes. I was considering implementing a combination of Type 2 and Type 1 attributes in my dimension. That is for some dimension attributes, we track history by inserting new rows in the dim table (Type2), for other attributes we will just update the existing row for any changes (Type1)
Questions:
Is this a good practice? is it OK to have a combination of SCD 1 and 2 for the same dim?
Is there any limit on the number of SCD 2 attributes in a dimension? My dimension is pretty wide, like 300 cols, out of which one of the users is requesting for about 150 cols to be tracked by scd type 2. Is it OK to have so many scd2 attributes in a dim? Is there going to be any impact on performance of downstream reporting BI solutions like cubes and dashboards because of this?
In the OLTP system, we maintain an "audit" table to log any updates. Though this is not in a very easily queryable format, we get answers to most of our questions related to changes from this. We don't need much reporting on data changes. Of course there are some important columns like
Statusfor which we definitely need SCD2 but rest of the columns, I am not sure having history for lot of other columns in the DWH adds any value. My question is when we have this audit table in OLTP, how do I decide what attributes need SCD 2 in the DWH?
data-warehouse dimensional-modeling scd scd2
edited Mar 21 at 16:02
marc_s
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
We are implementing a new dwh solution. I have many dimensions that require slowly changing type 2 attributes. I was considering implementing a combination of Type 2 and Type 1 attributes in my dimension. That is for some dimension attributes, we track history by inserting new rows in the dim table (Type2), for other attributes we will just update the existing row for any changes (Type1)
Questions:
Is this a good practice? is it OK to have a combination of SCD 1 and 2 for the same dim?
Is there any limit on the number of SCD 2 attributes in a dimension? My dimension is pretty wide, like 300 cols, out of which one of the users is requesting for about 150 cols to be tracked by scd type 2. Is it OK to have so many scd2 attributes in a dim? Is there going to be any impact on performance of downstream reporting BI solutions like cubes and dashboards because of this?
In the OLTP system, we maintain an "audit" table to log any updates. Though this is not in a very easily queryable format, we get answers to most of our questions related to changes from this. We don't need much reporting on data changes. Of course there are some important columns like
Statusfor which we definitely need SCD2 but rest of the columns, I am not sure having history for lot of other columns in the DWH adds any value. My question is when we have this audit table in OLTP, how do I decide what attributes need SCD 2 in the DWH?
data-warehouse dimensional-modeling scd scd2
edited Mar 21 at 16:02
marc_s
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
We are implementing a new dwh solution. I have many dimensions that require slowly changing type 2 attributes. I was considering implementing a combination of Type 2 and Type 1 attributes in my dimension. That is for some dimension attributes, we track history by inserting new rows in the dim table (Type2), for other attributes we will just update the existing row for any changes (Type1)
Questions:
Is this a good practice? is it OK to have a combination of SCD 1 and 2 for the same dim?
Is there any limit on the number of SCD 2 attributes in a dimension? My dimension is pretty wide, like 300 cols, out of which one of the users is requesting for about 150 cols to be tracked by scd type 2. Is it OK to have so many scd2 attributes in a dim? Is there going to be any impact on performance of downstream reporting BI solutions like cubes and dashboards because of this?
In the OLTP system, we maintain an "audit" table to log any updates. Though this is not in a very easily queryable format, we get answers to most of our questions related to changes from this. We don't need much reporting on data changes. Of course there are some important columns like
Statusfor which we definitely need SCD2 but rest of the columns, I am not sure having history for lot of other columns in the DWH adds any value. My question is when we have this audit table in OLTP, how do I decide what attributes need SCD 2 in the DWH?
data-warehouse dimensional-modeling scd scd2
data-warehouse dimensional-modeling scd scd2
edited Mar 21 at 16:02
marc_s
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 16:02
marc_s
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 21 at 16:02
marc_s
583k13011241270
edited Mar 21 at 16:02
marc_s
583k13011241270
edited Mar 21 at 16:02
marc_s
583k13011241270
583k13011241270
asked Mar 21 at 15:23
sirisiri
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 21 at 15:23
sirisiri
1
asked Mar 21 at 15:23
sirisiri
1
1
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
siri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Good practice? Yes. Standard feature of dimensional modelling that is overlooked too often. I've seen dimensions with combinations of SCD0, SCD1 and SCD2, and there's nothing to prevent other SCD-types being used as well.
No limit on columns, but that does seem a little excessive. You probably want to use a "hash" method to detect the SCD2 changes, where you calculate a hash over the SCD2 columns, and use this value to detect if any of the columns have changed.
Sorry, but I don't understand the question about audit logs. Are these logs your data source?
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
add a comment |
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
);
);
siri is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55283841%2fdwh-scd-type-2-implementation-in-sql-server-scd2-and-scd1%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Good practice? Yes. Standard feature of dimensional modelling that is overlooked too often. I've seen dimensions with combinations of SCD0, SCD1 and SCD2, and there's nothing to prevent other SCD-types being used as well.
No limit on columns, but that does seem a little excessive. You probably want to use a "hash" method to detect the SCD2 changes, where you calculate a hash over the SCD2 columns, and use this value to detect if any of the columns have changed.
Sorry, but I don't understand the question about audit logs. Are these logs your data source?
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
add a comment |
Good practice? Yes. Standard feature of dimensional modelling that is overlooked too often. I've seen dimensions with combinations of SCD0, SCD1 and SCD2, and there's nothing to prevent other SCD-types being used as well.
No limit on columns, but that does seem a little excessive. You probably want to use a "hash" method to detect the SCD2 changes, where you calculate a hash over the SCD2 columns, and use this value to detect if any of the columns have changed.
Sorry, but I don't understand the question about audit logs. Are these logs your data source?
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
add a comment |
Good practice? Yes. Standard feature of dimensional modelling that is overlooked too often. I've seen dimensions with combinations of SCD0, SCD1 and SCD2, and there's nothing to prevent other SCD-types being used as well.
No limit on columns, but that does seem a little excessive. You probably want to use a "hash" method to detect the SCD2 changes, where you calculate a hash over the SCD2 columns, and use this value to detect if any of the columns have changed.
Sorry, but I don't understand the question about audit logs. Are these logs your data source?
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
Good practice? Yes. Standard feature of dimensional modelling that is overlooked too often. I've seen dimensions with combinations of SCD0, SCD1 and SCD2, and there's nothing to prevent other SCD-types being used as well.
No limit on columns, but that does seem a little excessive. You probably want to use a "hash" method to detect the SCD2 changes, where you calculate a hash over the SCD2 columns, and use this value to detect if any of the columns have changed.
Sorry, but I don't understand the question about audit logs. Are these logs your data source?
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
answered Mar 21 at 23:13
Ron DunnRon Dunn
1,6111118
1,6111118
add a comment |
add a comment |
siri is a new contributor. Be nice, and check out our Code of Conduct.
siri is a new contributor. Be nice, and check out our Code of Conduct.
siri is a new contributor. Be nice, and check out our Code of Conduct.
siri is a new contributor. Be nice, and check out our Code of Conduct.
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%2f55283841%2fdwh-scd-type-2-implementation-in-sql-server-scd2-and-scd1%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
