Even though BPC 7.5 was released, some customers are still using BPC 5.X. Some of them said "BPC is great product, we saved a lot of time!" but others said "it is too slow!”
What is the truth?
Here are some facts based on my experience.
First, some customers are still using same input schedule or template that they used. It means they might not get the benefit of new report function, which is EVDRE. Usually, EVDRE will process worksheet by worksheet therefore; it will show much better performance than EVGTS, EVGET and EVSND.
Second, Report structure might be wrong. One of customer reported that system can't process any query and send request due to CPU pecked 100%. After I investigated it, I found that Report structure was wrong. They wanted to get end of year value so they used 2008.TOTAL instead of 2008.DEC (YTD appset). Therefore, Analysis Services need to calculate all year value instead of calculating one month. After changed time dimension member from 2008.Total to 2008.DEC, CPU usage came back to normal and performance issue was solved.
Third, BPC 5.X version is based on Microsoft Analysis Services as OLAP Database. Therefore, most of query performance is depends on Microsoft Analysis Service's power. Unfortunately, SQL2005 has some performance issue when user defines COMPLEX dimension formula (Custom Rollup formula). In addition, there is some cache handling issue when MDX uses dimension property. These issues didn't exist in SQL2000 because Microsoft completely redesign Analysis Service engine in SQL2005. These performance issues improved a lot with SQL2005 Service Pack2 and Cumulative hot fixes. On top of that, SQL2008 improved more with 'block computation' and global cache handling even though MDX refers dimension property. As a result, if customer uses a lot of complex dimension formula in 4.2 with SQL2000 and upgraded it to 5.X with SQL2005, they used to complain its performance. Unfortunately, BPC 5.X doesn't support SQL2008. Therefore, if customer wants to use complex dimension formula and have a performance issue with it.
Here are the reasons why I strongly recommend upgrading it BPC 7 or 7.5.
1. SQL2008 MDX performance is much better than SQL2005 (Based on my test result, it is 4 times faster than SQL2005)
2. BPC Shared query engine has improved. If user has a big report, performance will improve a lot.
3. Fixed and enhanced a lot of functional features.
In my opinion, BPC is too flexible product so if our partners and consultants do not guide customer well, it will make the problem.
We can't use laundry machine to wash and peel potato. As a matter of fact, some customer used laundry machine for washing and peeling potato before. That's why you can see this kind of warning sticker now 🙂
