Configuration du système

Utilisation de SQL Workbench

Objective

After completing this lesson, you will be able to utilisez SQL Workbench pour créer des requêtes SQL.

SQL Workbench

Bien que l'accès direct à la base de données de production APM ne soit pas disponible, vous pouvez interroger la base de données à l'aide de SQL Workbench. Gardez à l'esprit que seules les instructions SELECT peuvent être utilisées ; SQL ne peut pas être utilisé pour manipuler des données dans la base de données ; par exemple, à l'aide de INSERT, UPDATE ou DELETE. Les instructions SQL et leurs résultats ne sont pas stockés. Si l'instruction SQL doit être exécutée plusieurs fois ou si les résultats doivent être capturés dans un extrait ou un rapport, QBQuery doit être utilisé à la place de SQL Workbench.

SQL Workbench peut être ouvert à partir du portail Administrateur sous Outils→SQL Workbench. Pour créer une requête de base, saisissez la requête SQL dans la fenêtre Paramètres d'entrée et sélectionnez Exécuter. Les résultats de la requête s'affichent dans une grille qui peut être exportée vers un fichier .CSV ou Excel.

SQL Workbench, contenant un extrait de code court.

Définition d'un favori

Si vous avez créé une requête que vous souhaitez réutiliser à l'avenir, vous pouvez la définir comme favorite. L'onglet Favoris en haut de la fenêtre de requête contient une liste de requêtes favorites.

Historique

L'onglet Historique ouvre l'écran Recherche dans l'historique du workbench, qui affiche toutes les requêtes qui ont été exécutées dans le Workbench SQL. Vous pouvez également ouvrir cet écran sous Outils→d'administration→Atelier Historique.

Limites d'enregistrement de ligne et d'exportation

Par défaut, SQL Workbench renvoie jusqu'à 500 lignes de données et permet d'exporter jusqu'à 10 000 lignes.

Pour supprimer la limite des résultats de la requête, décochez la case Limiter les résultats ? cadre. Cela renverra un nombre illimité de lignes de données. Vous pouvez également définir une limite différente en modifiant le nombre dans la zone Afficher.

L'écran SQL Workbench. Les options de définition des limites d'affichage et d'exportation des lignes sont mises en surbrillance.

Vous pouvez également utiliser la zone Exporter pour modifier le nombre de lignes pouvant être exportées. Toutefois, vous pouvez également utiliser les options système pour réduire le nombre d'enregistrements pouvant être exportés, jusqu'à un maximum de 50 000. Cette limite remplace la limite définie dans SQL Workbench.

Pour définir une limite d'enregistrements d'exportation :

  1. Sur le portail Administrateur, sélectionnez Options→de configuration.
  2. Recherchez l'option ui.sqlworkbench.export.max.rows.nonlimitedaccess.
  3. Modifiez le nombre dans la zone Valeur d'option.
  4. Sélectionnez Sauvegarder.

Variables dans SQL Workbench

L'utilisation de variables dans les requêtes SQL vous permet de créer des requêtes plus robustes qui peuvent être utiles pour le dépannage, la définition des bonus et d'autres analyses de données.

L'image suivante illustre un exemple de requête dans SQL Workbench qui utilise une variable pour définir la valeur pour le type de courtier.

La première image montre le SQL Workbench, avec un extrait de code SQL qui utilise une variable appelée AGY pour le type de courtier. La deuxième image affiche le résultat, qui affiche uniquement les enregistrements avec un type de courtier AGY.

Chargement d'une requête QB dans SQL Workbench

Bien que vous puissiez enregistrer une requête comme favori, comme nous l'avons vu ci-dessus, SQL Workbench ne stocke pas les requêtes enregistrées. Vous pouvez également créer des requêtes QB qui stockent des requêtes SQL pour la récupération. Nous explorerons les requêtes QB plus loin dans ce cours.

Vous pouvez facilement charger le contenu d'un enregistrement QB Query dans SQL Workbench. Pour référencer une QB Query :

  1. Sélectionnez l'onglet Paramètres d'entrée.
  2. Sélectionnez Obtenir QB Query SQL.
  3. Recherchez la QB Query.
  4. Sélectionnez la requête. Cela chargera la requête dans SQL Workbench.L'écran Recherche QB Query. L'icône Charger requête est mise en surbrillance.
  5. Sélectionnez Exécuter.

Surveillance de l'activité de la base de données

L'écran Activité de base de données affiche toutes les instructions SQL en cours d'exécution dans le système. Vous pouvez afficher les instructions qui sont en cours d'exécution, inactives ou dont l'exécution prend plus de temps que prévu. Une fois l'instruction SQL exécutée, elle n'est plus affichée.

Pour afficher l'activité de base de données, à partir du portail Administrateur, sélectionnez Activité système→Activité de base de données .

Annulation d'un processus

Si un processus prend plus de temps que prévu, vous pouvez l'annuler en cliquant sur l'icône Notifications dans le coin supérieur droit de la fenêtre et en sélectionnant Annuler.

Plans d'exécution

Lorsqu'une requête SQL est exécutée, le serveur lit la requête et détermine le moyen le plus efficace d'extraire les données en fonction des index disponibles. Un plan d'exécution contient les détails de l'acquisition de données.

Dans certains cas, le plan d'exécution peut ne pas être correct, ce qui peut ralentir ou raccrocher les processus SQL. Dans ce cas, vous pouvez créer une demande de plan d'exécution qui déclenche un processus qui récupère le plan d'exécution à partir du serveur. Cela vous permet de résoudre les processus lents en identifiant les problèmes et en personnalisant la requête sans avoir besoin d'aide extérieure.

Exécution d'une requête de plan d'exécution à partir de SQL Workbench

Étapes

  1. Sélectionnez Plan d'exécution en bas à droite.

    L'écran SQL Workbench. Le lien Plan d'exécution est mis en surbrillance.

    Remarque

    L'exécution du plan d'exécution prend quelques minutes.
  2. Dans le portail Administrateur, sélectionnez Outils → Plan d'exécution.

  3. Sélectionnez le nouveau plan d'exécution pour ouvrir les détails.

  4. Faites défiler jusqu'en bas et sélectionnez Télécharger le fichier.

    L'écran SQL Studio, avec un plan d'exécution affiché.

Résultat

Le fichier résultant peut être affiché dans un éditeur SQL tel que SQL Studio.

Tables et zones clés

Avant de pouvoir importer des données, vous devez comprendre la structure de la base de données, la manière dont les tables sont utilisées et comment les tables sont associées et liées entre elles.

Pour vous aider à comprendre les tables et le flux de données entre les tables, nous pouvons diviser les tables en trois groupes :

  • Les tables InFile sont les tables dans lesquelles les données sont chargées à partir de systèmes externes.
  • Les tables de configuration sont principalement utilisées pour stocker les données utilisées pour gérer les enregistrements de transactions.
  • Les tables de transaction contiennent des références aux données clés de configuration utilisées pour traiter la transaction.

Regardez la vidéo pour obtenir une synthèse des tables clés.

Le diagramme ci-dessous montre que chaque table de configuration a une table inFile correspondante. Par exemple, la table Broker a une table inFile correspondante appelée inBroker.

Table Synthèse du modèle de données

Il y a beaucoup plus de tables, mais vous n'avez pas besoin de toutes les connaître. Il vous suffit de connaître les zones clés dans certaines tables couramment utilisées : Broker and BrokerDetail, Customer, Transaction Header (TranHead), Transaction History (TranHis) et BrokerHistory.

La table Courtier contient des informations sur les hiérarchies et l'affectation du client et de la propriété de police.

La table Détails du courtier contient les détails du courtier qui peuvent changer au fil du temps, tels que la date d'expiration et le type d'activité.

La table Client contient la fiche de chaque client. Un exemple de client mappé dans APM serait un groupe de salariés, un membre d'un régime de santé individuel ou un souscripteur d'assurance. L'enregistrement client contient généralement uniquement des informations démographiques de base et ne contient pas de détails de couverture sensibles à la date.

La table TranHead contient les informations d'en-tête statiques pour l'opération de facturation.

La table TranHis contient les détails de chaque transaction datée. Il est utilisé comme information source pour le calcul du montant de la commission.

La table Historique du courtier contient la synthèse de tous les soldes de courtier, y compris les commissions acquises et payées par période de facturation. Les enregistrements de cette table sont générés lors de l'enregistrement d'une transaction.

Pour afficher la liste complète des tables et zones clés associées, reportez-vous au sujet Vérification des structures de données inclus dans le dernier chapitre de ce cours.

Zones clés et relations entre tables

Comme pour n'importe quelle base de données, pour garantir l'intégrité des données, chaque table comporte au moins une zone clé utilisée pour identifier de manière unique chaque enregistrement. Certaines tables ont plusieurs zones clés, mais une seule zone est la clé primaire. Dans APM, il s'agit du numéro d'objet. Par exemple, dans la table Courtier, la zone serait Numéro de courtier.

Les zones clés utilisées pour associer les enregistrements entre les tables ont le suffixe Val. Examinons un exemple que vous verrez souvent dans APM : la table d'adresses.

La table Adresse est non seulement référencée dans plusieurs autres tables, telles que la table Courtier et la table Client, mais les courtiers et les clients peuvent avoir plusieurs adresses. Par conséquent, une instruction SELECT dans SQL Workbench peut renvoyer une liste comme celle de l'image suivante.

SQL Workbench affiche les zones clés dans la table Adresse : AddressNo, AdrObject, AdrVal et AdrType.

Le champ AddressNo est la clé primaire de la table Address. Cette zone sera toujours unique dans cette table. D'autre part, notez que le champ AdrVal, qui mappe chaque adresse à un enregistrement Courtier, est le même sur les deux premiers enregistrements. Cela nous indique qu'un seul courtier a deux adresses : une pour le bureau et une pour la maison.

Clés externes

La base de données APM utilise largement les clés étrangères pour garantir l'intégrité des données. Une clé externe est une zone d'une table qui fait référence à la clé primaire dans une autre table.

Si une table contient des clés étrangères, le nom sera presque toujours identique à la clé primaire de la table associée. Prenons l'exemple de la table Courtier, qui a une relation plusieurs à plusieurs avec la table Fournisseur. Une table de jointure appelée BrokerVendor a des clés étrangères pour les deux tables.

Diagramme affichant la relation entre les tables Courtier, Fournisseur courtier et Fournisseur.

La zone BrokerNo est la clé primaire de la table Broker.

La zone VendorNo est la clé primaire de la table Vendor.

La zone BrokerVendorNo est la clé primaire de la table BrokerVendor.

La table BrokerVendor a des relations de clé étrangère avec les tables Broker et Vendor. Les zones BrokerNo et VendorNo de cette table sont des clés externes. Dans cet exemple, la contrainte de clé étrangère empêchera la suppression d'un enregistrement Courtier ou Vendeur associé à un enregistrement BrokerVendor existant.

Clé externe "Clé combinée"

Dans APM, certaines tables ont des relations de clé étrangère "Clé combinée". Les clés combinées permettent d'associer un enregistrement à plusieurs enregistrements dans une autre table.

Prenons l'exemple des adresses des courtiers. Comme nous le verrons dans l'unité suivante, la même entité de courtier peut être stockée dans plusieurs tables, y compris Courtier, Producteur et Fournisseur. Cela signifie qu'une seule adresse peut être associée à plusieurs enregistrements. Nous ne voulons pas stocker la même adresse plusieurs fois. Par conséquent, la table Adresse comporte un champ Clé étrangère combinée qui est utilisé pour joindre chaque adresse à un courtier, un producteur et un fournisseur.

Pour permettre le stockage des adresses de plusieurs entités dans une seule table d'adresses, la table Address contient deux champs qui fonctionnent ensemble : AdrVal est la clé externe pour le BrokerNo et AdrObject indique au système que l'enregistrement associé est un courtier.

Relation entre les tables Courtier et Fournisseur et la table Adresse, à l'aide de clés étrangères.

Dans l'exemple ci-dessus, Max Martinez est à la fois un courtier et un fournisseur. Naturellement, les deux enregistrements ont la même adresse. La table Address contient donc deux enregistrements : un avec AdrVal pointant vers le BrokerNo (MM-101) et AdrObject défini sur Broker, et l'autre avec AdrVal pointant vers VendorNo (MM-101V) et AdrObject défini sur Vendor.

Utilisation du Dictionnaire ABAP et du Workbench SQL

Exemple de gestion :

Dans cet exercice, vous allez utiliser le dictionnaire de données pour rechercher des détails sur la table Producteur dans APM, puis utiliser le Workbench SQL pour interroger et exporter les données dans la table.

Étapes

  1. Utilisez le dictionnaire de données pour trouver des détails sur la table Producteur.

    1. À partir de l'icône Aide, sélectionnez Dictionnaire de données.

      Le menu Aide APM, avec la sélection Dictionnaire de données mise en surbrillance
    2. Saisissez Producteur dans la barre de recherche.

    3. Sélectionnez l'entité Producteur .

    4. Sélectionnez le champ ProducerNo et vérifiez les informations sur le champ.

    5. Sélectionnez le lien Producteur pour revenir à la vue d'entité Producteur.

  2. À l'aide de SQL Workbench, saisissez une requête SQL pour répertorier tous les producteurs.

    1. Dans le portail Administrateur, sélectionnez Outils → SQL Workbench.

    2. Saisissez l'instruction SELECT suivante pour rechercher un seul enregistrement dans la table Producer :

      Code Snippet
      1
      SELECT * from Producer WHERE ProducerNo = 16813280486430000
    3. Sélectionnez Exécuter.

  3. Faire de cette requête un favori.

    1. En bas à droite, sélectionnez Créer un favori.

    2. Sélectionnez OK.

    3. Pour vérifier cette étape, sélectionnez l'onglet Favoris en haut de la fenêtre de requête.

  4. Charger une QBQuery dans SQL Workbench

    1. Sélectionnez l'onglet Paramètres d'entrée.

    2. Sélectionnez Obtenir QB Query SQL.

    3. Dans la fenêtre Recherche QB Query, saisissez Paiement dans le champ Nom.

    4. Sélectionnez Rechercher.

    5. Sélectionnez Extrait de paiement.

      L'écran Recherche QBQuery.
    6. Sélectionnez Exécuter.

  5. Exportez les résultats dans un fichier .XLSX.

    1. Sélectionnez le lien XLSX en bas à droite.

    2. Une fois le fichier téléchargé, ouvrez-le dans MS Excel et affichez les données.

Synthèse

  • SQL Workbench est une interface utilisée pour interroger les données APM à l'aide de requêtes SQL.
  • Par défaut, SQL Workbench renvoie jusqu'à 500 lignes de données et permet d'exporter jusqu'à 10 000 lignes.
  • L'utilisation de variables dans les requêtes SQL vous permet de créer des requêtes plus robustes qui peuvent être utiles pour le dépannage, la définition des bonus et d'autres analyses de données.
  • SQL Workbench ne stocke pas les requêtes enregistrées. Vous pouvez également créer des requêtes QB qui stockent des requêtes SQL pour la récupération.
  • Les tables APM incluent les tables InFile, les tables de configuration et les tables de transaction.
  • Les tables couramment utilisées incluent Broker and BrokerDetail, Customer, Transaction Header (TranHead), Transaction History (TranHis) et BrokerHistory.