09.12.2015

Tajunnanlaajennusta tietovarastoon XML:llä mallintamalla

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ä:

Esimerkki1

 

 

 

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ä:

Esimerkki2

 

 

 

 

Summa summarum, hyödyt:

Share
Contact Person

Blog writer

Jani Liimatta

Vincit Bilot

Bilot & Vincit have joined forces!

See where the story continues 

You have Successfully Subscribed!

Vincit Bilot

Bilot & Vincit have joined forces!

See where the story continues 

You have Successfully Subscribed!