Synaltic vous partage un article de notre partenaire Dremio : https://www.dremio.com/tutorials/how-to-efficiently-query-your-data-lake-using-sql-parameters-in-excel/
Comment mieux partager vos données, les interroger à l’aide Excel, et éliminer le chaos des dossiers partagés ?
“Les implémentations analytiques aléatoires conduisent au chaos. Une façon d’atténuer ce chaos consiste à mettre en œuvre un catalogue de données – le «cerveau» qui surveille les sources de données et d’analytiques et informe les utilisateurs métier où et comment ils peuvent trouver ces précieux composants (les jeux de données) d’aide à la décision. Le catalogue de données dynamique a les bonnes caractéristiques pour agir comme ce cerveau.” Claudia Imhoff
Il existe aujourd’hui une myriade d’outils de BI.
Bien qu’ils offrent une grande variété de fonctionnalités pour presque tous les cas d’utilisation imaginables de l’analyse de données, les feuilles de calcul restent le moyen le plus populaire de consommer des données. Si vous faites partie des 800 millions d’utilisateurs de Microsoft Excel, lisez la suite pour savoir comment relever l’un des défis les plus courants lorsque vous essayez d’interroger des données – à partir de Microsoft Excel – directement sur votre lac de données. Tout le monde n’a pas des data lakes mais tout le monde voudrait pouvoir rechercher et identifier les jeux de données éparpillés dans les dossiers partagés, les savoir à jour, pour savoir les exploiter et s’en servir pour construire les analyses et finalement éclairer ses décisions.
Afin de tirer le meilleur parti de votre lac de données ou de vos données sous forme de fichiers dispatchés dans vos dossiers partagés, il est important d’avoir un accès rapide à vos données. Cela vous permettra de créer des rapports interactifs qui vous aideront, vous ou vos équipes métiers, à prendre des décisions opportunes basées sur les données pour améliorer les processus de votre organisation.
Un défi commun lors de la création de ces rapports est l’exigence de l’interactivité, c’est-à-dire: la possibilité de modifier les paramètres du rapport pour satisfaire les besoins en données qui diffèrent d’un utilisateur à l’autre ou d’une direction à l’autre, avec des niveaux de granularité personnalisés, etc. Jusqu’à présent, une solution de contournement admis pour répondre à ce défi consistait à créer un rapport statique basé sur chaque demande. Ce qui entraîne bien évidemment une multiplication des fichiers et autres extractions, avec la complexité supplémentaire d’éparpiller tous ces fichiers, d’en perdre la maîtrise des autorisations d’accès et de leurs référencements.
Dans ce tutoriel, nous allons vous guider à travers le processus de connexion directe à Dremio via Excel, nous vous montrons comment écrire des requêtes dans Excel qui sont exécutées dans Dremio chaque fois que la feuille de calcul est actualisée et nous vous montrons comment paramétrer ces requêtes pour rendre les interactions depuis Excel avec les données plus dynamiques et répondre à ce besoin de personnalisation selon les utilisateurs ou les direction métier ou les sujets traités.
Ce didacticiel suppose que vous avez déjà installé le pilote ODBC Dremio sur la machine sur laquelle vous exécuterez Excel et que vous pouvez vous connecter à Dremio à partir de cette machine.
Si vous n’êtes pas familier à Dremio, ou comment rendre accessible vos données grâce à Dremio, jetez un œil à notre tutoriel Se familiariser avec Dremio avant de continuer.
Ajouter une connexion à Dremio et définir une requête sur cette connexion
La première étape du processus consiste à se connecter depuis Excel à Dremio afin de définir les données que nous voulons éventuellement paramétrer.
- Sous l’onglet données, sélectionnez obtenir des données> à partir d’autres sources> à partir d’ODBC
2. Sélectionnez Dremio Connector dans la liste des noms de sources de données, puis développez les options avancées:
3. Dans la zone Instruction SQL (facultative), entrez une requête Dremio valide, puis cliquez sur OK. RECOMMANDATION: ajoutez une clause WHERE qui représente le type de filtrage que vous souhaitez effectuer lorsque des paramètres sont introduits :
4. Une fois la requête exécutée, vous verrez une fenêtre de résumé des résultats, cliquez sur Charger
Nous avons maintenant une requête préparée et prête pour l’ajout d’un paramètre.
Créer une fonction de requête Excel
Avant d’essayer d’ajouter un paramètre à la requête Dremio, l’un des éléments clés pour permettre une expérience de paramétrage transparente est de créer une fonction de requête Excel générique qui vous permet de spécifier une cellule Excel nommée en entrée et fournit la valeur de cette cellule en sortie .
Il s’agit essentiellement d’une tâche ponctuelle, la fonction peut être utilisée plusieurs fois sur toutes les feuilles d’un seul classeur. Dans ce tutoriel, nous supposons que la fonction s’appelle fGetValue, mais vous pouvez l’appeler comme vous le souhaitez.
- Dans Excel, sous l’onglet Données, sélectionnez Obtenir des données> À partir d’autres sources> Requête vide
2. Dans l’éditeur Power Query qui est présenté, sélectionnez Éditeur avancé
3. Collez la fonction suivante dans la fenêtre de l’éditeur avancé, puis cliquez sur Terminé :
1
2 3 4 5 6 7 |
let fGetValue=(rangeName) =>
let name = Excel.CurrentWorkbook(){[Name=rangeName]}[Content], value = name{0}[Column1] in value in fGetValue |
- Changez le nom de la requête en fGetValue, puis cliquez sur Fermer et charger :
Ajouter un paramètre à une requête Dremio SQL dans Excel
Avec notre requête Dremio SQL et notre fonction générique maintenant prêtes, nous sommes prêts à ajouter la fonction dans la définition SQL à la place de toute valeur fixe que nous aurions pu ajouter à une clause WHERE dans la requête d’origine. Si aucune clause WHERE n’était initialement présente, il sera alors temps d’en ajouter une.
- Cliquez avec le bouton droit sur une cellule dans Excel que vous souhaitez utiliser comme valeur pour un paramètre, sélectionnez Définir le nom
2. Fournissez un nom pour la colonne, dans ce cas, nous choisissons de nommer la colonne region2, notez comment elle capture la cellule spécifique à laquelle ce nom fait référence. Cliquez sur OK:
3. Cliquez avec le bouton droit sur la table de données précédemment chargée dans Excel et sélectionnez Table> Modifier la requête dans le menu contextuel
4. Sélectionnez l’éditeur avancé
5. Dans la boîte de dialogue Requête SQL, remplacez tout prédicat à valeur fixe par des appels à la fonction précédemment créée, en passant le nom de la cellule contenant la valeur du paramètre dans la fonction, puis cliquez sur Terminé. Exemple:
1
2 3 4 5 6 |
let
Source = Odbc.Query(« dsn=Dremio Connector », « SELECT COUNTRIES.COUNTRY_ID, COUNTRIES.COUNTRY_NAME, COUNTRIES.REGION_ID FROM « »Staging.Application« ».COUNTRIES WHERE COUNTRIES.REGION_ID = « & Number.ToText(fGetValue(« region2 »))) in Source |
- De retour dans l’éditeur Power Query, cliquez sur Fermer et charger
2. Nous pouvons maintenant changer la valeur de la cellule dont nous avons changé le nom en region2, puis cliquer sur Refresh All:
3. À ce stade, une nouvelle requête s’exécutera dans Dremio avec le paramètre défini sur notre nouvelle valeur et les résultats seront renvoyés à Excel:
Emballé ?
Garder l’efficacité à l’esprit est fondamental lorsque vous travaillez avec des données. Dans ce didacticiel, nous avons parcouru les étapes pour créer des paramètres dans des requêtes SQL lors de l’utilisation de Microsoft Excel pour consommer des données de votre lac de données ou vos fichiers stockés sur dossier partagé (NAS) à l’aide de Dremio.
Suivre ces étapes vous aidera à augmenter l’efficacité des activités de création de rapports, en particulier lorsque ces rapports nécessitent des capacités en temps réel telles que la granularité des données personnalisée en fonction de la personne qui consulte les données. Nous avons vu comment cela est possible en implémentant des paramètres qui vous permettront d’utiliser le même rapport pour satisfaire les besoins en données de plusieurs utilisateurs au lieu de créer un rapport statique pour chacun d’entre eux.
Il y a de nombreuses ressources disponibles sur le site de l’éditeur si vous souhaitez aller plus loin avec Dremio.
Néanmoins, en tant que partenaire, les équipes de Synaltic seront ravis de vous accompagner à mieux partager vos données dans votre organisation et surtout vous aider à inscrire Dremio tel le point d’accès unique à l’ensemble de vos données.
Découvrez nos dernières actualités :
La Data Fédération Open Source avec Dremio