Archive

Archive for the ‘Universe Designer’ Category

Universe Parameters – ANSI 92

Universe Parameters – ANSI92

Business Objects Universe Designer supports ANSI 92 syntax for SQL joins. ANSI 92 is not supported by default. We have to activate it by setting the SQL universe parameterANSI92 to YES. If this option is set to yes, then the SQL generated will be in ANSI92 format.

 

clip_image002

 

Some of us may want to set this option to ‘yes’ when universe has mostly outer joins, since outer joins are less problematic for ANSI92 format of SQL.

Comparison between default join syntax and ANSI 92 syntax

Join syntax for two joins is shown below. The first shows the default behavior where the join is defined in the WHERE clause, the second shows the same join in the FROM clause using the ANSI 92 standard.

Default join syntax

SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
FROM Sales.SalesTerritory ST,
Sales.SalesPerson SP,
HumanResources.Employee E,
Person.Contact C ,
Sales.SalesOrderHeader SOH
where ST.TerritoryID = SP.TerritoryID and
SP.SalesPersonID = E.EmployeeID and
E.ContactID = C.ContactID and
SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
ORDER BY ST.Name

Implicit

  • Out-dated format
  • Difficult to read
  • Promotes errors
  • Does not follow best practices and naming conventions.

Same query using the ANSI 92 standard Join

SELECT ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
FROM Sales.SalesTerritory ST INNER JOIN
Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.Name, SP.SalesPersonID, C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
ORDER BY ST.Name

Explicit

  • SQL 92 ANSI compliant
  • Easier to read
  • Less prone to errors
  • Keeps code consistent
  • Performance benefits

Let’s understand this concept better by using any oracle’s explain plan or sql server profiler. For my example I have taken profiler. Following screenshot shows the information of cost and duration for above two queries. With which we can easily understand the cost, performance and duration issues when using the two formats of join.

 

clip_image004

 

Even if we take the execution plan, most of the times there won’t be any differences like the above one. If we add some join conditions (Outer) there are chances for seeing some differences. It’s up to the users or developers whether which format they can use, some of us might feel comfortable using old syntax and some don’t. But when it comes to performance, we should check and use it appropriately.

Thanks.

Business Objects – Best Practices – Tips and Tricks

In this thread, I am trying to collect all the best practices as much I can. If you can contribute your own best practices for Business Objects, it would be really good to share with all. Have a look at this post and comment about the best practices that you follow when you develop anything with Business Objects,

Universe:

  • Use proper naming conventions for universe objects, classes which should be clear enough for the business user to access the objects and its data

 

  • Try to classify the objects and folders according to the business usage and not based on any technical terms (Dimensions, Measures, data types etc.) as it should not misinterpret the business user

 

  • Give them a clear name for each objects and classes, like the one below             Category

                   Sub Category

                               Product

Use some special characters to signify the object as prompt or condition like “Product?”

  • While creating the objects (Dimension and Measures), always give a brief description about the objects. The Business users who don’t understand the objects even after having a good naming convention can go for the description details. A brief description with some good examples is more than enough for the user.

 

  • Try to avoid duplicate objects in different classes. Business Objects will not allow you to create classes with same name but it will allow for objects with same name in different classes. To avoid any confusion, give a specific name to each object. All the objects in universe should be unique.

 

  • Try to resolve all complexities (data relationship, formatting) in universe itself otherwise it may become tedious in reporting side. For instance, take Revenue as a measure that you created in universe without any formatting. It is the biggest trouble for any report developer to format for currency. This would be easy for one report but what if the developer has to do it for some 50 reports that have Revenue measure in it. So try to do all formatting for numbers, currencies from universe side itself. If it is done, then it’s just a drag n drop for the developer

 

  • After developing the complete universe, always have a complete Integrity Check which will ensure that everything in placed in perfect place and will work properly.

 

  • Always import a new copy from the repository and do the development. Anyway, the inbuilt Version Control System of Business Objects is going to warn if you export an older version of the universe when the latest version exists in the repository. But the best practice is to get the latest version from the report to avoid any kind of discrepancies.

 

  • While creating a new connection, use the connection type as SECURED. By this we can incorporate the security features that come with BO. Universes must be secure, in order to link them to others.

 

  • Hide all the objects from the universe that are not often used for reporting and not going to be used for some time.

 

  • Turn on the LOV only on the required objects

 

  • Nested classes are good but don’t drag it up to level 5 , 6 & 7

 

  • Use contexts and alias table to resolve fan trap and chasm traps in universe

 

  • Set the proper query limits at the universe parameters under “control” tab, especially “Limit size of result set to” & “Limit execution time to”. Setting the right parameters will result in a good universe by performance wise

Web Intelligence: 

  • It is always good to have a handy report template in any BI tools. For Business Objects, that would be the most needed because BO is designed to handle a large set data from very big organizations that have branches all over the world. As the size of the company increases the number of report has to be created will also increase. So it should not create any tedious work for the BO developers, anyway the business need always sticks with some standard report layouts and formats, it is better to create template based on the initial specification that is received from the organization. Now you have a template that is already formatted for page orientations, paper size, numbers, text fields, block layout, headers, footers, execution timestamp, page no and back ground logo or image. So when time comes, it becomes easy as you can save as the template and adding objects for the new report
  • To share it with all the developers, we need to export this template to the repository and assign necessary rights for everyone to access the same.

 

  • While working on a large data avoid using the report view instead use “view structure” mode of display as it is going to eliminate some complexities in data rendering and simply it saves time.

 

  • Use “Save for ALL Users” option to remove the document security from the WEBI files. It can used when we physically transfer the *.wid files from machine to machine, say for example QA to PROD.

Universe Prompts using @prompt function

Let’s play with prompts

In this thread I am going to explain some frequently used prompts which are extensively used for getting input range in reports. The prompts which I am going to illustrate will fall under LOV Categories i.e. there will be manually generated LOV’s to get values from the database. The following examples in this thread are done using Oracle and SQL Server databases and you can change the syntax to appropriate databases like DB2 or any other supported databases.

Everyone who is handling BI will have some ideas in prompts, these are used to get input parameters from the users when the run a report. There are some time range prompts like “Enter Start Date:” and “Enter End Date:” where the users can enter a date value or select a date from calendar control (Web Intelligence) and report will be retrieved for the selection.

If report is in repository with necessary access privileges being given, the users will refresh on their own. By this, the above style of getting the dates from the users is really good but in some cases the reports will be scheduled to different clients in and around the organization, in this particular scenario the prompts should be static i.e. it cannot be changed from time to time. There will be request to from clients that they need report for one month, last month, last week, week before last week, yesterday …etc….

So now the report should work for both manual feed and fixed range. This can be achieved from writing prompt definitions from report or universe.

Following are some of the frequently used time ranges for which I have written some prompt definition,

TIME RANGE LOV

This prompt is written in universe as a where condition, so that whenever it is used in the report , it will act as the where condition of generated query set.

Now the same prompts can be used as manual time feed and as well as the manually generated LOV like “LAST WEEK”. It is pretty self explanatory query which is mentioned below, take a look and you will get the exact idea behind this short prompt definition. For example, the below prompt definition is written for a date field from AdventureWorks database tables, if the user refreshes the report the values of the dates will between Currentdate –7 to Currentdate.

 

   
AdventureWorks.Production.Product.SellStartDate      
between Case @Prompt('Enter start Date:','A',,MONO,FREE)       
when 'LAST WEEK' then getdate()-7       
when 'CURRENT MONTH' then DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)       
when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))       
when 'YESTERDAY' then DATEADD(d,-1,GETDATE())       
end       
and       
Case @Prompt('Enter End Date:','A',,MONO,FREE)       
when 'LAST WEEK' then getdate()       
when 'CURRENT MONTH' then  DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))  
when 'LAST MONTH' then DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))       
when 'YESTERDAY' then Getdate()       
end 

image

Cascading Prompts

Creating Cascading Prompt in Universe Designer

Create the Classes and objects that are needed to be cascaded in the universe. Bring up the objects in a single folder which falls on a natural hierarchy according to your table relations. Like in the example below, i have used some tables in Adventure Works DW2008 sample database (SQL Server 2008).

Tables :

Factsinternetsales

Dimproduct

DimProductSubcategory

DimProductCategory

BO Product Version:

Business Objects XI 3.1 SP3.

Universe Designer.

1

Considering the best practices on creating the universe objects, i have brought all hierarchical objects in single class folder named "Product". To create cascading prompt, go to TOOLS –> Lists of Values –> Create cascading Lists of Values.

2

A new window for creating cascading list of values will open, it will display the list of available objects in the universe,

put the corresponding objects for cascading list of values in hierarchical order.

Category –> Subcategory –> Product

3

after adding up the objects in order for list of values, check the option "Hierarchical View" to get the prompt view like a collapse/expand style of selection in Web Intelligence,  disabling this option will result in showing the prompt values individually,   like it will show prompts for category, subcategory and product at the last. And Both the view will cascade on selected values.

5

Finally, click on Generate LOVs then cascading prompts are created,

6

Save and Export the universe and start using the leaf level object in the prompt to cascade for above two levels,

In this case, use product object in the prompt for web intelligence Reports, then it will show in either of the styles (Hierarchical view option) according to your choice,

Run the query, Answer for Category then answer for the prompt "Sub category" and finally select the product. Cascading prompts are now created.

image_thumb1

a. Hierarchical View

 

image_thumb2

b. Without Hierarchical View

%d bloggers like this: