Implementation of Sales Executive Dashboard for A Multistore Company in Yogyakarta

,


INTRODUCTION
Development of Information Technology has been switched from operational to strategical issue.Information Technology helps company to make better decision by providing data analysis.Management takes benefits from this strategic information technology.One of the issues of strategic information technology is Business Intelligence.Business Intelligence brings a new horizon for company and bring real-time processing and analyzing data.Business Intelligence brings data processing into one step ahead.Business Intelligence providing information from the enterprise data.
Processing data from database needs three processes: extract, transform, and load (ETL).These three processes combine data from different and many data sources.It pull these data and store it into another database, which is called a data warehouse.Data Warehouse is built by using multidimensional model.Multidimensional model contains fact as a subject analysis and dimensions as perspectives to analyze the fact.This multidimensional model will be a baseline to build dashboard reporting.
Report visualization is a process to bring enterprise data into a meaningful information fast and easy to understand [1].This visualization is called dashboard.Dashboard is a visualization result for data processing and has a purpose to be used for company's executive or manager (stakeholder) to measure company performance real time [1].A system in the dashboard can handle and process big data and show it into short pages, show trends and information needed by company's manager or executive.Dashboard also gives manager a responsive user interface comparing with traditional reporting.Dashboard also give more information comparing with traditional reporting [1].Designing the dashboard will help management and executive to identify trend, pattern, and / or data anomaly.Dashboard also can be used to help data analysis.
A retail company in Yogyakarta with more than one store has obstacles for management to manage and monitor sales condition.Sales data from outlet only save in local computer.It raises a problem if management want to monitor real time sales reporting.It takes much time to analyze the sales condition and needs to take strategic decision.
Management also has difficulties to produce real time sales report.Generating sales report spend more time.It needs to collect reports from stores and summarize it manually.Thus, management is unable to generate real-time reports.It needs much time to analyze some sales and managerial issues, such as stock level in every store / branches.From those situations, there are two research questions: (1) How to formulate multidimensional model for this ISSN 2354-0082 multistore retail company; (2) How to implement dashboard to visualize transaction data and bring information to management.In order to solve the research questions, researchers took data from 3 outlets as samples ranging from 1 February 2014 to 31 January 2015.

A. Data Warehouse and Dimensional Model
Data warehouse system were conceived to support decision making within organizations.These systems homogenize and integrate data of organizations in a huge repository of data in order to exploit this single and detailed representation of the organization's decision making [2].Data warehouse is a system to extract, clean, transform, and send data source into a dimensional model and support to implement query and analysis to support decision making [3].
Therefore, considering that the very survival of the organizations frequently depends on the correct management, security and confidentiality of information [4], and the extreme importance of the information that users can discover by using these kinds of applications, it is crucial to specify confidentiality measures in the MD modeling process, and enforce them [4].
The data warehouse is a huge repository of data that does not tell us much by itself; like in the operational databases, we need auxiliary tools to query and analyze data stored [5].Without the appropriate exploitation tools, we will not be able to extract valuable knowledge of the organization from the data warehouse, and the whole system will fail in its aim of providing information for giving support to decision making.
The usage of data warehouse will bring a good analysis of data.It will bring a good information to the business user.This information will support to make decision.Although as a decision support, information security is also a serious requirement which must be carefully considered as a element in the development lifecycle, from requirement analysis to implementation and maintenance [6].
Building data warehouse, the data should follow these three steps: extract, transform, and Load [7].Extract is the first step.In this step, data is extracted.In this step, what information will be retrieved is also defined.The purpose of this step is to take data from its source.The next step is transform.In data transformation, some processes can be done: (1)  Multidimensional modelling is the process of the data modelling in the universe of discourse with the modelling structure to provide a multidimensional data model [8].Multidimensional models categorize data, either as facts associated with numerical measure or as dimensions that characterize the facts and generally in plain text.
All multidimensional model contains fact and dimension tables with the variant of star schema or snowflake models.Fact is a term of the subject of analysis [2].While, dimension shows different perspective or point of views where a subject can be analyzed from [2].
Multidimensional model is stored in a data structure called cube [2].Cube can be generalized interpreted as a basic logical structure to describe multidimensional database.Cube operation is based on the most straight forward way to model multidimensional data.Each cube has a quantitative data that can be analyzed.This kind of data is called measures [2] Building multidimensional model follows some phases / steps [5]

B. Business Intelligence
Business Intelligence is a term that covers data warehousing, data integration technology, query, report, and analysis tools [3].Business intelligence gives business user independent access to information.One of the Business Intelligence implementation examples is implementation of Business Intelligence in multi-channel service delivery capability [9].
Business intelligence as IT application that helps organizations make decisions by using technology for reporting, data access, and also analytical applications [10].Business Intelligence application also help to retrieve information that is hiding in database [10].This hiding information are able to help worker formulate decisions by analyzing data [11].A thorough formulation of business objectives and information technology must be established for an enterprise to obtain value from a BI implementation [12].
Performance Dashboard is a new model for Business Intelligence, building innovation to give suitable user interface for every user to retrieve information [1].Imelda [13] tells that business intelligence is a process to extract company operational data and collect it into data warehouse.During data extraction, transformation and cleaning are done to implement formula, aggregation, and validation.This will bring a good for business issue.

1) Data Extraction (Extract)
Data from 3 stores are generated from different database.Those 3 stores use Access Database.These data are exported into SQL Server Database.From 3 stores, the number of data gained by researchers as follows:

Outlet in Jamal :
There are 9649 transaction data and 30993 transaction detail data from this outlet.

Outlet in Kuncen
There are 6825 transaction data and 19019 transaction detail data from this outlet.

Outlet in Minang
There are 9521 transaction data and 31751 transaction detail data from this outlet.
Researchers drop some unnecessary columns.Those columns are in the some tables:

2) Data Transformation
Transformation is a process where extracted raw data is filtered and coded.This transformation will change data into specific value or add new columns.
There are some transformation processes that are done: 1. Categorize the product into generic and specific code.Category is used to simplify the analysis process.It will ease the management and executive to read the analysis.

3) Data Loading
Cleaning data cause reduce the number of rows.Table IV shows the number of rows before and after cleaning From the table IV, it is shown that there are some rows are deleting.Delete the rows that is not needed will bring a good analysis result.

B. Dimensional Model
To build dimensional model, researchers adopted processes from Ballard [5]  From table V, there is only 1 business process which involve in developing dimensional model.From the table V, it is needed outlet data as a dimension.It is needed to arrange the suitable plan and strategy for every outlet.

Sales Transaction
From the table VI, it is known that there is only one source.Transaction detail will be the fact table and its value will be information detail, such as product name, transaction date, quantity, price per item, gross total, and outlet.

Outlet
Outlet is identified into outlet code.
Transaction Detail

ISSN 2354-0082
From the table VII, it is shown that there are 3 dimensions are needed in dimensional model.Those are time, product, and outlet.Every dimension is categorized into subcategory.From table VIII, it is shown that there is one fact tables and 3 dimensions: time, product, and outlet.Fact table (transaction detail) already contains data which is needed for analysis process.

5) Verify the model
In this process, model is verified and some columns are recalculated as a formula.

Fact table: Transaction Detail
Model: • SalesTotal = sum(detail_nota.Jumlah) In this process, there are two columns as a model.SalesTotal is the sum of quantity of sold product.GrossRevenueTotal is the sum of total gross.

6) Physical Design Consideration
The last step is physical step, especially data sorting and searching through indexing.Data in data warehouse already sorted based on primary key.From figure 1, it is shown that there are 1 fact table with 3 dimensions.It is based on the design step.As a fact table is transaction detail with 2 added columns: sales total and total revenue.These 2 columns will be facts.

C. Dashboard
Building dimensional model will lead to dashboard design.Information from the fact and dimension table will be presented using graphics.
1. Line Chart will be used to present sales transaction trend from specific period.Line chart will help user to read the trend of the data 2. Bar Chart will be used to compare between sales conditions among specific time.
3. Pie chart will be used to give information about percentage of product sales and comparing with dimension category, specific and generic category.

IV. IMPLEMENTATION AND ANALYSIS
A. System Implementation Fact table and dimension table in Figure 1 and Table IX are initial steps to make cube and multidimensional database.Figure 2 shows cube multidimensional database that will be implemented to generate sales dashboard.Cube consists of fact table, dimensional table which has some descriptive columns, and measure.Descriptive columns in dimensional table will help to filter data based on dimension attribute.Measure is quantitative value to be analyzed.Measure value is gained from fact table and will be counted based on dimension on cube.
Data warehouse is ready after ETL process and cube multidimensional model design.Data warehouse can be used to analyze sales report.The next step is report implementation which has important role in managerial level and help in analysis process.It will bring to decision making based on this report.

B. System Analysis
Dashboard is designed to monitor and support management analysis for sales condition in company.Using dashboard, management is able to capture and monitor current sales record and condition.Dashboard also help management to explore data in many dimension and support performance review between ISSN 2354-0082 management and operational [1].Sales transaction data will be visualized into 3 dimensions: time, outlet, and product.Using these 3 dimensions will help user to generate report based on its need.By developing dashboard business intelligence, sales manager and management has an independent access to the information [10].The information generated in the dashboard are vary: (1) Top 10 product sold for every store; (2) Total revenue for each store and accumulative; (3) Revenue for each product category.
The system has some advantages for user, especially for sales manager and owner.Those advantages are: (1) Multidimensional model that are generated help owner to summarize the data into pivot table and can be monitored from some perspective through dimension; (2) Information are able to be drilled down into smaller dimension; (3) Sales data visualization and graphics are able to deliver sales information to sales manager and owner.But this system also has a disadvantage.System is unable to calculate company profit.To analyze company profit, it needs expenditure data.
V. CONCLUSION Based on system implementation and analysis, there are some points as conclusion: 1.Sales transaction data dimensional model has been developed using 3 dimensions: time, outlet, and product category based on fact table which has information of sales transaction detail.
2. Dimensional model can be used to help making of executive reporting.The report is presented in table and converted into graphic.
3. Dashboard can be used based on dimensional model using multidimensional expression.Sales data can be derived and seen from many dimensions.
4. Business intelligence system and dimensional modelling can bring information and present it into dashboard visualization.This will help management to understand and analyze sales condition fast.This analysis will bring good and quick business decision.

Figure 5
Figure 5 shows sales report dashboard.Dashboard has feature to show graphics interactive based on chosen dimension value.Dashboard is also designed to capture information based on filter parameter.Filter parameter is defined based on dimension in cube.

Fig. 6 .
Fig. 6.Graphic of Item Product sold based on outlet dimension Figure 6 shows total item product sold in every outlet.This graphic also indicates outlet dimension is used in this report.With this graphic, user is able to

Fig. 8 .
Fig. 8.Total Revenue Trend Figure 8 shows trend for total revenue.Line chart indicates fluctuation of sales revenue during specific period of time.This line chart also bring an analysis to sales trend based on time dimension (day, week, month, and quarter).Management can read the data and analyze the sales position.

Table M_Data_Harga_Jual
This table contains master data for product.In this table, columns for product code [Kode Barang], product name [Nama Barang], selling price [Harga Jual], and net selling price [Harga Net] is used.Other columns will be dropped since it is not needed in dashboard reporting.After the cleaning process, the table structure as shown in table 1 below:

TABLE I
From the table I, it is shown that the data warehouse uses 4 columns.Those columns have important information: Product id, product name, selling price, and net price.The other columns are deleted since have null values or 0. 2. Table Dt_Nota_H This table is the header for transaction data.There are some columns which have null values.Three columns are remaining: [No Faktur], [Tgl Keluar], [Tipe Pembayaran].The table structure can be shown in table II:

TABLE STRUCTURE
. Dimensional model is a model based data to support high volume query access.Star schema is a tool to modelling multidimensional data in data warehouse.Star schema contains fact table with composite primary key and dimensional table with foreign key.Foreign key in dimensional table should respond exactly one component of primary key in fact table.

TABLE VII .
IDENTIFY DIMENSION NEEDED

TABLE VIII .
IDENTIFY FACT TABLE

Table
IX shows the dimensional model.Dimension time is divided into: Days in Week, Week, Month, and Quarter.While product is divided into: Generic Category, Spesific Category (Sub Category), and Product ID.Outlet dimension only has one dimension, which is outlet ID.As facts in this dimensional model are: Sales Total (Total penjualan produk) dan Revenue Total (Total pendapatan).