Solving the Mystery of Data Provider Synchronisation

building reports desktopintelligence
1

I’m referring to the well-advertised and much-used presentation by Steve Bickerton. I’m hoping that you’ll read this one Steve.

I’m trying to help out a colleague. He had 14 data providers in a 22-tab report. Don’t say anything. The 14 DPs are 3 from the same universe and 11 from spreadsheets. The 22 tabs are a mixture of tables and crosstabs summarising the data in different ways and applying a variety of filters.

Moving from tab to tab takes a long time as BO recalculates everything. There aren’t that many local variables (apart from sums on measures). I’m guessing the time is taken doing the synchronisation of the 14 DPs. (And 2/3 of them return in the region of 100k rows).

My question is this. Is there anything I can recommend to minimise the time it takes to calculate (without changing the configuration of his report)? The last point on Steve’s presentation refers to filters and suggests “creating the defined filter when you’ve purged the data”. I don’t think I fully understand this. Sure I can purge the data, create the filter and apply it, but when I refresh the data, won’t I be back to square one.

Any tips would be greatly appreciated. Thanks.

Grantie :uk: (BOB member since 2004-07-01)

2

Sorry, but you really can’t improve the performance here. You’re asking BO to be a database in this case. It is having to “join” all this stuff together.

If you want to improve performance, you should have the spreadsheets loaded into a database table and build a mart that will perform better.

Steve Krandel :us: (BOB member since 2002-06-25)

3

Yep I guess you’re telling me what I already know and I’m looking for a magic solution. Thanks Steve. :wah:

Grantie :uk: (BOB member since 2004-07-01)

4

You could chop it into 2 reports, each with half the tabs…or 4 reports or whatever…you could use BCA to refresh the two reports, one after another so the duplicate sql cached for the second refresh. You could experiment with removing the links between DPs to see if that is the cause of your problem. Oh and best of all you could review the report with a magnifying glass. Even if get rid of say one column on one tab you’re going in the correct direction…apart from starting again as per Steve’s suggestion :wink:

Nick Daniels :uk: (BOB member since 2002-08-15)