Utilizing a Union, Ranking, and Subquery in SAP’s New BI Platform
Unleash the power of your database utilizing these advanced features within the Query Panel. There are several tools within the query panel that will allow you to push complex business rules and conditions down to your database instead of having your Business Objects/Lumira Server handle them.
Normally, your database server will have more power and resources available to it than your Business Objects Server. Utilizing these tools should then not only improve your report rendering time but free up resources on your Business Objects Server by pushing this complex logic down to your database. This means fewer rows generated for BusinessObjects to format. An added bonus is now the database is carrying most of the load, fewer rows will be passed back and forth over the network between the database and BusinessObjects server.
Combined queries allow you to answer complex business questions that are difficult to formulate using standard queries. These can be highly effective when looking for the same Dimension/Metric combination, but with different conditions. A new combined query is created each time you click on the Combined Query icon. The Combined Query panel will appear after adding the first combined query.
You can combine queries in three relationships:
- A UNION query takes all the data from both queries, eliminates duplicate rows, and builds a combined data set. (Union All is not available)
- An INTERSECTION 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.
Double-clicking on how the queries are combined (“Union” in the screenshot above), allows you to change how each query interacts with the next: Union, Intersection or Minus.
In the example below, you have two queries that return lists of countries as shown in the following table:
|Query 1||US; UK; Germany; France|
|Query 2||US; Spain|
The different types of combined query return the following values:
|UNION||US; UK; Germany; France; Spain|
|MINUS||UK; Germany; France|
A subquery is a flexible query filter that allows you to restrict values in more sophisticated ways than is possible with an ordinary query filter. In the past, you might have used two or more queries to return the data you needed and then added one or more complex report variables to filter the data to what you needed on the report. A subquery not only will do this complex work for you, it should also improve the report’s performance by now only retrieving the rows you really needed. In the example above, we only want to see customers who ordered an All-Purpose Bike Stand in 2016 and had an order ship between 9/1/2017 and 12/31/2017, regardless of whether this order contained a bike stand.
Subqueries also allow you to:
- Compare the values of the object whose values are used to restrict the query with values from other objects.
- Restrict the values returned by the subquery with a WHERE clause.
You can create a subquery by first selecting an object from the list of objects available to you in the left panel and then clicking on the Subquery icon in the top right corner of the Query Filters pane. Subqueries work by modifying the SQL that is generated to retrieve the query data. The SQL contains a subquery that restricts the data returned by an outer query.
Inherently, Web Intelligence allows you to return unranked data from a database, then rank it at the report level.
A database ranking allows you to specify a ranking at the query, so that the returned data is already ranked. This has many advantages. Not only is the data already ranked, but the amount of data returned is much smaller, minimizing the complexity of your report development.
Database rankings allow you to answer questions like “Who are the top five customers, based on the revenue they generated for each year?” at the query level, without the need to return to Web Intelligence data that falls outside the ranking and then filter it utilizing a report ranking.
In the query above, we’re bringing back the top five sales territories in 2016 and then seeing how their sales looked between 9/1/2017 and 12/31/2017.
You can create a Rank Filter by clicking on the Rank icon in the Query Filters pane. The Rank Panel appears, allowing you to build the condition. You can pick either a Top or Bottom ranking, as well as the number of selections to return, which can be hard-coded with a constant or prompt the user at runtime.
Note: This function only works if ranking is supported/turned on for your database. The Rank Icon will be grayed out if either statement is not true.
If this blog didn’t quite answer your question or you have other Business Intelligence questions or concerns, please feel free to contact us.
Read more Tips and Tricks in our other blogs