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/

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/

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 

Aquesta funció la podeu trobar a: https://paultebraak.wordpress.com/2015/01/14/biml-xix-creating-a-staging-area-using-biml/

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/

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.
 

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

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.