Global Bike Inc. – Sales Data Integration And Data Warehouse Implementation

Workflow

Global Bike Inc. is a leading wholesale bike brand which sells various bikes and accessories across the world with their major customers in US and Germany.  Robert Jones is the manager of several sales organizations at GBI and he primarily manages the sales activities across the organization (Chen and Sun, 2015). At the end of each month he receives sales report which does not aid him in making strategic business decisions.

All the day to day transaction of sales data is recorded in an existing OLTP system (SAP ERP) which will serve as the source for loading the data into our Data warehouse. We are integrating data from multiple OLTP data sources which is SAP, SAP Net Weaver BI and Non SAP.  SAP BW is used for implementing the data warehouse solution for GBI Bike Inc. where in the Extraction, Transformation and Load of the source data is performed for loading source data into Enterprise data warehouse. SAP Business Objects if used for multidimensional analysis of the sales metrics and analyzing the Key Performance Indicators (Day and Day, 2009).

Workflow

Global Bicycle Incorporated (GBI) uses the production process to manufacture goods to place into inventory and to fill customer orders received from the fulfillment process through sales.

The major objective of implementing this Business Intelligence solution is to enable easy access of data and models to provide business managers with the ability to conduct analysis. It will also help to transform the data to information which will be used to make key decisions and will finally be converted into actions (Schiff and Warren, 2017). The major area of focus is the sales data extracted from disparate OLTP which will be loaded into an Enterprise Data Warehouse after cleansing/scrubbing and transforming the data.

The major players in the market for Data Warehouse implementation are:

• Teradata: It has been rated as the market leader in the data warehousing space for the past 15 years by Gartner’s 2014 Magic Quadrant. The company is on top of its game with new tools, innovations and capabilities, including Hadoop based technologies
• Oracle: It is known for high performance, scalable and optimized data warehousing. There are an estimated 390,000 oracle DBMS customers across the globe
• IBM: It provides us a solution for Data warehouse implementation with IBM Cognos Data Manager (ETL) and IBM Cognos (Business Intelligence)
• SAP: SAP Business Warehouse can be used for tightly integrating the warehousing capabilities for a single version of truth and designing business intelligence solutions for accelerated operations.

The Gartner’s Magic Quadrant for 2015 depicts the market leaders for implementation of DW solutions:

FIG 1: Magic Quadrant for data warehouse and data management solutions for analytics

The most optimal solution to cater to the needs of Global Bike Inc. is to implement a solution employing SAP BW & BO as close to 80% of our data is being sourced from SAP systems. Hence, it will enable us to tightly integrate large proportions of SAP data into a single version truth (Nashine, 2013).

Step 1: Display Materials

Display all the below raw materials, semi-finished goods, and finished goods.

Description	Number	Type	Storage Location
Brake Kit	BRKT1000	Raw
Chain	CHAN1000	Raw
Derailleur Gear Assembly	DGAM1000	Raw
Hex Nut 5 mm	HXNT000	Raw
Lock Washer 5 mm	LWSH1000	Raw
Packaging	PCKG1000	Raw
Pedal Assembly	PEDL1000	Raw
Socket Head Bolt 5x20mm	BOLT1000	Raw
Touring Aluminum Wheel	TRWH1000	Raw
Touring Frame-Red	TRFR3000	Raw
Touring Handle Bar	TRHB1000	Raw
Touring Seat Kit	TRSK1000	Raw
Touring Tire	TRTR1000	Raw
Touring Tube	TRTB1000	Raw
Warranty Document	WDOC1000	Raw
Touring Aluminum Wheel Assembly	TRWA1000	Semi.
Deluxe Touring Bike (red)	DXTR3000	Fin.

• In the “SAP Easy Access” screen, follow the navigation path below:

Navigation

Logistics à Materials Management à Material Master à Material à Display à Display Current

• In the “Display Material (Initial)” screen, enter the following information:

Field Name	Data Entry
Material	BRKT1000

• Click on (ENTER).
• In the “Select View(s)” pop-up, highlight the “Basic Data 1” row.
• Highlight the “Purchasing” row.
• Highlight the “MRP 1” row.
• Highlight the “MRP 2” row.
• Highlight the “General Plant Data / Storage 1” row.
• Highlight the “Accounting 1” row.
• Highlight the “Costing 1” row.
• Click on (SAVE AS DEFAULT VALUES).
• Click on (CONTINUE (ENTER)).
• In the “Organizational Levels”pop-up, enter the following information:

Field Name	Data Entry
Plant	Plant Dallas
Stor. Location	Raw Materials

• Click on (SAVE AS DEFAULT VALUES).
• Click on (CONTINUE (ENTER)).
• In the “Display Material BRKT1000”screen, click on the
• Click on (ENTER) until you reach the last tab.
• Click on (ENTER).
• Refer to the beginning of this step to display the rest of the raw materials.
• In the “Display Material (Initial)” screen, enter the following information:

Field Name	Data Entry
Material	TRWA1000

• Click on (ENTER).
• In the “Select View(s)” pop-up, deselect the “Work Scheduling” row.
• Click on (SAVE AS DEFAULT VALUES).
• Click on (CONTINUE (ENTER)).
• In the “Organizational Levels”pop-up, enter the following information:

Field Name	Data Entry
Stor. Location	Semi-Fin. Goods

• Click on (CONTINUE (ENTER)).
• In the “Display Material TRWA1000”screen, click on the
• Click on (ENTER) until you reach the last tab.
• Click on (ENTER).
• In the “Display Material (Initial)” screen, enter the following information:

Field Name	Data Entry
Material	DXTR3000

• Click on (ENTER).
• In the “Select View(s)” pop-up, click on the
• In the “Organizational Levels”pop-up, enter the following information:

Field Name	Data Entry
Stor. Location	Finished Goods

• Click on (CONTINUE (ENTER))
• In the “Display Material DXTR3000”screen, click on the

Exercise Deliverables

In the System:

• None

On Paper:

• Answer(s) to Question(s)
• Table(s) Filled Out

Step 2: View Inventory Balance

Look at the inventory balance of a raw material located within Dallas.

• In the “SAP Easy Access” screen, follow the navigation path below:

Navigation

Logistics à Materials Management à Physical Inventory à Environment à Stock Overview

• The transaction code to display a material’s inventory level?!
• In the “Stock Overview” screen, enter the following information:

Field Name	Data Entry
Material	Touring Frame-Red
Plant	Plant Dallas
Storage location	Raw Materials

• Click on (EXECUTE).

Exercise Deliverables:

In the System:

• None

On Paper:

• Answer(s) to Question(s)

Step 3: View Material Inventory

In this step, you view the material inventory of all the materials maintained in Dallas.

• In the “SAP Easy Access”screen, follow the navigation path below:

Navigation

Logistics à Materials Management à Inventory Management à Environment à Stock à Warehouse Stock

The transaction code to display a material’s inventory level?!

• In the “Display Stock of Material”screen, clear out the “Material” field.
• Click on (EXECUTE).

Exercise Deliverables:

In the System:

• None

On Paper:

• Answer(s) to Question(s)

Display Materials

Step 4: Display Requirements List

Look at the stock/requirements list for the touring aluminum wheel assembly.

• In the “SAP Easy Access”screen, follow the navigation path below:

Navigation

Logistics à Materials Management à Inventory Management à Environment à Stock à Stock/Requirements List

• The transaction code to display a stock/requirements list?!
• In the “Stock/Requirements List”screen, enter the following information:

Field Name	Data Entry
Material	Touring Aluminum Wheel Assembly

• Click on (ENTER).
• The material type of the wheel assembly?!
• At this time, the screen is blank. This will not be the case when performing the production.

In the System:

• None

On Paper:

• Answer(s) to Question(s)

Step 5: Display Bills of Materials

Display a BOM for the wheel assembly and then display a BOM for the bike.

• In the “SAP Easy Access”screen, follow the navigation path below:

Navigation

Logistics à Production à Master Data à Bills of Material à Bill of Material à Material BOM à Display

• In the “Display material BOM”screen, enter the following information:

Field Name	Data Entry
Usage	Production

• Click on (ENTER).
• Click on (BACK).
• In the “Display material BOM”screen, enter the following information:

Field Name	Data Entry
Material	Deluxe Touring Bike (red)

• Click on (ENTER).
• In the “Display material BOM: General item Overview”screen, review info.

Exercise Deliverables:

In the System:

• None

On Paper:

• Answer(s) to Question(s)

Step 6: Display Product Routings

Display the product routings for the wheel assembly and the touring bike.

• In the “SAP Easy Access”screen, follow the navigation path below:

Navigation

Logistics à Production à Master Data à Routings à Routings à Standard Routings à Display

• In the “Display Routing: Initial”screen, enter the following information:

Field Name	Data Entry
Material	Touring Aluminum Wheel Assembly

• Click on (ENTER).
• In the “Display Routing: Operation Overview”review the routing information.
• Click on (BACK).
• In the “Display Routing: Initial”screen, enter the following information:

Field Name	Data Entry
Material	Deluxe Touring Bike (red)

• Click on (ENTER)
• The importance of routings?!

Exercise Deliverables

In the System:

• None

On Paper:

• Answer(s) to Question(s

Step 7: Create Initial Inventory Balance

We will perform a detailed requirement analysis process which will help us to identify the key business needs that the BI solution must address.

For this business requirement we have identified our key stakeholders as Robert Jones, sales manager of GBI, Sales team, Marketing team, Finance team, R&D team. Huge volumes of sales data is generated from day to day activities and this needs to be managed and analyzed efficiently by these stakeholders for developing effective sales and marketing strategy for Global Bike Inc.

In this step we identified that our stakeholder’s require a holistic view of data from multiple sales organizations within Global Bike Inc. which would give them access to clean, concise, coherent, non-redundant, de-duplicated data. This data will be used by the stakeholders to meet key business objectives by performing descriptive analytics, prescriptive analytics and predictive analytics.

In order to make the analysis easier we have broadly group the stakeholder’s into 4 categories:

2.3.1 Functional Requirements

We require a Business Intelligence solution which will address the needs of the stakeholders for easy access of summarized data along with the capability to slice and dice the data across multiple dimensions like product category, region etc.

2.3.2 Operational Requirements

Integration of the data from multiple operational source systems into the Data Warehouse model through Extraction, Transformation and Load of data. The data from the data warehouse can be divided into multiple data marts to cater to specific business areas. Business Intelligence tools and analytical tools will be employed to perform end user capabilities such as data mining, reporting and creation dashboards.

2.3.3 Technical Requirements

Utilization of SAP Business Warehouse and SAP Business Objects to implement the Business Intelligence solution for Global Bike Inc. We will be additionally using Tableau 9.2 to perform visual analytics on our sales metrics.

2.3.4 Transitional Requirements

Data should be completely migrated from our legacy systems into our DW. There is need to plan and schedule the frequency of the ETL process based on the various factors like the volume of data, end user requirements etc. Since we are recording day to day sales transactions, daily load of data should be ideal for GBI. Regular data reconciliation process should be performed to validate the data with the source systems. There is need to plan for effective change management process which would include documentation update, cross training of staff etc.

Market Leaders for Data Warehouse Implementation

Based on the analysis to cater the needs of key stakeholders, a resolution has been made to implement a data warehouse solution with a hub and spoke architecture. There is implementation of this solution because of the need to access historical as well as current data from the system for analytical processes. In the Data warehouse model Operational Data store will enable us to capture a snapshot of current from the operational systems, on the other hand Enterprise Data warehouse will house the historic as well as current data. In addition, this architecture supports a single version of truth (i.e. Enterprise data warehouse) which will act as a source for various subject oriented data marts. This would help us target specific decision support systems like sales data mart, customer data mart, inventory data mart. This solution is also very cost effective because it involves a single Extract, Transform and Load process.

System Solution

Based on the market expertise, there is need to implement SAP BW policy in combination with the SAP BO to work as the resolve for the BI reporting for the GBI sales organizations. The SAP Business intelligence landscape is depicted below:

FIG 1: The SAP Business Intelligence Landscape diagram

There are a number of advantage of using the SAP BI platform some of which are listed below:-

• Performance and Scalability
• Integration with Business Objects
• Development Efficiency
• Simplified configuration and Operations
• High ROI
• Cross-Application Visibility into Business Process Performance
• Permanent data storage
• Improved Customer Service Levels
• Increased Capacity Utilization Rates
• Proactive Decision Making Enabled by Real-Time Data
• Compatibility with SAP HANA (Real time analytics)
• Bad data is Quickly Identified by SAP BW  [1]

 SAP Business Objects business intelligence (BI) solutions provides business top and factually recognized BI customer-friendly applications that control the Enterprise Reporting necessities. It enhances business necessities such as ad-hoc enquiring investigation about interactive or multi-dimensional data bases, report formatting or KPIs or data visualizations, SAP Business Objects BI resolutions offers common BI site that enhances customers in making real data centric business assessments.

• The customers are able to effortlessly form ad-hoc enquiries and control influential data examination to attain corporate vision irrespective of the database policy complication.
• The SAP BI resolutions eases Total Cost of Ownership (TCO) through provision of a common, scalable, greatly accessible, and integrated BI enterprise policy with an award winning service leaning architecture.
• Executing SAP BI resolutions restructures BI processes through enhancing the customer data application process. 
• The SAP Business Objects offers the structure for obtaining information as well as its analysis which assists the management in coming up with proper data centric decisions which assist in improving the bottom line and upholding operative firmness and effectiveness in the long run.

There is resolution of applying the Hub and Spokes data warehouse design. In Hub and Spokes design, the information is transferred from databases applied in functioning systems into a DW performance area, followed by a DW layer then at the end a set of followed data stores. The information is reproduced from one database to another with the use of the ETL.  The Hub and Spokes design is as showed below:

FIG 2: Hub–and–spoke data warehouse architecture

The Inmon’s approach is used for data warehouse design and development over Kimball’s approach.. A number of advantages are associated with this approach:

• Easy maintenance
• Enterprise wide data integration
• High initial costs but subsequent project development costs are much lower (which is the case here as we are planning on expanding to other business areas outside of sales) [4]

There is implementation of the Hub and Spoke architecture using Inmon’s approach for the sales organizations. Despite the high initial cost, this architecture allows us to expand our Business Intelligence scope to other business areas like finance, procurement etc. This is contrary to the Kimball approach where independent data marts are built for the corresponding business areas. These data marts are then integrated to an EDW. However, this approach can turn out to be costly during integration of the independent data marts.

There is creation of SAP to uphold the necessities of sales structure

FIG 3: GBI Sales Organization dimensional data model

Conclusion

The systems improve the customer support by improving the customer center and better integration of Woolworths’ processes for easy customer issues handling. The CRM system is not aimed just to increase sales, but also to improve the company processes, which include customer service center. Moreover, ERP system will integrate different departments of Woolworths, and therefore it would be easy to solve customers’ issues when the company’s processes are controlled from a single computing application. Therefore, Woolworths can benefit from these expert systems and with proper implementation; not only sales will be increased, but customer service will be improved as well.

References

Chen, Q. and Sun, T. (2015). A model for the layout of bike stations in public bike-sharing systems. Journal of Advanced Transportation, 49(8), pp.884-900.

Day, M. and Day, M. (2009). Data, Databases, and Communities. Nature Precedings.

Schiff, A. and Warren, M. (2017). Implementing A Business Intelligence (BI)/Corporate Performance Management (CPM) Solution: Challenges Faced By A Major National Retailer. Journal of Business Case Studies (JBCS), 13(2), p.63.

Nashine, H. (2013). Existence of best proximity points: global optimal approximate solution. Sarajevo Journal of Mathematics, 9(2), pp.231-241.