International Journal on Recent and Innovation Trends in Computing and Communication ISSN: 2321-8169

Volume: 6 Issue: 3 33 - 36
______________________________________________________________________________________
Generation of MIS Report Using Data Warehouse

Deepak Gupta Ruchika Kataruka Rakesh Surana

IT-SAKEC IT-SAKEC IT-SAKEC
Mumbai, India. Mumbai, India. Mumbai, India.
[email protected] [email protected] [email protected]

Ashwini Deshmukh Swati Tiwari

Professor IT-SAKEC
IT-SAKEC Mumbai, India.
Mumbai, India. [email protected]
[email protected]

Abstract— MIS reports are used as decision support systems for higher Management in hospitality industry. Manual generation of MIS report
is a time consuming task which can lead to many human errors and excessive calculations process.
The main objective of this research is to develop an automated system for generation of MIS reports, using data mining and data cleansing
technique with best suitable OLAP data modelling approach. It can be used for better budget prediction which can help to run the business in
good financial shape. It also helps reducing error and optimizing the operational cost in better way.
The MIS report will be generated on the low-level accounting data which has day to day transaction linked to each GL accounting code. The
report will display the Actual amount for each particulars (report lines), along with budgeted amount.

__________________________________________________*****_________________________________________________

I. INTRODUCTION OPERA system doesn’t have this functionalityso it is done

The purpose of this project is to draw a clear picture of manually in most of the hotels.
financial status for any hospitality industry, which can help a The tools that will be used for creating MIS Reports are
Management team to take appropriate decision to run the Microsoft SQL Server, SSIS (SQL Server Integration
business smoothly.MIS report can help for better future Service), SSRS (SQL Server Reporting Service).Design of
budgeting and also guide the organization for better prospect. Datawarehouse for MIS Report is done using STAR schema
Most of the time the MIS reports are created manually by modelling.ETL process will be used for extracting the data,
financial experts. This manual intervention can result in transformation of data i.e. Data cleansing, Aggregation and
unexpected human error, which can lead to unhealthy Calculation. Once data is loaded in to Staging area, ETL tool
business decision for any organization. Automated MIS can will be used for data cleansing and various
overcome these issues and provide a better automated solution calculation/aggregation and final data to be loaded in
to generate the MIS report. Since the generation process is respective FACT and Dimension table. After this step
automated, it can avoid any human error and will always business formula will be applied and fact table will be
produce the correct result, which is very much beneficial for updated with respective calculated values. Report will be
management and team can go in right direction. MIS report generated using SSRS tool. So that report that are generated
acts as a decision support system as it provides the Monthly will be very helpful for predicting the financial budgets,
summary data on report level out of low level accounting comparing between previous month and current month status,
data. This management information system report is used by as well as comparative analysis for yearly data can also be
upper management to analyse the business performance by done using ytd(Year To Date) values.Roles and responsibility
looking at current summary level data and comparing the involvescreation of various Dimension and Fact tables. SSIS
same with budgeted data, which is again available in same tool will be used for ETL process and SSRS will be used for
report. reporting process.
In our current scenario, the existing Hospitality industry
(XYZ) is using third party software OPERA as a Hotel A. AIM OF THE PROJECT
management system. OPERA is an OLTP system which The software product ―GENERATION OF MIS REPORT
record day to day transaction for this industry. This system USING DATAWAREHOUSE‖ will be a system that will
generates monthly GL data on granular accounting level in a generate monthly reports using data cleansing, data mining
big flat file. Other ERP software like SAP,PeopleSoft have techniques within a proper data modelling. This software can
inbuilt module to generate different MIS reports, butas of now be used to calculate actual amount of various financial
transaction for the given month, and also can be compared with
33
IJRITCC | March 2018, Available @ http://www.ijritcc.org
_______________________________________________________________________________________
International Journal on Recent and Innovation Trends in Computing and Communication ISSN: 2321-8169
Volume: 6 Issue: 3 33 - 36
______________________________________________________________________________________
budgeted number. It will effectively reduce the unexpected The research examined whether the selected
human error and also reduce the processing time drastically. Financialinstitution of Bahrain varies so as to use of MIS
leadership of decision makingfor strategic and tactical
B. SCOPE OF THE PROJECT planning purpose. But it compares decision between top level
This software will improve the efficiency. The MIS report (Strategic) and mid-level (tactical). [4]
generation using Data warehouse project is report generation
software with the help of which finance manager or any III. PROBLEM DEFINITION
authorized user can generate MIS report which can help the Currently the financial information (data) is available at
top management to take correct decisions to grow the business different location which is very difficult to consolidate for any
in right direction. Every user has their own profile and can analysis and Business decision. MIS report can give vision of
have access with given login to the system. As various reports all the financial data at common location, which would help
are generated this will be very helpful for predicting the Management to take the proper decision and action based on
financial budgets, comparing between previous month and history data.
current month status, as well as comparative analysis for In most of the industry where OPERA like systems are used,
yearly data can also be done. they have to create these kind of MIS report manually, which
lead to lot of unexpected human errors and it can could be
II. LITERATURE SURVEY time consuming task as well. Automation of this report
Oracle hospitality generation can help Hospitality industry in a big way and help
Oracle hospitality is the brochure of oracle, in this states a management to take proper decision and any corrective action
opera cloud Opera cloudbrings to the OPERA Software suite as needed.
that leading hotels & casinos relies on for year to We are creating the MIS report using ETL tool – SSIS &
manage properties hotel operation and distribution. But it reporting tool - SSRS. This will automate the MIS process
provides only providescheck-in, check-out and does not and will avoid any human errors.Generation of reports take a
provide financial budgeting. lot of time and automation of this will make the data available
on a single click. Previous history reports can also be accessed
Management information systems and business decision at any given point of time via this system.
making: review, analysis, and recommendations

Management information system and business decision IV. DESIGN & IMPLEMENTATION
making in these Comparative figure sale for one week or A. Design
month and the next project revenue figure based on new 1. Staging Area: Staging area is used to stage the
product sales assumption but Different situation call for source data without any transformation.
different decisions to be made MIS theorist since same MIS Stage_tb_fie is created to stage the source file
tend to not be adaptable.[1] coming from Opera system to store it as is.
Budget table is created to stage the monthly
Proposing a Conceptual Readiness Assessment Model of budget data.
MIS/IS Deployment in Manufacturing Companies 2. Dimension modelling:
Star Schema is used to model this data warehouse.
Proposing of conceptual readiness assessment model of MIS
deployment in organization is working on model that was
tested using the results obtained by the case study conducted
on an auto parts manufacturing company. But in this
Assessment done for Mehr Campus Corporation indicates the
company readiness for installing and implement MIS have not
reaches to acceptable and standard level. [2]

A Proposal for Using Parallel Flows with the Aid of DSS in

ERP Projects

Proposal for using parallel flows using the Aid of DSS in ERP
Dimension table:
Project is describe A new method to create parallel flows that
MIS_DIM is use to hold the attributes of report
make different use of DSS in ERP System. But in this Data
particulars.
flowing from parallel system is not accurate that can be
Time_DIM is used for storing time dimension on
consumption error of wrong data and it might result in wrong
month level as per requirement.
input data for MIS system. [3]
TB_MAPPING is used to define the aggregation
logic dynamically.
The signification of management information system for
FACT table is used to store all the fact data.
enhancing strategic and tactical planning

34
IJRITCC | March 2018, Available @ http://www.ijritcc.org
_______________________________________________________________________________________
International Journal on Recent and Innovation Trends in Computing and Communication ISSN: 2321-8169
Volume: 6 Issue: 3 33 - 36
______________________________________________________________________________________
A database has been designed with different tables each used
for different purpose.
While designing the database we used star schema.
Table Stage_tb_fie is created inwhich the input file is loaded.
Table Report master has been made with attributes
Report_code and Report_name. In this all the reports are
present of which MISreports haveto be generated.
 Table dimension has been made with 7 different
attributes. Data in the dimension table is loaded ones
using SSIS (Sql Server Integration Services).
 Table budget has been made with 5 different attributes
including Budget Month and Budget amount which will
be used for making budget related information.
 Table Tb_mapping is created in which mapping is done
according to Department and account.
 Table Formula_Mapping is created with 10 different
attributes in which all the formulas are applied according
to the report description.
 Table Fact is created and it is loaded dynamically using
queries and joins.

B. Implementation
The system has been implemented using Sql Server
Management Studio, Sql Server Business Intelligence
Development Studio. The main part of our implementation
was concentrated on database pre-processing and attributes
selection.
Star schema is used for designing the database.The data in the
database is loaded using Sql Server Integration Services
(SSIS) .To load the data in the fact table join queries are
written and the data will be loaded automatically when the
input file will be present.For the calculation of actual amount
year to date amount pl/sql code is written.
Input files will be provided by the opera system andour
software will help to generate automated MIS report with all
the required calculation and logic. Fig1. Project Flow Diagram
Reports are generated using Sql Server Reporting
Service.Login id will be provided to all authorized users that
C.Results
is the accountant and manager of the hotel with the help of
which they can access the reports. In the generated reports
drop down will be provided so that they can choose a
particular report and comparative analysis can be done.
Reports can also be generated using pie charts so that better
understanding of the system can be done.

35
IJRITCC | March 2018, Available @ http://www.ijritcc.org
_______________________________________________________________________________________
 International Journal on Recent and Innovation Trends in Computing and Communication ISSN: 2321-8169
Volume: 6 Issue: 3 33 - 36
______________________________________________________________________________________
V. CONCLUSION
We implemented the model using Sql Server Management
Studio, Sql ServerIntegration Service, Sql Server Reporting
service. MIS report project helped to avoid human errors in
the report and the time consumption has been reduced very
drastically. Management team can use this report to take
appropriate decisions for growing business in positive
direction in this competitive market.Manual calculation,
human error, risk of losing data is reduced. This report will
help financial managers and accountant to do comparative
analysis onprevious year data as well as previous month data
with the help of this proper budgeting decision can be made
for various departments. Budgeting decision can be done for
decision support system.

VI. REFERENCES
[1]. Srinivas Nowduri, ―Management information systems and
business decision making: review, analysis, and
recommendations‖ , Bloomsburg University of Pennsylvania ,
2011.
[2]. Jale Mirzaei, Fariborz Mosavi Madani , ―Proposing a
Conceptual Readiness Assessment Model of MIS/IS
Deployment in Manufacturing Companies ‖, Dept. of
Information Technology, School of Eng. Alzahra University
Tehran, Iran, 2009.
[3]. Solmaz Moradi Moghaddam , G. Latif Shabgahi , Mohsen
Moradi Moghaddam, Ramin Nasiri , ―A Proposal for Using
Parallel Flows with the Aid of DSS in ERP Projects‖ , Azad
University of Arak Tehran, Iran ,2009.
[4]. Akram Jalal Karim, ‖ The significance of management
information systems for enhancing strategic and tactical
planning ‖, Ahlia University, Manama, Kingdom of Bahrain .

36
IJRITCC | March 2018, Available @ http://www.ijritcc.org
_______________________________________________________________________________________