Posts Tagged ‘SQL’

Bypassing the Universe – Using Custom SQL

The Downfall of Business Objects?

Sometimes the wonderful drag and drop functionality of BusinessObjects can be its downfall as well. When report writers do not have access to Designer or edit access the universes that they must use there exists an opportunity for this downfall. If any object in the world of SAP BusinessObjects follows the rule of trash in-trash out it is the universe.

BusinessObjects Universes Must be Perfect

A universe has to be just right. It can’t be too complex, nor too simple. It can’t have errors and it must be up-to-date with its underlying database. Additionally, different users have different expectations of a universe and therefore all judgements a universe’s adequacy hinge on the collective opinions of that universe’s report writers/developers. Nothing I am saying hear is news to anyone that has struggled with universes and reports; however, I hope it helps some feel more justified or more understanding.

A Workaround

If a universe is deficient, incomplete, or lacking there is a workaround. If a user is very comfortable with the data source data model and they want to bypass the universe, there is a workaround. If you are editing your report’s query and you want to test something not in the universe without editing the universe, there is a workaround. What is this workaround? Use Custom SQL.

How Do You Use Custom SQL?

First of all, you must have the rights of “View SQL” and “Edit Query” granted to you user account (members of Administrators user group should have these already). Both of of these are found in Business Objects XI 3.1 under the object’s “User Security” > “Advanced” > “Content” > “Web Intelligence Report”. Once you have these rights you can open the report (modify) in Java Report Panel. Once everything loads. click on the “Edit Query” button and then click the small “SQL” button on the toolbar. You will then see a new window open named “SQL Viewer”. Here you can see the underlying SQL of the query. Please note that you can copy it to your clipboard by clicking the “Copy” button. You can also pass the point of no return by selecting the radio button option of “User custom SQL”. Actually, don’t let my drama scare you, you can always undo your custom SQL. In fact, it is rather easy to lose your custom SQL and revert to the Business Objects generated SQL; so please take care to back-up your custom SQL statements.
PLEASE NOTE: If any of the above mentioned options are disabled for you then you do NOT have your rights set incorrectly.

The Rules of BO Custom SQL Editing

Here are a few rules that you must follow when working with Custom SQL in Web Intelligence (or Desktop Intelligence):

1. The number of fields in your SELECT clause must match the number of objects in the query



2. The type of the fields in you SELECT clause must match those of their corresponding BO objects. In other word, if your first query object is “Customer Name” and it has a type of “string” then the first field/item in your SELECT clause must also have a datatype of string. If the third object in you query is of date type then you better make sure that the third field/item/element in your SELECT clause is of the date datatype. Usually a click of the “Validate SQL” will highlight any such violations (see screenshot).
3. This rule is really an extension of rule #2. Functions on objects in the SELECT clause are allowed; however, they must output a matching data type
4. Don’t forget about object type; the object containers will be treated by BO according to their type. If the BO object you selected, for example, “Customer Number” is a dimension object and you successfully edited the SQL and forced order quantity (an obvious measure candidate) into this object. You cannot expect BO to start treating the container object “Customer Number” like a dimension in any aggregations. This won’t cause errors; nevertheless, for best results please try to use container objects that will match your custom SQL field in object type.
5. When your Custom SQL edits are complete I suggest you copy the SQL and save it to a file somewhere, then click the “Save” button, and click “Run Query” to refresh the query and data.
6. After you Custom SQL is set then you should take much care not to touch the rest of the query in any way. That is to say, do not attempt to adjust the results section or the conditions sections. Any edits to these will result in reverting your report’s SQL to that generated by BusinessObjects and your custom SQL statement will be lost completely. So if you suddenly decide you need to add another object then you have to be very careful to copy the SQL you have, then add the object and then replace the SQL that you copied. BE CAREFUL.
7. Do NOT select the option to “User the SQL generated by your query” unless you want to LOSE all of your custom SQL.
8. This is not actually a rule, but I felt I should point out that as long as rules 1, 2, 3, and 4 are followed the fields used in the SELECT clause and the contents of the rest of the the SQL statement do not have to have any relation at all to the objects selected.

Let’s Build BO Custom SQL Knowledge Base Together


I have not played around with Custom SQL as must as some. The best tip I can offer beyond the rules above is that if you plan to do this a lot you might want to prepare some dummy/generic objects that has their data type and object type in the object name. This would make it easy to get the right objects that you need.

Please share any tips that you might have picked up along the way.

In Business Objects how can I get a list of reports using a specific universe?

I have been asked the following related questions a few times in the past months and I thought it would be a good idea to post a public answer to them. First the questions:

• Is there any way to get a list of reports that are using a specific universe?
• If I change this universe which reports will be affected?

The Answer – Yes, Definitely, Using Query Builder

The short answer to these questions is “Yes, you can”. The slightly longer answer is, “Yes, this can be done through Query Builder, the BO Auditing data*, or through the Business Objects SDK.” For the purposes of this article, I will confine my further comments to the Query Builder and BO Auditing data solutions; the BO SDK solution requires a set of skills and even licenses that many BO Developers and Administrators do not possess.

I haven’t been keeping track, but I expect that those who have emailed me this question have not studied The Best Query Builder Guide Ever Written. I just wanted to point out that our Query Builder Guide answers these questions, and provides so much more.

The Resolution using Query Builder

For those of you familiar with SQL or just the concept of tables you might think that all you have to do is join up the record of the specific universe with all of the records of the reports where it is used. Well, this is partially true. Firstly, if you know something about query builder you know that reports and universes are stored in separate logical tables and you know that you can’t really join two tables together. The solution here is two part: (1) query the record for the specific universe retrieving all associated report IDs and then (2) query for for each of those report IDs to get the reports’ identifying information.

Query the Universe’s Reports

This is a straightforward query, but be careful if you do not have the universe’s object ID, because it is possible to have two universes with the same name and you wouldn’t want to get wrong data from the start. Here is the SQL statement you need to put in Query Builder:

SELECT
si_id,
si_name,
si_webi,
si_cuid
FROM
CI_AppObjects
WHERE
si_name = 'UNIVERSE_NAME' AND
si_kind = 'Universe'

From this you will get some additional universe info and a list of all reports that are bound to this universe. Copy that list of report object IDs to a text editor and proceed.

Query the Report’s Identifying Information

Take that list of report object IDs from the previous step and parse the list so that each ID is separated by a comma. Then substitute that list for the string “111111,222222,33333 below in the SQL:

SELECT
si_id,
si_name,
si_universe,
si_cuid
FROM
CI_InfoObjects
WHERE
si_id IN (111111,222222,33333) AND
si_kind = 'WebI' AND
si_instance = 0

The output should be your desired list of reports. Now that you see the “si_universe" property you might ask yourself why I don’t query the report table for my universe’s object ID. Good idea, but Business Objects’ Query Builder query language doesn’t yet support filtering on “property bags”, and si_universe, just like “si_webi” are property bags. They hold multiple values in a single property.

The Resolution using BusinessObjects Auditing Data

If you have enabled auditing on your environment’s report servers then you can look to your Auditing data for an answer to this question. However, auditing data is not meta data; it’s records are created as the objects are used. Therefore if a report is never used then it will not be present in this data set. If this is not a concern, or if you would like to know which reports have been used belong to a certain universe, then querying the auditing data is an option for you.

Querying the Auditing Records

There are many ways to mine this data source for the data you need. For most folks the best, easiest method is to use the “Activity” universe. Here you will find the objects you need. A tutorial on this universe and the auditing data is out of scope here, but I’d like to put together a guide on the topic if I hear of any interest. Another way to mine your data is directly against the database tables (these tables are not encrypted like the Business Objects XI CMS Infostore. This certainly allows for better query tuning, but it will increase the complexity for you. You could even use the SQL of a report created against the “Activity” universe as a starting point, but be careful as that universe is full of derived tables.

Final Thoughts

Both of these steps might seem difficult at first, but with a little practice you will find that you can whip out answers to these kinds of questions rather quickly. In fact, now you have the tools to answer the reverse as well: “How can I get a list of universe using a specific set of reports?”. Now you know, and knowing is half the battle.

Selective Operators: Allowing Users to Decide Which Operator to Use

Everyone once in a while you come across something that makes you say, “that is great, now why didn’t I think of that?”. Recently I came across such a thing: an idea of how to make operators in the WHERE clause of a query definable by the user at the moment of report refresh through creative use of SQL and Designer. Honestly I think everyone could benefit from a greater understanding of SQL. This like piece of code is the proof of that:

Selective Operators in Business Objects XI Web Intelligence or Desktop Intelligence at Run-Time


( 'Lesser than or Equal' = @Prompt('Select Operator:','A',{'Lesser than or Equal','Equal','Greater than or Equal'}, MONO,CONSTRAINED) AND Emp.salary <= @Prompt('Enter Salary:','N',,MONO,FREE) )
OR
( 'Equal' = @Variable('Select Operator:') AND Emp.salary = @Variable('Enter Salary:') )
OR
( 'Greater than or Equal' = @Variable('Select Operator:') AND Emp.salary >= @Variable('Enter Salary:') )


The value in this case does not come from fancy Business Object prompt syntax, but rather from creative combinations of SQL and prompts. Note: Please remember this is just an example that you should use to learn the technique and then adapt it to your database’s SQL and to your business requirements.

How it Works

Although this example code has three clauses they can all be bound to each other through their placement in a single universe-level condition, or you could place them in multiple conditions for mixing and matching at the report level. The power of this logic is that it uses one dedicated prompt to collect the desired operator from the user: ‘Lesser than or Equal’,'Equal’, or ‘Greater than or Equal’. Then using hard-coded string values it creates a scenario where only one of the three statements can be true.

And Example with SQL Substitution

In each statement the hard-coded operator text is matched with the actual operator following “Emp.salary”. Therefore, if the user selects “Equal” for the @Prompt(‘Select Operator:’) and “1000″ for the @Prompt(‘Enter Salary:’) then the following substitutions will be made for the actual SQL run by Business Objects:

( 'Lesser than or Equal' = 'Equal' AND Emp.salary <= 1000 )
OR
( 'Equal' = 'Equal' AND Emp.salary = 1000 )
OR
( 'Greater than or Equal' = 'Equal' AND Emp.salary >= 1000 )

Because of the magic of SQL and logic only the clause ( 'Equal' = 'Equal' AND Emp.salary = 1000 ) can execute; the others are ignored because the first part of the SQL evaluates to FALSE. In the rules of logic a statement of “FALSE and TRUE” is always FALSE. In this case we don’t know if the second part could evaluate to true, because we don’t know the underlying data, but regardless the entire clause is FALSE when one of the components is FALSE and it is linked to the others using “AND”.

A Few of the Other Details

OK, I feel I got a little technical on the logic, but I think the examples speak clearly enough just in case I lost someone. Again, the whole point that I am making with this code is that it allows users at run time (a.k.a. report refresh) to select the operator for they want to use. If a user wants they can also use the Java Query Panel and change the operators all they want, but they would need access and training to accomplish this.

Some notes: In order to keep it simple I only used the @Prompt statement once and I use @Variable for all of the other instances of the prompt’s use. This helps to avoid problems if I accidentally set the @Prompt differently and it makes it easy to modify Prompt syntax later. The parenthesis around each clause is necessary. Please let us know what you think and share your modifications or spin-offs or alternate uses if you have any.