Advice in how to use Universes

building semantic layer
1

I currently have 2 identical universes. One has my current production data, the other has archived data which comes directly from my production database. Currently, i have to update each universe any time I make a change, which I would like to find a solution to and when I need to create a report that has Year to Date info I have to query both universes and then merge the data in the report.

Is there anyway to set up the universes to avoid having to query both when creating these YTD type reports? Ideally I would like to have my Objects pull from both databases.

Any ideas or suggestions would be greatly appreciated.

Thanks!

d1cjm1 (BOB member since 2003-02-24)

2

One option

Can you create view for each table defined along the lines of (pseudocode):

TableA from DB A1 UNION ALL TableA from DB A2

And then build your universe against these views?

Prerequisite:
DB A1 can “talk/connect” to DB A2 (in Oracle DB link for example)

Possible drawbacks:
Poor performance againt the views and the DB link

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

3

What is the DB, and are they on the same server?

Example, SQL server, 2 databases on the same server.
One connection to the server gives you access to all the tables in both DB’s, meaning you can add them to the same Universe.

Scott Bowers :us: (BOB member since 2002-09-30)

4

Both databases are on same server and Oracle. The schemas are identical.

d1cjm1 (BOB member since 2003-02-24)

5

I am new to oracle, but I believe it would be the same as SQL server where as you can add the tables from 2 databases as long as they reside on the same server.

Scott Bowers :us: (BOB member since 2002-09-30)

6

I guess I dont understand how I can add the same tables. Essentially what I have is a table called “TABLE1” in the first db and “TABLE1” in the second. They both have the same fields. I would like BO to be able to see them as one. So when i pull up OBJECT1 for a report it pulls data from both databases.

Thanks.

d1cjm1 (BOB member since 2003-02-24)

7

Have you considered schema prompting? I had 7 identical schemas so I placed a prompt in the ‘Owner’ portion of the table name dialog box. When a user selects an object and hits ‘Run Query’, the schema prompt asks the user which schema to use…in a user friendly way of course. Check out this thread if you’re interested…

Cindy Clayton :us: (BOB member since 2002-06-11)

8

You should be able to add tables with same names to the universe as long as you have qualified the structure with Owner in Oracle and Owner, Qualifier in SQL Server (Select all then Edit->Rename).

The UNION approach might be the best as it allows you to use these tables in the YTD reports and omit them from current data reports. As you are on Oracle, the performance should be dictated only by the type of query and the amount of data being brought back as there is no network overhead to the queries.

avaksi :us: (BOB member since 2002-08-22)

9

Thanks for all the help. Based on what I am looking to do it sounds like the UNION approach is best. I shall give it a whirl.

d1cjm1 (BOB member since 2003-02-24)

10

This strike me as an issue better addressed at the DB level, using a view that combines the two tables.

If that’s not feasible, doing a union will work for Reporter but not WebI

Tom Thompson :us: (BOB member since 2003-06-04)