Business Objects: Web Intelligence

WEBI - (WEB INTELLIGENCE)

It is a designing tool and hence we generate BO reports through it. I have used this tool till date and part from it i have rarely gone for Lumira.
So before starting this i would like to inform you that the topics that i am going to say you in this which are listed as follows -

1. FILTERS
2. SUB QUERY
3. INPUT CONTROLS
4. CONDITIONAL FORMATTING
5. SORTS
6. BREAKS AND SECTIONS
7. CHARTS IN WEBI
8. HYPERLINK
9. MERGING
10. RANKING
11. TRACKING
12. SCOPE OF ANALYSIS
13. COMBINED QUERY

Above mentioned things are important as per me and one who design reports with this tool he or she will come across often these things time to time.

SO LETS BEGIN -

Filters -

these are the option available in business intelligence to filter a data or we can say refine a data at report level i.e. in web intelligence a.k.a WEBI.
Following are the type of filters that I have known and used so far in my career in my previous projects.

1. Predefined filters-

These are the filters which are created during the time of universe creation and directly be used in report level. The main benefits of these filters is that once it is been created at the universe level after that there is no need for creating custom filters furthermore at report level at WEBI. To add a predefined filter one can drag or double click the filter to query panel – then filter pane. After that when we run the query corresponding to the data with respect to filters will be added to the report.

2. Custom filters –

These are the filters that are created at the report level. There is 2 ways by which I could create them one is before running the query and another is after running the query.
The main purpose of using the filter is for hiding the data from the specific user.
To create a custom query filter add the object you want to use in the filter then drag it query filter pane and then click the arrow next to default operator and finally select the operator such as in list, not in list, equal to, not equal to, greater then, greater then equal to, less then etc.…
Then click the arrow on filter type and now we can select the following filter types which are listed such as constant, value from the list, prompt, object from the query.
Then select the value that you wish to include in the filter. And for removing the filter select the filter and press the delete button and further more if you like to remove all the filters then click remove all option that is present at top right corner of the screen.

3.Quick filters –

The main objective behind using this filter is that to retrieve the values quickly you want to have and in this way there is no need of using the filter editor.
For this select add quick filter which is present at top right corner of result object pane.
Then a new dialog box will get open and now we have to finally select the list of values i.e. LOV that we like to use in the quick filter.

Example-

there are several years in a report such as from 2000-2017 and if we like to get only the years 20016-20017 then we can use it by selecting these both years.
And to delete this filter select the filter in query filter pane and press delete button.

4.Prompts –

I can define as a special filter for a user in business intelligence which allows the user to enter a value every time data is refreshed in the document. This filter helps in performance tuning.
Following are the elements of prompt which are an object, an operator and message.
Ex – suppose we are the taking the current year i.e.2017 so in this we can say current year equal to (“2017”).

So in this                            current year ------------- an object

                                           Equal to – -------------an operator  

                                          Prompt message is------- (“2017”)

A prompt can be applied to dimension, measure, attributes, hierarchies etc.
More over to it we can also do use of and”, ör”operators here to create multiple prompts in the same query.
Note – “while suing IDT AND Bex query we can only use “AND”OPERATOR WITH THE PROMPTS.”

Steps involved in creating a prompt are as follows –

1. To create a prompt add the object that you like to filter with a prompt to query filter pane.
2. Select the filter operator from the list and click on the last arrow mark to select a prompt
3. Then we can add the text message for prompt value and run the query.
4. While we run the query prompt dialog box will allow us to enter the values as per the selected operator.
5. When the values are selected just click ok and data for selected values in the document will be reflected in the report.
6. When we refresh the document in WEBI during that time prompt will appear every time to the select the values.
7. There are conditions when we have prompt already applied or we can say that existing prompts in a report then in that scenario
8. Drag the object on which prompt has to be applied to query filter pane.
9. Then select from universe by selecting the existing prompt and press ok button. It will then display the list of all the prompts which are compactable with the objects in the query filter.

There are few types of prompt which are –

 Merged prompts –

when we are using multiple data provider in that time we have to use merge option to get the data into one report. So in this type of scenario we can use merge multiple prompts with same data type , same operator and same prompt text are been merged. So while we will refresh the data providers then one prompt will only appear for all the data providers.

Hierarchical prompts –

we can create this where the objects like hierarchy,   levels, and dimension associated with hierarchical LOV are been displayed.
It is displayed in a tree form and we can navigate up and down in the tree. As per filters in the prompts we can select items from different level of LOV.
In my last project I have even combined prompts with the query filters which have helped me to limit the data in the document and helped me in performance tuning.

Example

I had added query filter for department and the year and the prompt for a specific employee name.

State not equal to Orissa

Year 2017

Which category

So now when we run the query it will ask for enter the value of category so in webi it will filter the data as per filter in the query panel and display the result as per prompt value.
One if like to define order of prompt then we have to go to query properties at the top and from there we can select the order of prompt as per our need.

SUB QUERY –

it helps in filtering of the data as compared to other query filters. Using this we can limit the values returned with WHERE clause. These sub queries work on basis of SQL which is used to retrieve the query data. If the database doesn’t support SQL then we can’t able to see the option of sub query at query panel It only be made on dimensions and not on measure objects or attributes.

Steps for making sub query

1.Select an object in the result pane on which we like to make a sub query.
2. Then it will add sub query outline in the query filter pane. Now if we like to add a WHERE condition then drag and drop the objects to the area of sub query.
3. Then after this select the operator value used to filter object in WHERE condition. We can add multiple queries to query panel. We can also use her and, or operator relationship between sub queries.
4. By default we have 2 sub queries always linked with the AND relationship. For this drag a sub query to the area and drop an object.

Input controls –

Input controls are there to filter and analyze the report data. I have defined input controls using text boxes and radio buttons. These are associated with report elements like tables and section headers and use control to apply filter on report elements.
When we select a value for input control it will filter the values in the report element that is associated with the input control by selected values.
Input controls can also be used on hierarchical data.
For using this one must be in design mode of WEBI.

Go to analysis – filters – input control – define control.

It will then open define input control dialog and select the report object used to filter data and click “next”.

Example –

suppose if we have selected a Report element and then we can select Define Control, we will get an option to include objects from selected block only. If we select this checkbox, it will show us objects only from the selected report element.
Then after this we will get a option choose and define control. Choose control allows us to select different control types like single value or multiple values. Define control allows us to select the name of control, description, values in control to select, operator and number of lines displayed in input control, etc.
In the next window, you have to assign Report Elements. It will allow you to choose from report elements. You can assign report elements to input controls.
Click ‘Finish’ and input controls will be added to the left pane under Input Control tab.
If further if we like to edit the input control then
To edit an input control → Select Input control from the left pane → Click Edit icon.
It will open Edit Input Control dialog box. Make changes to Input Control and click Ok.

Steps to define tables and charts as input control

Select the table → Right-click and select linking → Add Element Linking.
You can select a single object or all objects to define as filtering objects. Click ‘Next’.
You can enter the name and description of input control. Click ‘Next’. Once you click next, you can select any other block to use this object as input control. Click ‘Finish’.

Conditional formatting – (alerters)

Conditional Formatting is used to highlight some specific values in the report. If you want you can show specific low or high values with some specific colors. Conditional formatting can be applied to the following elements −

• Rows in horizon tables

• Columns in vertical tables

• Cells in forms and cross-tables

• Sections

• Free holding cells

• Condition formatting can be used to make the following formatting changes − Text color, size and style, cell border, color, size and style, and cell backgrounds.

• You can add up to 30 conditional formatting formulas in a Webi document.

  

Steps –

1. First open the report in design mode and we must use rich client as it will not work in web mode.

2. Go to Analysis → Conditional → New Rule. It will open formatting rule editor.
3. Enter the rule name and description. Select the cell contents in filtered object or cell box.
4. We can select Operator and Operands value. You have to define text formatting in Format tab. Click on Format to set the formatting of the tab.
5. We can select the font, font style, size, effects (underline, etc.) and alignment. Once formatting is defined for condition, you have to click Ok.
6. Now to apply conditional formatting to the object, select the column in the report. Go to formatting rules drop down → Select the conditional formatting rules checkbox you have created.
7. Conditional formatting will be applied to the desired cells. We can add multiple conditions in a single formatting rule on multiple objects.

Sorts –

It can be applied on tables on section. And in charts to get organize the data in specific order in a WEBI report.
By default it is set in order i.e. left to right order of objects of query panel.

Example -

Ascending numeric order for numeric data
Alphabetical order for alphanumeric data
Ascending − in this, smallest value at the top to highest value at the bottom.
Descending − in this, highest value at the top to smallest value at the bottom
Apart from default way of sorting we can even customize it as per our wish. Custom order of sorting is available on dimension and attributes and not at hierarchy and measures.

Steps –

1. To insert a sort, open report in Design mode. Select the Column you want to sort.

2. Then Go to Analysis → Display → Sort → Ascending/Descending.
3. You can apply multiple sorts in a single table on different columns. You can use ‘Remove All Sorts’ option to delete all the sorts.
4. To manage the Order of Sorts, go to Advance tab. You can define order for all sorts using up and down arrows. You can define Custom Sorts here if no other sort is applied.

Example –

suppose we have sales report of XYZ of ABC state and what all its raw material which is used in its company such as manganese, cobalt, zinc, aluminum etc.
So while we apply sort we have 2 options i.e. ascending or descending order and we can choose any one order and it will arrange them in required order of alphabets.

Breaks  & Sections–

• Breaks are used to define all data for every unique value of an object in separate parts. Using Breaks you can apply – Subtotals and Sub aggregations and Display data in more effective manner.

Break vs Sections –

Section breaks up the data into multiple cells that are called section headers and each section header contains a value for dimension and data corresponding to dimension value.
Break is used to divide the data into one block and each column carries dimension, attributes and measures. These values are repeated for each row value in the block.

Steps to insert breaks –

1. First select the column in which you want to insert a Break. Go to Analysis → Display → Break → Add Break.

2. This will divide the table into many mini tables as there are unique values in the column.
3. If we like to manage breaks that we have applied then steps for it are as follows –
4. Go to Analysis → Display → Break → Manage Break. It will open a new window and show all the breaks in the table.
In WEBI we can even filter of data and by doing so we can limit the data displayed in a WEBI document. We must have to mention elements such as filtered objects, operators, filter value, to create report filter.

The basic difference between query and report filter is that query filters are defined at query panel and these are used because of limiting the data retrieved from different data source and get back to WEBI report. Whereas report filters are the filters which are used for hiding the data in a table, report, chart, section present within a WEBI report.

We have various operators which are been used in a WEBI report such as equal to, not equal to, greater then, lesser then, between, in list, not in list, is null, is not null etc.

Steps in applying a filter are as follows –

1. First Select the Report Element you want to apply a filter. Go to Filter → Add Filter.

2. It will open the Report Filter dialog box. You can add objects, operand and value to apply filter at the report level.
3. Using the functions − Add Filter, Remove or Remove All − you can add or delete filters accordingly.
4. To Edit a Filter → Go to Analysis Tab → Filter → Edit Filter.
5. You can make changes to filters in Report Filter Dialog box. Click Ok to apply.

Charts in WEBI –

There are multiple types of chart available in WEBI which help us in graphical representation in a report that we are making.
Bar chart - charts are used to compare similar groups of data and they display data in rectangular form horizontally.

Box plot chart - it is a graphical display of a five number summary based on distribution of a dataset: the maximum, the minimum, the first quartile, the third quartile, and the median. It can also show abnormal values called outliers.

Column charts - charts are constructed of vertically-oriented rectangular bars. The height of the rectangles are proportional to the values associated with different category items.


Line charts - XY chart that displays lines connecting plots. Value axis plot positions are expressed by analysis category items. The second value axis plot positions represent the associated values.

Map charts - chart displays values within nested rectangles that can be colored. The levels of nesting correspond to the levels of hierarchy breakdown. The size of rectangles and their color both express a set of values.

Pie chart - circular chart made up of sectors. The area of circle represents a whole, and the sectors of circle represent the parts of a whole.

Waterfall - is used to show the cumulative effect of values of a measure and each bar starts with the level of previous one.

Steps of adding chart to a WEBI report- it is of 3 ways. First way is -

Go to Report Element → Chart → Select a Chart and click on the report area where you want to insert a chart.

Chart is greyed when there is no data assigned to it. To assign data, you can drag the object from the list of available objects to chart axes.

Second way is –

Right-click → Insert → Select the Chart type as shown in the following screenshot. Click the report to add a blank chart and then assign data by dragging objects from the list of available objects.

Third way –

Another method is by converting a table into chart. You can do this by selecting a table in a Webi report. Right-click on a column → Turn Into → Select a Chart. In this way tables will be converted to column chart.

Merging –

According to me merging is a feature which is available in WEBI which allows linking of 2 or more data providers (query).
Basically we do merging from 2 or more queries one WEBI report so that we can use them in same block. The primary thing in merging is that data must be of same type.
It is basically of 2 types – extended merging and force merging.
EXTENDED MERGING –
It is a condition where dimension do not have same nature and features and still merge.
For example – we have 2 data providers which are –
DP – 1 CITY AND SALES REVENUE.
DP2 – 2 COUNTRY AND CITY

CITY	SALES REVENUE
LA	$1000
NY	$2000
JAPAN	$3000

COUNTRY	CITY
USA	LA
USA	NY
JAPAN	TOKYO

If auto merge option is enabled than city dimension will merge from both data providers and report will get result as,

COUNTRY	CITY	SALES REVENUE
USA	LA	$1000
USA	NY	$2000
JAPAN	TOKYO	$3000

Force merging – it is a condition where measure objects are been merged.
Example – for creating a report for country wise sales without cities in that then we know that there is no direct link between country and sales. For this we do force merging function for sales measure.

 FORCE MERGE ([SALES REVENUE])

COUNTRY	SALES REVENUE
USA	$3000
JAPAN	$3000

 Here in the above table when we are applying force merge function than in case of USA it is adding up the entire sales revenue of USA country i.e. total of NY & LA which is equal to $3000. Where as in japan there is only Tokyo city and its sales revenue is $3000.

FUNCTIONS 

@PROMPT	@prompt(‘message’,[‘type’],[lov],[mono],[multi],[free],[constrained])
@SELECT	@select(class name\object name)
@WHERE	@where(class name\object name)
@VARIABLE	@variable(‘my name’)
@SCRIPT	@script(‘var name’,’var type’.script name)

DIMENSION MODELLING –

Logical design technique to present data in standard frame work to allow for high performance access.
Every dimension model is composed of one table with multi part key called as fact table and set of smaller table called as dimension table.

DATA TRACKING –

WEBI have a feature to track data change by highlighting the change data and display the previous values of dimension or fact along with current values. It highlights the changed data according to parameter we set, in the option auto update current data becomes the reference data after each data refresh and other option is used the current data as reference data. The report always show the difference between the most current data and this fixed reference data.
We can track following types of data change such as –

• Inserted data • Deleted data • Changed data • Increased values • Decreased values

Example - If sales have decreased in a region, data tracking displays the decrease. You can then drill down into the data for the region to understand why revenue is falling.
It can be done in 2 ways, these are as follows –
Automatic - In automatic data tracking mode, you always compare the current data with the data before the last refresh. This is achieved by automatically setting the current data as the reference data just before each refresh. The reference data is always one refresh behind the current data.
Manual - In manual data tracking mode, you select the reference data. You continue to use this data as a reference point until you update the reference point.

Steps –

1. Click Track Changes on the status bar at the bottom of the window to display the "Data Tracking" dialog box.
2. Select the Data tab.
3. To compare the data with the last refresh, select Compare with last data refresh. When you select this option, the current data becomes the reference data after each data refresh.
4. To compare the data with a specific data refresh, select Compare with data refresh from and select the date of the data refresh from the list.
5. Select the reports that you want to display data tracking from the Reports with data tracking shown list.
6. Click Refresh data now to refresh the data when the dialog box closes.
7. Click OK

SCOPE OF ANALYSIS –

The scope of analysis for a query is extra data that you can retrieve from the database to give more
Details on the results returned by each of the objects in a query.
In the universe, the scope of analysis corresponds to the hierarchical levels below the object selected
for a query. For example, a scope of analysis of one level down for the object Year, would include the
object Quarter, which appears immediately under Year. The hierarchies in a universe allow
us to choose your scope of analysis, and correspondingly the level of drill available.

Steps involved in this are as follows –

1. Click the Show/Hide Scope of Analysis Pane button so that it appears pressed in.

2.The Scope of Analysis panel appears at the bottom of the Result Objects pane. The default scope of analysis is none. Each dimension in the Result Objects pane appears in the Scope of Analysis pane.
3.Click the down arrow in the Scope of Analysis drop-down list box.
4.Select a level for the scope of analysis. The level appears in the list box and the dimensions that are hierarchically below each dimension in the Result Objects pane appear in the Scope of Analysis pane.
5.If you want to add selected dimensions to the scope of analysis or create a custom scope of analysis, select dimensions in the Query Manager and drag them across to the Scope of Analysis panel.

COMBINED QUERY –

A combined query is a group of queries that work together to return a single result. You can combine queries in three relationships:• union• intersect• minus
A UNION query takes all the data from both queries, eliminates duplicate rows, and builds a combined data set.
An INTERSECT query returns the data that is common to both queries.
A MINUS query returns the data in the first query that does not appear in the second.

Steps –

1. Create an initial query in the query panel.
2. Click the Add a Combined Query icon on the toolbar to display the Combined Queries panel under the list of query objects. The Combined Queries panel shows the current query. You can change the query name by clicking the query in the panel, selecting Edit Name from the menu, then typing a new name in the Name box in the dialog box that appears.
3.  Click Add a query to add another query. The second query appears in the Combined Queries pane and is:

• Combined with the original query in a UNION relationship.
• Named Combined Query #n.

4. To switch to a query, click it in the Combined Queries pane.
5. To delete a query select it in the Combined Queries pane and press the Delete key, or drag and drop the query to the universe outline.

6. To change the combination type, double-click on the operator. The operator moves through the sequence UNION, MINUS, INTERSECT.

7. Build each query within the combined query as you build any normal query.
8. Click Run Query.

STEPS FOR RANKING WITH EXAMPLE.

RANKING – STEPS

1. Add the objects that you want to appear in your query to the Result Objects pane of the query Panel.

2. Click Add ranking on the toolbar at the top of the Query Filters pane. The ranking outline appears in the Query Filters pane.
3. Select the ranking direction and type (Top, Top%, Bottom, Bottom %).
4. Type the number of records (if you selected Top or Bottom) or percentage of records (if you selected Top% or Bottom %) you want the ranking to return in next box.You can specify a prompt instead of a constant by clicking on the arrow next to the number and selecting Prompt. When you select a prompt the user must enter the ranking number when the query is run.

5. Drag the ranking dimension to the box to the left of the Based on box.
6. Drag the measure on which you want to base the ranking to the Based on box.
7. Drag the dimension that provides the calculation context for the measure to the Ranked by box. This dimension is optional. To display the Ranked by box, click the arrow to the right of the Based on measure.

8. Drag any dimensions that you want to include in the WHERE restriction to the area at the bottom of the ranking.

9. Click Run Query.

EXAMPLE – (10 employee by salary in each dept.)

The query panel is open.

1. Drag the Department, Employee Name, and Salary objects to the Report Objects pane within the Query Panel

2. Click Add ranking at the top of the Query Filters pane. An outline database rank is added to the Query Filters pane.

3. Rank the top 10 employees in descending order by setting the ranking direction/type to Top, and the number of records to 10 in the box next to Top.

4. Rank employees by dragging the Employee Name object to the box to the right of the number of records.

5. Base the ranking of employees on salary by dragging the Salary object to the Based on box.
6. Click the arrow next to the Based on measure if the Ranked by box is not already visible to display the Ranked by box.

7. Rank employees based on salary by department by dragging and dropping the Department object to the Ranked by box and run the query to return to the ranking.

STEPS FOR ADDING HYPER LINK IN WEBI -

1. Select the cell and select, right-click and select Linking > Add Hyperlink to display the "Create Hyperlink" dialog box.

2. Select the Link to web page tab.
3. Type or paste the hyperlink text into the box.
4. Click Parse to extract the hyperlink parameters into the Customize URL parameters area (which is not visible until you click Parse).

Dynamic hyperlinks contain parameters whose values can change. Parameters appear as name=value parts at the end of the hyperlink after the question mark. For example, the URL – http://salesandproductreport/default.asp?reportname=products contains one parameter, report name, whose value is “products". After you click Parse, each parameter appears on a separate line with the parameter name on the left and the parameter value on the right. The static part of the hyperlink (the part without the parameters) appears in the Main section.

5. To supply data from formulas or variables as parameter values, click the arrow next to each parameter value and select an option.
6. To add or remove a parameter, modify the hyperlink syntax, then click Parse.
7. Click the arrow next to Cell content to change the text displayed in the hyperlink cell and choose one of the options.
8. Type the tooltip text in the Tooltip box or build a dynamic tooltip by using the Build formula or Select variable
9. Click the arrow next to Target window to define how the target URL appears.