Determining the need for a BWA
How do you decide if you need a BWA or not, whilst user experience is paramount to adoption of a BW system, speed of reports is not the only criteria and not all reports will be improved by a BWA. Others may decide that expected data volumes will obviously impact the report speeds.
Our challenge was if a BWA was purchased how would we be able to set some criteria that the projects could use to determine if they would benefit from a BWA. In our scenario any BW project needs to budget for entry into the BWA, to pay for ongoing costs and potential increased capacity, and the earlier this is done the better.
How do we justify technically a BWA is required
Mainly a BWA requirement is driven from the following identifiers
Queries that have long database read times
- Infocubes where aggregate change runs inhibit the ability to load data in the overnight schedule.
Whilst it is easy for the support team to see the impact aggregates are having on the dataloads the process for identifying queries and InfoCubes involves analysis of historical statistics.
SAP has a number of recommendations for early identification of queries likely to achieve the biggest performance benefit.
Subsequent and more detailed analysis could also be conducted post implementation to further identify opportunities for query performance enhancement if necessary.
DBSEL is very high (DBSEL > 1 million records)
- DBTRANS ratio to DBSEL (DBSEL/DBTRANS > 100)
- DBTRANS is very high (DBTRANS > 50000)
- This may not highlight the need for a BWA but will show areas where the design may need changing to make use of the BWA
These statistics can be obtained using standard technical content, by using the InfoCube 0TCT_C01 (aggregated data) by creating a query that has the following key figures
Records selected on DB per query (DBSEL)
- Records transferred from DB per query (DBTRANS)
- DBSEL / DBTRANS (ratio of DBSEL:DBTRANS)
Then use filters to limit the amount of data coming back in the query, there is no point in having records in your query where the records selected is 0. Also remember to take into account the frequency a report is run. Will the business pay for a BWA based on reports that are run once a quarter!! These reports can be enhanced to include this information
How do the business justify a BWA
Now we had the technical view we needed to provide the business and BW projects the process with which they could get their Infocubes into the BWA.
There was a need to ensure that the BWA was not an excuse for bad design.
We therefore created the following process
A business case is identified for performance improvement – this could be from degrading performance of reports or a report becoming business critical
- Business Case approved
- Potential of performance is highlighted – using the technical content the support/project team can verify the need
- The project or support team must prove that other performance options have been considered – this could include aggregates, redesign, caching, broadcasting
- If the other options will improve performance then they will be considered and implemented
- If not then the support team will identify the InfoCube size required in the BWA – this will enable the team to see if the BWA needs additional blades
- If the budget/funding is available then the Infocubes will be allowed into the BWA
What did the client get?
Now in essence we have the criteria and a robust process for the need of a BWA. The BW team have a clear idea of those Infocubes that will be indexed on the BWA and these can be fed into the sizing task undertaken by the hardware provider, or any further sizing/pricing.
Also through the technical content we have identified the reports that should see improvement from the implementation and allow us to create a performance baseline which will benefit during testing and benchmarking.