BO sap

Document Sample
BO sap Powered By Docstoc
					                         SAP in BUSINESS OBJECTS STUFF


Use of List of values.
When user needs to filter data in a query based on specific object values, User can simply
view the LOV of that objects and choose the value on which they want to filter the data.


e.g. if COUNTRY dimension has following distinct values


A,B,C and if user wants to filter the data of country B, user can put a filter on Country
dimension and choose the B as filter while executing the query.


How to create a LOV for an object.
1. Double click on object in designer to view its properties.
2. Click on Properties Tab
3. Click on “Associate a List of Values” checkbox.
4. Select other LOV options based on requirement.




When first LOV is created it is stored in .LOV file name at universe subfolder onthe system file
system.
The default location is


C:Documents and Settings<UserName>Application DataBusiness ObjectsBusiness Objects
12.0Universes@<ServerName><UniverseName>
LOV Options
List Name
It’s the name of LOV file by which it will stored on local file system. User can override the
default name and can enter his own LOV name. Maximum character limit is 8.


Allow Users to Edit List of Values
When checked this option allows report users to edit the list of values of an objects. The
purpose of a list of values is usually to limit the set of available values to a user. If they can
edit a list, you no longer have control over the values they choose. Normally, if you are not
using a personal data file as a list of values source, you clear this option to ensure that users
do not edit lists of values.
Automatic Refresh before Use
When selected this option LOV will be refreshed each times it is referred and used in report.
You should choose this option only if contents of underlying column are frequently changing.
This options should be use very carefully after evaluation. If this option is not selected LOV is
refreshed first when the objects is used in a user session.


Hierarchical Display
Select the Hierarchical Display property to display the cascading list of values as a hierarchy
in Web Intelligence.
Export with Universe
When this option is selected LOV file associated with object is exported to universe CMS and
gets stored as XML on CMS


Viewing the LOV of an object
To view the LOV of an objects click on display button on properties tab of an object
Modifying the LOV of an object
You can remove the values from LOV of an object by applying a filter or add values to LOV by
adding a column.


Apply condition on LOV
To apply condition on LOV
1. Click on Edit button on objects edit properties tab
2. The designer query panel will appear showing default object of a LOV
3. Drag drop the condition object in condition pane and specify the appropriate condition.
4. You can also view the SQL of the LOV query by click on SQL icon on toolbar.
5. Run the query to test the values after applying condition on LOV
View and Edit LOV of complete universe
You can also view all the object which has LOV associated with them and edit them.


1. Click on Tools->List of Values->Edit
2. List of values dialog will appear
3. Select the LOV objects and click on Edit if you want to edit a LOV.
1. In addition to query you can also define LOV for an object using personal data file like CSV
   and values from this file can also be used as LOV for an object. To do so.
2. Click on Personal Data and provide the details on Personal data LOV dialog box.
Cascading LOV
Cascading LOV is a LOV associated with hierarchy of an object in the universe. Cascading LOV
is created, and if any of the object is used as prompt filter in report query, user has to answer
series of values from cascading LOV.


How to create Cascading LOV
1. Click on Tools->List of Values->Create Cascading LOV.




1. Add the object and re-arrange them as per your hierarchy
2. Click on generate LOVs
3. Click OK.
Now if you use any of the objects as prompt in query. It will prompt the hierarchical LOV to
user.


Loops:



Understanding Loops and its Resolution Technique in Universe

by BIDW TEAM on NOVEMBER 7, 2010


In the series of Business Objects Designer Tutorial in this post we will be learning about loops in
Universe how to resolve it.
What is Loop
Loop is a join path problem which causes query to return less records than expected. Loop arises
when join between tables forms a closed path.


e.g. in below tables, join arrangement between table forms a loop when these tables are joined in
universe.




How to detect loop.
There two ways to detect loop, automatically and manually. You can just view the structure
in BO designer and detect the loop visually by seeing a closed or circular join path or other way is
detect it using loop detection tool.
1. Click on Tool
2. Automated Detection
3. Detect Loop or click on detect loop icon on toolbar.




How loop causes a query to return incorrect data
Now if you look carefully at country table. Country table has two meaning, country where resort
exists and country of a tourist.
Now if you create a report to see number of tourist and their country by each resort country, you
will get undesired result. Query will only show data for only that country where tourist country
and resort country is same, means it will not show tourist which came from other country.


If you build report on above object, designer will generate query like


WHERE
( City.city_id=Customer.city_id )
AND ( City.region_id=Region.region_id )
AND ( Country.country_id=Region.country_id )
AND ( Resort_Country.country_id=Resort.country_id )
AND ( Customer.cust_id=Sales.cust_id )
AND ( Sales.inv_id=Invoice_Line.inv_id )
AND ( Invoice_Line.service_id=Service.service_id )
AND ( Resort.resort_id=Service_Line.resort_id )
AND ( Service.sl_id=Service_Line.sl_id )
AND ( Service_Line.service_line = ‘Accommodation’ )
Now if carefully notice the WHERE caluse you will see why result are coming wrong.
Country.country_id=Region.country_id
Country.country_id=Resort.country_id
Above two joins are putting the restriction causing query to return only those tourists which reside
in same country of resort


You can fix the loop issue using Aliases or Context in designer.
Resolve Loops using Aliases
Alias is an alternative name given to table or any other object. Same technique can be used to
break the loop in designer.
In our loop problem COUNTRY table is serving two purposes, resort country and customer country.
We can break this loop by creating an alias table for country table to separate resort country and
customer country as.


Country table will join to resort to become resort country


Country_Regioin alias will be joined to region to become customer country
Now if you create same report you will see


Country_region =Region.country_id
Country.country_id=Resort.country_id
There is now one join applying a restriction on the Country table and another join applying a
restriction on the Resort_Country table. Now you can see the loop has been broken.


How to create an alias
1. Select the table for which you want to create a alias.
2. Right click on table and from menu click on alias OR
3. from Insert menu select Alias
4. Give the new name of an alias
5. Remove the join from original table
6. Join the alias table appropriately and set the cardinality.
Once you have created an alias, you would also need to redefine the object to use alias table. In
above case origin country should use coutry_region.country column instead of country.country
column.


Resolving Loops using Contexts
Context is another way to resolve loops in universe. Contexts resolves loop by defining a set of join
that defines specific path through tables in a loop. It makes sure that join are not included from
different path in the same query.


Generally contexts are used solve a loop caused by multipurpose lookup table.
Understanding context and its use in Business Objects
Universe
by BI DW T EAM on NOVEMBER 7, 2010



What is a context?
A context is a group of path that defines a specific path for a query. Any objects created on a table
column which belong to specific contexts isnaturally compatible with all other objects from same
contexts. When objects from two or more contexts are used, separate SQL is generated and results
are then merged in a micro cube. This makes sure that no incorrect result is generated due to loop
or any other join path issue.
How to Create Context.
There are multiple ways to create context.


1. Automatically using Detect Context
2. Automatically using Loop Detection Tool.
3. Manually
For automatic detection of context, cardinalities must be set correctly.


How to detect context automatically
1. Click on Detect context icon from the toolbar.
2. Designer will show up the name and joins for the candidate context
3. Review the highlighted joins in respective context if you are ok with it click on add button.
4. once done click OK




How to detect context using Loop Detection Tool
1. Click on tools
2. Click on Automated detection-> detect loops
3. Detection tool will show the loops in universe and it will propose either alias on context to
    resolve the loop.
4. Click on forward button till it says “The loop is not covered by any context”
1. Click on candidate context button to detect context.
2. It will show the possible context. Follow the process of “Creating context automatically” to
   create context and resolve loop.
Creating context manually.
To create context manually


1. Click on Insert->Context
2. A create context box will appear.
3. Enter the name of context and joins in context.
4. Click OK
5. Make sure each join except shortcut join is included in at least one context.




Testing Contexts
When context exist in universe. Designer can generate three types of queries.


   Ambiguous query
   Inferred query
   Incompatible Objects query.
Whenever you create a context you should check context against these three queries two test
correctness of context.


Ambiguous query
If you have multiple contexts in universe, try creating a report which includes objects common to
both the context. This situation does not give enough information on which context to use while
generating the query and inturn it prompts all available contexts to user to choose from. Once user
selects the context, query is generated accordingly.
Make sure Allow selection of multiple context option is selected from SQL tab of universe
parameters as user might select multiple context in case of ambiguous query.
Incompatible Objects query.
If you have used object in a report which belong to multiple context and objects which are unique
to each contexts. The tool creates multiple SELECT statements for each context and then result is
merged in to present in single table form this is called as Incompatible Objects query.
Inferred query
Inferred query is query which gives enough information to tool to choose the right context without
prompting to user.


When you have context in universe make sure you test the contexts by creating all three types of
query and observe the behavior of tool in each query type.
Resolving Loops using Shortcut Join.
Apart from alias and context, shortcut join can also be used to resolve the loops in universe.




Shortcut Join and Its use in Business Objects Universe
by BI DW T EAM on NOVEMBER 7, 2010
In this SAP Business Objects Universe Designer Tutorial we will learn about shortcut joins, How to
use shortcut join to generate performance optimized query and use of shortcut join to resolve
loops.
What is shortcut join?
Shortcut join is a join that joins tables by bypassing middle table that exist in the universe.
Generally shortcut joins are used to generate more efficient query by reducing the joins in the
query.
e.g. Consider following scenario. If we want to see article and its sold amount the query will join
article_lookup and shop_facts through product_promotion_fact,




However this is not a efficient query as we can simple join article_lookup ans shop_facts using
article_id.
But creating such a join, It will introduce a loop in universe, now you can solve this loop using alias
or shortcut join.


Lets see how we can solve this using shortcut join.


Instead of creating normal join between article_lookup and shop_fact, we will create shortcut join.
Now check the loop. You could see that, loop has been resolved.
How to create shortcut join
1. Join the tables using common column between tables.
2. Check the shortcut join check box to mark join as shortcut.
3. Shortcut join is represented as dotted line.
4. Click OK.
SQL Traps in Business Objects Universe : How to Solve CHASM Trap

by BIDW TEAM on JUNE 7, 2011



A Chasm trap is a join path type problem between three tables where two many-to-one join path
converge on a single table and there is no context to separate the converging path.
However even if we have above type of joins in universe we experience chasm trap problem only
when


1. There is many-to-one-to-many relationship between three tables
2. Reporting query have objects on tables from many end
3. There is more the one value for a single dimensional value.
Let’s see it in detail. Consider below diagram.
Now in above case when a query includes object from table B and Table C and objects from table
A, the CHASM trap causes a query to return every possible combination of one measure with other.
This result gets multiplied by number of rows in result set and output is similar like
a Cartesian product.
This CHASM trap can be resolved by executing separate query for each measure and then merging
the results.
How to detect CHASM trap in a universe
CHASM trap can be detected automatically; you would need to use multiple ways to identify a
possible CHASM trap issue.


   Make sure you arrange one-to-many table from left to right in universe and analyze one-to-
    many relationship to detect possible CHASM trap issue.
   Use detect context automatic tool to detect possible context in universe and use them in order
    to avoid CHASM traps.
   Test many-to-one tables by creating reports using object from table at many end. Try adding
    additional dimension object in report. If there is a CHASM trap aggregated values will be double
    which might help you to detect possible CHASM trap.
Lets see a practical CHASM trap example in a universe


Let consider a following joins in universe. In following diagram three tables are joined by many-to-
one-to-many join relationship.
If I want to see number of guest for a sports service, report returns following result




Service      Number of Guests
Sports       145
If I want to see number of future guest for a sports service, repot returns following result
Service       Number of Future Guests
Sports        8

However If I include moth the measure together in the same query.


Service      Number of Guests       Number of Future Guests
Sports       188                    96
Result seems to be inflated due to CHASM trap issue.


How does CHASM trap inflate the result of a query?
CHASM trap causes query to return every possible combination of a measure with other measure,
which makes query to return Cartesian product result and since result is grouped against single
dimension value its gets aggregated.


In above example


   Number of guests transactions *Number of future guest transactions
   Number of future guest transactions*Number of guests transactions
Lets go deeper to understand what has happened.
In order to examine what all rows are included in aggregation we would need to split the
aggregated data into granular level.


For “Number of Guest’ we would include additional dimension “Days Billed” to see granular data.


Service       Number of Guests      Days Billed
Sports        4                     3
Sports        133                   4
Sports        8                     6
For “Number of Future Guest’ we would include additional dimension “Days Reserved” to see
granular data.


Service       Number of Future Guests          Days Billed
Sports        7                                1
Sports        1                                2
Now lets combine the result (Disable generate separate query for measure from universe
parameters)


Service    Days Billed                  Number of Guests     Days Reserved   No. of FutureGuests
Sports     3                            4                    1               3
Sports     3                            4                    2               1
           4                            129                  1               75
           4                            35                   2               9
           6                            8                    1               6
           6                            8                    2               2
           Sum                          188                  Sum             96




You could see, query returns every possible combination of future guests with number of guests
and when result is aggregated it gives wrong numbers.


How to solve CHASM Trap?
You can solve CHASM trap using context. In above example you can create context.


1. Analyze many-to-one-many relationship to detect possible CHASM trap.
2. Use Detect Context to create contexts
1. Select the contexts and click on Add.
2. Select File->Parameters to launch universe parameter box.
3. Click SQL Tab
4. Select Multiple SQL for each contexts option.
5. Click OK
Now when you create query, two separate queries will be generated and result will be merged
on common dimension. This is how CHASM trap gets resolvedusing contexts.
Using “Multiple Statement for Measure” to solve CHASM trap.
If you have only measure objects defined for both fact tables, then you can use the Universe
Parameters option Multiple SQL statements for each measure. This force the generation of
separate SQL queries for each measure that appears in the Query pane.
This solution does not work for dimension and detail objects.




How to solve Fan Trap in Business Objects Universe
by BI DW T EAM on JUNE 9, 2011



Apart from CHASM trap, fan trap is another kind of trap that may occur in universe causing
wrong results in reports. A fan trap can occur in universe when there is a one-to-many join
between two tables which again joins to another table having one-to-many join relationship.
However having one-many-many relation in universe does not always cause fan trap. You get fan
trap issue only when reporting query have


1. There is a measure object based on middle table – Table B
2. Measure object from last table. – Table C
3. Table B (Middle table) holds values which is aggregate of values from ,Table C
e.g. Consider following example.




It satisfies the first condition of Fan trap i.e 1-N-N relationship between three tables.


Now if user creates a query to see how many models were sold by customer Sham and show his
sale quantity and sale total.


Query will include following object.
CustName, SaleTotal, ModelId, Sale Quanity


which would certainly give a wrong result as below due to existing fan trap issue.
If you could observe, Sham has sold total two models and its sale total is 100, however in above
report it would come as 200 as for each instance of model sale total is repeated in report.


So what’s really happening in Fan Trap
When you run the query with following objects


CustName, SaleTotal and SaleQty, measures are correctly aggregated, However when also want to
know modelid, you get saletotal for every model which makes result looks inflated.


There is now way can detect Fan Trap automatically, you would need to visually analyze the
relationship between table and result in report.
How to solve Fan Trap
There are three ways to resolve fan trap.


1. Changing the universe parameters to generate different SQL for each measure.
This method works only for measure objects. Though it works to solve CHASM trap and Fan trap it’s
not recommended.


1. Use combination of alias and context based on scenario in Fan traps.
Scenario 1
    1. When three tables are joined by 1-many relationship
    2. Report query contains dimension from first table and measure from rest of the two tables.
Solution
1. Create an alias for a middle table which is at many ends.


2. Create a join between new alias table and first table which is at one end.


3. Set the cardinality and set context


4. Change the SELECT of SALE_TOTAL object to refer to alias table.




                                                  `
Now if you create a create query by implementing above solution you would see two queries for
each measure and in turn result gets merged at the report cube level. This should solve the fan
trap.


Scenario 2
    1. When two tables are joined by 1-many relationship
    2. Report query contains dimension and measure from first table and measure from last table.




Solution
1. Create an alias for table A
2. Crate join from the alias Ax to table A and set cardinalities
3. Set context B and A
4. Edit object Y so that it refers to columns in the alias Ax instead of table A




How to Avoid Fan Trap
While designing a universe you can avoid the Fan trap altogether by creating measure from the
table which is at the end table of 1-M-M relationship however it also depends on if you have
those aggregate columns present inend table.
Hierarchies in Business Objects Universe
by BIDW TEAM on JUNE 12, 2011



Hierarchy is an ordered sequence of dimensions which is very helpful for multi-dimensional
analysis of data.


e.g. time hierarchy.


Day->Week->Month->Quarter->Year


Multidimensional analysis enables user to see data from different perspectivewhich uncovers the
various pattern in data which would be very helpful in taking tactful business decision.
Hierarchies can be natural/Default or logical based on business rules.
Default Hierarchies/Natural Hierarchies:


A natural hierarchy is nothing but natural order of dimensions.
e.g. Time Hierarchy


Day->Week->Month->Quarter->Year
Geographic Location Hierarchy.
City->State->Country->Region


Logical Hierarchies:


Logical hierarchies are set of dimensions whose order is set based on business domain of analysis.


e.g. Hierarchy of designation.


CEO->CTO->Director->Manager


Hierarchies in Business Objects Universe
Default Hierarchies
By default hierarchy is set based on order in which dimensions are placed in the class.


e.g In store class dimensions are arranged as State->City->Store Name. So by default hierarchy
would be set as from top dimension to lower dimension.




Custom Hierarchies:
Business Objects allows creating logical hierarchies using custom hierarchy. In custom
hierarchy the sequence of dimensions is defined by developer based on business need of analysis.
End user might want to drill down the revenue generated based on employee designation. You can
define the sequence of dimensions based on business need.


Creating Custom Hierarchies:


1. Click on Tools->Hierarchies
2. Hierarchies editor will show default hierarchies
1. Click on New Button to create new hierarchy
2. Select objects by expanding classes and click on Add button
3. You can use Move UP/Down Button to arrange the dimensions.
If two or more hierarchies starts with same dimensions but follow different dimensions at lower
end and if user performs drill-down on dimensions from such hierarchy then Web Intelligence asks
user to select the drill path.
e.g. If we create two custom hierarchy which starts with Year but follow different path.




Then it asks user to select the drill path as there two drill path defined from Year.




Automatic Time Hierarchies
Generally, almost every business wants to analysis the data based on time e.g. Year, Month,
Quarter
In data warehouse time analysis is done by associating a date dimensions however there might be
cases where date dimensions are not available. IN such cases Business Objects gives an facility to
create time hierarchy based on Date.
e.g. We have revenue table which has date in it however we want to analyze the revenue based on
year, month.


We can create automatic time hierarchy using Revenue Date.


To Create Automatic Time Hierarchy.


1. Create a object using date
2. In the properties tab of object click on “Automatic Time Hierarchy”




1. Select the dimensions to be created using “Automatic Time Hierarchy”
1. Click OK.
Advantages of Automatic Time Hierarchy:


1. It’s the fastest way to create time hierarchy
2. Business Objects uses database scalar function automatically and uses it in SQL Select
    statement.
Dis-advantages of Automatic Time Hierarchy:


1. Developer can not control the appearance of object.
2. Developer can not edit the object definition of automatic time hierarchy.
3. LOV can be applied to original Date object not the derived objects like Year.
Note: We would not recommend using automatic time hierarchy. Its always good to have separate
date_dimension table in database.




Using @Functions with Universe Objects

by BIDW TEAM on JUNE 14, 2011


@Functions are available in Universe designer and are available in the edit select box of an object.
Following @functions are available in Designer.
   @Select()
   @Variable()
   @Where()
   @Script
   @Prompt
   @Agreegate_Aware
Let learn them one by one.


@Prompt
This function is very useful to create an interactive object which would force user to enter some
value for a condition in order to restrict the dataset to be returned by the query.


Prompt functions are generally used in where clause of an object to build interactive filter
condition in the report.


The syntax of @prompt function is as below.


@Prompt(‘message’,'type’,[lov],mono/multi,free/constrained/primary_key,persistent/not_persiste
nt, [{'default value':'default key'[,'default value':'defaultkey',...]})


Message: Is the text which would be prompted to user. It should be included in single quotes.
Type: Data type to be returned by function. It can be any one of following.
A: Alphanumeric


N: Number


D: Date


LOV: List of values which would be displayed to end user to chose from. LOV could be hardcode or
you can use LOV of an exiting object.
e.g. {‘A’,’B’} or ‘CountryName’


Mono: User can select only one value from list
Multi: user can select multiple values from list
Free: User can select or enter value.
Constrained: User ,ust select value from the list.
Primary Key: This options needs to be used with free or constrained. When used , user selected
values are not used its key values is used from index awareness column.
Persistent: Last user selected value is persistent when report is refreshed.
Defaultvalue:keyvalue: Default values to be presented to user. If you have used Primary Key in
function you must specify the key value here.
@prompt(‘Enter value(s) for Customer with A:’, ‘A’,'CustomerCustomerA’,
Multi,primary_key,{‘A’:’1’,‘B’:’2’})


@Select
This function is used to reselect the select clause of another object. This function is pretty useful
to reuse the existing select clause of another object instead of creating it again. So when original
object select clause changes, all other objects would be changed if they are using original objects
select clause using this function.


Syntax.


@Select(ClassnameObjectname)


@Where
This function can be used to re-use the where clause of another object.


Syntax:


@Where(ClassnameObjectname)


@Variable
The @Variable function is used to call the value assigned to variables. This function is generally
used in security implementation. You can more information on this function on business
objects user guide.
e.g. You variable (‘BOUSER’) will return the name of currently logged inbusiness objects user
@Aggregate_Aware()
This function is used to define the aggregate awareness and will be discussed in another article.


@Script
This function is used to call a VBA macro and works only for desktop intelligence and designer and
is not advised to use in case of web intelligence. It works only on windows platform.
@Script(‘var_name’, ‘vartype’, ‘script_name’)
Using Data Restriction Techniques in Business Objects Universe

by BIDW TEAM on JUNE 16, 2011



Data restriction is a condition which would restrict the data to be returned to user. There are
multiple ways and type of restrictions in universe which you can use.


Forced Restrictions.
Forced restriction are those who gets applied whenever particular objects is used in reporting
query. User does not have to choose any condition for this.


You can create forced restrictions by putting condition in a WHERE clause of an objects.




Drawbacks of forced restrictions.


1. User can not override the objects restriction.
2. User might not know that there is default restriction on objects and might get confused after
    seeing a data.
Object restriction Using CASE.
In forced restriction user does not have ability to select the restriction to apply which might cause
user confusion. There is another way to apply restriction using CASE statement to objects.


You can use CASE statement to restrict the data returned by user and name the object accordingly
to represent data to be returned by object.


e.g. If we want to report year level revenue we can create multiple object as below.


Revenue2007


SUM( CASE WHEN RevenueYear = 2007 Then Revenue Else 0 END)
Revenue2008


SUM( CASE WHEN RevenueYear = 2008 Then Revenue Else 0 END)
e.g. If user wants to see amount sold per year by each shop the he might create query as below.




The actual SQL will look like as below.
End user can select any of the objects based on his/her requirement. However drawback of this
method is number of object. Developer will have to create lots of object based on requirement. In
above case one object per year.


Using Condition Objects to apply Restriction
A condition objects or a predefined condition is restriction, created indesigner which user can
choose to apply or not.
When user creates a report query they can inert these restriction whenever they want to restrict
the data which gives much more control to user to apply restriction than forced restriction.


How to create condition Objects
1. In designer select the filter radio box at the bottom of classes and object window.
2. Click the class in which you want t create a condition objects.
3. Right click on class and click insert condition or click on insert condition from editing toolbar.
4. In the condition objects dialog, type in name in name of the condition objects.
5. Enter the condition in the WHERE field.
1. In condition objects, you can define if condition to be mandatory applied on universe or class.
2. Click OK and export the universe.
Now whenever user uses the condition object as a restriction on data, SQL WHERE clause will
contain condition defined in condition objects which would restrict the data.


Advantages of restriction
1. User can choose the restrictions.
2. We can apply it on entire universe or a class.
e.g. If I want to see revenue for 2003, I can simply add the predefined conditions object.
Query:
Using Objects to create restriction.
Above all method use universe, in this technique you can use normal universe objects to create
restriction. Just drag-drop the object from web intelligencequery panel and select the user
interaction type like prompt, from LOV etc.
Using Derived Table in SAP Business Objects Universe

by BIDW TEAM on JULY 13, 2011



What are Derived table and its use?
Derived table is not a physical in database however its logical table created inBusiness
Objects Universe using SQL. Derived table can be considered like views in database where
its structure is defined using SELECT statement.
Advantages of Derived Table:
      Derived table in Universe lets you create a SQL statement to fetch data using various
       expressions, joins which is not possible using universestructure.
      Its lets you put inline views (select statement in FROM clause) which are not possible in
       Universe normally.
e.g.

select agg1_id as id from
(select * from Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma)

      Derived table can be treated as normal tables and can be joined with actual table in Universe.
      Its lets you merge data from different table which is not possible using normal in universe using
       underlying data sources.
      One can embed prompts in derived table definition.
      You can use derived table as a lookup when you have multiple fact table separated by
       contexts. Normally if you want to use measured from different fact table then Business
       Objects creates two queries one for each measure. Now some time this may result
       in performance issues. You can avoid this by creating a lookup table for different fact tables
       using derived tables.
e.g.


Suppose you have measure1 in fact1 and measure2 in fact2 and dimension is dim1. Now if you
create a query with dim1, measure1, measure2 you will get two different queries. Now instead of
this you can create separatederived table which includes dim1, measure1, measure2.

Disadvantages if derived table.
Since derived table is not an actual table you may face performance issues if underlying SQL query
has performance issues.


How to Create Derived Tables
      · From menu mar select Insert->Derived Tables
      · Now write the SELECT statements which to define the structure ofderived table. Remember
       to give Alias to column if you are using any expression in column list.
      · Click on “Check Syntax” to confirm the definition of derived table
      · Click OK
· Join the newly created derived table to existing table.


   · If you have context. Include the join in relevant context.
   · Save the Universe.
   · Now you can create object using derived table just like normal tables.
Nested Derived Tables
Nested derived table is nothing but a derived table using another derived in definition. It behaves
similar to normal derived tables. Nested derived tables are generally using when
underlying derived table is complex to build. In that case you can create different
small derived tables and then use this derivedtable in main derived table.


Advantage of using nested derived table is the simplicity in derived table creating. Also Business
Objects combines the definition in single SQL and it’s treated as single SQL.
Business Objects does not limit number of derived tables but nesting is limited to 20 levels.
You can create a nested derived table by using existing derived table in the from clause.




Linking Universe in Universe Designer
There are many times when universe development task is big and it’s not possible to dingle person
to develop the universe in stipulated time. To cater this need Business Objects has provided the
facility to divide the universe design task amongst several designers and then integrate the work in
single universe using universe linking method.
What is Universe Linking?
Linked universe are universes which share common component such as objects, classes and joins.
When two universes are linked the one universe is called as core universe. It’s the main universe
which contains commoncomponents where other universe is called as derived universe. Changes
made to core universe are automatically propagated to derived universe.
Uses and advantages of universe linking.
   When you have to develop multiple universes but there are some commoncomponents across
    these universes in that you can create a core universe of common components and link it to
    other universes.
   Linking of Universe enables to distribute the universe designing taskamongst other developers.
   It’s also helps to follow the code-reusability practices.
   If there are any changes to common objects. It needs to be done only in core universe. It gets
    propagated to all derived universes.
   Linking of universes help in universe maintenance.
Universe Linking Strategies.
Core Strategy: This strategy is used when you need to develop a universe for different functions. In
such case you can create multiple universes for each function and the link all universes in single
universe. This strategy allows us to create a common object only once and also help to split
the universe designtask amongst developers.




Master Strategy: Suppose you have a requirement to create clone of existing universe. You can
achieve this by creating another copy of existing universe however this will increase maintenance
as you would need to maintain two universes now. To avoid that you can use master
linking strategy. In which existing universe is linked to new blank universe so it creates a copy of
existing universe with different CUID and we need to maintain only one universe as core universe
is linked.
Multiple Core Strategy: Now if you want divide the development task then you can follow this
approach in which developers can develop their universe and at the link all universe in one
universe.


Limitations and Restrictions of Linking Universes:


   Both the universes (core and derived) must use same connection and should connect to same
    database.
   Both the universe must be present in same repository in order to link.
   Only one level of linking is allowed you can create derived universe from
    another derived universe.
   Both universes should have unique object and classes. If there are duplicate objects/classes it
    will be renamed in core universe.
   Tables from two universes must be joined after linking in order to avoid Cartesian product.
   When core universe is linked in derived universe only classes, objects and tables are made
    available in derived universe. Context and LOV needs to be recreated in derived universe.
How to Link Universes?
To link universe make sure


   · Core universe is exported to repository
   · It’s open in designer.
   · Now open universe parameters from File->Parameters
   · Click on “Link” tab
   · Click on “Add Link”
   · Select the core universe to link.
   · Click OK
   · After this components from core universe will be available in deriveduniverse and it will be
    grayed.
   · Now analyze the derived universe and create joins between tables added from core universe.
   · Create context/Alias wherever required.
   · Save and Export the derived universe.




Using Include to import one universe into another
In linking universe contents are not copied to derived universe and one cannot edit the core
universe components in derived. However sometimes one need to merge two universe into one.
For this purpose one can use “Include” universe approach instead of linking. When universe are
included components of one universe are copied into another.


Difference between Linking and Including Universes
Including and Linking decision needs to be taken based on your own need following are the points
which may help.


Linking.
   · Core universe structure is created once and used in many deriveduniverse.
   · Only one copy of components from core universe exists in repository.
   · Changes needs to done only to core universe and it gets propagated to allderived universe.
   · Linking universe needs both the universe to be present in repository.
   · Only one level of linking is allowed.
   · Context and LOV needs to recreate in derived universe.
   · Both the universe must use same connection and connect to same database
Including
   · It’s the easiest and fastest way to copy universe into another.
   · Context needs to redefine after including.
   · Changes are not propagated from core to derived universe.
   · Both the universe must exist in repository like in linking.
   · One can easily maintain one universe rather than multiple universe o maintenance becomes
    bit easy.



Using Index Awareness in Business Objects Universe for Performance Optimization

by BIDW TEAM on JULY 18, 2011



What is Index Awareness?
Making universe index aware means telling universe on which columns indexes are created. This
helps Business Objects to generate efficient query which uses indexes instead of actual column
values which help to speed up the data retrieval.
e.g. If we have customer name in the query its useful to end user of report however we can use
customer id to retrieve the data by making the use of index awareness which would help to fetch
data faster.
You can define two types of index awareness in universe.
Primary Key: Using primary key index awareness universe can use index valueinstead of actual
value of column. The query will thus use the key value. This helps database to fetch data faster.
Foreign Key: Using foreign key index awareness on object universe can filterthe data without the
need of join in query. Suppose you need to build a report which has filter on dimensions table. In
absence of index awarenessdesigner will use actual dimension values which requires join
between fact table and dimension table. However if we had foreign key index awareness applied.
Designer can apply filter directly in fact table using foreign key index. So this avoids join between
dimension table and fact table. However foreign key index awareness requires dimension column
values to be unique if same value is represented by different key. Then this may return unwanted
result.
How it works?
Suppose you are building a report on Island Universe “Service wide Sale revenue.”, After you drag
drop the object, You will see following query generated.




Now if you see in the query, designer has joined Outlet_Lookup table to put restrictions on the
states and using actual values from service table to filterthe data.


Now Suppose we apply index awareness on service object defining primary keys and foreign keys.


The query will look as below.
If you could see in the query, Dimension values have been replaced with actual foreign keys
filtering data on fact table. Which is an efficient way to filter data? So query will run faster
compared to earlier way.


However remember, index awareness might return wrong result if you have dimension value which
have two different keys in dimension table.


e.g.


If we have data as below in service dimension
Service_id                                          Service


212                                                 Activities


213                                                 Activities
Now if you have index awareness applied on service object and using service as a filter in report.
Since ‘Activities’ have two keys, Business Objects does not know this might put any of the PK as
filter returning wrong data in report


While defining the index awareness for particular column you can also definethe data restriction
for the object using WHARE clause in index awareness. It’s very useful to restrict the data in index
awareness.
e.g. for service object I can define service price WHERE clause as below.




How to define index awareness.


To define index awareness


1. Right click on Objects to open its properties.
2. Open the Keys Tab
3. Define the primary keys and foreign key to be used in index awareness.




Index awareness when used with proper data analysis can give you significant performance gains.




Universe Access Restriction

by BIDW TEAM on JULY 22, 2011



Once you are done with development of universe there might be requirement to restrict the
universe access to particular user or user group. You can achieve this by applying various access
restrictions in universe.
Universe has various types of access restriction as explained below.
Connection: Using this access restriction you can define which connection is accessible to
which user group.




SQL Controls: Using the restriction type one can define result set size and query execution time.
SQL: Using this restriction you control the SQL generation.
Objects: Using this restriction you can define which objects will be not be accessible to which
users/group.
Row: Using the restriction type you can define row level access restriction. It uses the WHERE
clause to restrict the data access.
Table Mapping: Using the restrictions you can define alternate tables for particular group of user.
I will be covering each restriction type in detail with example in others posts. Also you
can play around with restriction types to understand its working.




Removing the Confusion from Calculation Contexts
JULY 31, 2011 8 COMMENTS

If you’ve read enough of my blog posts, you’re probably aware that I’ve been using BusinessObjects for a
long time, and, during the early years, I was pretty clueless. One of the topics that had me completely
stumped was Calculation Contexts. I attended the classes, and learned a lot, but could not grasp the concept
of Calculation Contexts. So, I studied the training manuals, but it just wasn’t sinking in. So, I studied the user
guides, but it still wasn’t making sense. So, I decided that I just wasn’t smart enough to understand this
concept, and did my best to live without it.
It wasn’t until 2002, while attending the BusinessObjects User Conference that I finally got it. I was attending
a session by my good friend Dave Rathbun. One of his topics was Calculation Contexts. Dave explained
them in a slightly different way than the books. Suddenly the light bulb went on, and it hasn’t dimmed since.
In 2004, I started teaching BusinessObjects classes, and I always taught Calculation Contexts the way I
learned it, and it seemed to make more sense than what was in the books. So, my first piece of advice is, if
you really want to understand Calculation Contexts, don’t read the books.
What is Calculation Context?
The bottom line is that all calculations in Web Intelligence, by default, happen in the context of where they
appear on a report. The same formula, in different places, can give different results. Often, the default
context is exactly what we want. But sometimes it isn’t, and it’s then that we need to understand how to
manipulate the contexts.
Default Calculation Context
Let’s start with a simple example, using the infamous eFashion universe. Create and run the following query:




Next, section the report on Store name. Add a Sum to the Sales revenue column. Notice that, even though
the formula for the Sum is the same in every section, it yields different results in each section:
=Sum([Sales revenue])
This is because the calculation takes place in the context of where it appears on the report. In this case, the
formula is within a section, so it outputs a different value for each section.
Next, drag the cell with the sum, out of the block, and drop it next to the section header cell.
Notice that the results are the same as when the sum was in the block. This is because the formula is still in
the context of the section, Store name, and therefore, outputs a different value for each Store name.
Next, drag the cell with the sum up and to the left, next to the Report Title cell, but hold down the Ctrl key
before you drop it. This creates a copy of the cell.




This time, after dropping the cell, you will see a different number. The number you see now is the grand total
for the whole report. Once you move the formula outside the context of a section, it returns results in the
context of the whole report.
But what if you want to calculate the percentage that each store makes up of the grand total? You would
need the following calculation: (Revenue for each store) / (Grand Total Revenue)
But how do you get the grand total to appear in the section? Well, before we answer that question, let’s talk
about Calculation Contexts, and what makes them up.
Components of Calculation Contexts
Every calculation in Web Intelligence has an Input and an Output context.
   Input Context: Determines what values are taken into the calculation. Think of it as, “I need to take into
    the calculation, one number for every __________?
   Output Context: Determines the output level, or dimensions, by which the calculation is output. Think of
    it as, “I need this formula to put out one number for every __________?
The syntax for Calculation Context is as follows:
=Aggregate Function([Measure] Input Context ) Output Context
Notice that the Input Context is inside the aggregate function, and the Output Context is outside the
aggregate function. If you don’t specify an Input or Output Context, Web Intelligence will use the defaults.
More on defaults later. By the way, while Calculation Contexts are most often used with aggregate functions,
they are sometimes used without functions.
How to use Calculation Contexts
Let’s try a couple examples to demonstrate how these contexts work. Add another report to the document
that we have already started, and place a block on it with Store name and Sales revenue.




Select the Sales revenue column, and add an Average calculation. In the footer of the block, you should see
the following:




Next, we’re going to put an Alerter on the Sales revenue column, to highlight all values that fall below
average. In order to reference the average of 2,799,016 from an Alerter, we will need to turn the Average into
a Variable. So, select the cell that has the Average value in it, and click the Create Variable button on the
Formula Toolbar.




Give the Variable the name, Average. Now we can create the Alerter. Select the Sales revenue column, and
then click the Alerters button on the Reporting Toolbar.
In the Alerters box, click the New button, to create a new Alerter. Name the Alerter, Below Average. In the
Sub-Alerter section, set the following conditions and formats:
Click OK, then OK in the Alerters box. At this point, the Alerter is applied to the Sales revenue column. But
we don’t see any values highlighted in bold red. Why not? Well, it’s all about Calculations Contexts.
To see why this isn’t working, add the Average variable to the block, as the third column.




The value that we would expect to see, 2,799,016, isn’t showing up. Instead we’re seeing the same values as
we see in the Sales revenue column. So let’s explore why.
As I mentioned, if we don’t specify an Input or Output Context, Web Intelligence will use the default contexts.
And in this case, we didn’t specify any contexts in the Average variable. So what is the default? Well, in the
body of a block, the default Input Context is the dimension(s) displayed in the block. And, the default Output
Context is the dimension(s) displayed in the block.
If you were going to calculate the average manually, you would need to know what the numbers are for each
store, sum them up, then divide by the count of stores. In other words, you would need to take into the
calculation one number for each store, and you would want to output one number for the whole block of
stores.
Well, that’s exactly what we need the Calculation Contexts to do. Since the default Input Context is the
dimension(s) displayed in the block, and, in our case, the dimension displayed in the block is Store name,
the default Input Context is to take in one value for each Store name. So, the default Input Context is fine.
So the problem must be the Output Context. Again, the default Output Context is the dimension(s) displayed
in the block. In our case, therefore, the default Output Context is to output one number for each Store name.
So, what is happening here is that we’re taking into the calculation one number for each Store, and
outputting one number for each Store. Clearly, that isn’t very useful. What we really want is to take in one
number for each Store (the default), and output one number for the entire block (not the default). So, we need
to specify the correct Output context.
So, let’s open up the Average variable, so we can modify the Output Context. Click at the end of the formula,
outside the aggregate function, and enter the following:
In Block
So the entire formula should now be:
=Average([Sales revenue])In Block
The “In Block” part of the formula tells the formula to output one number for the entire block. Click OK, and
confirm that you wish to modify the variable. Now, not only is the Average variable giving us the same value
on every row, but the Alerter has now started working.




OK, so now that we have this working correctly, we can remove the Average column from the block. Our next
step is to add another variable to the block. This time, we want to display the most revenue earned by each
store in any one year. So, create another measure variable, called Maximum, with the following formula:
=Max([Sales revenue])
Add this new variable to the block as the third column. Does it look familiar? Just like the Average variable,
Maximum is just repeating the Sales revenue values. This can’t possibly be correct! You’re right. It’s not
correct. So let’s figure out why.
Since we didn’t specify an Input or Output Context, the formula is using the defaults. Therefore, it is taking in
one number for each Store name, and outputting one number for each Store name. What do we want it to do?
Well, in order to know the most revenue each Store earned in any one Year, we need to know what the values
are for each Store, for each Year. In other words, we need to take into the calculation both Store name and
Year, not just Store name. And, we want to output one number for each Store name, which is the default, so
we don’t need to specify an Output Context.
So, open up the Maximum variable, and click just inside the closing parenthesis, so we can specify the Input
Context. Add the following:
In([Store name];[Year])
So, the whole formula should now look like this:
=Max([Sales revenue]In([Store name];[Year]))
Notice that this time, we put parenthesis around the objects used in the context. When you use a keyword,
such as Block or Report as the Context, you don’t put parenthesis around it. But when you use objects, like
Store name or Year, you always put parenthesis around them.
Click OK, and confirm that you want to modify this variable. Now, you see very different values.




                                                                                 Are these numbers correct?
Well, if you want to validate the results, add another block to the right with Store name, Year, and Sales
revenue, and see if the Maximum variable is showing the highest annual revenue for each Store. You will find
that, yes, the results are correct.
Now, let’s take a brief look at the first report that we sectioned on Store name. If we want to sidplay the
percentage that each Store makes up of the total, we would use the following formula within the section:
=Sum([Sales revenue])/Sum([Sales revenue])In Report
The second part of the formula, with “In Report” as the Output Context, specifies that we want it to output
one number for the whole report. Therefore, we are dividing the Sales revenue for each section, by the Sales
revenue for the whole report. Format the cell as a percentage, and you’re done.
Using Other Context Keywords
So far, we have explored the Input and Output context using the “In” keyword. But there are more than just
one keyword that can be used in Contexts. Let’s take a quick look at ForEach and ForAll.
ForEach can be used to add dimensions to the default context. For example, in the Maximum variable we just
created, we specified both Store name and Year for the Input Context. If we had used “ForEach” instead of
“In”, we would not have needed to specify Store name in the Context, as Store name is the default. Test this
by creating another variable, called Max ForEach. Enter the following formula:
=Max([Sales revenue]ForEach([Year]))
Go back to the second report in our document, and add the new variable to the block as the fourth column.
Notice that it gives the same results as Maximum. Use ForEach when you need the formula to be dynamic. If
you add additional dimensions to the block, they will automatically be added to the Input Context.
ForAll is the opposite of ForEach. While ForEach adds dimensions to the defaults context, ForAll removes
dimensions from the default Context.
OK, so let’s take a quick look at one more Context keyword: Where
The Where keyword allows you to add a filter to a formula. For example, if we wanted to split the revenue for
each year into separate columns, we could create a variable for each year, using the Where keyword. Let’s
try one.
Create a variable called Revenue 2001 as a measure, with the following formula:
=Sum([Sales revenue])Where([Year]=”2001″)
Add this variable to the existing block. In this way, you can easily filter a formula for one or more values of a
dimension. Of course, this could also allow you to calculate the variance between two years. For example, if
you wanted the variance between the 2002 revenue and the 2001 revenue, you would use the following
formula:
=Sum([Sales revenue])Where([Year]=”2002″) - Sum([Sales revenue])Where([Year]=”2001″)
There are a couple rules when using the Where keyword:
   Following the Where keyword, always use parenthesis for the condition.
   In the condition, the value to the right of the equal sign must always be a constant, not a variable.
Conclusion
This post turned out to be much longer than I expected when I started it. I guess I got carried away. But I
think that it’s very important to understand Calculation Contexts. Just remember, the Input Context says, “I
need to take into the calculation, one number for every what?” And the Output Context says, “I want to put
out one number for every what?” Of course, the more you practice it, the easier it will get over time. So,
practice much, and let me know how it goes.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:55
posted:9/8/2012
language:simple
pages:61