Friday, May 23, 2008

Gas, Water and Electricity Registration

Friday, May 23, 2008 Posted by Andre Broers , , , , 2 comments
Over here in Holland the utilities are getting more expensive everyday. Time for me to make a small registration tool to get some insigth information in the consumption of the resources. I want the resources (facts) to layout againsts dates and times. So I need two dimensions: date and time. I also need a fact table in which I register the usage of the resources every hour. Because it is not possible to get a sample of the meters every hour, I created a procedure which splits the difference between the previous and the current resource usage over the hours that are between. I know it isn't the excact resource usage, but the more samples I have the more accurate it will get. The nice thing about splitting the usage over the hours is that querying the results gets very easy.

Over here in Holland we have another 'problem'. We have peak and dew hours for the electricity. Also the certain holidays and the weekends are dew prices.

Lets start with creating the schema:

create user gwe identified by gwe;
grant connect,resources to gwe;
connect gwe/gwe

After this, create the dat dimension as showed in the previous blog.

Now the time dimension:

[sourcecode language="java"]

create table tim
(
timkey number not null,
timhour number(2) not null,
timelekhoog varchar2(1) not null,
constraint tim_pkey primary key (timkey)
)
/

drop sequence timkey_seq
/

create sequence timkey_seq
start with 1
increment by 1
nomaxvalue
/

create or replace trigger timkey_trigger
before insert on tim
for each row
begin
select timkey_seq.nextval into :new.timkey from dual;
end;
/

create unique index timhour_idx1 on tim (timhour)
/

insert into tim (timhour,timelekhoog) values (0,'Y');
insert into tim (timhour,timelekhoog) values (1,'Y');
insert into tim (timhour,timelekhoog) values (2,'Y');
insert into tim (timhour,timelekhoog) values (3,'Y');
insert into tim (timhour,timelekhoog) values (4,'Y');
insert into tim (timhour,timelekhoog) values (5,'Y');
insert into tim (timhour,timelekhoog) values (6,'Y');
insert into tim (timhour,timelekhoog) values (7,'N');
insert into tim (timhour,timelekhoog) values (8,'N');
insert into tim (timhour,timelekhoog) values (9,'N');
insert into tim (timhour,timelekhoog) values (10,'N');
insert into tim (timhour,timelekhoog) values (11,'N');
insert into tim (timhour,timelekhoog) values (12,'N');
insert into tim (timhour,timelekhoog) values (13,'N');
insert into tim (timhour,timelekhoog) values (14,'N');
insert into tim (timhour,timelekhoog) values (15,'N');
insert into tim (timhour,timelekhoog) values (16,'N');
insert into tim (timhour,timelekhoog) values (17,'N');
insert into tim (timhour,timelekhoog) values (18,'N');
insert into tim (timhour,timelekhoog) values (19,'N');
insert into tim (timhour,timelekhoog) values (20,'N');
insert into tim (timhour,timelekhoog) values (21,'N');
insert into tim (timhour,timelekhoog) values (22,'N');
insert into tim (timhour,timelekhoog) values (23,'Y');

commit
/
[/sourcecode]

Now the fact table and the foreignkeys:

[sourcecode language="java"]

create table gwe
(
gwekey number not null,
gwedat number not null,
gwetim number not null,
gweelekhoog number(10,5) not null,
gweeleklaag number(10,5) not null,
gwegas number(10,5) not null,
gwewater number(10,5) not null,
constraint gwe_pkey primary key (gwekey),
constraint gwedat_fkey foreign key (gwedat) references dat (datkey),
constraint gwetim_fkey foreign key (gwetim) references tim (timkey)
)
/

drop sequence gwekey_seq
/

create sequence gwekey_seq
start with 1
increment by 1
nomaxvalue
/

create or replace trigger gwekey_trigger
before insert on gwe
for each row
begin
select gwekey_seq.nextval into :new.gwekey from dual;
end;
/

declare l_datkey number;
begin
select datkey into l_datkey from dat where datdate=trunc(sysdate)-1;
insert into gwe (
gwedat
,gwetim
,gweelekhoog
,gweeleklaag
,gwegas
,gwewater
) values (
l_datkey
,1
,0
,0
,0
,0
);
end;
/

commit;
[/sourcecode]

The fact table is initialized with 0 values at the date yesterday.

Now we can use the following stored procedure to add meter measures.

[sourcecode language="java"]
create or replace procedure tussenstand( p_datumin date
,p_elekhoog number
,p_eleklaag number
,p_gas number
,p_water number
) as
l_prevdatum date;
l_diffelekhoog number(10,5);
l_diffeleklaag number(10,5);
l_diffgas number(10,5);
l_diffwater number(10,5);
l_inselekhoog number(10,5);
l_inseleklaag number(10,5);
l_datum date;
l_prevkey number;
l_aantalelekhoog number;
l_aantaleleklaag number;
l_aantalgas number;
l_aantalwater number;
l_elekhoog varchar2(1);
l_datkey number;
l_timkey number;
l_weekend varchar2(1);
l_feestdag varchar2(1);
begin
select max(gwekey)
into l_prevkey
from gwe
;
select p_elekhoog-sum(gweelekhoog)
,p_eleklaag-sum(gweeleklaag)
,p_gas-sum(gwegas)
,p_water-sum(gwewater)
into l_diffelekhoog
,l_diffeleklaag
,l_diffgas
,l_diffwater
from gwe
;
select datdate + (timhour/24)
into l_prevdatum
from gwe,dat,tim
where gwekey=l_prevkey
and gwedat=datkey
and gwetim=timkey
;
l_datum := l_prevdatum+(1/24);
l_aantalelekhoog := 0;
l_aantaleleklaag := 0;
l_aantalgas := 0;
l_aantalwater := 0;

while (l_datum<=p_datumin)
loop
select datweekend, datholiday
into l_weekend, l_feestdag
from dat
where datdate = trunc(l_datum)
;
select timelekhoog
into l_elekhoog
from tim
where timhour=to_number(to_char(l_datum,'HH24'))
;
if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
l_aantaleleklaag := l_aantaleleklaag + 1;
else
l_aantalelekhoog := l_aantalelekhoog + 1;
end if;
l_aantalgas := l_aantalgas + 1;
l_aantalwater := l_aantalwater + 1;

l_datum := l_datum + (1/24);
end loop;

if l_aantaleleklaag=0 then l_aantaleleklaag:=1; end if;
if l_aantalelekhoog=0 then l_aantalelekhoog:=1; end if;
if l_aantalwater=0 then l_aantalwater:=1; end if;
if l_aantalgas=0 then l_aantalgas:=1; end if;

l_diffgas := l_diffgas / l_aantalgas;
l_diffwater := l_diffwater / l_aantalwater;
l_diffeleklaag := l_diffeleklaag / l_aantaleleklaag;
l_diffelekhoog := l_diffelekhoog / l_aantalelekhoog;

l_datum := l_prevdatum + (1/24);

while (l_datum <= p_datumin)
loop
select datkey
,datweekend
,datholiday
into l_datkey
,l_weekend
,l_feestdag
from dat
where datdate = trunc(l_datum)
;
select timkey
,timelekhoog
into l_timkey
,l_elekhoog
from tim
where timhour=to_number(to_char(l_datum,'HH24'))
;
if (l_elekhoog='N' or l_weekend='Y' or l_feestdag='Y') then
l_inseleklaag := l_diffeleklaag;
l_inselekhoog := 0;
else
l_inselekhoog := l_diffelekhoog;
l_inseleklaag := 0;
end if;
insert into gwe (
gwedat
,gwetim
,gweelekhoog
,gweeleklaag
,gwegas
,gwewater
) values (
l_datkey
,l_timkey
,l_inselekhoog
,l_inseleklaag
,l_diffgas
,l_diffwater
);
l_datum := l_datum + (1/24);
end loop;
end;
/
[/sourcecode]

After adding some measures, we can use the following views in our Business Intelligence tool to create nice graphs:

[sourcecode language="java"]

create or replace view elek as
select null link, datyyyyww label, sum(gweelekhoog+gweeleklaag) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view gas as
select null link, datyyyyww label, sum(gwegas) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/
create or replace view water as
select null link, datyyyyww label, sum(gwewater) value
from "GWE"."GWE", "GWE"."DAT"
where gwedat=datkey and datwwcum >
( select max(datwwcum)-30
from gwe.dat, gwe.gwe where datkey=gwedat)
group by datwwcum,datyyyyww
order by datwwcum
/

[/sourcecode]

2 comments:

  1. Lucien Piccard is about the a lot of fake rolex watches acclimatized watchmakers in the accepted society, operating out of any amazing host to Switzerland which is alleged your arresting abode affiliated with actual best superior watch, Lucien Piccard is absolutely cloudburst bodies over Eighty seven decades anytime back the time associated with 1923 and actualization been acclaimed apropos abundant assortment of finest akin of superior watches. Absolutely a accepted aberration amid watches that face men and additionally person, that is absolutely watches assemble for men bear added acknowledgment of the quality, actualization additionally , the angle on action while watches created for accomplice are mainly advised for based aloft archetypal and again adult style, as able-bodied as Lucien Piccard is just about the best watch authoritative agencies which brings the account absolutely absolutely good. You will ascertain lots of watches of these humans for females in altered areas and aswell allotment these guys varies according to replica watches clandestine sample bartering another but you are august based aloft several arrangement apropos prerequisites, as able-bodied as getting ideal with altered marriage gowns likewise, a wrist watch of which actualization complicated in the anniversary isn't displayed artlessly by about any alive harder wife at work, don't an breezy watch is active apropos abstracts architecture functions, which agency that Lucien Piccard includes developed a ample amount for casework as able-bodied as anatomy ofneeds. Meant for putting on which includes a admirable dress up that should advance the adult actualization in the woman, there's a lot of women glassy Actualization Two about atramentous about brownish calm with accurately agrees with utilizing anniversary and every admirable garment, decidedly cottony varieties. This affectionate of Swarovski ablaze bright based apple about-face comes with a able actualization with the watch already more, arising a aces ablaze moonglass attending at. Straps tend to be fabricated aural forth with white or maybe red leathers with all-around ablaze about-face to flawlessly bout up with every admirable arm and aswell add added attraction to be able to breitling replica the country's breeding in some degree. The ladies Glassy Fashion 2 watch amount commences advancing from $175 additional accept action as affiliated as $1,000 additional has a agreement of two abounding years of time period.

    ReplyDelete