Data Warehouse Script Generator Doel Op basis van een aantal standaard sql scripts ( create table, create view ) een nieuwe sql script genereren welke alle objecten ( tables & views ) kan aanmaken in een data warehouse.
Dataschema
Uitleg bij het dataschema Een organisatie heeft meerdere companies. In de source database zijn de tabellen gekwalificeerd met een table owner welke de company voorstelt. Bvb. Een organisatie ‘DEVIMO Consult’ heeft 46 companies onder zich. In de productie database zijn een aantal tabellen gemeenschappelijk voor alle companies, maar zijn heel wat tabellen gescheiden per company. Er is bvb een tabel ‘land’ welke alle landen bevat, dit is gemeenschappelijk voor alle bedrijven. In SF is deze tabel te benaderen via comm.pays ( table owner is hier comm ) De tabel ‘klanten’, echter, is per bedrijf te benaderen en krijgt dus een owner naargelang het bedrijf. Er bestaan dus in de productie database 46 tabellen met klantgegevens. Bvb foko.cli, fowe.cli, devc.cli, …. In de data warehouse echter, willen we deze tabellen samenvoegen tot één tabel ‘dbo.dw_customer’ welke een view is op de productie db en welke alle klanten bevat van alle companies. De view moet dus zodanig gebouwd zijn dat er een Union ontstaat van alle tabellen ‘cli’ : Create view dbo.dw_customer ( id_customer, id_company, customer_name ) As Select id_cli, ‘foko’, name from foko.cli Union all Select id_cli, ‘fowe’, name from fowe.cli Union all Select id_cli, ‘devc’, name from devc.cli …
Voor elk object ( een view of een table) zal de dwh-ontwerper een basisscript maken op één fictieve company ‘SOC_’ De basisscript zal ‘tags’ bevatten welke dan tijdens het genereren van de globale script vervangen dienen te worden door de echte company owner en ook ‘tags’ welke aangeven dat er een UNION dient te gebeuren op alle companies welke we in de dwh willen stoppen. Deze basisscript zal de ontwerper opslaan op schijf. Via de web applicatie moet dus kunnen : -
-
-
De organisatie toevoegen – wijzigen - verwijderen De companies per organisatie toevoegen – wijzigen – verwijderen De standaard objecten, met hun filename, toevoegen – wijzigen – verwijderen Een of meer dwh’s toevoegen – wijzigen – verwijderen Per dwh : o De companies selecteren welke we in de dwh willen opnemen o De objecten selecteren welke we in de dwh willen opnemen voor de verschillende companies De uiteindelijke script genereren in een file : in eerste release zullen we de script dan manueel uitvoeren via SQL Management studio ( of php admin – query … ). We zullen dus niet connecteren aan de doel-database om de script automatisch uit te voeren De uiteindelijke script moet kunnen her-uitgevoerd worden, dwz dat de view of de table eerst zal moeten gedropt worden.
Home Page
( alternatief homepage : )
Organisations
Overzicht met -
Organisation.name Organisation.script_source_path
Toevoegen, bekijken, bewerken : Formulier met -
Organisation.name Organisation.script_source_path : een ‘verkenner’ zou handig zijn om de directory aan te wijzen
Verwijderen : Na bevestiging door de gebruiker : delete cascade ( organisation, companies, dwh, dwh_object, dwh_company )
Companies
Overzicht met : -
Organisation.name company.name company.table_owner
Toevoegen, bekijken, bewerken : Formulier met -
drop down lijst voor selectie van de Organisation.name company.name company.table_owner
Verwijderen : Na bevestiging door de gebruiker : delete cascade ( company, dwh_company )
Objects
Overzicht met : -
object.name object.script_path object.script_filename
Toevoegen, bekijken, bewerken : Formulier met -
object.name object.script_path object.script_filename het zou handig zijn om hier een verkenner te kunnen gebruiker om de file op te halen
Verwijderen : Na bevestiging door de gebruiker : delete cascade ( object, dwh_object )
DWH
Overzicht met : -
Organisation.name Dwh.name Dwh.target_db_name
Toevoegen, bekijken, bewerken : Formulier met -
Drop down lijst om de organisation_name te kiezen Dwh.name Dwh.target_db_name Dwh.target_db_server Dwh.Target_server_type ( 1 = MS SQL Server, 2 = SyBase, 3 = MySQL ) Dwh.login Dwh.password Dwh.script_destination_path Dwh.script_detination_filename Dwh.default_source_db_name Dwh.default_source_db_server
Vervolgens een box met een 2e formulier om de companies aan te duiden welke in de dwh moeten opgenomen worden dit gaat in de table ‘dwh_company’ ( cfr accordeon boxes die je kan in/uitklappen ) - Hier worden alle companies van de organisation getoond, met een checkbox of ze al dan niet in de dwh moeten opgenomen worden. De gebruiker kan vervolgens aan of uitvinken Vervolgens een box met een 3e formulier om de objecten aan te duiden welke in de dwh moeten opgenomen worden dit gaat in de table ‘dwh_object ( cfr accordeon boxes die je kan in/uitklappen ) - Hier worden alle objecten getoond, met een checkbox of ze al dan niet in de dwh moeten opgenomen worden. De gebruiker kan vervolgens aan of uitvinken - Per object wordt ook de source db en source server opgegeven : deze 2 velden zijn default ingevuld met de waarden uit dwh.default_source_db_name en dwh.default_source_db_server Knop ‘Opslaan’ Knop ‘Opslaan en script genereren’ zie verder
Verwijderen : Na bevestiging door de gebruiker : delete cascade ( dwh, dwh_object, dwh_company )
Genereren van de script Een source script voor één object ziet er als volgt uit : -- Object: View [dbo].[dw_customer] use %target_db% go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO drop view dbo.dw_customer go create view dbo.dw_customer ( id_customer, customer_name, id_country, customer_city, customer, id_company , customer_litigation_class, follow_up_profile , gl_account, payment_condition, credit_status, credit_limit , name_index1, name_index2 ) as -- %START_UNION% select cli.id_cli, rtrim(rela.rel_nom1)+' '+ isnull ( rela.prenom , '' ), rela.id_pays, rtrim(post.id_pays) + rtrim(post.id_postal) + ' ' + post.lib_commune , rtrim(rela.rel_nom1)+' '+ isnull ( rela.prenom , '' ) + ' - ' + isnull ( rtrim(post.id_pays) + rtrim(post.id_postal) + ' ' + post.lib_commune , '' ) + ' (' + rtrim(cli.id_cli) + ')' , 'soc_' , isnull(clas_cli.id_clas, 'NC' ) , cli.id_t_suivi , str_cptpara.id_cgen , id_cpmt_credit , choix_lib.libelle , limite_credit , substring ( rela.rel_nom1 , 1, 1 ) , substring ( rela.rel_nom1 , 1, 2 ) from [%source_server%].[%source_db%].soc_.cli cli left outer join [%source_server%].[%source_db%].comm.rela rela on cli.id_rela = rela.id_rela left outer join [%source_server%].[%source_db%].comm.post post on rela.id_pays = post.id_pays and rela.id_post = post.id_postal and rela.id_var = post.id_variante left outer join [%source_server%].[%source_db%].soc_.clas_cli clas_cli on clas_cli.id_tclas = 'CONT' and cli.id_cli = clas_cli.id_cli left outer join [%source_server%].[%source_db%].soc_.str_cptpara str_cptpara on cli.id_str_cptpara = str_cptpara.id_str_cptpara left outer join [%source_server%].[%source_db%].comm.choix_lib choix_lib on choix_lib.id_choix = 'T_SOLVAB' and choix_lib.id_lang = (SELECT rela.id_lang FROM [%source_server%].[%source_db%].syst.soc AS soc INNER JOIN [%source_server%].[%source_db%].comm.rela AS rela ON soc.id_rela = rela.id_rela WHERE (soc.id_rela = 'soc_')) and cli.t_solvabilite = choix_lib.valeur
-- %END_UNION%
GO
Nadat de volgende vervangingen gebeurd zijn :
vervang
soc_
door
target company owner
vervang
%target_db%
door
target_db_name
vervang
%source_db%
door
source_db_name
vervang
%source_server%
door
source_db_server
herhaal de statements tussen %START_UNION% and %END_UNION% voor elke company met een UNION ALL, uitgezonderd voor de eerste company create view … as select … from … where … union all select … from … where … union all select … from … where …
Ziet de script er als volgt uit : In dit voorbeeld : enkel één company met als owner devc
-- Object:
View [dbo].[dw_customer]
use cds2000_v2_devimo go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO drop view dbo.dw_customer go create view dbo.dw_customer ( id_customer, customer_name, id_country, customer_city, customer, id_company , customer_litigation_class, follow_up_profile , gl_account, payment_condition, credit_status, credit_limit , name_index1, name_index2
)
as -- %START_UNION% select cli.id_cli, rtrim(rela.rel_nom1)+' '+ isnull ( rela.prenom , '' ), rela.id_pays, rtrim(post.id_pays) + rtrim(post.id_postal) + ' ' + post.lib_commune , rtrim(rela.rel_nom1)+' '+ isnull ( rela.prenom , '' ) + ' - ' + isnull ( rtrim(post.id_pays) + rtrim(post.id_postal) + ' ' + post.lib_commune , '' ) + ' (' + rtrim(cli.id_cli) + ')' , 'devc' , isnull(clas_cli.id_clas, 'NC' ) , cli.id_t_suivi , str_cptpara.id_cgen , id_cpmt_credit , choix_lib.libelle , limite_credit , substring ( rela.rel_nom1 , 1, 1 ) , substring ( rela.rel_nom1 , 1, 2 ) from [win7-franky\sqlexpress].[cds2000_v2_devimo].devc.cli cli left outer join [win7-franky\sqlexpress].[cds2000_v2_devimo].comm.rela rela on cli.id_rela = rela.id_rela left outer join [win7-franky\sqlexpress].[cds2000_v2_devimo].comm.post post on rela.id_pays = post.id_pays and rela.id_post = post.id_postal and rela.id_var = post.id_variante left outer join [win7franky\sqlexpress].[cds2000_v2_devimo].devc.clas_cli clas_cli on clas_cli.id_tclas = 'CONT' and cli.id_cli = clas_cli.id_cli left outer join [win7franky\sqlexpress].[cds2000_v2_devimo].devc.str_cptpara str_cptpara on cli.id_str_cptpara = str_cptpara.id_str_cptpara left outer join [win7franky\sqlexpress].[cds2000_v2_devimo].comm.choix_lib choix_lib on choix_lib.id_choix = 'T_SOLVAB' and choix_lib.id_lang = (SELECT rela.id_lang FROM [win7-franky\sqlexpress].[cds2000_v2_devimo].syst.soc AS soc INNER JOIN [win7-franky\sqlexpress].[cds2000_v2_devimo].comm.rela AS rela ON soc.id_rela = rela.id_rela WHERE (soc.id_rela = 'devc')) and cli.t_solvabilite = choix_lib.valeur
-- %END_UNION%
GO
