Accéder aux tables du modèle de données Excel (Power Query) depuis ODBC

Question

Nous pouvons accéder aux données Excel en utilisant ODBC (pilote ODBC Excel). Pouvons-nous également accéder aux données du modèle de données (c'est-à-dire aux tables Power Query) ? En fait, je pense (mal) utiliser Excel/Power Query comme une base de données et laisser une application externe y récupérer des données (en utilisant SQL).

Pour lire la feuille 1, je peux le faire :

SELECT ... FROM [Sheet1$]

mais

SELECT ... FROM [table in data model] 

ne semble pas fonctionner pour moi. Est-ce que cela est censé fonctionner ou est-ce que cela n'est pas du tout pris en charge ?

Il existe une tonne d'informations sur Power Query utilisant ODBC pour importer des données. Ici, c'est l'inverse qui se produit.

Answer 1

Vous devez distinguer vous-même les tables Power Query et les tables Data Model (Power Pivot). Vous pouvez configurer certaines tables PQ en tant que tables, chargeables dans DM, de sorte que les données seront "transférées" de PQ à DM uniquement pour ces tables particulières.

Je suis à peu près sûr qu'il est impossible d'obtenir des données à partir des tables "PQ seulement". Vous pouvez juste obtenir les requêtes (pas leurs résultats) via VBA ou en décompressant Excel.

Concernant les tableaux PP (DM). En fait, il existe un moteur Analytical Services (VertiPac) dans Excel (au cas où - ainsi que dans PowerBI Desktop). Ainsi, dès que vous démarrez Excel ou PBI, vous démarrez également l'instance du moteur AS. Les données qu'il contient sont accessibles via :

1. Excel VBA (Visual Basic for Applications). Vous disposez de l'API Thisworkbook.Model.DataModelConnection.*, et pouvez accéder aux données elles-mêmes ainsi qu'au modèle. C'est la seule façon "officielle" d'obtenir les données de manière programmatique.

2. Power Query - comme source de données des services analytiques. Il s'agit d'une méthode non officielle, mais j'ai lu que Microsoft avait déclaré qu'elle ne serait pas fermée à l'avenir (mais on ne sait jamais :-)). Dax Studio, par exemple, peut le faire. https://www.sqlbi.com/tools/dax-studio/ . Malheureusement, alors que l'accès au service PBI AS est assez facile, je ne sais pas comment accéder au service Excel AS sans Dax Studio. D'après ce que j'ai compris, le problème principal ici est de savoir comment obtenir un numéro de port AS, lancé par Excel. Mais j'espère que cette information vous aidera au moins à comprendre le chemin à suivre pour une recherche plus poussée, si vous voulez suivre la voie de Power Query. Ou peut-être est-il raisonnable d'utiliser Power BI Desktop pour cette tâche.

3. Excel est juste un fichier zip, donc certainement les fichiers AS sont à l'intérieur de celui-ci. Je n'ai jamais suivi cette voie, mais vous pouvez observer ce qui se trouve à l'intérieur du zip d'Excel - il est possible que les fichiers AS s'y trouvent sous une forme utile.