Estratégia para buscas de Fontes de Dados Incrementais no Excel

Question

Estou em um cenário com uma tabela atualizada por um terceiro aplicativo toda semana. Eu preciso continuar acumulando todos os dados no Excel, usando uma conexão ODBC para o banco de dados.

Eu estou querendo saber

Abordagem 1: Existe uma maneira de forçar o Excel a adicionar resultados a cada atualização (essa atualização seria acionada de acordo com um parâmetro que indica a semana)? Tentei definir a tabela para a qual a conexão é carregada usando uma referência dinâmica, mas uma vez ancorada pela primeira vez, a posição da tabela nunca é redefinida

Abordagem 2 : use um ETL para acumular todos os resultados semanais em uma tabela intermediária e conecte o Excel a ela em tempo real. Mas, eu precisaria de um mecanismo para armazenar dados antigos, já que não posso aumentar exponencialmente o tempo que o Excel abre. Imagine que depois de 10 anos, o Excel precisaria atualizar na abertura de 10 anos para os dados antes de mostrá-los. Existe uma maneira de armazenar dados já buscados e incrementá-los em tempo real (quando o livro é aberto), selecionando novos dados (com uma consulta / filtro de algo)

Obrigado

EDIT: Talvez seja melhor perguntar assim: Qual é a estratégia ideal para uma tabela que continua crescendo e precisa ser lida em tempo real pelo Excel? Eu só não quero buscar absolutamente todos os dados depois de alguns meses ...

    

Answer 1

Eu usaria o suplemento de consulta de energia para o Excel para resolver isso. Ele pode manipular os resultados anexados de uma consulta SQL a uma tabela existente.

link

Se você armazenar os dados no formato Modelo de Dados do Excel (não em uma Tabela do Excel), os dados serão altamente compactados - há relatórios de pessoas armazenando 160 milhões de linhas nesse formato (com Excel de 64 bits). Você pode então acessar os dados de volta para o Excel através de Tabelas Dinâmicas e / ou Fórmulas de Cubo.

    

Answer 2

Eu achei a resposta selecionada um pouco deficiente (mas um ótimo começo:)

Usando o PowerQuery mais recente (a partir de novembro de 2014, você pode carregar na pasta de trabalho além de carregar no modelo de dados)

Aqui, os dados da Origem são apenas uma tabela no Excel, mas você pode extrair de qualquer lugar, incluindo um banco de dados SQL.

Primeiro passo Crie uma carga inicial. A consulta é chamada FROM (o nome é herdado do nome da tabela: FROM)

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow())
in
    AddCustom

Carregue no modelo de dados E na planilha. A tabela da planilha é chamada FROM_2 por padrão.

Atualize a consulta FROM assim:

let
    //Load from Table FROM
    Source = Excel.CurrentWorkbook(){[Name="FROM"]}[Content],
    AddCustom = Table.AddColumn(Source, "Load Date", each DateTimeZone.UtcNow()),
    //Load from Table FROM_2  (this is just a copy of what's in the DataModel)
    Custom1 = Excel.CurrentWorkbook(){[Name="FROM_2"]}[Content],
    ChangedType = Table.TransformColumnTypes(Custom1 ,{{"Load Date", type datetimezone}}),
    //Append the two Loads.  The New data and the existing loads in the DataModel
    Append = Table.Combine({ChangedType,AddCustom})
in
    Append

Então, agora você pode apenas, por exemplo, adicionar uma tabela dinâmica apontando para o DataModel ou para a tabela FROM_2. Se você precisar de outra tabela com os dados no DataModel, onde você pode adicionar outras colunas, basta criar outra consulta apontando para a tabela FROM_2.

Como você está usando uma tabela na planilha como uma etapa intermediária, duvido que isso seja dimensionado para muitos dados.