Report Design Rules of thumb

Stating the purpose
What is the overall purpose of the report?
Who is going to read the report?

Determining the layout of the report
What is the report title going to be?
What identifying information is needed in the header and footer?

Finding the data
What data do you want to use in the report?
What specific data should appear in the body of the report?
Does the data exist or does it need to be calculated?
What types of fields contain data?

Manipulating the data
Do you want the data organized into groups?
Do you want the data sorted based on record or group values?
Do you want the report to contain only specific records or groups?
Do you want to summarize the data?
What information should be flagged on the report?
How do you want information flagged?

Determining printing area characteristics
In what order will the areas print on the report?
How often do report objects print?

Developing a prototype on paper

ITIL in BI

When I attended the ITIL Foundation V3, the first question I asked was how practical is ITIL in practise for Business Intelligence in a corporate organization.

In my opinion, in BI we can first zoom into these 3 areas - Change Management, Problem Management and Project Delivery. When we start to identify the essence of these areas, we are nominating ownership for each employee in the division. Without a sense of ownership, an organization may end up creating an unhealthy culture whether there will be one or two 'star employee' who holds all the important information without needing to share any of it to the peers or everyone is doing everything in a messy way based on adhoc instructions. 

What makes me want to look into these 3 areas are based on some idealogy as below. 

Change is the only thing that is constant. Hence change is our business as usual. BI change management has to take place in parallel with the business change. The execution of change management in BI is the result of impact asessment and this exercise take place at 2 main trigger points: 

1) when there is a new initiative. This can be:

- new products or service line

- groupwide consolidation

- new kpi or division

2) when there is a change in core functions. This can be:

- restructuring of organization

- change in service line and product offerings

- change in business operations

A good system and design has to take account into potential changes. Project Delivery should also take into the account of balancing the deliverables on time based on current requirements and scopes as well as to include the requirement and effort required to ensure system scalability in terms of business change. A smart angle to control the project from budget and business needs perspectives are to manage the later point through new phases or enhancement.

Problem is the only thing we see things we fail to see at the initial stage.In BI problem management or commonly known as Support, it is the execution of the result of root cause analysis.Root cause analysis reveals a lot about the reasons for data discrepencies or system flaws that goes all the way back to business requirements, data cleaniless, governance practices and best design practices.

Both of these fundamental ITIL procesess - Change Management and Problem Management tie back to the initial stage of the solution proposal stage in Project Delivery because from the root cause we can propose better solution based on lesson learnt and with impact asessment we see what need to take into account to avoid impacts on other areas such as data accuracy in reports or system downtime when there are new changes introduced. In a layman way of saying, Problem Management takes place after bad things had happened , Change Management takes place as a precaution measure before bad things happen.

Hence Project Delivery , Change Management and Problem Management are tightly coupled functions that need to co-exist and integrate well to ensure the success of any Analytical or BI operations in a large organization.

Tackling Master Data for New & Old Infoobjects

Master data in a global environment is controlled by one source to ensure the single version of truth. BI and ERP system has to share the same type of master data as it is only appropriate that the master data comes from the r/3 system and BI extract from there. In some cases where the master data is not in r/3 system, the BI extract it from the global master data database.

Scenario 1
In BI reporting, the version of master data reported depend on the period selection. If material group in year 2009 month 12 is reported as Semi Finish Good and in year 2010 month 1 is reported as Finish Good, how does BI actually cater for this scenario?

Scenario 2
If the material classification in R/3 takes from GS_XXX and now it's replaced by GS_YYY, how does BI changed according to the material attributes extraction?

In scenario across BI landscapes eg in different regions and one global BI instance in which all the regional master data flows up to global level, the master data has to be compounded with source system as the object might have the same name, eg. same material code may exist in two different regions, an error stack may have te same technical name in two or more BI instances.

Impact Assessment

Impact assessment(IA) is the process of identifying the future consequences of a current or proposed action.A Technical Design Authority needs to carry out IA every time there is a change or a new project landing into the BI system. It is a very challenging job as it really test your level of understanding, experience and thoughts. It's paper work but when we actually put our mind into the piece of work, good result is achieved in term of notifying the project team on the possible bugs resulted from the solutioning or simply pin point out mistakes in design and at the same time ensure the design adheres to the company standard guideline such as infoobjects,naming convention and architecture.

This document on impact assessment based on ITIL is worth a read.

Tips on IA for BI environment..
Give emphasis on the following when reviewing the blueprint:
1.Shared Objects

• Infoobjects
• Datasource
• Hierarchy
  
• Authorization objects
• Unit of measure conversion
• Currency exchange rate

2. Authorization

• Authorization objects
• Authorization DSO

3. Standards

• Global/regional infoobjects
• Projects/System infoarea
• Naming convention

4. Batch Schedule

• Master data and transactional data load not already exist in current system
• Availability of windows from R/3 for extraction and work process slots for extraction in BI

5. Housekeeping

• Data retention for PSA
• Data retention in reporting cube (consider factors like the needs of backposting and reload)
• Is CML (Corporate Memory Layer) used
• Compression and aggregates

6. Flat files

• Define ownership and frequency of load
• Define wheather upload of data is via FTP/Webdynpro interface or IP
• If FTP,define standard SAP directory and ftp method that does not violate security policy

7. Any potential data reload and methods to reload wrongly defined(mapped) data, eg. deletion by request or selective deletion depending on the key figure mode is in overwrite or summation.

Housekeeping & Database Sizing

In a BI environment, 3 items need to be considered for housekeeping and data retention period:
1) DSO change log
2) PSA change log
3) Logs of error stack

Some useful program:
RSPC_LOG_DELETE
RSPC_INSTANCE_CLEANUP
RSB_ANALYZE_ERRORLOG
RSBM_ERRORLOG_DELETE

This is a useful document to explain the deletion at database level.

Where-used list of an attribute (either display or navigational)

This program will list down all the queries and cubes that contains the infoobject. It is very useful especially if that object is a shared infoobject as we'd know which reports and cubes are impacted.

Code download

P/S table that store navigation attribute info : RSDCUBEIOBJ(infocube),RSDATRNAV(characteristic)

Shared Infoobjects

It is utmost important that a governance body is able to identify the shared infoobjects or any assignment ofthe shared infoobject to the same business rules definition as required by new projects or enhancement. This is important to keep single version of truth of the same business definition master data. One example of this is location. If the location refers to general geographical place, then it can fit any geographical definition like plant,endmarket or factory. But in certain reporting drilldown or mappings, a specific master data set is required hence plant is different from endmarket and they are different set of master data with respective infoobjects. This is important when assigning the attributes as attributes are technically correct for 1:m relationship but not m:n. Eg. Malaysia is an attribute of Selangor but Malaysia cannot be an attribute to Selangor if the business definition is not referring to national country but export country as Malaysia,India,UK could be the export country for Selangor and vice versa Malaysia can be export country to Selangor,Perak and Melaka.

Some of the important infoobjects to watch out for during a request for creation or change in infoobjects are:
1) Geographical infoobject such as region,area,cluster,endmarket and business unit
2) Material infoobject that has a lot of attributes
3) Regional infoobjects that push master data to global level has to be compounded with source system as the same master data can mean different thing in different region. Eg is material ABC is a Finish Good in Asia but it is a Raw material in Europe.

Inconsistent business unit (lowest granularity of geographical pointer)

The changes on the business unit will result in data issue both for master data and transactional data. This also impact authorization for user's access who refer to business unit in the centralized authorization DSO. From my understanding,there are a couple of scenario:

1) wrong business unit -> correct business unit (happen when there is alignment required especially for Marketing and Finance business unit hierarchy)
2) old business unit -> new business unit (happen when there is a change in business process. Eg. for endmarket level when China include HK& Macau)
3) obsolete business unit (happen when there is a change in business process)


Scenario (1) Needed for all reports and remapping/reload required. Master data ZG_BUSUNT (infoobject for business unit) should be populated to the correct business unit. ZG_LOC (infoobject for location - used in APO)should point to correct default business unit.Both transactional and master data should be in sycn in global and regional.

Eg:1338_Summerset to 1338_Sommerset

Transactional data (APO Demand Planning)

A mix of entries for both wrong and correct business unit which does not make sense.For planning year period 201401 forecast version 201103 , the data is planned under 1338_Summerset but it was planned under 1338_Sommerset for 201102.

If the report need to reflect the correct business unit, then there may be a need to perform a self-transformation (to map to correct mapping or with some logic included) in the dso level to offset the records that mapped to the wrong business unit and reload the impacted data with correct business unit. The new set of data will be reflected in the cube level as after image. This can potentially be a repeatable action on regional level depending on as and when there is inconsistent issues in business unit esp between APO,Marketing and Finance. A full reload on the impacted generated datasource is required at Global level.


Scenario (2) The end users have to decide whether they require the new business unit view to reflect historical data or not.

This is a challenge to get the same consistent data across region to global as well as an agreed way of viewing the APO report whether to view total volume only by the new business unit or to cater the historical snapshot view (view by both old and new business unit).

Finance reports may need to reflect data that require both old and new business unit mapping and it is important the derivation of business unit in mapping table is correct and in sync with the business unit master data. There is potential issues for some Finance reports that have SPLY (Same Period Last Year). Eg. business unit A01 exist on hierarchy version A but when it became obsolete or replaced by new business unit A02, hierarchy version B is created. When user view SPLY on current hierarchy version (version B), they won't have the comparison figure for A01.


Scenario (3) The business unit will appear on the hierarchy version it ties to (In other solutions, this can also be controlled by time dependent master data and hierarchy). The master data should remain intact and should not be blanked out.

UOM

Often in a complex multiple regional SAP instance environment that does not have a standard UOM defined or in the midst of doing so, BI is required to 'define' the standard UOM that is govern by an existing or new data standardization team.The standard UOM is specific to the company's business rules and not necessary refer to the international ISOCODE. In order to convert the transactional UOM to the standard UOM, the logic derived from the r/3 unit of conversion table such as matunit and t006. The standard UOM and base UOM also needs to be captured in the material group or material master data so that there is a base and target UOM that can be referred to for conversion.

Step 1,2,3 & Validation check + message class

The enhancement RSR00001 (BW: Enhancements for Global Variables in Reporting) is called up several times during execution of the report. Here, the parameter I_STEP specifies when the enhancement is called.

I_STEP = 1
Call takes place directly before variable entry. Can be used to pre populate selection variables

I_STEP = 2
Call takes place directly after variable entry. This step is only started up when the same variable is not input ready and could not be filled at I_STEP=1.

I_STEP = 3 In this call, you can check the values of the variables. Triggering an exception (RAISE) causes the variable screen to appear once more. Afterwards, I_STEP=2 is also called again.

I_STEP = 0
The enhancement is not called from the variable screen. The call can come from the authorization check or from the Monitor. This is where you want to put the mod for populating the authorization object.

Full text available here.

Global Exchange Rate Program

Exchange rate is shared by financial reports from different projects such as Management Information (MI) and Product Costing. Thus ensuring both are taking and defining the same rate is crucial. Rounding adjustment and inverse flag are some of the items that should be factored in when designing the reports.

In a global environment, the exchange rate is retrieved from a single point , eg. from ft.com.
The exchange rate is usually downloaded to r/3 and global transfer to BI. Either that, BI can also have its own exchange rate program that download the latest rate for Budget,COPA,COPC etc.

Global Authorization

I a global and regional BI system environment, it is crucial to have business access segregation through a set of controlled and standardized roles and analysis authorization. Hence the BI developer/gatekeeper and GRC team has to work closely to ensure the roles are used correctly and new menu roles and AA objects are introduced whenever there is a new set of reports developed. Portal team is involved in creating the menu link at portal as well.

One of the approach is to introduce the usage of a centralized Authorization DSO in which the user and their report access privileges are maintained in the DSO and the access check is executed  through CMOD whenever the report is run .The check aims to identify  the type of BI reports/solutions and the authorization analysis object the report is based on. The regional Authorized DSO is also replicated to the Global centralized DSO and this can ensure the users have the similar report access across regional and global level. The standard forms for user to request for new roles has to be in place first and existing old roles have to go through a cleanup to reflect the new set of standard authorization.