Emmascarar noms de taules i camps MDS

En el passat post veiem com migrar models entre entorns o entre versions. Un dany col·lateral de la migració és que els noms de les taules i dels camps que genera automàticament l'MDS. Això ens impacta a l'hora de fer ETLs o altres processos que utilitzin les taules d'MDS. Una solució es crear una vista que emascari aquests noms variables, però aquest procés ha de ser el més automàtic possible per evitar accidents humans.
Avui veurem un procediment per poder crear automàticament les vistes. Aquest procediment utilitzarem les sentències que vam veure en el post: http://www.eljordifabi.tech/2018/03/crear-una-vista-en-una-altra-bbdd.html

Migració MDS I (movent models entre servidors)

Quan treballem en diversos entorns, o bé hem de fer una migració de versió de MDS, per moure/migrar els models no ho podem fer copiant directament les dades d'una BBDD a una altra. Si és la mateixa versió per que no podem garantir que els ids siguin els mateixos (pot haver agents externs que hagin afegit models, per exemple) i en una migració per que les taules no són les mateixes ni tenen la mateixa esructura.
Per tal de fer el moviment de models entre servidors utilitzarem la utilitat MDSModelDeploy.

MDSModelDeploy està a la carpeta c:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration
En el cas de tenir el servidor en SQL Server 2014, si és SQL Server 2016 la carpeta és la 130.

El primer que hem de fer és trobar el nostre servei fent un listservices.

En el nostre cas és el MDS5 (MDSPRO).

Master Data Services (V)

Avui veurem una característica de MDS que ens pot ser útil quan volen guardar fotos inamobibles: el versionat. Podem generar versions dels models per tal i que es quedin en estat només lectura i modificar altres versions.
Si volem crear una nova versió ho hem de fer des del navegador a l'opció Version Managment

Per poder veure aquest apartat hem de tenir permisos de Version Management (menú User and Group Permissions).
Dins de Version Management triarem el model a versionar.

 En aquest punt ens hem de fixar en 2 columnes:
- Status: ens indica si la versió és modificable.
- Validation: ens indica si la versió està validada. Validada significa que s'ha passat el test de validació per que compleixi totes les regles que hem definit.

Per poder validar una versió ha d'estar bloquejada (Lock). L'status canvia a Locked.
Des del menú Validate Version podem validar-la si no ho està, i un cop validada fer Commit.

Un cop fer el commit ja no podrem modificar la versió. El que sí que podrem fer és copiar-la.
Aquesta còpia serà la que podrem modificar. A la taula ens indica a partir de quina versió s'ha creat

Podem canviar el nom de la versió fent doble-click a la columna Name de la versió a reanomenar.

Això és tot sobre les versions. Sobretot molt útils quan hem de deixar dades a la força de només lectura.

Master Data Services (IV)

Després de veure diferents maneres de carregar les taules creades amb el Master Data Services avui veurem algunes consultes útils sobre la metadata.

Quan creem una nova entitat del MDS ens crea una taula dins de la BBDD de MDS, però no són noms intuïtius com el de les taules stag.

La 1a consulta serveix per saber els models creats, la data de creació i modificació i quina és la seva versió activa.

select
 m.id, m.Name, m.EnterDTM, m.EnterUserID, m.LastChgDTM, m.LastChgUserID,
mv.id as idLastVersion
from
mdm.tblModel m
join (select Model_ID, id from [mdm].[tblModelVersion] where status_id=1) mv on m.id=mv.Model_ID

 La 2a consulta serveix per saber les taules d'un model i entitat.

select
e.id, e.Name, e.EntityTable, e.SecurityTable,  e.StagingBase
from
mdm.tblModel m
join [mdm].[tblEntity] e on e.Model_ID=m.ID
where m.Name='Control de gestión'

Un cop tenim les taules veiem que els seus atributs tampoc tenen un nom amigable.

 Amb la 3a consulta podem veure el mapeig entre els noms de les columnes de les taules amb els noms que hem definit nosaltres al MDS.

select
e.id, e.Name,
a.id, a.MemberType_ID, a.DisplayName, a.TableColumn
from mdm.tblModel m
join [mdm].[tblEntity] e on e.Model_ID=m.ID
join [mdm].[tblAttribute] a on a.Entity_ID=e.ID
join (select Model_ID, id from [mdm].[tblModelVersion] where status_id=1) mv on m.id=mv.Model_ID
where m.Name='Control de gestión'
and e.name='Vuelos'

 

Amb aquestes 3 consultes podem extreure la metadata bàsica per poder atacar a les taules creades pel MDS.

MDS té la capacitat d'auditar els canvis de dades fets. Per poder consultar-los podem executar la query:

SELECT *
FROM [MDS].[mdm].[tblTransaction]
where Version_ID=5
and Entity_ID=12
and Attribute_ID=360
order by lastChgDTM 

On Version_ID és la versió a consultar. Entity_ID és l'ID retornat de la 2a consulta i Attribute_ID és l'ID retornat a la 3a consulta. En aquesta taula hi ha les columnes OldValue i NewValue per veure com han canviat els valors. Per veure el canvi en una fila concreta podem filtrar per la columna MemberCode.
En aquesta taula també es guarda l'id de l'usuari que ho ha modificat i que es pot creuar amb la taula [mdm].[tblUser] . Ara ja tenim als usuaris controlats i ja no podran dir que no han tocat res ;)

Avui hem vist les principals taules de metadata del MDS. En propers posts veurem la gestió de Versions i regles.

Master Data Services (III)

En l'anterior post vam veure com crear Models, Entitats i Atributs en MDS. Avui veurem com carregar les dades.
Hi ha 3 maneres de carregar les dades:
La primera és des de la interfície web d'MDS a través de l'Explorer.

A part de carregar dades, podrem veure les que ja hi ha al sistema.

És una forma senzilla de carregar dades, no necessites tenir cap producte instal·lat (excepte Silverlight), però has de carregar les files una per una.

La segona manera és a través de BBDD. Quan es crea una entitat es crea a la vegada una taula a l'esquema stg de la BBDD MDS amb les mateixes columnes (i mateix nom) que els atributs de l'entitat. Hi ha unes columnes extres importants:
- ImportType: indica l'acció a realitzar a les files de la taula (inserir, esborrar, desactivar...)
- BatchTag: agrupa un conjunt de files a processar de cop

Un cop carregada la taula executarem el stored procedure stg.udp_<nom taula stg> per processar les files. Els paràmetres del procediment són:
- VersionName: Versió sobre la que s'aplicaran els canvis.
- LogFlag: si els canvis es guardaran a la taula de log.
- BatchTag: el tag de dades a processar.

Us adjunto els links amb tota la documentació:

https://msdn.microsoft.com/en-us/library/ee633854(v=sql.120).aspx

https://technet.microsoft.com/en-us/library/ee633726(v=sql.120).aspx

https://technet.microsoft.com/en-us/library/hh231028(v=sql.120).aspx

Un cop carregades les dades es poden consultar amb l'Explorer per veure si s'han carregat correctament. 

Aquest mètode de càrrega de dades és menys intuïtiu, però permet fer càrregues massives de dades de forma senzilla.

La tercera manera, i la preferida pels usuaris, és a través de l'add-in de l'Excel.

Des de la plana principal de MDS hi ha un link per baixar-vos l'add-in. Un cop instal·lat us apareixerà com a una nova pestanya al ribbon.

 

Amb l'addin ens podrem connectar a MDS (amb la mateixa URL amb la que ens connectem via web), triar un model i versió, i importar una entitat.

Quan ens connectem a l'add-in no ens deixa triar ni tipus d'autenticació, ni usuari. Utilitza l'usuari de la màquina que ha obert l'Excel. Si ens volem impersonar ho podem fer a través de la comanda runas de cmd. Per exemple: 

runas /netonly /user:sql2014biml\jordi.isidro "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE"

 

Un cop importades les dades les podrem modificar o afegir i, un cop acabat, clicar a Publish per pujar-les a MDS. Pes esborrar s'ha de seleccionar tota la fila sencera i clicar a Delete.

L'acció delete s'executa internament fila per fila. Si esborrem moltes files l'acció pot ser lenta.


MDS no està pensat per grans volums de dades (centenars de milers de files) i el seu rendiment baixa molt en aquests casos, sobretot a l'importar les dades des de l'add-in o al publicar moltes files a la vegada.

El proper dia veurem consultes útils per saber quines estructures ens ha creat internament el MDS quan hem creat Models, Entitats i Atributs.

Master Data Services (II)

En el passat post vam veure què era i per què servia el component Master Data Services d'SQL Server. Avui veurem quines estructures de dades estan disponibles.
L'estructura principal de MDS és el model, seria equivalent a una BBDD. Cada model conté una o més entitats, que serien equivalents a les taules. I cada entitat té atributs, que serien equivalents al les columnes d'una taula de BBDD.
Per crear un model nou hem d'anar a l'url de MDS (ex. http://<servidor>/MDS/default.aspx) a l'apartat "System Administration"

I després a Manage --> Models

 

Des d'aquest menú podrem crear nous models o editar el nom dels models existents.

Des del mateix menú Manage podem seleccionar Entities per afegir entitats als models.

 

Al crear una nova entitat hi ha 2 punts importants:
- Name for staging tables, que serà el nom de la taula staging que ens servirà per poder fer càrregues massives (ho veurem al proper post)
- Create Code values automatically. Per defecte tota entitat té 2 atributs: Code, que és la Primary Key, i name. En aquest punt escollirem si el code volem que sigui autogenerat o no.

Si anem a editar una entitat podrem modificar els seus atributs.

Quan afegim un nou atribut podrem triar el seu tipus de dades, longitud, domini, format, etc. La majoria d'aquests atributs no són editables, així que penseu-vos-els bé abans de crear-los.

Amb això ja podeu crear les estructures bàsiques per poder començar a operar amb MDS. En un nivell més avançat es poden crear jerarquies, business rules, etc.

Us recomano utilitzar Internet Explorer en la consola de MDS ja que ni amb Chrome ni Firefox el format es veu bé (ex. alguns camps deshabilitats no es veu que estan deshabilitats) , ni totes les funcions javascript funcionen.

En el proper post veurem com omplir de dades les entitats que hem creat als nostres models.

Master Data Services (I)

Odio l'Excel. Si no ho dic com a mínim un cop al dia és que no he treballat prou.
Amb l'SSIS s'ha de vigilar amb els drivers...que si 32-bit o 64-bit...si toquen el nom d'una columna ja falla tot...els tipus de dades els agafa sobre una mostra i si hi ha un tipus de dades diferent falla la càrrega....etc....etc....etc
També odio als usuaris que tenen les seves dades guardades en Excels i que acaben creuant amb les dades del DWH, però només tenen ells les dades. I després volen que les coses quadrin!

Com a possible solució als dos problemes hi ha un component infrautilitzat d'SQL Server, el Master Data Services (MDS) (https://msdn.microsoft.com/en-us/library/ee633763(v=sql.120).aspx).
Per a l'usuari és un plug-in d'excel que li permet introduir i compartir dades fàcilment, guardar versions. Per al desenvolupador d'ETL és una taula de BBDD que no té els problemes de l'Excel.

MDS està pensat per que les empreses tinguin una sola realitat de les dades mestres, ja que la informació emmagatzemada es pot compartir fàcilment a diversos usuaris, però al final és una bona manera de compartir tant dades mestres com qualsevol altre tipus de dades.

MDS té un component web, que és des d'on es gestionen les estructures, permisos, versions, etc, i un plug-in d'Excel, des d'on es poden introduir les dades fàcilment.

 

Per instal·lar el MDS es fa des del setup normal de l'SQL Server. Si no voleu tenir problemes amb el servidor d'IIS us recomano executar les següents comandes en Power Shell:

Install-WindowsFeature Web-Mgmt-Console, AS-NET-Framework, Web-Asp-Net, Web-Asp-Net45, Web-Default-Doc, Web-Dir-Browsing, Web-Http-Errors, Web-Static-Content, Web-Http-Logging, Web-Request-Monitor, Web-Stat-Compression, Web-Filtering, Web-Windows-Auth, NET-Framework-Core, WAS-Process-Model, WAS-NET-Environment, WAS-Config-APIs 
 
Install-WindowsFeature Web-App-Dev, NET-Framework-45-Features -IncludeAllSubFeature –Restart 

 

Un cop instal·lat heu d'anar al configuration manager per escollir el site web que utilitzareu i la BBDD on es guardaran les dades de MDS.

 

El proper dia veurem les estructures de dades que té MDS i com es tradueixen en taules de BBDD.