Reporting from DW to the Internet at the Agricultural Data Processing Centre Ltd, Finland

(1)

Reporting from DW to the

Internet at the Agricultural Data

Processing Centre Ltd, Finland

SEUGI 17

June1999, The Hague

(2)

Use of on-line services

0 50000

100000

150000

200000

250000

1994

1995

1996

1997

1998

Extension

Breeding

Farmers

(5)

PC-programs for farmers

• AMMU - cow-house diary

• ELMER - Animal ID update

• POTKA - piggery diary

• PIHVI - beef cattle recording

• MLK - user interface to services

• 2700 registered contract users

• 8000 unregistered users

(6)

Why the SAS

®

System?

• MLOY is a SAS user since 1982

• extensive data extraction capability

• wide range of hardware and operative system

environments

• scalability

• references in Data Warehousing

• expertise available

(7)

Internet networks

• internets use protocols based on TCP/IP

• intranet

• an internal isolated/protected internet

• safety is not a big issue

• extranet

• an intranet with access for selected users from outside

• growing concern for moderate safety

• Internet

• the worldwide internet

(8)

Internet services

• email (mail/elm/pine..., POP3, SMTP...)

• remote terminal use (telnet, ssh...)

• file transfer (ftp)

• newsgroups (news)

• on-line meetings (talk, IRC...)

• information on people (finger...)

• miscellaneous tools (ping, ... ...)

services

(9)

DW-pilot

Niklas Data carried out a DW-pilot project for

the The Agricultural Data Processing Centre

Ltd in 1998.

On the following:

• the objectives of the project

• building and administration of the DW

• reports to the Internet

(10)

Objectives

• get started with data warehousing

• support the strategic goals of the company

• continue reporting in changing technical environment

• modernize the environment of reporting services

• be a nationwide agricultural information provider

• identify important information needs of the

owning customers into pilot subject areas

• Internet reports on selected subject areas

• propagate the idea of data warehousing among

MLOY’s owning customers

(11)

DW building and administration

• tool: SAS

Warehouse Administrator

• operational data source definitions

• data warehouse detail table definitions

• summary tables / data marts for exploitation

• process build / description

• reports linked as information mart files

• architecture: metadata and administration on

an NT workstation, data reside and processes

run on an NT server

(12)

(13)

Operational data

• on IBM mainframe (VM/VSE)

• CA Datacom

• access through ODBC

• VSAM-registries

• access through flat files (FTP)

• fast extract tool: description+%VSAM2SAS

• SAS tables on the mainframe

• CPORT/FTP

(14)

(15)

(16)

Internet reports

• target groups

• farmer - extension expert - rural centre - MKL,

the nationwide union of rural centres

• farmer - inseminator - breeding association - FABA,

the nationwide union of animal breeding associations

• official authorities, the Ministry of Agriculture

• veterinarians, researchers, consumers

• SAS IntrNet Application Dispatcher

• data services for non-SAS applications

(17)

(18)

(19)

What was reached?

• data warehousing started; support to the

strategic goals of MLOY

• technical basis for administering warehouses

• Internet reports on selected subject areas,

fulfilling information needs of two customers

• MLOY’s owning customers know more about

the benefits of data warehousing

• commitment to data warehousing as the future

means of reporting at MLOY

(20)

Internet in Data Warehousing at MLOY

• integrate systems and platforms

• connection (SAS/Connect, odbc, jdbc, ftp, http...)

• common file standards (

html

, gif, MIME-standardi)

• user identification and access

• teamwork

and share

intermediate results

• share

metadata

with data providers and

advanced analysts (data mining)

• share

dynamic models

for test & evaluation

• deliver

reports

for end users

(21)

Thank You!

Copyright © 1999, Niklas Data Group

The Niklas Data logo is a registered trademark of Niklas Data Europe B.V., Amsterdam, The Netherlands.

All other product or trade names are trademarks or registered trademarks of their respective owner.

(22)

Niklas Data Oy 15.07.99 page 1 (5) Jukka Korva

How the information system helps in the business of

selling alcoholic beverages

By Risto Husso Alko Oy / Jukka Korva Niklas Data Oy

Alko Oy

Business

Alko Ltd. sells alcoholic beverages in Finland through its nationwide network of special stores, the number of which is at the moment 256. It is an important player in the Finnish alcohol system due to its special legal status, which grants it monopoly in retail sales of alcohol directly to consumers. Strictly equal treatment of all products and providers is a prerequisite of the status. Alko is also committed to quality service even in provincial areas of the country. It has a product range of about 1300 labels originating from over 50 different countries. The monopoly retains its position even after Finland joined EU, due to the internationally recognized positive aspects of co-ordinated alcohol policy in Finland. Discussion is going on, whether wines should be available in supermarkets equally to beers. However, many people are worried about the assortment concentrating on few cheap brands, and quality wines becoming a privilege of urban citizens, if that would happen.

Organization and operating environment

In the past, what was then called Alko operated in all the businesses of production, import, wholesale and retail sales of alcoholic beverages, even owning restaurants and hotels. During past few years, these activities were reorganized in separate companies. Since January 4, 1999, in the spirit of free competition, Alko Oy is completely independent of the rest of the newly renamed Altia Group, and concentrates entirely on retail sale. Both Alko and the Altia Group are still owned by the state of Finland.

Responding to information needs

As Alko operates in a turbulent and constantly changing business environment and organization, it has the need for better business information. Since 1982, Alko has built several information systems using the SAS® System. We touch here just a few of the areas covered.

Pricing

Alko’s status as a monopoly and Finland’s membership in the European Union set strict rules for transparency and publicity in the pricing of alcoholic beverages. The application,

somewhere between operative and information systems, using the SAS® System was described at SEUGI 16 in Prague.

The system provides:

• control over the rules (transparency and publicity) behind the final price of the label

(23)

Retail outlet monthly reporting

Formerly Alko’s 256 retail stores received a monthly pile of paper reports by mail. Some of the reports were produced manually with spreadsheet software from other reports, and only part of them were directly made with the SAS® system, although all the information came from the SAS Data Warehouse.

Since March 1999, all the reports are produced from the SAS DW into Web reports that are linked to Alko’s Alvari intranet. They are also linked to each other so that they produce a powerful source of information to the store managers. The permanent graphical and table reports are produced using SAS Web Publishing Tools and the SAS/GRAPH.

Benefits of the system are:

• more timely reports

• less labor in producing and delivering the reports

• more uniform reports

• better precision as intermediate decimal rounding is avoided

• support of the intranet Alvari as a medium of information

Supplier reporting

Alcohol suppliers and agents is an important target of information. The performance of all the products is followed up in a transparent, standard manner. Whether or not a product remains in Alko’s assortment depends on its performance. The suppliers are also informed on how widely Alko keeps the products available (coverage), how large stocks are maintained, etc. This information is so far distributed either as paper reports or spreadsheet files created and sent as mail attachments automatically with the SAS® System. In the future, the information will probably be available through an extranet offered to partners.

Logistics reporting

The distances in Finland are long, and Alko needs the services of several road transport companies. Because of the fee paid for empty bottles, cases and cans, a quite high

percentage of containers are returned. While good for recycling and the environment, it also increases transportation.

Both Alko personnel responsible of logistics and the transport companies are delivered information on the flows of products and return containers via different channels. The reports are distributed by email once every month or fortnight.

Stock reporting

If a product is not available at any Alko store, the personnel can find out using a Web interface, whether the product is out-of-stock and why, if it is discontinued or temporarily outsold, and in which store has it and how much.

Quality complaints

The whole process starting from an unhappy customer claiming for a defective product at any Alko store, followed by the action taken at the Quality control department up to a possible replacement of the product or other decision, is managed using a Web user interface made with the SAS/IntrNet. If a biophysical analysis is needed, a sample is sent to the quality lab by mail.

(24)

Official reports to the authorities

Alko is responsible of producing regular reports to the Ministry of Social Affairs and Health and the social and health research center STAKES on the amounts of alcohol sold by Alko, and the distribution among different types of beverages. The Ministry of Environment is delivered information on recycling and environmental impact. The reports, mostly in paper, are produced from the SAS DW.

Reports to the management

The Data Warehouse facilitates a multitude of ad hoc reports requested by Alko’s management. Finance and Purchase need frequently tailored reports with up-to-date information. Often these reports are wanted on paper.

The SAS Data Warehouse

Alko has been building the data warehouse for some years already. In the information management, data warehousing is seen as a process rather than a project. It is utmost important that the information requirements and the possibilities offered by the new technology meet. The infrastructure in Alko gives an excellent opportunity to meet this challenge. From the beginning of 1999, SAS Warehouse Administrator was adopted as the tool of managing the growing data warehouse environment.

Operative data sources

Alko has a variety of operative systems, most of which are used as data sources in the SAS DW. Some of the most important are mentioned below.

Cash register system KAP

This system was build by ICL and it is based on cash machines connected with an NT server at each Alko store. The data are extracted daily into an operative data store (Oracle

database, HP-UX) and thereafter read into the SAS DW using SAS Access to Oracle.

Logistics system TIVA

TIVA is the system for managing purchase orders and the central product stock. It is an Oracle-based system modified by Tieto Group from the Swedish ESS system.

Human resources management system FENIX

FENIX operates in a HP9000 platform under HP-UX.

Finance management system INTIME

Finance data are increasingly important sources for the DW.

Banking system KOKOPANKKI

This system is the interface to the bank accounts of Alko.

Lotus Notes

This system, in addition to the Alvari intranet being the main information medium, is also the source for some data to the SAS DW. These include the management of offers and the register of retail stores.

(25)

Data warehouse platform

The SAS data warehouse operates in a HP9000 platform using HP-UX operating system. Interactive SAS use is mostly done via SAS/CONNECT (TCP) from NT workstations. SAS Warehouse Administrator is used at the workstations using shared (SAS/SHARE) metadata in the Unix server.

Detail and granularity

Currently the detail fact tables are horizontally split by month, and the grain is one row for each retail store (256), product (>2000), customer (only companies) and month. By the end of 1999, daily data will be stored.

Aggregated data marts and summary tables

Currently only few permanent data marts are maintained. These include the cumulative monthly sales of each year over all retail stores. In the future, extensive scheduled aggregation of data will become necessary, as daily data are too detailed for repeating aggregation.

Means of exploiting the data warehouse

The users range from management to sales personnel both in the headquarters and out in the field. Well-organized data in the data warehouse can be used in many ways.

SAS client applications

SAS on workstations is increasingly being replaced by Web interfaces. It is being used by analysts and specialized applications for a limited number of users, such as the pricing application mentioned above.

Ad-hoc reports

These reports paper reports produced by a trained SAS user at a SAS workstation. They may be on paper or ready for spreadsheet (Microsoft Excel) or word processing software (MS Word).

Dynamic Web reports

To produce dynamic HTML pages, SAS/IntrNet’s application dispatcher services are offered by the SAS Unix server. A model/viewer technique from Niklas Data separating the data creation (Base SAS programs) from its presentation and layout (HTML page templates) is employed. Dynamic reports are used where the possible variations in user selections are many and unpredictable.

Static Web reports

Where a limited number of reports is distributed to many users, it is beneficial to make them in advance into static Web pages. Excel or Word documents are produced and linked to the Web reports when desired.

Scheduled ASCII reports or Excel sheets as email attachments

The scheduling is managed with the Maestro system. Many regular reports are produced in scheduled SAS batch programs that even mail the resulting reports as attachments of email messages. Email messages are sent using the Unix mail facilities with system command pipes.

(26)

Budgeting system TARGET

The budgeting gets the sales history from the SAS data warehouse.

Management information system Gentia

Gentia is a multidimensional database “cube” with a client/server viewer system. The SAS data warehouse produces all the sales data to the Gentia system.

The

Alvari

intranet

Alvari is a Lotus Domino (Notes) implementation by ICL. Domino and SAS/IntrNet produce a seamless user interface for information delivery at Alko. The end user does not know what comes from the Notes, what from the SAS DW; what counts is good-to-know reliable information.

Risto Husso is system manager at Alko Oy, responsible among other things of the SAS Data Warehouse.

Jukka Korva is senior consultant at Niklas Data Oy, the Finnish branch of Niklas Data Group. Niklas Data Group has over 140 consultants focusing on the SAS® System.

Reporting from DW to the Internet at the Agricultural Data Processing Centre Ltd, Finland