Business Objects: Universe Designer

Business Object - (BO)

BO stands for business objective and now it is called as BI i.e. business intelligence. It is a front end application that allows users to view, sort and analyze business intelligence data from the database with the help of connection like OLAP or relational connection.

In this blog i will explain on the topics of BO by which you can get a clear concept from designing --> to reporting with the help of tool i.e. WEBI (web intelligence).

so, lets start with the lessons of business objective with me. - 

As a consultant in the domain of SAP BO/BI my roles and responsibilities is to designing of universes and generate report on the basis of that as per my client requirement time to time. I have used variety of designing tools in my career but i found WEBI is the best as it is like a vast ocean where we can perform each and every task of report with a wide range of tools that is available.

As per my knowledge in this domain i have created universe with the help of 2 tools one is UDT (universe design tool) and other is IDT (information design tool). The important thing is that what client say us to do and which tool is to be used and some time it's our choise.

Before i proceed to say you that how i used to work i would like you to define few terms that will come during my explanation process.

UNIVERSE - in terms of BO Universe is a place where data is fetched from the data source and then cleaned and then objects are chosen as per client requirement that is there in the BRD (business requirement document) and then analyzed and then we have to map the objects with the help of joins. on the basis of what objects we have selected in the universe we can be able to design reports on the basis of that.

WEBI - it is a designing tool where we design and form reports for the clients which they view. In WEBI we can filter data that is been coming from universe level, then we can make many sorts of calculations and further we can represent the data in graphical form etc.

So coming back to the tutorial of BO, i would now like to share how i used to work in this domain. I have worked extensively with the tool UDT in my past.

As per my knowledge i have divided the universe design into 2 sections basically, one is basic and another is advance part.

BASIC part - i have included tables, joins, class, sub class, objects and checking of integrity.

ADVANCE part - loops, traps, hierarchies, LOV'S, cascading LOV'S, filters, prompts, RLR & OLR i.e. (row level and object level) restrictions.

So lets start from the basic part first but before that i would like to say you steps for getting started with BO.

1. first of all install the BO software set up in your computer.
2. then run it and  once installed open it.
3. now set up the connections that is relational connections.
4. after that set up select database like e-fashion or club etc.
5. then set the parameters and start with BO with UDT with enterprise authentication.
6. once the UDT get opens we can go for work on it with the database that we have selected.

when the UDT get open up we can see menu bar on the top, below to which we have structure pane and the universe pane side ways. structure pane is on left hand side to universe pane.
as per my requirement i used to first right click on the structure pane and the table browser used to get open after which i used to select the desired tables and then drag them to structure pane one by one and then create join between them or we can say i used to map the tables. After that i used to move to universe pane and create class and under class i used to add some time sub class or directly add the objects. Post addition of objects i used to define the objects into its type such as dimension, measure and detailed objects.
Now let me explain you what is dimension , measure and detail objects -
Dimension objects - objects with one time entry. Ex - customer name, employee name.
Measure object/ fact object - objects that can be calculated. Ex - salary of the employee
Detail objects - these objects provide the description of dimension objects. Ex - customer address, phone number, etc.
after i have created class and objects in universe pane and joining of tables i used to go for integrity check in universe level. This helped me in knowing is there any error that i have committed while mapping of tables with the help of joins. Integrity check is the last thing that is there in my basic part. After this i go for the advance part after which i used to create reports with the help of tool WEBI.
For integrity check i used to go to menu bar that is located on top and then click the option integrity check and it shows the result that is my universe design fine or will i have to check for any sort of errors. Some time it did happened that i got errors and as per me errors are of 2 types basically that occur in universe level i.e. LOOP and TRAPS.

LOOPS - 

Loops are the errors that is visible in the structure pane of universe between the tables. It is the condition that occur when it is an endless path that occur between the tables and its join. So i could define loop as number of tables is equal to number of number of joins.

NUMBER OF TABLES  =  NUMBER OF JOINS ==> LOOP

This will lead to give us the in correct results in the WEBI while we run the reports. I would like to give you an example for this condition which will make you clear.
In the universe structure i have COUNTRY, REGION and CLIENT tables and all of these are been joined to one another so now it has became an endless path and when we run report on basis of these 3 tables it will give error.
I have resolved loop with help of 2 ways i.e. ALIAS & CONTEXT.

ALIAS - in simple i would like to define alias as "xerox copy of main table". It completely eliminates the loop that is present in the universe.

CONDITION - if there are 3 tables in which loop is taking place then one table among them must be a fact table to perform alias.

STEPS - 

1. For performing alias i used to go for checking of cardinality in which tables must be in 1:1 cardinality.
2. After this right click on the table for which alias has to be created and then click on the alias option that is present in the menu bar at top.
3. Finally it will create the "xerox copy"of the table that i have selected.
4. Post to this remove the join from the original table or main table and re connect it to alias table that is been created.
5. Finally click OK button and we could see that loops present has be removed.

EXAMPLE - let's take an example in which we have tables like, country, client, sales, and show room and we will now join them all. After this when we go for checking of integrity then we could be able to see that loop so here country table is the main table and for this table we will only create the alias table and then rejoin and resolve the loop.

By doing this we achieve "performance tuning".

 CONTEXT - 

Apart from alias i used to use one more method to resolve loops and that was Context.
According to me context is the best way to resolve the loop since while we use alias it completely eliminate the loop where as context will give or show an alternative path to resolve the loop.

By doing this we achieve "performance tuning"

CONDITION - the number of paths depends here on number of fact tables and it is
            when the condition is having more then one fact table.

STEPS - 

1. First i used to go for checking of cardinality
2. Then i used to click on automated detection of loop and there it is been written that it can be solved by using of context.
3. Then click on context and it will give the alternate path for the join and resolve the loop.

Sometime it do happen that even after resolving the loops in the Universe, still i used to get wrong result in the reports. I used to be surprised in my initial days that what could be the reason behind this and later after that i came to know it was happening due to "TRAP"

TRAP - 

It is a condition that couldn't be seen in the universe level but do exist in universe level. Like the loops we can't see it in the universe and finally it will give us incorrect result that is Cartesian product in the report.

It is of 2 types - FAN and CHASM

FAN TRAP - according to me fan trap is a condition that occur when we have 1 fact table with 2 dimension table.

or we can say that as one to many to many joins formation occur between tables then fan trap occurs.

RESOLUTION - I have resolved it with the alias of 2nd table and defining the context so that the normal table is joined with the 1st table and the 3rd table. In this 2nd table could only be measure/fact table.

EXAMPLE -

let's again assume 3 tables such as client, sales and sale model and i am creating a join between them
Now lets take client (X) whose sales revenue is $1000. Then when there is a fan trap we could see that X's sales revenue will double up the value to $2000 in the report.

CHASM TRAP -

Chasm trap is the condition that is opposite to fan trap and in this 2 fact tables with 1 dimension table. In other words we can define it as many to one join merge on a table which in return too many join and thus many relationship occur.
It leads to Cartesian product i.e. doubles the value in the reports
I have resolved this with help of 2 ways -

1. With the help of SQL parameter by selecting multiple query for each measure objects. This will generate SQL statement for each measure object and give correct result in report and helped in performance tuning.
2. But some time I had dimension table (client) then I go for next way and in this using of Context and I find this is the best way to resolve chasm trap. This will create 2 separate path for each measure object and resolve the trap and finally helps in performance tuning.

EXAMPLE - 

let’s take 3 tables like client, sales and rental and create the join between the three. Let client (X) having sales revenue be $1000 and rental be $2000. In the case of chasm trap the rental value will be seen as $4000 and sales revenue will be seen $2000 like that of Cartesian product.
After resolving the errors i use to go for the next thing in advance part or i can say performance tuning part i.e.hierarchy

HIERARCHY - 

normally in BO we have a default hierarchy  but we have a provision to create a custom one.
I can define hierarchy as a parent child relationship and in hierarchy objects are arranged from higher granularity to lower granularity or in other words we can define it as collection of dimension objects arrangement to support drill functionalities.

EXAMPLE - 

yearly –> quarterly –> monthly –> weekly –> daily. {Time hierarchy}

Continent -> Country -> State -> Region -> Province.

STEPS for setting up hierarchy is as follows - 

1. When I go for set up of custom hierarchy in universe then I go to menu bar.
2.  Then go the tools – hierarchy- enable custom radio button.
3. Provide the name
4. Then select the desired objects and drag and drop them.
5. Click add
6.  Arrange them in required order.

In the above screen shot it shows how to set HIERARCHIES. 

. Like hierarchy another thing is there that also improves performance tuning and i.e. “CUSTOM LOV”.

CUSTOM LOV-

I define custom LOV as a set of lov associated with a hierarchy in universe and prompts are defined for each level to return lov for level.

STEPS involved in this are as follows - 

1. For setting up the custom lov I go to tools
2. Then create a list of values and then create cascading lov.
3. After that I select custom hierarchy in my project for which I wanted to create cascading lov.
4. Then I double click and selected the objects of higher granularity in the hierarchy.
5. Once added LOV objects into the LOV section each object must have a prompt text.
6. Then I selected the check box “hierarchical view “to get the prompt view like expand or collapse in the WEBI.
7. Then finally it show cascading LOV generation successfully.
8. Finally it also helps in performance tuning.

GLOBAL FILTERS -

This is the last thing that i used to perform in advance part at the Universe level.
Filters generally means to filter something when passed through it. In BO global filter restrict the data to come to the report that we run thus helps in performance tuning.

EXAMPLE - 

Lets us assume that we need a data of a state “Orissa” but there are many other states available in the state object. So with the help of global filter we can pass the data of Orissa only and hence it will take less time and help in performance tuning.

STEPS - 

1. For setting up this global filter I used to click on radio button in universe pane and then the new window get opened.
2. After that I named the condition and gave description. In where clause I wrote SQL command for Orissa”
3. Then again a new window got opened in which I selected Orissa as state object.
4. Then I gave the condition state.
5. State name = "ORISSA”. Then I clicked on parse and done finally OK.
6. In this way I sat up the global filter in universe.

RLR & OLR - (row and object level restriction)

We have a feature in Universe designer that we can restrict the data at row and in the object level since it helps in performance tuning.
Here we can restricts the users from database based on columns known as row level security or restriction. It can be applied in following ways such as in BO supervisor, using database and using designer and we can also use the function @Variable(‘BOUSER’)* in where condition area of a Object Definition
Ex -  Country.CountryId=@Variable(‘BOUSER’)

STEPS  FOR RLR - 

• Universe Designer – Tools – Manage Security –Manage Access Restrictions.
• Manage Access Restrictions window will open.
• Click New in the above screen and it would open new Restriction pop up window which holds Connection, Controls, SQL, Objects, Rows and Table Mapping tabs and by default it will point to Row Access tab.
• Add Restriction Name – “Restrict Country Singapore”.
• Click Add and will Open to Select the Table and WHERE Clause conditions.
• Select the added restrictions and Click Check All option to validate.
• Click ok.
• Click the “Add User or Group” and you can see list of available users and Groups list.
• Click the users / group from left side and add it into right side panel and OK.
• Select available restrictions and available users and groups and Apply.
• For the first resections and groups, the priority will set to one and if you are adding more restrictions and group, you have set the priority by selecting the Priority Options. There won’t be any priority settings for individual users.
• Click OK. Now Row select Security applied for the CS Users group and they cannot see Canada Country data into their reports. You can validate the same by checking the SQL query in the report Query panel.

NOTE -  @Variable(‘BOUSER’) : It returns InfoView user name who is running the query.

OLR - it is the features which help us to hide some of the objects to particular end users. we do apply a access level  for each object within a universe. Only groups or users which have access level equal to to higher then that level can only have the access to the particular object.
We can assign access levels as - public, controlled, restricted, confidential and private.
These are just a place holder for the developer perspective and the values were stored in from zero (0) to four (4) in repository. As long as a user had a security level that was greater than or equal to an object security level, that user could see that object.
Once all your objects security has been set in universe designer, then you need to create groups (controlled, restricted, confidential, and private) in CMC and have to add the users to the respective groups and assign the access level.
0 – PRIVATE (Only users with the user profile of Private can see and use the object)
1- CONFIDENTIAL (Only users with the user profile of Confidential or higher can see and use the object)
 2- RESTRICTED (Only users with the user profile of Restricted or higher can see and use the object)
 3 – CONTROLLED (Only users with the user profile of Controlled or higher can see and use the object)
4 – PUBLIC (All users can see and use the objects)