8 Replies Latest reply: Feb 4, 2011 4:03 PM by Erika Atencio RSS

How to create a Universe with excel?

Erika Atencio
Currently Being Moderated

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

  • Re: How to create a Universe with excel?
    Efstratios Karaivazoglou
    Currently Being Moderated

    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.




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





  • Re: How to create a Universe with excel?
    Swarna K
    Currently Being Moderated

    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.

  • Re: How to create a Universe with excel?
    Mark Prosser
    Currently Being Moderated

    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.

  • Re: How to create a Universe with excel?
    Praveen Vodela
    Currently Being Moderated

    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


    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


    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.


  • Re: How to create a Universe with excel?
    ashok ashok
    Currently Being Moderated



               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 .