SAP B1 8.81 PL 08 with BI OnDemand Integration

Introduction

With the patch 08 of SAP B1 8.81, there is a small integration to bi.ondemand.com. Before this integration, users had to upload Excel files manually to the website. This small blog entry shows how the integration works, prerequisite is an account on bi.ondemand.com and the latest SAP patch.

Create query

I saved the following example query in the query manager and named it Revenue. The query is just a small example, considering invoices and credit memos.

/*
Small example Query with a measure (line total) and
some information to group the data later on bi.ondemand.com
*/

-- Invoices
SELECT
  'Invoice' AS DocType
, T0.DocDate AS 'Date'
, CAST(YEAR(T0.DocDate) AS CHAR(4)) AS 'Year'
, MONTH(T0.DocDate) AS 'Month'
, T1.ItemCode
, T2.ItemName
, T3.ItmsGrpNam AS ItemGroup
, T0.CardCode
, T4.CardName
, T5.GroupName AS 'Card Group'
, T1.LineTotal * ((100.0-ISNULL(T0.DiscPrcnt,0))/100.0) AS LineTotal
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

UNION ALL

-- Credit Memo
SELECT
  'Credit Memo' AS DocType
, T0.DocDate AS 'Date'
, CAST(YEAR(T0.DocDate) AS CHAR(4)) AS 'Year'
, MONTH(T0.DocDate) AS 'Month'
, T1.ItemCode
, T2.ItemName
, T3.ItmsGrpNam AS ItemGroup
, T0.CardCode
, T4.CardName
, T5.GroupName AS 'Card Group'
, T1.LineTotal * ((100.0-ISNULL(T0.DiscPrcnt,0))/100.0) * -1 AS LineTotal
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
INNER JOIN OCRD T4 ON T0.CardCode = T4.CardCode
INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

Upload data to BI OnDemand

By navigating to “Administration > BI OnDemand Integration”, it is possible to define the query, which should be uploaded to your account. Then, you can select the query and press the upload button.

Hint: Before you upload the data, you should log in to the website one time.

Create some dashboards

After uploading the data, it is possible to create some dashboards directly on bi.ondemand.com and consuming those dashboards also on mobile devices like the iPhone (The app is called SBO Explorer)

Limitations

The integration is a nice gadget. But with the free OnDemand account, the user is limited by the data capacity. It is also not the best solution for bigger data sets. Another limitation is the manual upload process. To browse through bigger data sets, an OLAP cube like coresuite cube would fit better.