Dimensional Model handling a multiple timestamps
0
I am thinking of building a simple data warehouse from a dataset that includes 2 timestamps - clock_start, clock_stop.
A clock can stop and restart multiple times. One of the metrics I need to measure is time taken from the first clock start to last clock stop.
The raw dataset i have creates a new row of data every time a clock stops or restarts so there are multiple rows of data for each event.
I would like the granularity to remain at the lowest possible level so I can see each clock stop and start in the new model.
What is the best way to model this so I get fastest possible performance?
Thank you very much.
sql database-design data-warehouse
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
add a comment |
0
I am thinking of building a simple data warehouse from a dataset that includes 2 timestamps - clock_start, clock_stop.
A clock can stop and restart multiple times. One of the metrics I need to measure is time taken from the first clock start to last clock stop.
The raw dataset i have creates a new row of data every time a clock stops or restarts so there are multiple rows of data for each event.
I would like the granularity to remain at the lowest possible level so I can see each clock stop and start in the new model.
What is the best way to model this so I get fastest possible performance?
Thank you very much.
sql database-design data-warehouse
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
1
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39
add a comment |
0
0
0
I am thinking of building a simple data warehouse from a dataset that includes 2 timestamps - clock_start, clock_stop.
A clock can stop and restart multiple times. One of the metrics I need to measure is time taken from the first clock start to last clock stop.
The raw dataset i have creates a new row of data every time a clock stops or restarts so there are multiple rows of data for each event.
I would like the granularity to remain at the lowest possible level so I can see each clock stop and start in the new model.
What is the best way to model this so I get fastest possible performance?
Thank you very much.
sql database-design data-warehouse
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
I am thinking of building a simple data warehouse from a dataset that includes 2 timestamps - clock_start, clock_stop.
A clock can stop and restart multiple times. One of the metrics I need to measure is time taken from the first clock start to last clock stop.
The raw dataset i have creates a new row of data every time a clock stops or restarts so there are multiple rows of data for each event.
I would like the granularity to remain at the lowest possible level so I can see each clock stop and start in the new model.
What is the best way to model this so I get fastest possible performance?
Thank you very much.
sql database-design data-warehouse
sql database-design data-warehouse
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
asked Nov 13 '18 at 0:42
parakkrama pereraparakkrama perera
84
84
1
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39
add a comment |
1
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39
1
1
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39
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
});
}
});
draft saved
draft discarded
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%2f53272149%2fdimensional-model-handling-a-multiple-timestamps%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
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
draft saved
draft discarded
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%2f53272149%2fdimensional-model-handling-a-multiple-timestamps%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
1
"One of the metrics I need to measure is time taken from the first clock start to last clock stop". A row per start/stop with a metric storing the start stop time. Do multiple start/stops need to be correlated? In other words are you only interested in matching pairs of start/stpo or do you need to analyse a whole string of related start/stops?
– Nick.McDermaid
Nov 13 '18 at 0:53
I only need the metric for the first start to last stop. But I should be able to see all the different clocks for one event somehow. Do you suggest I calculate it before loading it to the presentation layet? Thanks Nick+
– parakkrama perera
Nov 13 '18 at 1:34
You're going to have to post some sample data. I'm trying to understand if a fact row containing a start and stop time is enough information for your analysis or not. Is "First start to last stop" represented by two rows or many rows in your source data? If you are building a DW then you take every opportunity to improve simplicity and performance by pre-calculating stuff.
– Nick.McDermaid
Nov 13 '18 at 2:16
Hi Nick first start and the last stop are enough for to do the calculation but I need the other clocks for a detailed report I need to develop. I did ask a solution architect at work and he suggested this - If you want all the stop/starts, you need an separate ( but logically related in a parent/child manner) FACT table with the granular key from the major fact table on as many rows as required in the new fact table If you do this, you can still store the earliest start and latest stop at the granular level on the major fact table to save on queries that need only this information
– parakkrama perera
Nov 13 '18 at 3:33
Yes that's a good approach. Create a fact that has start, stop, duration in it which is generated from detail data. Then you can drill through from the high level to the detail data base on the start/stop time (and whatever other codes make it unique)
– Nick.McDermaid
Nov 13 '18 at 3:39