BCO$7004-$ BDM&DW Assessment three

Assessment $3$ Group Report and

Presentation

The Assessment Requirements

Contents

The report $...............................................................................................................................................\mathrm{.}1$

I. Warehouse Management...........................................................................................................$\mathrm{.}1$

II$.$ Product Promotions....................................................................................................................$\mathrm{.}1$

III. Information Technology Systems at Acme Inc $.......................................................................\mathrm{.}2$

IV$.$ Conclusion: Building a Decision Support Infrastructure.........................................................$\mathrm{.}2$

V$.$ Instructions for providing the solutions $.....................................................................................\mathrm{.}2$

The Presentation.....................................................................................................................................$\mathrm{.}4$

The report $(30\%$ out of $40\%$ Overall Score$)$

I. Warehouse Management

There are several areas where the company is trying to make improvements. First, the forecast

estimates were often inaccurate deviating as much as $30-40\%$ for some SKUs. This results in excess

inventory in a few cases and stock$-$outs in many other cases.

Second, there were frequent complaints from retailers of delayed shipment of items.

Third, the company experienced many cases of damaged items. This normally occurs during product

shipments or material handling inside the warehouse leading to increased shrinkage. The company

was unable to identify the root cause of the problem or bottlenecks to take concrete actions to

improve the situation.

II$.$ Product Promotions

The sales and marketing team at Acme was interested in building a loyalty card program to retain its

customers and improve overall profitability. The company also did not have appropriate metrics in

place to measure the effectiveness of its promotional campaigns. The marketing managers believe

that significant improvements can be made on its promotional spend if proper decision support

infrastructure is in place. While the basic sales data is available, it cannot get an integrated view of the

sales across multiple stores, perform slicing and dicing of data, conduct multi$-$dimensional data

analysis and build data mining models for identifying the right customer targets.

III. Information Technology Systems at Acme Inc

IT systems at Acme are highly heterogeneous. A few operating units $($retail store$/$regional warehouse$)$

are using Enterprise Resource Planning $($ERP$)$ software as part of their order and inventory

management processes. Each of the company$$s stores and regional warehouses used a relational

database to maintain operational data generated from their business processes. The individual units

used different relational database products and possibly different versions and$/$or configurations of

the database products. While there are multiple differences in the way the data is stored, the

underlying database design can be understood using an Entity$-$Relationship $($ER$)$ diagram. The ER

diagram shown in Exhibit $2$ gives a partial view of how different entities pertinent to Acme$$s business

are related and the nature of their relationships.

The heterogeneity of IT systems at the hardware, system software, business application and data level

makes it extremely difficult for Acme managers to get timely, relevant and accurate information for

making better decisions.

Some of the key architectural choices available for data warehouse implementation are depicted in

Exhibit $6.$ The team is yet to decide on which architecture will be the most appropriate for Acme.

IV$.$ Conclusion: Building a Decision Support Infrastructure

Vishal Mehta and his team are preparing a plan to meet the organizations analytic requirements. While the

team believes that the data warehouse is the best way to meet their analytic requirement, several

questions are lingering in their minds.

What are the potential pitfalls in integrating a warehouse system with existing IT systems? What are the

design considerations that one needs to make while meeting all of the analytic requirements? How to go

about performing sizing$/$estimation for making decisions on the hardware infrastructure needed? What

are the available architectural choices and which is the best option that suits their needs? At an

organizational level, what are the change management initiatives and communication plans needed for

successful implementation?

V$.$ Instructions for providing the solutions

Considering the above summarized issues and problems in Acme Inc and taking a detailed attention to

the information presented in Pilot Data Mart Solution$-$ Key Analytic Requirements $($in Pages $8$ and $9$ of the

Case Study pdf file$),$ the group sh