dimarts, 30 d’agost de 2016

Speaker al SQL Saturday #586 Madrid

Avui m'han confirmat seré speaker al SQL Saturday #586 a Madrid el proper 24 de setembre!
Serà la primera vegada que presento en un congrés d'informàtica i espero que no sigui l'última.

La ponència que presentaré serà: "Extracción automàtica de datos mediante BIML" on expicaré com generar la metadata i els dtsx mitjançant BIML per a extreure dades d'orígens com SQL Server, Excels, fitxers plans. Bàsicament el que he estat escrivint en els posts anteriors.

Ens veiem el 24!

dimecres, 17 d’agost de 2016

ADEU-BIML: Text File (II)

Després de massa temps sense escriure continuarem amb la generació dels dtsx per carregar TextFiles. En l'anterior post (http://www.eljordifabi.tech/2016/07/adeu-biml-text-file.html) vam veure com generar la metadata.
El primer punt diferencial respecte la resta d'orígens de dades és la creació de la connexió. Anem a explorar el fitxer UT_generateConnections.biml

En la definició de FlatFileConnection hi ha l'atribut FileFormat. Aquest atribut fa referència al tag FileFormats de final de tot del fitxer. Dins de FileFormats es defineixen les diferents columnes que tindran els fitxers i també atributs com poden ser el separador de columnes i de files, si té capçalera, etc. Tots aquests atributs els recuperarem de la metadata generada. Cal tenir en compte els tipus de separadors que accepta BIML i com escriure'ls
 case row_delimiter when '{CR}{LF}' then 'CRLF' when '{CR}' then 'CR' when '{LF}' then 'LF' when ';' then 'Semicolon' when ',' then 'Comma' when 'tab' then 'Tab' when '|' then 'VerticalBar' else row_delimiter end as row_delimiter
  
Per a completar el tipus de dades utilitzarem la funció UT_SQLServer_datatype.SQLServerToBimlDatatype que està al fitxer: UT_SQLServer_datatype.cs que fa la traducció de SQLServer a tipus de dades BIML.
Un cop definides les  FlatFileConnection amb els siure FileFormats ja es pot utilitzar la connexió.

En el nostre cas tenim l'opció de carregar un fitxer, o bé, carregar tots els fitxers d'una carpeta que continguin un patró en el nom. En el cas de carregar només un fitxer a l'espression de la connectionString hi haurà el path i el nom del fitxer, però al carregar els fitxers d'una carpeta hi haurà la variable que es farà servir dins del bucle.En el primer cas l'expression és opcional, en el segon és obligatòria.

<FlatFileConnection Name="<#= _table["source_name"] #>" FileFormat="<#= _table["source_name"] #>" FilePath="<#=  _dbtable["connectionString"]  #>" >
    <# if (_param &&  (_packageName!="LoadData" || Convert.ToInt32( _dbtable["isFolder"]) ==0)) { #> <!-- o no és folder -->
        <Expressions>
            <Expression ExternalProperty="ConnectionString">@[$Project::<#= _table["source_name"] #>_Path]+@[$Project::<#= _table["source_name"] #>_File]</Expression>
        </Expressions>
    <# } #>
    <# if (_packageName=="LoadData" && Convert.ToInt32( _dbtable["isFolder"]) !=0) { #> <!-- i és folder -->
        <Expressions>
            <Expression ExternalProperty="ConnectionString">@[User::File]</Expression>
        </Expressions>
    <# } #>
</FlatFileConnection>


El següent fitxer a explorar és LoadDataFlatFiles.biml 
En aquest fitxer es completa el dataFlow per a carregar les dades del fitxer a l'Staging Area. L'estructura és:
1) Truncar la taula de l'staging Area
2) Recórrer tots els fitxers si és una carpeta o un sol fitxer
3) Llegir el fitxer
4) Comptar les files llegides
5) Bolcar les dades a l'staging Area
6) Escriure les files carregades a la taula de Log

En cas de voler carregar les dades d'una carpeta utilitzarem el ForEachFileLoop.
<ForEachFileLoop Name="FL_file_loop" ConstraintMode="Linear" Folder="<#= _tableTD["path"] #>" FileSpecification="<#= _tableTD["pattern"] #>"
                            ProcessSubfolders="<#= _tableTD["traverse_subfolders"] #>" RetrieveFileNameFormat="FullyQualified">
    

i guardarem el valor de la connectionString a la variable File
<Variables>
    <Variable Name="File" DataType="String"></Variable>
</Variables>
<VariableMappings>
    <VariableMapping Name="0" VariableName="User.File"  />
</VariableMappings>

Aquest és l'únic punt diferent respecte la resta de càrregues.

Amb això ja podem generar els nostres paquets dtsx. Com sempre, tot el detall del codi el podeu trobar al github: https://github.com/jordiisidro/adeu-biml/ 

Amb aquest post s'acaba una primera sèrie on hem generat la metadata i els fitxers dtsx per carregar una BBDD SQL Server, un fitxer Excel (podent recòrrer les seves pestanyes dinàmicament) i un fitxer pla.


 

dimarts, 12 de juliol de 2016

ADEU-BIML: Text File (I)

Després de veure com generar automàticament els dtsx amb BIML per a orígens SQL Server i Excel ara veurem com fer-ho per fitxer plans de text.
Tractarem fitxers de text de 2 tipus: de camps delimitats i de camps de mida fixa. El que canviarà, bàsicament, serà la generació de la metadata.
També afegirem la possibilitat de processar tots els fitxers de text que estiguin en un directori i que compleixin un patró en el nom.
L'esquema per a guardar la metadata quedarà de la següent manera:

Tenim la taula source_FlatFile que hereta de source i que té com a subclasse sourceFlatFileFolder, que és on guardarem els atributs del directori on estan els fitxers, el patró de nom dels fitxers, si s'han d'explorar els subdirectoris, etc.
En aquest cas no ens fa falta subclasse de source_detail, amb els atributs de la superclasse en tenim prou. El que sí que fa falta és una subclasse de source_column per als fitxers de camps de mida fixa. En la subclasse es guardarà la mida de cada camp.


En els cas dels flat files la majoria de la metadata l'haurem de posar manualment. En el cas de fitxers amb camps de mida fixa tota la metadata és manual. En els fitxers de camps separats per un caràcter sí que podrem definir les columnes (en cas que tingui capçalera amb el nom de la columna i en cas contrari amb un nom genèric tipus col1, col2...) i utilitzarem un tipus de dades estàndard per a tots els camps (varchar(4000)). El tipus de dades s'haurà de corregir posteriorment.

Un dels punts més diferent dels flatFiles respecte a les altres connexions és la definició de la connexió. No només s'ha de definir la FlatFileConnection, sinó que també s'ha de definir el FileFormat amb la informació dels camps. Teniu el codi a: https://github.com/jordiisidro/adeu-biml/blob/master/UT_generateConnections.biml
Per a la generació de la metadata considererem que el fitxer només té una columna que serà de tipus AnsiString de 4000. A través del procediment sp_generateColumnsFF trencarem aquesta columna tenint en compte el separador que li hem passat.
Per saber el tipus de dades a mapejar en aquest bloc trobareu la informació molt detallada (http://www.cathrinewilhelmsen.net/2014/05/27/sql-server-ssis-and-biml-data-types/)

I això és tot per a generar la metadata. En els FlatFiles no es pot fer gaire cosa, bàsicament podem saber quines columnes hi ha en un fitxer on els camps estan separats per símbols, però no podem saber el tipus de dades.

La creació i esborrat de taules és idèntica que en els casos anteriors.
En el proper post veurem com generar els dtsx per carregar les dades. Ens centrarem en com generar el FileFormat i també en com carregar els fitxers d'un directori.

Com sempre teniu tot el codi a: https://github.com/jordiisidro/adeu-biml/


dimarts, 5 de juliol de 2016

ADEU-BIML: Excel (II)

Al post anterior (http://www.eljordifabi.tech/2016/06/adeu-biml-excel-iii.html) vam veure una manera de generar la metadata automàticament quan tenim fulls excel, tant xlsx com xls.
La creació i esborrat de les taules es fa de la mateixa manera que amb orígens SQL Server (http://www.eljordifabi.tech/2016/06/adeu-biml-sql-server-ii.html).
Avui veurem com generar els dtsx per a carregar les dades. L'estructura general és la mateixa que per SQL Server. En el cas dels orígens Excel només implementarem la càrrega full. Si l'Excel té més d'un full es generarà un dtsx per cadascun d'ells. Queda pendent com a implementació futura poder recórrer tots els fulls d'un mateix Excel en bucle quan aquests tinguin tots la mateixa estructura.

Per a la generació de codi per bolcar les dades  a l'staging area implementarem la mateixa estructura de Truncate Staging area - Load Staging Area - Save number rows que vam implementar per a la càrrega full d'SQL Server. Podeu veure el codi a: LoadDataExcelSheetFull.biml
La part diferent és el component ExcelSource.


La resta de codi és idèntic que per un orígen SQL Server.

Com acabem de veure, un cop implementada la càrrega d'un component per implementar nous components sobretot ens hem de centrar en la generació de metadata, la resta de codi biml és pràcticament igual.
En els propers posts veurem com implementar l'extracció de dades automàtica tenint com a origen fitxers plans.

Com sempre podeu trobar tot el codi al github: https://github.com/jordiisidro/adeu-biml/


dimarts, 28 de juny de 2016

ADEU-BIML: Excel (I)

Odio l'Excel.
Bé, potser no tant... només l'odio quan l'he d'utilitzar en una ETL. Que si driver de 32 o 64 bits, que si problemes amb els tipus de dades, etc. però per molt que l'odii els usuaris no el deixaran de fer servir, així que ens haurem d'adaptar.
En els anteriors posts hem vist com utilitzar BIML per generar els dtsx per carregar les dades d'una BBDD SQL Server, ara veurem com fer-ho per fitxers Excel.
La part que més varia és la generació de metadata. A partir de tenir la metadata carregada la creació i esborrat de les taules és igual en tots els casos i la càrrega de dades és molt similar.

Anem a veure primer com queda l'esquema de BBDD de la metadata.

Veiem que com a filla de source tenim source_excel que serà el nostre fitxer excel, que tindrà un o diversos sheets.

Per a que em funcionés tant amb fitxers xlsx com amb fitxers xls al servidor tenia instal·lat l'excel de 64 bits i Microsoft Access Database Engine 2010 Redistributablede 32 bits.

Per extreure la metadata jugarem molt amb linked servers. Una altra alternativa és passar l'excel a CSV a través de powershell. En aquest vídeo del Miguel Egea ho explica molt bé (https://youtu.be/nJ5_xC--Y2I)

 Per a fonts d'origen excel podem generar automàticament el contingut de les taules source_detail i source_excel, source_column. L'atribut is_pk l'haurem d'informar manualment i el data_type podem agafar directament el que genera la metadata o bé modificar-lo a posteriori. Al fer sempre càrregues complertes no farà falta informar  el camp incremental.

El codi de generació de metadata per excel el teniu a https://github.com/jordiisidro/adeu-biml/blob/master/GenerateMetadataExcelSheet.biml
El primer que farem serà executar la funció: [sp_generateSheetsExcel] que omplirà les taules source_detail i souce_excel_table amb cadascun dels sheets dels fitxers excels definits a l'origen.


Posteriorment per a cada sheet crearem un linked server que ens crearà una taula amb el mateix format que l'excel a un esquema trash. Des d'allà farem com si en nostre origen fos directament SQL Server per extreure la metadata.
A l'acabar de generar la metadata esborrarem la taula de l'esquema trash.

Ara ja tenim la metadata generada, i seguint el mateix esquema que amb SQL Server podrem generar les taules a l'Staging Area.

Al proper post veurem com fer la generació de dtsx per a la càrrega de dades.

Com sempre podeu trobar tot el codi a: https://github.com/jordiisidro/adeu-biml/


divendres, 24 de juny de 2016

ADEU-BIML: SQL Server (III)

En els posts anteriors hem vist com generar amb BIML els dtsx que extreuen la metadata de BBDD SQL Server i que, a partir d'aquesta metadata, també podem generar els scripts de creació i esborrat de les taules de l'staging area i dels índexos. Avui toca mirar la part final, la generació dels dtsx que  carreguin aquestes taules.
La càrrega de dades la farem amb la mateixa estructura que la resta de bimls. Teniu el codi a https://github.com/jordiisidro/adeu-biml/blob/master/004_LoadDataParent.biml
Per a la càrrega de dades des de SQL Server generarem un dtsx per a cada taula. El motiu es que si volem obrir després el codi del dtsx si hi ha moltes taules triga molt a fer les validacions. En el nostre cas, per contra, serà ràpid obrir els dtsx, però en podem arribar a tenir molts.
El fet de generar un dtsx per a cada taula i que estiguin les connexions parametritzades complica una mica la generació de paràmetres. En el fitxer UT_generateParameters.biml haurem d'afegir un bucle per a cadascun dels paquests (i per tant de les taules) que ens generi el nom del paquet. També haurem de generar un paquet per cada font de dades.
Si no volguéssim utilitzar paràmetres en el nostre codi aquesta part no faria falta.

Un cop tenim els paràmetres passem a generar els paquets pares per cada origen de dades diferent, que el que faran serà invocar als paquets fills (UT_generatePackages.biml) a través d'un ExecutePackage. Cada origen de dades tindrà un paquet fill, i en el cas dels orígens SQL Server cada paquet fill tindrà més paquets fills amb cadascuna de les taules. Aquests paquets fills es generaran a UT_generateSubPackages.biml.
Des de UT_generateSubPackages.biml s'invoca a LoadDataDatabase.biml que el que fa és un bucle per cada taula i invoca als paquets fills (un per taula) també a través d'un ExecutePackage.
Finalment anem a implementar per fi la càrrega de dades, fins ara només hem generat paquets buits.

En el cas d'un origen SQL Server hem definit 2 tipus de càrrega: full i incremental. Per cadascuna d'elles hi haurà un biml diferent que s'invocarà segons el que s'hagi definit a la metadata.
Per a la càrrega full farem un truncate de la taula.
Seguit d'un dataflow on farem un select des de l'orígen (evitarem el select * i generarem el select <columnes>* ja que tenim la metadata) i ho bolcarem a l'staging area. També guardarem en una variable el nombre de files, que ens anirà molt bé a l'hora de debugar.
I finalment guardarem el nombre de files a una taula de log.

I ja tenim implementada la càrrega full!

Per a la càrrega incremental haurem de definir 2 variables de data per utilitzar-les de filtre i les alimentarem a través d'un ExecuteTask.
També farem el truncate table de l'staging area i un dataflow. En el dataflow utilitzarem les variables de data amb el camp que hem definit a la metadata com a data de modificació.

I també guardarem el nombre de files carregades.

I ja tenim també implementada la càrrega incremental!

Si en el nostre entorn necessitéssim altres tipus de càrrega només hauríem d'implementar el biml corresponent, la resta d'estructura es reutilitzaria.

En aquest punt ja podem generar el codi per omplir l'staging area dels nostres origens SQL Server.
En els propers posts explicarem el mateix procés per altres orígens com fitxers Excel i fitxers plans.

Com sempre tot el codi el trobareu al github: https://github.com/jordiisidro/adeu-biml/




divendres, 17 de juny de 2016

ADEU-BIML: SQL Server (II)

En el darrer post (http://www.eljordifabi.tech/2016/06/adeu-biml-sql-server-i.html) vam veure com generar la metadata d'un origen SQL Server. Avui utilitzarem aquesta metadata per començar a generar dtsx. Ens queden la creació de les taules a l'staging area, l'esborrat de les taules, la creació dels índexos i, finalment, l'extracció de les dades.

Creació de taules

Per crear les taules començarem amb el fitxer: 001_CreateTablesStagParent.biml que podeu trobar al github. La primera part afectada és: UT_generateSubPackages.biml

 En aquest paquet invocarem als .biml per cadascuna de les funcions. A l'anterior post vam veure l'implementació del GenerateMetadataDatabase.biml, ara veurem el de CreateTablesStagDatabase.biml
En aquest cas recuperarem de les taules de metadata el nom de les taules i de la bbdd i farem un bucle per poder recuperar les columnes de cada taula:

<# string _table_sql = "select t.table_name, t.schema_name, d.source_name, t.database_table_id, d.source_id, d.short_name "+
"from biml.source_database_table t "+
"join biml.source_detail de on t.database_table_id=de.source_detail_id "+
"join biml.source d on t.database_id=d.source_id "+
"join biml.project p on d.project_id=p.project_id "+
"join biml.source_type st on st.id_source_type=d.source_type "+
"where de.extract_type is not null and project_name='"+_project+"' "+
" and d.source_name='"+_bdcx+"'";
 En el punt de recuperació del tipus de dades de la columna veiem que s'utilitza la funció RowConversion del fitxer UT_SQLServer_datatype.cs 
Si des de 001_CreateTablesStagParent.biml generem els dtsx ens apareixerá un dtsx pel projecte i un altre per cada BBDD que haguem configurat a la nostra BBDD de metadata. És important tenir en compte que només és generarà l'script d'aquelles taules en que haguem informat a mà el tipus d'extracció (en el nostre cas Full o Incremental).
Un punt important a l'hora de crear taules: AFEGIU SEMPRE UN CAMP QUE INDIQUI QUAN S'HA INSERIT LA FILA, serà molt útil a l'hora de debutar.

Esborrat de taules

Per l'esborrat de taules el sistema és molt similar. El codi inicial està al fitxer 002_DropTablesStagParent.biml que a través de UT_generateSubPackages.biml invoca a DropTablesStagDatabase.biml
Igual que abans es consulten totes les taules que hi ha introduïdes a l'esquema de metadata i, en comptes de create, es genera el drop.

Creació d'índex

En la part de creació d'índex només crearem els índex unique clustered, però es poden crear tota la resta amb petites modificacions de la consulta inicial. El BIML inicial és: 003_CreateIndexStagParent.biml que acaba invocant a CreateIndexStagDatabase.biml
Recuperarem els índex i per cadascun d'ells les seves columnes

<# string _ix_sql = "select ix.index_name, ix.database_table_index_id, t.table_name, t.schema_name, d.short_name"+
" from biml.source_database_table t "+
" join biml.source_detail de on t.database_table_id=de.source_detail_id "+
" join biml.source d on t.database_id=d.source_id "+
" join biml.project p on d.project_id=p.project_id "+
" join biml.source_type st on st.id_source_type=d.source_type"+
" join biml.source_database_table_index ix on ix.database_table_id=t.database_table_id "+
" where is_unique='UNIQUE' "+
" and index_type='CLUSTERED' "+
" and de.extract_type is not null and project_name='"+_project+"'"+
" and d.source_name='"+_bdcx+"'";

Al ser índex clustered no tenen columnes included, també es podria modificar l'script per que acceptes columnes included d'índex non-clustered.

Ara ja tenim tota l'estructura creada a l'staging area, ara només falta omplir-la de dades. Això ho veurem al proper post.

Com sempre teniu tot el codi al Github: https://github.com/jordiisidro/adeu-biml/


dimecres, 8 de juny de 2016

ADEU-BIML: SQL Server (I)

En el darrer post sobre BIML (http://www.eljordifabi.tech/2016/05/adeu-biml.html) havíem acabat veient l'esquema BIML per generar els dtsx per a la generació de metadata dels orígens de dades. Aquesta metadata ens permetrà posteriorment crear els dtsx que ens extreguin les dades automàticament cap a la nostra Staging Area.
Avui començarem a analitzar com fer aquests passos tenint com a origen una base de dades en SQL Server.
L'esquema de metadata ampliat és el següent:

S'ha afegit com a subclasse de Source, source_database que és on guardarem la connection string. Source_database_table com a subclasse de source_detail. I 2 taules on guardarem els índex amb les seves columnes. Aquestes taules ens permetran saber quina és la clau primària de cadascuna de les taules per poder fer càrregues incrementals.

Per a cada origen de dades tindrem 4 paquets biml principals que aniran invocant a altres biml que es comportaran de manera diferent segons el tipus d'origen de dades.
  1.  000_GenerateMetadataParent.biml : per obtenir les metadates de l'origen
  2. 001_CreateTablesStagParent.biml : per generar els scripts per crear les taules a l'staging area
  3. 002_DropTablesStagParent.biml : per generar els scripts per esborrar les taules a l'staging area
  4. 004_LoadDataParent.biml : per generar els dtsx per carregar les dades des dels orígens a l'staging area.
 I excepcipnalment per BBDD en tindrem un cinquè: 003_CreateIndexStagParent.biml :per generar els scrips per crear els índex a les taules de l'staging area
Anem a pel codi. Implementarem el fitxer: 000_GenerateMetadataParent.biml

 Començarem explicant les parts comunes. De moment deixarem a banda les funcions UT_SQLServer_datatype.cs que ja l'explicarem quan el necessitem.

UT_getProject.biml

<# String _project = "ADVENTURE PROJECT"; #>
Aquest biml només té el nom del projecte.

 UT_connections.biml

 Aquest biml té com a objectiu recuperar les cadenes de connexió de l'staging area i del datawarehouse i posar-les en variables que podrem fer servir més tard.

UT_generateConnections.biml

 Aquí crearem les connexions del dtsx. A través de la consulta:
<#
string _sources_sql = "select s.source_id, s.source_name, t.source_type_name, s.hasParameter "+
       "from biml.source s "+
        "join biml.project p on p.project_id=s.project_id "+
        "join biml.source_type t on s.source_type=t.id_source_type "+
        "where project_name='"+_project+
        " order by source_id";
#>


Obtindrem les nostres fonts de dades i per cada una d'elles,segons el tipus (ara només SQL Server) crearem la connexió.
Podeu trobar el codi a: https://github.com/jordiisidro/adeu-biml/blob/master/UT_generateConnections.biml

En aquest punt també crearem la connexió a l'staging area. Biml és suficientment intel·ligent per que si una connexió no es fa servir dins el dtsx no la crei, encara que la definim aquí.

UT_paramExpression.biml

Aquest biml té el codi de definició dels paràmetres de projecte si és que es volen definir. Els paràmetres tindran el mateix nom que el que hem donat quan hem definit l'origen de dades- 
<# if (_param) { #>
    <Expressions>
        <Expression ExternalProperty="ConnectionString">@[$Project::<#= _table["source_name"] #>]</Expression>
    </Expressions>
<# } #>




UT_generateParameters.biml

Aquest biml definirà els paràmetres necessaris per als nostres orígens de dades i els packages que hi haurà dins del projecte. Per que biml reconegui un paràmetre s'ha de definir dins dels tags <Project><PackageProject><Parameters> i dins de <PackageProject> s'han d'enumerar els <Packages>

En el cas d'origen BBDD hem decidit crear un package per cada taula que volguem carregar. Per una banda ens generarà molts packages, però per l'altra seran més ràpids d'obrir que si en tenim un de sol amb totes les taules.
De moment amb la generació de metadata no ens afecta, només tindrem un dtsx per origen de dades.

UT_generatePackages.biml

  Aquest biml crearà el package principal que s'encarregarà d'invocar cada un dels subpackages per origen de dades.

UT_generateSubpackages.biml

Finalment aquí hi ha la lògica! Ja tocava.... Tot el que hem fet fins ara era per preparar la generació de paquets. Ara anem a construir.
<# if (_packageName =="GenerateMetadata"){ #> <#@ include file="GenerateMetadataDatabase.biml" #> <# } #>

 Per cada tipus d'origen de dades tindrem el nostre GenerateMetadata*.biml

Anem a veure que hi ha a dins del GenerateMetadataDatabase.biml

GenerateMetadataDatabase.biml

https://github.com/jordiisidro/adeu-biml/blob/master/GenerateMetadataDatabase.biml 
El que fa és atacar a les DMV de SQL Server per extreure la metadata.
L'ordre dels passos és:
  1. Esborrar les columnes dels índex
  2. Esborrar els índex
  3. Esborrar les columnes
  4. Esborrar les taules
  5. Carregar les taules
  6. Carregar les columnes
  7. Carregar els índex
  8. Carregar les columnes dels índex
  9. Actualitzar les PK a partir dels índex.
Al codi teniu com accedir a la metadata a través de les DMV.
Si generem el codi d'aquest conjunt de bimls (generant el de  000_GenerateMetadataParent.biml n'hi ha prou) ens crearà el dtsx per omplir la metadata dels nostres orígens de dades SQL Server.
Si executem el dtsx resultant ens omplirà les taules del nostre esquema. 

Totes ? En aquest cas sí, però no tots els atributs. N'hi ha alguns que s'han d'omplir manualment per que impliquen lògica de negoci.
En el cas de BBDD el camp is_pk només es generarà automàticament en cas que tinguem els índex clustered unique a l'origen, i el camp incremental_date_field l'haurem d'indicar manualment.
També haurem d'indicar per cada taula si volem que la càrrega sigui sencera o incremental (o el que vulguem definir en un futur). 


Això és tot en quant a la generació de metadata per una bbdd SQL Server.
Teniu tot aquest codi i alguna cosa més a:  https://github.com/jordiisidro/adeu-biml 

En els propers posts explicarem com generar els scripts de creació i esborrat de taules a l'staging area i, finalment, el que de veritat ens interessa, carregar les dades.
 

dissabte, 4 de juny de 2016

MCSE: Business Intelligence certification

Avui m'han confirmat la renovació per 3 anys de la certificació de Microsoft: MCSE: Business Intelligence certification !!
La veritat és que ha estat fàcil (alguns diuen que la recertificació és massa fàcil). Només he hagut de fer 8 cursos de la Microsoft Virtual Academy. Ha estat un luxe, sobretot després del que vaig suar per fer l'upgrade de MCITP de SQL Sever 2008 cap a MCSE. L'últim examen dels 3 (70-460) el considero un infern i no em feia cap gràcia haver-me de tornar a enfrontar amb ell.
Aquí teniu la info per tots els que us hagueu de recertificar: https://www.microsoft.com/en-nz/learning/recertification-virtual-academy.aspx
Jo fins al 2019 estic tranquil :-)

dijous, 2 de juny de 2016

SQL Server 2016

Ja el tenim aquí! Semblava que no arribaria mai la GA del SQL Server 2016. Després del canvi de 2012 a 2014 amb els índex columnars clustered ja tinc ganes de provar com va de rendiment el fet de poder tenir índex columnars barrejats amb índex B.

Aquí van les millores que publiciten:

1.      Microsoft is the recognized industry leader with Gartner Magic Quadrant leadership in Operational DBMS (ahead of Oracle, IBM and SAP), BI and Analytics and Data Warehousing.  
2.       Delivers the most secure & least vulnerable database, and unique security functionality with Always Encrypted that protects data both at rest and in motion.  This unique encryption capability also enables customers to stretch their database into Azure while retaining their encryption key on-prem, enabling hybrid cloud scenarios that are unmatched.
3.       Provides ground-breaking performance and scale as evidenced by the #1 performance benchmarks for the most mission critical workloads and analytics, plus everyday operations which are just faster.    
4.        Has everything built-in providing industry-leading TCO, nearly 12x saving relative to Oracle.  Combine this with the new Compete Database Migration offer and you have a compelling value proposition that Oracle customers will want to know more about. 
5.       New unique hybrid scenarios like Stretch Database helps customers keep more of their historical data at their fingertips and reduces storage costs.  This introduces accelerated hybrid cloud scenarios with Azure delivering faster backups and disaster recovery with Azure and reduced operational costs.
6.       Boasts THE fastest In-Memory technology on the planet across workloads with SQL Server 2016, and help our customers take performance and throughput to another level. See the outstanding benchmark results:  http://www.tpc.org/3321
7.       Enables mission critical intelligent applications delivering real-time operational intelligence by combining in-database advanced analytics (in R) and in-memory technology without having to copy/move the data or impact application performance. This is a first in the industry!
8.       Tackles Big Data workloads as only SQL Server delivers PolyBase technology built-in that allows customers to link structured and unstructured data and query over both with the simplicity of T-SQL that so many DBA’s are already familiar with. 
9.       SQL Server 2016 and Windows Server 2016 will offer higher availability, greater performance and faster analytics.  This will provide platform choice when we introduce SQL Server on Linux in the future where we already have 15K+ customers signed up to learn more. Any data, any size, any application and any platform is the new mantra!
10.      Is the only end-to-end mobile BI solution in the market that doesn't require the customer to pay for each named user. We can stand up 1,000 to 8,000 users on 8 cores, delivering a comprehensive BI solution at a fraction of the cost of Tableau and Qliktech.
I un bloc de referència:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/01/sql-server-2016-is-generally-available-today/

Si el BIML em deixa temps per fer proves ja les aniré explicant.

 

dimecres, 25 de maig de 2016

ADEU BIML

Tal com explicava a l'anterior post, la E i la L d'un procés ETL per a la càrrega d'un DataWareHouse (DWH) es poden automatitzar mitjançant patrons. Els propers posts es centraran en l'automatització de la part d'extracció de dades.

 

 ADEU BIML?

Després de vendre les qualitats de BIML no és que vulgui prescindir d'ell, al contrari. ADEU és l'acrònim de Automatic Data Extractor Utility. L'ADEU té la intenció de ser una utilitat per poder generar paquets dtsx a través de BIML que permetin l'extracció de dades  des de diferents orígens de dades i es bolquin a l'staging area (SA) del DWH. 
Per que pugui ser útil l'aplicació ha de ser el màxim extensible i reutilitzable possible. Cal tenir en compte que per programar aquesta aplicació he utilitzat la versió gratuïta de BIML integrada amb Visual Studio, no he utilitzat MIST.
El primer pas de l'aplicació ha estat dissenyar el model de metadata que emmatzemarà les característiques necessàries per poder crear els dtsx.


Les taules són les següents:
  • Project: conté el nom del projecte i la cadena de connexió a l'SA i al DWH
  • Source: origen de dades. Cada origen de dades tindrà una subclasse en que es definiran els atributs propis.
  • Source_type: tipus d'origen de dades (ex. BBDD SQL Server, fitxer pla, excel, etc)
  • Source_detail: elements del source (ex. taules de BBDD, fulls d'un excel). Cada origen de dades tindrà una subclasse en que es definiran els atributs propis.
  • Source_extract_type: tipus d'extracció de les dades (ex. full, incremental)
  • Source_column: columnes de l'origen de dades (columnes de la taula de BBDD, columnes del fitxer de text, etc).
Aquest esquema l'aniré ampliant amb les subclasses a mesura que afegeixi nous orígens de dades.
Per cada origen de dades es generaran 4 paquets principals:
  1. Generació de metadata: generació dels dtsx per alimentar els màxims camps possibles de l'esquema de metadata.
  2. Creació de les taules: a partir de la metadata es generaran els dtsx amb els scripts de creació de les taules a l'SA
  3. Esborrat de taules: ídem a l'anterior, però amb drop table
  4. Càrrega de dades: generació del dtsx que accedeix a l'origen de dades i carrega l'SA
Encara que la càrrega de dades pugui semblar la part important del projecte, és més important la generació de la metadata. Quan més automàtica sigui la generació de metadata menys feina haurem de fer en el futur. Sempre hi haurà parts que s'hauran d'alimentar a mà (ex. el camp que indica el source_extract_type, o el tipus de dades en un fitxer de text, o les columnes en un fitxer de text de mida fixa, etc).

Per cada origen de dades comentaré com s'ha generat el BIML per fer cada un dels paquets i també quina part de la metadata es pot generar automàticament i quina s'ha d'omplir a mà.

Esquema del BIML

Els fitxers .biml per a generar els 4 tipus de paquet tindran el mateix esquema:
  1. Includes de les funcions c#
  2. Nom del projecte
  3. Nom del tipus de paquet
  4. Obtenció de les connection strings del projecte
  5. Tag <Biml>
  6. Generació de les connexions d'orígens de dades del projecte 
  7. Generació dels paràmetres
  8. Generació dels packages "pares" per cada origen de dades
  9. Generació dels subpackages per cada source_detail  

  En el proper post començaré a entrar en detalls de la implementació. De moment el primer que es pot veure és que es poden incloure fitxers biml dins d'altres fitxers biml, això permetrà reutilitzar codi i fer l'aplicació extensible. Quan s'implementin nous origens de dades "només" haurem de crear els nous bimls i invocar-los dins del codi.

dijous, 19 de maig de 2016

Benvingut BIML

BIML (Business Intelligence Markup Language) és un llenguatge de programació que permet generar paquets dtsx d'SSIS automàticament. BIML és propietat de Varigence (www.varigence.com).
BIML permet combinar codi XML amb .Net (Vb o C#) per generar les ETLs. Aquesta combinació ens permet aplicar patrons de disseny o templates per generar per a taules d'un mateix tipus la mateixa estructura en l'ETL.

BIML té una part que és gratuïta i que es pot integrar al SQL Server Data Tools a través del BIDS Helper (https://bidshelper.codeplex.com/) o també el BIML express. I també té una part de pagament MIST que ofereix funcionalitats com fer enginyeria inversa o més facilitat a l'hora de debugar.


DWH i BIML

A quines parts de la càrrega d'un DWH ens pot ajudar MIST?
En un procés de càrrega d'un DWH tenim les fonts d'origen que es carreguen a l'Staging Area (Extract). A l'Staging Area s'aplica la lògica de negoci fins a transformar les dades en l'estructura que té el DWH (Transform), des d'on, finalment, les dades passen al DWH (Load).
La E i la L del procés ETL és una part que no aporta valor a negoci i que es pot automatitzar mitjançant patrons d'extracció o de càrrega.

BIML ens permetrà aplicar els patrons necessaris per als nostres orígens i destins per no haver de programar el mateix procés per totes les taules.

 Un cas real 

En un dels meus darrers projectes havia de carregar a l'Staging Area més de 150 taules. Les taules eren de 3 tipus. Les que havia de carregar senceres, les que havia de carregar incrementalment segons un camp de data i les que havia de carregar incrementalment segons un camp de data que estava en una altra taula.
Amb BIML vaig escriure només 3 XMLs i amb un bucle amb c# vaig poder generar les ETLs que em carregaven totes les taules. Vaig aconseguir diversos beneficis. Per una banda no em vaig tornar boig programant les 150 càrregues de taules amb els seus possibles errors humans, i per una altra banda vaig poder corregir errors de concepte només tocant el codi BIML sense haver d'anar a cadascuna de les ETLs afectades.

En propers posts veurem alguns exemples senzills de codi BIML.
Us recomano molt el blog de la Catherine Wilhelmsen : http://www.cathrinewilhelmsen.net/ i també que li feu un cop d'ull a la web http://bimlscript.com/ 

dilluns, 16 de maig de 2016

Retornar part del que he rebut

Després de més de 10 anys al món del Business Intelligence ja va sent hora que comenci a compartir a la comunitat el que vaig aprenent. Per mandra, vergonya, falta de temps o qualsevol altra excusa no ho he fet.
Probablement el punt d'inflexió ha estat trobar-me de cara amb BIML (Business Intelligence Markup Language) i els primers posts aniran enfocats a veure per què ens pot ser útil. També vull parlar de novetats de BI, sobretot de la part de Microsoft que és on em moc més habitualment a la feina, però també de la SDK de BO i altres cosetes que vagin sortint.

J.