Acesso Direto ao Snowflake

Nota: Para saber mais sobre Relatórios Personalizáveis, consulte o artigo O que são Relatórios Personalizáveis?.

Sobre o Acesso Direto ao Snowflake

A plataforma Alchemy Training System utiliza o Snowflake, uma solução de data warehouse extremamente robusta. Para clientes com operações sofisticadas de Business Intelligence (BI) ou Business Analytics (BA), a Alchemy oferece a possibilidade de conexão direta ao Snowflake, permitindo que as organizações visualizem, consultem e analisem diretamente todos os seus dados de treinamento.

O Snowflake Direct Access oferece análises e relatórios exclusivos sobre os treinamentos que você realiza via Intertek Alchemy. Com isso, você pode:

• Construir suas análises de forma eficiente.
• Combinar facilmente os dados do Alchemy com conjuntos de dados de outros sistemas.
• Descubrir insights e tendências valiosas.

Os clientes podem acessar a nuvem de dados Snowflake do Alchemy Manager de duas maneiras:

1. Conta do cliente: esta opção destina-se a clientes que possuem sua própria conta Snowflake e relacionamento de faturamento. Esta opção exige que o cliente tenha seus dados armazenados na região us-east-1 da AWS.
2. Conta de Leitor Alchemy: esta opção é onde a Alchemy cria e gerencia uma conta de leitor para o cliente e toda a cobrança é feita pela Alchemy.

Seja usando uma das interfaces web do Snowflake ou uma conexão de rede direta com o Snowflake, seu acesso aos dados é realizado com SQL, portanto, você precisará de alguém que tenha conhecimento em usá-lo. O nível de expertise em SQL que você precisa será determinado pelo que está tentando realizar, mas pode facilmente exigir o uso de múltiplos JOINs, múltiplas cláusulas WHERE e uma lista extensa de campos SELECT.

Modelos de Dados

Os Modelos de Dados (DMs) são as visualizações dos seus dados Alchemy aos quais fornecemos acesso direto. Seus Relatórios Personalizáveis e painéis utilizam esses DMs para gerar os dados. Eles fornecem uma versão dos dados do aplicativo, que simplifica a recuperação e apresenta excelente desempenho na análise dos dados.

Os campos "chave" são usados para identificar os principais objetos no banco de dados. Eles podem ser usados para identificar objetos únicos e também são usados com um "join" para trazer dados adicionais.

DM_DETALHE_ESTUDANTE

Esta tabela contém dados extensivos sobre cada aluno. Você pode consultá-la diretamente assim:

select user_full_name, user_id, user_worksite, date_of_hire, user_groups from dm_student_detail order by date_of_hire desc nulls last limit 20

As Colunas da Lista de Grupo

Os grupos aos quais o usuário pertence podem ser encontrados nos campos a seguir.

• USER_GROUPS contém grupos do tipo Corporativo e Sistema
• DYNAMIC_GROUPS contém Grupos Dinâmicos

Grupos de Classificação

O Alchemy Manager pode ser configurado para suportar até 13 "Classificações de Alunos". Cada classificação possui um campo em DM_STUDENT_DETAIL que contém o nome do grupo (ou grupos) do qual o estudante é membro.

As colunas são nomeadas da seguinte forma:

• STATUS_GROUPS
• PAY_TYPE_GROUPS
• TIME_TYPE_GROUPS
• EMP_TYPE_GROUPS
• REGION_GROUPS
• SUB_REGION_GROUPS
• DIV_GROUPS
• BUSS_UNIT_GROUPS
• DEPT_GROUPS
• JOB_CODE_GROUPS
• JOB_TITLE_GROUPS
• JOB_CLASS_GROUPS
• SHIFT_GROUPS

Nomes de múltiplos grupos serão separados por espaços e pelo símbolo de barra vertical (|). Apenas os primeiros 100 nomes de grupos serão listados porque isso é destinado a ser exibido "como está" em vez de manipulado ou pesquisado.

DM_*_RELATÓRIO

Estas são as principais tabelas do relatório. Em geral, cada Relatório Personalizável no Alchemy Manager utiliza uma tabela de relatório, e cada linha na tabela representa uma linha de saída num Relatório Personalizável. Os casos mais simples provavelmente usarão uma tabela de relatório e a tabela DM_STUDENT_DETAIL. Uma consulta como esta funcionaria:

select s.user_full_name, tr.training_end_date, tr.activity_code, tr.activity_title from dm_tr_report tr inner join dm_student_detail s on tr.student_key = s.student_key where date(tr.training_end_date) = current_date

As tabelas são:

Observe que estamos removendo os campos de estudante das tabelas de relatórios para simplificar novos relatórios e obter detalhes de vários estudantes (ou seja, OBSERVED_BY_KEY, FACILITATOR_STUDENT_KEY, etc.). Para compatibilidade futura, você deve recuperar os dados dos alunos da tabela DM_STUDENT_DETAIL.

DM_LOCALE_*

Estas tabelas contêm as várias traduções para objetos no Alchemy Manager que as suportam. Cada tabela representa as traduções disponíveis para um único campo de texto. Cada linha na tabela representa o texto que seria exibido para um único local. (Atualmente, o campo de local contém um número obscuro. Isso será posteriormente).

Observe que uma tradução está presente em cada um dos nossos locais de interface de usuário suportados, mesmo que nenhuma tenha sido fornecida. A tradução "en_US" será usada neste caso.

O conteúdo do curso pode estar disponível em uma ampla variedade de idiomas, mas o título principal está em DM_LOCALE_PARENT e apenas nos locais de interface do usuário suportados. Por exemplo, há cursos em suaíli, vietnamita, etc., com nomes que não estão em inglês, mas o título principal teria uma tradução em inglês, como "Electrical Safety Swahili", e pode não haver uma tradução em espanhol do título principal, então em dm_locale_parent ele seria armazenado com o mesmo título em inglês.

Por exemplo, para obter nomes de cursos relacionados em "es-US" (obteremos "en-US" onde "es-US" não estiver disponível) para treinamento recente, uma consulta como esta funcionaria:

select s.user_full_name, tr.training_end_date, tr.activity_code, p.name, tr.activity_title from dm_tr_report tr inner join dm_student_detail s on tr.student_key = s.student_key inner join dm_locale_parent p on p.parent_key=tr.parent_key and p.filter_locale=160 where date(training_end_date) = current_date() limit 20;

As tabelas são:

O ID de Localidade (filter_locale) para locais da interface do usuário do Alchemy são:

DM_ANSWER_DETAILS

Esta tabela contém os detalhes das respostas dos alunos a um teste, uma avaliação ou uma Observação. Alguns campos são preenchidos ou não, dependendo do tipo de objeto. Isso será unido à tabela de relatórios usando TEST_SESSION_KEY.

Nota: "Avaliações Anônimas" são anônimas na interface do usuário, mas não nos dados. Para preservar o anonimato, qualquer uso dos dados precisa obscurecer os detalhes.

DM_STUDENT_GROUP

Esta tabela inclui todos os grupos associados a um estudante. Destina-se a ser usado apenas para filtragem. Isso é realizado adicionando uma cláusula WHERE, como:

where student_key in ( select distinct student_key from dm_student_group where group_name='A Status Name' and group_type='Status' )

DM_STUDENT_SECURITY

Esta tabela permite que você aplique o nível de segurança de supervisor do Alchemy Manager. A segurança do Manager é inteiramente baseada no acesso a um estudante. As linhas nesta tabela devem ser filtradas por ADMIN_STUDENT_KEY e unidas novamente à tabela de relatório usando o STUDENT_KEY. O resultado é eliminar as linhas que não são acessíveis ao administrador. Para obter a ADMIN_STUDENT_KEY correta, procure a STUDENT_KEY do administrador na tabela DM_STUDENT_DETAIL. Aqui está um exemplo para filtrar os resultados para o administrador com USER_ID='000001':

where student_key in ( select distinct student_key from dm_student_security where admin_student_key=( select student_key from dm_student_detail where user_id='000001' ) )

Nota: A tabela também pode ser filtrada por UNIQUEADMINID, que é composto pelo StudentID e seu DB_NAME. Ficaria dessa forma:

where student_key in ( select distinct student_key from dm_student_security where uniqueadminid='12345@alchemy_clientname_live' )

Carga Incremental

Se você estiver espelhando os dados do relatório num sistema próprio, recarregar os dados regularmente pode ser muito lento ou caro. A solução padrão para este problema é carregar apenas o que foi alterado. Isso é chamado de carga incremental ou, às vezes, carga delta. Há várias dificuldades com este plano:

• Identificar as linhas alteradas pode ser difícil.
• As linhas alteradas precisam ser mescladas com os dados antigos.
• Não há como saber quais linhas precisam ser excluídas.

A maneira mais fácil de lidar com uma grande carga de dados é fazer cargas incrementais a cada dia, mas depois fazer uma recarga completa uma vez por semana. Em outras palavras, use uma carga incremental como um "melhor esforço" para ter dados atualizados, mas use uma recarga completa para corrigir qualquer coisa que tenha sido perdida ou precise ser deletada.

Estamos trabalhando para facilitar a identificação das linhas que foram alteradas. Atualmente, cada tabela possui um campo CHANGE_DATE, mas você pode achar que isso muda com muita frequência. Então, estamos adicionando um campo DM_LAST_MODIFIED para indicar apenas quando ocorreram mudanças importantes. Se você estiver usando quaisquer junções, também deve respeitar as alterações nessas tabelas. Para ser minucioso, procure por quaisquer datas maiores do que a maior data dos dados carregados anteriormente.

Para ilustrar uma estratégia para manter uma cópia local do DM_TR_REPORT, este bloco de pseudocódigo assume que seu "banco de dados local" funciona exatamente como outra conta Snowflake. O objetivo disso é minimizar a quantidade de dados transferidos do Alchemy para o banco de dados local.

    -- Transfer changed rows from Alchemy to Local DB SET last_previous_date = ( SELECT COALESCE(MAX(DM_LAST_MODIFIED), '1980-01-01') FROM localdb.DM_TR_REPORT ); CREATE OR REPLACE localdb.changes AS SELECT your_columns FROM ALCHEMY.DM_TR_REPORT WHERE DM_LAST_MODIFIED > $last_previous_date; -- Locally update existing rows where there are matches UPDATE localdb.DM_TR_REPORT SET your_columns FROM localdb.changes WHERE localdb.DM_TR_REPORT.transcript_key = localdb.changes.transcript_key; -- Insert rows where there are no matches INSERT INTO localdb.DM_TR_REPORT (your_columns) SELECT your_columns FROM localdb.changes LEFT JOIN localdb.DM_TR_REPORT ON localdb.DM_TR_REPORT.transcript_key = localdb.changes.transcript_key WHERE localdb.DM_TR_REPORT.transcript_key IS NULL;

Observe que isso ainda não lidará com linhas excluídas.

Aqui está uma lista das tabelas DM e a chave (ou chaves) que deve ser usada para identificar linhas únicas.

Atualmente, apenas o DM_TR_REPORT possui um campo DM_LAST_MODIFIED. As outras tabelas têm um campo CHANGE_DATE que pode ser usado da mesma forma, mas não será tão eficaz na redução dos registros considerados "novos".

Relatórios avançados

Alguns dos nossos relatórios têm complexidade adicional além das tabelas normais. Você não conseguirá recriar o resultado desses relatórios sem reproduzir essa complexidade adicional.

Por exemplo:

• O Relatório de Inscrição do Plano de Aprendizado utiliza agrupamento para simplificar os resultados quando os usuários estão inscritos no mesmo plano várias vezes.
• Os relatórios "Matriz" utilizam múltiplas linhas e realizam uma operação de pivotagem para converter essas linhas extras em colunas.
• Os Gráficos do Painel disponíveis no Manager agregam as linhas em uma ou duas dimensões e, em seguida, fornecem os dados para uma biblioteca de gráficos.