Habilitando a Consulta Direta para um conector baseado em ODBC

Visão geral

Usar a função Odbc.DataSource do M é a maneira recomendada de criar conectores personalizados para fontes de dados que têm um driver ODBC existente e/ou que suportam uma sintaxe SQL consulta. O empacotamento da função Odbc.DataSource permitirá que o conector herde o comportamento de dobramento de consultas padrão com base nos recursos relatados pelo driver. Isso permitirá que o mecanismo M gere instruções SQL baseadas em filtros e outras transformações definidas pelo usuário dentro da experiência Power Query, sem precisar fornecer essa lógica dentro do próprio conector.

Opcionalmente, as extensões ODBC podem habilitar o modo consulta direta, permitindo que Power BI gere consultas dinamicamente em runtime sem o armazenamento em cache do modelo de dados do usuário.

Observação

A habilitação do suporte ao Direct Query gera o nível de dificuldade e complexidade do conector. Quando a Consulta Direta estiver habilitada, Power BI impedirá que o mecanismo M seja compensado por operações que não podem ser totalmente aplicadas à fonte de dados subjacente.

Esta seção se baseia nos conceitos apresentados na Referência de Extensibilidade M e assume familiaridade com a criação de um Conector de Dados básico.

Consulte o exemplo de SqlODBC para a maioria dos exemplos de código nas seções abaixo. Exemplos adicionais podem ser encontrados no diretório de exemplos ODBC.

Funções de extensibilidade ODBC

O mecanismo M fornece duas funções de fonte de dados relacionadas ao ODBC: Odbc.DataSourcee Odbc.Query.

A função Odbc.DataSource fornece uma tabela de navegação padrão com todos os bancos de dados, tabelas e exibições do sistema, dá suporte à dobra de consultas e permite uma variedade de opções de personalização. A maioria das extensões baseadas em ODBC usará isso como sua função de extensibilidade principal. A função aceita dois argumentos uma cadeia de conexão e um registro de — opções para fornecer substituições de comportamento.

A função Odbc.Query permite executar instruções SQL por meio de um driver ODBC. Ele atua como uma passagem para execução de consulta. Ao contrário da função Odbc.DataSource, ela não fornece a funcionalidade de dobramento de consultas e requer que SQL consultas sejam fornecidas pelo conector (ou pelo usuário final). Ao criar um conector personalizado, essa função normalmente é usada internamente para executar consultas para recuperar metadados que podem não ser expostos por meio de canais ODBC regulares. A função aceita dois argumentos uma — cadeia de conexão e uma SQL consulta.

Parâmetros para sua função de fonte de dados

Conectores personalizados podem aceitar qualquer número de argumentos de função, mas para permanecer consistentes com as funções de fonte de dados internas fornecidas com Power Query, as diretrizes a seguir são recomendadas:

  • Exigir o conjunto mínimo de parâmetros usados para estabelecer uma conexão com o servidor. Quanto menos parâmetros os usuários finais precisam fornecer, mais fácil será o uso do conector.

  • Embora você possa definir parâmetros com um número fixo de valores (ou seja, uma lista de menus suspensos na interface do usuário), os parâmetros são inseridos antes que o usuário seja autenticado. Todos os valores que podem ser descobertos programaticamente depois que o usuário é autenticado (como nome do catálogo ou banco de dados) devem ser selecionáveis por meio do Navegador. O comportamento padrão para a função Odbc.DataSource será retornar uma tabela de navegação hierárquica que consiste em nomes de Catálogo (Banco de Dados), Esquema e Tabela, embora isso possa ser substituído em seu conector.

  • Se você acha que os usuários normalmente saberão quais valores inserir para itens que eles selecionariam no Navegador (como o nome do banco de dados), faça com que esses parâmetros sejam opcionais. Parâmetros que podem ser descobertos programaticamente não devem ser necessários.

  • O último parâmetro para sua função deve ser um registro opcional chamado "opções". Normalmente, esse parâmetro permite que usuários avançados deem um conjunto de propriedades comuns relacionadas ao ODBC (como CommandTimeout), definir substituições de comportamento específicas ao conector e permitir extensibilidade futura sem afetar a compatibilidade com vertida para sua função.

  • Argumentos relacionados à segurança/credencial NUNCA DEVEM fazer parte dos parâmetros da função da fonte de dados, pois os valores inseridos na caixa de diálogo de conexão serão persistentes para a consulta do usuário. Os parâmetros relacionados à credencial devem ser especificados como parte dos métodos de Autenticação com suporte do conector.

Por padrão, todos os parâmetros necessários para sua função de fonte de dados são fatorados no valor caminho da fonte de dados usado para identificar as credenciais do usuário.

Observe que, embora a interface do usuário para a função interna Odbc.DataSource fornece uma lista suspenso que permite ao usuário selecionar um DSN, essa funcionalidade não está disponível por meio da extensibilidade. Se a configuração da fonte de dados for complexa o suficiente para exigir uma caixa de diálogo de configuração totalmente personalizável, é recomendável que os usuários finais configurem previamente um DSN do sistema e que sua função leve o nome DSN como um campo de texto.

Parâmetros para Odbc.DataSource

A função Odbc.DataSource leva dois parâmetros uma connectionString para o driver e um registro de opções que permite substituir vários — comportamentos de driver. Por meio do registro de opções, você pode substituir recursos e outras informações relatadas pelo driver, controlar o comportamento do navegador e afetar o SQL consultas geradas pelo mecanismo M.

Os campos de registros de opções com suporte se enquadram em duas categorias aquelas que são públicas/sempre disponíveis e aquelas que estão disponíveis apenas em — um contexto de extensibilidade.

A tabela a seguir descreve os campos públicos no registro de opções.

Campo Descrição
CommandTimeOut

Um valor de duração que controla por quanto tempo a consulta do lado do servidor tem permissão para ser executado antes de ser cancelada.

Padrão: 10 minutos

ConnectionTimeout

Um valor de duração que controla quanto tempo esperar antes de abandonar uma tentativa de fazer uma conexão com o servidor.

Padrão: 15 segundos

CreateNavigationProperties

Um valor lógico que define se as propriedades de navegação serão gerados nas tabelas retornadas. As propriedades de navegação são baseadas em relações de chave estrangeira relatadas pelo driver e aparecem como colunas "virtuais" que podem ser expandidas no editor de consultas, criando a junção apropriada.

Se o cálculo de dependências de chave estrangeira for uma operação cara para o driver, talvez você queira definir esse valor como false.

Padrão: true

HierarchicalNavigation

Um valor lógico que define se as tabelas agrupadas por seus nomes de esquema serão visualizadas. Quando definidas como false, as tabelas serão exibidas em uma lista simples em cada banco de dados.

Padrão: false

SqlCompatibleWindowsAuth

Um valor lógico que determina se uma cadeia de conexão SQL Server de conexão compatível ao usar Windows autenticação — Trusted_Connection=Sim.

Se o driver for compatível com Windows Autenticação, mas exigir configurações adicionais ou alternativas na cadeia de conexão, você deverá definir esse valor como false e usar o campo de registro de opção CredentialConnectionString descrito abaixo.

Padrão: true

A tabela a seguir descreve os campos de registro de opções que só estão disponíveis por meio de extensibilidade. Campos que não são valores literais simples são descritos nas seções subsequentes.

Campo Descrição
AstVisitor

Um registro que contém uma ou mais substituições para controlar SQL de consulta. O uso mais comum desse campo é fornecer lógica para gerar uma cláusula LIMIT/OFFSET para drivers que não dão suporte a TOP.

Os campos incluem:

  • Constante

  • LimitClause

Consulte a seção AstVisitor para obter mais informações.

CancelQueryExplicitly

Um valor lógico que instrui o mecanismo M a cancelar explicitamente todas as chamadas em execução por meio do driver ODBC antes de encerrar a conexão com o servidor ODBC.

Esse campo é útil em situações em que a execução da consulta é gerenciada independentemente das conexões de rede com o servidor, por exemplo, em algumas implantações do Spark. Na maioria dos casos, esse valor não precisa ser definido porque a consulta no servidor é cancelada quando a conexão de rede com o servidor é encerrada.

Padrão: false

ClientConnectionPooling

Um valor lógico que habilita o pool de conexões do lado do cliente para o driver ODBC. A maioria dos drivers deseja definir esse valor como true.

Padrão: false

CredentialConnectionString

Um valor de texto ou registro usado para especificar propriedades de cadeia de conexão relacionadas à credencial.

Consulte a seção Credencial para obter mais informações.

HideNativeQuery

Um valor lógico que controla se o conector permite que instruções SQL nativas sejam passadas por uma consulta usando a função Value.NativeQuery().

Observação: essa funcionalidade atualmente não está exposta na experiência do Power Query usuário. Os usuários precisariam editar manualmente suas consultas para aproveitar essa funcionalidade.

Padrão: false

ImplicitTypeConversions

Um valor de tabela que contém conversões de tipo implícitas com suporte pelo seu driver ou servidor de back-end. Os valores nesta tabela são aditivos às conversões relatadas pelo próprio driver.

Esse campo normalmente é usado em conjunto com o campo SQLGetTypeInfo ao substituindo informações de tipo de dados relatadas pelo driver.

Consulte a seção ImplicitTypeConversions para obter mais informações.

OnError

Uma função de tratamento de erro que recebe um parâmetro errorRecord do tipo record.

Usos comuns dessa função incluem o tratamento de falhas de conexão SSL, o fornecimento de um link de download se o driver não for encontrado no sistema e o relatório de erros de autenticação.

Consulte a seção OnError para obter mais informações.

SoftNumbers

Permite que o mecanismo M selecione um tipo de dados compatível quando a conversão entre dois tipos numéricos específicos não é declarada como compatível com as funcionalidades SQL_CONVERT_*.

Padrão: false

SqlCapabilities

Um registro que fornece várias substituições de recursos de driver e uma maneira de especificar recursos que não são expressos por meio do ODBC 3.8.

Consulte a seção SqlCapabilities para obter mais informações.

SQLColumns

Uma função que permite modificar os metadados de coluna retornados pela função SQLColumns.

Consulte a seção SQLColumns para obter mais informações.

SQLGetFunctions

Um registro que permite substituir valores retornados por chamadas para SQLGetFunctions.

Um uso comum desse campo é desabilitar o uso da associação de parâmetros ou especificar que as consultas geradas devem usar CAST em vez de CONVERT.

Consulte a seção SQLGetFunctions para obter mais informações.

SQLGetInfo

Um registro que permite substituir valores retornados por chamadas para SQLGetInfo.

Consulte a seção SQLGetInfo para obter mais informações.

SQLGetTypeInfo

Uma tabela ou função que retorna uma tabela que substitui as informações de tipo retornadas por SQLGetTypeInfo.

Quando o valor é definido como uma tabela, o valor substitui completamente as informações de tipo relatadas pelo driver. SQLGetTypeInfo não será chamado.

Quando o valor for definido como uma função, sua função receberá o resultado da chamada original para SQLGetTypeInfo, permitindo que você modifique a tabela.

Esse campo normalmente é usado quando há uma incompatibilidade entre tipos de dados relatados por SQLGetTypeInfo e SQLColumns.

Consulte a seção SQLGetTypeInfo para obter mais informações.

SQLTables

Uma função que permite modificar os metadados da tabela retornados por uma chamada para SQLTables.

Consulte a seção SQLTables para obter mais informações.

TolerateConcatOverflow

Permite a conversão de tipos numéricos e de texto em tipos maiores se uma operação faz com que o valor saia do intervalo do tipo original.

Por exemplo, ao adicionar Int32.Max + Int32.Max, o mecanismo vai lançar o resultado para Int64 quando essa configuração for definida como true. Ao adicionar um campo VARCHAR(4000) e VARCHAR(4000) em um sistema que dá suporte a um tamanho VARCHAR maximizado de 4000, o mecanismo transformará o resultado em um tipo CLOB.

Padrão: false

UseEmbeddedDriver

(uso interno): Um valor lógico que controla se o driver ODBC deve ser carregado de um diretório local (usando a nova funcionalidade definida na especificação ODBC 4.0). Geralmente, isso é definido apenas por conectores criados pela Microsoft que são Power Query.

Quando definido como false, o gerenciador de driver ODBC do sistema será usado para localizar e carregar o driver.

A maioria dos conectores não deve precisar definir esse campo.

Padrão: false

Substituindo o AstVisitor

O campo AstVisitor é definido por meio do registro de opções Odbc.DataSource. Ele é usado para modificar instruções SQL geradas para cenários de consulta específicos.

Observação

Os drivers que dão suporte a cláusulas e (em vez de ) fornecerão LIMIT OFFSET uma substituição TOP LimitClause para AstVisitor.

Constante

Fornecer uma substituição para esse valor foi preterido e pode ser removido de implementações futuras.

LimitClause

Esse campo é uma função que recebe dois argumentos (skip, take) e retorna um registro com dois campos Int64.Type de texto (Texto, Localização).

LimitClause = (skip as nullable number, take as number) as record => ...

O parâmetro skip é o número de linhas a ignorar (ou seja, o argumento para OFFSET). Se um deslocamento não for especificado, o valor skip será nulo. Se o driver for compatível com , mas não for compatível com , a função LimitClause deverá retornar um erro não simplificado LIMIT OFFSET (...) quando skip for maior que 0.

O parâmetro take é o número de linhas a ser seguido (ou seja, o argumento para LIMIT).

O Text campo do resultado contém o SQL texto a ser acrescentado à consulta gerada.

O Location campo especifica onde inserir a cláusula . A tabela a seguir descreve os valores com suporte.

Valor Descrição Exemplo
AfterQuerySpecification

A cláusula LIMIT é colocada no final do SQL.

Essa é a sintaxe LIMIT mais comumente suportada.

SELECT a, b, c

Tabela FROM

WHERE a > 10

LIMITE 5

BeforeQuerySpecification A cláusula LIMIT é colocada antes da instrução SQL gerada.

LIMITAR 5 LINHAS

SELECT a, b, c

Tabela FROM

WHERE a > 10

Afterselect LIMIT vai após a instrução SELECT e depois de quaisquer modificadores (como DISTINCT).

SELECT DISTINCT LIMIT 5 a, b, c

Tabela FROM

WHERE a > 10

AfterSelectBeforeModifiers LIMIT vai após a instrução SELECT, mas antes de quaisquer modificadores (como DISTINCT).

SELECT LIMIT 5 DISTINCT a, b, c

Tabela FROM

WHERE a > 10

O snippet de código a seguir fornece uma implementação LimitClause para um driver que espera uma cláusula LIMIT, com um OFFSET opcional, no seguinte formato: [OFFSET <offset> ROWS] LIMIT <row_count>

LimitClause = (skip, take) =>
    let
        offset = if (skip > 0) then Text.Format("OFFSET #{0} ROWS", {skip}) else "",
        limit = if (take <> null) then Text.Format("LIMIT #{0}", {take}) else ""
    in
        [
            Text = Text.Format("#{0} #{1}", {offset, limit}),
            Location = "AfterQuerySpecification"
        ]

O snippet de código a seguir fornece uma implementação LimitClause para um driver que dá suporte a LIMIT, mas não OFFSET. Formato: LIMIT <row_count> .

LimitClause = (skip, take) =>
    if (skip > 0) then error "Skip/Offset not supported"
    else
    [
        Text = Text.Format("LIMIT #{0}", {take}),
        Location = "AfterQuerySpecification"
    ]

Substituindo SqlCapabilities

Campo Detalhes
FractionalSecondsScale

Um valor numérico que varia de 1 a 7 que indica o número de casas decimais com suporte para valores de milissegundos. Esse valor deve ser definido por conectores que querem habilitar a dobra de consultas em valores datetime.

Padrão: nulo

PrepareStatements

Um valor lógico que indica que as instruções devem ser preparadas usando SQLPrepare.

Padrão: false

SupportsTop

Um valor lógico que indica que o driver dá suporte à cláusula TOP para limitar o número de linhas retornadas.

Padrão: false

StringLiteralEscapeCharacters

Uma lista de valores de texto que especificam os caracteres a usar ao escapar literais de cadeia de caracteres e expressões LIKE.

Ex.: {""}

Padrão: nulo

SupportsDerivedTable

Um valor lógico que indica que o driver dá suporte a tabelas derivadas (subselecções).

Esse valor é presumido como verdadeiro para drivers que definiam seu nível de conformidade como SQL_SC_SQL92_FULL (relatado pelo driver ou substituído pela configuração Sql92Conformance (veja abaixo)). Para todos os outros níveis de conformidade, esse valor assume false como padrão.

Se o driver não relatar o nível SQL_SC_SQL92_FULL de conformidade, mas tiver suporte para tabelas derivadas, de definir esse valor como true.

Observe que o suporte a tabelas derivadas é necessário para muitos cenários de Consulta Direta.

SupportsNumericLiterals

Um valor lógico que indica se o valor SQL deve incluir valores literais numéricos. Quando definido como false, os valores numéricos sempre serão especificados usando Associação de Parâmetros.

Padrão: false

SupportsStringLiterals

Um valor lógico que indica se o valor gerado SQL deve incluir valores literais de cadeia de caracteres. Quando definido como false, os valores de cadeia de caracteres sempre serão especificados usando Associação de Parâmetro.

Padrão: false

SupportsOdbcDateLiterals

Um valor lógico que indica se a SQL deve incluir valores literais de data. Quando definido como false, os valores de data sempre serão especificados usando Associação de Parâmetros.

Padrão: false

SupportsOdbcTimeLiterals

Um valor lógico que indica se a SQL deve incluir valores de literais de tempo. Quando definido como false, os valores de hora sempre serão especificados usando Associação de Parâmetros.

Padrão: false

SupportsOdbcTimestampLiterals

Um valor lógico que indica se o valor SQL deve incluir valores literais de timestamp. Quando definido como false, os valores de timestamp sempre serão especificados usando Associação de Parâmetro.

Padrão: false

Substituindo SQLColumns

SQLColumnsé um manipulador de funções que recebe os resultados de uma chamada ODBC para SQLColumns. O parâmetro de origem contém uma tabela com as informações de tipo de dados. Normalmente, essa substituição é usada para corrigir incompatibilidades de tipo de dados entre chamadas para SQLGetTypeInfo e SQLColumns .

Para obter detalhes sobre o formato do parâmetro de tabela de origem, acesse Função SQLColumns.

Substituindo SQLGetFunctions

Esse campo é usado para substituir valores SQLFunctions retornados por um driver ODBC. Ele contém um registro cujos nomes de campo são iguais às constantes FunctionId definidas para a função ODBC SQLGetFunctions. Constantes numéricas para cada um desses campos podem ser encontradas na especificação ODBC.

Campo Detalhes
SQL_CONVERT_FUNCTIONS Indica quais funções têm suporte ao fazer conversões de tipo. Por padrão, o Mecanismo M tentará usar a função CONVERT. Os drivers que preferem o uso de CAST podem substituir esse valor para relatar que apenas SQL_FN_CVT_CAST (valor numérico de 0x2) tem suporte.
SQL_API_SQLBINDCOL

Um valor lógico (true/false) que indica se o Mecanismo de Mashup deve usar a API SQLBindCol ao recuperar dados. Quando definido como false, SQLGetData é usado em vez disso.

Padrão: false

O snippet de código a seguir fornece um exemplo explicitamente dizendo ao mecanismo M para usar CAST em vez de CONVERT.

SQLGetFunctions = [
    SQL_CONVERT_FUNCTIONS = 0x2 /* SQL_FN_CVT_CAST */
]

Substituindo SQLGetInfo

Esse campo é usado para substituir valores SQLGetInfo retornados por um driver ODBC. Ele contém um registro cujos campos são nomes iguais às constantes InfoType definidas para a função ODBC SQLGetInfo. Constantes numéricas para cada um desses campos podem ser encontradas na especificação ODBC. A lista completa de InfoTypes que estão marcados pode ser encontrada nos arquivos de rastreamento do Mecanismo de Mashup.

A tabela a seguir contém propriedades SQLGetInfo comumente substituídos:

Campo Detalhes
SQL_SQL_CONFORMANCE

Um valor inteiro que indica o nível de SQL-92 com suporte pelo driver:

(1) SQL_SC_SQL92_ENTRY = Nível de SQL-92 compatível.

(2) SQL_SC_FIPS127_2_TRANSITIONAL = em conformidade com o nível de transição FIPS 127-2.

(4) SQL_SC_ SQL92_INTERMEDIATE = Nível intermediário SQL-92 em conformidade.

(8) SQL_SC_SQL92_FULL = Nível completo SQL-92 em conformidade.

Observe que, Power Query cenários, o conector será usado em um modo Somente Leitura. A maioria dos drivers deseja relatar um SQL_SC_SQL92_FULL de conformidade e substituir um comportamento SQL geração específico usando as propriedades SQLGetInfo e SQLGetFunctions.

SQL_SQL92_PREDICATES

Um bitmask enumerando os predicados com suporte em uma instrução SELECT, conforme definido em SQL-92.

Consulte as constantes SQL_SP_* na especificação ODBC.

SQL_AGGREGATE_FUNCTIONS

Um suporte de enumeração de bitmask para funções de agregação.

SQL_AF_ALL

SQL_AF_AVG

SQL_AF_COUNT

SQL_AF_DISTINCT

SQL_AF_MAX

SQL_AF_MIN

SQL_AF_SUM

Consulte as constantes SQL_AF_* na especificação ODBC.

SQL_GROUP_BY

Um valor inteiro que especifica a relação entre as colunas na cláusula GROUP BY e as colunas não agregadas na lista de seleção:

SQL_GB_COLLATE = uma cláusula COLLATE pode ser especificada no final de cada coluna de grupo.

SQL_GB_NOT_SUPPORTED = cláusulas GROUP BY não são suportadas.

SQL_GB_GROUP_BY_EQUALS_SELECT = A cláusula GROUP BY deve conter todas as colunas não agregadas na lista de seleção. Ele não pode conter nenhuma outra coluna. Por exemplo, SELECT DEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT.

SQL_GB_GROUP_BY_CONTAINS_SELECT = A cláusula GROUP BY deve conter todas as colunas não agregadas na lista de seleção. Ele pode conter colunas que não estão na lista de seleção. Por exemplo, SELECT DEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT, AGE.

SQL_GB_NO_RELATION = As colunas na cláusula GROUP BY e na lista de seleção não estão relacionadas. O significado de colunas não agrupadas e não agregadas na lista de seleção é dependente da fonte de dados. Por exemplo, SELECT DEPT, SALARY FROM EMPLOYEE GROUP BY DEPT, AGE.

Consulte as constantes SQL_GB_* na especificação ODBC.

A seguinte função auxiliar pode ser usada para criar valores de bitmask de uma lista de valores inteiros:

Flags = (flags as list) =>
    let
        Loop = List.Generate(
                  ()=> [i = 0, Combined = 0],
                  each [i] < List.Count(flags),
                  each [i = [i]+1, Combined =*Number.BitwiseOr([Combined], flags{i})],
                  each [Combined]),
        Result = List.Last(Loop, 0)
    in
        Result;

Substituindo SQLGetTypeInfo

SQLGetTypeInfo pode ser especificado de duas maneiras:

  • Um valor table fixo que contém as mesmas informações de tipo que uma chamada ODBC para SQLGetTypeInfo .
  • Uma função que aceita um argumento de tabela e retorna uma tabela. O argumento conterá os resultados originais da chamada ODBC para SQLGetTypeInfo . Sua implementação de função pode modificar/adicionar a esta tabela.

A primeira abordagem é usada para substituir completamente os valores retornados pelo driver ODBC. A segunda abordagem será usada se você quiser adicionar ou modificar esses valores.

Para obter detalhes sobre o formato do parâmetro de tabela de tipos e o valor de retorno esperado, consulte a referência da função SQLGetTypeInfo.

SQLGetTypeInfo usando uma tabela estática

O snippet de código a seguir fornece uma implementação estática para SQLGetTypeInfo.

SQLGetTypeInfo = #table(
    { "TYPE_NAME",      "DATA_TYPE", "COLUMN_SIZE", "LITERAL_PREF", "LITERAL_SUFFIX", "CREATE_PARAS",           "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE", "FIXED_PREC_SCALE", "AUTO_UNIQUE_VALUE", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX", "INTERNAL_PRECISION", "USER_DATA_TYPE" }, {

    { "char",           1,          65535,          "'",            "'",              "max. length",            1,          1,                3,            null,                 0,                  null,                "char",            null,            null,            -8,              null,               null,             0,                    0                }, 
    { "int8",           -5,         19,             "'",            "'",              null,                     1,          0,                2,            0,                    10,                 0,                   "int8",            0,               0,               -5,              null,               2,                0,                    0                },
    { "bit",            -7,         1,              "'",            "'",              null,                     1,          1,                3,            null,                 0,                  null,                "bit",             null,            null,            -7,              null,               null,             0,                    0                },
    { "bool",           -7,         1,              "'",            "'",              null,                     1,          1,                3,            null,                 0,                  null,                "bit",             null,            null,            -7,              null,               null,             0,                    0                },
    { "date",           9,          10,             "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "date",            null,            null,            9,               1,                  null,             0,                    0                }, 
    { "numeric",        3,          28,             null,           null,             null,                     1,          0,                2,            0,                    0,                   0,                  "numeric",         0,               0,               2,               null,               10,               0,                    0                },
    { "float8",         8,          15,             null,           null,             null,                     1,          0,                2,            0,                    0,                   0,                  "float8",          null,            null,            6,               null,               2,                0,                    0                },
    { "float8",         6,          17,             null,           null,             null,                     1,          0,                2,            0,                    0,                   0,                  "float8",          null,            null,            6,               null,               2,                0,                    0                },
    { "uuid",           -11,        37,             null,           null,             null,                     1,          0,                2,            null,                 0,                  null,                "uuid",            null,            null,            -11,             null,               null,             0,                    0                },
    { "int4",           4,          10,             null,           null,             null,                     1,          0,                2,            0,                    0,                   0,                  "int4",            0,               0,               4,               null,               2,                0,                    0                },
    { "text",           -1,         65535,          "'",            "'",              null,                     1,          1,                3,            null,                 0,                  null,                "text",            null,            null,            -10,             null,               null,             0,                    0                },
    { "lo",             -4,         255,            "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "lo",              null,            null,            -4,              null,               null,             0,                    0                }, 
    { "numeric",        2,          28,             null,           null,             "precision, scale",       1,          0,                2,            0,                    10,                 0,                   "numeric",         0,               6,               2,               null,               10,               0,                    0                },
    { "float4",         7,          9,              null,           null,             null,                     1,          0,                2,            0,                    10,                 0,                   "float4",          null,            null,            7,               null,               2,                0,                    0                }, 
    { "int2",           5,          19,             null,           null,             null,                     1,          0,                2,            0,                    10,                 0,                   "int2",            0,               0,               5,               null,               2,                0,                    0                }, 
    { "int2",           -6,         5,              null,           null,             null,                     1,          0,                2,            0,                    10,                 0,                   "int2",            0,               0,               5,               null,               2,                0,                    0                }, 
    { "timestamp",      11,         26,             "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "timestamp",       0,               38,              9,               3,                  null,             0,                    0                }, 
    { "date",           91,         10,             "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "date",            null,            null,            9,               1,                  null,             0,                    0                }, 
    { "timestamp",      93,         26,             "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "timestamp",       0,               38,              9,               3,                  null,             0,                    0                }, 
    { "bytea",          -3,         255,            "'",            "'",              null,                     1,          0,                2,            null,                 0,                  null,                "bytea",           null,            null,            -3,              null,               null,             0,                    0                }, 
    { "varchar",        12,         65535,          "'",            "'",              "max. length",            1,          0,                2,            null,                 0,                  null,                "varchar",         null,            null,           -9,               null,               null,             0,                    0                }, 
    { "char",           -8,         65535,          "'",            "'",              "max. length",            1,          1,                3,            null,                 0,                  null,                "char",            null,            null,           -8,               null,               null,             0,                    0                }, 
    { "text",           -10,        65535,          "'",            "'",              "max. length",            1,          1,                3,            null,                 0,                  null,                "text",            null,            null,           -10,              null,               null,             0,                    0                }, 
    { "varchar",        -9,         65535,          "'",            "'",              "max. length",            1,          1,                3,            null,                 0,                  null,                "varchar",         null,            null,           -9,               null,               null,             0,                    0                },
    { "bpchar",         -8,         65535,           "'",            "'",              "max. length",            1,          1,                3,            null,                 0,                  null,                "bpchar",          null,            null,            -9,               null,               null,            0,                    0                } }
);

SQLGetTypeInfo usando uma função

Os snippets de código a seguir anexam bpchar o tipo aos tipos existentes retornados pelo driver.

SQLGetTypeInfo = (types as table) as table =>
   let
       newTypes = #table(
           {
               "TYPE_NAME",
               "DATA_TYPE",
               "COLUMN_SIZE",
               "LITERAL_PREF",
               "LITERAL_SUFFIX",
               "CREATE_PARAS",
               "NULLABLE",
               "CASE_SENSITIVE",
               "SEARCHABLE",
               "UNSIGNED_ATTRIBUTE",
               "FIXED_PREC_SCALE",
               "AUTO_UNIQUE_VALUE",
               "LOCAL_TYPE_NAME",
               "MINIMUM_SCALE",
               "MAXIMUM_SCALE",
               "SQL_DATA_TYPE",
               "SQL_DATETIME_SUB",
               "NUM_PREC_RADIX",
               "INTERNAL_PRECISION",
               "USER_DATA_TYPE"
            },
            // we add a new entry for each type we want to add
            {
                {
                    "bpchar",
                    -8,
                    65535,
                    "'",
                    "'",
                    "max. length",
                    1,
                    1,
                    3,
                    null,
                    0,
                    null,
                    "bpchar",
                    null,
                    null,
                    -9,
                    null,
                    null,
                    0,
                    0
                }
            }),
        append = Table.Combine({types, newTypes})
    in
        append;

Definindo a cadeia de conexão

A cadeia de conexão do driver ODBC é definida usando o primeiro argumento para as funções Odbc.DataSource e/ou Odbc.Query. O valor pode ser texto ou um registro M. Ao usar o registro, cada campo no registro se tornará uma propriedade na cadeia de conexão. Todas as cadeias de conexão exigirão um campo Driver (ou campo DSN se você exigir que os usuários configurem previamente um DSN no nível do sistema). As propriedades relacionadas à credencial serão definidas separadamente (veja abaixo). Outras propriedades serão específicas do driver.

O snippet de código abaixo mostra a definição de uma nova função de fonte de dados, a criação do registro ConnectionString e a invocação da função Odbc.DataSource.

[DataSource.Kind="SqlODBC", Publish="SqlODBC.Publish"]
shared SqlODBC.Contents = (server as text) =>
    let
        ConnectionString = [
            Driver = "SQL Server Native Client 11.0",
            Server = server,
            MultiSubnetFailover = "Yes",
            ApplicationIntent = "ReadOnly",
            APP = "PowerBICustomConnector"
        ],
        OdbcDatasource = Odbc.DataSource(ConnectionString)
    in
        OdbcDatasource;

Solução de problemas e teste

Para habilitar o rastreamento Power BI Desktop:

  1. Acesse Arquivo > Opções e configurações > Opções.
  2. Selecione na guia Diagnóstico.
  3. Selecione a opção Habilitar rastreamento.
  4. Selecione o link Abrir pasta traces (deve ser %LOCALAPPDATA%/Microsoft/Power BI Desktop/Traces ).
  5. Exclua os arquivos de rastreamento existentes.
  6. {1>Execute seus testes<1}.
  7. Feche Power BI Desktop para garantir que todos os arquivos de log sejam liberados para o disco.

Aqui estão as etapas que você pode seguir para testes iniciais Power BI Desktop:

  1. Feche o Power BI Desktop.
  2. Limpe o diretório de rastreamento.
  3. Abra Power BI desktop e habilita o rastreamento.
  4. Conexão à fonte de dados e selecione Modo de Consulta Direta.
  5. Selecione uma tabela no navegador e selecione Editar.
  6. Manipule a consulta de várias maneiras, incluindo:
  • Pegue as primeiras N linhas (por exemplo, 10).
  • Defina filtros de igualdade em diferentes tipos de dados (int, cadeia de caracteres, bool e assim por diante).
  • Definir outros filtros de intervalo (maior que, menor que).
  • Filtre null/NOT NULL.
  • Selecione um subconjunto de colunas.
  • Agregação/Agrupar por combinações de colunas diferentes.
  • Adicione uma coluna calculada de outras colunas ([C] = [A] + [B]).
  • Classificar em uma coluna, várias colunas. 7. Expressões que não se dobram resultarão em uma barra de avisos. Observe a falha, remova a etapa e vá para o próximo caso de teste. Detalhes sobre a causa da falha devem ser emitidos para os logs de rastreamento. 8. Feche o Power BI Desktop. 9. Copie os arquivos de rastreamento para um novo diretório. 10. Use a Power BI de trabalho para analisar e analisar os arquivos de rastreamento.

Depois que você tiver consultas simples funcionando, poderá experimentar cenários de Consulta Direta (por exemplo, criando relatórios nas Exibições de Relatório). As consultas geradas no modo consulta direta serão significativamente mais complexas (ou seja, o uso de subseções, instruções COALESCE e agregação).

Concatenação de cadeias de caracteres no modo consulta direta

O mecanismo M faz a validação básica do limite de tamanho de tipo como parte de sua lógica de dobramento de consulta. Se você estiver recebendo um erro de dobramento ao tentar concatenar duas cadeias de caracteres que potencialmente estouram o tamanho máximo do tipo de banco de dados subjacente:

  1. Verifique se o banco de dados pode dar suporte à conversão de up para tipos CLOB quando ocorrer estouro concat de cadeia de caracteres.
  2. De definir TolerateConcatOverflow a opção para Odbc.DataSource como true .

Atualmente, não há suporte para a função CONCATENATE do DAX Power Query/ODBC. Os autores de extensão devem garantir que a concatenação de cadeia de caracteres funcione por meio do editor de consultas adicionando colunas calculadas ( [stringCol1] & [stringCol2] ). Quando a capacidade de dobrar a operação CONCATENATE for adicionada no futuro, ela deverá funcionar perfeitamente com extensões existentes.