FAQ: Designer

[Bob]

Designer FAQ

How should I impose a WHERE clause in my object(s) in the universe?

Is there any way to set up a prompt that will allow a user to select one value, many values, or type 'ALL' for all values in a list?

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list?

I've deleted a universe and I'm in a mess! What do I do now?

How do I migrate universes between repositories?

How do I create cascading or nested prompts?

What is an isolated join and how do I fix it?

How do I increase the number of items I can pick from a list of values?

What is synchronization?

How can I calculate the number of hours between two dates?

Why do all my date lists of values include time and can I get rid of the time portion?

What are stored procedures and how do I use them?

Is there some automated way to populate descriptions for my universe objects?

How do I link universes? Why would I link universes? What are the restrictions? What are the advantages and disadvantages?

I'm confused about connections! What's the difference between secured, personal, shared, full-client, and ZABO connections?

What is a fan trap, why is it a problem, and how do I resolve it?

What is a chasm trap, why is it a problem, and how do I resolve it?

How can I implement row level security?

What functions are included in the Functions list, and can I add more if I want to?

How can I select a table based on the response to a prompt?

Should I use Contexts or Aliases to resolve my loops?

How can I use the system date in a prompt or an object? Why won't they parse?

How can I provide a default value for a prompt? How can I make the prompt default to another object, or a formula like today's date?

I have a universe and it tells me it's from a different CMS. How can I open it?

[Cindy Clayton]

Is there any way to set up a prompt that will allow a user to select one value, many values, or type 'ALL' for all values in a list?

Yes. Below is a sample of the syntax required to allow a user to select one, many, or all values from a prompt.

[Nick Daniels]

What is an isolated join and how do I fix it?

An isolated join is one that has not been included in any context in your universe. Once you start using contexts in a universe, every single join must belong to at least one context. A join can belong to more than one context, but if it doesn't belong to any it is considered by BusinessObjects to be isolated from the rest of the universe structure.

To understand this add a new table to an existing universe that uses contexts. Create a join between that table and an appropriate existing table. Now create a new object based upon your new table. Now create a simple query with one object from your new table and one object from the table you joined to. The result is two bits of synchronised SQL. Why? Because we left our join isolated, as we forgot to add it into an appropriate context. Go back to the universe and add the join to a suitable context. Now try your report again

[Cindy Clayton]

What is the limit on the number of objects I can pick from a list of values and can I increase the limit?

BusinessObjects restricts the number of objects that can be picked from a list of values to '99'. It is possible to increase this limit by adding a MAX_INLIST_VALUES value to your database prm file. This parameter is not in any driveren.prm file by default. To use it, you must enter it in the [RDBMS] section under the (GENERAL) subsection. If you do not enter this parameter, the default value is 99.

Your database has a constraint on the number of values that can be present in an inlist condition. Oracle for example, allows thousands of values.

Picking values from a list is different than pasting or typing values into a prompt box. If values are pasted or typed in, the constraint does not apply.

One of our listserv users did some testing with regard to inlist values and had the following to report...

[Cindy Clayton]

How can I calculate the number of hours between two dates?

The best way to do it (and the fastest) is to do it in the database. If you simply subtract the two days, you will get a number in the format D.T, where "D" is the number of days, and "T" is the fraction of days in decimal form. In other words, day 2 and noon minus day 1 at midnight will yield 1.5 as the result.

Take this number and multiply by 24, that gives you the number of hours. Take the decimal portion of that and divide by 60 and you get the number of minutes. So if you have 1.5 (earlier example) and multiply by 24 you get 36 hours and 0 minutes.

[Cindy Clayton]

How can I eliminate the time portion from my dates and my date lists of values?

Read this till I have time to summarize for the FAQ's ...

[Cindy Clayton]

Is there some automated way to populate descriptions for my universe objects?

See here...

[MichaelWelter]

How do I create cascading or nested prompts?

Author: Robert Metzker

Let's create a Series of Cascading Prompts for the Island Resort Marketing Universe. We'll simply work our way through the Resort class for a quick example. All of the prompts will be fairly similar, and I am going under the assumption that everyone has some Designer Experience.

Let's start at the Highest Level that we'd need a prompt for. That would be the Resort itself, since we need to know what Resorts we're interested in.

* Edit the List of Values for the Resort object.
* Check the Automatic Refresh Before Use option.
* Click Edit.
* Bring Country into the Conditions and set it to: in list Prompt('What Country are you interested in?')
* Click Save and Close
* Click Apply and edit the next LOV object.

Edit the Service Line and set up the same Prompt on Resort as: in list Prompt('What Resorts are you interested in?')
Again... Save and Close, ensuring that the Automatic Refresh Before Use checkbox is selected then Edit the next.

Service will have: Service Line in Prompt('What Service Lines are you interested in?') with the Automatic Refresh...

--------
Save this Universe and let's generate a report.
--------

Generate a report that pulls in the Country, Service Line and Revenue. Set a prompt on Service Line and apply a prompt as a condition asking: 'What Service Lines are we tracking?'

Refresh this report.

For the Service Line, click on Values.... A new Prompt should appear, asking about the Services. Again... click the Values button, and so on... and so forth.

Once you've answered the questions, you only need to hit the Values button when you need to backtrack to another level.

[Cindy Clayton]

Now that my LOV lets me use ALL for all values, how do I get the ALL in the list?

Author: Dwayne Hoffpauir

There are two approaches - overriding the LOV SQL, and creating a universe object.

Overriding the LOV SQL
1. Press the SQL button in the query panel of the LOV
2. Add the phrase UNION SELECT 'ALL' FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don't care if the object parses or not) add SELECT xx From xx UNION SELECT 'ALL' (You don't need a FROM table)
3. Click the "Do not generate SQL before running" checkbox.


Universe Object
1. Create a universe object defined as a constant 'ALL'
2. Associate it with the SYS.DUAL or equivalent table so it will parse (optional)
3. Press the combined query button in the query panel of the LOV (defaults to UNION)
4. Include the new universe object.

[Cindy Clayton]

I've deleted a universe and I'm in a mess! What do I do now?

Keep away from sharp objects . Take a deep cleansing breath and don't panic

First, ask yourself some questions:

Can you get a copy of the universe from the web server?
Can you get a copy from another user or Designer's PC?
Do you have a backup copy in a source control system?
Do you have a repository backup that your DBA could restore to a temporary location? If so you could do the stuff in this post and import the universe.

Reports will complain if they are unable to find the universe you've deleted. A thorough explanation by digpen is located here.

[Cindy Clayton]

What is synchronization?

Synchronization happens when BO creates two queries for you but doesn't really understand how to merge the result sets. Say you have a product table, an inventory table, and a sales table. You join products to inventory on product_code and place that join in the inventory context. You join products to sales and place that join in the sales context. Create the product code object from the product table, the inventory measure from the inventory table, and the sales measure from the sales table. When you create a report in the query panel, you'll get two JOINED queries. BO understands that it can write a query with inventory and product and another with sales and product and put them back together by product_code.

If you're getting synchronized queries, you've got messed up contexts, joins not in the proper contexts or objects in the query that aren't from dimension tables joined to both facts. It is very rarely ever acceptable to have synchronized queries.

[MichaelWelter]

How do I migrate universes between repositories?

Although it is not advisable to have multiple repositories, sometimes it is necessary. Migrating universes from one repository to another is not difficult, at least the first time.

Log into Designer, using the Key of the repository that currently contains the universe.
Import the universe.
Click on File - Save As, and, in the Save As dialogue box, check "Save for all users".
Click Save, and then Yes, to overwrite the existing universe.
Log back into Designer, using the Key for the destination repository.
Open the universe, and give it a new connection.
Export the universe.

In subsequent migrations, you will be prompted, during export, if you want to overwrite the existing universe. Click yes.

[Dwayne Hoffpauir]

I'm confused about connections! What's the difference between secured, personal, shared, full-client, and ZABO connections?

Glad you asked.

There are three types of connections:

Secured (Sec): stored in the repository
Personal (Per): stored on the workstation, and usable only by the BusObj user that created it
Shared (Shr): stored on the workstation, but usable by any BusObj user

Next to consider is your method of connecting to the repository:

Full-client (FC) means you are using middleware that is on your workstation
ZABO means the middleware is on the ZABO server

Now lets see how these two sets of distinctions work together (Y=Yes, N=No):

[Bob]

What is a fan trap, why is it a problem, and how do I resolve it?

Dave Rathbun describes it well in his presentation, ZEN and the ART of Universe Design. Which is located here: http://www.dagira.com/conference-presentations/
Here is a quick synopsis of Fan Trap from his presentation:

A Fan Trap is a one - many - many relationship. This would happen if you are using a Summary and a Detail table in one query. Fan Traps can cause numbers to multiply. Business Objects resolves this transparently by using 2 queries. It is resolved automatically as long as the measure objects have aggregrate functions (query will split in 2). You just need to make sure the Multiple SQL Statements for Each Measure box is checked in Designer. It is a default setting, so before you turn it off consider what it is doing.

Another way to solve a Fan Trap comes from the Business Objects web site: [obsolete link removed]

Create an alias for the table containing the initial aggregation, then create a context for the alias table and a context for the original table. You can then apply the aggregate awareness function to optimize the query. This is the most effective way to solve the Fan trap problem.

1. Create an alias for the table that is producing the multiplied aggregation.

2. Create a one to one join between the original table and the alias table.

3. Build the object that is causing the aggregation on the alias tables.

4. Define a context for the original table and a context for the alias.

5. Select File>Parameters and click the SQL tab to display the SQL page.

6. Select the Multiple SQL Statements for Each Measure checkbox.

7. Modify the select statement for the columns that are summed so that the columns in the alias table are summed and not the columns in the original table.

[Edit - updated link to new presentations page at Dagira.com]

[Bob]

What is a chasm trap, why is it a problem, and how do I resolve it?

Business Objects describes it best at this web site: http://tips.businessobjects.com/tips/unvdes/tip/ut001/ut001.htm

The Chasm trap occurs when two "many to one" joins converge on a single table. It is a one - many - one relationship. You will get incorrect results when the following circumstances exist:

A "many to one to many relationship" exists among three tables in the universe structure. The query includes objects based on two tables both at the "many" end of their respective joins. There are multiple rows returned for a single dimension.

1. One way to resolve a Chasm Trap use Designer to define a context for each table at the "many" end of the joins. This creates two SQL statements and two separate tables in Business Objects.

2. Another way to resolve a Chasm Trap is in Designer select the option Multiple SQL Statements for Each Measure from the Universe Parameters dialog box. (This is a default setting). Only applies to measures. You force the SQL generation engine in Reporter to generate SQL queries for each measure that appears in the Query panel. You cannot use this solution to generate multiple SQL statements for dimensions.