Universe sources and joins problem

building semantic layer
1

I have five sources oracle, access and three excel files , All I need to create a universe to bring all the sources in one common place to create reports in webi and deski. my questions are…

  1. Is it possiable to create a universe by combining all the five sources.
    if yes then how, can i get the steps please.
  2. the third source excel does not have any common object is it possible to join the table with other table from another source forcefully if yes how can i do it.
  3. Can we make the excel as dynamic instead of static?
  4. I have a thought that can we create a script in sql to create a temp table to update dynamically from excel sheet from time to time and use that table in universe is that possible.

thanks in advance

bobtom (BOB member since 2008-01-17)

2

  1. No. :nonod: A universe can point to a single connection and not to several connections. You can create multiple universes and create multiple data providers at the report level and link/merge them using common dimensions.

  2. You should definitely have a common object for linking.

3 and 4. You can include Excel as personal data provider or create an Excel based universe and use it in the report. Search BOB for more details about it. It has been discussed many times. One such is this.

Jansi :india: (BOB member since 2008-05-12)

3

Thanks Jansi, is it possible to link all the universe if yes will there be any performance issues out of it. is it good practice.

bobtom (BOB member since 2008-01-17)

4

This topic can answer your question.

Jansi :india: (BOB member since 2008-05-12)

5

I more quick question I have multiple tabs in the excel but when i tried to create a universe iam unable to fetch data from all the tabs.

bobtom (BOB member since 2008-01-17)

6

That’s how it behaves. :frowning:

Jansi :india: (BOB member since 2008-05-12)

7

so there is no work around to get data from all tabs

bobtom (BOB member since 2008-01-17)

8

I’m sorry. Are you trying using a personal data provider? In that case, you can select only one worksheet from a workbook.

Or are you speaking about building a universe from Excel? If yes, then please go through this. MichaelWelter speaks about it. I’ve never created a universe out of Excel.

Jansi :india: (BOB member since 2008-05-12)

9

I am trying to create a Universe using the Excel

bobtom (BOB member since 2008-01-17)

10

Okay, then read Michael’s first post in the suggested link. He mentioned something about each tab behaving as a separate table.

Jansi :india: (BOB member since 2008-05-12)

11

Ya I have read that part , but in my case we get the excel extracts since as excel had data storage limitations we get data in multiple worksheets in excel file and I need to create a universe and make it a single table of it so that I can fetch all the data from all the tabs/worksheets.

bobtom (BOB member since 2008-01-17)

12

A universe is no ETL tool. Use the right tool for the job.
Load the MS Excel sheets into a database using an ETL tool or a load script (in Oracle for example). Then create your universe based on this database.

Andreas :de: (BOB member since 2002-06-20)

13

Thank You Andreas and Jansi , I will let my ETL Team do so the same.

bobtom (BOB member since 2008-01-17)