How to create a Universe with excel?

[Former Member]

I want to create a universe using excel, but I don't know if it is possible.

If it is possible should I save the excel file in an specific folder? where?

Is it possible to create WebI reports using this universe?

Edited by: PadawanGirl on Jan 31, 2011 7:53 PM

[Former Member · Accepted solution]

Hi PadawanGirl,

It cannot be that difficult to create a new universe based on Excel spreadsheet. You can log on to the Designer, do a "File-->New", and from the dialog boxes, you should be able to find "Excel Spreadsheet" under "Microsoft". Just follow the dialog boxes and you should be able to create the universe.

If you have WebI, you should be able to run against that universe with no problem. Just make sure your Excel file and the DSN are on the BusinessObjects server.

After you have created the DSN on the BusinessObjects server, go back to Universe Designer, when you select the new connection, you should see your new DSN name from the drop-down list. After you added the table to the universe and created the new objects, you should always go to the Properties tab of the new dimension object, click on the "Display..." button to view the data. This will give you some idea if your universe is actually pulling data or not.

Also, if you are including the headers on the Excel file, it is better to name the headers with no space. And don't forget to name your Excel tab instead of leaving it with the default name of "Report 1". This makes it so much cleaner.

The "properties" tab that I was referring to belongs to the dimension object in the universe. I thought you have already added the table and created the objects. Let me make sure I understand you correctly, have you completed successfully the following steps?

1. Prep the Excel file... i.e. remove spaces in headers (these headers can become your object names in the universe), name the tab instead of the default name of "Report 1", store the file in the BusinessObjects server.

2. Create a system DSN on the BusinessObjects server using the Microsoft Excel Driver to point to the Excel file in the same server. Test the connection to make sure it is good.

3. Open Universe Designer to create a new universe. Click "New" to create a new connection and select the DSN from the drop-down list. Test the connection to make sure it is good with no error.

4. Add table to the universe by selecting the Excel file from the list (there should be just one on the list).

5. Drag the table to the left pane to create the objects.

6. Click on each object to open up a dialog box. On the Properties tab, click on Display to make sure you have data.

[Former Member · Accepted solution]

I don't have Microsoft Excel Driver installed on my server.

Where can I get this driver? With MS Office installation?

[Former Member]

Hai

I will tell the steps .

store the Excel file in a Directory or in Local Machine.

.Go to Web-Report :

Click on new , Create a new Report .

Select the Data dource : From Excel file,

An pop-window will appear, Give the path of the Excel file where it has been stored.

Click on next,next. the New webr-report is created .

run the Webi-report .

[Former Member]

Hi Padawan,

Creating a Business Objects Universe from an Excel File

To create an Example Universe, we will first Extract data in a BusinessObjects report and

save it to Excel. Letu2019s create a new Document from the Island Resort Marketing

universe, and include All of the Resort attributes and Revenue.

Generate a New Report from a Universe

Generate a Standard Report

Creating a Business Objects Universe from an Excel File

Choose Island Resorts Marketing.

Choose all of the Resort class, and the <Revenue> Measure object.

Creating a Business Objects Universe from an Excel File

Depending on your preference here, you can either choose Edit > Copy All or you can

edit the Data Provider to export the results of this query to Excel.

I like to use the Data Provider method, since this allows me to specify a path, etc. If you

have used copy/paste, you will have one extra step that will be described, but not

demonstrated.

Export to Local File Format and make sure that the Format is listed as: Microsoft

Excel 97 Files (*.xls). You may even want to Change the Path, or Rename the file at the

Name line, or Browseu2026 to a new path.

Creating a Business Objects Universe from an Excel File

Once the Export has completed, launch Excel and browse open the file that was just

created. If you have Copied/Pasted the values, then you should already be at the next

step.

If you havenu2019t done this already, browse over the Resorts.xls file and open it. If you

have used Copy/Paste, just bring Excel to the foreground.

Creating a Business Objects Universe from an Excel File

Select all of the data. If you Copy/Pasted, make sure that you delete the Header that

copied as well. With all of the data selected, make sure the range has a name. You will

need to enter a name if youu2019ve pasted this, otherwise it should match the Filename minus

the .xls. Save this if youu2019ve made any changes, or created this from scratch.

Jump to the BusinessObjects Designer module and create a New Universe. Iu2019ve named

my Universe: Resort Revenue

Creating a Business Objects Universe from an Excel File

Create a new Connection, and choose ODBC Drivers.

Iu2019ve named my connection: Resorts_xls and chosen Generic ODBC Datasource for my

Database engine. Click on the ODBC Admin button.

Add a new System or User DSN. If you want this available for anyone on your machine,

System DSN is the better bet. Select the appropriate tab and hit Addu2026

Creating a Business Objects Universe from an Excel File

Choose the Microsoft Excel Driver (*.xls) from the list of available data source drivers.

Name the Data Source Name something that is meaningful, as it will be what appears in

the Drop-Down menu. I named it Resorts.xls File and next weu2019ll choose Select

Workbooku2026 and browse to the location of our Resorts.xls file.

Unless youu2019ve changed the location, it should be located under:

C:/Program Files/Business Objects/Business Objects 5.0/UserDocs/

Select the XLS file and click u201COKu201D

Creating a Business Objects Universe from an Excel File

Verify that the path is set for the Workbook and click u201COKu201D

You should see the new DSN created with the name of your Choosing. (In my case, itu2019s

Resorts.xls File.) Go ahead and click u201COKu201D

Back in the Designer Module, Choose the Resorts.xls File from the Data Source Name:

drop-down list, and click u201COKu201D

Creating a Business Objects Universe from an Excel File

Once the connection has been set, click u201COKu201D and weu2019ll get to Universe Building.

Double-Click in the Schema area in the designer, or use the Table Browser to add the

Resorts table (named-range). Once they are added, feel free to move them over to create

Classes and Objects off this table as well.

Creating a Business Objects Universe from an Excel File

Iu2019ve modified the SQL of the Revenue object, to turn this into an aggregate. Set the

formula to: Sum(Resorts.Revenue) and click OK.

My Final Universe looks like the above. Once this Universe is created, Save it and

launch the Business Objects Reporter module.

Creating a Business Objects Universe from an Excel File

Select the newly created Resort Revenue universe and choose the objects youu2019d like to

report on.

Iu2019ve selected the <Country>, <Resort> and <Revenue> objects.

Here are my final resultsu2026

All the best.

Praveen

[Former Member]

It is possible, but I would strongly recommend importing the Excel file into a database instead. You need to consider that an Excel spreadsheet is a single user data source and won't be as accessible as a database with an OLEDB connection, together with its inherent security features.

[former_member142702]

You have to create an ODBC DSN that uses your excel file on your BOBJ server. The file should be located in a folder accessible for the user that runs the BO services.

If you have BO XI 3.1 SP3 then you can use Excel files as data sources directly (no need for a universe) using the WebI rich client. The documentation of the WebI rich client describes where the Excel file should reside in order to be able to refresh your report againt this data source even if the report runs in the InfoView.

http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_webi_rich_en.pdf

Check the Section "Refresh option in Infoview" in page 33.

Regards,

Stratos

[Former Member]

sorry I can't find where it says I have to save my Excel file so I can use it from infoView.

Edited by: PadawanGirl on Jan 31, 2011 10:10 PM

[former_member142702]

Quoting from page 33

The default server path is <Installdir>Business Objects\PersonalDPFilessymantec, you can change this path by
changing the value of Server_Path in the registry.

Regards,

Stratos