OracleBIBlog Search

Wednesday, August 5, 2009

OBI Reports from SAP BW Cube

OBIEE has the capability to build repository by importing a MOLAP Cubes. Let's see the steps to build OBI Repository and develop Reports from a SAPBW cube.

1. Open an instance of the OBI Adinistration tool
2. Click File--> New and give a name like SAPBW to the repository
3. Click File--> Import from Multi-Dimensional
4. In the Import from Multi-Dimension dialog box provide the parameters for connection to the XMLA Data structure



















5. This is the imported SAP Cube structure



















6. Create a new Logical Model with necessary logical joins as per requirements














7. Create the Presentation Layer as per requirements
















8. Make the necessary updates to the configuration files and starting the OBI services

9. Logon to OBI Presentation services and create a sample report to validate the data

30 comments:

OBIEEguy said...

Does this only work with SAP or can other multi-dimensional data sources be imported?

Ramana Chittoor said...

The steps that I have indicated are for SAPBW as a source but OBIEE 10.1.3.4.x allows to import data from various MOLAP data sources like ESSBASE, Microsoft Analysis Services. There are few pre-requisites that needs to be configured for these sources other than passing the required connection parameters

OBIEEguy said...

What would be the pre-requisites for ESSBASE?

Ramana Chittoor said...

Oracle BI Server connectivity to Essbase is through the Essbase client libraries. The client libraries must be installed on the Oracle BI Serverachine which are part of an Essbase client installation on a machine where the OBI server is installed.

Check for the environment variables PATH, ARBORPATH, and HYPERION_HOME are set correctly

Swatiii said...

Hi,

We have followed the same steps for importing data from SAP BW to OBIEE. But we get the following error when we select some combination and click on results:

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. Expected an attribute value (HY000)
SQL Issued: SELECT "Business Domain"."Business Domain Level 01" saw_0, "Process Group Code"."Process Group Code Level 01" saw_1, "Project Code"."Project Code Level 01" saw_2, KeyFigures."Review Effort" saw_3, KeyFigures."Rework Effort" saw_4 FROM QM ORDER BY saw_0, saw_1, saw_2

We are unable to figure out why we get this error. Kindly provide your inputs.

Ramana Chittoor said...

I believe like this is got to do with the selected column and underlying logical model. Are these columns selected from a single star. Let me know the star details beacuse as per the error it indicates a missing attribute but I see that there are couple of attrib columns selected.

Swatiii said...

We have not done any changes with respect to the joins. We have just dragged and dropped the Cube from Physical layer to Business and Presentation layer.

Could this error be related to the volume of data? We have huge data in the cube.

Ramana Chittoor said...

This is a generic error and is not due to data size. Did you test this effort as an Administrator, Can you increase the log level to 7 and send me the logical query from Manage Sessions. Also can you check on the complex joins within the BMM layer for the star.
Try with few columns like 1-2 attribs and 1 measure and let me know if there are results

Swatiii said...

We are testing this with Administrator only. There are no complex joins.Following is the log with Vendor as attribute and two Measures.
-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report';SELECT Vendor."Vendor Level 01" saw_0, Metrics."Invoice amount: Returns" saw_1, Metrics."Net purchase order value" saw_2 FROM MM_PUR_C01 ORDER BY saw_0
-------------------- Logical Request (before navigation):
RqList
Vendor.Vendor Level 01 as c1 GB,
Invoice amount: Returns:[DAggr(Metrics.Invoice amount: Returns by [ Vendor.Vendor Level 00, Vendor.Vendor Level 01] )] as c2 GB,
Net purchase order value:[DAggr(Metrics.Net purchase order value by [ Vendor.Vendor Level 00, Vendor.Vendor Level 01] )] as c3 GB,
Vendor.Vendor Level 00 as c4 GB
OrderBy: c1 asc
-------------------- Execution plan:
RqList <<33725>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<33736>> [for database 0:0,0]
(
Child Nodes (RqCache):
RqList <<33708>> [for database 0:0,0]
D1.c1 as c1 [for database 0:0,0],
D1.c2 as c2 [for database 0:0,0],
D1.c3 as c3 [for database 0:0,0],
D1.c4 as c4 [for database 0:0,0]
Child Nodes (RqJoinSpec): <<33721>> [for database 0:0,0]
(
RqList <<33667>> [for database 3023:32063:$INFOCUBE,43]
$0PUR_C01.Vendor Level 01 as c1 GB [for database 3023:32063,43],
sum($0PUR_C01.Invoice amount: Returns by [ $0PUR_C01.Vendor Level 00, $0PUR_C01.Vendor Level 01] ) as c2 GB [for database 3023:32063,43],
sum($0PUR_C01.Net purchase order value by [ $0PUR_C01.Vendor Level 00, $0PUR_C01.Vendor Level 01] ) as c3 GB [for database 3023:32063,43],
$0PUR_C01.Vendor Level 00 as c4 GB [for database 3023:32063,43]
Child Nodes (RqJoinSpec): <<33689>> [for database 3023:32063:$INFOCUBE,43]
$0PUR_C01 T32096
GroupBy: [ $0PUR_C01.Vendor Level 00, $0PUR_C01.Vendor Level 01] [for database 3023:32063,43]
) as D1
OrderBy: c1 asc [for database 0:0,0]
) as D1
OrderBy: c1 asc [for database 0:0,0]
-------------------- Sending query to database named $INFOCUBE (id: <<33667>>):
With
set [0VENDOR1] as '{[0VENDOR].[LEVEL00].members}'
set [0VENDOR2] as ' hierarchize ({ [0VENDOR1], Generate({[0VENDOR1]},Descendants([0VENDOR].currentmember,[0VENDOR].[LEVEL01],SELF), ALL) } )'
set [Axis1Set] as '{[0VENDOR2]}'
member [Measures].[MS1] as '[Measures].[0IR_VAL_RET]'
member [Measures].[MS2] as '[Measures].[0NET_PO_VAL]'

select
{[Measures].[MS1],[Measures].[MS2]} on columns,
NON EMPTY {[Axis1Set]}properties MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME on rows
from
[$0PUR_C01]

Ramana Chittoor said...

Did you try setting up a valid filter criteria to any attribute that constrains the data. Also I may have to look at the rpd.Can you mail me the rpd with password.You must have complex joins and also set aggregation to metrics.

Swatiii said...

I have set up filters for a particular Month and Material but i still get the error. We do have complex joins but no aggregates. I will not be able to share the rpd as per our policies. Hope you understand.
However i have been trying out with the joins but no luck.

Ramana Chittoor said...

I can understand and I honor any organization policy. I thought that it was a POC that you were working on.Unfortunately this is a very generic error and to nail this down you may have to run a simple request which you did already.Please try running the logical SQL directly on the database and check if it returns errors. I have asked you to set aggregation like Sum, Count.. on metrics. I hope it works. Also can you create a simple rpd with data sets of smaller cube. Build a good logical star of that and try validating with a small request filtered to bring in less number of rows of data.

Swatiii said...

Thank you so much. I am able to retrieve data from the cube. The issue was with the aggregation in the metrics.

Thank you for all the support.

Swatiii said...

How are the infoobjects declared as navigational attributes for the infocube handled in OBIEE?

Ramana Chittoor said...

Good to know that the issue was resolved by setting the aggregation for metrics. Please let me know in case you have any more questions for OBI Data Modeling.

Well regarding the infocubes I believe that this is based of the characteristic values set for hierarchy,data granularity. In OBI model this is handled by Dimensional Hierarchy modeling to address navigation of data from low level of hierarchy to top level.

Swatiii said...

In case of SAP BW each fact data (Value) is associated with a unit may be Quantity or Currency which gets reflected when we pull reports in Bex Analyzer.

In case of OBIEE how do I associate the same?
Eg. 100 INR, 2 USD. Ideally this should be shown when we use the KeyFigures in the report.

Ramana Chittoor said...

OBIEE needs repository changes to accomplish this. The BMM layer needs to be modeled to handle the values for Units, Currency Codes.
The data is through seperate dimension columns for Units and Currency. So the report will have a seperate columns for Units, Currency.

Michael R. said...

I have been able to connect to and import into OBIEE from a SSAS 2008 cube - but there are issues. The attributes are actually imported as dimensions. Is there a setting that I may have missed in OBIEE? Has anyone experienced this before?

Thank you for your time.

Michael R.

Ramana Chittoor said...

Michael,

OBIEE treats attributes as dimensions. I am not sure what is the issue that you have with it. Did you get an error while creating a request with some columns of these attribute. if not please send me a screen shot of the issue

nanii said...

Hi
Nice to meet you on web
I am working on OBIEE as source from SAP BW which is on Oracle Database
Can you please provide me some information like Notes or documentation or material on OBIEE W/ SAP BW

Thank you

Ramana Chittoor said...

Unfortunately there is no documented material from Oracle specific to OBIEE with SAPBW. The steps that I have indicated are at high level to import SAP MOLAP blocks into OBI physical layer. Well after that you need to implement the data model design based of business requirements and configure the rpd

Mariaelena said...

Do you know if this approach for connecting OBIEE to SAP BW will exploite the BW accelerator?

Ramana Chittoor said...

I have not validated this but yes if the case being the MOLAP source structure. Its all about importing the MOLAP structures into OBI physical layer

Suman.obi said...

Hi ALL,

i have posted the same ? in the same forum but none repsponded, so i taught of asking you ...Dont mind... i taught of asking in specific to U.
because that u aer working on SAP OBIEE....


I have tried to import SAP BI Cubes to OBIEE, but after entering the Details in the import Pop-up it is giving error as below.

"nQSError:46100 Sax Parser failed with error Expected whitespace."

THE DETAILED ERROR, as per my investigatation:
46100 Sax Parser failed with error @1%ls.
*Cause.* Sax xml parser encounters a problem as described in the error string.
*Response.* Please check to see if the syntax of the xml file passed in for

The login credentials given at the time of importing are.

provider type: SAP BW/BI
URL: http://domain:port/sap/bw/xml/soap/xmla
Username = about your BW user
Password = about your BW user

but when i checked, For BW system, with transaction SE37 and execute the function module RSBB_URL_PREFIX_GET.
the following values for the import parameters of the function module:

I_HANDLERCLASS = CL_RS.............................
I_PROTOCOL = (it is blank here)
I_MESSAGESERVER =

Should i change my URL mentioned, I dont think so as i followed the sytax. corret me if iam wrong...

i have enabled the SAP CUBE section in OBIEE @ nqsconfig file. and As per the Error, "nQSError:46100 Sax Parser failed with error Expected whitespace." should i change any other file on OBIEE or make any changes in SAP BI.

what is the error cause, had anyone experienced this error. let me know if i skip somethingg...

this is an Show Stopper.....


THANKS IN ADVANCE..


SUMAN

Ramana Chittoor said...

Suman,
Can you try by using differnt login credentials like a user/pw without any space in that. I dont see any issue with the URL

Suman.obi said...

Ramana,
Thanks for your promtpt reponse...
actually my question was should i make any changes in SAP or OBIEE side to get it fixed.
because as you said iam using the username/PW in SAP thats working fine. The Error i got is mentioning about some issue with some XMLA fine Error...iam not sure..
I have sent you an mail to your Gamil Account in detial... Let mekwno if you need anymore information on this...


Thanks
Suman

Suman.obi said...

Ramana,

The credentials mentioned are not the correct one. Actual measures are different and working fine with SAP BI. I have sent a mail to EmailID in detail..... are there any extra settig to be done from the SAP or OBIEE side..

Thanks
Suman

Anonymous said...

I have tried to connect SAP BW 7.0 with Patch level 18 to Oracle BIEE 10.1.3.4.0, but after entering the Details in the import Pop-up it is giving error as below.

"nQSError:46100 Sax Parser failed with error Expected whitespace."

Steps
1. Import from MultiDimentional - Provider SAP/BW 3.5/7.0,
2) URL : with port - http://ipaddress:port//sap/bw/xml/soap/xmla
and
URL without port: http://ipaddress//sap/bw/xml/soap/xmla

Userid & pwd, but I am getting the same error message.

Thanks in Advance
Murugan.V

Mais said...

Dear Ramana,

I followed the same steps, but when i open the hierarchy, i cannot see all the levels? I am using OBI EE 10.1.3.4 on top of SAP BW R3.

Any advice? I am in a POC, so your prompt response is highly appreciated.

Kind Regards,
Mais

Megh Salgia said...

Hi Swati,

I'm trying to import SAP Cubes into OBIEE. I have retrieved the url from the sap machine and after filling all the credentials in OBIEE Import from Multidimensional option, I am getting error "NQSError 46100 Sax parse failed error expected whitespace"

I have also enabled the Cube Section in NQSconfig.ini file. And I have also checked the xMLA service in SAP system. Its working fine.

Kindly help me where I am missing. Any help would be really appreciated.

Thanks