olap for a small company
In a post Multidimensional Cubes, OLAP and MDX Vitko wrote: “the topic is very interesting and every day it becomes more and more relevant”. Unfortunately, this spell has been pronounced for a very long time (at least I have heard it since 2004), but the olap projects are still very few. Perhaps because it is traditionally considered that everything connected with olap is needed only for large companies with large amounts of accumulated data and is very expensive. But it is not so. I want to talk about a project that is implemented in one relatively small company.
The project is very ancient, it began in 2003. About some things you can say "as it happened historically." But, the general idea seems to me, it can be useful.
So. The company is engaged in wholesale trade in confectionery. Confectionary opt specific business. At relatively low revs you have to deal with large amounts of data. Clients of the company are both large retail chains and small shops in the villages of the region. Plus a huge range of products. Moreover, the client can buy any amount of goods - from one sneaker to a carriage of biscuits (there were precedents when half of the marshmallows arrived at the return warehouse, (history is silent about what the reason for the return was)).
The main accounting system is 1C “Trade and Warehouse” 7.0, with the dbf version. She successfully copes with the tasks of accounting goods. But to get reports in it for large periods of time is almost impossible. Such attempts create a serious load on the server, problems begin for operators 1c, complaints to the It department.
')
The need for such reports was constant. There was an ideal situation for the implementation of the bi project: a large amount of information + people interested in its analysis.
For a start, a small video demonstrating how a user can receive information himself.
You can watch avi in normal quality by downloading 5.25Mb from here (6 minutes)
You can work with a local cube by downloading the example 2.64Mb
or here 8Mb
Blue arrows - the ways in which information enters the system, green - how information is subsequently used.
Information from the storage is used not only by cubes, but also by external applications, for example, these data are needed for calculating salaries, for accounting for payment-deliveries, for planning the work of a manager. At the same time, the data from these external programs also fall into cubes.
Employees in the office work with cubes - management, managers, marketing, accounting. The same information is sent to suppliers and sales representatives in different cities of the region.
Sometimes the user needs to periodically receive reports containing large amounts of data. For example, the marketing department sent reports to suppliers in the form of Eksel files containing several dozen pages.
Olap is not “sharpened” to receive such information - reports have been generated for a very long time.
As a rule, the supplier is also inconvenient to work with large reports. Therefore, the majority, trying to work with local cubes, agreed to receive reports in this form. The list of reports that formed the marketing department, significantly reduced. The remaining heavy reports were implemented in SSRS, subscriptions were created (reports are generated automatically and sent to suppliers on a schedule)
processor: 2xAMD Opteron 280
memory: 4Gb
disk arrays:
operating system: RAID 1 (mirror) 2xSCSI 15k
data: RAID 0 + 1 4xSCSI 10k
Agree, such a machine can hardly be called a "powerful" server
10GB storage, data since 2002
aggregation 30%
Size of multidimensional base 350M
number of members of the "big dimensions": goods 25 thousand, addresses - 20 thousand
number of documents per day - 400. average number of lines in the document - 30
The project is very ancient, it began in 2003. About some things you can say "as it happened historically." But, the general idea seems to me, it can be useful.
So. The company is engaged in wholesale trade in confectionery. Confectionary opt specific business. At relatively low revs you have to deal with large amounts of data. Clients of the company are both large retail chains and small shops in the villages of the region. Plus a huge range of products. Moreover, the client can buy any amount of goods - from one sneaker to a carriage of biscuits (there were precedents when half of the marshmallows arrived at the return warehouse, (history is silent about what the reason for the return was)).
The main accounting system is 1C “Trade and Warehouse” 7.0, with the dbf version. She successfully copes with the tasks of accounting goods. But to get reports in it for large periods of time is almost impossible. Such attempts create a serious load on the server, problems begin for operators 1c, complaints to the It department.
')
The need for such reports was constant. There was an ideal situation for the implementation of the bi project: a large amount of information + people interested in its analysis.
For a start, a small video demonstrating how a user can receive information himself.
You can watch avi in normal quality by downloading 5.25Mb from here (6 minutes)
You can work with a local cube by downloading the example 2.64Mb
or here 8Mb
How it is implemented:
Blue arrows - the ways in which information enters the system, green - how information is subsequently used.
- Information about orders is entered into the system 1c - dbf version.
- Download data "autoexchange". Actually, this is an extra step. Data can be obtained directly from the dbf database. But 1c programmers decided that the standard (for 1c) data upload mechanism would do less harm.
- Once a day, the changes for the past day are uploaded to a specially prepared database MsSql - storage. Not all information is uploaded, but only what is needed for cubes.
In principle, it is not necessary to build a “repository”. Data for a cube can be obtained directly from the 1c database (MsSQL or dbf). But in my case, from 1c, data from past periods are periodically deleted and reference books are cleared. In addition, before loading into the repository, the data is slightly “cleaned”. - The cube is recalculated - the data falls into the cube.
Information from the storage is used not only by cubes, but also by external applications, for example, these data are needed for calculating salaries, for accounting for payment-deliveries, for planning the work of a manager. At the same time, the data from these external programs also fall into cubes.
Employees in the office work with cubes - management, managers, marketing, accounting. The same information is sent to suppliers and sales representatives in different cities of the region.
Any user can get information in different ways:
- Build a report yourself on a web page or in excel
At first, only excel was used, but there were many problems with the fact that the Eksel files “scattered”, it was necessary to get one “entry point” to select the information.
Therefore, a local site was created, which published pages with PivotTable. An employee who wants to get a couple of numbers "here and now" comes to this site and builds a report in the form he needs. If a person needs to use this report in the future, he can write a request so that his report is published in SSRS or he himself saves it in excel. - View a standard report published in SQL Server Reporting Services (SSRS)
- Get a local cube - and rotate data out of the office using excel
- Subscribe to the newsletter and receive standard reports from SSRS by e-mail
- The marketing department also uses the CubeSlice program. It is possible to create local cubes on your own and much more conveniently than in excel.
Local Cubes
Sometimes the user needs to periodically receive reports containing large amounts of data. For example, the marketing department sent reports to suppliers in the form of Eksel files containing several dozen pages.
Olap is not “sharpened” to receive such information - reports have been generated for a very long time.
As a rule, the supplier is also inconvenient to work with large reports. Therefore, the majority, trying to work with local cubes, agreed to receive reports in this form. The list of reports that formed the marketing department, significantly reduced. The remaining heavy reports were implemented in SSRS, subscriptions were created (reports are generated automatically and sent to suppliers on a schedule)
Basic system parameters
Server configuration:
processor: 2xAMD Opteron 280
memory: 4Gb
disk arrays:
operating system: RAID 1 (mirror) 2xSCSI 15k
data: RAID 0 + 1 4xSCSI 10k
Agree, such a machine can hardly be called a "powerful" server
Data volume:
10GB storage, data since 2002
aggregation 30%
Size of multidimensional base 350M
number of members of the "big dimensions": goods 25 thousand, addresses - 20 thousand
number of documents per day - 400. average number of lines in the document - 30
As a result, the company received:
pros
For enterprise management
It allows you to look at the situation from above, to identify the general patterns of business development.
It helps to track the dynamics of changes in the main indicators of the organization as a whole and to promptly evaluate the performance indicators of subordinates.For manager
The ability to independently and in a short time to obtain the information necessary for making a decision.
Ease of work. All actions are intuitive.For suppliers
Possibility of interactive work with informationFrom the point of view of an IT specialist
Reduced routine work. The user receives most of the reports independently.
Minuses:
- The cost of implementation. Additional hardware and software needed.
- Shortage of trained specialists. The cost of training employees of the it-department.
Source: https://habr.com/ru/post/66920/
All Articles