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.










share|improve this question


















  • 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 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 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 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 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 at 3:39
















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.










share|improve this question


















  • 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 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 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 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 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 at 3:39














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.










share|improve this question













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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 13 at 0:42









parakkrama 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 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 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 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 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 at 3:39














  • 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 at 3:39

















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


















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






























active

oldest

votes













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














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





















































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







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)