Want to create a Universe with a "Variable" Fact Table

building semantic layer
1

Hello, Listers. I’m interested in hearing if anyone has solved a similar problem, or has some great ideas for how to solve this problem in BusinessObjects 4.1.

We have the situation of having many universes with identical classes and objects, except that the fact table in the middle of a star schema is a different name. The fact table is a huge (about three-megabyte rows) table of financial transactions for a given fiscal year. The only difference between universes is that the name of the table consists of the name of a different fiscal year.

Think of the ten (or more) fact tables as a “single” table – that has been partitioned into ten tables to make accesses more efficient. So table X_1990 has the same columns as table X_1999, but one is for fiscal year 1990, and the other is for fiscal year 1999.

Essentially, we’d like to be able to maintain a single universe, and to dynamically choose which fact table to “substitute” into the universe.

Have any of you figured out a way to do something like this? Maybe prompt the user for a fiscal year when the universe is opened, and allow the central fact table’s name to be dynamically assigned?

Or do any of you have any clever way of handling such a situation? (Other than maintaining ten universes?) In BusinessObjects 3.1, our predecessors handled it by maintaining a single universe, exporting it as each of the fiscal-year-universes, and then by running UNIX/SQL scripts, updating the BusinessObjects3.1 data dictionary tables to chance the OBJECTS’ tables’ OBJ_SELECT1, OBJ_SELECT2, OBJ_COND1, … OBJ_COND5 columns to “point” to the correct base fact table.

Thank you for any advice!

Anita Craig

Listserv Archives (BOB member since 2002-06-25)

2

Have you considered a Linked Universe set-up? First, delete the TABLE_19XX table from your existing Universe. Then create a Universe that contains TABLE_1998 only, link it to your main Universe, and do the joins as needed.

Do this for each year. If you need to change or update the main Universe, you do it once and the changes are reflected in all Universes linked to the main Universe.

George Baranowski/QuadraMed

Hello, Listers.

We have the situation of having many universes with identical classes and objects, except that the fact table in the middle of a star schema is a different name. The fact table is a huge (about three-megabyte rows) table of financial transactions for a given fiscal year. The only difference between universes is that the name of the table consists of the name of a different fiscal year.

Essentially, we’d like to be able to maintain a single universe, and to dynamically choose which fact table to “substitute” into the universe.

Thank you for any advice!

Anita Craig

Listserv Archives (BOB member since 2002-06-25)

3

Anita,

  1. Do joins from your reference tables to, say, X_1995

  2. rename (Table|Rename…) a your fact table from X_1995
    to
    X_@Prompt(‘Pick a year’,‘N’,mono,free)

Never done this in production (and I always skip this one when doing training), so be careful out there… I just tested this on Island Resorts Marketing, and don’t know how other DBs react.

HTH,
-Harri

Listserv Archives (BOB member since 2002-06-25)

4

Harry, this is EXACTLY what I was looking for. We’ve tried it, and it’s GREAT! Our users will also love being able to easily reuse queries for the different fiscal years.

The next time you travel from Finland to sunny (sometimes) California (San Francisco Bay area), please be sure to give us a call, and Kathy Mahler and I would be delighted to take you out to lunch!

Kind regards,
Anita Craig
Stanford University

At 11:11 PM 11/30/98 +0200, you wrote:

Anita,

  1. Do joins from your reference tables to, say, X_1995
  1. rename (Table|Rename…) a your fact table from X_1995
    to
    X_@Prompt(‘Pick a year’,‘N’,mono,free)

Never done this in production (and I always skip this one when doing training), so be careful out there… I just tested this on Island Resorts Marketing, and don’t know how other DBs react.

HTH,
-Harri

Harri Kinnunen
BusinessObjects Consultant
Enator Group, Finland
+358-50-5554488
harri.kinnunen@enator.fi

…

We have the situation of having many universes with identical classes and objects, except that the fact table in the middle of a star schema is a different name. … So table
X_1990 has the same columns as table X_1999, but one is for fiscal year 1990, and the other is for fiscal year 1999.

Essentially, we’d like to be able to maintain a single universe, and to dynamically choose which fact table to “substitute” into the universe.

Listserv Archives (BOB member since 2002-06-25)

5

Harri Kinnunen schrieb:

Anita,

  1. Do joins from your reference tables to, say, X_1995
  1. rename (Table|Rename…) a your fact table from X_1995
    to
    X_@Prompt(‘Pick a year’,‘N’,mono,free)

Never done this in production (and I always skip this one when doing training), so be careful out there… I just tested this on Island Resorts Marketing, and don’t know how other DBs react.

YES, That’s the right trick. Unfortunately, this only works for type ‘N’ prompts. i.e. numeric prompts. If you try to, say, let the user enter the whole table name (X_1998) you need an alphanumeric prompt, in which case BO later generates a query with single quotes around the name of the table. This might work, e.g. with Access-files, but not with other database systems… and you will get something like

SELECT ‘X_1997’.a,‘X_1997’.b
FROM ‘X_1997’
where …

The typing of the prompt (‘N’) in Harri’s example will inhibit the single quotes, but I guess, will force the user to numeric input only…

Another ‘unsupported’(?) trick:
you can also rename a table to, e.g. ‘(Select x.a namea, y.b nameb from table1 x, table2 y where …)’, create an alias, say ‘dyn_tab’ for this table, and define objects, say, A to be ‘namea’ from this table and B to be ‘nameb’, which will give you the following SQL:

SELECT dyn_tab.A, dyn_tab.B
FROM (select … ) dyn_tab
WHERE <>

which is not possible otherwise

PS: take care: the length of table names is limited by about 130 characters in the repository, and I never tried to export such a beast… :wink:

Have fun!

Walter

DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)

6

Sent: 1. joulukuuta 1998 20:23

[snip]

The typing of the prompt (‘N’) in Harri’s example will inhibit the single quotes, but I guess, will force the user to numeric input only…

Oddly enough,

the following SQL works on my 4.1.3 on Access97. I just type “Customers” (without quotes) into the prompt. But, like Walter said, other DB may not be so lenient on this one.

SELECT
Table__2.last_name,
sum(Invoice_Line.daysInvoice_Line.nb_guestsService.price) FROM
@Prompt('Pick a window','N',,mono,free) Table__2, Service,
Invoice_Line,
Sales
WHERE
( Table__2.cust_id=Sales.cust_id )
AND ( Invoice_Line.inv_id=Sales.inv_id ) AND ( Invoice_Line.service_id=Service.service_id ) GROUP BY
Table__2.last_name

Best regards,

-Harri

Listserv Archives (BOB member since 2002-06-25)

7

See this topic for more valuable info…

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