26 Ranking Data In Queries - Webiworx

Ranking Data in Queries26 Ranking Data in QueriesThis session describes how to query a database to request data such as:Top 5 customers based on Customer Spend in this yearBottom 10 products based on Quantity Sold in last quarterDatabase Ranking calculates the ranking based on a measure and then filters the data to only returnthe records satisfying the request, therefore the above queries will be processed by the database as:Top 5 customers based onCustomer Spend in this yearOnly the Top 5 customers are returnedbased on Customer Spend.Bottom 10 products based onQuantity Sold in the lastquarterOnly the Bottom 10 products are returnedbased on Quantity Sold.NOTES1. Database Ranking is only available if your database supports this type of query.database does not support Database Ranking then thein the Query Panel will be disabled.If your‘Add a database ranking’ button2. To demonstrate this functionality the ‘eFashion’ data has been migrated to Oracle and a newuniverse has been created to work with Oracle instead of Microsoft Access. This universe iscalled ‘eFashion Oracle’ in our examples. If you do not have ‘eFashion Oracle’ available toyou then hopefully you can still follow the examples shown to get an understanding of‘Database Ranking’.3. After selecting an object and then clicking on the ‘Add a database ranking’ button adds theDatabase Ranking Filter into the Query Filters pane:For Reference, Web Intelligence uses the SQL-99 Rank function in ranking SQL.NOTEFor ‘eFashion Oracle’ universe, further details and instructions can be found atwww.webiworx.com. Look for information on ‘SAP BusinessObjects Web IntelligenceTraining Course’ on the 'Downloads' page.425

Ranking Data in Queries26.1 Parameters for Database RankingThe Database Ranking Filter has a number of parameters as shown below:This is the Ranking Order.You can rank based on avalue (e.g. Top or Bottom10 based on [Quantitysold]).Ranking Dimension, e.g.if the dimension is [State]and the ranking is Top 10then the Ranking Filterreturns the top 10 states.For example, a ranking for[State] with a condition of[Christmas period] restrictsthe ranking to use dataavailable for the seasonalsales around Christmas.OrYou can rank based on apercentage of a value (e.g.Top or Bottom 20% of[Quantity sold]).Number of records toreturn, for example the Top10 records when RankingOrder is Top or Bottom.OrPercentage when RankingOrder is % Top or %Bottom. In this case thenumber of records will varyas it will be based on therecords that contribute tothepercentageyouspecify.Where condition (optional)is used to specify additionalrestriction(s) on the valuesreturned in the databaseranking.Based on (a Measure) bywhichtheRankingDimension is ranked.e.g.if the measure is[Quantity sold] and thedimension is [State], WebIntelligence ranks states bythe quantity sold in eachstate.Ranked by (optional) isused to specify additionalcalculation context for theranking.e.g. if the Database RankingFilter definition is:Top 3 [Store name] Basedon [Quantity sold] Rankedby [State] Where [Year] 2005thenWebIntelligenceisrequesting the database toreturn the Top 3 stores ineach state for 2005.426

Ranking Data in Queries26.2 Using Database Ranking Filter in QueriesLet us take the example we used in Sub-Queries and adapt it to use database ranking instead.In the Sub-Query we demonstrated how to:Show data for all years for the States that have sold less than 6000 (in quantity) in this year, i.e.show data for all years for the states Massachusetts, Colorado and Florida.We specified a finite limit (6000), so in the future we could run this query to find all states thathave sold less than 6000 (say in January of next year) or all states have sold less than 6000(say in September of next year). Therefore, in January we could get all states being reportedand in September we would have no data at all!It would be better if we had a dynamic query that returned the Bottom 3 states based on the[Quantity sold] (irrespective of the actual number).In the Sub-Query example, we first ran a queryfor [This year] that was sorted descending on[Quantity sold].From this we were able to see the 3 states thathad sold less than 6000 in this year.We then modified the query to show all years forthe states that have sold less than 6000 (inquantity) in this year, i.e. show data for all yearsfor the states Massachusetts, Colorado andFlorida.To do this we edited our query to include a SubQuery as shown below.427

Ranking Data in QueriesWe want to keep the same result objects as the sub-query example but we will also use the DatabaseRanking Filter instead of the Sub-Query Filter.NOTE – Use the ‘eFashion Oracle’ universe for this example.1. Add the following objects into the ‘Result Objects’ pane, then click on [State] object and then clickon ‘Add a database ranking’ button.Web Intelligence will add the database ranking filter into the Query Filters pane.2. For our example we want to specify Bottom 3.3. Drag [Quantity sold] and drop into the area labelled ‘Drop a measure here’ in the database rankingfilter.The Database Ranking Filter is nowrequesting Bottom 3 [State] Based on[Quantity sold].428

Ranking Data in Queries4. For our example we also want to add in a filter to the database ranking filter itself, so drag and drop[This year] into the database ranking filter.The Database Ranking Filter is nowrequesting Bottom 3 [State] Basedon [Quantity sold] in [This year].5. Running the query shows data for all years for the Bottom 3 [State] in [This year] Based on[Quantity sold].Our results are the same as the SubQuery example (shown opposite), exceptwe now have a query that is dynamic inthat it will always show data for theBottom 3 states in this year (irrespectiveof the values for [Quantity sold]).429

'Database Ranking'. 3. After selecting an object and then clicking on the 'Add a database ranking' button adds the Database Ranking Filter into the Query Filters pane: For Reference, Web Intelligence uses the SQL-99 Rank function in ranking SQL. NOTE For 'eFashion Oracle' universe, further details and instructions can be found at