Wednesday, 7 November 2012

Scope of Analysis

Seems very small topic, but I have seen people with different understanding about its functionality in Business Objects. 
So let’s see what exactly it is.
Let’s say I have a hierarchy in universe YearàQuarteràMonthàWeek.

So it means, in the report when I am drilling at these levels it will go through the above hierarchy.

What this feature “Scope of Analysis “actually does?
This is an option in web intelligence query panel.

It allows to set the scope at query time.
For eg. If I selected Year and Revenue in my query and the sql generated here is:

Which is correct.
I ran the report and the results are:

Now I will enable drill option and will try to drill through the hierarchy.
From YearàQuarter
See the results below.

Webi brought quarter in the query.
Same way as you drill down to month and week, it will bring in run time other columns as needed by the drill(it will hit the database to bring the columns data)
Now I will use Scope of analysis and see how it behaves.
Note: Scope of analysis by default is set to none.
I will enable scope of analysis and from drop down I will select  “One level”.

You will notice that it brought Quarter object in scope of analysis and in query, quarter column which is one level after Year but there is no change in result objects pane, there it is still year and revenue.
I will run the report:

Note: Even though we have not selected Quarter in the select, it is there in query and in left pane (data pane).
This means by selecting scope of analysis , we have brought the quarter also, when we ran the query for  year and revenue. 
Even if we drill to quarter from Year now, we don’t have to hit the database to bring quarter results. 
But now if I try to drill further to Month from quarter, query will hit the database to bring month data also. Basically this helps in avoiding database hit each time you are drilling for analysis.
If you already set scope of analysis to Custom, it will bring all the objects in the query for that hierarchy and at time of drilling it won’t hit the database to fetch detail data, as it is already in webi microcube. I have tested the same using SQL profiler.(you can check here when a query hits the database in sql server)
Summary is:
Setting scope of analysis brings objects in the query for the levels you have specified and it wont hit the database while drilling for those levels.
So if you are aware of the users request of a drill you can set this option in the report you have created, so it should not have a time lag at time of drilling.

One important thing to note is in Desktop intelligence reports, it allows drill down till the level you have specified in scope of analysis, for the rest level to drill , you have to right click on the column and use drill by.
But this is not the case with Web Intelligence reports.

There is a Web Intelligence security rights for scope of analysis.
This is in CMCà ApplicationàWebIntelligence
“Extend Scope of Analysis”

What is this and how it works?
Lets see an example:
I am a report designer and create a report with Year and Revenue and I set the “Scope of Analysis” to 1 level.
As such when I run this report , I can drill from YearàQuarter and can also drill to other levels(like month, week)  but than for other levels it will hit the database.
Let’s say I have a user ‘X’, I gave this report to him with “Scope of analysis” set to one level.
Assume he has the right to extend scope of analysis, he will be able to access the report same as I did, i.e. can drill to all levels.
But now, if I go to CMC and deny the right “extend scope of analysis”.
This will restrict the user to drill only till quarter and no further levels as, you have set in your report.
So by this you are actually allowing user to access only the levels you want him to drill down.

Request the reader's to share there views about this topic.

Monday, 22 October 2012

Aggregate Awareness in Universe

Lets see what is this aggregate awareness functionality and how it is useful in universe.
Lets say I have two tables customer and daily fact  with below data:

If I create the report of customers daily sales, it will join customer and daily fact table using cid column and will give me 24 rows of data.
But if my requirement is to create only quarterly sales or just monthly sales than the data will still traverse through 24 rows of daily fact table.
For eg.  Monthly sales will give me 12 rows of data while quarterly will be 4 rows of data. But this is still getting data from detail fact which has 24 rows, which if hold million facts can slow down the performance.
So we create materialised views or summary tables in our database for better performance.
And I created monthly and quarterly fact in my sample database. Below are the aggregated tables that I can use in my universe.

Now, the question is, we have aggregate tables but how to use the same in my universe based on user queries dynamically.
Aggregate awareness functionality will help me in achieving the same.
It is a two step process:
1    Use aggregate aware function in objects that needs to be made aggregate aware.
      Setting incompatibilities of objects with tables in aggregate navigation section.

Below is the structure of my universe now:

Now, I have made time dimensions i.e. Sales Dt, Sales Month and Sales Qtr and measue Sales as aggregate aware.
Aggregate function has syntax like:

@Aggregate_Aware(sum(aggregate table1),...,sum(aggregate tableN)) Defines a measure object using precalculated aggregate tables.

 Where table1 is highly aggregated(in our case quarterly fact),........and tableN is the least aggregate or    detailed one(in our case daily fact)

If you observe the definition in the select statement of Sales measure here, it is from highly aggregated table to detail one , same is done for the dimension table as well.

Now month is present in only two tables, hence only monthly and daily fact is used.

Sales Qtr object, quarter is present in all three fact tables; hence all the three are used.

We have used aggregate function in these tables, so we hope that system automatically will decide which table to refer dynamically, when a user selects or tries to run a query.
That means if user wants daily data than daily fact should be used, if monthly than monthly fact or if quarterly sales than quarterly fact.

Now, we have to set the incompatibilities.
This is done from Aggregate Navigation section under tools, in menu bar.
We have to make objects incompatible to tables.
For eg. Sales dt is incompatible to quarter and months table, similarly Sales month object is incompatible to quarter table.
You have to click the table and check the box in front of object to make it incompatible with the selected table.

Lets see how the queries behave, when you select different set of objects:
Scenario 1:
I selected customer name and sales, and if you see the below query , it is behaving same as the one i expect it, as it is taking data from quarterly fact table.

Scenario 2:  I added sales month object to it, and will remove quarter object.(it should only now point to monthly facts)

And if you observe, it is now using monthly facts only.

Scenario3:  Using sales dt object.

This also worked correctly.
Note: if aggregate navigation have not been used than the measure sales will always point to quarterly fact, even if you use sales dt object in your query, which is wrong behavior.
Also it is required to link aggregate fact to dimension tables, else it would fetch Cartesian product for table which are not joined. In efashion universe the aggregate table are not joined to other because the table itself has all the dimension objects, which is not always the case. In real time aggregate fact will hold aggregate measures and foreign keys of dimension table only, that is why it has to be joined to dimension tables.

Hope this article helps in better understanding of this feature.....
Forgive me for the typo's........

Wednesday, 20 June 2012

Merge Dimensions in Webi

Merge Dimension
This is a feature in Web Intelligence which allows linking of two data providers(or 2 queries).
Basically we merge objects from two or more queries in one webi report, so that we can use them in same block.
Mandatory condition to merge objects is to have same data type.
For eg.  See below tables , coming from two different queries in webi report.
Query 1

Query 2

Now if you  try to use all these objects in same block , you will get data sync error.(As webi will not be able to find any relation between these queries)
I will now  merge  empid and cust id.

Merge Id is showing all rows coming from emp id and cust id and their corresponding revenue and salary column values.
This is now showing results as Full outer join.
But if I involve any one non-merged dimension in this block, the results will start showing dimension values pertaining to that query only.

So basically it is working as Left or Right Outer join when we try to use any dimension in the block which is not merged.
But we will not be able to use both emp name and cust name in same block as these are not merged.
We will get data sync error if we try to use them in same block.
Similarly if you don’t use merge id column and use emp id or cust id along with revenue and salary, that will work as Left or Right outer join and not as Full outer join.

So this explains when merge dimension works as Full outer join and when it works as left or Right outer join.
There is also one option which makes the functionality of left or right outer join to work as full outer join. i.e. Extended merge dimension. (note:- but this works only when unmerged dimension is not selected in the block)
See for below example.
When I used cust id revenue and salary, the block look like this:

So its showing data as left outer join .
Now I will check the option”Extended Merge Dimension” from document properties.

Now it will work as full outer join as shown in screenshot:

So this option makes the query to give full outer join result set even if you have selected specific and not merged object in the block.
Note: this option wont work if you use non-merged dimension in  the block. For eg. If you use cust name in the above block it wont work as full outer join.

Tuesday, 20 March 2012

Advanced Join Property

Advance join property in universe is present , when you click a join between any two tables and click on Advance button.
You need to have ANSI92, set to “Yes”, to enable this feature.
So, the question is, what exactly this feature do?
First of all let’s look what is the execution order of  a SQL statement:
When a query is submitted to the database, it is executed in the following order:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
So if I use my conditions in FROM Clause instead of WHERE Clause, it will be calculated first and can help in performance improvement.
Take for example in my universe:

If I select c_name object from customer table and sale_value from sales table and use c_name as a filter, the query will be: 

It can be seen that the filter condition is coming in WHERE clause.

The Advanced Join Property allows to the conditions to be used in “FROM” clause.
Again as I said previously, you need to have ANSI92=”Yes”  in your universe parameters for this to work.

Now we will set the advanced join conditions.
Click on the join between the two tables:

The drop down option says it what kind of behavior you want from objects selected in query filter.
Lets say I selected “Selected objects in FROM” in drop down and checked on C Name dimension object.

Now I will create the query again:

See how the filter which was coming in WHERE clause is coming in FROM clause.
I went through number of forums that tells how conditions in FROM clause helps in query performance compared to WHERE clause.
Although both types (i.e. filters in FROM clause and filters in where clause) generate the same results but there are scenarios specially when you use Left Outer joins in your query, that can lead to difference in results, which needs to be taken into consideration while using this option in your universe.

Monday, 19 March 2012

Fan Trap

Fan Trap in Business Objects
This is also a join path problem created in universe when below scenarios are met:
Consider table A, B and C.
  • A   --<   B   --<   C   i.e.  A to B is “One to Many” and B to C is “One to Many”   AND
  • Measures selected from Table B and any kind of object from Table C  AND
  • Table B holds aggregated values from Table C.
This trap can also be created when we are having only two tables say B and C with “One to Many” relationship, where we are using dimension and measure(which is aggregation of a column in C) and an object from C table.
The results will be inflated like Cartesian product.
For eg.
I have three tables

These tables are connected in below manner: 

The relationship is something like this:
A customer say ‘Gaurav’ purchased a car and accessories. He paid total amount(car cost + accessories)=4000.
Now table Sales contain 4000 as the amount and sales detail captures the breakup of that amount i.e.(car=3000 and accessories = 1000). So Sales tables hold aggregated value from sales_detail table.
Classes and objects:

Now I will try to create a webi report using  objects:
Query 1

Id Sales
C Name
Sale Value
Correct result
Id Sales
C Name
Sale Value
Item Value
Incorrect result

Adding another object i.e. Item type from table C. 
C Name
Id Sales
Item Type
Item Value
Sale Value
Observe that when we added Item Type to get the detail , item value is properly displayed but sale value still shows two rows with same value and hence when we remove the item_type, it gets aggregated and show 8000, which is a wrong value.
How to resolve this:
Again like Chasm trap you have to create two separate queries to display correct result.
  1. Create two sql statements by checking on the option of “Multiple sql statements for each measure”.
  2. Create alias and context.
  3. Use aggregate awareness to avoid this scenario.

  1. Setting the option in universe parameter:

This will generate 2 sql statements, if measures are selected from 2nd and 3rd table. 

C Name
Item Value
Sale Value

This gives correct results. But will not work in case you are selecting only dimension objects and not measure.
  1. Using Aliases and Context.
We will now try to solve the trap using combination of aliases and context.
Create an alias of middle table , in our eg. It is Sales table.

Join Customer with alias table and set cardinalities of the same.
Now we will create two context lets say C1 and C2 like below.

Now we have to take the measure object from sales table i.e. sale_value from the aliased table rather than sales, so repoint the sales value object to point to sale_value column of sales2 table. 

Also check the below option in universe parameters: 

Now, if you use the measure objects from two different context in one query, it will generate multiple sql statements and the results will be correct. 
C Name
Sale Value
Item Value

Note:  There is another scenario also where this kind of trap can be created.
Lets say for example, in our structure of universe:

Lets say I ignore Customer table. 

Now If have objects like C_id(dimension Object) , Sale_value(measure) from sales table and item_value from sales_detail table in webi rich client query, it will again create a trap and will show inflated results.

C Id
Sale Value
Item Value

So it’s showing inflated results again i.e. wrong value of sale value.
To resolve this, we will create alias of sales table, same as above case.

Created two contexts C1 and C2.
Checked the option of ‘Multiple SQL for each contexts’ in universe parameters.
Change the sql of sale value object to point to sale_value column of alias table i.e. sales2.
Now, if you select the objects c_id, sale_value and item_value. It will display correct result.