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
Developer Advocate
Developer Advocate

(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

... apply for the win and all the way. 

Dan_Wroblewski
Developer Advocate
Developer Advocate
0 Kudos

47f972a9d554e8daef9180cde1f3a00a6efbbff721cdc0d30f78c0dbeb8c9539




--------------
See all my blogs and connect with me on Twitter / LinkedIn

GRABLERE
Product and Topic Expert
Product and Topic Expert
0 Kudos

dbe21d49be8189a618688ff97e6cf1cab0bb6d3c0d695b4885267c41ce93ab8f

cguttikonda24
Participant
0 Kudos

66fc499e64b8c37a6abd76508a034eafe494c0df42cfd81bbee6d50b5501f4fc

govardhansahil
Explorer
0 Kudos

8d14930211a7d41fbba7fcc20b9563c99bd802d8c9596086fa1368bb288fad6b

Performed GET using $apply with aggregate on UnitsInStock grouped by Discontinued.

0 Kudos

b64d374a8fd39fe32e5f4966cb3448dc8aa44642cbd50c3c47d062c938024ee8

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

f95d1475279f8a34d075f15c29badc4695c5db41644e645920045f5ac418f93d

huseyindereli
Active Contributor

c2731aa8f57f8e5fd6ff625decd245d0fa4ad7947d74b942da29d7d48f5fa684

Sandra_Solis
Participant
0 Kudos

bd3aca24289970265e6a48afddd63ec3b6c707f3b722c9d730d3087ed03587ce

JohnPaulLiberal
Explorer
0 Kudos

558e12543aa95ba96de1e63218e04e46b155d85da589fcea49437b6edd5e3fc6

choujiacheng
Explorer
0 Kudos

9b30692cae2f2a5d0665f7a1781595b41c3cbc8626784237d8341bb64d3128e3

Using the OData V4 data aggregation feature, at first it seemed a little bit daunting. However, it eventually improved when I figured out what to do. I am a bit mixed on the need to declare all of my filters and calculations via the apply feature, or at the very least I could not figure out how to do it outside the apply function, as doing filters outside it will cause the OData to not send a value. This might also be the fact that the OData might be filtering the new values instead of the old values outside the apply function, but I yet to test that part myself. Regardless, I think I figured out on what to do for this challenge and it became manageable by the end.

qmacro
Developer Advocate
Developer Advocate

Thanks for the great comment and your thoughts. Indeed, you are on to something with your conjecture there.

Consider this query: https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze/Products?$apply=filte...

This produces:

{
"@odata.context": "$metadata#Products(Category_CategoryID,TotalStock)",
"value": [
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 539,
"Category_CategoryID": 1,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 507,
"Category_CategoryID": 2,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 386,
"Category_CategoryID": 3,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 393,
"Category_CategoryID": 4,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 282,
"Category_CategoryID": 5,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 136,
"Category_CategoryID": 6,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 74,
"Category_CategoryID": 7,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 701, "Category_CategoryID": 8,
"@odata.id": null }
]
}

If you then add a "regular" $filter system query option, you are filtering on this result set. For example, let's filter this output to just those category IDs with a total stock of more than 500:

https://developer-challenge.cfapps.eu10.hana.ondemand.com/odata/v4/northbreeze/Products?$apply=filte...

{
"@odata.context": "$metadata#Products(Category_CategoryID,TotalStock)",
"value": [
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 539,
"Category_CategoryID": 1,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 507,
"Category_CategoryID": 2,
"@odata.id": null
},
{
"TotalStock@odata.type": "#Decimal",
"TotalStock": 701,
"Category_CategoryID": 8,
"@odata.id": null
}
]
}

I can't get this platform to stop swallowing whitespaces so this formatting is a little difficult to read. But you can go to the URLs to see the results too. 

ajos
Explorer
0 Kudos

94bde52261c517a5fba113d07b26f767796bfb2358f32d8ee27c62604fa46c9e

I have used odata filter and aggregate to get the sum.

I used node.js to confirm if the sum is correct

qmacro
Developer Advocate
Developer Advocate
0 Kudos

Great stuff, thanks for sharing!

raulguti
Discoverer
0 Kudos

43a4b418126725ff04dfde0d6a8657102e104a27fd831972a6d079d41fb3294f

Petchimuthu_M
Associate
Associate
0 Kudos

45e34bee5a1c7924bb75cd77a83cff4c94742f693d8bd5c082923ab870fe0d2b

AbapAndy728
Explorer
0 Kudos

df1cf180aca6ff29f887f3268c5ee5c2f894b4403e26fa3824dbf47260e52e3f

bztoy
Participant
0 Kudos

950101b4449e967aebf43e5124c665c873f6f32401ee123f1bf6d01b61e4fec1

jmalappil
Discoverer
0 Kudos

451deb629673d6674a7f52bc69a6886da81d6b30a2f090c2a7e8905297280092

bztoy
Participant

discussion:

I tried to solve this problem with 3 different approaches.

1. using $apply with filter and aggregate sum, it is amazing.

Hint: I saw a tweet from Nico this morning about the $apply in this challenge then I realized this is what I have to learn today 🙂 😉

2. My main language is ABAP but since I am learning TypeScript, I am also using TypeScript to calculate the total stock and Hooray, it seems my answer is correct. 😄😄

task-3-ts-captured-screen.png

3. Using curl and jq. with this approach, this was the 1st time that I used jq functionality like slurp (-s), map and add that you (DJ) taught us in the hands-on live session to solve real problem. It was a very nice feeling. 😎

In the 3rd approach, I found that I have to manually replace blank(space) with %20 in the $filter block to be able to fetch an OData API. If I misunderstood the thing, please help correct me.

btw, I also learned the different between $count and $count=true in OData V4 since I haven't use it often.

my understanding now is

append /$count in the resource path will return a number of items of the collection (calculated after any $filter, $search are applied) as a response.

$count as a system query will add property "@odata.count" as a count of the matching resources included with the resources in the response.

 

Thanks,

Wises

 

qmacro
Developer Advocate
Developer Advocate

Hey Wises, nice to see you on this challenge!

What a great reply, thanks for sharing what you did, and what you learned. You're spot on about the $count mechanisms, they did change a bit between OData V2 and V4 (I have a little bit on this, plus some links to further reading, from a talk that I gave at an SAP Inside Track here https://github.com/qmacro/odata-v4-and-cap/blob/main/slides.md#count-as-system-query-option). 

On your comment regarding replacing spaces with %20 in the $filter system query option, when using curl, I decided to write a quick blog post on the topic, instead of commenting on it specifically here. Perhaps that way I can reuse the content and it may help others.

👉 OData query operations and URL encoding the system query options with curl

Wow, thank you so much DJ for a new grate blog about the OData query with curl. 🙏

Reading this useful blog together with my morning coffee time is a perfect match. 😁😎

Happy coding and happy learning 🙂

Wises

0 Kudos

wow! have you written anywhere about what platform your blog is powered by, how is it developed etc?  would be interesting to know.  

abdullahgunes
Participant
0 Kudos

0c284fcfe2e160aab3a7084cf4bab24343cf666f3c8a32fc33f7b67993e389fd

0 Kudos

db3e09b79c7f870957d454735d7ca98c7049984c965dfb14fbae4af8b381b486

IsmailFatih
Explorer
0 Kudos

3df5db188ab386823c877f7264c557c0010df28803008090f1cd0cb8acecf8a8

ecem_yalim
Explorer
0 Kudos

89fd4ea53e35cefedb0e3411f3943d51fa0cba6fde814ac646671df9e8f50319

ADR
Participant
0 Kudos

2429123abbbe038ef51ddd91d1efdb913c900add50f106e5f9d8af6e52d0cc6a

It was great to explore and enhance my knowledge. Thanks. 

qmacro
Developer Advocate
Developer Advocate
0 Kudos

That's great to hear! But. Your lovely comment, added to your hash reply ... might have invalidated it 😉 Remember the instructions in https://groups.community.sap.com/t5/application-development/sap-developer-challenge-apis-task-0-lear... 😉

tobiasz_h
Active Participant
0 Kudos

0fd1c23dbd63b541777f4a0ca7733d285b6a927567b00d3c539ceb4194792e09

Sharadha1
Active Contributor
0 Kudos

383cdc18204202abd032d3a0ed5fe34791105ec4ad92196ccb67f03d3d6c87e7

eakucuk
Explorer
0 Kudos

c9274d67d00ba07f0bb51e0f5c17b7b9105b1884b12b4529f3579f423d190c7e

I used SAP Integration Suite for this challenge. Firstly I made an HTTP call and get the data in JSON. After converting JSON data to XML (Integration Suite Filter mechanism accepts only XML data), I filtered the Discontinued field. And finally I used groovy script to parse the filtered XML data and got the sum amount of UnitsInStock fields.

Regards 🙂

eakucuk_0-1691498048885.png

 

qmacro
Developer Advocate
Developer Advocate

Wow, awesome. Nice work, and thanks for sharing! 

0 Kudos

You are welcome and thank you.

Cylia_Oulebsir
Participant
0 Kudos

48e4377406e13fcf6ffac19c60853828cf188aaab9ee6d08166fea71c93fc51d

Tomas_Buryanek
Active Contributor
0 Kudos

dada4b97b145423aebe0b66c4557fa6f46271fce3bde3d74e3c1b493950144a9