Syystä tai toisesta on tullut viime aikoina törmättyä useammin ja useammin XML-muotoiseen dataan tietovarastojen datalähteenä. Yksi yleisimmistä käyttötapauksista taitaa olla tuotetiedon hallintajärjestelmien (PDM/MDM) tuottamien XML-dokumenttien hyödyntäminen.
SQL Serverissä on ollut XML tuki jo versiosta 2005 asti. Harvempi sitä on kuitenkaan tietovarastokäytössä hyödyntänyt. XML-tuki löytyy myös SSIS-välineessä. Ne jotka ovat sitä yrittäneet oikeissa projekteissa käyttää, ovat huomanneet sen vaillinaiset ominaisuudet. Kolmansien osapuolien parempia (ja maksullisia) SSIS- XML-lähdekomponenttejakin on olemassa – mutta miksi ei käyttäisi natiivia Transact-SQL:ää? Pienellä oppimiskäyrällä SQL Serverin XML-ominaisuuksista voi saada paljon irti.
Eli lyhyesti ja ytimekkäästi päivän idea on laajentaa tietovaraston tuotedimensiota SQL Serverin XML-sarakkeella. Erityisesti PDM-järjestelmissä tallennetaan monessa tapauksessa valtavasti tuotetietoa, joiden hyödyntäminen ja tallentaminen kokonaisuudessaan relaatiomalliin on järjetöntä. Miksi ei tallennettaisi tietovarastoon koko XML:ää? Osa sisällöstä on toki pakko kääntää sarakkeiksi, monasti jo historiointitarpeidenkin vuoksi (Slowly Changing Dimension)
Tuumasta toimeen.
1) Luodaan taulu, jossa on XML-tyyppinen sarake
[code language=”sql”]
CREATE TABLE D_ProductXML
(Product_ID INT IDENTITY (1,1),
ProductName VARCHAR(50),
ProductXML XML)
[/code]
2) Tuupataan dataa tauluun (elävässä elämässä tuupattaisiin toki hieman eri lailla ja XML:tkin seuraisivat standardia, he jotka jaksavat lukea tätä juttua tänne asti todennäköisesti tämän tietävätkin). Sitten taas ne, jotka saavat näppylöitä polkupyörädemoista, ottakaa hydrokortisoonipurnukat tässä vaiheessa esille.
[code language=”sql”]
INSERT INTO D_ProductXML (ProductName, ProductXML)
VALUES (‘Mummopyörä’,
‘
<bike>
<id>1</id>
<status>Active</status>
<names>
<name>Bike</name>
<name>Cykel</name>
</names>
<parts>
<parts id="1">Etupyörä</parts>
<parts id="2">Runko"</parts>
</parts>
</bike>
‘
),
(‘Kilpapyörä’,
‘
<bike>
<id>2</id>
<status>Passive</status>
<names>
<name>Competition</name>
<name>Cykeln</name>
</names>
<parts>
<parts id="1">Etupyörä 22"</parts>
<parts id="2">Kisarunko</parts>
</parts>
</bike>
‘
)
[/code]
Eli meillä on nyt käsissä maailman yksinkertaisin tuotetaulu, jossa on surrogaatti, tuotteen nimi sekä lisää dataa XML-sarakkeessa. Tätä XML:ää voi hyödyntää SQL:ssä monella tavalla.
Esimerkki1. Pidetään data flattina mutta kysellään XML:stä ensimmäinen nimi sekä tuotteen status. Jos tämä näyttää monimutkaiselta, tuon xqueryn voi toki piilottaa raportintekijältä vaikka näkymän taakse.
[code language=”sql”]
SELECT
Product_ID,
ProductName,
ProductXML.value(‘(bike/names/name)[1]’, ‘varchar(50)’) as Name1,
ProductXML.value(‘(bike/status)[1]’, ‘varchar(50)’) as ProductStatus
FROM D_ProductXML
[/code]
Lopputulos näyttää tältä:
Esimerkki2. Käyttämällä CROSS APPLY:a voidaan XML räjäyttää riveiksi. Tässä halutaan nähdä käytetyt komponentit polkupyörittäin riveillä. Kahdesta tuoterivistä populoituu yksi rivi per yksi komponentti.
[code language=”sql”]
SELECT
Product_ID,
ProductName,
Parts.value(‘(text())[1]’, ‘varchar(100)’) as Part
FROM D_ProductXML
CROSS APPLY ProductXML.nodes(‘//parts/parts’) as P(Parts)
[/code]
Lopputulema näyttää jotakuinkin tältä:
Summa summarum, hyödyt:
- Ylläpito: XML voi elää omaa elämäänsä. Uuden sarakkeen lisääminen ei tarkoita välttämättä ensimmäistäkään muutosta tietovarastoon tai ETL:ään. XML:n uutta tietuetta voi hyödyntää suoraan näkymässä tai kyselyllä
- Suorituskyky: XML:n lukeminen kantaan on nopeampaa kuin XML:n käsittely tiedostona – käyttötapauksesta riippuen. Toisaalta esim. laskurivin mallintaminen riveinä XML:ksi laskun otsikkoriville ei välttämättä ole se paras idea
- ETL:n tekemisen kannalta natiivi-SQL:n käyttö XML:n lukemisessa on mielestäni yksinkertaisempaa ja vähemmän virhealtista kuin SSIS:n omien komponenttien käyttö. Etenkään jos XML:t ovat näitä esimerkkejä yhtään monimutkaisempia.
