BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

FAQ: Designer
44 members found this topic helpful
Goto page 1, 2  Next
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Bob
Site Administrator
Site Administrator



Joined: 06 Jun 2002

Posts: 1337
Location: I Live Here



PostPosted: Wed Jul 24, 2002 10:15 am 
Post subject: FAQ: Designer

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?


Last edited by Bob on Tue Aug 10, 2004 10:14 am, edited 2 times in total
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Sun Aug 18, 2002 8:21 pm 
Post subject: How can I prompt for select one, many, or all values?

icon_question.gif 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?

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

Code:
(@Select(Sales Geography\Country Group) IN @Prompt('Select Country Group or
enter * for all groups','a','Sales Geography\Country Group',multi,free) ) OR
('*' in @Prompt('Select Country Group or enter * for all groups','a','Sales
Geography\Country Group',multi,free))

In the sample, the asterisk is used as the wildcard. The first part of the formula...

Code:
(@Select(Sales Geography\Country) IN @Prompt('Select Country or
enter * for all countries','a','Sales Geography\Country',multi,free) )


works when a user picks one or more values from the list. The second part...

Code:
OR
('*' in @Prompt('Select Country or enter * for all countries','a','Sales
Geography\Country',multi,free))


works when the user selects the asterisk.

If a user selects 'USA', the following statement results...

COUNTRY IN 'USA' OR 'USA' IN '*'

If the user selects the asterisk, the following statement results...

COUNTRY IN '*' OR '*' IN '*'

The IN, combined with 'multi' are the keys to allowing the selection of multiple values from the list.

Here is another example which demonstrates how a prompt of this type can accept multiple wildcards and handle case sensitivity...

Code:
(PER_JOBS.ATTRIBUTE3 IN @Prompt('Enter Employee Survey Code(s) ("ALL" for
all)','A','Job Information\Add. Job Details Survey Code',multi,free) OR
'ALL' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'All' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'all' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'*' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free) OR
'%' IN @Prompt('Enter Employee Survey Code(s) ("ALL" for all)','A','Job
Information\Add. Job Details Survey Code',multi,free))
Back to top
Nick Daniels
Forum Aficionado
Forum Aficionado



Joined: 15 Aug 2002

Posts: 14237
Location: England


flag
PostPosted: Thu Jul 10, 2003 12:27 pm 
Post subject: What is an isolated join and how do I fix it?

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

icon_idea.gif 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 icon_wink.gif icon_mrgreen.gif
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Mon Jul 14, 2003 8:16 pm 
Post subject: How do I increase the number of items I can pick from a list

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

icon_exclaim.gif 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...

Quote:
I have thoroughly tested this ("this" being max values when using the "in list" operator in a condition) and here is what I've found out:

1. If you select the values from an LOV, BusObj checks to see if you have set the MAX_INLIST_VALUES parameter value in your .prm file. If you have, it uses this number to limit how many values you can select. This number CAN be greater than 256 if your database supports it. Oracle 8 supports up to 1000 values, so I would assume you could set this value up to 1000 if you use Oracle. (I tried 280 and it worked!).

2. If you select values from an LOV, and you have NOT set the MAX_INLIST_VALUES parameter, BusObj will limit the number of values to 99.

3. ...and this is what I didn't see documented anywhere!... If you type or cut and paste the values instead of selecting them from an LOV, the limit is whatever your database allows. It doesn't matter if you have the parameter set or not.
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Mon Jul 14, 2003 8:28 pm 
Post subject: How can I calculate the number of hours between two dates?

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

icon_exclaim.gif 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.
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Mon Jul 14, 2003 8:32 pm 
Post subject: How can I get rid of the time portion of my dates?

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

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


Last edited by Cindy Clayton on Thu Jun 24, 2004 11:54 am, edited 1 time in total
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Mon Jul 14, 2003 8:38 pm 
Post subject: How can I give my objects descriptions programmatically?

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

icon_exclaim.gif See here...


Last edited by Cindy Clayton on Thu Jun 24, 2004 11:54 am, edited 1 time in total
Back to top
MichaelWelter
Forum Advocate
Forum Advocate



Joined: 08 Aug 2002
ASUG Icon
speaker.gif*6medal_bronze.gif
Posts: 15015
Location: Rancho Cucamonga, CA, USA, Earth, Milky Way


flag
PostPosted: Tue Jul 15, 2003 11:21 pm 
Post subject: How do I create cascading or nested prompts?

icon_question.gif How do I create cascading or nested prompts?

Author: Robert Metzker

icon_exclaim.gif 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.
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Sat Sep 27, 2003 6:50 pm 
Post subject: How can I see 'ALL' in my list of values?

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

Author: Dwayne Hoffpauir

icon_exclaim.gif 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.
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Tue Jun 01, 2004 8:44 pm 
Post subject: Re: How can I prompt for select one, many, or all values?

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

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

First, ask yourself some questions:

icon_question.gif Can you get a copy of the universe from the web server?
icon_question.gif Can you get a copy from another user or Designer's PC?
icon_question.gif Do you have a backup copy in a source control system?
icon_question.gif 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.
Back to top
Cindy Clayton
Forum Fanatic
Forum Fanatic



Joined: 11 Jun 2002

Posts: 5610


flag
PostPosted: Tue Jun 01, 2004 9:08 pm 
Post subject: Re: FAQ: Designer

icon_question.gif What is synchronization?

icon_exclaim.gif 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.


Last edited by Cindy Clayton on Thu Jun 24, 2004 11:54 am, edited 1 time in total
Back to top
MichaelWelter
Forum Advocate
Forum Advocate



Joined: 08 Aug 2002
ASUG Icon
speaker.gif*6medal_bronze.gif
Posts: 15015
Location: Rancho Cucamonga, CA, USA, Earth, Milky Way


flag
PostPosted: Wed Jun 23, 2004 11:21 pm 
Post subject: How do I migrate universes between repositories?

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.

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

In subsequent migrations, you will be prompted, during export, if you want to overwrite the existing universe. Click yes.
Back to top
Dwayne Hoffpauir
Forum Groupie
Forum Groupie



Joined: 19 Sep 2002
ASUG Icon
medal_gold.gif*2speaker.gif*5medal_bronze.gif
Posts: 8644
Location: Plano, TX USA


flag
PostPosted: Fri Jul 23, 2004 8:10 pm 
Post subject: Connection confusion! Secure? Non-Secure? Full-Client? ZABO?

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

icon_idea.gif 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):
Code:
         ----Type---
  Method Sec Per Shr
    FC    Y   Y   Y
   ZABO   Y   N   N

Remember that Designer, Supervisor, and Business Query only use the full-client method of connecting. Reporter can use full-client or ZABO methods. Since the ZABO method cannot use personal or shared connections, it explains why ZABO users can't use Free-Hand SQL.

_________________
Dwayne Hoffpauir
Image link
Back to top
Bob
Site Administrator
Site Administrator



Joined: 06 Jun 2002

Posts: 1337
Location: I Live Here



PostPosted: Tue Aug 10, 2004 10:11 am 
Post subject: What is a fan trap why is it a problem how can I resolve it?

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

icon_exclaim.gif 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]


Last edited by Bob on Fri May 11, 2007 6:59 pm, edited 2 times in total
Back to top
Bob
Site Administrator
Site Administrator



Joined: 06 Jun 2002

Posts: 1337
Location: I Live Here



PostPosted: Tue Aug 10, 2004 10:13 am 
Post subject: What is a Chasm trap?

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

icon_exclaim.gif 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.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0989 seconds using 17 queries. (SQL 0.0034 Parse 0.0631 Other 0.0324)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo