I’m asking the community for tips on how to best utilise SAP BusinessObjects when managing massive amounts of data.
Larger and larger datasets are being handled by our BusinessObjects environment as we oversee a project. Although the performance of our current configuration has been sufficient, as our data quantities increase, we’ve witnessed a progressive drop in performance. I’m searching for methods or guidelines that will boost output and guarantee that our dashboards and reports continue to function properly.
In particular, I’m curious about:
Methods for Data Modelling: Which specific data modelling techniques have you discovered to be useful for improving performance? How can you manage huge fact tables and guarantee the effectiveness of your queries?
Report Design: Which best practices should be followed to create reports that process big data sets quickly without sacrificing accuracy? Is it possible to optimise specific BusinessObjects features or configurations for enhanced performance?
Server Configuration: What are some tips for setting up BusinessObjects servers to run as efficiently as can when handling big datasets? How do you efficiently manage resources and maintain load balance?
Troubleshooting Advice: What typical performance-related problems have you run into, and how can you fix them? Exist any particular performance measures that should be kept an eye on in order to identify possible bottlenecks?
We would be grateful for any advice or firsthand knowledge you could provide. In order to increase overall efficiency, I’m eager to absorb your suggestions and implement them to our existent arrangement.
Hello,
in addition to the mentioned article the following points should be considered:
of course the data model of the datamart you use should be designed to fit reporting on large data
volumes . E.g. a star schema, means a central (denormalized) fact table surrounded by lookup
and hierarchy tables that contain hierarchies/test for codes stored in the fact table.
Depending on which objects you use, BO will build the sql only containg the tables/joins you need.
educate your users (this is primarily a network issue). When they already filter in the report
design, the database will (and should) do the main workload.
If the data is poorely filtered (because users like to do it in the reports), the result might contain
(depending on what you allow in the universe settings) millions of rows.
these have to be a) delivered over the network and b) handled on the client machine, which
(depending on the hardware) can easily end with “out of memory” . Or (as we watched) the query is finished after a minute in the database, but the user waits for 20 minutes to see the results …
(not recommended, but possible) :use “aggregate_aware”: this is a way of telling the sql generator
that BO is where to get its results from. Depending on the list of objects needed it can decide to get the results from the preferred ( first in list ) highest aggregated table which should be substantially smaller than following tables with finer granularity.
cons: 1) in your datamart you have to populate these aggregate tables with each loading process
2) you have to set up lists of “incompatible objects” for each table
eg. the highest aggregate T1 contains “year, customer, country”
and the next one T2 “year, month,customer,country,district” as dimensions,
then month and district have to be incompatible to T1, thus a report containing them
has to use T2
Setting up the list of incompatible objects is very errorprone and makes universe
maintenance much harder
Database:
E.g. Oracle supports “query rewrite” which in essence does the same as “aggregate aware”
but is limited to “non complex joins” That means no outer joins between tables.
Partitioning: If you can slice your data into meaningful chunks (e.g. one parttion per month)
you can help your database to decrease the workload, because when a report wants the data
of one year only 12 parttions will be searched instead of maybe hundreds.
Indexing: this is not easy, because your datamart should be universally usable, but maybe
you can identify (maybe by looking at the sql of the reports). the dimensions that are
frequently used and do proper indexing on them.
Don’t blame BO for bad performance !
If you really have “massive amounts of data” you have to invest in a decent Database and powerful DB-servers (e.g. HANA), so you can push-down all the complex/massive calculations to the DB and BO is “only” used for preparing and displaying the final results.
SAP BusinessObjects continues to add several different cloud databases to its supported platforms. I got to participate in a large migration effort from on-premise Teradata to cloud-based Snowflake. Cloud databases can scale compute power based on demand at different times of day, something that just can’t be done on-premise without a huge investment in hardware.