SAP Developer Challenge - APIs - Task 2 - Calculate Northbreeze product stock

[qmacro]

(Check out the SAP Developer Challenge - APIs blog post for everything you need to know about the challenge to which this task relates!)

In this task you'll move from the public Northwind service to a simple version powered by CAP, and explore data with an OData operation and some system query options.

Background

The OASIS curated Northwind service is great, but it's also sometimes useful to have one's own version. There's an extremely simplified version of the classic Northwind service, called Northbreeze (get it?) at https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze.

This Northbreeze service is powered by the SAP Cloud Application Programming Model (CAP) and offers four entity sets:

• Products
• Suppliers
• Categories
• Summary of sales by years

(Well there's technically a fifth, TotalProducts, but that's just a calculation projection on the count of products).

The reason for running our own version of Northwind is that we can modify and extend it as we see fit, plus being based on CAP, we can learn about and experiment with CAP's rich support for serving OData APIs.

In this task you'll start to become familiar with the data offered.

Specifically for this task, you'll need to become familiar with the Products data. To do that, have a look at the Northbreeze service's metadata document at https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze/$metadata.

Identify the EntityContainer element that describes the entity sets available, in the form of EntitySet elements, and find the element describing the entity set with the name Products, which should look like this:

<EntitySet Name="Products" EntityType="Northbreeze.Products">
 <NavigationPropertyBinding Path="Category" Target="Categories"/>
 <NavigationPropertyBinding Path="Supplier" Target="Suppliers"/>
</EntitySet>

You can see that this entity set is a collection of Northbreeze.Products entity types. The 'Northbreeze' part is essentially the namespace, generated based on the service name. Follow the trail to the Products entity type, which will be an element outside the EntityContainer element, but still within the Northbreeze-namespaced Schema element.

The Products entity type should look like this:

<EntityType Name="Products">
 <Key>
 <PropertyRef Name="ProductID"/>
 </Key>
 <Property Name="ProductID" Type="Edm.Int32" Nullable="false"/>
 <Property Name="ProductName" Type="Edm.String"/>
 <Property Name="QuantityPerUnit" Type="Edm.String"/>
 <Property Name="UnitPrice" Type="Edm.Decimal" Scale="variable"/>
 <NavigationProperty Name="Category" Type="Northbreeze.Categories" Partner="Products">
 <ReferentialConstraint Property="Category_CategoryID" ReferencedProperty="CategoryID"/>
 </NavigationProperty>
 <Property Name="Category_CategoryID" Type="Edm.Int32"/>
 <NavigationProperty Name="Supplier" Type="Northbreeze.Suppliers" Partner="Products">
 <ReferentialConstraint Property="Supplier_SupplierID" ReferencedProperty="SupplierID"/>
 </NavigationProperty>
 <Property Name="Supplier_SupplierID" Type="Edm.Int32"/>
 <Property Name="UnitsInStock" Type="Edm.Int32"/>
 <Property Name="UnitsOnOrder" Type="Edm.Int32"/>
 <Property Name="ReorderLevel" Type="Edm.Int32"/>
 <Property Name="Discontinued" Type="Edm.Boolean"/>
</EntityType>

Amongst other things, you can see that a product has an ID (ProductID), a name (ProductName), a count of the number of units currently in stock (UnitsInStock) and a boolean that is used to indicate whether or not a product is discontinued (Discontinued).

Request the first few products to see data for these and the other properties, via https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze/Products?$top=5. You should see something like this:

{
 "@odata.context": "$metadata#Products",
 "value": [
 {
 "ProductID": 1,
 "ProductName": "Chai",
 "QuantityPerUnit": "10 boxes x 20 bags",
 "UnitPrice": 18,
 "Category_CategoryID": 1,
 "Supplier_SupplierID": 1,
 "UnitsInStock": 39,
 "UnitsOnOrder": 0,
 "ReorderLevel": 10,
 "Discontinued": false
 },
 {
 "ProductID": 2,
 "ProductName": "Chang",
 "QuantityPerUnit": "24 - 12 oz bottles",
 "UnitPrice": 19,
 "Category_CategoryID": 1,
 "Supplier_SupplierID": 1,
 "UnitsInStock": 17,
 "UnitsOnOrder": 40,
 "ReorderLevel": 25,
 "Discontinued": false
 },
 {
 "ProductID": 3,
 "ProductName": "Aniseed Syrup",
 "QuantityPerUnit": "12 - 550 ml bottles",
 "UnitPrice": 10,
 "Category_CategoryID": 2,
 "Supplier_SupplierID": 1,
 "UnitsInStock": 13,
 "UnitsOnOrder": 70,
 "ReorderLevel": 25,
 "Discontinued": false
 },
 {
 "ProductID": 4,
 "ProductName": "Chef Anton's Cajun Seasoning",
 "QuantityPerUnit": "48 - 6 oz jars",
 "UnitPrice": 22,
 "Category_CategoryID": 2,
 "Supplier_SupplierID": 2,
 "UnitsInStock": 53,
 "UnitsOnOrder": 0,
 "ReorderLevel": 0,
 "Discontinued": false
 },
 {
 "ProductID": 5,
 "ProductName": "Chef Anton's Gumbo Mix",
 "QuantityPerUnit": "36 boxes",
 "UnitPrice": 21.35,
 "Category_CategoryID": 2,
 "Supplier_SupplierID": 2,
 "UnitsInStock": 0,
 "UnitsOnOrder": 0,
 "ReorderLevel": 0,
 "Discontinued": true
 }
 ]
}

Your task

Your task is to calculate the total stock quantity (i.e. the total units in stock) for all current products, i.e. products that are not been marked as discontinued. The result of this calculation should be a number.

Once you have calculated the number, which should be an integer, you should hash it and post the hash as a new reply to this discussion thread, as described in Task 0 - Learn to share your task results and in a similar way to how you've done this in the previous task.

Hints and tips

Like all tasks in this challenge, you are free to approach this one however you see fit. One way would be to request all the products (https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze/Products) and manually sum the values of the relevant UnitsInStock properties.

But where's the fun in that?

How about requesting the entire products entity set in your favorite language and obtaining result by parsing the response and using that language to make the calculation?

You could also use OData's $filter system query option to first reduce the entity set result to only those products that have the value false for the Discontinued property.

And what about the $count facility, which in OData V4 is now a system query option as well something you can append to a resource path?

This would also be a good opportunity to take your first steps exploring some great new OData V4 features supported by CAP, such as data aggregation.

For discussion

How did you approach this task? If you used a programming language, which one did you use, and how did you do it? If you used an $apply based data aggregation feature, what was it, and was was your experience using it?

[Suneeth_P]

82ce9469db39fd63d128678f040bd32199a5a5edd69d09f480b277b12631da74

[elkhenati]

525afd619ae4a22984c09aaef127a863888010b815f49b70846e1d8ef96dbf90

[d067595]

10f1978f4c292269d17b77d8aad426bb6bb1a71f1bbdb777cc4561603d86f001

[kamesh-sap]

7823dff55716f81382d0dcf332ae313de787d9937022b62358ec24cc963404bd

[szeteng00]

a1b3613b9d874c0f866740032fa79902c204a4acc0f865147c8420bc15537824

[hunamm]

4b3706ee3e63cd90f5434b559103f7487e822be4f47cfaeb1941d8ec9c81b27f

[ramana_salapu]

d6ac69225d477ae3c2c7128579d7b7cdb22cd38a57a9b2e18df8ab982a8030d2

[ofilho]

48de0888fb85849b92398b1bad5a21115fb4b87f39d8120439fdf38711c8d709

[vinaychowdary]

3eb1f01ffb6f852a033b9ddd27c71da853fc94a30d2fd2f5ef9aef3f0802eb8b

[cinemandre]

82ebbd61444f7b39ede46beb76616a7a5641a8b312e1bc99ea871f4f04084d20

[cinemandre]

Doing it with postman where the response of the request is processed directly with test. Great to test out some basic postman functions like Runner where several calls can be combined (first the task then the translation to hash)

[qmacro]

Nice one @cinemandre ! Yes, while I'm a `curl` person always, Postman has its place and is very powerful.

[tly]

9978ee68a2377fa7cd2d27086e003eda0e771bddd74513a583a00d6f4675e032

[arunneerolil]

c4bdca6889a4af43755bc79739f97959bcfba2905882c9170efc20037761beef

[jmuiruri]

253a1b0346f37f59d99d1ce5834687ee034c774c0cf0938b272f88f552799275

[ktrkanjec]

393e70054e73bbaf42e702d37c6185d44bcc4a55595d161dd08ab4a9cf180616

[VinitaSangtani]

c99f8b2b7847d7dc24512ff6e4f5e8384479a1ca3666b6c224fd896f4cc79617

[HelenaFortun]

020e753c0741b1b9133b0c6e6a75ed2a39065c5032b23cd1a51f8592058c5bfc

[salilmehta01]

0868ddf2083b3306084de1e3376376908a556cfacce2d943017183fdfd5fd5bd

[SubaR]

e42cb32b4fe33eb911a9379e48b0372b03a7f61ee0f7d5542d2938f017b764ec

[RafkeMagic]

519e9f7294151f78cffa78619335d3ca2934beea58c42aea4e87e5e9beefa5ce

played around a bit with $apply... pretty cool

[DazOwens]

593ad02ddd69675ebb6cfb32f3064300dd6358eee6b03a41c3191c84a8585508

[DazOwens]

I have enjoyed '$apply'ing myself 🙂

[qmacro]

Excellent pun, I for one appreciated that 🙂

[lvhengel]

267f1292a1a3b1897ee5ff89b1e146e27fb87dfe71c0f0ae5809b49d0aec82c7

[Nicolas]

5e01867f09cebaa1d7769fafb06bb32f75b5d6081acf726c313a10a3b4ea5d62

[Sudhakaran]

8cfd276fb29dc1633d88330c64abb41bd8a58b54d79036a6e6e34612b5cc2c50

[ArindamSAP]

66413e60b389961f0465883889fc2a7ca6708ff6c6e49d380afdda6f3919cd75

[johna69]

2501c9e941c7b5b1592d69fe59c284781bc83b560ef449b984ab7adc1a0e097a

[former_member136915]

c5de127adf021a4c6ff0ec3048af9d0c6e91a9a1a963ec374a5e24363038f9e5

[lehuynhnam]

My approach:
1. lazy approach: using postman with apply, filter `Discontinued` then aggregate the `UnitsInStock` 

2. user approach: using excel to retrieve the Odata then pivot the table data

I prefer to use excel since Microsoft just announced to combine my favorite programming language python and excel haha.
Announcing Python in Excel (microsoft.com)
PS: I'm not sure how long does it take to work on ABAP as there are many steps to do, for instance,  define destination, trust setting, certificate then, write a bunch of code to receive the response and aggregate. 

[devrajsinghr]

1c76209f961af74a9a57438cd1508d652b3b03ff638813f3f28d709ced9fd964

I am assuming my community id is my user name - devrajsingh (Initially I used my sap user id which was Pxxxxxx)

[qmacro]

@Former Member and @lehuynhnam - you might want to check the instructions in Task 0 as to how you should post your hash answer 🙂

[Cmdd]

0bc8f378a64a944bfefc8e3d9ec1702efec6138d338d0918b065c4d95118c745

[ec1]

0e933d52b9cb2d6b5de7aa87843253d256833973b567f22ae07d1614a8851d36

[Flavio]

fd861a039e38c749a355e4a74c2c4c4dc3ce55b86790c82238341486b7d731a8

[bugsanderrrors]

229bd2e15ef8d8d0e56c59da9f079d8a3804a589ab4ace28ca115c3ac1b0c4d7

[adrian-ngo]

287937502197c6bec998a6cb26d8d030c8c76b9b62967a7e84f10007f4b57af5

[lehuynhnam]

9eb1882b816a2029f38c67ed3ef5f6b9659dff5e55c0e8fbc669568817163bce

[andrew_chiam]

9d767f7aaaf2a5fbd55f0e063f05de17194d0c78b928ad1887ed60488d1df03b