Relating SQL and Web Intelligence - a birdview

Coming back to technical writing is a tough ask. Simply because it takes much of accurate stuff to fill details & also something which is helpful for most of blog readers since I am not writing a chapter by chapter tutorial.

So I decide to touch SQL query building here. I will not be going much in detail - will be just give some pointers and notes that I always try to remember myself with.

First thing when it comes to a SQL query I remember is the structure -

SELECT column_list

  FROM table_list

 WHERE condition_list

GROUP BY column_list_for_grouping
    HAVING condition_list

ORDER BY column_list_for_ordering

Tips: 
1. Order by clause is always at the end of the SQL statement.
2. Group by clause should mention all the column names that are present in the select statement. The columns on which aggregate function is used can be avoided.

Let us relate the SQL query structure to a SAP Business objects - Web Intelligence report. Custom sort is an option that is readily available in Web Intelligence reports. Easy to understand - it provides 3 options as Ascending, Descending and custom order.

How to Apply Sort to a Report in Web Intelligence:

1. Open a Web Intelligence report. Select the section or table cells you want to sort.
2. Click the down arrow next to the "Apply/Remove Sort" button on the Report toolbar.

webi sort - icon

3. Select "Ascending," "Descending" or "Custom" from the list of sort options.
4. Click on a value in the list if you're applying a "Custom" sort. Click the up or down arrow to promote or demote the value in the sort order. Type a value in the "Temporary Values" box and click the double-arrow button if you want to add a temporary value to the sort list.

Another option is to change the Query panel's query (button click - View SQL) and provide Order By clause at the end. This will not work in case the source for WebI report is a SAP BW query whether Universe designer is used or not.

Query panel and View SQL (2nd row last icon)

Grouping however, can be accompolished by various options. I will take you through in upcoming posts. As of now - here are some ways of achieveing grouping in a web intelligence report.

1. Create variable and assign group of values to it. Perform required aggregate function on it. This is however for a complex require which is rare.

2. Use of Breaks - naturall groups for aggregate functions

3. Use of Sections- most easy and common way to achieve grouping

To create section.
Select the dimension which should be displayed as section > Click Reporting > Set as Section

Section created on Month

Sections can be applied and formatted to function more or less like breaks; however, breaks can never function as sections do, they are limited to the scope of the block on which they are defined. I tend to prefer sections as they allow for greater flexibility and they seem to have more options. Even then, breaks can be excellent for grouping relatively small amounts of data and then aggregating the measures at the bottom of the table.

WebI - Section and Break

In SQL - aggregate functions are very important. The reason is simple - because it provides necessary information in the summary format.

Below is the list of useful aggregate functions that can be used when grouping data to display summary output:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

More to come, hopefully.

Real BO Web Intelligence Report basic

A very serious day... High learning though.

Here is an interesting problem which turns the BO developer into a fool like face. Thought to put it on here since the developer's search for resolution was a failure.
Creating a Web I report on top of a universe created for a SAP BW query got tricky. We had provided some input parameters and selected just a couple of fields.

Now, the parameters were on a field something like country. It was noticed that when the parameter is provided, say India, then the data was shown only for India however the number orlf rows returned were no less than the total number of rows present for all the countries.
Even the attempt to create restricted variables on BW queries did not work.

Solution: Include a key figure in the selection of the web I query. This will return the filter data and exact rows as returned by BW query.
Techsi for the day: A key figure is must in a Web I report without which the data will be fetched from the masters dicectly and the number of rows will be equal to the numbers of the masters, i.e. for all values.

From next time..have a key figure in web I whether you need it or not.

More to come, hopefully.

The first one on blogger - lets keep it technical

I used to write down few of the memorable things...but later there was not enough time for it.
There is so much to learn everyday at job..talking just about the technical stuff here. Couldn't get much inspiration to note down all the learnings till now.
But from now on - blogger will be colored by the technical stuff at least.

I work in SAP BO now..earlier was Oracle. Lots of things to share which will do one by one as and when I remember.

SAP BO techsi for the day - If you are just starting to use it better make a habit of proper naming conventions for every variable you create. You may not be able to document from day one but a logical name would help good way.

More to come, hopefully.