MODEL GEDREVEN
DATAWAREHOUSE ONTWIKKELING
MET MICROSOFT BI door Willem Otten, Microsoft BI-consultant Kadenza
Lees het volledige artikel en andere blogs op www.playitsmart.nl/blog
Ik ben een groot voorstander van modelgedreven softwareontwikkeling en het automatiseren van het software ontwikkelproces. Met behulp van softwareontwikkeling werken we al jaren aan het automatiseren van verschillende processen in een organisatie, maar het bouwen van de software zelf is vaak nog een grotendeels handmatig proces. In dit artikel wil ik aan de hand van een eenvoudige voorbeeldimplementatie laten zien hoe een modelgedreven aanpak kan worden geïmplementeerd met technologie uit de Microsoft BI stack.
VOORBEELDIMPLEMENTATIE: DE HOOFDLIJNEN
Onderstaande illustratie geeft de voorbeeldimplementatie in hoofdlijnen weer: Een functioneel metamodel beschrijft de bronsystemen en het doelsysteem, het datawarehouse, in termen van entiteiten, attributen, onderlinge relaties en de mapping van bronentiteit of -attribuut naar doelentiteit of -attribuut. Op basis van dit functionele model wordt een technisch model gegenereerd. In het voorbeeld is dat een eenvoudige transformatie waarbij twee auditkolommen worden toegevoegd aan elke doelentiteit. Maar het zou ook een vertaling kunnen zijn van het functionele model naar een data vault of anchor-model. Vanuit het technische metamodel worden vervolgens enerzijds DDL-scripts gegenereerd voor het aanmaken van de database en anderzijds SSIS ETL-packages. Daarbij gebruik ik Biml en Bidshelper om de uiteindelijke packages te genereren.
WAAR WE NAAR TOE WERKEN
Als voorbeeld werk ik het volgende eenvoudige dimensionele model uit, gebaseerd op informatie uit de Adventureworks-database: DIM_ProductHierarchy ProductID ProductName ProductSubCategory ProductCategory dwh_loadDate dwh_loadSrc
int
nvarchar(100) nvarchar(100) nvarchar(100) datetime varchar(100)
FACT_Sales SalesId ProductID DateID SalesOrder Quantity LineTotal dwh_loadDate dwh_loadSrc
int int datetime nvarchar(100) int money datetime varchar(100)
DIM_Date DateID MonthOfYear Year MonthYear dwh_loadDate dwh_loadSrc
date int int int datetime varchar(100)
Het resultaat van de voorbeeldimplementatie moet DDL zijn om het bovenstaand model te genereren, plus de SSIS-packages waarmee de ‘dim_producthierarchy’ en ‘fact_sales tabel’ worden gevuld. De datumdimensie vul ik met behulp van een tabelfunctie.
HET LOGISCHE MODEL
Voor de vastlegging van het logische metamodel maak ik gebruik van een SQL-database. Dat biedt een aantal voordelen: • De opslag leent zich ervoor om met verschillende tools uit te vragen. Denk aan een modelleringstool om het model te vullen, SSRS-rapport om systeemdocumentatie te tonen, etc. • Wijzigingen in het logische model kun je eenvoudig via SQL doorvoeren. • De metadefinities van bronsystemen (tabellen, attributen, etc) laten zich via de SQL system views makkelijk inlezen in het model. In dit voorbeeld maak ik gebruik van het volgende logische metamodel: t_attribute t_system systemid name description connectionstring
int varchar(20) varchar(150) varchar(500)
t_entity entityid systemid domain name description
int int varchar(20) varchar(20) varchar(150)
target
attributeid entityid name description datatype length scale isbusinesskey ordernumber
int int varchar(50) varchar(150) varchar(50) int int bit int target
source t_mapping mappingid targetentityid customfromclause sourcesystemid
int int varchar(500) int
t_mappingattribute mappingattributeid mappingid sourceattributeid targetattributeid expression
int int int int varchar(100)
Met behulp van ‘t_entity’ en ‘t_attribute’ beschrijf ik de entiteiten binnen een systeem (t_system). Met ‘t_mapping’ en ‘t_mappingattribute’ leg ik de mappings tussen entiteiten van bron- en doelsysteem vast, de lineage-informatie van het doelsysteem. Bovenstaand metamodel is een vereenvoudigd voorbeeld. In de praktijk zal een metamodel er uitgebreider uitzien, onder andere aangevuld met relaties tussen entiteiten. In het metamodel geef ik de volgende vulling op: • Bronmodel Adventureworks voer ik als ‘t_system’ op, met de entiteiten ‘salesorder’, ‘salesorderdetails’, ‘product’, ‘productsubcategorie’ en ‘productcategorie’ inclusief de bijbehorende attributen. • Doelmodel AV_DWH voer ik op met entiteiten ‘FACT_Sales’, ‘Dim_Producthierarchie’ en ‘Dim_data’. • De mapping wordt opgegeven tussen de attributen uit Adventureworks en de attributen uit AV_DWH. Dit resulteert in de volgende vulling van het metamodel:
T_entity
T_system
T_attribute (niet volledig)
T_mapping
T_mappingattribute
HET TECHNISCHE MODEL
Zoals gezegd, kies ik in dit voorbeeld voor een eenvoudig technisch model. Het technisch model is een één-op-één vertaling van het functionele model, aangevuld met twee audit-kolommen (bronsysteem en loaddate) per doelentiteit. Omdat de transformatie naar het technisch model eenvoudig is, heb ik die met twee views geïmplementeerd: • De view ‘vw_technicalmodel’ geeft alle attributen terug, aangevuld met een ‘dwh_loaddate’ en ‘dwh_loadsrc’ kolom per entiteit. • De ‘view vw_technicalmappingattribute’ geeft alle mapping-attributen terug, aangevuld met mappings voor de twee audit-kolommen per entiteit. In complexere gevallen kun je ervoor kiezen om het technisch model in aparte tabellen op te slaan en de transformatie van functioneel naar technisch model met een stored procedure of ETL-package te implementeren.
Ter illustratie, het technische model voor ‘DIM_ProductHierarchy’
DE DATABASE DDL
Voor het generen van de database DDL gebruik ik een t-SQL script dat met behulp van een cursor de ‘create table statements’ samenstelt en deze print naar de output. Er is bewust voor gekozen om hier geen intelligentie in te bouwen die wijzigingen ten opzichte van een bestaand databasemodel kan detecteren. Er zijn genoeg tools, waaronder Visual Studio, die deze functionaliteit al bieden. De procedure hierbij zou dus zijn: het genereren van het nieuwe schema naar een lege database en die vergelijken via een passende tool met de bestaande versie om zo een ‘change script’ te genereren.
BIML-SCRIPT VOOR DE ETL
In het voorbeeld wordt per doeltabel een ETL-package gegenereerd met een eenvoudige laadstrategie: • Op basis van de ‘Business key’ velden wordt bepaald of een record al bestaat. Is dat het geval, dan wordt er niets gedaan, anders wordt het record toegevoegd. Uiteraard is dit geen laadalgoritme dat in de praktijk veel toepasbaar zal zijn, maar voor de begrijpelijkheid van dit artikel heb ik gekozen voor een eenvoudig voorbeeld. • Met behulp van een t-SQL script wordt een Biml XML-file gegenereerd die vervolgens in Visual Studio kan worden gebruikt. • Het script vraagt de benodigde gegevens op uit het functionele en technische metamodel en vertaalt dit naar een Biml-compatible XML-formaat met behulp van de XML-functionaliteit van SQL-server. De Biml-file bestaat uit de volgende onderdelen: • Connectie-instelling voor bron- en doelsystemen. • Package per doelentiteit met een dataflow task, met daarbinnen: - OleDbSource met bron-query. - OleDbSource met doel-query. - ‘Merge join’ van bron en doel om te bepalen welke records al bestaan. - ‘Derived columns’ operatie om audit-kolommen toe te voegen. - OleDbTarget om resultaat weg te schrijven. Het resultaat kan als Biml XML-bestand worden opgeslagen in Visual Studio. Onderstaande query geeft een beeld van hoe vanuit het metamodel een Biml-bestand wordt gegenereerd.
Hieronder staat de voorbeeld Biml-query uitgewerkt. Om de gewenste XML-structuur te krijgen, maak ik gebruik van subqueries en de ‘for xml path...’ syntax. declare @targetSystem as varchar(100) set @targetSystem = 'AV_DWH'; WITH XMLNAMESPACES (DEFAULT 'http://schemas.varigence.com/biml.xsd') select ( Select Connection.name as Name, Connection.connectionstring as ConnectionString from t_system Connection for xml auto, type ) as "Connections", ( select Name, ConstraintMode, ProtectionLevel, ( Select -‐-‐Tasks ( select Name, -‐-‐ DataFlow ( SELECT -‐-‐Transformations ( /* Add datasource for each mappping */ select Name as "@Name", -‐-‐OleDbSource ConnectionName as "@ConnectionName", DirectInput, ( Select ( Select 'src_' + a.name as "@SourceColumn", case when a.isbusinesskey = 1 then ma.rowNumber else null end as "@SortKeyPosition" from vw_technicalMappingAttribute ma join t_attribute a on ma.targetattributeid = a.attributeid where ma.mappingid = package.MappingID for xml path ('Column'), type ) for xml path ('Columns'), type ) from vw_ETLTransformationSource as OleDbSource where OleDbSource.mappingID = Package.MappingID for xml path ('OleDbSource'), type ) ... for xml path ('Transformations'), type ) FROM vw_ETLPackage Dataflow WHERE DataFlow.mappingID = Package.MappingID for xml auto, type ) FROM vw_ETLPackage Tasks WHERE Tasks.mappingID = Package.MappingID for xml auto, type ) from vw_ETLPackage Package where Package.SystemName = @targetSystem for xml auto, type) as "Packages" for xml path ('Biml')
GENEREREN VAN SSIS-PACKAGES
Nadat je de Biml-file in Visual Studio hebt geïmporteerd, kun je die uitvoeren om SSIS-packages te genereren:
Hierna worden de packages toegevoegd aan het project en zijn ze klaar om uit te voeren.
DOCUMENTATIE
Het functionele model ligt volledig vast in een database. Dit houdt in dat er ook goed over gerapporteerd kan worden. Onderstaand SSRS-report laat zien hoe een rapportage kan worden gemaakt van datamodel en lineage van een doelsysteem.
Voorbeeld SSRS-report
CONCLUSIE
Met dit voorbeeld heb ik geprobeerd aan te tonen hoe je met behulp van Biml een modelgedreven aanpak kunt hanteren in een BI-project en daarbij uitsluitend gebruik maakt van bekende Microsoft BI-technologie. Wanneer je een model gedreven product gaat ontwikkelen of selecteren zou ik de volgende uitgangspunten hanteren: Ontwikkel geen functionaliteit die andere producten al voldoende bieden Wanneer je een eigen product ontwikkelt heb je al snel de neiging om alles zelf op te lossen. Maak echter zoveel mogelijk gebruik van bestaande software en componenten in plaats van het wiel opnieuw uit te vinden. Denk hierbij aan de user interface om het logische model vast te leggen, het detecteren van changes in de database en versiebeheer op het model. Ontkoppel functionele definitie en implementatie Zorg dat de functionele definitie niet gekoppeld is aan een specifieke implementatie. Als het functionele model in datavault wordt gemodelleerd, is het erg lastig om hier later toch een andere modelleringsvorm van te maken. Als afleidingen in SSIS-syntax zijn vastgelegd, kun je hier moeilijk een andere ETL-tool voor inzetten. Het is handig om een abstractielaag in de vorm van een technisch model te introduceren waarin de technologie-specifieke zaken worden toegevoegd/vertaald vanuit het functionele model. Gebruiksvriendelijke user interface Het is van belang dat je eenvoudig en snel kunt modelleren. Wanneer je functionele modellen en mappings alleen via SQL of een beperkte user interface kunt onderhouden, zal er te weinig draagvlak zijn om het product te gebruiken.
ZELF PROBEREN?
Alle code die ik heb gebruikt in het voorbeeld vind je in onderstaande zipfile, inclusief instructies voor hoe je die lokaal kunt gebruiken.
DOWNLOAD HET VOORBEELD (ZIP BESTAND, 13 KB)
MEER INFORMATIE? NEEM CONTACT OP MET KADENZA MAIL [email protected] OF BEL +31 (0)35 - 5394490
WERKEN BIJ KADENZA? KIJK OP PLAYITSMART.NL
