need help

[#1]

i have a table with data like

ServerName , Timestamp, Service,Status

abcd,25/07/2015 10:12:45 AM,service1,stopped

abcd,25/07/2015 10:12:45 AM,service2,running

abcd,25/07/2015 10:12:47 AM,service3,running

abcd,25/07/2015 10:12:48 AM,service4,running

efgh,25/07/2015 10:12:55 AM,service1,running

efgh,25/07/2015 10:12:55 AM,service2,running

efgh,25/07/2015 10:12:56 AM,service3,stopped

efgh,25/07/2015 10:12:57 AM,service4,running

Now my requirement is if the Service service1 is not running then i want to add a new column as OVERALLSTATUS in this table like below

ServerName , Timestamp, Service,Status,OVERALLSTATUS

abcd,25/07/2015 10:12:45 AM,service1,stopped,0

abcd,25/07/2015 10:12:45 AM,service2,running,0

abcd,25/07/2015 10:12:47 AM,service3,running,0

abcd,25/07/2015 10:12:48 AM,service4,running,0

efgh,25/07/2015 10:12:55 AM,service1,running,1

efgh,25/07/2015 10:12:55 AM,service2,running,1

efgh,25/07/2015 10:12:56 AM,service3,stopped,1

efgh,25/07/2015 10:12:57 AM,service4,running,1

basically if service1 is running then OVERALLSTATUS will be 1 for all the other services for the server and if its not running then OVERALLSTATUS should be 0.

Please suggest a way to do in load script as well as data will be huge

[jonathandienst]

I would not expect that to be a problem. The size would be limited only by your system memory -- and it is much faster than trying to sort a very large data set.

>>In this case equal to the no of fact table records.

I did not notice that the time stamps differ for each service. What granularity can we apply to the timestamps?

If its daily, then truncate the time stamp to the date portion only:

MapStatus:

Mapping LOAD ServerName & '-' & Date(Floor(Timestamp)),

  Status

FROM ....

WHERE Service = 'service1';

LOAD ServerName,

  Timestamp,

  Service,

  Status,

  ApplyMap('MapStatus', ServerName & '-' & Date(Floor(Timestamp)), 0) As OVERALLSTATUS

FROM ...;

Now the mapping will have one record per server per day...

[#3]

i have currently 5 million records and it will grow exponentially .. memory is 64 gb .. will that be fine

[#4]

hi this is sample in actual data every 10 mins we wil be getting the data for servers with same set of services status.