Exclude

building semantic layer
1

We have some test data in our database I want to have excluded on every report generated. Is there a way of specifing one clause to make shure it is not used at all (without specify the where clause on every object causing multiple test’s clauses?

hans

************************************************************* Hans Kingma
Unisource Carrier Services
Industriestrasse 21
8304 Wallisellen
Switzerland
Phone: +41 1 839 32 11
Fax: +41 1 839 32 95

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

2

In a message dated 10/19/98, 10:32:13 AM, BUSOB-L@LISTSERV.AOL.COM writes: <<We have some test data in our database I want to have excluded on every report generated. Is there a way of specifing one clause to make shure it is not used at all (without specify the where clause on every object causing multiple test’s clauses?>>

Hans:

Once you have completed your universe and exported it, you can build a constant “where clause” using the Supervisor application. That will ensure that every time a particular table is used a where clause is added to the SQL.

To make this change:

  1. Start Supervisor
  2. Click on the Universe tab at the bottom of the screen 3. Select the universe you need to customize 4. Right-click on the universe and select Properties 5. Look at the “Rows” tab on the screen that comes up.

I don’t have Supervisor actually running to verify those menu choices, but I believe they are correct. On that screen you can define constant “where” clauses that are used, which will solve your problem.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

3

Hans,

If you don’t want to go for the Supervisor side, the easiest way to do this in universe is to create sort of a self-join in the universe:

  • Say, you want to restrict your queries to Products.Product_Group = ‘A’ -->
  1. Make a join from Product_Group to any other field in the same table, say Product_Price
  2. Resulting join would be something like: Products.Product_Group = Products.Product_Price 3) Now, manually edit this join by double clicking it 4) In Edit Join -dialog, replace the orginal join SQL with: Products.Product_Group = ‘A’
    —> If someone chooses any object referring to this table, "Products.Product_Group = ‘A’ " will be inserted into the WHERE part of the SQL.

The SQL will not of course be inserted, if the table is not used in any of the result objects. Sometimes this is desired, sometimes not. Two solutions: If you have a star schema, put the self-join into the fact (the middle) table of your schema.
Or, in non-star-schema-schemas you can enforce the table in question to be used in queries by using the “Tables”-button in Object Properties -dialog. This approach is a bit clumsy, so the self-join method works at its best with star schemas.

HTH,

-Harri

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