BusinessObjects Board

IDT Performance issues on webi

building semantic layer
#1

Hi Experts,

Please find the attached ppt , please help me on this .

Thanks,
vsr
IDT WEBI Performance issue.pdf (93.0 KB)

vsr.2k :us: (BOB member since 2012-04-05)

#2

What is the issue?

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

#3

What DB and connectivity are you using?
6 million records really isn’t that many.

Are the DB indexes and stats up to date?
Where are your DB and BO servers located, in the same data center?

You could look at getting your DB team to partition by country.

Mak 1 :uk: (BOB member since 2005-01-06)

#4

@Marek Chladny : i am looking to improve the performance of the webi report and the universe, currently webi taking >5 mints to retrieve the data.

@Mak 1: DB is SQL Server, yes the DB indexes and stats up to date.
and yes DB and BO servers located in the same data center.
Can you please ex plane this point > You could look at getting your DB team to partition by country.
i was thinking to creating the multiple tables based on the each country is this what you mean ?

Thank you for the help.

Thanks,
vsr

vsr.2k :us: (BOB member since 2012-04-05)

#5

Read up on partitioing:-

Obviously, there may be bottle necks with other components on your system. For example:-
:arrow_forward: Incorrect Array fetch size set in universe
:arrow_forward: SQL measures not defined with an aggregate function (sum, for example)
:arrow_forward: Database functions used as conditions in where clause.
:arrow_forward: Low BO Server Spec
:arrow_forward: Low SQL Server Spec
:arrow_forward: Poor quality network (latency)

Mak 1 :uk: (BOB member since 2005-01-06)

#6

Extract the SQL statement that takes a long time to run(in webi query editor -> view SQL -> copy) and run it directly on the database(paste the statement in management studio). If it takes the same time as in webi, look at the execution plan and tune the database. If it’s less, tune the BI environment.

Derf :canada: (BOB member since 2011-05-16)

#7

Good point, but when doing this analysis he needs to make sure he returns all the rows in Management Studio, to allow comparision to the BO query.

Mak 1 :uk: (BOB member since 2005-01-06)

#8

Please, start here: Some advice on how to performance tune SAP BI BusObjects

Andreas :de: (BOB member since 2002-06-20)

#9

HI Mark 1

We are planing to do partitioning the table by country like USA , UK , Australia.

If i partitioning table each table will have a country by restriction and common key figures.

I have one question if i select the country for USA in webi is this webi retrieve the USA related key figure data are it will aggregate the all the table key figure data for USA.

Thanks,
vsr

vsr.2k :us: (BOB member since 2012-04-05)

#10

it should use the USA partition and return the fact data aggregated by country, if that is what you are asking.

Hwoever, my orginal comment still stands, I don’t see why you should have to do this with quite a small amount of records.

Are you sure the bottleneck is the database?
How long does it take to execute the full query results against unpartitioned tables in Management Studio?

Mak 1 :uk: (BOB member since 2005-01-06)