Misunderstanding of BPC
1. Function coverage
Misunderstanding : No, there is no standard function to implement what you require.
: Everybody knows there is no solution to cover all user requirements. But you can also find many cases that overcomes the barriers by developing. If you say it is wrong, since there might be broken when you upgrading, so you need to use standard feature only. Let me ask where is menu driven function or configuration just like ECC to fit into user requirement in BPC. If you think BPC is a just solution just like others such as ECC and others, No~!, BPC is development tool for planning and consolidation. It also provide API you can develop easily with others. Therefore most requirement can be covered by developing as various ways.
2. Performance problem
Misunderstanding : It is up to your hardware spec or network or something others.
: Most cases of performance problems are related to what you implemented. only few of them is really related to H/W(Customer already know spec might be the reason of the problem). Let me specify major reasons here.
1) Wrong usage of VBA
=> object coding should be used, memory object(just like array,range and so on) must be returned at the end of procedure or function.
=> Avoid using Loop. You might use filter and then copy & paste instead to reduce # of looping.
2) Do not use API which is provided
=> for instance, there are several types of refresh, you can define the purpose of refresh, then you can use appropriate API to increase performance
=> there is user guide of EPM-add in, you can find many API of BPC.
3) Uses lots of worksheet function, it increases memory usage of excel, even you close the workbook, the memory is not fully returned
=> should be coverted to variable(global)
4) Big size of Excel file
=> There are three sectors you need to manage, first of all, you need to minimize the size of workbook. It impacts the first prompting when you launch it. Delete all columns and rows(there is an option to control it). It means you might need to code expand for dynamic stuff just like time. for example, Time should be driven by version, the code should read version when opening workbook, then assign time which are related to the version before expanding.
1) Change hierarchy setting
=> It is fully related aggregation, BPC provides reserved field which is named ‘PARENTH1~N’, it makes all relatioship between members(code). In case of use this mechanism, SQE(share query engine) select MDX instead of SQL for reading parent. Now we can think what if we lead SQE read only SQL instead of MDX, then the performance dramatically improved. Then 1) you need to change hierarchy configuration, do not use reserved field, use property and make 1:N relationship which is ID : LEVEL N. And then you can aggregate yourself using logic. Then the parent records will be inserted to fact table, and SQL can read them. For example, you can read hierarchy of materials from MARA. PRDHA(? not sure it is exact name ^^) represents the level of SKU, you can find the rule from ECC and make chain to import the hierarchy to your product dimension automatically. If it is not possible, if the hierarchy is only in planning, you can easily determine how it works with your customer.
2) Report should be designed to satisify 1) above
=> Customer wants to deal with level as the analysis frame, therefore all members should be hierarchrized. This could be done by putting level property to the dimension, you can define level just like hierarchy.
3) Decrease number of dimensions
=> It is to decrease # of cartesian when reading, one of example, Flow and Account can be combined. Book value for holding, book value for selling.
4) In case of you cannot make combine dimension even there is + relationship becase of reporting purpose.
=> There is dimension property link option in report editor, you can assign ID of A dimension to B dimension’s property. Then it does not make NXM query.
5) Usually one big report is faster then multi reports.
=> In case of data from one cube but different definition, use exclued member option.
=> There might be a limitation, max-tuple. Could not find out how to overcome here, therefore you need to find out optimal solution based on the requirement.
1) All data related calculation should be done by logic.
=> of course there are some you can easily make by equation in excel just like %, it might be cheaper then logic. It is up to how it is calculate, you need to decide.
=> Script Logic vs HANA SQL, definitely I am proposing HANA SQL because SQL script is common for IT. Many customer do not want to use very specific script just like BPC Script Logic and FOX script.
=> Minimize ABAP, push all if it is possible to DB side. It is following the main cocept of HANA. If you use SQL script, then majority data handling should be done in SQL script.
=> Following HANA SQL script guide and performance guide. For instance, avoid cursor if possible, do set based query.
=> SQL script can be adopted by BADi or AMDP. (you can find details from another post in my blog)
=> If you choose to implement AMDP, you need to find out how to write back. There are two ways, which are using write-back function, using direct insert. In case of you choose BADi, no concern for writing back.
3. Stand alone(not integrated)
Mistunderstanding : BPC is not a part of ECC, therefore all code(or member) should be defined again by user.
: Yes it is right, BPC optimized for S4HANA(aka. IBPF) will solve your problem. BUT, actually it is possible to synch with ECC in other version of BPC. Of course you need to know ECC and need to develop several things. If you think it is too expensive, deal with it with your customer with plan to make them automate.
1) BPC classics, there is UI for handling master, also there is datamanager package for this purpose. But it is not enough since the functionality to get master from ECC is truly lack. You need to find out what table you should read, for instance MARA is MM table, you can find all information reg. the materials therefore you can select what you need, then make a IOBJ(info object), chain to synch with dimension.
2) Most important thing you need to confirm with customer is the process to deal with master code registration. All information except ‘plan code’ should be managed by ECC, then the info of code should be imported as planning step automatically.