Business Objects – Best Practice – Universe Design
The purpose of this document is to provide a description of what I consider to be best practice when designing a Business Objects Universe. We will also describe the reasoning behind the suggestion.
This section will describe any items around the connection between Business Objects and the underlying database that holds the data.
Always use the vendorâs native connectivity where possible. Avoid using generic drivers, e.g. ODBC for Oracle. You always get better performance and more parameters when using the vendors own drivers.
When accessing Microsoft SQL Server databases use an OLDDB connection rather than an ODBC connection. The connection is faster. OLEDB connections store their parameters in the connection definition itâs self whereas an ODBC connection refers to a predefined ODBC connection name which must be defined on every PC and server.
Keep Connections Open
It is possible to define if the database connection is kept open permanently, closed after a specified period of inactivity or closed after each query. The process of creating a database connection is quite âexpensiveâ? time wise so it is better to keep connections open for longer. Open connections consume resources on the database server so it is better to keep the number of connections to a minimum.
Generally, connections to the Business Objects repository database should be kept open permanently. Connections to data source databases should generally time out but this is dependant on the typical users usage profile.
Tuning Network Connections
It is possible to fine tune the way that Business Objects uses the network by changing network packet sizes etc. This can make a large difference to problem reports that return a large volume of data.
2 Physical Structure
This section will describe any practices that should be adhered to when implementing the physical data structures into the Business Objects universe.
Alias All Tables
When importing the database tables, immediately create an aliased version. The aliased versions should be the ones that are joined and are used to build objects from. This is done purely to make the generated SQL and the universe itâs self easier to read.
Join All Tables
Every table that will be being used to supply information should be joined to at least one other table. It should not be possible to create a Cartesian product
Ensure that all joins that are implemented in the universe have a corresponding index on the physical table. Queries that join tables together will almost always run faster when the joining fields are indexed.
There is the facility to get designer to count the number of rows in each table. This should always be done and the results displayed in the universe. There are two reasons for this: if data is missing from a particular table it is easier to spot, if you are using Designers built in validation tools it needs the row counts to check the cardinality of the joins.
3 Business Structure
This section will describe any practices that should be adhered to when implementing the logical classes and objectsinto the Business Objects universe.
Each class should be uniquely named and have an appropriate business description.
Every object should be uniquely named and have an appropriate business description. If you find you have two versions of the âsameâ? object, for example âYearâ? you should find out when you would use each and then name them appropriately.
You should implement as many condition objects as you can. It should be the exception that users have to manually build a condition. Conditions should come in two flavours. Interactive filters will use the prompt feature to as the user to enter information, for example âWhich years?â?. Static filters will apply a restriction without any visible interaction, for example âThis Year Onlyâ?.
If you intend to schedule reports then you should ensure that any conditions you do use are not interactive. For example; if you were to add the condition âYear=2007â? or âWhich Years?â?, the value that is hard coded or the value that is supplied at schedule time would be used for every subsequent run, even when the calendar year does change.
If you find you need to define the same object in multiple places or you have an object that extends the definition of an existing object, you should not redefine the logic of the core object every time it is used. For example MyCustomers = case ownerID when 1234 then customerID end. If I then wanted a MyGoodCustomers object I should not redefine âMy Customersâ? but reuse the definition already in place. This is done via the @Select syntax. MyGoodCustomers=@Select(MyClass,MyCustomers) and
In reality, I would store the value of 1000000 used above in an object again so that there is a single place where this is defined.
If you are using core definition objects, you may end up with objects that simply hold definitions but should not be used in their own right. These objects can be hidden. Hidden objects are still accessible to @Select calls but do not appear in the object list for users to select them.
Every object has the ability to have a where clause added to it. This should never be used. If you add where clauses to objects then when you select multiple measures, these where clauses are combined. For example Sales2006 = select sum(sales) from sales where year=2006. Sales2007 = select sum(sales) from sales where year=2007. If I were to use both measures in the same query then the result would be: select sum(sales), sum(sales) from sales where year=2006 and year=2007. It is not possible for a single row to belong to both years so no data would be returned.
The âcorrectâ? way to implement this object would be Sales2007=select sum(case year when 2007 then sales else null) from sales.
4 Linked Universes
Linked universes are a great way to maintain different âversionsâ? of the âsameâ? universe. For example you may wish to have an exact copy of your live universe but point it at your training database. You will not want to maintain your training universe every time your production universe changes. Using the live universe as a kernel of the training universe and only changing the universe connection will allow this to happen automatically.
Universe parameters are not automatically replicated between linked universes.
When you create a linked universe the contexts contained in the core universe are not propagated into the linked universe. If you wish to have an âexactâ? copy then you must re-implement the contexts and also maintain them on an ongoing basis.
This section will describe any parameters that can be applied to the universe that will have an affect.
This is an example where you may wish to have multiple versions of a universe, one for scheduling with no limits in place and one for novice users with very tight limits.
Limit Size Of Result Set
This option allows you to restrict the number of rows that a single query that a user runs will return. This is one of the features that will prevent ill thought out queries from saturating the network. The value used will be dependant on the number of rows that a âtypicalâ? report returns.
Limit Execution Time
This option allows you to set the maximum time in seconds that a users query will run for. This is one of the options that will prevent long running queries from consuming resources on both your database server and your Business intelligence server. The value of this will be dependant on the âtypicalâ? report execution time.
Warn If Cost Estimate Exceeds
When a user submits a query and this option is enabled and the underlying database supports the feature then you can warn users if the expected duration of their query exceeds a particular value. The value of this will be dependant on the âtypicalâ? report execution time.
Limit Size Of Long Objects
Databases have the option of holding large volumes of data in a single field. Each âlongâ? field in each row can contain a maximum of 4GB of data. If all of this data was returned then this could easily saturate the network and the memory of the local PC. We would recommend that this value is kept quite low to limit the volume of data returned by this field. We would also question as to why long fields are included in the universe in the first place.
Allow Use Of Sub Queries
Sub queries are a way of restricting queries based on the results of others. This is not something that novice users would be using but it is an easy way to implement complex selection criteria.
Allow Use Of Union, Intersect and Minus Queries
These set based operators are easy ways to compare different result sets. We would recommend that this is enabled as other ways to answer the same questions are very complex.
Allow Complex Operands in Query Panel
Selecting this option gives you access to the full range of operands in the query panel. This should normally always be selected unless the universe is targeted at novice users.
Multiple SQL Statements for each Context
If you write a query that requires to access multiple contexts, this parameter specifies that a separate SQL statement should be used for each context. This should normally be enabled if your universe contains contexts.
Multiple SQL Statements for each Measure
When using a relational data source certain structural conditions can exist; chasm and fan traps. One way to resolve these is to tell Business Objects to write a separate SQL statement for each table that is providing a measure. This should normally be selected when using relational data sources.
Allow Selection of Multiple Contexts
Contexts are one way to prevent loops, chasm traps and fan traps. If this is the reason that you have created a context then this option should not be selected.
Cartesian Products (Warn / Prevent)
A Cartesian Product is when no join has been defined between two tables. In this circumstance every row of the first table will be joined to every row of the second table. The setting of this parameter is not relevant. Every table that is defined in the universe should be joined to at least one other.
Core Order Priority
If you are using linked universes then this should be set to YES in the derived universes. This forces the order of the classes and objects to match the order as they are defined in the kernel universe.
Depending on the nature of the data you may require to restrict access to the information held in the universe. There are two main methods of implementing this.
This is the most secure method to implement security. Each Business Objects user requires a corresponding database login with matching usernames and passwords. The universe connection is then defined so that it uses the users credentials. The definition of the scope of the data that the user has access to is defined in the source database.
This is the preferred solution as it is a more generic solution. This means that even if the user chooses to use a differing query tool, the security cannot be bypassed.
This method involves using a generic logon to the database. The definition of the security roles are stored wither in the universe itâs self via object definitions or in database tables, which are included in the universe. This solution is good as it means that control of the security definition is either data driven or in the control of the Business objects universe designer. Using the previous method, the control of the security is the remit of the DBA. The disadvantage of this method is that if any other query tool is used then the data becomes unsecured. There si also the risk that the user will have the ability to rewrite the SQL that Business Objects produces prior to running the query, allowing them to remove or change the security implementation.
These are what I would consider to be the current best practices when designing business intelligence universes. As is always the case there are times when many of these can and should be ignored. This list should not be used dogmatically but pragmatically. If you are having any particular problems then a member of our Service Delivery team would be happy to work with you to resolve them.
19 replies on “Business Objects – Best Practice – Universe Design”
Really good to know and brush up my knowledge.Moreover i also strongly recommend to use OLEDB Connection instead of ODBC.
What would you recommend and why for conditions, which are static filter should be created within universe or within report?
What is the purpose of the filter?
If it “must always” be there, define it in the universe as a self restricting join on the table it’s self.
If it “should mostly” be there, define it in the universe as a self restricting join on the table it’s self but with a prompt to allow it to be disabled.
If it should be used “sometimes” then it should be a pre-defined universe filter object and the user should select, on a report by report basis, if it should be used.
If it only makes sense on one report then the condition should be built on the report using the existing objects and manually defining the condition.
I am talking about last two scenario, which are more relevant to me at the moment.
So, Let me get it clear, you are recommending that if pre-defined condition is used by one report then it should be built on the report manually using universe object and if pre-defined condition is used by multiple reports then it should be defined in universe as universe filter.
Would you consider defining all pre-defined condition objects in universe rather than building it up in the report? If yes, then what are the benefits?
I would always try and push as much of the build work into the universe as possible.
The reason being, if the definition of your condition changes, you update the definition of the filter once in the universe rather than you having to:
1) Find all the affected reports.
2) Change and test all the affected reports.
Do you see any issues with building a new universe using a connection to a live data source? Apart from the possibility of test queries using up database server resources I don’t see any particular problem. The universe would be hidden from all users except me.
There are two issues to be concerned with:
The first you have correctly identified, unless you are careful with your universe resource limit settings then your end users “could” adversly affect the performance of your live system. Remember it’s not just the database resources – think network bandwidth and also queries paging “current” data out of memory by reporting on “historic” data.
The second is concerning reproducable results. If you are accessing a “live” system then everyone must understand that the results on their report are “live” and they should not expect two copies, even if they were refreshed only seconds apart, to reconcile.
thanks this is very useful for interviews
Can you explain where do we mention the maximum number of DB connections in BO.
Also, does each request by the same user make a separate DB call.
I’m not aware of a location in Business Objects where you can set a maximum number of connections. Depending on what database you are using this “may” be able to be set there.
“does each request by a user will make a separate database call”. If by “request” you mean report refresh then yes but a Business Objects “request” for example to sort or filter the data could work on a local dataset and not need to access the database.
created a conditional measures using single measure at universe level and used aggregate aware on same measure.
When I am creating a report I need to take some of the conditional measures in single query then when i am doing same thing what ever conditions applied on measures it is applying on whole query but i need those conditions on related condition measure only not on whole report please let me know if you have any solution.
I assume that you have created your measure and made it conditional by adding a where clause.
The contents of the where clause of an object will be applied to the overall where clause of the containing select statement.
If you want the restriction to apply to just the object then you have to embed the restriction into the “select” portion of the object.
For example, if you wanted to count open accounts but allow the query to also contain closed objects then you could define your object as:
count(distinct case status when “open” then account_number else null)
If the account status is open the account number will be counted if it is not open then the null will not be counted.
NOTE: The query will be able to count closed accounts as well – we have to restricted the contents of the query.
Hope that helps.
If you got this question in an interview as beginner, what will be your best answer:
How can you tell a universe is badly design by just looking at it?
Good question, and not one that’s easy to answer.
If it looks overly complex then it probably is.
That said the universe has to cater for complex data models and complex user requirements.
“an OLDDB connection rather than an ODBC connection” -> OLEDB
” it’s self” -> “itself”
“feature to as the user” -> “to ASK the user”?
” so that it uses the users credentials” -> “user’s credentials”
“There si also the risk” -> “There is..”
Thanks for your comments RE the typos.
Unfortunately I’m not able to edit this post at the moment (wordpress bug affecting only some of my posts).
Once I have this resolved I’ll come back and implement your suggested changes.
is there way in Business Object universe to limit the number of concurrent report that can be run against?
i.e I would like to restrict maximum 5 reports can be run at the same time. anything above should be queued
can you please help me?
This is not done at the universe level but done in the server configuration.