Random selection of record set

building reports webintelligence
1

HI BO Gurus,
I am working on a webi report wherein the requirement is to show all records from the universe in one tab1/report1 and 50 unique randomly selected records in tab2/report2. I am using oracle 10g database , I am not able to see sample record set option in query property. Please suggest how can I achieve this.

Thanks in Advance,
Surendra

SurendraNAB (BOB member since 2009-10-09)

2

I’d have said use a random number generating function to create a random number between 1 & the count of your records & then use Rank() to rank the top 50 & filter on numbers 1-50… however I’m using Webi XI R2 & I’ve just discovered I don’t have a random function available = argh!

In that case, are you using XI R3 & check if you have random function available. If not, maybe create an object in the universe to give you random figure…

SteveD :uk: (BOB member since 2009-11-02)

3

I am using XI R2, I am not sure how to create a random number object in Universe? is there any in built function in Universe designer. I know dbms_random.value function in oracle , but not sure how to use it in random number object generation in universe designer.

Please suggest how can I achieve this.

Surendra

SurendraNAB (BOB member since 2009-10-09)

4

See here out of interest:

http://www.brettb.com/SQL_Help_Random_Numbers.asp

There’s some good advice in there, but your universe designers should be more than familiar with this already. I’m not a uni designer myself. A random function’s something I expected to be there in webi, clearly not! :hb:

SteveD :uk: (BOB member since 2009-11-02)

5

You could select a sample set by reducing the number of rows from the fact table to a random percentage.

Example below shows a SQL query returning .01% random rows from the fact table, then joins these to a dimension table


select * from dimension_table a, fact_table sample(.01) b
where a.id = b.id

I assume that this requirement is for testing, therefore, I suggest hard coding the “sample(0.1)” (or whatever percentage works best for you) into the SQL of the report.

dgpotter :uk: (BOB member since 2010-01-04)

6

Surendra,

This older topic could help:

Marek Chladny :slovakia: (BOB member since 2003-11-27)