Excel data source for webi

[former_member203111]

Hellow Experts

I am trying to load/import excel data into bobj, I am wondering which appraoch is the best and what are the pros and cons of these approaches.

We just deployed bobj 4.1 sp01

Approach 1: Build universe based on excel datasource and then build webi query

Approach 2: Import excel file directly into webi

I would appreciate if you advise the following.

1. What is the refresh machnaism in both these approached

2. Can I refresh using BI launch pad in 4.1 version or what is another option

3. Is there any other machanism to refresh these kind of report which is based on excel in 4.1 version

4. What is the best suitable file format for this kind of data load into bobj, I have following file format, please see attachment cell hielighted in yellows are dimension and green are measures) can you please let me kow if this format is correct.

Regards

[Former Member · Accepted solution]

Hello Imran,

Your exel format is right and you need to repeat the "Walmart" for  Salt and Oil also..

Approach 1 Benefit: Build one universe use it in any no of reports.

                             You can create formula's in universe as well which improves query performance.

                             ...   

Approach 2 Benefit: For each report you have to start from beginning because you have to change the object type each time when you are using the excel source.More ever you have to  create variables in report it self...

Conclusion: If your excel is having multiple sheets use approach 2

                   If your excel is having single sheet then use approach and you have 2 create multiple reports then use approach 1.

Finally it's your choice to choose the best one which suits your requirement with less developing time.

Note:In Both approaches you need to close the report and open it to make changes done in excel(structural changes only ). for delta rows you don't need to do anything.. except saving the excel sheet.

let us now if anything is required.

Regards,

Subbarao M

[former_member203111 · Accepted solution]

Hi Subbarao

Thanks for your detail reply, it is very helpful.

Can you take a look at my excel format, I attached it again as you described in your reply.

One more question though:

Do I always need to save the excel file in a centeralized location such as in application server or share point and then keep on updating the file every month as it is updated with delta records (NOT structure as I understand that changing structure will make me change the universe structure as well.

I would rather go for excel to webi import and update the file as this assignment is only pertaining to one department within the organization.

Regards

[former_member191664 · Accepted solution]

Hi,

You might want to check out New in SAP BI 4.1 SP02: Microsoft Excel as data source for Web Intelligence on http://scn.sap.com/docs/DOC-49324 for the centralized location for excel file detail.

Also, you can turn on the refresh on open option in Webi report so that the report will always fetch the excel file when it is viewed.

Hope this helps,

Jin-Chong

[Former Member · Accepted solution]

Hello Imran,

Yes, you need to make sure that the excel file should be available for BO server.(place it in a shared folder, so that respected department people will access it and add the delta records..)

Regards,

Subbarao M

[Former Member · Accepted solution]

Hello,

perhaps this link helps:

Regards

Andreas

[Former Member · Accepted solution]

Hi Subba Rao, hope this is not a very old post I have a scenario as below

We receive a report file in email and a macro is run to format (excel attached) and we have created an initial webi report (attached).  the combined webi works as a planning report

1. how to combine both (can you explain the procedure) in the webi without universe (directly in webi) and if there is no such option please mentioned the steps in universe.

2. also the macro seems to be erroneous, can you tell me if there is a way to use raw excel and combine it with my webi

3. even after the macro runs there are still BLANK ROWS as you see in the attachment, how can I get rid of them and use it

4. most columns are the same in both except a couple, can you tell me how I can address this

5. How do i refresh the incremental data in the webi report as soon as excel file changes with new data. (in my case a macro runs on excel we get everyday)

System: backend: BW and BI 4.1 sp4

Your help would be greatly appreciated

Thanks, Krishna

[Former Member]

This message was moderated.

[Former Member]

Hi Imran,

Approach 1 is a good one. Place the excel file in NAS location, where the BOE SIA can access. Create DSN on the server, so that you can create a connection to excel data source.

Once it is done, you can develop the universe and reports. Please let me know if you have any queries.

Regards,

Siva.

[former_member203111]

Hi Siva,

Thanks for your reply.

As if you read my question, I was concerned on how to refresh the delta or incremental data in that webi report, In other words I need to know the best machnaism to refresh webi with updated data as soon as excel file changes with new data.

Please note I am using 4.1 sp01 version of BOBJ

Regards

[Former Member]

Hi Imran,

Even if you are updating your excel file frequently...this approach works fine. All you need to do is update the excel and run the report.

If you are adding any new columns to the excel, you have to update your universe as well.

Regards,

Siva

[KodandaPani_KV]

Hi imran,

Approach 1: Build universe based on excel datasource and then build webi query
Approach 2: Import excel file directly into webi

Approcah1 : it is good approach.

which one your using data base like SAP BI, MY SQL, MS- SQL ?

get the data form behind data base then design the model at using IDT or UDT what ever you required

like desing the table, hier, creating relation connections,

then export to same universe to webi develop the webi reports.

Approach 2 : it is testing for user ok,

nothing but mack up reports.

create the test data in excel then export webi then develop the webi reprots.

hope it will help.

Thanks,

Phani.

[former_member203111]

Hi Kodanda,

As I mentioned, it is direct excel load so there is no database involved in this excersise.

Also can you let me know for Approach 2, can we refresh the report automatically as soon as the data changes in the excel report, if not, Is it even possible to refresh the webi report manually once we have the updated excel file with latest data next month.

Regards

[former_member195290]

Hi Imran,

Check the link  http://blog.davidg.com.au/2011/10/excel-data-sources-for-webi-and.html

will surely help you on your topic.

Regards,

JC