13 Setembro 2016

Master Data Services – Um Guia Prático

Master Data Services (MDS) é uma ferramenta do SQL Server para Master Data Management (MDM) constituída por vários componentes, entre os quais uma aplicação web (Master Data Manager) e um add-in para Microsoft Excel. Utilizando a primeira, é possível criar e administar modelos ou regras de negócio, e o add-in permite gerir dados e criar novas entidades e atributos diretamente a partir do Microsoft Excel.

Através deste guia prático, será possível seguir os passos para criar modelos, entidades, atributos e hierarquias, bem como gerir os dados através do add-in para Excel. Mas antes, é importante compreender alguns conceitos básicos:

  • Modelo – Funciona como um “contentor” de dados. É o nível mais alto de organização dos dados, definindo a sua estrutura. Contém entidades que, por sua vez contêm atributos, hierarquias e coleções.
  • Entidade – Objeto contido num modelo. Contém membros, que correspondem às linhas de dados e atributos que, por sua vez, correspondem às colunas. Entidades podem conter hierarquias explícitas, coleções, ou ser usadas para criar hierarquias derivadas.
  • Membro – Dados físicos de uma entidade (ex: um dado cliente na entidade “Cliente”), descritos pelos atributos dessa entidade.
  • Atributo – Objeto contido numa entidade. Os atributos descrevem os membros de uma entidade (ex: “Nome” ou “Morada” na entidade “Cliente”) e podem ser organizados em grupos.
  • Atributo com base no domínio – Atributo cujo valor provém de um membro de outra entidade (ex: entidade “sub-categoria” tem um atributo “categoria” que é um atributo com base no domínio, provindo os valores deste atributo da entidade “categoria”).
  • Hierarquia explícita – hierarquia baseada numa única entidade, estruturada de maneira especificada pelo utilizador.
  • Hierarquia derivada – hierarquia que deriva de relações entre atributos com base no domínio já existentes num modelo. Os membros de uma entidade são assim usados para agrupar os membros de outra entidade.
  • Colecção – Grupo de membros de uma entidade.
  • Vista de subscrição – Vista de SQL na base de dados MDS; pode ser criada e mantida a partir da página de MDS. As vistas de subscrição são criadas quando se pretende criar uma vista dos dados para uso por um sistema de subscrição.
  • Sinalizador de versão – Serve para indicar a versão do modelo que os subscritores ou sistemas de subscrição devem usar.

 

De seguida são apresentados os passos básicos que permitem a criação de estruturas.

 

Criação de estruturas

Criar um modelo

Na página de MDS aceder à área “Administração de Sistema"

Em “Gerir”, clicar em “Modelos”

Clicar em “adicionar modelo” (“+”)

Inserir um nome para o modelo e clicar em “guardar modelo” (opcionalmente podem ser selecionadas as opções de criar entidade com o mesmo nome, criar hierarquia com o mesmo nome, e hierarquia obrigatória)

Criar uma entidade

Clicar Em “Gerir” > “Entidades”

Selecionar um modelo da lista de modelos e clicar em “Adicionar entidade” (“+”)

Inserir um nome para a entidade (opcionalmente pode atribuir-se outro nome para tabelas de teste). Pode optar-se por criar ou não valor de código automaticamente, e qual o valor em que se iniciam (>=1), e escolher se queremos ou não coleções e hierarquias (pode alterar-se posteriormente). Clicar em guardar.

Criar um atributo

Na página “Manutenção da Entidade” (“Gerir” > “Entidades”), escolher o modelo e selecionar a linha da entidade para a qual se quer criar o atributo. Clicar em “Editar entidade selecionada”

Clicar em “adicionar atributos de folha” (“+”)

Escolher entre “forma livre”, “com base no domínio” ou “ficheiro”. Para atributos de forma livre, escolher um nome para o atributo, o tipo de dados e comprimento, bem como a largura de pixéis de apresentação – a largura com que a coluna vai ser apresentada. Clicar em guardar, e em seguida na página da entidade clicar em guardar entidade. Deve escolher-se a opção “com base no domínio” quando se pretende criar um atributo baseado num atributo de outra entidade (ver conceitos). Já a opção “ficheiro” deve ser selecionada quando se pretende utilizar um ficheiro como atributo (ex: fotografia).

Criar uma hierarquia derivada

Clicar em “Gerir” > “Hierarquias derivadas”, selecionar o modelo e clicar em “adicionar hierarquia derivada” (“+”). Atribuir um nome à hierarquia e clicar em “guardar hierarquia derivada”.

Na página “Editar a Hierarquia derivada”, clicar numa entidade ou hierarquia e arrastar para o campo “Níveis atuais”. Construir a hierarquia. Clicar em “Anterior”.

Gestão e visualização de estruturas

Criar sinalizadores de versão

Na página de MDS, clicar em “Gestão de versões”.

No menu, passar o rato sobre “Gerir” e escolher a opção “Sinalizadores”.

Clicar em “Adicionar”. Escolher um nome, uma descrição, e se é pretendido ou não apenas versões consolidadas (ao escolher falso, o sinalizador pode ser atribuido a versões com qualquer estado). Clicar em guardar.

Atribuir um sinalizador a uma versão

Na página “Gestão de versões” (ver acima), na versão pretendida, duplo-clique na célula relativa à coluna “Sinalizador”.

Escolher um sinalizador da lista de sinalizadores existentes.

Carregar na tecla “Enter” para guardar as alterações.

Criar uma vista de subscrição (“subscription view”)

Na página de MDS aceder à área “Gestão de integração”.

Selecionar “Criar vistas” no menu.

Clicar em “adicionar vista de subscrição” (“+”)

Adicionar uma nome para a vista de subscrição, escolher um modelo, escolher a versão ou a opção “sinalizador de versão” (esta última é a recomendada e permite reatribuir a vista a uma nova versão aberta, quando se bloqueia uma versão, sem ter de atualizar a vista de subscrição) e escolher os restantes parâmetros sobre os quais se pretende criar a vista. Clicar em “guardar”.

Manutenção e gestão dos dados – Master Data Services Add-in for Microsoft Excel

O Add-in para Microsoft Excel, disponível através da página de MDS, permite a gestão e distribuição dos dados organizados via MDS por qualquer pessoa numa organização, permitindo o carregamento destes dados em Excel. Os dados podem assim ser trabalhados como quaisquer outros dados em Excel e, no final, carregados de volta para o MDS, sendo armazenados numa base de dados (SQL Server). Um administrador pode utilizar o Add-in para criar entidades e atributos e introduzir dados relativos aos mesmos. Data Quality Services (DQS) podem ser utilizados para validar se os dados já se encontram no MDS e evitar duplicados.

Criar uma ligação e ligar ao repositorio MDS utilizando o Excel

Após instalar o Add-in, abrir o Excel. Irá aparecer uma nova aba “MasterData”. Nesta aba, clicar na seta por baixo de “Connect” e seleccionar “Manage Connections”.

Na nova janela selecionar “Create a new connection” e clicar no botão “New”.

Adicionar uma descrição e o endereço do servidor MDS, e clicar “Ok”.

Selecionar a conexão e clicar em “Test” para testar a conexão. Por fim clicar em “Connect”.

Importar dados do repositório MDS

Ligar ao repositório MDS. Se já existirem conexões criadas, clicar na seta por baixo de “Connect” e escolher a conexão pretendida.

Selecionar um modelo e versão no painel “Master Data Explorer” (se este painel não aparecer, clicar em “Show Explorer”

É possível filtrar os dados antes de importar, escolhendo uma entidade no painel “Master Data Explorer” e clicando em “Filter” na aba “MasterData”. Desmarcar os atributos que não se quer importar, e adicionar filtros de linha, se necessário. Por fim, clicar em “Load Data”. Se não se quiser aplicar filtros, basta fazer duplo clique sobre a entidade a importar.

Notas:

Cada sheet vai representar uma entidade/tabela do modelo, sendo que o painel “Master Data Explorer” não estará disponível se a sheet aberta já contiver dados MDS. Para importar dados de outra entidade, é necessário criar uma nova sheet.

É possível guardar um atalho para um conjunto de dados, através do botão “Save Query”, opção “save as query”. Para aceder aos dados através do atalho, ir à opção “Manage Queries” do mesmo botão e clicar em “Load”. Também é possível enviar um atalho como ficheiro por email, através da opção “Send Query”.

Se houver regras de negócio aplicadas a uma entidade, clicar em “Apply Rules” para fazer a validação dos dados.

Publicar dados no repositório MDS

Quando são feitas alterações ou adicionados dados a uma entidade, é possível publicar os dados no repositório MDS. De notar que quando uma entidade é definida para ter códigos gerados automaticamente, não é necessário especificar o atributo “código” da entidade em questão.

Para publicar os dados, clicar em “Publish” na aba “MasterData”. Se aparecer a janela “Publish and Anotate”, clicar “Publish” outra vez (adicionar comentários se pretendido).

É efetuada uma validação dos dados, e se houver erros, é possível ver a descrição do erro (botão “show status”).

Gerir os dados publicados

Rever alterações

É possível rever todas as transações efetuadas e alterações aos dados publicados, bem como anotações que tenham sido adicionadas, selecionando uma célula da linha cuja transação se pretende visualizar, e em seguida clicando com o botão direito do rato, e selecionando “View Transactions”.

Uma janela como a mostrada abaixo irá aparecer. É possível ainda inserir novas anotações para cada linha.

Apagar linhas (membros)

É possível apagar dados publicados, selecionando uma ou várias linhas completas (carregando no cabeçalho/número da linha) e clicando no botão “Delete” da aba “Master Data”. Irá aparecer uma caixa de diálogo a confirmar se é pretendido apagar as linhas selecionadas. Clicar sim.

 

Nota: Quando um membro é apagado, o código do mesmo não volta a ficar disponível para um novo membro. Para apagar registos da base de dados e não apenas da entidade, é necessário executar um Stored Procedure gerado pelo MDS, fornecendo-lhe os parâmetros necessários, que vai apagar ou atualizar registos das tabelas respetivas com base nesses parâmetros (Ver secção “Criar, atualizar, apagar ou inativar membros folha”).

Combinar dados

É possível combinar dados MDS com outros dados. Para tal, na sheet que contém dados MDS, clicar no botão “Combine Data”.

Na caixa de diálogo que aparece, escolher o intervalo de dados a combinar (em “Range to combine with MDS data” clicar no ícone para minimizar a janela (assinalado com uma seta na imagem abaixo), clicar na sheet com os dados a combinar, e selecionar o intervalo de dados que se quer combinar, incluindo cabeçalhos; voltar a clicar no ícone para a caixa de diálogo expandir), selecionar as colunas a combinar e clicar “combine”.

 

Uma nova coluna “SOURCE” irá aparecer, para indicar quais os dados que provém de fonte externa e quais provém do MDS.

 

Criar uma entidade utilizando o Add-in para excel

É possível criar uma nova entidade num modelo existente a partir do Excel diretamente, através do botão “Create entity” da aba “Master Data”. Para tal, criar uma nova sheet com os dados que se pretendem carregar para o MDS, incluindo cabeçalhos. Selecionar as células com os dados a carregar. Clicar no botão “Create Entity” da aba “Master Data”. Na janela que aparece, selecionar o modelo e a versão a que se pretende adicionar a entidade. Escolher um nome para a entidade, e selecionar qual a coluna com o código (pode escolher-se a opção “gerar código automaticamente”) e qual a coluna com o nome. Por fim clicar “ok”.

Criar um atributo utilizando o Add-in para excel

Para criar um novo atributo através do Excel, basta adicionar uma nova coluna a uma entidade existente e clicar em “Attribute Properties” da aba “Mater Data”. Na nova janela, escolher o nome do atributo, o tipo de atributo e respetivas propriedades consoante o tipo escolhido. De seguida clicar em “ok”.

Criar um atributo com base no domínio

Para criar um atributo com base no domínio, no tipo de atributo (na janela referida acima) escolher a opção “Constrained list (Domain-based)”. Na opção “ Populate the attribute with values from” selecionar a entidade a partir da qual se pretende popular o novo atributo. Escolher “the selected column” se se pretender utilizar dados da própria sheet. Clicar “ok”. Em cada célula do atributo aparecerá uma “drop-down” que permite escolher um valor para aquele atributo.

Criar, atualizar, apagar ou inativar membros

Leaf member staging table

Para criar, atualizar, apagar ou inativar membros folha (membros folha são os membros por defeito; membros consolidados existem apenas quando hierarquias explícitas e coleções estão ativadas para a entidade em questão) é necessário preencher uma tabela de staging (“stg.name_Leaf”, onde “name” é o nome definido quando se criou a entidade). Também existem tabelas de staging para os membros consolidados e para as relações.

Abaixo encontra-se descrito para que serve cada um dos campos (colunas) da tabela:

  • ID

Identificador gerado automaticamente.

  • ImportType (Requerido)

Determina a tarefa a executar quando os dados nesta tabela já existem na base de dados MDS. Os valores possíveis e respetiva explicação encontram-se definidos na tabela abaixo:

ImportType Descrição
0 Cria novos membros. Substitui os dados MDS existentes pelos da tabela de staging (se não forem nulos). Valores nulos (NULL) são ignorados.
1 Cria novos membros apenas. Não atualiza dados existentes.
2 Cria novos membros. Substitui os dados MDS existentes pelos da tabela de staging, inclusive valores NULL.
3 Desativa o membro, com base no seu valor de Código, deixando este de estar disponível para o utilizador (não sendo, no entanto, apagado). Se o membro for utilizado como atributo com base no domínio, a desativação irá falhar (Ver opção 5 como aternativa).
4 Apaga permanentemente o membro, com base no seu valor de Código. Se o membro for utilizado como atributo com base no domínio, a eliminação irá falhar (Ver opção 6 como aternativa).
5 Desativa o membro, com base no seu valor de Código, deixando este de estar disponível para o utilizador (não sendo, no entanto, apagado). Se o membro for utilizado como atributo com base no domínio, os valores relacionados irão ser definidos como NULL.
6 Apaga permanentemente o membro, com base no seu valor de Código. Se o membro for utilizado como atributo com base no domínio, os valores relacionados irão ser definidos como NULL.
  • ImportStatus_ID (Requerido)

O estado do processo de importação. Os valores possíveis são:

0: indica que o registo está pronto para o processo de staging.

1: é atribuído automaticamente e indica que o processo de staging para o registo foi bem sucedido.

2: é atribuído automaticamente e indica que o processo de staging para o registo falhou.

  • Batch_ID (Requerido pelo serviço web apenas)

Um identificador atribuído automaticamente que agrupa os registos para o processo de staging. Os registos do mesmo batchficam assim com o mesmo identificador.

  • BatchTag (Requerido, excepto pelo serviço web)

Um nome único para o batch.

  •  ErrorCode

Mostra um código de erro.

  • Code (Requerido, excepto quando os códigos são gerados automaticamente)Um código único para o membro.
  • Name (Opcional)

Um nome para o membro.

  • NewCode

Utilizado apenas quando se pretende alterar o código de um membro.

  •  <Nome do atributo>

Uma coluna por cada atributo. Utiliza-se com um ImportType 0 ou 2. Para atributos livres, especificar o novo valor para o atributo. Para atributos com base no domínio, especificar o código do membro que será o atributo.

 

Staging Stored Procedure

Após a tabela de staging ser preenchida, é utilizado um stored procedure – “stg.udp__Leaf”, para membros folha, onde “name” é o nome da tabela de staging definido quando se criou a entidade (“stg.udp__Consolidated” para membros consolidados e “stg.udp__Relationship” para relações). Este stored procedure requer vários parâmetros, que se encontram listados na tabela abaixo.

Parâmetro Descrição
VersionName

Requerido

Nome da versão. Pode ser ou não case-sensitive, dependendo das definições do SQL Server.
LogFlag

Requerido

Determina se as transações são ou não registadas durante o processo de staging. Os valores possíveis são:

0: Não regista transações;

1: Regista transações.

BatchTag

Requerido, excepto pelo serviço web

BatchTag especificado na tabela de staging.
Batch_ID

Requerido pelo serviço web apenas

O  Batch_ID especificado na tabela de staging.
User Name Parâmetro opcional.
User ID Parâmetro opcional.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Este stored procedure vai assim permitir criar, atualizar, apagar ou inativar membros, com base nos campos preenchidos na tabela de staging.

Para maior detalhe ver https://msdn.microsoft.com/en-US/library/hh231028.aspx

Automatização do processo

Para facilitar o preenchimento da tabela de staging e correr o stored procedure, fornecendo-lhe os parâmetros requeridos, é possível usar um outro stored procedure que executa estas tarefas, ou criar um package de SSIS que automatiza o processo. Esse package SSIS terá uma estrutura semelhante à mostrada nas imagens abaixo.

 

No control flow, deverá existir um data flow e um objeto do tipo Execute SQL Task, sendo que neste último é executado o stored procedure referido (exemplo: “EXEC [stg].[udp_Cliente_ClientePai_Leaf]  @VersionName = ?, @LogFlag = ?, @BatchTag = ?”), fornecendo-lhe os devidos parâmetros, previamente definidos como variáveis no package:

data flow terá um data source, um objeto “Derived Column” e um “OLE DB Destination” e é onde se executa o preenchimento da tabela de staging.

Na derived column são adicionadas colunas aos dados provenientes da fonte de dados, para preencher a tabela de stagingcom os valores requeridos:

A tabela de destino é a tabela de staging, sendo definidos os respetivos mapeamentos no objeto de destino:

Assim, no exemplo acima a tabela de staging é preenchida com base numa fonte de dados e em duas colunas adicionadas (ImportType = 2, que define que se pretende adicionar novos membros e atualizar membros antigos; BatchTag, que identifica o batch em questão) e em seguida é executado o stored procedure que, com base no BatchTag, no nome da versão e na LogFlagvai proceder ao ImportType definido para cada membro com esses parâmetros.

Com base na lógica aplicada neste package, é possível criar um stored procedure que executa as mesmas tarefas, em alternativa.

 

.
.
.
.
.
.
Adriana Policarpo
      Consultant