My client has a custom Infocube of Sales data with about 6 years of history. It has grown to be very large and their users are complaining about the performance of their Query workbooks.
I have noted that:
Transaction RSRV - [Database info] tells me that the FACT table has over 123 million records.
Transaction RSRV - [Database Parameters] tells me that there are 0 partitions.
We wish to achieve the following:
1. Migrate the existing cube and data to a new BW instance.
2. Improve the performance of the queries.
We should have some improvement with the new hardware but I am not sure if this will be enough. Our intention is transport the cube to the new instance and then Generate an Export DataSource to transfer the data between the two instances.
My questions are as follows:
1. When we have transported the cube should we look at changing the InfoCube to have partition defined (e.g based on Fiscal year)?
[InfoProvider>Change; Menu: Extras>Partition].
- With 6 years of data, I'm assuming we'd get roughly 6 partitions of 20 million records. I'm guessing that this would have to be done before we load the data. Would this noticeably improve the performance?
2. The other option I am considering is analysing what parameters are typically running within BEX Analyser and creating Query Views. We can then prime the OLAP cache with these Query Views.
Note: My client does not wish to create a new cube as they have hundreds of queries hanging off which they rely on for their business. Therefore, I have to work with what is there.
Many thanks for any help.