create universe from excel sheet

[Former Member]

Hello everybody,

I am trying to create a universe from an Excel Sheet.

This should be possible because when selecting "Microsoft" as a source, there is the option "Excel Spreadsheet" and under that ODBC-driver. Unfortunately I cannot find any field referencing to a source excel file name or something.

Has anyone done an Universe from an Excel sheet?

My goal would be to create an Xcelsius Dashboard with live data coming out of an Excel file.

Thanks

Victor

[Former Member]

Hey here,

To accomplish it, we have to do some other stuff.

Tools needed: MS Excel, Designer, LiveOffice & Xcelsius.

-- As stated in previous post and you know, develop WebI reports on top of Excel.

Install LiveOffice and check-- View On Demand option and Save it. (In LO Object/ Application Properties)

Install Xcelsius-- Now you can LiveOffice add-on in below spread sheet.

Import the data what you want see and use it for Dashboard.

Thank You!!

[Former Member]

Try the following steps to create an Excel universe

1. Open the Excel spreadsheet

2. Select all the data values, make sure the remaining blank cells don't get selected

3. Click on Insert>>Name>>Define a name to the table

4. Save the Excel

5. Go to Start>>Control Panel>>Administrative tools>Data Sources(ODBC)

6. Select System DSN>>Add>>Microsoft excel Driver(*.xls)

7. Give a new data source name and click on 'Select workbook' and point the saved excel sheet and click on OK

8. Create a new connection in Universe designer by using the Generic ODBC connection

9. Create a new universe and insert the table which we have defined in the Excel sheet

Note: Make sure the parameter <Parameter Name="Transactional Available">No</Parameter> is set to Yes under the Generic ODBC Data Source in below location

...\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc\odbc.sbo

[Former Member]

Hello Manoj and all,

I have a rek to build a universe using excel. So i created a DSN and used that to build the universe. I am able to use this only from my machine. I can generate the reports using Webi rich client and refresh but when i export the file to BOE and try to refresh i get the error

"Unable to bind to Configuration objects". Also if another user try to access this universe to generate report they get this error. Looks like a database connection issue but i am able to find the solution to it.

We have XIR3 and the excel that i am using is in the local machine. Earlier in this post i saw that

"create a system DSN at the server end and have to place your excel at the server". but can someone pls help me how to do this. Will really appreciate help on this as the project is in crunch period.

[Former Member]

Hey here,

*How to create System DSN??*

Steps:

1.Go to Run: ODBCAD32

2. Select System DSN

3. Add

4. Select Microsoft Excel Driver(*.xls)

5. Finish

6. Give Data Source Name

7. Description

8. From below "Select Workbook"

9. Choose the Excel

10. Ok -- Ok --Ok

Now how i can point the Excel DSN??

1. Login to Universe Designer

2. Click on Connection -- New

3. Point Datasource -- Microsoft -- Excel (ODBC Drivers)

4. Next screen-- Select the DSN, what you created.

5. Next -- Select the Sheet(s)

6. Next

7. Finish

Your Universe is ready now.

Note: One has to do System DSN on Server machine, where BO Enterprise version is installed. Otherwise from Client one can't get a chance to use them.

Hope It would help you a lot.

Thank You!!

[Former Member]

Hello BOCP-BOE can you pls give more information about this "One has to do System DSN on Server machine, where BO Enterprise version is installed. Otherwise from Client one can't get a chance to use them"

What do you mean when you say do System DSN on Server machine? I have BO enterpise installed on my laptop and i have built a universe using excel but it is not working the way required.

I have created a universe using excel, this excel is in my laptop only i can use this and run reports. None of the users seems to be able to run reports against this reports. Infact when i export the report ran using this universe(built using excel) to infoview, it wont refresh giving me an error "Unable to bind to configuration objects"

Any help will be very much appreciated as this is off high priority and i am going no were to resolve this issue.

[Former Member]

Hey here,

If you are creating DSN on your local machine/PC means it is only available to you only. No other Users can access it. Hope am clear in this point. This is called User DSN.

How I can make it available to all Users??

Sol:

==>> Create System DSN on server machine, means where BO is installed (Enetrprise version)

=> In Server machine only, you have to place the Excel sheet also.

==>> Now Design Universe on top of it.

All Users can play with that now.

Enjoy

Thank You!!

[Former Member]

Hello BOCP-BOE

thank you for you reply. I will follow what you have mentioned about creating a DSN and storing the excel file on the computer on which BO Enterprise is installed.

I am a newbie in this so may ask some funny question but i need to ask to make me clarify. So will a client have a computer on which they install the BO enterprise and then all the consultants/employees will get only the client version installed on their laptop. Am i right till now? and i need to find that machine on which the BO Enterprise is installed, save the excel file and create a DSN on that machine.

[Former Member]

Hi,

To use Excel as a data source for universe then you have to create a Required DSN connection.

For that go to Control Panel -> Performance and management -> Administrative tools -> ODBC Data source Administrator -> System DSN -> Add -> Microsoft Excel Driver(.xls) then specify the data source name and browse the excel sheet on clicking Workbook

The new DSN connection will be reflected in universe connections so in universe designer under available connections you should be able to see the created DSN name. Select that DSN connection to required universe and import all data exists in it.

Cheers,

Suresh Aluri.

[Former Member]

Hi,

For creating an universe from the excel sheet first of all you need to create a system DSN at the server end and have to place your excel at the server.

Go to ODBC Data source Administrator -> System DSN -> Add -> Microsoft Excel Driver.

then specify the data source name and browse the excel sheet on clicking Workbook (not remember exactly).

Now go to universe designer in the available connections you should be able to see the created DSN name. now you can import tables and go-ahead with the universe creation.

Regards,

Rohit