BIT234 Enterprise Resource Planning System Database Analysis Report

Desklib provides past papers and solved assignments for students. This report analyzes an ERP database using data warehousing and BI.

BIT234 - ENTERPRISE RESOURCES

Contents
List Of Figures......................................................................................................................................2
Introduction...........................................................................................................................................3
Data Ware House...................................................................................................................................4
Business Intelligence.............................................................................................................................5
Database Used.......................................................................................................................................6
Analysis and Evaluation of Database..................................................................................................11
Conclusion...........................................................................................................................................15
References...........................................................................................................................................16
List Of Figures
Figure 1 prodCategoy Table.......................................................................................................5
Figure 2 prodChannels Table.....................................................................................................5
Figure 3 prodCustomer Table....................................................................................................5
Figure 4 prodOrderDetail Table.................................................................................................6
Figure 5 prodOrderSummary Table...........................................................................................7
Figure 6 prodProducts Table......................................................................................................7
Figure 7 prodReturnDetail Table...............................................................................................8
Figure 8 prodReturnSummary....................................................................................................8
Figure 9 prodSalesperson Table.................................................................................................8
Figure 10 Pivot Table for database............................................................................................9
Figure 11 Design View of Database........................................................................................10
Figure 12 First Pivot table........................................................................................................10
Figure 13 Pivot chart For the first Pivot Table........................................................................11
Figure 14 Second Pivot table...................................................................................................11
Figure 15 Second Pivot Chart..................................................................................................12
Figure 16 Third Pivot Table.....................................................................................................12
Figure 17 Pivot Chart for third Pivot Table.............................................................................13

Introduction
This report will give brief documentation of the database which is being made for an ERP
system. A data warehouse is being generated with the help of database in MS Access which
is a licensed product of Microsoft. It can also be done with the help of SAS ERP system. The
database which is being used is of a small business which has a various category of spices
and various products which have been developed with the help of spices. Customer Data have
been built which stores information of different customers like their names, city, state and so
on. After creating the data warehouse pivot table is being created using MS Excel. The
dashboard is also built in MS Excel using those pivot tables.

Data Ware House
A data warehouse is generally a relational database which is based on an enterprise system. A
data warehouse stores its data from the extracted data stores and various external sources.
Data records which are stored in the database are used for searching and useful for business
users. There were three main concepts of a data warehouse:
 Data sources for extracting data will be from operational systems such as ERP, Excel,
financial applications or CRM.
 There would be a data staging area in which data is ordered and cleaned.
 Presentation area is the area where data is stored in warehouses.
Data warehouse design methods:
 Top-down Approach:
It was based on Inmon’s method in which data warehouse is building first and then further
processing is done. Data which is being extracted are either from third-party sources or
operational resources. Systems which are external are generally validated In staging area
before it is being integrated into the data model which is normalized. Data marts are being
created form the stored data in the data warehouse.
 Bottom-up Approach:
In the bottom-up approach, data marts are being created as the same architecture which is
being used in Kimballs’s data warehouse architecture. Data is being extracted from the
operational system and after that, it is being processed under the staging area and then it is
being converted into star schema design. The data is then converted into data marts after
processing. After that data mart is being included in the data warehouse.
 Hybrid Approach:
As the name suggests it includes both approaches top-down and bottom-up methods. Due to
the high speed of bottom-up approach first, it is being used for design data marts and
integration of data is being done with the help of the Top-down approach (Informatica.com,
2019).

Business Intelligence
Business Intelligence is those technologies and practices of application which is being
integrated, analyzed and presented to form a business architecture. BI is used to support
decision making which is being best for business. Systems which uses Business Intelligence
are data-driven Decision Support System. BI systems are used to provide current, historical
and predictive views which are of business operations. Data has been gathered first and after
that processed data is being used further for more operations and then data which is being
processed is sliced and diced to form pivot tables. After generation of the pivot table, the
dashboard is being used for visualizing data in an interactive manner. Many Business
Intelligence tools are available which have been built by companies like IBM, Oracle, SAP,
and SAS. Business Intelligence is defined as solving the problem by using the latest data
which is being generated or collected through external sources or operational systems.
(Investopedia, 2019)

Database Used
Figure 1 prodCategoy Table
This table would provide the category which is being available in the shop. There are two
columns in the database which includes category and Category of spices which is being
available in the shop.
Figure 2 prodChannels Table
This is the second table which gives information about the channel through which customer
can contact the shop. ChannelID is the primary key for the table rod channels and Channel
attribute will store the information through which customer contacted to the shop.
Figure 3 prodCustomer Table
This table will be about Customer Table which has CustomerID as the primary key attribute.
Details of the customer are being stored in this table. Name, city and zip code is being

inserted into this table. SalespersonID would be the foreign key of the table salesperson
which means that from which person they bought their product.
Figure 4 prodOrderDetail Table
This table will give brief information about order detail and stores information like OrderID,
OrderSummaryID, and productID. Order Quantity and Order Cost and Price per unit is being
stored in this table.

Figure 5 prodOrderSummary Table
Above table is about Order Summary table which will provide the summary of the order
which is being placed at the shop. It is being like a min bill which can give all information
which is required by a shopkeeper to store which will be used further.
Figure 6 prodProduccts Table

This table will provide information on products which is being available in the shop.
ProductID is the primary key of this table and it stores the various products which can be
made through different spices. This can be retained through their category that which product
will belong to which category and their price per unit are also defined in the table mentioned
above.
Figure 7 prodReturnDetail Table
The image shown above is the image of table prodReturnDetail in which return details of the
product is being stored like productID, Return Detail and ReturnID and the quantity of the
returned product.
Figure 8 prodReturnSummary
The image of the table above will provide a summary of the returned product and the date on
which it is being returned. This table will provide a brief summary of the returned product.
Figure 9 prodSalesperson Table
This image is of Salesperson Table which will have salespersonID and Salesperson name
stores in the table.

Figure 10 Pivot Table for database
The image above is of the Pivot table which is being generated using MS Excel. The pivot
table is showing operations on more than three tables in the database. This pivot table is
saved in a database which is being created in MS Access. (MS Access Blog, 2019)
Analysis and Evaluation of Database
Figure 11 Design View of Database

In the above image design view of the database is being displayed in which different tables
and their relationship is being displayed. In the above image, prodCategories table has two
attributes CategoryID and category. prodProduts table stores information about different
products which is being available for sale. Order Detail table will have information regarding
order which is being made by the customers. Customer table will have information about the
customer. Order Summary will provide a summary of an order placed. (Bose, 2019)
First decision-making pivot table:
Figure 12 First Pivot table
In this table, the Total sum of OrderQuantity is being calculated for various customers with
respect to categories of products. Rows will store the value of different customers which will
order different category products like spices, tea and Coffee is being displayed in the column
of the table. A grand total of every row and column is being displayed at the end of row and
column.
Pivot Chart for the first Pivot table:

Figure 13 Pivot chart For the first Pivot Table
In the above figure bar chart is being plotted using a pivot table. The above chart will be
useful for visualizing sales of different categories of products according to various customers
of the shop. Customer First name is being displayed in the x-axis and the sum of order
quantity is being displayed on the y-axis of the graph. (Smartsheet, 2019)