Data Warehouse And Its Applications In Agriculture Based On Rajasthan State

Data Warehouse And Its Applications In Agriculture Based On Rajasthan State

Mr Felix Deepak Minj [HOD Dept. of IT, Shekhawati Group of Institutions, Sikar]

Introduction

A Data warehouse is a repository of integrated information, available for queries and analysis.  Data and information are extracted from heterogeneous sources as they are generated.  This makes it much easier and more efficient to run queries over data that originally came from different sources. In other words Data warehouse is a database that is used to hold data for reporting and analysis. 

A data warehouse is a single, complete and consistent data archive, extracted from different sources and made available to end-users in a form understandable and usable to them in the context of the business. A data warehouse consists of a set of subject-oriented, integrated, permanent, time-dependent data providing support to managerial decision-making.

Economic foundation and productivity growth depends on agricultural sectors. Agriculture is the driving force behind the way of live and source of earnings for the majority of peoples. More than 60 percents of population are living in rural areas and the majority are farmers. The rural communities as a main producer for country food productivity and food security earn only 11 percents of Gross Domestic Product (GDP). The arrival of information age guides this country to new development strategies.

National Electronics and Computer Technology Center (NECTEC) in collaboration with the Ministry of Agriculture, has launched "Agriculture Information Network" as a response to the unmet information requirements of the agricultural sector. Farmers should gain benefit from the contents provided which include risk assessment, agriculture warning system and agricultural knowledge base, which aim to improve technology, productivity, income and stability of India agriculture sector through the age of Information Technology. The data warehouse consists of common databases and geo-spatial databases from various departments and organizations in the country and abroad. Farmers can get access to the contents through Internet by themselves or from groups of professional people called "Information Brokers".

Abstract:

First step towards understanding any agricultural system is the comprehension of relationships between the system and numerous physical, chemical and biological factors influencing it. Any decision regarding such systems requires analytical exploration of the involved data. The exploration task is to be supported by an efficient data storage and retrieval mechanism. In this paper we have presented the case of an Agri data warehouse for this purpose. We have briefly discussed the process we adopted for establishing the data warehouse encompassing pest, pesticide and metrological data. We have also shown how implementing an OLAP tool on top of the Agri data warehouse resulted in interesting findings from a decision support point of view.

Methodology

The information system will consist of several integrated sub-systems for input, storage, retrieval, analysis and output based on strong database design with its essential functions. Besides this it will include other functions such as manipulation and dissemination of information to various users. The information system, composed of set of files for use in a RDBMS and GIS will be capable of delivering accurate, useful and timely information to various applications. Design of spatial and non-spatial database will have specifications of different data fields, their logical array and inter-relationship with subsystem database.

Data warehouse

Data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate reporting and analysis. This definition of the data warehouse focuses on data storage. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository, and tools to manage and retrieve metadata.

A data warehouse is used for answer any ad_hoc, complex, statistical or analytical queries. Data warehouse is situated at the center of a decision support system (DSS) of an organization. Data warehouse stores integrated historical data both summarized and detailed information for organization

Benefits of data warehousing

Some of the benefits that a data warehouse provides are as follows:

• A data warehouse provides a common data model for all data of interest regardless of the data's source. This makes it easier to report and analyze information than it would be if multiple data models were used to retrieve information such as sales invoices, order receipts, general ledger charges, etc.
• Prior to loading data into the data warehouse, inconsistencies are identified and resolved. This greatly simplifies reporting and analysis.
• Information in the data warehouse is under the control of data warehouse users so that, even if the source system data is purged over time, the information in the warehouse can be stored safely for extended periods of time.
• Because they are separate from operational systems, data warehouses provide retrieval of data without slowing down operational systems.
• Data warehouses can work in conjunction with and, hence, enhance the value of operational business applications, notably customer relationship management (CRM) systems.
• Data warehouses facilitate decision support system applications such as trend reports (e.g., the items with the most sales in a particular area within the last two years), exception reports, and reports that show actual performance versus goals.

A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject, that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization. Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.

A data mart is a data repository that may or may not derive from a data warehouse and that emphasizes ease of access and usability for a particular designed purpose. In general, a data warehouse tends to be a strategic but somewhat unfinished concept; a data mart tends to be tactical and aimed at meeting an immediate need.

There can be multiple data marts inside a single corporation; each one relevant to one or more business units for which it was designed. Data marts may or may not be dependent or related to other data marts in a single corporation. If the data marts are designed using conformed facts and dimensions, then they will be related. In some deployments, each department or business unit is considered the owner of its data mart including all the hardware, software and data. This enables each department to use, manipulate and develop their data any way they see fit; without altering information inside other data marts or the data warehouse. In other deployments where conformed dimensions are used, this business unit ownership will not hold true for shared dimensions like customer, product, etc.

Reasons for creating a data mart

• Easy access to frequently needed data
• Creates collective view by a group of users
• Improves end-user response time
• Ease of creation
• Lower cost than implementing a full Data warehouse
• Potential users are more clearly defined than in a full Data warehouse

OLAP:

OLAP allows business users to slice and dice data at will. Normally data in an organization is distributed in multiple data sources and are incompatible with each other. A retail example: Point-of-sales data and sales made via call-center or the Web are stored in different location and formats. It would a time consuming process for an executive to obtain OLAP reports such as - What are the most popular products purchased by customers between the ages 15 to 30?

Part of the OLAP implementation process involves extracting data from the various data repositories and making them compatible. Making data compatible involves ensuring that the meaning of the data in one repository matches all other repositories. An example of incompatible data: Customer ages can be stored as birth date for purchases made over the web and stored as age categories (i.e. between 15 and 30) for in store sales.

It is not always necessary to create a data warehouse for OLAP analysis. Data stored by operational systems, such as point-of-sales, are in types of databases called OLTPs. OLTP, Online Transaction Process, databases do not have any difference from a structural perspective from any other databases. The main difference, and only, difference is the way in which data is stored.

OLAP can be a valuable and rewarding business tool. Aside from producing reports, OLAP analysis can aid an organization evaluate balanced scorecard targets.

 Steps in the OLAP Creation Process

Agriculture Information System Network (AGRISNET):

Department of Agriculture and Cooperation (DAC) have taken steps to establish "Agricultural Information System Network (AGRISNET)" in collaboration with NIC. The Proposal recommends (i) the state-of-the-art IT infrastructure requirements to establish AGRISNET as the INTRANET over NICNET, (ii) development of databases and information systems for decision support for evaluation, monitoring and policy formulations, and (iii) human resources development, (iv) multi-media based training and demonstration of transfer of technology to strengthen Farm Research and Education using broadcast VSATs, (v) special interest groups in respect of subjects, problems, programmes, schemes, etc, and above all, to make Indian Agriculture on-line for INTERNET and INTRANET access through AGRISNET Nodes.

Geo_Phisical Setting of Rajasthan:

Rajasthan situated in the north_western part of India between 23o3’       and 30o12’ north latitudes and 69o30’ and 78o17’ east longitudes, is surrounded in north and west by Pakistan, in north_east by Panjab, Haryana and Uttar Pardesh, in south_east by Madhya Pradesh and in south_west Gujarat.

Area of 3.42 lakh sq. km. makes the state the first largest in the country having population density 165 persons per sq. km.

Aravalli Hills stretching from north_east to south_west from the most conspicuous geo physical features of the state.

Agriculture scenario of Rajasthan:

Rajasthan is predominantly agrarian state with about 70 percent of the population depending on agriculture and allied activities. Agriculture plays an important role in State economy with large contribution in State Domestic Product (SDP) viz. about 27 to 32 percent of  the Gross State Domestic Product. At present, less than one fourth of the State’s area is under irrigation. The gross cropped area has been fluctuating from year to year depending on the monsoon conditions.

Land Utilization

(Area in ,000 Hect.)

Table 1.

Sl.No.

Calssification

Year

1

2002-03

2003-04

2004-05

2005-06

2

Reporting area for land utilization purpose

34266.378

34266.151

34266.092

34266.09

3

Forest

2651.014

2660.6

2660.816

2674.961

4

Area not available for cultivation(4+5)

4278.953

4259.109

4266.913

4262.26

5

Area put to non agricultural uses

1764.582

1760.266

1775.987

1823.361

6

Barren & un-culturable land

2514.371

2498.843

2490.926

2438.899

7

Total cropped area

13217.538

21664.039

21062.486

21699.348

Cropwise Irrigated Area

(Area in ,000 Hectares)

TABLE 2

2002-03

2003-04

2004-05

2005-06

(A) FoodGrain Crops

2612.071

2913.67

2825.294

3133.512

I. Cereals

2222.194

2452.542

2442.652

2713.854

(a) Kharif Cereals

244.032

202.223

292.841

414.405

1.Rice

41.808

43.257

35.788

43.352

2. Jowar

3.666

3.385

2.883

2.919

3. Bajra

183.277

141.901

224.431

320.823

4.Maize

15.179

13.653

29.701

47.28

5. Small Millets

0.102

0.027

0.038

0.031

(b) Rabi Cereals

1978.162

2250.319

2149.811

2299.449

1. Wheat

1794.545

2076.821

1983.247

2103.466

2. Barley

183.614

173.427

166.33

195.267

3. Small Millets

0.003

0.071

0.234

0.716

II. PULSES

389.877

461.128

382.642

419.658

(a) Kharif Pulses

32.758

37.744

19.715

20.198

1. Tur

0.596

0.198

0.79

0.79

2. Other Kharif Pulses

32.162

37.546

18.925

19.408

(b) Rabi Pulses

357.119

423.384

362.0927

399.46

1.Gram

341.108

394.842

329.504

363.483

2. Other Rabi Pulses

16.011

28.542

33.423

35.977

(B) OilSeeds

1262.561

1986.422

3012.988

3360.565

(a) Kharif oilseeds

146.795

170.32

296.769

416.73

1. Groundnut

118.458

114.833

169.666

226.593

2. Sesamum

8.573

4.315

6.321

8.814

3. Castorseed

16.707

49.326

74.831

96.73

4. Soyabean

3.057

1.846

45.951

84.593

(b) Rabi Oilseeds

1115.766

1816.102

2716.219

2943.835

1. Rape & Mustard

1104.898

1803.89

2705.807

2935.567

2. Linseed

0.618

1.168

2.314

0.61

3. Taramira

10.166

10.81

11.818

7.277

4. Others

0.084

0.234

280

0.381

© Fibres

366.332

326.404

413.452

447.145

1. Cotton

366.318

326.391

413.427

447.124

2. Sanhemp

0.014

0.013

0.025

0.021

3.Mesta

0

0

0

0

(D) Sugarcane

9.668

5.539

5.524

7.643

(E) Condiments & Spices

519.193

571.812

400.623

332.814

1.Dry Chilies

18.411

25.03

25.92

17.394

2. Ginger

0.181

0.134

0.086

0.103

3.Turmeric

0.129

0.091

0.091

0.08

4.Coriander

110.322

233.438

145.421

134.902

5.Cuminseed

321.09

226.775

159.24

134.983

6.Ajwain

0.008

0.011

0.006

0.018

7. Garlic

20.896

22.528

20.604

13.441

8. Saunf

3.626

6.197

4.802

3.804

9. Methi

43.85

56.748

43.751

27.803

10. Other

0.68

0.86

0.702

0.286

(F) Fruits

19.001

20.973

21.417

21.01

(G) Vegetables

75.969

87.579

94.486

100027

1. Potato

3.531

3.84

3.151

40.153

2. Onion

26.348

33.58

42.879

42.518

3. Sweet Potato

1.296

1.601

1.975

1.504

4. Others

44.794

48.558

46.481

51.852

(H) Drug & Narcotics

129.256

108.633

87.199

86.78

1.Tobacco

0.688

0.385

0.546

0.421

2.Others

128.568

108.248

86.653

86.359

(I) Fodder Crops

275.554

369.586

228.09

324.31

1. Guarseed

78.568

177.751

59.812

162.489

2. Other

196.975

191.835

168.278

161.821

(J) Other Crops

2.586

2.659

4.114

4.23

Cropwise Area

(Area in ,000 Hectares)

TABLE 3

2001-02

2002-03

2004-05

2005-06

(A) FoodGrain Crops

12742.92

8627.821

12079.15

12530.78

I. Cereals

9385.652

6824.992

8502.839

9040.687

(a) Kharif Cereals

6924.462

4832.385

6316.95

6714.435

1.Rice

144.378

83.585

101.361

407.492

2. Jowar

614.653

532.393

568.639

592.092

3. Bajra

5129.949

3215.39

4587.712

4993.678

4.Maize

1017.433

983.553

1042.511

1004.963

5. Small Millets

18.049

17.465

16.727

16.21

(b) Rabi Cereals

2461.19

1992.607

2185.889

2326.252

1. Wheat

2287.498

1800.659

2010.241

2123.91

2. Barley

173.664

191.945

175.414

201.626

3. Small Millets

0.028

0.003

0.234

0.716

II. PULSES

3357.272

1802.829

3576.313

3490.097

(a) Kharif Pulses

2352.499

1335.218

2488.817

2363.984

1. Tur

23.805

16.531

16.916

20.388

2. Other Kharif Pulses

2328.694

1318.687

2471.901

2343.596

(b) Rabi Pulses

1004.773

467.611

1087.496

1126.113

1.Gram

969.625

449.68

1036.792

1081.932

2. Other Rabi Pulses

35.148

17.931

50.704

44.181

(B) OilSeeds

3105.618

2448.976

5154.275

5284.44

(a) Kharif oilseeds

1260.897

930.078

1468.348

1615.089

1. Groundnut

242.627

241.832

290.032

320.765

2. Sesamum

316.355

190.479

446.695

422.079

3. Castorseed

45.999

26.117

109.717

127.941

4. Soyabean

655.916

471.65

621.904

744.304

(b) Rabi Oilseeds

1844.721

1518.898

3685.927

3669.351

1. Rape & Mustard

1760.185

1191.466

3286.659

3558.678

2. Linseed

3.89

0.878

3.036

1.422

3. Taramira

80.589

326.443

395.164

106.995

4. Others

0.057

0.111

1.068

2.255

© Fibres

512.243

387.663

439.328

473.053

1. Cotton

510.147

385.685

437.776

471.563

2. Sanhemp

2.068

1.974

1.552

1.49

3.Mesta

0.028

0.004

0

0

(D) Sugarcane

9.06

9.987

5.724

7.922

(E) Condiments & Spices

774.153

534.895

418.201

349.689

1.Dry Chilies

32.694

20.412

28.016

19.247

2. Ginger

0.261

0.181

0.086

0.105

3.Turmeric

0.135

0.131

0.091

0.08

4.Coriander

204.66

112.333

148.334

136.755

5.Cuminseed

381.534

321.201

159.537

135.113

6.Ajwain

11.167

9.429

10.755

12.266

7. Garlic

15.068

20.907

20.605

13.441

8. Saunf

9.539

4.134

4.885

3.895

9. Methi

107.865

43.856

43.778

27.81

10. Other

11.23

2.311

2.114

0.977

(F) Fruits

20.26

19.928

23.043

22.178

(G) Vegetables

87.005

79.585

98.511

104.246

1. Potato

2.448

3.537

3.174

4.153

2. Onion

28.399

26.501

42.924

42.562

3. Sweet Potato

1.203

1.445

2.119

1.55

4. Others

54.955

48.102

50.294

55.981

(H) Drug & Narcotics

109.145

135.526

112.841

106.516

1.Tobacco

0.794

0.808

0.701

0.52

2.Others

108.351

134.718

112.14

105.996

(I) Fodder Crops

3387.475

932.014

2678.754

2768.169

1. Guarseed

2409.948

556.429

1944.348

2444.648

2. Other

977.527

375.585

734.406

323.521

(J) Other Crops

50.428

41.143

52.657

52.351

Conclusions

Analytical exploration of vast amount of agricultural data can best be support by appropriate application of Data Warehousing and OLAP technologies. A Data Warehouse provides efficient and reliable structure of storage for vast amount data while OLAP techniques provide mechanisms for analysis of this data.

References

[1] Data warehouse and its applications in Agriculture, Anil Rai, Indian Agricultural Statistics Research Institute Library Avenue, New Delhi.

[2] Information Technology in Agriculture, S.C. Mittal.

[3] Data Warehousing concepts, Techniques, Products and Applications, C.S.R.Prabhu.

[4] 50 years Agricultural Statistics of Rajasthan, Published form Directorate of Economics and Statistics, Jaipur

[5] Data Ware housing ,C.S.R.Prabhu

[6] Data Mining, Jiawei Han, Micheline Kamber

[7] www.statistical.rajasthan.gov.in