1) Introduction
This paper deals with the Database Modelling and mining of data to derive information on Student’s expenditure. This also provides a way by which a Student can monitor their monthly expense, view their expense pattern and offer a way by which they can implement cost cuttings.
2) Existing system :
There are various such topics already available online, example: A generic Expense Manager, an Employees expense manager and so on. But there were less focus on this such topic as not much importance were given to the expense of a student.
3) Proposed System :
The Proposed system can be used for various purposes, some of them are listed below.
- Helping students monitor with their monthly expense.
- Providing Expense patterns which might help them to control it.
- Providing them with information of how much they should save so as to extend the usage of their fixed expense.
- Providing categories on their importance/relevance - like prioritize each of their expenditures.
- Analyzing data on location level, rent analyses, travel analyses, mobile usage analyses etc.
- Finally considering this data against the salary that they will be offered having the education expense as an investment.
- Also will be helpful for those expecting to do their masters / higher studies.
4) Proposed Database Architecture :
The Database consists of Nine Database tables.
1. CONFIG_MAST - Configuration Master data – Stores the top configurations of or details on users.
2. USER_MASTER - User Master data – Contains address, email and other contact details of the users.
3. FIXED_EXPENSE - Table to store Fixed/monthly/yearly expense of students.
4. VARYING_EXPENSE - Table to store the varying/daily expense of a student.
5. EXPENSE_CATEGORY - Holds the categories available and helps for analyzing data.
6. PROF_MAST - Profession master table – Professional details of the users are stored here.
7. ORG_MAST - Organization master table – Organization in which the user is studying of working part time etc. are maintained here.
8. CITY_MAST - City master table – The origin city and all available cities in the system will be stored here along with its state and country details.
9. EMAIL_PWD - Email and password table to store user’s credentials.
The below figure gives the overall view/overview of the database.
Figure 1 - Over view of database / Database Architecture
5) Schema Implementation :
The below are the queries that were used to create all the above said tables.
CREATE COLUMN TABLE "STUDENT09"."01city_mast" ("MANDT" TINYINT CS_INT NOT NULL ,
"CITY_CODE" NVARCHAR(7) NOT NULL ,
"CITY_NAME" NVARCHAR(10),
"STATE" NVARCHAR(16),
"COUNTRY" NVARCHAR(11),
PRIMARY KEY ("MAN",
"CITY_CODE")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."01config_mast" ("MANDT" TINYINT CS_INT NOT NULL ,
"USER_NUM" NVARCHAR(5) NOT NULL ,
"PROFESSION" NVARCHAR(7) NOT NULL ,
"ORG_CODE" NVARCHAR(6) NOT NULL ,
"TRANS_CODE" NVARCHAR(11) NOT NULL ,
"TOTAL_ALLOC_AMT" SMALLINT CS_INT,
"CURRECNY" NVARCHAR(3),
"TOTAL_DURATION" TINYINT CS_INT,
"FREQUENCY_CODE" NVARCHAR(7),
"ALLOWED_EXPENDITURE" SMALLINT CS_INT,
"TOTAL_EXECUTIONS" TINYINT CS_INT,
"EXTRA_SPLITS" TINYINT CS_INT,
PRIMARY KEY ("MANDT",
"USER_NUM",
"PROFESSION",
"ORG_CODE",
"TRANS_CODE")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."01email_pwd" ("MANDT" TINYINT CS_INT NOT NULL ,
"EMAILID" NVARCHAR(28) NOT NULL ,
"USER_NUM" NVARCHAR(9),
"PWD" NVARCHAR(4),
PRIMARY KEY ("MANDT",
"EMAILID")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."xxExpense_category" ("MANDT" TINYINT CS_INT NOT NULL ,
"CATEGORY" NVARCHAR(7) NOT NULL ,
"CATEGORY_NAME" NVARCHAR(17),
"HEADER_CATEGORY" NVARCHAR(17),
PRIMARY KEY ("MANDT",
"CATEGORY")) UNLOAD PRIORITY 5 AUTO MERGE
;
ALTER TABLE "STUDENT09"."xxExpense_category" WITH PARAMETERS ('CONCAT_ATTRIBUTE'=('$CATEGORY$CATEGORY$',
'CATEGORY',
'CATEGORY'))
CREATE COLUMN TABLE "STUDENT09"."01fixed_expense" ("MANDT" TINYINT CS_INT NOT NULL ,
"TRANS_CODE" NVARCHAR(11) NOT NULL ,
"TRANSACTIONS" NVARCHAR(10) NOT NULL ,
"CATEGORY" NVARCHAR(7),
"SUB_CATEGORY" NVARCHAR(7),
"AMOUNT" SMALLINT CS_INT,
PRIMARY KEY ("MANDT",
"TRANS_CODE",
"TRANSACTIONS")) UNLOAD PRIORITY 5 AUTO MERGE
;
ALTER TABLE "STUDENT09"."01fixed_expense" WITH PARAMETERS ('CONCAT_ATTRIBUTE'=('$CATEGORY$SUB_CATEGORY$',
'CATEGORY',
'SUB_CATEGORY'))
CREATE COLUMN TABLE "STUDENT09"."01org_mast" ("MANDT" TINYINT CS_INT NOT NULL ,
"ORG_CODE" NVARCHAR(4) NOT NULL ,
"ORG_NAME" NVARCHAR(7),
"CITY_CODE" NVARCHAR(5),
PRIMARY KEY ("MANDT",
"ORG_CODE")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."01prof_mast" ("MANDT" TINYINT CS_INT NOT NULL ,
"PROFESSION" NVARCHAR(7) NOT NULL ,
"PROFESSION_NAME" NVARCHAR(17),
PRIMARY KEY ("MANDT",
"PROFESSION")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."01user_mast" ("MANDT" TINYINT CS_INT NOT NULL ,
"USER_NUM" NVARCHAR(5) NOT NULL ,
"NAME" NVARCHAR(6),
"GENDER" NVARCHAR(1),
"PER_CITY_CODE" NVARCHAR(7),
"PER_CITY_ORGIN_CODE" NVARCHAR(7),
"ADDRESS" NVARCHAR(14),
"TEL_NUM" BIGINT CS_FIXED,
"EMAILID" NVARCHAR(28),
PRIMARY KEY ("MANDT",
"USER_NUM")) UNLOAD PRIORITY 5 AUTO MERGE
CREATE COLUMN TABLE "STUDENT09"."01Varying_expense" ("MANDT" TINYINT CS_INT NOT NULL ,
"TRANS_CODE" NVARCHAR(11) NOT NULL ,
"DATE" NVARCHAR(10) NOT NULL ,
"TRANSACTIONS" NVARCHAR(12) NOT NULL ,
"CATEGORY" NVARCHAR(7),
"SUB_CATEGORY" NVARCHAR(7),
"AMOUNT" TINYINT CS_INT,
PRIMARY KEY ("MANDT",
"TRANS_CODE",
"DATE",
"TRANSACTIONS")) UNLOAD PRIORITY 5 AUTO MERGE ;
ALTER TABLE "STUDENT09"."01Varying_expense" WITH PARAMETERS ('CONCAT_ATTRIBUTE'=('$CATEGORY$SUB_CATEGORY$',
'CATEGORY',
'SUB_CATEGORY'))
CREATE COLUMN TABLE "STUDENT09"."01Varying_expense_02" ("MANDT" TINYINT CS_INT NOT NULL ,
"TRANS_CODE" NVARCHAR(11) NOT NULL ,
"DATE" NVARCHAR(9) NOT NULL ,
"TRANSACTIONS" NVARCHAR(12) NOT NULL ,
"CATEGORY" NVARCHAR(7),
"SUB_CATEGORY" NVARCHAR(7),
"AMOUNT" TINYINT CS_INT,
PRIMARY KEY ("MANDT",
"TRANS_CODE",
"DATE",
"TRANSACTIONS")) UNLOAD PRIORITY 5 AUTO MERGE
6) Attribute view Implementation :
In the language of SAP Netweaver Business Warehouse, attribute views are like Dimension, but with difference is that attribute views are fully reusable throughout the system and not stuck up to a single model. Simplifying, these are views on one or more table that can be reused for different purpose. Generally attribute views represent master data. But, however technically there is no restriction and it's possible to make attribute views on transaction data.
In our case we have created four attribute views.
- Configuration master attribute view.
- User master attribute view.
- Fixed Expense attribute view.
- Varying Expense attribute view.
The above views are designed and given below
Configuration master attribute view.
This Attribute view Consists of three tables, one containing the overall configuration details and two other providing information on profession and the organization.
Figure 2 : Configuration master attribute view
User master attribute view.
This Attribute view contains three tables, one containing the master information on the users and two others providing information on the city details and user credential details.
Figure 3: User master attribute view
User and configuration attribute view
This attribute view is almost the combination of user master view and the configuration view but with only the required details.
Figure 4: User and configuration attribute view
Fixed Expense attribute view.
This attribute view gives details of the transaction table containing fixed expense details and another table which gives information of each expense category.
Figure 5: Fixed Expense attribute view
Varying Expense attribute view.
This attribute view gives details of the transaction table containing fixed expense details and another table which gives information of each expense category.
Figure 6: Varying expense attribute view
7) Analytical view implementation :
Again, in the language on SAP BW analytical views can be roughly compared with Info Cubes or Info Sets in SAP ERP. As same it joins together one central fact table with contains transaction data to report on, with number of tables or attribute views. You can create variables in an analytical view.
Student varying expense analytical view:
This view is used analyze the varying expense of a student and display against each students who are involved.
Data Foundation block:
This Block specifies the Fact table that we have used in this analytical view.
Figure 7: Student varying expense analytical view - Data Foundation Block
Logical Join:
This gives information on how the fact table and the attribute table/views are joined.
Figure 8: Student varying expense analytical view - Logical Join
Data preview:
1) Gives details on the overall varying expenditure of a student per instance.
Figure 9: Student varying expense analytical view - Data Preview (1)
2) Gives details on the overall varying expenditure of a student.
Figure 10: Student varying expense analytical view - Data Preview(2)
Student fixed expense analytical view
This view is used analyze the fixed expense of a student and display against each students who are involved.
Data Foundation block:
This Block specifies the Fact table that we have used in this analytical view.
Figure 11: Student fixed expense analytical view - Data Foundation Block
Logical Join :
This gives information on how the fact table and the attribute table/views are joined.
Figure 12: Student fixed expense analytical view - Logical Join
Data Preview :
1) Gives details on the overall fixed expenditure of a student per instance.
Figure 13: Student fixed expense analytical view - Data Preview (1)
2) Gives details on the overall fixed expenditure of a student.
Figure 14: Student fixed expense analytical view - Data Preview (2)
😎 Calculation view implementation :
This Calculation view is created inorder to collater all the fixed and varying cost of student.
Calculation view structure :
Figure 15: Calculation View Structure
1) Displaying the fixed, varying and the total expense of a student.
Figure 16: Calculation View - Data Preview (1)
2) Displaying the fixed, varying and the total expense for all the categories available.
Figure 17: Calculation View - Data Preview (2)
3) Displaying the varying expense of a student per day.
Figure 18: Calculation View - Data Preview (3)
9) Tools Used :
- SAP HANA studio : HDB_STUDIO_REV73_WIN64
- Microsoft Excel.
10) Test Data :
Check the attached file..
11) Conclusion :
Through this system we can provide and derive various information on a student’s expenditure. A Mobile app can be developed to make monitoring of these expenses easier. Finally considering all the data gathered, it can be used against the salary that a student will be offered having the education expense as an investment. This will also provide information to the future generation of students who would like to do their masters / higher studies.
12) Reference :
- § HA300 - SAP HANA Implementation and Modeling Collection 97 SPS4 - Material number: 50110712 Version: 97.
- § SAP HANA Modeling Guide - SAP HANA Platform SPS 07, Document Version: 1.0 - 27-11-2013.
- SAP Managed Tags:
