Kategori arşivi: Sql

çoklu bağlantılı platformlar tavsiye (multisource universe recommendation)

Sap Business Objects bir universe ün (unx) birden fazla bağlantı ile oluşturulmasına (kullanılabilmesine) olanak sağlar. Bunun için BI Platformda Data Federation servisinin oluşturulması gerekir. Çoklu bağlanıtılı universe özelliği universe ün farklı veritabanlarından aldığı veriyi Data Federation servisi ile birleştirerek (veritabanı gibi davranarak) raporlara sonuç verisini sağlamasıyla olur. Çoklu bağlantılı universe oluşturulurken bir kere çoklu kaynaklı olduğu belirlenir sonradan tek bağlantılı universe e çevrilemez.

Kişisel tavsiyem Çoklu Bağlantılı (MultiSource) universe özelliğinin zorunlu olunmadıkça kullanılmaması yönünde. Çünkü çoklu bağlantılı platformlar farklı veri kaynaklarından alınan veriyi veritabanı gibi (BI platformun veritabanı görevi yapması performansı çok negatif etkileyen bir faktör, hem işlemci hem de bellek kullanımı açısından) davranarak birleştirmesi gerekir. Özellikle farklı kaynaklardaki objelerin data foundation da join lenmesi durumunda.

Çoklu bağlantılı universe ler yerine veriyi Veri Entegrasyonu (Data Integration), ETL (Extract Transform Load) Araçları ile tek veritabanında birleştirmek ve tek bağlantılı universe ile bu veritabanından raporlamak daha doğru bir çözüm olacaktır. Veri Entegrasyonu araçları farklı veritabanı yönetim sistemlerine bağlanıp veri aktarımı yapabilmektedir. Bu sayede BI Platforma veri tabanı yükü bindirilmemiş olur ve sorgulama dilinin en hızlı çalıştığı ortamda veri alınarak performanslı raporlama yapılmış olur.

Not 1: tecrübelerimde çoklu kaynaklı universe ler ile ilgili çeşitli hatalarla (bug) karşılaştığımı söyleyebilirim.

Not 2: çoklu bağlantılı universe ler ile çalışırken tekli bağlantılı universe lere göre daha yavaş çalışırlar sebebi MultiSource universe lerin üzerinde geliştirme yapılırken işlemlerin yerel makinada değil sunucuda (bi platform) yapılıyor olmasıdır.

Not 2 Kaynak: IDT client tool slows dramatically in business layer

oracle sql ile bir satırı sütundaki değer kadar çoklamak

İhtiyaç:

t tablosu
———

s1, s2
1, ‘a’
2, ‘a,b,c’
3, ‘a,b’
4, ‘c,b’

Tablosunu yukarıdaki durumdan aşağıdaki duruma çevirmek. (s2 sütununda virgül ile ayrılmış değerleri satırlara çevirmek. Bir diğer kriter de bu tablonun milyon satır olduğunda da performanslı çalışabilmesi. Bir satırın kaça parçalanacağı (kaç satıra çevirileceği) virgüller sayılarak hesaplanabilir.)

sorgu sonucu amaçlanan
———————-

s1, s2
1, ‘a’
2, ‘a’
2, ‘b’
2, ‘c’
3, ‘a’
3, ‘b’
4, ‘c’
4, ‘b’

Oracle özyinelemeli (recursive) sql sorgu ifadesi ile çözüm:

WITH split (s1, s2, s2_count, split_level) AS (

    select t1.s1, t1.s2, regexp_count(t1.s2,',') as s2_count, 1 as split_level
    from t t1

    union all

    select split.s1, split.s2, split.s2_count, split.split_level+1 as split_level
    from split
    where split_level < s2_count

)

SELECT s1, s2, s2_count, split_level,

case when s2_count > 1 and s2_count <> split_level then
      substr( s2, instr(s2,',',1,split_level), (instr(s2,',',1,split_level+1)-instr(s2,',',1,split_level)-2) )
     when s2_count > 1 and re_cnt = lvl then
       substr( s2, instr(s2,',',1,split_level), (length(s2)-instr(s2,',',1,split_level)+1) )
     else s2
end as new_s2

FROM split
ORDER BY 1,2,3,4,5

Kaynak: https://stackoverflow.com/questions/41589163/oracle-split-single-row-into-multiple-rows-based-on-column-value

tablo satır sayıları arşivi oracle plsql

Periyodik olarak tablolardaki satır sayılarını saklamak için kullanılabilecek Oracle stored procedure.


create table sample_schema.table_counts (
owner_schema varchar2(30),
table_name varchar2(30),
row_count number
);

create or replace procedure sample_schema.table_counts_calculator(p_schema varchar2)
is

v_user varchar2(30);
v_schema varchar2(30);
v_now date;
v_cnt number;

cursor c_tables(cp_schema varchar2)
is
select owner, table_name
from all_tables
where owner = cp_schema
order by 1;

begin

select user into v_user from dual;

v_schema := upper(p_schema);

select sysdate into v_now from dual;

for tbl in c_tables(v_schema)
loop

    execute immediate '
    select /*+parallel(8)*/ count(*) cnt
    from '||tbl.owner||'.'||tbl.table_name||'
    ' into v_cnt;

    execute immediate '
    insert into '||v_user||'.table_counts values ( '''||tbl.owner||''','''||tbl.table_name||''', '||v_cnt||' )
    ';

end loop;

commit;

end;
/

Türkiye gps il koordinatları (enlem ve boylam) oracle sql

Mobil uygulamanın Türkiye illerine göre kullanım istatistiğinin dashboard’unu hazırlarken kullandığımız il gps koordinat bilgileri ve bir koordinatın ile çevrilmesini sağlayan fonksiyon. Oracle veritabanı üzerinde.

Kodu dosya olarak indir: Türkiye Gps İl_Koordinat Sql


create table sample_schema.t_city_gps (
  il_id number, -- plaka kodu,
  il_adi varchar2(255),
  lat number, -- il merkezi enlem,
  lng number, -- il merkezi boylam,
  northeast_lat number, -- kuzeydoğu sınırı enlem
  northeast_lng number, -- kuzeydoğu sınırı boylam
  southwest_lat number, -- güneybatı sınırı enlem
  southwest_lng number, -- güneybatı sınırı boylam
);

insert into sample_schema.t_city_gps values (1,  'ADANA', 37.00000000, 35.32133330, 37.07200400, 35.46199500, 36.93552300, 35.17470600);
insert into sample_schema.t_city_gps values (2,  'ADIYAMAN', 37.76416670, 38.27616670, 37.82566700, 38.33546500, 37.71708600, 38.18818800);
insert into sample_schema.t_city_gps values (3,  'AFYONKARAHİSAR', 38.76376000, 30.54034000, 38.80210500, 30.61116700, 38.71428900, 30.44232000);
insert into sample_schema.t_city_gps values (4,  'AĞRI', 39.72166670, 43.05666670, 39.74860500, 43.08524100, 39.68814400, 43.00177800);
insert into sample_schema.t_city_gps values (5,  'AMASYA', 40.65000000, 35.83333330, 40.67283400, 35.85632100, 40.63691100, 35.78909100);
insert into sample_schema.t_city_gps values (6,  'ANKARA', 39.92077000, 32.85411000, 40.10098100, 33.02486600, 39.72282100, 32.49909700);
insert into sample_schema.t_city_gps values (7,  'ANTALYA', 36.88414000, 30.70563000, 36.97517800, 30.84095300, 36.78586600, 30.51609500);
insert into sample_schema.t_city_gps values (8,  'ARTVİN', 41.18333330, 41.81666670, 41.20707800, 41.85479900, 41.15541500, 41.77736100);
insert into sample_schema.t_city_gps values (9,  'AYDIN', 37.84440000, 27.84580000, 37.87099700, 27.88535500, 37.81957300, 27.79052200);
insert into sample_schema.t_city_gps values (10, 'BALIKESİR', 39.64836900, 27.88261000, 39.69366600, 27.95241000, 39.61241100, 27.83831300);
insert into sample_schema.t_city_gps values (11, 'BİLECİK', 40.15013100, 29.98306100, 40.19761700, 30.01359200, 40.10624300, 29.95665700);
insert into sample_schema.t_city_gps values (12, 'BİNGÖL', 38.88534900, 40.49829100, 38.89796300, 40.52592900, 38.87298700, 40.47725800);
insert into sample_schema.t_city_gps values (13, 'BİTLİS', 38.40000000, 42.11666670, 38.45363800, 42.14887900, 38.38531200, 42.09384000);
insert into sample_schema.t_city_gps values (14, 'BOLU', 40.73947900, 31.61156100, 40.75800400, 31.65841800, 40.71680200, 31.57332400);
insert into sample_schema.t_city_gps values (15, 'BURDUR', 37.72690900, 30.28887600, 37.76375000, 30.33666200, 37.69143300, 30.19175700);
insert into sample_schema.t_city_gps values (16, 'BURSA', 40.18257000, 29.06687000, 40.29719400, 29.21123700, 40.16299700, 28.85963000);
insert into sample_schema.t_city_gps values (17, 'ÇANAKKALE', 40.15531200, 26.41416000, 40.19946200, 26.45710600, 40.08808600, 26.37802600);
insert into sample_schema.t_city_gps values (18, 'ÇANKIRI', 40.60000000, 33.61666670, 40.65515900, 33.68485300, 40.55770900, 33.59657100);
insert into sample_schema.t_city_gps values (19, 'ÇORUM', 40.55055560, 34.95555560, 40.60931700, 35.00643800, 40.49521400, 34.88763400);
insert into sample_schema.t_city_gps values (20, 'DENİZLİ', 37.77652000, 29.08639000, 37.84736600, 29.18891500, 37.71617100, 28.98137300);
insert into sample_schema.t_city_gps values (21, 'DİYARBAKIR', 37.91441000, 40.23062900, 37.97617400, 40.24580300, 37.87962400, 40.10615900);
insert into sample_schema.t_city_gps values (22, 'EDİRNE', 41.66666670, 26.56666670, 41.69793900, 26.60309300, 41.64993500, 26.51405900);
insert into sample_schema.t_city_gps values (23, 'ELAZIĞ', 38.68096900, 39.22639800, 38.71356800, 39.27731400, 38.63056400, 39.13340500);
insert into sample_schema.t_city_gps values (24, 'ERZİNCAN', 39.75000000, 39.50000000, 39.76916400, 39.53174300, 39.72988400, 39.46025300);
insert into sample_schema.t_city_gps values (25, 'ERZURUM', 39.90431890, 41.26788530, 39.95453300, 41.30359400, 39.86671500, 41.17709300);
insert into sample_schema.t_city_gps values (26, 'ESKİŞEHİR', 39.78430200, 30.51922000, 39.81679300, 30.66769000, 39.72475200, 30.41408400);
insert into sample_schema.t_city_gps values (27, 'GAZİANTEP', 37.06622000, 37.38332000, 37.11372200, 37.46695800, 37.00594700, 37.30370900);
insert into sample_schema.t_city_gps values (28, 'GİRESUN', 40.91281100, 38.38953000, 40.92799500, 38.45004400, 40.87787600, 38.30901800);
insert into sample_schema.t_city_gps values (29, 'GÜMÜŞHANE', 40.46027780, 39.48138890, 40.47135900, 39.52064100, 40.43020100, 39.44691400);
insert into sample_schema.t_city_gps values (30, 'HAKKARİ', 37.58333330, 43.73333330, 37.60165600, 43.76225700, 37.55154900, 43.69809000);
insert into sample_schema.t_city_gps values (31, 'HATAY', 36.40184880, 36.34980970, 37.01492100, 36.69753500, 35.81269200, 35.77937500);
insert into sample_schema.t_city_gps values (32, 'ISPARTA', 37.76666670, 30.55000000, 37.83071900, 30.60919700, 37.73988000, 30.50367300);
insert into sample_schema.t_city_gps values (33, 'MERSİN(İÇEL)', 36.80000000, 34.63333330, 36.87827200, 34.71670200, 36.69950300, 34.45765500);
insert into sample_schema.t_city_gps values (34, 'İSTANBUL', 41.00527000, 28.97696000, 41.32078600, 29.45645600, 40.80275000, 27.97130700);
insert into sample_schema.t_city_gps values (35, 'İZMİR', 38.41885000, 27.12872000, 38.50677300, 27.30441500, 38.34315700, 27.01719200);
insert into sample_schema.t_city_gps values (36, 'KARS', 40.59267000, 43.07783100, 40.62484300, 43.13390000, 40.57748400, 43.05643200);
insert into sample_schema.t_city_gps values (37, 'KASTAMONU', 41.38871000, 33.78273000, 41.43605900, 33.81498900, 41.35066800, 33.75290100);
insert into sample_schema.t_city_gps values (38, 'KAYSERİ', 38.73333330, 35.48333330, 38.81879700, 35.62767500, 38.62200200, 35.30432400);
insert into sample_schema.t_city_gps values (39, 'KIRKLARELİ', 41.73333330, 27.21666670, 41.76005400, 27.24222000, 41.71061500, 27.18284300);
insert into sample_schema.t_city_gps values (40, 'KIRŞEHİR', 39.15000000, 34.16666670, 39.20393900, 34.21774300, 39.08129300, 34.12553200);
insert into sample_schema.t_city_gps values (41, 'KOCAELİ', 40.85327040, 29.88152030, 41.20976000, 30.35968400, 40.53278200, 29.33346200);
insert into sample_schema.t_city_gps values (42, 'KONYA', 37.86666670, 32.48333330, 38.05063000, 32.68011600, 37.71903100, 32.33873700);
insert into sample_schema.t_city_gps values (43, 'KÜTAHYA', 39.41666670, 29.98333330, 39.44271800, 30.06650300, 39.37947300, 29.91919900);
insert into sample_schema.t_city_gps values (44, 'MALATYA', 38.35519000, 38.30946000, 38.39755900, 38.45324400, 38.30512300, 38.23262200);
insert into sample_schema.t_city_gps values (45, 'MANİSA', 38.61909900, 27.42892100, 38.64228900, 27.48797600, 38.60237600, 27.32608600);
insert into sample_schema.t_city_gps values (46, 'KAHRAMANMARAŞ', 37.58333330, 36.93333330, 37.60526100, 36.99767800, 37.53030900, 36.82970300);
insert into sample_schema.t_city_gps values (47, 'MARDİN', 37.31223610, 40.73511200, 37.33055400, 40.76448600, 37.30510900, 40.71697100);
insert into sample_schema.t_city_gps values (48, 'MUĞLA', 37.21527780, 28.36361110, 37.22581000, 28.41449800, 37.19465100, 28.34127900);
insert into sample_schema.t_city_gps values (49, 'MUŞ', 38.74329260, 41.50648230, 38.78350300, 41.54062500, 38.71822400, 41.47230600);
insert into sample_schema.t_city_gps values (50, 'NEVŞEHİR', 38.62442000, 34.72396900, 38.68410700, 34.76475900, 38.58904700, 34.66975200);
insert into sample_schema.t_city_gps values (51, 'NİĞDE', 37.96666670, 34.68333330, 37.97991300, 34.69158500, 37.95699200, 34.66190600);
insert into sample_schema.t_city_gps values (52, 'ORDU', 40.98333330, 37.88333330, 41.02034000, 37.97897600, 40.93163100, 37.82599200);
insert into sample_schema.t_city_gps values (53, 'RİZE', 41.02005000, 40.52344900, 41.04649200, 40.58680200, 41.01078200, 40.47857800);
insert into sample_schema.t_city_gps values (54, 'SAKARYA', 40.75687930, 30.37813800, 40.82551800, 30.43983200, 40.68619900, 30.32213800);
insert into sample_schema.t_city_gps values (55, 'SAMSUN', 41.29278200, 36.33128000, 41.34413400, 36.38120500, 41.23500200, 36.24951900);
insert into sample_schema.t_city_gps values (56, 'SİİRT', 37.94429000, 41.93288000, 37.95594000, 41.96365800, 37.90967100, 41.87254700);
insert into sample_schema.t_city_gps values (57, 'SİNOP', 42.02642220, 35.15507450, 42.03213500, 35.19494700, 42.01267500, 35.11595700);
insert into sample_schema.t_city_gps values (58, 'SİVAS', 39.74766200, 37.01787900, 39.78366900, 37.06167300, 39.69902400, 36.91985800);
insert into sample_schema.t_city_gps values (59, 'TEKİRDAĞ', 40.98333330, 27.51666670, 40.99672300, 27.60025200, 40.93770400, 27.46899300);
insert into sample_schema.t_city_gps values (60, 'TOKAT', 40.31666670, 36.55000000, 40.35893200, 36.59321900, 40.26786800, 36.46966200);
insert into sample_schema.t_city_gps values (61, 'TRABZON', 41.00000000, 39.73333330, 41.01237000, 39.82051000, 40.95573600, 39.65240700);
insert into sample_schema.t_city_gps values (62, 'TUNCELİ', 39.10798680, 39.54016720, 39.11347700, 39.55185000, 39.09453400, 39.53001500);
insert into sample_schema.t_city_gps values (63, 'ŞANLIURFA', 37.15000000, 38.80000000, 37.18963900, 38.84087600, 37.09827400, 38.76786800);
insert into sample_schema.t_city_gps values (64, 'UŞAK', 38.68230100, 29.40819000, 38.71588000, 29.46983800, 38.63067900, 29.35593600);
insert into sample_schema.t_city_gps values (65, 'VAN', 38.49416670, 43.38000000, 38.58031400, 43.45535600, 38.42188000, 43.26952400);
insert into sample_schema.t_city_gps values (66, 'YOZGAT', 39.82000000, 34.80444440, 39.83842300, 34.85823600, 39.79896800, 34.77109400);
insert into sample_schema.t_city_gps values (67, 'ZONGULDAK', 41.45640900, 31.79873100, 41.48078400, 31.87442500, 41.42529600, 31.75705200);
insert into sample_schema.t_city_gps values (68, 'AKSARAY', 38.36869000, 34.03698000, 38.44672400, 34.08869800, 38.29701200, 33.93251500);
insert into sample_schema.t_city_gps values (69, 'BAYBURT', 40.25516900, 40.22488000, 40.27263600, 40.23826100, 40.24288100, 40.20876200);
insert into sample_schema.t_city_gps values (70, 'KARAMAN', 37.17593000, 33.22874800, 37.22937300, 33.33424400, 37.14825700, 33.16600300);
insert into sample_schema.t_city_gps values (71, 'KIRIKKALE', 39.84682100, 33.51525100, 39.87284300, 33.59796800, 39.81380200, 33.46828900);
insert into sample_schema.t_city_gps values (72, 'BATMAN', 37.88116800, 41.13509000, 37.94727400, 41.17603000, 37.85657000, 41.08059100);
insert into sample_schema.t_city_gps values (73, 'ŞIRNAK', 37.51638890, 42.46111110, 37.53357200, 42.47112700, 37.50009300, 42.44463100);
insert into sample_schema.t_city_gps values (74, 'BARTIN', 41.63444440, 32.33750000, 41.65654200, 32.37105000, 41.61351300, 32.29745700);
insert into sample_schema.t_city_gps values (75, 'ARDAHAN', 41.11048100, 42.70217100, 41.12146700, 42.71712900, 41.09891000, 42.68641800);
insert into sample_schema.t_city_gps values (76, 'IĞDIR', 39.91666670, 44.03333330, 39.95016300, 44.09282500, 39.88410900, 43.98072900);
insert into sample_schema.t_city_gps values (77, 'YALOVA', 40.65000000, 29.26666670, 40.66806000, 29.31653600, 40.63413800, 29.21023300);
insert into sample_schema.t_city_gps values (78, 'KARABÜK', 41.20000000, 32.63333330, 41.23812900, 32.67551800, 41.16301400, 32.59258200);
insert into sample_schema.t_city_gps values (79, 'KİLİS', 36.71839900, 37.12122000, 36.74781900, 37.15954100, 36.70267300, 37.08518300);
insert into sample_schema.t_city_gps values (80, 'OSMANİYE', 37.06805000, 36.26158900, 37.10406100, 36.28794100, 37.04694000, 36.20803400);
insert into sample_schema.t_city_gps values (81, 'DÜZCE', 40.84384900, 31.15654000, 40.89176200, 31.19580700, 40.82471700, 31.10666200);

commit;

-- illerin dikdörtgen olarak düşünüldüğündeki fonksiyon

create or replace function sample_schema.f_gps_to_city(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where p_lat between southwest_lat and northeast_lat
and p_lng between southwest_lng and northeast_lng;

return v_il_id;

exception when others then return 0;

end;
/

-- koordinatın il merkezlerine olan yakınlığına göre ile çevrilmesi

create or replace function sample_schema.f_gps_to_city_2(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) ) = (
  select min(  sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) )  )
  from sample_schema.t_city_gps
);

return v_il_id;

exception when others then return 0;

end;
/

Kaynak: Türkiye’nin il ilçe koordinatları (enlem ve boylamları) MySql sql

sap bo webi raporlama tecrübesi

Örneğin:
M müşteri tablosu olsun ve 500 bin kayıt içersin,
I işlem tablosu olsun, toplamda 100 milyon kayıt içersin ve A işlem kodundan 2 milyon kayıt içersin,
U universe’ü de bu tabloları içersin ve 1 milyon satır sayısı limiti olsun,
amacımız da A işlemini yapan müşteri numarası listesini almak olsun.

Bu durumda rapor sorgu panelinde Müşteri No objesi sonuç kısmında, İşlem Kodu (=’A’) objesini de filtrede kullandığımızda aşağıdaki gibi bir sorgu oluşturulur.

select m.musterino
from musteri m,
islem i
where m.musterino = i.musterino
and i.islemkodu = 'A'

Bu sorgu sonucu 2 milyon satır dönmesi gerekir ama universe satır limiti aşıldığı için 1 milyon sonuç döner ve webi tarafından parçalı sonuç (partial results) uyarısı verilir.

Mantıksal olarak (teknik olmayan bir şekilde) bakıldığında maksimum 500 bin müşteri numarası gelebilir ve satır limiti aşılmaz bir durum beklenir. Basit bakış açısıyla bu durum bug (hata) olarak tanımlanır; fakat teknik bakış açısıyla ilgili objeler ile doğru sorgu üretilmiş olur.

Böyle bir sonuç ile karşılaşmamak için de rapor panelinin sonuç kısmına Müşteri Adedi gibi gruplamayı sağlayacak bir ölçüt (measure) sürüklemek çözüm olabilir. Aşağıdaki gibi bir sorgu oluşturulur, universe satır limiti aşılmaz ve teknik ve mantıksal açıdan hedefe ulaşılmış olur.

select m.musterino, count(distinct m.musterino)
from musteri m,
islem i
where m.musterino = i.musterino
and i.islemkodu = 'A'
group by m.musterino

oracle geçersiz tarihleri bulma fonksiyonu

Oracle veri tabanında kirli ya da tutarsız verilerle çalışıldığında ilgili tarih alanının geçerli bir tarih değeri olup olmadığını kontrol eden basit bir kontrol fonksiyonu.

create or replace function sample_schema.f_invalid_date
(date_field in varchar2,format_field in varchar2) return number as
  v_d date;
begin
  v_d := to_date(date_field, format_field);
  return 0;
exception
  when others then
    return 1;
end;
/

Aynı yöntemle karakter olarak saklanmış (stored) sayıların da geçersiz olup olmadığı kontrol edilebilir.


create or replace function sample_schema.f_is_valid_number(p_num varchar2) return number
as
  r number;
begin
  r := to_number(p_num);
  return 1;
exception
  when others then return 0;
end;
/