BI Project Management

(1)

Business Intelligence Project

Implementation And Management

Do’s and Don’ts with Best Practices

(2)

B

U

S

I

N

E

S

I

_I

N

T

E

L

I

G

E

N

C

E

P

R

O

J

E

C

T

I

M

P

L

E

M

E

N

T

A

T

I

O

N

&

_&

M

A

N

A

G

E

M

E

N

T

D

O

’

S

A

N

D

O

N

’

T

S

W

I

T

H

B

E

S

T

P

R

A

C

T

I

C

E

S

Author: Chaitanya Bhure Service Offering(s): Consulting Publish Date: 15 ‐ October ‐ 2010

(3)

1.0 EXECUTIVE SUMMARY 5 2.0 REQUIREMENT GATHERING AND ANALYSIS 7 2.1 System Requirement Study / Business Requirement Study 7 3.0 DESIGN PHASE 10 3.1 Report Rationalization Document (RRD) 10 3.2 Report Definition Document (RDD) 11 3.3 Universe Definition Document (UDD) 12 3.4 Logical Data Model (LDM) 15 3.5 Mapping Design Document 16 3.6 Report Scheduling Specification 17 4.0 DEVELOPMENT PHASE 18 4.1 ETL Development 19 4.2 Universe and Report / Dashboard Development 21 4.2.1 Universe Development 21 4.2.2 Webi Report Development 23 4.2.3 Dashboards Development 25 4.3 Unit and System Integration Testing 27 4.3.1 ETL Testing 27 4.3.2 Report Testing 28 5.0 DEPLOY PHASE 28 5.1 Perform UAT on Development environment 29 5.2 User Training 30 5.3 Prepare the BODS XI 3.1 Production Environment 30 5.4 Prepare the BO XI 3.1 Production Environment 31 5.5 Migration of Development to Production 31 5.5.1 ETL Migration 31 5.5.2 Report Migration 31 5.6 Review and Validation 32 6.0 EVOLVE PHASE 32 6.1 Knowledge Sharing 33 6.2 Post Production Support 33 6.3 Exclusions 33

(4)

7.0 PROJECT ORGANIZATION 33 7.1 Vendor’s Roles and Responsibilities 34 7.2 Customer’s Roles and Responsibilities 37 7.2.1 Customer Responsibility: 38 8.0 DELIVERABLES AND ACCEPTANCE CRITERIA 39 8.1 Deliverables 39 8.2 Acceptance Procedure and Criteria 39 8.3 Sign‐off the User Acceptance Test (UAT) 40 8.4 Requirement Changes 41 8.4.1 Change Control Process 41 8.4.2 Response to Request for Change 42 8.4.3 Customer Approval 42 8.4.4 Implementation 42 9.0 PROJECT COMMUNICATION & CONTROL MECHANISMS 43 9.1 How does one do Basic Level BI Project Management? 44 10.0 RISK MANAGEMENT 47

(5)

1.0 EXECUTIVE SUMMARY

This document details about the learning’s of one of the major fixed bid Business Intelligence Project Implementation for one of the major FMCG player from the Indian Market. This type of major implementation requires careful monitoring process and clear definition of milestones. Reaching a milestone at the prescribed deadlines requires the milestones to be broken into various tasks and the resources that are going to complete those tasks.

For tracking the completion of various tasks and reaching a milestones on said deadline requires a Project Management tool like MS Office Project Professional Edition which gives various analysis like which task missed the deadline and why. This will avoid the project delay and dispute with customer. These tools will also ensure proper change control mechanism if client suggests some changes which needs to be incorporated in the project plan and new delivery schedule is agreed upon with customer. In the design phase tools like ERWIN should be used instead of Excel for Entity‐Relationship Diagram (E‐ R) which also ensures reduction in manual work and delay.

While implementing the above BI project, manual monitoring was done instead of proper project management tool which was time consuming with no proper analysis of tasks and milestones achievement. This resulted into dispute and delay with both the vendor and client companies blaming each other for delay. Proper change control mechanism also could not be ensured because the process was all manual and a lot of time was wasted in estimation of efforts and convincing client about the change.

It should also be noted that before you submit the proposal for such large BI implementation project, proper system study should be undertaken so as to properly estimate the efforts and commercials. If customer ask to give them the proposal within short span, the vendor company should convince customer for system study which will help in avoiding future issues on project commercials, efforts, delivery and number of requirements.

Due to ever changing information needs of an organization ranging from reporting to business modeling, and the data modeling, design keeps on changing. If we need to have the information management related to a new set of data elements, the entire chain of BI may be impacted. This will transcend across source system mapping, ETL, data‐warehouse modeling, OLAP modeling and metadata updation.

(6)

There can be number of issues which can result into delay and affect project profitability. Some of the major issues are listed below which are elaborated in multiple sections of this document. • Efforts and Commercial Estimation without proper System Study • End requirement changes • Requirement Logic change • RDD without proper logic for some reports • Data Transformation Logic change • Historical Data Load • Quality & Consistency of Data • Source Connectivity Issues (SAP) • Shared Folders Permission Issues • Disk Space Issues related to Target Data Warehouse • Excel Source Issues (Path Permission, Formats & Data) • Delay due to User UAT • Delay due to Inputs from User required for output • No usage of Project Management Tool • Delay due to multiple training sessions on BI tool for business users • Lack of commitment from User community (Resistance to Change) • Frequent resource change deployed on project

(7)

2.0 REQUIREMENT GATHERING AND ANALYSIS

Analyze the existing systems and the requirement of the Customer that will help in driving the Business Intelligence Solution implementation in detail, and use those requirements to guide the analysis of the reporting, infrastructure, data and user interface requirements. Customer shall provide necessary support and information required in order to complete the tasks specified in the scope.

2.1 System Requirement Study / Business Requirement Study

Do’s

This document is prepared after understanding Business Requirements that are driving the whole project. Use these requirements to recommend the infrastructure architecture. • Understand the existing Business and terminologies

• Understand and review the Reporting requirements by studying the existing reports created by the Customer and understanding of related Dimensions and Measures. • Understand the existing data sources.

• Understand the various crucial parameters defined by studying the existing reports generated which are already provided by the Customer. • Analysis of Database Source Structure and source to table mappings. What types of information must you gather in the preliminary survey? At a minimum, obtain general information on the following from each group of users: • Ask very specific questions rather than high level. • Mission and functions of each user group • Computer systems used by the group • Key performance indicators • Factors affecting success of the user group • Who the customers are and how they are classified • Types of data tracked for the customers, individually and groups • Products manufactured or sold

(8)

• Categorization of products and services • Locations where business is conducted • Levels at which profits are measured‐per customer, per product, per district • Levels of cost details and revenue • Current queries and reports for strategic information

The vendor’s primary goal in the requirements definition phase is to compile information packages for all the subjects for the data warehouse. Once firmed up the information packages, the implementing vendor will be able to proceed to the other phases. Essentially, information packages enable implementing vendor to: • Find out the Data Sources for the Data Warehouse data. • Define the common subject areas • Design key business metrics • Decide how data must be presented • Determine how users will aggregate or roll up • Decide the data quantity for the user analysis or query (Historical Data) • Decide how data will be accessed • Establish data granularity • Estimate data warehouse size • Determine the frequency for data refreshing • Ascertain how information must be packaged • Get a list of reports that is supposed to come out of the new data warehouse.

At the conclusion of this phase, the complete understanding of the systems and mapping from the source systems will be done.

Cost‐Adherence:

There are various elements of cost linked to BI/DW projects. Cost mentioned here is the project related cost which needs to be considered in this phase of project and proper cost justification needs to be given to customer. Take all the points into consideration while arriving at project commercials. The points are given below: • License Cost (Business Objects Cost if licenses are not purchased) • Hardware cost (Vendor is not having the requisite hardware for BI) • Project Management Cost • Scoping and analysis cost • Modeling cost • Design and Development cost

(9)

• Testing and implementation cost • Employees Charged time • Others The cost management is an important piece for BI project implementation with license and tools cost being only a smaller piece.

Don’ts

• Going overbroad in capturing the requirements. This should be avoided since you are going to document it. Understanding of reporting requirements and feasibility of delivering those reports to the end customer by doing proper source system study helps in setting the expectations right and also helps in estimating the development efforts and commercials of the project. • Documenting the requirements where you have doubts about data availability, tool constraints etc. Avoid this since this document becomes the guiding principle for the project development where customer sign‐off is required. • Non ascertaining data quantity for the user analysis (Historical Data) in this stage. Ascertain the data quantity in this stage itself and inform the customer that if historical data is for multiple years then it needs to be consistent and clean and the vendor is not responsible for cleansing and consistent data as it is a separate activity itself which needs to be estimated for efforts and commercials avoiding future dispute and delay.

Summary

Requirement analysis and gathering is a bit tough as client might not be clear with his/her requirements. Its implementation vendor expertise in the design process and prior experience which help to analyze the requirements and put it on paper. Once everything is clear make sure to get a sign‐off from the client on the requirements to avoid dispute.

(10)

3.0 DESIGN PHASE

The requirements identified in the previous phase are further analyzed to produce detailed design specifications for the architecture, user interface, reports, data model. A detailed Test Plan is produced in conjunction with the design specifications, which outlines unit, system, and user acceptance test scenarios.

3.1 Report Rationalization Document (RRD)

There are requirements provided by the end users to the implementing Vendor by the Customer during the business requirement meetings. Each of these requirements have been documented and approved by the end users. The purpose of a rationalization activity is to further get a detailed understanding on the business logic of each of the requirements and suggest to the end users based on past experience and expertise, as to which reports or requirements are similar in nature and which are too huge to be accommodated into one BI dashboard / report. An exercise of rationalization will enable the implementing Vendor to proceed with the design phase of the project and also help the end users meet the informational requirements with minimum number of BI reports / dashboards to be referred to.

Methodology

(11)

The Rationalization activity will be initiated by the implementing Vendor and the following methodology will be followed throughout the process to arrive at a conclusion on the number of reports and dashboards to be developed for each department • The implementing Vendor will create a matrix containing the data elements required in each and every requirement given by the respective departments • Based on the data elements so captured, the team will arrive at a conclusion whether certain reports are to be merged to provide more information in a single document or needs to be split to be able to make the requirement more readable and user friendly • Once the activity is completed along with an explanation of the reason for report splitting and merging, the same information will be shared with the business users for their approval • Once approved, the implementing Vendor will then proceed ahead to initiate the creation of the Report Definition Document which will contain the necessary information about every rationalized requirement in terms of type of tabular data, user actions, graphs, hierarchies, drills, slice and dice and so on • The implementing Vendor will also create patterns of reports and link the Report Definition Documents to the respective patterns so that the end users are able to visualize the type of report that will be developed • Lastly, the requirements, design and the definition of the requirements will be fixed and developed accordingly by the implementing Vendor. Sample LDM is attached below PA_Technical_RRD_B _V_1.1.doc

3.2 Report Definition Document (RDD)

Do’s

Before starting the Implementation of Data Warehouse, we need to understand the business requirements clearly from the user in terms of how exactly the business user community see their reports at the end of the day.

(12)

• The next step is to understand the Business logic with the functional people. The business logic should be properly documented for each and every requirement (reports/dashboard) and sign‐off should be taken. • Once through with user requirements and business logic of those requirements the next step is creating the Report Definition Document (RDD) and thoroughly checking whether all the elements are captured or not according to the discussion. • The Report Definition Document will reflect the no of requirements (Reports and Dashboard) for which the implementing vendor will provide the development efforts and commercials. • Taking the Sign‐off of RDD from the business user. • Any changes coming while system development which is not captured in signed‐off RDD will be a Change Request and should be dealt with change control mechanism. • If business user insists on change which is important to him and not captured in requirement gathering should be taken as change request and development and commercial efforts should be estimated with proper escalation to project owner from customer side. • Once the change request is approved then it should be incorporated in project plan and project delivery schedule should be updated with new timelines and milestones.

Don’ts

• After taking the sign‐off on RDD, allowing addition in the number of requirements (Reports and Dashboard). This should not be allowed as it will have commercial implication as it is going to extent the project delivery. Any additional requirement should be treated as change request. Sample LDM is attached below PA_Technical_RDD_B _V_1.0.doc

3.3 Universe Definition Document (UDD)

A universe is a semantic layer or in more familiar terms a data abstraction layer which is built with the Business Objects Designer tool. This semantic

(13)

layer is where you define your business objects which are essentially encapsulated snippets of SQL that when properly built express a leggo‐like bit of business logic that can be presented in the reporting tool or through an application be used and reused to build reports. The genius of this is that this data abstraction layer sits between the database and your reports or application. This means that even if the database structure changes that instead of being required to change what could be hundreds or even thousands of reports throughout an organization to accommodate the change you instead make the changes in the universe and those changes are automatically passed through to all the reports. This has numerous benefits one being that it allows a much more flexible environment for preparing for and facilitating change in an organization. Prepare and Analyze Before you touch the Universe Designer • Well, in most of the BI projects the developers make this mistake and they jump directly on designer. But this might end up in future Universe Maintenance problems. Understanding the data source on top which universe to be developed is very important. The Universe designer must understand the tables, type of data stored in those tables, relationship between tables, Business terms and their meaning, any specific formula which will be used to derive the measures. Understand Reporting need and what all tables are required to feed the data to reports. Plan the Universe. • Before you actually start building the Universe, plan it well in advance. Identify number of universes required for reporting. Identify measures, dimensions and details objects. Try to document it well and in detail. This would be a Universe blueprint which is called Universe Definition Document and which will help while actually designing the universe. Implementation or Actual Design of Universe. • Once you have completed first two stages, start building the universe using Universe designer. The planning Universe Definition Document created in planning stage will help you a lot while building the

(14)

universe. While building the universe it’s always better to create a Unit Testing document for universe and create unit tests for every object you create in universe. Test each and every object in universe as soon as you create it. This will minimize the possible errors and bugs. Frequently use the universe integrity test tool to identify SQL traps, join path problems. Test once it’s built. • This is one of the very important stage of Universe building process. Have very detailed universe testing plan ready for this stage. Test universe against different scenarios, for SQL traps by creating sample reports, test measures, compare the data against manual SQL data. If possible ask few business users to use the universe for creating some sample ad hoc reports. Deploy it. • Once all above stages are completed and well documented, it’s time to deploy it for actual use for creating reports. You can deploy universe using BIAR tool. If production Business Objects Server is available you can directly export the universe to production servers from development server.

(15)

Maintenance. • Since nothing is prefect issues are supposed to come frequently after deployment. Change the Universe for possible resolution and re‐ deploy it. Make sure you document every change you made in universe against the change request.

3.4 Logical Data Model (LDM)

• Once RDD is finalized identify the Entities, Attributes and the Grain level. • Design the Entity‐Relationship Diagram (E‐R) by giving the appropriate relations of the tables. For this we can use ERWIN Tool but currently we have designed it in the Excel Sheet which is again time consuming and requires regular updates manually. • Based on this we will start to design the Dimension tables by capturing all the elements by maintaining hierarchies which are used for Reporting purpose useful for business users to analyze at the lower level. • Next step is to design the Fact tables by maintaining the key relationship from Dimension tables. • For doing the above LDM we have developed an Excel sheet with prescribed format.

Do’s

• The data types which coming from the source tables have to have the following nomenclature which we will be implementing for the target database as shown below. Also for every column in the target table which we design have to follow the given below column prefix.

SNO NATIVE TYPE SQL TYPE COL PREFIX

1 CHAR VARCHAR chv_

2 NUMC NUMERIC num_

3 DATS DATETIME dt_

4 TIMS

5 CUKY VARCHAR cid_

6 CURR NUMERIC amt_

7 QUAN NUMERIC qty_

8 UNIT VARCHAR uom_

9 DEC NUMERIC int_

10 LANG VARCHAR lid_

(16)

Don’ts

• Once the LDM is created the users (Business / Technical) are given permission to change the structure of the target tables with the given nomenclature mentioned above. It should not be allowed. • If the users change the structure then it will have the impact on the Universe and Reports which can result into project delay and project commercials and should be taken as change request with the requisite commercials. Sample LDM is attached below LDM_V1_0.xls

3.5 Mapping Design Document

• Creating the Mapping Design Document in excel sheet where the given below details are elaborated. 1) Source Definition (Where it is exactly coming from i.e. source database name, source tables, which server etc 2) Transformations (Which transformation has to be used to load the data into target tables and what was the logic to implement the ETL flow) 3) Target Definition ( Where we have to load the transformed data i.e. target database name, target tables, which server etc) • Once the above process is completed by designing in the Excel sheet, it’s been given to the development team for the Data Warehouse Implementation. The Excel sheet which is prepared is called the Mapping Design Document which is also submitted to the technical users for checking the business logic and once it is approved, it should be given sign‐off by the respective users. • Once the Development is over, ETL Unit Testing has to be done.

(17)

Do’s

• Source related Inputs (Server Name, User Name / Password, Source Database Name, Permissions) has to be given by the customer. • Transformation Logic needs to be finalized by the customer. • User Name / Password changes by the customer at the source level should be immediately intimated to the implementing vendor, so as to modify the credentials at the ETL level by the developer.

Don’ts

• Once the ETL mapping is finished and approved by the customer accepting changes from the customer end in terms of adding source tables, adding columns, changing the business logic to existing mappings. This should not be allowed since it will impact the project delivery. If user insists on change it should be taken as change request and change control mechanism should be followed. Sample LDM is attached below Source_Target_Map ping_V.1.0.xls

3.6 Report Scheduling Specification

Scheduling Reports in Web Intelligence

As per the requirements from the business users the reports can schedule daily,

weekly, monthly etc or the users might refresh the report on their own. Below

are the given scheduling steps needed to be followed.

(18)

• Before scheduling the reports logon to central management console. • Click on central management console. Click on servers. • Click on web intelligence job server to configure. Click on destination tab. • Check on unmanaged disk and click on enable button on the right. Click ok to enable the selected item. • Go back to central management console and re start the web intelligence job server. Open the report to be schedule. Click on schedule option in the report. • Select the format as Adobe Acrobat. Expand the destination option Uncheck “use the job server Default” • Give the destination location for the file. E.g. D:\\Scheduled. Click on schedule button below right to run the job.

4.0 DEVELOPMENT PHASE

The specifications completed during the Design phase are used to install and configure the architecture, access the data, develop the user interface and create the reports. The Build phase is where the system is configured to fulfill the requirements defined in the previous phases.

(19)

4.1 ETL Development

The Business Intelligence project where data warehouse development is integral part, the initial requirement gathering should be precise and exhaustive, since the ETL function in a data warehouse are most important, challenging, time‐consuming and labour‐intensive which determines the project profitability. Data extraction is complex because of the disparate source systems; data transformation is difficult because of the wide range of tasks; data loading is challenging because of volume of data. The given below are the ETL steps: • Based on LDM, creation of Physical Data Model takes place which means determine all the target data needed in the data warehouse. • Loading Master Tables: Loading and grouping data into Master tables • Creating Staging Area which will have Transformation, Aggregation and Loading as per Business Requirement. • Creating Target Area which will have Transformation and Loading as a process • The data loading function relates to the initial load, regular periodic incremental loads and full refreshes from time to time.

Do’s

• The components of the dimensional model are derived from the information packages in the requirements definition (System Study) • The entity – relationship modeling technique is not suitable for data warehouses; the dimensional modeling technique is appropriate • The STAR schema used for data design is a relational model consisting of fact and dimension tables. • The fact tables contain the business metrics or measurements; the dimensional tables contain the business dimensions. Hierarchies within each dimension tables are used for drilling down to lower levels of data. • STAR schema advantage is: easy for users to understand, optimizes navigation, most suitable for query processing, and enables specific performance schemes. • Slowly changing dimensions may be classified into three different types based on the nature of the changes. Type 1 relates to corrections, Type 2 to preservation of history and Type 3 to soft revisions. Applying each type of revision to the data warehouse is different. • Large dimension tables such as customer or product need special considerations for applying optimizing techniques. • Aggregate or summary tables improve performance. Formulate a strategy for building aggregate tables.

(20)

• While development if development teams come across certain product limitation then it should be immediately communicated with the technical user of the customer and decision need to be taken with mutual consideration especially with the cluster tables in SAP. • If there is problem in data extraction from source system like extraction of data from purchase registered tables of SAP then Z‐table can be provided by the customer which the ETL team picks as source and push the data to target database. The schedule to update the Z‐table is entirely customer responsibility against which our ETL jobs are schedule which should be notified to the customer in such situations. • If historical data is part of such project then it is duty of customer to provide clean and consistent data and vendor would not be responsible for any data quality or consistency issues. It should be informed to customer prior to implementing the project and proper expectations needs to set. • If the source is excel then vendor shall standardized those excel sheets and submit it to the user for data population in the prescribed format only. Users should be intimated not to change the format while populating the excel sheet. If user even after repeated communication alters the format of standardized excel sheet which can result into project delay due to error in ETL load. Such instances should be recorded and notify to the Project Owner (Customer) for possible commercial implications. • Keep buffer for activities like ETL: ETL (extraction, transformation, loading) is the most complex work one needs to do in the Data‐Warehouse. One should play more realistic, when estimating the time for ETL.

Don’ts

• “Snowflaking” or creating a snowflake schema is a method of normalizing the STAR schema. Although some conditions justify the snowflake schema, it is generally not recommended. • Miscellaneous flags and textual data are thrown together in one table called a junk dimension table. • Changing business logic during the ETL development for any business area which is completed and the reporting developers have already developed universe and reports / dashboards. If the users insists on changes in the business logic (ETL Flow) the vendor should be considering it as a major change request and appropriate efforts and commercials need to be estimated since this will delay the project and affect the project profitability. • Including data quality scope in such type of projects even critical from customer point of view. Data quality problems run the gamut of dummy values, missing values, cryptic values, contradicting values, business rule violations, and inconsistent values and so on. Data quality is project in itself and proper customer expectation should be done prior to bidding for such projects.

(21)

4.2 Universe and Report / Dashboard Development

4.2.1 Universe Development

Do’s

• Development of Semantic Layer (Universe(s)) as per the SRS, RDD and UDD Documents. • Development of reports as per the business requirement. • While developing the Universe login ID, password, connection string to the database and database name to be provided before development. • Relationship between facts and dimensional tables should be captured in a UDD which the development team can use in the universe development and which was not implemented in the current project. • While universe development understanding the tables and their names, their relationship (Joins), defining joins, context and resolving the loops are major activities. • For classes and objects the business names should be define in concurrence with the business users • Objects description need to be captured in the RDD • Proper Universe versioning need to be maintained. • Universe back‐up (biar file) need to be taken every day as best practice in case of any untoward incident like server problem/crash etc • Proper network connection is the responsibility of the customer for smooth development.

Don’ts

• Forgetting to take the user sign‐off once the Universe development for a particular department is completed. Take the sign‐off so to take the concurrence on business names and relationship between tables from the business users which was not practice in the current project. • Forgetting to provide the sample reports developed on the Universe which completed from development perspective. Sample reports need to be provided to the user for testing the universe which was not a practice in the current project to take the concurrence on report results. • Changing table structure developed in target database while doing data modeling should be avoided as it will have a major impact on the Universe and reports already developed resulting into project delay.

Universe Development Guidelines and Best Practices

Gives the basic guidelines/practices that could be followed in any Universe Design

(22)

Connection

• When using a repository, always define a SECURED Connection to the Database. • Use the Universe Property panel to define the Universe Use and Version (last update). • Define the Connection Name that helps for Easy Database Identification.

Class

• Define Universe Classes / Subclasses as per the business logic & Naming Convention. • AVOID Auto Class generation in the Designer. • Give description for the use of each Class/Sub‐Class. • Avoid deep level of subclasses as it reduces the navigability and usability.

Objects

• Object to be used in calculation HAS to be Measure Objects. • Object to be used in Analysis HAS to be Dimension Objects. • Give description for the use of each Object. • Include an Eg. in the description for Objects used in LOV. • Do not set LOV Option for each Dimension. Use it only for required Objects, esp. those to be used in Report Prompts. • Keep "Automatic Refresh before Use" option clicked for LOV Objects • If LOV is editable by the user, provide a significant name to List Name under object properties. • All the measure objects should use aggregate functions. • Avoid having duplicate Object names (in different classes).

Predefined Conditions

• Give description for the use of each pre‐defined condition. If condition is resulting in a prompt, make sure associated Dimension Object has LOV.

Tables

• Alias Tables should be named with proper functional use.

(23)

• Arrange the tables in the Structure as per Business/Functional logic. This helps other Universe users in understanding.

Joins & Context

• AVOID keeping hanging (not joined) tables in the structure. • AVOID having joins that are not part of any context. • Give proper functional naming to the context for easy identification. • AVOID having N: N joins.

Import/Export

• Make sure of the path for Import, which usually is always in the Business Objects' Universe folder. • LOCK the universe if Administrator/Designer does not want any user to Import/Export. • DO "Integrity Check" before Exporting the Universe. • Good to have correct folder structure, so that you can have a secured environment.

Migration

• Better take a backup of the repository and then proceed with the migration

4.2.2 Webi Report Development

Do’s

• Once the Universe development is frozen and sign‐off is taken then start the report development. • Standard template of reports need to be finalized in concurrence with the business user which includes logo, report title, refresh date, prompts, filters, format of the page i.e. alignments etc which should be captured while documenting the RDD. • The above standard template needs to be provided to the development team prior to report/dashboard development. • Report development depends on RDD and standard templates of report/dashboard requirements. • Report development should be done in concurrence with RDD only, any deviation needs to be raised and taken into change request.

(24)

• Unit Testing needs to be done once the report development is complete which nothing is but data validation and report format testing as per the RDD. • Quality need to be maintained as per standards like proper fonts size, colors, headers of the table, proper alignment, sub‐total figures, total figures etc. The quality check should be done properly prior to releasing the reports for UAT. • Once the unit testing and quality checks are performed by the development team the reports / dashboards are released for UAT. • UAT schedule should be communicated to the customer / business user well in advance and acceptance need to be taken. Once accepted the business users should adhere to the UAT timings otherwise it will impact the project delivery. • While doing the UAT if a report or tab not found to be in concurrence with the RDD by the user then sufficient time to be taken to incorporate the same and again the report should be released for UAT. If user does not respond within stipulated agreed time then those reports would be considered as accepted and would be released for production. • Once the UAT is successfully completed for a particular department, immediately the training session needs to be conducted for the user from that department and sign‐off needs to be taken on report sign‐off template. • Productionisation of UAT completed report and user sign‐off of productionised reports. • Once reports are productionised on particular date the support starts on that date itself. The support is given only for the existing reports. • If some reports are taking longer time for the refresh then it should be properly communicated to the customer and a decision needs to be taken about those reports with mutual understanding. • While development if development team comes across certain product limitation then it should be immediately communicated with the technical user of the customer and decision need to be taken with mutual consideration.

Don’ts

• Taking changes while doing the UAT not mentioned or not captured in the RDD. This should be avoided and should be taken as change request and proper communication on email about the same should maintain. • Doing changes to existing report while on support. This should be avoided and escalated to the customer and proper efforts and commercial need to be communicated. • Doing new report development while on support. This also need to be avoided and such activities should be communicated and efforts to be estimated for commercial and time duration thus avoiding the delay. It also helps in managing the original project scope. • Providing support by the existing development team on project. This is not best practice and support should be provided by the separate team and not by the existing development team as it may have an impact on ongoing project development and can result into project delay.

(25)

Webi Report Development Guidelines and Best Practices

Given below are the basic guidelines/practices that could be followed in any Report Design and Development.

General

• Give meaningful names for the report tabs • For complex reports, keep overview report tabs explaining the report • Use the report properties to give more information about the report data providers • Each data provider should be given a name that reflects the usage of the data it’s going to fetch. • Select Objects in such a fashion that the resulting SQL gives a hierarchical order of tables which helps to achieve SQL Optimization. • Avoid bringing lot of data into the report which will unnecessarily slow down the report performance.

Report Variables

• Follow the naming convention of "var_" as prefix to each report level variable. This helps to identify Report Variables different from Universe Objects.

4.2.3 Dashboards Development

Dashboards are the new face of BI. For a face to be affable‐‐and for a dashboard to be friendly to your business‐‐there are a few requisites that need to be in place. Business intelligence dashboard is not all that different from the dashboard in your car. To be useful, it must make you drive better, keep you comfortable and tell you when you're running out of gas, but without distracting you. Let's look at the top‐five do's and don'ts of dashboard implementation as given below. Do 1: Let the Dashboard Be Business‐driven and Focused Ask Business Users: what competitive goals are you trying to achieve through this tool? What specific processes are you trying to make more efficient? What critical information are you trying to make more readily available and why? Be ruthlessly specific. The more surgically you zero in on precise tactics, the better your chance to achieve your strategy of getting proper information.

(26)

Example: you want the inventory of the top‐10 SKUs to always remain optimal, so that you're not out of goods while never getting overstocked. You set up a dashboard that shows this information in intuitive eyeful‐‐in graphic form and of course in real time. Don't Don't make the dashboard into a less unprofessional version of solitaire. Too much freedom and too little focus, and your users will spend time on it for bringing new thought process which might be 360 degree change from what you have captured in the requirement gathering which can result into delay and dispute. Deliver what has been captured in requirement gathering and don’t deviate from it. If users ask for change treat it as change request and raise the red flag that efforts and commercials needs to be frozen before moving to the development activities. Do 2: Let the KPI Be Your Friend What's a KPI? It's a key performance indicator‐‐a handy little color‐coded dot or gauge that "indicates" if your "key" items are "performing" well or if they're headed for the dogs. Set a threshold (e.g. minimum month‐to‐date sales) for the critical items; when you're on the good side of the threshold, the KPI shows you a green dot‐‐all A‐OK. When you're on the wrong side of the threshold, the KPI turns red‐‐time to take action. Example: so you want to have an optimal in‐stock level of your top 10 SKUs. Have 10 KPIs that alert you without even having to read numbers. Green: all is going well. Red: either too much or too little inventory. Don't Don't turn your dashboard into the single‐screen version of those multiple‐sheet Excel tables, where you have to sort through more figures. Educate users the difference between Webi reports and dashboards and their objectives so as to set the expectation right in the beginning itself. Do 3: Make Your Dashboard Actionable It’s good to give “What If” in dashboard where it makes more user interaction and brings satisfaction to business users for the tool implemented, since they can derive information on which they can act. Don't But for the sake of making the dashboard interesting don’t put “what if” criteria until and unless some business value is driven out of it. “What If” are complex development and users tend to ask for more “What If” even they don’t make any business value which can result into time consuming activity and delay. Do4: Dashboard Estimation While we were executing the Parle‐Agro project where there were 29 dashboards which we have to deliver for the 10 departments, which was herculean task. What we have missed

(27)

while doing the project requirement gathering is our own analysis which would have reduced the no of deliverable dashboards by asking more specific questions about each dashboards business value. Don’t Don’t ever count dashboard requirements into Webi requirement at least for efforts and commercial estimation purpose. They should be treated separately which will avoid the dispute while delivering the requirements, since dashboard implementation is totally a different ball game and complex where factors such as Live Office Connection, Excel Sheet, Universe Development, What If criteria and user thought process plays critical role. Do5: Technical • Data sets should be at a highly aggregated level. • Use colors, labels and borders to identify cells or ranges of cells in the spreadsheet. • In spreadsheet, leave room below and to the right of your data so it can grow over time without having to add/remove rows or columns. • Try to limit dashboard size to minimum to get good performance. Don’t • Use of SUMIF, COUNTIF, HLOOKUP and VLOOKUP functions on larger data sets. • Use of spreadsheets having links to other spreadsheets. • Modification of reports used in Dashboard through Live Office/QWAAS connection. In Summary In the end, remember that the dashboard is just a tool. The easier it is to use, and the more directly it makes your customers' life easier, the more it will be adopted. And the more it is adopted, the more positively it will impact your business as a vendor.

4.3 Unit and System Integration Testing

4.3.1 ETL Testing

• Basic Validation with Source • Validating the count of records from source and target database (Unit Testing) maintaining the DB_RECORD_STATUS table at the target database. • Working of appropriate business logic which designed in the ETL flow • Testing the jobs on the daily basis which are successful or not by maintaining the DB_FETCH_BATCH_LOG table at the target database.

(28)

4.3.2 Report Testing

• Validate the report format and structure with the RDD. • Validate the report data against the validated Data warehouse data. • Unit Testing and System Integration Testing (SIT) of the Reports and Universe as per Test Plan.

5.0 DEPLOY PHASE

The Deploy phase is where all of the built and unit tested system is moved to the production environment and then fully tested to ensure all of the requirements specified in Previous phases have been fulfilled. At the conclusion of the Deploy phase, the system will be transitioned into general use and turned over to the IT department for on‐going Production support

(29)

5.1 Perform UAT on Development environment

The Data Warehouse / Mart, universe(s) and report(s) will be tested by the user for their accuracy in terms of data and formats on the development environment.

Do’s

• Report and Dashboard development should be done in accordance with RDD only. • Standard template design for report development which includes logo, heading, prompt display, fonts, heading background colors and margins (Header, Footer, Left and Right margin) should be finalized before the report development work starts. • Data is loaded in the target database according to the RDD and business logic defined during requirement gathering phase. • If historical data is considered while bidding the project then data quality and consistency comes under client’s purview. It should be notified to the client that they have to provide the historical data prior to UAT and data testing is clients responsibility since lot of time is wasted in the doing the said activity if it is not properly defined while bidding the project. • Always inform well in advance the UAT schedule to client. • Standardized Excel inputs for data warehouse should be given to business users for data population which they are not authorized to change (excel format). The business users need to give these excel data well in advance to implementing vendor so as to proceed with UAT

Don’ts

• Taking changes like addition of new elements, reports, tabs in development which is not documented in RDD. This should be considered as change request and should be escalated for efforts estimation and commercials. • Forgetting to notify the client that once the reports are developed and the business user insists on changing the formatting of reports then it will impact the project delivery. It should be taken as change request and estimated accordingly. • Entertaining logic change brought by user in the UAT stage which is considered as a major change since it can have phenomenal impact on Universe for one or may be multiple departments and thus can have impact on reports from one or may be from multiple departments. It should be taken as change request and estimated accordingly. • Forgetting to raise the delay because of user UAT

(30)

• Forgetting to notify the client about Historical Data validation, cleansing and consistency of data which needs to be loaded in target database prior to UAT is not vendor’s responsibility. • Forgetting to escalate the standard excel data input format change by user while ETL. This is again a major loss of time since the developer needs to find out the reason for ETL fail while loading the excel input. If it is for multiple years/months then everything needs to be truncated and loaded again with unchanged standard excel format with proper data because of ETL fail / improper data in reports which is highlighted while doing UAT.

5.2 User Training

Do’s

• User training needs to be conducted immediately after UAT completion for a particular department since big gap between UAT and user training will result into lack of commitment from business users. I such scenario training is conducted multiple times which affects project delivery and profitability. • User training should be conducted only on UAT accepted reports only. • BI tool functionality should be shown in general (how to access the BI tool, refreshing report/dashboard, different ways to analyze the data like filtering etc, how to publish the reports in various formats like pdf etc, how BI tool will help in eliminating the manual efforts etc) and not at great depth which makes users think of complexity of using the tool resulting in lack of interest.

Don’ts

• Forgetting to inform business users well in advance of training schedule. • Forgetting to escalate the issues relating to delay in user training because of non‐ availability of business user on the schedule time.

5.3 Prepare the BODS XI 3.1 Production Environment

Installation and configuration of BODS XI 3.1 on supported platform • Install and configure BODS XI 3.1 server on supported platform. • Deploy necessary BODS XI 3.1 server components on supported Windows platform. • Install the DI (Data Integrator) functions on SAP R3 on production server • SAP R3 authorizing the functions for DI to operate (To extract the tables from SAP R3)

(31)

• Scheduling the Data Integrator jobs in Data Services Management Console on production environment.

5.4 Prepare the BO XI 3.1 Production Environment

Installation and configuration of BOXI 3.1 on supported platform • Install and configure BO XI 3.1 server on supported platform. • Deploy necessary BO XI 3.1 server components on supported Windows platform. • Configure CMS and Audit database and creating Repository databases on supported database server. • Tomcat server installation on the supported environment.

5.5 Migration of Development to Production

5.5.1 ETL Migration

Promote the Data Integrator work flows to the Production environment by two ways described below. • Method 1: Log on to the data services designer in the development environment and export the related project directly from the development to the production server with the credentials like repository name, user name/password of production environment. If this process is followed for migration then there might be some issues like network issue, power failure/fluctuations etc which can corrupt any environment. • Method2: Log on to the data services designer in the development environment and take a back up of the related project on a shared folder. Log on to the data services designer in the production environment and import that file from the shared folder. This process of migration can avoid the issue described in Method 1 of migration process. • Once the project is imported successfully on the production environment we have to run some sample jobs for testing purpose.

5.5.2 Report Migration

Migration of the UAT Tested reports and universes to Production environment can be done in two ways as described below.

(32)

• By using the import wizard • By saving the entire universe and reports in the biar file and migrating by using the import wizard.

5.6 Review and Validation

All the developed workflows, universe(s) and reports will be tested by the user for their accuracy in terms of data and formats on the production environment after which the system goes live for the users

6.0 EVOLVE PHASE

(33)

In the Evolve phase, the project is formally closed down. A Project closure is conducted to ensure that the lessons learned from the project are captured for future reference, and to identify the opportunities for generating further value from another iteration of development.

6.1 Knowledge Sharing

All the documents created during the project will be handed over to customer and the project knowledge will be shared during a two days knowledge sharing sessions.

6.2 Post Production Support

Hand holding to the Business Users and IT Department for maximum of 10 man‐days or as per the agreement with the customer.

Do’s

• Once reports and dashboards are productionised on particular date the support starts on that date itself. The support is given only for the existing reports and dashboards.

Don’ts

• Doing changes to existing Data Model (DW), Universes, Webi Reports and Dashboards while on support. This should be avoided and escalated to the customer and proper efforts and commercial need to be communicated. • Doing new development (Universe/Webi Reports/Dashboard) while on support. This also need to be avoided and such activities should be communicated and efforts to be estimated for commercial and time duration thus avoiding the delay. • Providing support by the existing development team on project. This is not best practice and support should be provided by the separate team and not by the existing development team as it may have an impact on ongoing project development and can result into project delay. • Database level scripting, like, creation/modification of views, tables, procedures, etc. This again should be avoided while on support and treated as new development.

6.3 Exclusions

• Resolution of Product Defects. • Working on any Business Objects Enterprise related activities that are not in the scope of the current deployment. • Any data cleansing is out of scope. Business Objects will assume that all the data that is provided would be clean and reliable.

7.0 PROJECT ORGANIZATION

(34)

7.1 Vendor’s Roles and Responsibilities

Project Manager:

Delivering the project(s) within time and resource budgets. Requisition of human, computer and consumable resources and ensure optimum usage. Conducting periodical meetings with team members to ensure effective communication & feedback (up and down). Producing periodical status reports to the reporting authority and to the Customer. Ensure that the status report is regularly reviewed by the reporting authority and by the Customer, if contractually required. Implementation of Quality System in the project(s). Organize project walkthroughs and co‐ordinate with QA for project audits or audits for a process to ensure that the quality objectives are being achieved.

Ensure that project team members are always kept informed on matters of interest concerning the project/process and their welfare to maintain their morale at a high level. Proper usage of project management tool for effective project delivery on time, to

manage change control mechanism and to ensure the optimum resource utilization so as to ensure project profitability.

BO Consultant:

Conducting smooth functioning of various integrations needed by Business Objects from SAP Systems. Coordinating with SAP Consultants on Customer site where additional information needed to understand the customization if any. Working in co‐ordination with the Developers. Ensure that all necessary documentation for the project(s) are prepared, maintained and approved, as required. Delivering the project(s) in accordance with the agreed contract / work order.

(35)

Obtaining Customer acceptance on project completion, technical queries, changes and concessions, if any.

Sr. Developer:

Delivering the project(s) in accordance with the agreed contract / work order. Monitoring and controlling the progress of the project(s).

Obtaining Customer acceptance on project completion, technical queries, changes and concessions, if any. Collaborating with the Customer executive(s) to ensure that Customer commitments are being carried out to clarify technical requirements and to resolve technical problems in a project. Escalating Customer complaints, if any and contractual issues or problems to the reporting authority and co‐operating with the reporting authority to monitor their status & assist in their resolution. Ensure that all necessary documentation for the project(s) are prepared, maintained and approved, as required.

Reviewing design specifications to ensure correct use of common modules, correct interfacing between sub‐systems, system integrity and operating efficiency. Evaluating and sizing Change Requests, if any. Sending Minutes of the Meetings to all the concerned authorities. Working in co‐ordination with the Developers.

Developer:

To perform all the tasks assigned. Maintain appropriate documentation and records. Demonstrate and implement the Business Objects skills. Maintain weekly status and obtain necessary feedbacks. Communicate the potential delays along with reasons.

(36)

(37)

7.2 Customer’s Roles and Responsibilities

This section details the Roles and Responsibilities, which Vendor has assumed, will be performed by Customer any deviation, which has a potential impact on Vendor responsibilities, will be subject to Change Control Process. Customer shall allocate/assign the required resources with the necessary skills and authority to execute the responsibilities given below.

Project Manager / Project coordinator

Customer shall designate a person, called Project Manager / Project coordinator to whom all Vendor communications may be addressed and who will have the authority to act for Customer in all aspects of the project. The specific roles and responsibilities of this person are: Setting up and managing the project team members from Vendor

Update Customer management on the project status.

For any change in scope, the cost implications of the same have to be considered. For any changes there is a standard change request procedure for which structured processes need to be followed.

Accept the deliverables or obtain the necessary acceptance sign‐off from Customer management, wherever so required.

Initiate and approve Change Requests or obtain necessary sign‐off and approval for the Change Requests from Customer Management.

Ensure the availability of Customer management and users whom Vendor may need to discuss with for the purpose of the project.

Furnish all the necessary information required by Vendor in connection with the Project.

(38)

7.2.1 Customer Responsibility:

To provide the required information and documentation associated with the development effort from end Customer. To provide the required Software and Hardware for the project. To pursue the project and to co‐ordinate discussions/demos/reviews/ approvals. To provide the acceptance criteria for the project. To provide the required technical assistance on relevant end Customer applications that will be used by Vendor on the project. To arrange for the required resources as per contract

To ensure availability of required persons Customer for discussions, meetings, clarifications, reviews and testing at appropriate times as per the project plan / schedule. To provide quick turnaround for approvals, sign‐offs and acceptances.

To provide quick turnaround for reviews, clarifications and answers to queries at various stages of the project.

To provide the development standards that needs to be followed, if desired by Customer To provide the necessary documentation support (For Example, Issue List) in assisting

Vendor project personnel for execution of tasks.

To make undisputed payments to Vendor in accordance with the payment schedule on Vendor fulfilling its obligations.

To conduct acceptance of all project deliverables within the time frame specified in the Project schedule.

To ensure that the deliverables from Customer including Software, tools, test data and other facilities will be delivered to Vendor in time.