Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

qmacro
Employee
Employee

(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?

208 REPLIES 208

seVladimirs
Galactic 5
Galactic 5
0 Kudos

3074a722566a0d38b50a5f3f5c2804ed4fa37dcc94f8f33ed2586a9f6c56f3ab

I was today years old when I found out that OData v4 supports data aggregation 🫣

Even though I've used $apply - I also used JS to fetch all Products where the Discontinued attribute is false and then used `reduce`. Trust no one! 😅

by the way, on which level aggregation is done, is it database or application layer? 

Agree - trust but verify, at least! Aggregation with `$apply` is built in. The Northbreeze service has neither CDS definitions in there, nor any implementation. It comes for free out of the box with CAP.

0 Kudos

Yup Map and reduce works well in JS

ajmaradiaga
Employee
Employee
0 Kudos

7e30e0c2f9e6214f7dcda4c45056ee7b1755f4a0c3efa73f0b568d93147e6123

I've been trying to solve the challenges using ChatGPT. In this task, it consistently provided a wrong answer haha. As magical as they might seem, can't fully trust the output of those models :-). I end up querying using OData and jq to do the sum.

jq "[.value[].UnitsInStock] | add" response.json

 

@ajmaradiagaI used ChatGPT4 to get the Python code, and it worked great! I also double-checked my answer using OData v4 data aggregation.

0 Kudos

I looked at the Python code, and it looked okay... I imagine it will give me the correct answer. In this instance, I explicitly asked for the actual result... I want it to calculate it for me and not have to run Python on my laptop. In this case, the result was incorrect.

se38
Galactic 4
Galactic 4
0 Kudos

76a789b93f7545767ddf4b44cd4a37a7e2b976140a10c1e4c262568daf527d99

The hashed "value" should only be the number, right?

I've used "apply" with filter and aggregate.

Thanks for asking, @se38 - the text in the "Your task" was a bit vague. I've modified it, thanks! And yes, it's just the number (total stock) that should be sent to be hashed 👍

ceedee666
Galactic 3
Galactic 3
0 Kudos
cfa7b85bac1866665ea24e08e8bf20472ceae1b42d1dcca8778597789bd04be0

I solved this task using three approaches:

  1. Getting all data form the service and perform the filtering and aggregation in Python 🐍
  2. Filtering the data using OData '$filter` and the aggregation in Python 🐍
  3. Using OData data for filtering and aggregation as described

Thanks @qmacro  for providing the nice hints!

It's great to have so many possibilities. Thanks for sharing, and thanks for doing this task in multiple ways 💪

FooThePolarBear
Galactic 2
Galactic 2
0 Kudos

94d74a329419222662e42356a0ba4c5c07ceaf9b550c53cabc0fa45847d39080

SandipAgarwalla
Galactic 4
Galactic 4
0 Kudos

6141c65b9ea179fcb34c1ba2505fd19badcbf79d77b1cd789632045fc0ca35aa

nicoschoenteich
Employee
Employee

d8342a9512672b9987909600065c0da57f64a77f93f400259e04afd1d31e565f

SandipAgarwalla
Galactic 4
Galactic 4

I used couple of ways to get the total number

1) wrote a small nodejs program, to get all the products and then using the filter and sum approach

2) used $apply to filter and aggregate. Took a while to get the correct syntax. But $apply & aggregate is so powerful, it was a great learning. 

Nice once @SandipAgarwalla . In your JS program, what did you use? For example: in mine, I used filter and reduce, of course, as I'm a fan of the functional style of programming. I find reduce a particularly powerful and beautiful function. 

I've written a few posts on my blog about reduce. Here's one: ES6, reduce and pipe.

0 Kudos

Hi @qmacro - in the nodejs program, I just got the entire list of products and then did the filtering and counting. Yes, I could have used the $filter option to reduce the result set. 

0 Kudos

I was referring to the `filter` (and `reduce`) functions in JS, not the `$filter` system query option of OData 🙂

0 Kudos

oops, my bad. Not quite getting the use of `reduce`. would love to see an example and learn more on this. 

harsh_itaverma
Galactic 4
Galactic 4
0 Kudos

30271d0c9c3a30e06cb955411e56c388e591ce1fa9606758a269cf97fa7c9cb4

harsh_itaverma
Galactic 4
Galactic 4

Used data aggregation to get the total Unit in stocks; with $apply query option, first used a filter transformation to get Current Products and then used aggregate transformation with sum method to return the total value. 

Being a JavaScript developer, just to make sure I was right; I did parse the response and used array traversing to get the total too! But the data aggregation approach was much easier and cleaner.

Great work @harsh_itaverma ! Feel free also to share more detail of what you did. I love learning from other folks' approaches.

The first approach was using $apply; I tried it with just aggregate, then with Discontinued products and with Current Products and it looked like the answer was correct.

Then for the JS approach,

Stored all the products locally;
I tried filtering the response and getting just the current products using filter function of array to get a new array called CurrentProducts.
and then used reduce function on this array to calculate the sum.

Great stuff. As I mentioned in another reply in this thread, I do love `reduce`. It's the mother of all (or at least many) functions, in a way.

Frank_Haschick
Galactic 3
Galactic 3
0 Kudos

c6fdf55191c39d672105bc098231babac4a26962c22f5b457fe557137d392d47

shotokka
Galactic 3
Galactic 3
0 Kudos

f4242d053d8369a4c422dfecb9a0214c6f6000d8597394e91e049a87f2677303

se71
Galactic 2
Galactic 2
0 Kudos

fa47e3ba263d9495192229021ad8dc8b71dbd77196e894d9ed14978506f28104

turanaydin
Galactic 2
Galactic 2
0 Kudos

69d27786f0cf5cdee7d8dd407bfa431cc55e57fc497bc434543f929d6fd6e51b

SyamisUnique
Galactic 3
Galactic 3
0 Kudos

0e501f74fc889f920ce3e2f2d8c8503237b064c31b847c9e1f06be5443570ca7

stickman_0x00
Galactic 3
Galactic 3
0 Kudos

b1b237619ea0fbade868f5e530a786ec0d13ab7f8071d9a6f0a63b8c1b26f8fc

With query options + loop of products in Go

0 Kudos

Don't forget - your hash replies should contain the hash, and nothing else 🙂 But please feel free (and indeed encouraged) to add more comments (e.g. about how you went about the task) in separate replies 👍

emiliocampo
Galactic 3
Galactic 3
0 Kudos

d31f01c4a5d1cbee3cb5944f13935ffdc69e6440a5fafaac4b94955aca59ec45

martinstenzig
Galactic 4
Galactic 4
0 Kudos
d2d356ab2c311b5d9c215a04a17458e8214cf844d289a3bde6b7aaa83e1f3224