Oracle Business Intelligence

A csillag séma a tény tábla és a dimenzió táblák csillag szerű összekapcsolásából áll. A középen álló tény tábla tartalmazza a számszerű (lehetőleg összeadható) mértékeket, valamint a csillag-szerűen kapcsolódó dimenzió táblákra való hivatkozásokat (távoli kulcsokat). A dimenzió táblák pedig - mint már említettem - a lehető legtöbb leíró tulajdonságot tartalmazza.



A legkézenfekvőbb tulajdonságai a csillag sémának az egyszerűség, és a szimmetria. Talán már ránézésre is érthetővé válik, hogy milyen üzleti folyamatot reprezentál a modell, még egy informatikában kevésbé jártas üzleti felhasználó számára is.

Az egyszerűség másik jelentős következménye a jó lekérdezési teljesítmény. Az adatbázis motor sokkal kevesebb join segítségével tudja végrehajtani a lekérdezéseket.

A szimmetriának köszönhetően minden dimenzió teljesen ekvivalens, mindegy melyik dimenzió felhasználásával indít lekérdezéseket a felhasználó, nincs különbség. Nincsenek a modellbe épített feltételezések a leendő lekérdezéseket illetően.

Mark Rittman írt egy bejegyzést a blogjába arról, hogy hogyan lehet egy normalizált modellből az Oracle BIEE segítségével dimenzionális modellt építeni.

Folytatódik a munkahelyemen a migráció előkészítése: nézegetjük, hogy egyrészt mire képes a Migration Workbench, másrészt mik azok a lehetséges buktatók, amikre még nem gondoltunk.

Eközben találtam egy újabb bug-gyanús - igen idegesítő - hibát az OMWB-ben:

Ha valamilyen Oracle kulcsszó szerepel azonosítóként az SQL Server oldali kódban, azt okosan átalakítja, például:

select @something = date
from MyTable

ez a kód teljesen jó SQL Server alatt, ha a MyTable táblának van date nevű oszlopa. Ezt az alábbi kódra alakítja át:

SELECT date_
INTO v_something
FROM MyTable ;

A konvertálás előtt egy kollégám elkészített egy olyan sémát, ahol az összes oracle-s kulcsszó mögé egy _-t szúrt. Tehát már SQL Server oldalon az összes date-ből date_ lett, a type-okból type_, és így tovább. Viszont konvertálás után az összes type_ azonosítóra való hivatkozásból újra type lett. Ami SQL Server alatt:

select @something = type_
from MyTable

az konvertálás után:

SELECT type
INTO v_something
FROM MyTable ;

ami természetesen hibás. Ugyanez más kulcsszó esetén nem jött elő, tehát a date_-ek megmaradtak :)

A másik bug a következő: amit már egy korábbi bejegyzésben írtam is, az SQL Server megengedi, hogy egy tárolt eljárás bemenő paraméterét mezei változóként kezeljük, azaz állhat értékadás bal oldalán. Ezt az OMWB kezeli is, egy belső, rendesen deklarált változó segítségével. Ha a paraméterre való hivatkozás mindenhol pontosan ugyanaz, case sensitive-en:

create procedure foo (@bar int)
as
begin
select @Bar=5
end

itt ugye a hivatkozás megegyezik, leszámítva a kis/nagy B betűt. Az OMWB emiatt megzavarodik picit:


CREATE OR REPLACE PROCEDURE foo
(
v_bar IN NUMBER DEFAULT NULL
)
AS
BEGIN
v_Bar := 5;

END;

Sajnos ezek igen idegesítő hibák, remélem valahogy meg lehet majd őket kerülni, vagy ha nem, akkor hamar kijavítják.

UPDATE: visszaigazolt bug: 7332018: FORUMS: PROCEDURE ARGUMENTS NEED TO MATCH VARIABLE NAMES IN CASE.

Találtam egy remek blogot a Migration Workbench-ről, ahol csomó ismert problémán kívül szerepel egy rövid leírás, hogy milyen lépések mentén célszerű a migrációt elvégezni. Ezt végignyomkodtam, csak próbaképp, hogy lássam, mennyire működőképes.

Miután lefutott (kb. 2-3 óra alatt) meglepődve tapasztaltam, hogy az összes nagy 'L' betűt, ami valamilyen azonosítóban (táblanév, oszlopnév, stb) szerepelt, eltűntette. Tehát pl.: az FX_Leg táblából FX_eg lett, a LoginCount oszlopnévből oginCount..

Erről nyitottam is egy fórum-topicot az OTN-en, de közben elkezdtem én is nézni, hogy mi lehet a gond.

A migráció elején, amikor létrehozzuk a repository-t (Associate Migration Repository), létrejön egy MIGRATION_TRANSFORMER nevű PL/SQL package, aminek az elején szerepel egy konstans:

C_DISALLOWED_CHARS CONSTANT NVARCHAR2(100) := ' #.@`!"%^&*()-+=[]{};:,.<>?/~''L';

Hogy miért került a felsorolás végére a nagy L betű, nem tudom. Mindenesetre, ha kiszedem onnan, és újrafordítom a csomagot, már működik :)

UPDATE: visszaigazolt hiba: We saw the English pound symbol problem, we now define the character as:

C_DISALLOWED_CHARS CONSTANT NVARCHAR2(100) := ' #.@`!"%^&*()-+=[]{};:,.<>?/~'''||UNISTR('\00A3');

Egyik előző bejegyzésben már írtam, hogy a migrációhoz igen nagy segítséget jelent az SQL Developer Migration Workbench része (dokumentáció itt). Van egy ún. Translation Scratch Editor része, ami két panelből áll, egyikbe beírja az ember a Transact-SQL kódot, és egy gombnyomásra megjelenik a másik oldalon a PL/SQL változata. Meglepő, de a generált kód igen nagy százalékban működik, és helyes! :)

Eddig két tipikus esetet találtunk, amikor valami bug miatt mégsem jó kódot generál. Az egyik paraméterkezeléssel kapcsolatos, a másik a T-SQL-ben létező INSERT INTO ... EXEC szerkezet átalakításával.

Az SQL Server megengedi, hogy a bemenő paramétereket mezei változóként használjunk, azaz a lenti kód helyes:

CREATE PROCEDURE foo (@dummy int = null)
AS
begin
select @dummy = 5
end

PL/SQL-ben viszont bemenő paraméter nem szerepelhet értékadás bal oldalán. A konvertáló ezt a kódot helyesen az alábbira alakítja át:

CREATE OR REPLACE PROCEDURE foo
(
iv_dummy IN NUMBER DEFAULT NULL
)
AS
v_dummy NUMBER(10,0) := iv_dummy;
BEGIN
v_dummy := 5;

END;

Azaz létrehoz egy változót is, aminek kezdeti értékül a paraméter értékét adja. Ez teljesen jó.

Viszont abban az esetben, ha az értékadást nem SELECT, hanem a SET operátorral végezzük:

CREATE PROCEDURE foo (@dummy int = null)
AS
begin
set @dummy = 5
end

már nem veszi észre, hogy valójában egy paraméterről van szó, és egy az egyben átfordítja:

CREATE OR REPLACE PROCEDURE foo
(
v_dummy IN NUMBER DEFAULT NULL
)
AS
BEGIN
v_dummy := 5;

END;

Ez a kód természetesen nem fordul le.

A másik hiba akkor fordul elő, ha T-SQL-ben meglévő INSERT INTO...EXEC struktúrát próbálja átalakítani. T-SQL-ben lehetőség van arra, hogy egy táblába szúrjuk bele egy tárolt eljárás által visszaadott eredményhalmazt (ugyanis T-SQL tárolt eljárás képes eredményhalmazokkal is visszatérni, nem úgy, mint a PL/SQL eljárások). Az alábbi T-SQL kódot:

CREATE PROCEDURE foo
AS
begin
insert into sometable (foobar)
exec sp_something
end

erre fordítja át:

CREATE OR REPLACE PROCEDURE foo
AS
v_temp SOMETABLE%ROWTYPE;
BEGIN
sp_something();
LOOP
FETCH cv_1 INTO v_temp;
EXIT WHEN cv_1%NOTFOUND;
INSERT INTO sometable VALUES v_temp;
END LOOP;
CLOSE cv_1;

END;

Maga a logika teljesen jó lenne: a tárolt eljárás egy kurzorváltozóval tér vissza, amin végigmenve egyesével beszúrjuk a sorokat a táblába. A bökkenő csak az, hogy nem deklarálja a cv_1 kurzorváltozót, így a fenti kód szintén nem fordul.

UPDATE: visszaigazolt bug No: 7335256 FORUMS: INSERT INTO .. EXEC PROCEDURE HAS UNDECLARED VARIABLES.

Ezek a hibák természetesen nem vészesek akkor, ha valaki csak pár eljárást szeretne átkonvertálni. Viszont akkor, ha több ezerről lenne szó, akkor már bosszantóak, ugyanis így nehézkes automatizálni.

A migráció kapcsán rögtön belefutottunk egy érdekes problémába: SQL Server oldalon több olyan oszlopnevet is használunk, ami Oracle alatt kulcsszó vagy fenntartott szó: name, text, comment, date, number, stb.

Úgy gondoltuk, hogy ha ezeket az oszlopneveket nagybetűsen és ""-k között hozzuk létre (és így is hivatkozunk rájuk) akkor működhet. A táblák létre is jöttek, viszont több esetben egyéb gondok léptek fel. Pl.:

CREATE TABLE asdf (
"DATE" date
);

Select * from asdf;

Ez tökéletesen lefut, viszont:

declare
v_temp number;
begin

select 1 into v_temp from dual where exists (select 1 from asdf);
end;

ez már nem:

ORA-06550: line 5, column 61:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 2:

Gondoltuk, hogy baj van, akkor át kell nevezni az oszlopokat. Elkezdtem próbálkozni, hogy milyen kulcsszavakkal van még gond, és nem az összes reserved word-del van baja:

CREATE TABLE asdf (
"AUDIT" date
);

Elvileg az audit is reserved word mégsem fut hibára a fenti kódrészlet.

A következő gondolat az volt, hogy a típusnevekkel van csak gondja, de ez sem teljesen igaz, ugyanis a lenti esetben:

CREATE TABLE asdf (
"DATE" int
);

Szintén hiba nélkül lefut a PL/SQL kód, ellenben, ha "NUMBER" int van (tehát nem "NUMBER" number), akkor megint csak az említett hibára fut.

Maradt még egy utolsó kombináció, méghozzá a kulcsszavak kisbetűsen, ""-k között:

CREATE TABLE asdf (
"number" number
);

Így jelenleg minden (date, number, stb.) működik. Egyelőre. Már csak az a költői kérdés maradt hátra, hogy ebben így mi a logika?

Munkahelyemen elkezdődött a puhatolózás, hogy miből állna egy migráció SQL Serverről Oracle adatbázisra. Ebben nagy segítség az Oracle SQL Developer Migration Workbench-je, valamint a Migrating Applications from Microsoft SQL Server to Oracle9i Database című white paper, amiben részletesen ki van fejtve jópár szintaktikai és szemléletmódbeli különbség is. Ezen az oldalon is fel van sorolva néhány :)

Mivel az üzleti logika jelentős része Transact-SQL tárolt eljárások formájában van megvalósítva, ezért talán az egyik legnagyobb feladat ezek PL/SQL-re konvertálása lesz. A Migration Workbench része egy scratch editor, amiben kb. gombnyomásra lehet az egyszerűbb (és nem SQL Server specifikus) kódokat átkonvertálni, több kevesebb sikerrel. Sajnos vannak még hibái, de mindenesetre nagyon ígéretes! Szerintem nem fogunk unatkozni az elkövetkezendő hónapokban :)

Lecseréltem a blog alatti template-et, hogy picit kultúráltabb legyen a kinézete :) Remélem úgy sikerült, hogy nem ment a funkcionalitás rovására.

Tény táblák

A tény táblák (fact tables) a dimenzionális modellezés központi elemei: ezek azok a táblák, ahol az adott üzleti folyamat számszerű mértékei szerepelnek. Például egy üzletlánc napi eladásait reprezentáló táblában ez a mérték lehet az eladott mennyiség, vagy az értük kapott pénzösszeg. Minden nap, bármelyik boltban bármelyik termék értékesítésre kerül, készül egy bejegyzés is. A dimenziók ezen listája határozza meg a tény tábla finomságát, felbontását.

Egy adattárház szempontjából a leghasznosabb mértékek számszerűek és összeadhatóak, mivel igen ritka az az eset, amikor egyetlen sorra kiváncsi a felhasználó a tény táblából. Éppen ellenkezőleg, általában a sorok százezreinek az aggregált értékére kiváncsi (az elmúlt hónapban eladott termékek mennyisége, bevétel, stb.). A fenti példában az eladott mennyiség és a pénzösszeg is összeadható bármelyik dimenzió mentén.

Nem minden tényadat összeadható, léteznek részlegesen összeadható (semiadditive) mértékek, amelyeket csak bizonyos dimenziók mentén lehet összeadni, és nem összeadható mértékek is. Például egy raktárkészlet aktuális állapotát vagy számlák aktuális egyenlegét reprezentáló tény táblák tipikusan ilyen részlegesen összeadható adatokat tartalmaznak, ugyanis értelmes összeadni a számlaegyenlegeket például ügyfelek szerint, de értelmetlen az idő szerint. Ilyen esetekben a legcélszerűbb megközelítés az átlagolás: az adott periódusra szóló átlag-egyenleg, vagy átlagos raktárkészlet.

Dimenziók

A dimenziók a tény táblák kísérői. Ezek a táblák tartalmazzák a szöveges leírásait az adott üzleti folyamatnak. Egy jól megtervezett dimenzionális modellben egy dimenzió táblának lehető legmagasabb számú oszlopa vagy másnéven attribútuma van, ugyanis ezek az attribútumok játszanak a lekérdezéseknél, elemzéseknél csoportosító, megszorító, vagy magyarázó szerepeket. Emiatt létfontosságú, hogy minél több jól definiált, értelmes dimenzió attribútum legyen, mert ezek határozzák meg az adattárház használhatóságát. A dimenziók jelentik az interfészt az adattárház és a felhasználó között.

Míg a tény adatok főleg számszerűek és folytonos értékkészletűek, addig a dimenzió attribútumok általában szövegesek, és diszkrétek.

A dimenziók sokszor hierarchikus kapcsolatot reprezentálnak. Például egy termék egy adott márkához tartozik, amiket kategóriákba sorolunk, és így tovább. A termék dimenzió táblában minden sorban (minden termékre) eltároljuk az adott termék márkáját és a kategória szöveges jellemzését is. Ez épp ellentétes egy normalizált adatbázissal, ugyanis rengeteg redundáns információt tartalmaz. A dimenzió táblák tipikusan denormalizáltak (kivéve snowflake séma esetében), a performancia és az egyszerűség, könnyen érthetőség érdekében feláldozzák a szükséges tárhely mennyiségét.