SAP BOBJ Usage and Delivery of Excel Data File in INBOX using SSIS
In this blog, I am going to discuss about different types of Business Objects and its usage among different roles in an organisation. I will also discuss what Business Object needs what type of data hierarchy. Further this blog will guide about how to extract Excel format data feed using SSIS tool for different Business Objects.
As we know, SAP Business Objects (a.k.a. BO, BOBJ) is a French enterprise software company, specializing in business intelligence (BI). Since 2007, it has been a part of SAP AG. Its flagship product is BusinessObjects XI, with components that provide performance management, planning, reporting, query and analysis and enterprise information management.
The above snippet describes us that different user roles needs different types of tools to interpret the data in an organisation. For Example, Business Analysts require tool like BEx (Business Explorer) for their Analysis. BEx is a set of tools used for analyzing data in SAP. It has (1) BEx Query designer which is being used to design queries for user. (2) BEx Analyzer is a Microsoft Excel add-on tool for analysis. (3) BEx Web Application Designer (WAD) — A desktop application used for creating Web and analytic applications that render SAP NetWeaver BI information using tables, charts, and graphs. (4) BEx Report Designer — A desktop application used to create static or dynamic formatted reports. The reports can be viewed within a Web browser or generated as a PDF.
As we moved up towards higher management, relevant tools also require highly summarized information to display results. e.g. SAP BI-ONDemand explorer tool (http://www.biondemand.com/businessintelligence) require low level of summarized information. When we put data-set in this tool and click explore, it automatically analyse data based on different data fields given. We can then filter information based on our need. Data-set example is attached as an image of Excel Sheet (BiOnDemandDataSet.jpg).
Discussing about XCelcius which is mainly used for Dashboard design, it requires high level of pivot information to present on a Graph or Dashboard. Pivot example is attached as an image of Excel Sheet (PivotDataSet.jpg).
Additional focus of this blog is to feed different Business Objects with their required information. Below SSIS image and SQL Query will give an idea who don’t know how to extract data directly from SAP database without doing any intervention in SAP application, and this is also theme of Business Objects which works independently from SAP application.
select YEARS ,
Coalesce([ACT],0) as [ACT],
Coalesce([NSW],0) as [NSW],
Coalesce([QLD],0) as [QLD],
Coalesce([SA],0) as [SA],
Coalesce([VIC],0) as [VIC],
Coalesce([WA],0) as [WA]
from (Select states,[Years],counting from B_Pivot) as SRC
PIVOT (SUM([counting]) FOR [STATES] IN ([ACT],[NSW],[QLD],[SA],[VIC],[WA])) as pvt
order by YEARS
Email Delivery with Excel Sheet as an Attachment:
declare @startdate datetime,@EndDate datetime,@MySubject nvarchar(255);
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)
SET @EndDate = GETDATE()
Set @MySubject=’Successfully Processed OEMSALES.xlsx File for Todays Date ‘ + Convert(nvarchar,@EndDate,103);
@body = ‘This file contains OEM SALES DATA From 2009 till today’,
From above discussion and developments, we can see that Business Objects (BOBJ) are independent of SAP application, and how much easy data extraction could be. Apart from using third party tool, BEx has also has query designer to build query and extract data. User just needs to know about schema/ERD (Entity Relationship Diagram) of the system. With these modern SAP tools, any user can extract data without having detail technical knowledge of queries and programming. Bi-OnDemand is free online tool to do data exploration , however XCelcuis and Crystal Reports are not free. MS Excel also provides great visual intelligence and it is one of the cheapest tool in the market.
Also I would like to thank #BCO6181 to give me opportunity to write first ever blog for SAP. You can contact me on email@example.com or tweet @mqaiser2.