Implementation of Data Warehouse for Food Sales Strategy Using

International Journal of Information System & Technology

Akreditasi No. 158/E/KPT/2021 | Vol. 6, No. 2, (2022), pp. 254-258

ISSN : 2580-7250

Snowflake Schema Model

Rima Tamara Aldisa

*, Mohammad Aldinugroho Abdullah

Faculty of Communication and Information Technology, Universitas Nasional,

Indonesia

Faculty of Information Technology, Master of Computer Science, Universitas Budi

Luhur, Jakarta, Indonesia

Email:

rimatamara@gmail.com,

[email protected]

Email Korespondensi: rimatam[email protected]

Abstract

Waroenk kids collage is a restaurant that sells various types of food menus and is located

in Indonesia, Jakarta. This restaurant has data that accumulates and accumulates without

any continuation of that data. This is not supported by the final report. Therefore, it is

necessary to build a data warehouse that can be used as information for waroenk restaurant

owners for college children. One of the important processes in the operation of the data

warehouse is the process of copying data from the operational database. Before the data

enters the data warehouse, the ETL (extract, transform, load) process is carried out on the

data. This scheme is designed for the data warehouse at Waroenk Anak Kampusan using the

Snowflake Schema model. The results of the research show that the Waroenk Anak Kampusan

data warehouse has four tables (product table, price table, service table and customer

dimensions), has one real table, namely the Sales table. All sales data can be monitored by

the owner and can find out the total sales data.

Keywords: Data Warehouse, Sales Strategy, Snowflake Schema, Waroenk kids collage

1. Introduction

Data warehouse is a system that can combine data from various sources into a single data

repository, implement and consistently support business analysis [1] From that explanation,

several journal references were found, such as, [2] Design and Implementation of sales data

warehouse (Case Study : Northwind Sample Database) By M. Firdaus Zulkarnain, Ni Putu

Novia Ardiyanti, I Wayan Wijaya K Sandi, I Dewa Ngurah T Hendrawan, Ida Bagus M

Mahendra in 2021 resulted in the data warehouse integration process using the ETL concept

with the help of Pentaho Data Integration. [3] Implementation of Data Warehouse and Sales

Data to Determine Strategic Plan for Batik Sales (Case Study of Batik Mahkota Laweyan) by

Fatah Yasin Al Irsyadi in 2014 resulted that all monitoring of data on Batik Mahkota

Laweyan products was carried out using a cube browser. [4] The design of a data warehouse

to support executive information systems at the Ummu'L Quro foundation in Depok by

Syamsul Bakhri1, Yamin Nuryamin in 2018 the results have that a database and its

applications can be carried out to analyze transactional information that occurs in the

education unit at the Ummu'l Quro Foundation, Depok, which is used to support the decision-

making process by the leadership. From some of the references above, a data warehouse is

designed that can be used as a good source of information for waroenk owners for college

students related to sales strategies for the future with price, taste quality, customer discounts.

International Journal of Information System & Technology

Akreditasi No. 158/E/KPT/2021 | Vol. 6, No. 2, (2022), pp. 254-258

255

2. Research Methodology

Research methodology is a scientific process or method to obtain data that will be used for

research purposes[5].

2.1. Data Collection Method

Data collection in this study used a literature study from several comparison journals, and

by searching for data directly from waroenk kids college.

Figure 1. Data warehouse architecture for marketing strategy

In Figure 1 above is the data architecture for marketing strategies for college students where

the initial process is to get an operational database, copy the data, how are the results from the

data copy, do the ETL process, then the results are finally obtained.

2.2. Transaction Data

The data needed to build a marketing data warehouse is from data sources from

restaurants. Based on the results of data collection at Waroenk kids collage, the data obtained

is derived from daily, weekly, monthly sales transaction records stored in Microsoft Excel.

Table 1. Transaction Data

Table 1 is sales transaction data made in Ms Excel carried out by Waroenk kids collage for 1

year starting in early 2020 until the end of 2021

2.3. Understanding the Snowflake Schema

Snowflake schema is a multidimensional database in a data warehouse, this schema is

composed of a fact table or fact table in the middle. This table is related to various dimensions

which are also grouped in the table [6].

Operational

Database

Copy Data

Operational

Database

Copy Results

ETL

Data

Warehouse

International Journal of Information System & Technology

Akreditasi No. 158/E/KPT/2021 | Vol. 6, No. 2, (2022), pp. 254-258

256

3. Results And Discussion

The scheme that has been designed for data warehouse development at Waroenk Anak

Kampusan is using the Snowflake Schema model. This model is used to provide information

that the restaurant owner wants to find and obtain easily and quickly if the transactional

database is difficult to obtain.

3.1 Snowflake Schema Design Results

Based on the results of the determination of the transactional table, it is in the form of the

Snowflake Schema which is shown in Figure 2 below:

Figure 2. Snowflake Schema

In the picture above, the sales table has product code, customer code, price code, time code

and total sales, in each sub category there is time, price, customer, product and type category.

3.2. ETL (Extract, Transform and Load)

Extract Transform Load is a data integration process that combines data from various

sources into one consistent storage and is loaded in a data warehouse [7].

3.3. How Works Extract Transform Load

a) Data Extraction : Data must be extracted from the source before being moved to

another place [7].

b) Transformation : The ETL transformation is cleaning and preparing the aggregation

for analysis [7].

c) Load : ETL loading, transformation into a new record in the data warehouse. Full

load is useful for generating exponentially growing and difficult to manage data sets

[7].

3.4. Process Extract Transform Load

The ETL process is used to move transactional data from the data source table to the data

warehouse [3]. Then the ETL process is needed so that the data entered in the data warehouse

is in accordance with the snowflake schema that was designed at the beginning.

Price

Code_Price

Food_Name

Sales

Code_Time

Date

Month

Year

Customer

Customer_Code

Name_Customer

Product_Code

Customer_Code

Code_Price

Code_Time

Code_Service

Total_Sales

Time

Product

Product_Code

Name_Product

Code_ Category

Code_Category

Name_Category

Category Food

Code_Service

Same Waiter

Service

International Journal of Information System & Technology

Akreditasi No. 158/E/KPT/2021 | Vol. 6, No. 2, (2022), pp. 254-258

257

Figure 3. ETL process in products

In Figure 3 above is the ETL process where the results of the ETL process are in the form of a

product table

Table 2. Slice of Product Table

Code Product Name Product

NG_001 Fried rice

ST_001 Steak

RB_001 Toast

ID_001 Indomie

From Table 2 above, you can see a snippet of the contents of the product table

Table 3. Category Table Cuts

Code Product Name Product

NG_002 Fried Rice

NG_003 Chicken Fried Rice

NG_004 Beef Fried Rice

ST_001 Steak

ST_002 Chiken Steak

ST_003 Sirloin Steak

RB_001 Toast

RB_002 Cheese toast

RB_003 Chocolate toast

RB_004 Peanut toast

MG_001 Noodle

MG_002 Chicken Noodle Ayam

MG_003 Beef Noodle

From Table 3 above, it can be seen a snippet of the contents of the category table

Table 4. Discounted Price Table

Code Product Price

NG_002 Rp. 23000

NG_003 Rp. 30000

NG_004 Rp. 25000

ST_001 Rp. 25000

ST_002 Rp. 25000

ST_003 Rp. 30000

RB_001 Rp. 18000

RB_002 Rp. 20000

RB_003 Rp. 21000

RB_004 Rp. 18000

MG_001 Rp. 18000

MG_002 Rp. 20000

MG_003 Rp. 25000

From Table 4 above, you can see a cut from the contents of the price table

International Journal of Information System & Technology

Akreditasi No. 158/E/KPT/2021 | Vol. 6, No. 2, (2022), pp. 254-258

258

Table 5. All pieces of data from the sales transaction

Fact

Code

Time

Code

Kode

Product