In this blog, I have reviewed some of the key design principles which should be followed when building dimensions in a SAP Business Planning & Consolidation (“SAP BPC”) environment. These design tenants should be applied irrespective of the nature of the models which are being built. Dimensions are one of the key building blocks in a BPC system and decisions made during the design phase will have implications throughout the build. Dimensions can be thought of in two groups; those which are mapped from existing master data objects e.g. General Ledger (“GL”) accounts from SAP ERP Central Component (SAP ECC) and those which are manually maintained e.g. AUDIT_ID for technical configuration related to BPC. The guidance below is more applicable to the latter and consequently more to dimensions in standard models.
UPPER/lower case and valid characters
- Since SAP Business Warehouse (“SABW”) is largely case-sensitive it is best practice to use UPPER CASE for all labels including:
- Dimension names
- Dimension member IDs
- Dimension property names
- Dimension property values (if they will be used for filtering or sorting)
- The list of permissible characters is most restrictive for dimension names where the only characters allowed are A-Z, 0-9 and _ (underscore)
- This results from a restriction on the valid characters in file names and would also apply to:
- Input Forms
- Transformation Files
- Conversion Files
- Some characters, including the following, have a special meaning in file or path names and consequently cannot be used in file names:
- / \ ? % * : | “ < > .
- Although the restrictions on dimension member IDs are slightly less restrictive ( = (equals), - (minus) &(ampersand) and . (full stop) are allowed) I would suggest that the restriction on valid characters for Dimension Names (A-Z, 0-9 and _) is used for all IDs.
- This will also reduce the risk of confusion with similar characters ( _-= )
- , (comma) should be avoided in IDs and descriptions as there may be confusion in CSV files
- The only exception I would propose is to allow . (full stop) in the time type dimension members.
- Dimension names (e.g. ACCOUNT) can be up to 16 characters and cannot start with a number
- The name should reflect terminology the business would recognise
- While retaining clarity, I would use short labels and ensure that any abbreviations are common e.g. PROFIT_CNTR, COST_CNTR
- I would also encourage using the singular style for the dimension name e.g. PRODUCT, EMPLOYEE, ACCOUNT rather than PRODUCTS, EMPLOYEES, ACCOUNTS. By using a common style, we will assist users in development and end user tasks.
Dimensions cannot have the following names as these are reserved system IDs.
Dimension Members and Descriptions
- Dimension member IDs can be up to 32 characters
- As discussed above they should be UPPER CASE and restricted to the characters A-Z, 0-9 and _
- Dimension member IDs cannot be used more than once in a dimension; they must be unique
- IDs should also not be duplicated across dimensions; this will confuse member recognition.
where a cell is populated with a member id automatically based on a partial input
- Duplicating descriptions can also cause issues with member recognition
- Consideration should be given to where descriptions will be used in assessing how long they should be; typically, longer descriptions are used in rows than in columns. An additional member property to hold a short description could be added and used where screen estate is limited
- Dimension member descriptions can be up to 60 characters
- Common abbreviations should be used in descriptions for consistency and clarity
Structured Dimension Member Naming
- The following methodology should be applied to dimensions as a default. There are, however, a number of dimensions where it may not be appropriate including:
- TIME type dimension
- CURRENCY type dimension
- CATEGORY type dimension
- Each dimension should be allocated a prefix and this should be used for all dimension members with the exception of those with the TOTAL, ALL and NO prefixes
- For example, members of the PRODUCT dimension could have PR_ prefix giving members such as PR_055010
- For dimensions based on a BW load, the prefix could be added in the transformation file
- The benefits of adding this prefix include:
- Guarantees unique dimension members across dimensions
- Assists in recognition of members and improves ease of system development
- Prevents numeric codes, particularly with leading zeros which create issues in Excel
- There is a trade-off between readability and length of member ID which should be considered. PRODUCT could have a prefix of P, PR, P_ or PR_
- A variation of this naming convention could be used for the account dimension where prefixes are used to designate account groups. e.g.
|AT_||Tangible Fixed Assets|
|AI_||Intangible Fixes Assets|
- Dimension property names can be up to 20 characters
- They should be UPPER CASE and restricted to the characters A-Z, 0-9 and _
- Dimension property values have a user defined length
- You should document dimension properties, particularly when they have a limited number of options and are used for filtering
- BPC dimension hierarchies can either be based on BW or manually maintained. Large, rapidly changing hierarchies are typically driven from BW
- There is likely to be a manually maintained member which is used for data for which the dimension analysis is not relevant. For example, data for which a PRODUCT analysis is not relevant would be mapped to NO_PRODUCT
- To encourage symmetry in the dimension design, I would suggest a standard dimension structure based on the example below:
- The hierarchy below ALL_PRODUCT would reflect the required functional hierarchy
- By using a common hierarchy structure administrators and users are always aware of the function and content of the TOTAL, ALL and NO dimension members
- The example above refers to the main hierarchy, but a similar approach can be applied for additional hierarchies; two cost centre hierarchies could be required to give both a regional and a business view. In this case parent members could be:
Master data can be loaded from several sources, including flat files and BW InfoObjects, with or without hierarchies. These will populate the dimension members within BPC. During this process, transformation and conversion files may be necessary to control the process.
Depending on the version of BPC, there are different ways of loading the master data.
For the standard version, the packages which are available to load the master data and hierarchies are:
|Import Master Data from Flat File||/CPMB/IMPORT_MASTER|
|Load Master Data from BW||/CPMB/IMPORT_IOBJ_MASTER|
|Load Hierarchy from BW||/CPMB/IMPORT_IOBJ_HIER|
For BPC embedded version, the master data is loaded using the BW process chains like any other BW master data and the ETL functionality will be leveraged for transformation and conversion if required.
The guidelines discussed above are general principles which should provide a solid framework for the design of your dimension library. There will, however, be questions on how to best to apply the advice in specific circumstances. I have always tried to explain the rationale behind the suggestions and this should allow you to challenge design decisions which you feel are sub-optimal.