How to load data from LUY in Excel via Power Query
It is possible to load data from LUY into Micrsoft Excel 365 via the REST API. This provides the opportunity to create an Excel list of multiple related elements and assigned attributes.
The following screenshot shows an example:
This example shows a list with related
“business objects” - “business process” - “information systems” - “technical components”
and with their assigned attributes “Sicherheitskritikalität” and “IST-Zustand”.
Prerequisites
To use the LUY REST API via Power Query the following is required:
Microsoft Office 365 Excel
Technical user for the access to the LUY REST API
How to get the data from LUY
The following steps describe how the Power Query can be used in Excel via the LUY REST API.
In this example data from “information systems” is used:
Open MS Excel 365.
Click on the “Data” tab.
Click on the button “From Web”.
Enter the URL of the LUY REST API endpoint in the dialog “From Web”.
In our example we use the endpoint for information systems:
“http://localhost:8080/luyexcel/api/data/InformationSystem”.
Switch to the “Basic” tab in the open dialog “Access Web Content“.
Enter the technical user credentials for the LUY REST API.
Click on “Connect”.
Click on “List” in the “result” row to continue.
Click on the button “To Table”.
Click on “OK”.
Click on “Expand” (The button marked in yellow).
Choose the needed columns in the list.
For each column click on “Expand” and “Extract Values..”
On some of the columns, e.g., relations, it is necessary to “Expand the New Rows” first
and then “Extract the Values”.
Click on “Close & Load”
Click on the edit button to change the shown columns or add additional columns.
How to relate different building block types
Via Power Query is possible to connect different building block types with one another in one list. In this example, technical components with their columns “Name” and “Hersteller” are used, and relations to infrastructure elements.
For this uses case it is necessary to use the columns:
infrastructureElementAssociations.id of technical components
technicalComponentAssociations of infrastructure elements
Click on “New Sources“ - “Other Sources” and “Web”.
Enter the required REST API endpoint in our example it is “localhost:8080/luyexcel/api/data/Infrastructureelement” and click on “Ok”.
The following screenshot shows the table infrastructure element with the columns “Name” and the column for the relations' ID to technical components.
Go back to the table of “Technicalcomponent”.
Click on “Merge Queries”.
Choose “Infrastructureelement” in the drop-down menu.
Select the columns “Column1.technicalComponentAssociations.id“
and “Column1.infrastructureElementAssociations.id“
For our example, the option “Inner (only matching rows)” is set for “Join Kind”
Click on “Ok”
Now the table “Infrastructure” is added as column in the “Technicalcomponent” list.
Expand “Infrastructure”
Click on “Close & Load”.
Click on the edit button to change the shown columns or add additional columns.
It is possible to connect more than one building block via the relation ID. That provides the possibility to show data connected over multiple relations.
It is possible to sort and filter the loaded results with the default options of Excel.