During Momentum, I covered a session on API calls for SemiOps and DemandOps focusing on the reporting APIs. The agenda included how to connect to the APIs, how to use them, the performance considerations, and a walk through of advanced querying using the Power Query tool on Microsoft products.
Both SemiOps and DemandOps have APIs, and they are slightly unique in the types and ways to connect. SemiOps is a Odata query and DemandOps is a Web JSON query. Also, the credentials are a bit different. For more details on how to connect to the APIs, read this blog post.
How to Use the APIs
There are several different API reporting tools available that can consume APIs. Excel and Power BI are the most familiar tools to most Tensoft customers.
Excel is better suited for sort of one-off queries. If you’ve an immediate need to analyze certain data and look at transactions, it’s a bit quicker to build the query, run it and format it and get it in a list format or a pivot table or a chart with Excel. It is useful for ad-hock reporting and detailed data analysis.
Power BI gets its power in more standardized visuals that you can share with multiple people. So, if you’ve got a report requirement that would be useful to many different users and you want users looking at the same data and you want to invest a little bit more time in the way it looks and data cleansing, then Power BI is useful.
The Power Query editor – building the data sets, connecting to them and cleaning the data, filtering – is the same, but the way it is presented is a bit different. So, both are good tools, but depending on ultimately who the user of the reports is might define the approach you take.
You can load the raw data directly as it is or transform the results using Power Query. Multiple data sources can be added in Power Query. So, you can reference DemandOps, SemiOps, Business Central or an Excel file on SharePoint, etc. There are connectors to various applications.
If there is some common relatable field like a customer ID or a part number that you can relate multiple systems together, the Power Query is a good way to merge data from disparate systems where there is some sort of relation.
Performance Considerations
The data sets aren’t too large. The LotHistories data set in SemiOps is the largest. It’s all the receipt transactions or scrapping or any kind of transaction that is done in the system.
All queries are real-time except for ‘InventoryInfos’ in SemiOps. The values on this API are updated when the Compute Cost process is run in SemiOps. This API takes inventory on hand and values it with the current standard cost. So, if you do use the inventory on hand API, make sure you run the Compute costs at whatever frequency works best for you and your users who are running their reports.
The DemandOps APIs can include filters in the APIs which can improve your query time. If you have multiple companies, you will need to create 2 APIs to call both the companies as each API has a unique company code.
To learn more about Tensoft products and its services, contact us today!