Golden rules/Tips & Tricks for BW/BI Designing
After a laborious hard work I found some of the golden rules in designing in SAP Business Warehousing in short, SAP BW/BI. Hope this will provide some guidance to you, too, in designing.
I prefer the below audience to be always present in Business user report requirements.
1. One Functional consultant, this person is helpful for a BW person in understanding business into SAP terms (Mandatory)
2. One BW Sr. Consultant/Solution Architect, he is a required attendee to make aware of users about the BW/BI/BOBJ (Mandatory)
3. One Project manager (optional), to make us understand what is promised to business and what not.
Without the Functional consultant, BW Solution Architect (Sr. Consultant) it is not good enough to proceed in collection of requirements.
In my experience, I’ve seen a scenario where in some of the companies they would allow any fresh graduates/low experienced people for requirements which may lead to a disaster situation in designing. So, it has to be avoided always.
In the above case, if a fresh graduate is present as BW report requirement gatherer they may accept for some of those avoidable requirements like invoice number or document number in prompts; very detailed level of reports in to BI/BW/BOBJ; posting date, fiscal year, period in prompts and many more….
There are cases where the requirements made without functional consultants, this is also an avoidable situation, because functional consultants are the person who assist us on the feasibility of the requirements.
Like if there is any field which is not exist in source system as standard and which is business insisting as requirement, here functional consultant can play a role to guide us what is the labour work or logic for extraction of that field in to BW.
Based on the above examples, you might have got an understanding that what are the major concerns on requirement gathering.
After a completion of milestone of report requirement gathering, the actual part of designing involves from BW side.
This hurdle is not an easy task, we need to get precise data mapping as these are kind of building blocks for our designing. It’s like bricks to a building. If we don’t have precise data then we may end up in wrong design.
For example: The field name may say Invoice amount but the data mapping for this field end up from postings table then this would lead us in wrong prospect where we cannot end up in appropriate design.
Activity 1 – Defining an approach
In this activity we need to invest much time to come up an appropriate design for the reports for this case I would like to share my knowledge.
There are 2 types of approaches which I would like to highlight here
a. Top-Down approach
b. Bottom-Up approach
At first I would like to let the pros and cons of each approach and which would be an opt for any case.
a. Top-Down approach (from report level to source table level)
1. Easy & efficient when we opt if most of the business content satisfies our requirement.
2. Business content is easy available and pre-built.
1. Field descriptions in the report requirement may lead to wrong perceptions of grouping of reports. For Ex: field description saying as “Invoice amount” but data mapping is from Contract tables for some reports and for other from invoice tables.
2. Business content is available but which is not satisfying in our requirements even 50%.
3. Requires most of business knowledge to derive the grouping.
4. There may be chances of Duplicate of data across the cubes/data model for ex: Invoice amount or Invoice related info is used in most of the reports.
5. Need to create Custom datasources as standard may not satisfy, 100%.
6. More time taking process as understanding of business required.
7. No re-usability (as it is specific to group of reports)
b. Bottom-Up approach(from datasource/table level to report level)
1. Use of Standard Datasources.
2. No redundancy
3. Mainly used for SAP source system.
4. Can overcome the problem with difference in field descriptions (between source and reports).
5. Not much business knowledge required.
6. Less time taking process.
7. can easily satisfy reports pulling data from multiple sources.
1. combination of cubes (creation of multi-cubes) is challenging in our case.
Activity 2 – Identification of standard datasources (if source system is ECC)
After defining your approach then the next task would be to identify standard content datasources. Based on your report requirement and data mapping you can able to identify any standard datasources with the source tables mapped.
For examples: for invoice extraction (FI-CA), we can use 0FC_INVDOC_00; for postings we can use 0FC_BP_ITEMS; for payments extraction we can use 0FC_PAY, etc.
Activity 3 – Validation of datasources
once the datasources are identified then the next task would be validation of the datasource data with report requirement. If 80 to 90% of our report requirement satisfies with the datasource then we can use that datasource.
Activity 4 – Master data Identification
Master data has to be identified with the like attribute, text and hierarchy based on our requirement. for example: Company code, company code country, company code address, company code telephone number as master data attribute and Company code name as text data.
For Hierarchy, organization structure will be an opt example.
Activity 5 – Designing of cubes/Multicubes
Need to come up with a cube design like dimensions, key figures and multicubes based on our cube design.
Activity 6 – Prototype (Proof Of Concept)
In order to support our design prototyping of the design would be very advantageous which can give us 100% confident on our design and also can support ourselves in each and every scenario of the reporting requirement.
Based on the above considerations we would be able to provide a perfect BW design or data model.