Important update!
As of SAP Business One 10.0 FP 2011 you can directly create SQL queries via Service Layer and run them directly without requiring the creation of a view previously. Please check the document
Working with SAP Business One Service Layer "4 SQL Query" chapter for full details and
this blog for an overview!
Do you need to run queries in order to get data on your SAP Business One company database other (or in other format) than the predefined Service Layer entities and services?
As of SAP Business One 9.3 PL02, version for SAP HANA, Service Layer supports automatic discovery and execution of the SAP Business One Semantic Layer views both system built-in
views and also custom views.
Semantic Layer works as an OData web service and exposes the views to clients via the OData protocol version 4.
If you want to execute a specific view like for example "AveragePurchasingPriceQuery" you simply send the following request to Service Layer:
GET https://hanaserver:50000/b1s/v1/sml.svc/AveragePurchasingPriceQuery
and you will get the following results:
{
"@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#AveragePurchasingPriceQuery",
"value": [
{
...
"PurchaseAmountLC": 5000,
"PurchaseQuantityInInventoryUoM": 100,
"AverageUnitPriceLC": 50,
"id__": 1
},
{
...
"PurchaseAmountLC": 2000,
"PurchaseQuantityInInventoryUoM": 10,
"AverageUnitPriceLC": 200,
"id__": 2
},
...
]
}
Semantic Layer service allows you to retrieve data with query option combinations:
GET
https://hanaserver:50000/b1s/v1/sml.svc/AveragePurchasingPriceQuery?$select=PostingYear,BusinessPart...
kip=1&$filter=PostingYear eq '2017' and startswith(BusinessPartnerCode,
'1')&$orderby=PostingYear
to get a reduced number of records and properties per record:
{
"@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#AveragePurchasingPriceQuery",
"value": [
{
"PostingYear": "2017",
"BusinessPartnerCode": "1071287676"
},
{
"PostingYear": "2017",
"BusinessPartnerCode": "1100270398"
},
{
"PostingYear": "2017",
"BusinessPartnerCode": "124052273"
},
{
"PostingYear": "2017",
"BusinessPartnerCode": "1785286082"
}
]
}
You can also fill your view input parameters in your request to Service Layer.
For example to run the view "BalanceSheetQuery" equivalent to the following query:
SELECT * FROM
"_SYS_BIC"."sap.sbodemous.fin.fi/BalanceSheetQuery"('PLACEHOLDER'=('$$P_AddVoucher$$',
'N'),'PLACEHOLDER'=('$$P_FinancialPeriod$$','2017'))"
you send the following request:
GET
https://hanaserver:50000/b1s/v1/sml.svc/BalanceSheetQueryParameters(P_FinancialPeriod='2017',P_AddVoucher='N')
/BalanceSheetQuery?$select=FiscalYear, AccountCode
and get the following result
{
"@odata.context": "https://hanaserver:50000/b1s/v1/sml.svc/$metadata#BalanceSheetQuery",
"value": [
{
"FiscalYear": 2017,
"AccountCode": "_SYS00000000049"
},
{
"FiscalYear": 2017,
"AccountCode": "_SYS00000000009"
},
...
{
"FiscalYear": 2017,
"AccountCode": "_SYS00000000029"
}
],
"@odata.nextLink": "BalanceSheetQueryParameters(P_FinancialPeriod='2017',P_AddVoucher='N')/BalanceSheetQuery?$select=FiscalYear,%20AccountCode&$skip=20"
}
The root URL for this service is /b1s/v1/sml.svc, to distinguish Semantic Layer service from Service Layer business entities and services.
You can get the Semantic Layer service metadata with the following query:
GET https://hanaserver:50000/b1s/v1/sml.svc/$metadata
that will return the list of views available to be executed from Service Layer:
<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:DataServices>
<Schema Namespace="SAPB1" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityType Name="AveragePurchasingPriceQuery">
<Key> <PropertyRef Name="id__"/> </Key>
<Property MaxLength="160" Name="LineDocumentOwner" Nullable="true" Type="Edm.String"/>
<Property MaxLength="15" Name="PaymentMethodCode" Nullable="true" Type="Edm.String"/>
<Property Name="PostingDateSQL" Nullable="true" Type="Edm.DateTime"/>
...
<Property Name="id__" Nullable="false" Type="Edm.Int32"/>
</EntityType>
<EntityType Name="OnTimeReceiptStatisticsQuery">
<Key> <PropertyRef Name="id__"/> </Key>
<Property MaxLength="160" Name="DocumentOwner" Nullable="true" Type="Edm.String"/>
<Property Name="NumberOfPurchaseOrder" Nullable="true" Type="Edm.Int32"/>
<Property Name="id__" Nullable="false" Type="Edm.Int32"/>
</EntityType>
<EntityContainer Name="SemanticLayer">
<EntitySet EntityType="SAPB1.PurchaseOrderFulfillmentCycleTimeQuery" Name="PurchaseOrderFulfillmentCycleTimeQuery"/>
...
<EntitySet EntityType="SAPB1.BalanceSheetComparisonQueryParameter" Name="BalanceSheetComparisonQueryParameters">
<NavigationPropertyBinding Path="BalanceSheetComparisonQuery" Target="BalanceSheetComparisonQuery"/>
</EntitySet>
...
<EntitySet EntityType="SAPB1.KPICashFlowStatementQueryParameter" Name="KPICashFlowStatementQueryParameters">
<NavigationPropertyBinding Path="KPICashFlowStatementQuery" Target="KPICashFlowStatementQuery"/>
</EntitySet>
</EntityContainer>
</Schema>
</edmx:DataServices>
</edmx:Edmx>
If you want to learn more details about this feature: how to make available your custom views for Semantic Layer, how to authorize B1 users to run Semantic Layer views,...) please check the document "Working with SAP Business One Service Layer" available in your SAP Business One installation at the link
https://hana-server:50000/. You can also download this document from the SAP Note
2538519 - Service Layer Exposes Semantic Layer as an OData Service (S-user required).
Please also read this blog
How to export and deploy SAP HANA model for SAP Business One to get more details on the process to get the views available from Service Layer Semantic Layer feature.
Don't hesitate to share your feedback with us in this blog!
If you have questions/problems while using this feature please don't post them here but in the SAP Community forums to allow an easier follow up.
Hope you enjoy this feature!
