Olap cube measures and dimensions. What is a cube? OLAP on client and server

/ In a cubist manner. Application of OLAP cubes in management practice of large companies

In contact with

Classmates

Konstantin Tokmachev, system architect

In a cubist style.
Application of OLAP cubes in management practice of large companies

Perhaps the time has passed when a corporation's computing resources were spent only on recording information and accounting reports. At the same time, management decisions were made “by eye” in offices, at meetings and meetings. Perhaps in Russia it’s time to return corporate computing systems to their main resource - solving management problems based on data registered in the computer

About the benefits of business analytics

In the corporate management loop, between the “raw” data and the “levers” of influencing the managed object, there are “performance indicators” - KPIs. They form a kind of “dashboard”, reflecting the state of various subsystems of the controlled object. Equipping a company with informative performance indicators and monitoring their calculation and obtained values ​​is the work of a business analyst. Automated analysis services, such as the MS utility, can provide significant assistance in organizing the analytical work of a corporation. SQL Server Analysis Services (SSAS) and its main feature is the OLAP cube.

One more point needs to be made right here. Let's say, in the American tradition, a specialty focused on working with OLAP cubes is called BI (Business Intelligence). There should be no illusions that the American BI corresponds to the Russian “business analyst”. No offense, but often our business analyst is an “under-accountant” and “under-programmer”, a specialist with vague knowledge and a small salary, who really does not have any of his own tools and methodology.

A BI specialist is, in fact, an applied mathematician, a highly qualified specialist who puts modern mathematical methods into the company’s arsenal (what was called Operations Research - methods of operations research). BI is more consistent with the specialty “system analyst” that was once in the USSR, graduated from the Faculty of Computational Mathematics and Mathematics of Moscow State University. M.V. Lomonosov. The OLAP cube and analysis services can become a promising basis for the workplace of a Russian business analyst, perhaps after some advanced training in the direction of American BI.

Recently, another harmful trend has emerged. Thanks to specialization, mutual understanding between different categories of corporation employees has been lost. An accountant, manager and programmer, like “a swan, a crayfish and a pike” in I.A.’s fable. Krylov, are pulling the corporation in different directions.

The accountant is busy with reporting; his amounts, both in meaning and in dynamics, are not directly related to the business process of the company.

The manager is busy with his part of the business process, but is not able to evaluate globally, at the level of the company as a whole, the results and prospects of his actions.

Finally, the programmer, who was once (thanks to his education) a conductor of advanced technical ideas from the sphere of science to the sphere of business, has turned into a passive executor of the fantasies of the accountant and manager, so it is no longer uncommon for the IT departments of corporations to be driven by accountants and, in general, everyone to whom not lazy. A lack of initiative, illiterate, but relatively highly paid 1C programmer is a real scourge Russian corporations. (Almost like a domestic football player.) I’m not even talking about the so-called “economists and lawyers”; everything has been said about them a long time ago.

So, the position of a business analyst, equipped with a knowledge-intensive SSAS apparatus, proficient in the basics of programming and accounting, is capable of consolidating the work of the company in relation to the analysis and forecast of the business process.

Advantages of OLAP cubes

OLAP cube is modern remedy analysis of the corporate computer system database, which makes it possible to provide employees at all levels of the hierarchy with the required set of indicators that characterize manufacturing process companies. The point is not only that the convenient interface and flexible query language for the MDX cube (MultiDimensional eXpressions) allow you to formulate and calculate the necessary analytical indicators, but the remarkable speed and ease with which the OLAP cube does this. Moreover, this speed and ease, within certain limits, do not depend on the complexity of calculations and the size of the database.

Some introduction to OLAP-
cube can be given by a “pivot table” of MS Excel. These objects have similar logic and similar interfaces. But, as will be seen from the article, OLAP functionality is incomparably richer, and performance is incomparably higher, so the “pivot table” remains a local desktop product, while OLAP is an enterprise-level product.

Why the OLAP cube is so good for solving analytical tasks? The OLAP cube is designed in such a way that all indicators in all possible sections are pre-calculated (in whole or in part), and the user can only “pull out” the required indicators (measures) and dimensions (dimensions) with the mouse, and the program can redraw the tables.

All possible analytics in all sections form one huge field, or rather, not a field, but just a multidimensional OLAP cube. Whatever request the user (manager, business analyst, executive) turns to the analytics service, the speed of response is explained by two things: firstly, the required analytics can be easily formulated (either selected from a list by name, or specified by a formula in the MDX language ), secondly, as a rule, it has already been calculated.

The formulation of analytics is possible in three options: it is either a database field (or rather, a warehouse field), or a calculation field defined at the cube design level, or an MDX language expression when working interactively with the cube.

This means several attractive features of OLAP cubes. Essentially, the barrier between the user and the data disappears. The barrier is in the form of an application programmer, who, firstly, needs to explain the problem (set a task). Secondly, you will have to wait for the application programmer to create an algorithm, write and debug the program, and then possibly modify it. If there are many employees and their requirements are varied and changeable, then a whole team of application programmers is needed. In this sense, an OLAP cube (and a qualified business analyst) replaces an entire team of application programmers in terms of analytical work, just as a powerful excavator with an excavator operator replaces an entire team of migrant workers with shovels when digging a ditch!

At the same time, another very important quality of the obtained analytical data is achieved. Since there is only one OLAP cube for the entire company, i.e. This is the same field with analysts for everyone, which eliminates annoying discrepancies in the data. When a manager has to ask the same task to several independent employees in order to eliminate the factor of subjectivity, but they still bring different answers, which everyone undertakes to explain somehow, etc. The OLAP cube ensures uniformity of analytical data at different levels of the corporate hierarchy, i.e. if a manager wants to detail a certain indicator of interest to him, then he will certainly come to the lower-level data with which his subordinate works, and this will be precisely the data on the basis of which the higher-level indicator was calculated, and not some other data, received in some other way, at some other time, etc. That is, the entire company sees the same analytics, but at different levels of aggregation.

Let's give an example. Let's say a manager controls accounts receivable. As long as the KPI for overdue receivables is green, it means everything is normal and no management actions are required. If the color has changed to yellow or red, something is wrong: we cut the KPIs by sales departments and immediately see the departments “in red”. The next section by managers - and the seller whose clients are behind on payments is identified. (Further, the overdue amount can be divided by customers, by terms, etc.) The head of the corporation can directly contact the violators at any level. But in general, the same KPI (at their hierarchy levels) is seen by both department heads and sales managers. Therefore, in order to correct the situation, they don’t even need to wait for a “call on the carpet”... Of course, the KPI itself does not necessarily have to be the amount of overdue payments - it can be the weighted average period of overdue payments or, in general, the rate of turnover of receivables.

Let us note that the complexity and flexibility of the MDX language, together with the fast (sometimes instantaneous) results, allows us to solve (taking into account the stages of development and debugging) complex control tasks that otherwise might not have been posed at all due to the complexity for application programmers and initial uncertainty in the formulation. (Lengthy deadlines for application programmers to solve analytical problems due to poorly understood formulations and long modifications of programs when conditions change are often encountered in practice.)

Let us also pay attention to the fact that each employee of the company can collect from the general field an OLAP analyst exactly the harvest that he needs for his work, and not be content with the “strip” that is cut out for him in communal “standard reports”.

The multi-user interface for working with an OLAP cube in client-server mode allows each employee, independently of others, to have their own (even self-made with some skill) analytics blocks (reports), which, once defined, are automatically updated - in other words, they are always up to date condition.

That is, the OLAP cube allows you to make analytical work (which is actually carried out not only by reception analysts, but, in fact, by almost all employees of the company, even logisticians and managers who control balances and shipments) more selective, “not in general terms” , which creates conditions for improving work and increasing productivity.

To summarize our introduction, we note that the use of OLAP cubes can raise the management of a company to a higher level. The uniformity of analytical data at all levels of the hierarchy, their reliability, complexity, ease of creating and modifying indicators, individual settings, high speed of data processing, and finally, saving money and time spent on supporting alternative analytical paths (application programmers, employee’s independent calculations) open up prospects for the use of OLAP cubes in the practice of large Russian companies.

OLTP + OLAP: outline feedback in the company management chain

Now let's look at the general idea of ​​OLAP cubes and their point of application in the corporate management chain. The term OLAP (OnLine Analytical Processing) was introduced by the British mathematician Edgar Codd in addition to his previously introduced term OLTP (OnLine Transactions Processing). This will be discussed later, but E. Codd, of course, proposed not only the terms, but also the mathematical theories of OLTP and OLAP. Without going into details, in the modern interpretation, OLTP is a relational database, considered as a mechanism for recording, storing and retrieving information.

Solution methodology

ERP systems (Enterprice Resource Planning), such as 1C7, 1C8, MS Dynamics AX, have user-oriented software interfaces (entering and editing documents, etc.) and a relational database (DB) for storing and retrieving information , represented today by software products such as MS SQL Server (SS).

Note that the information registered in the ERP system database is indeed a very valuable resource. The point is not only that the registered information ensures the current document flow of the corporation (extracting documents, adjusting them, the ability to print and reconcile, etc.) and not only the ability to calculate financial statements (taxes, audit, etc.). From a management point of view, it is much more important that the OLTP system (relational database) is, in fact, an actual life-size digital model of the corporation’s activities.

But to manage the process, it is not enough to register information about it. The process should be presented in the form of a system of numerical indicators (KPIs) characterizing its progress. In addition, acceptable ranges of values ​​must be defined for indicators. And only if the value of the indicator falls outside the permissible interval, a control action should follow.

Regarding this logic (or mythology) of control (“control by deviation”), both the ancient Greek philosopher Plato, who created the image of the helmsman (cybernose), who leans on the oar when the boat deviates from the course, and the American mathematician Norbert Wiener, who created the science of cybernetics on the eve of computer era.

In addition to the usual system for recording information using the OLTP method, another system is needed - a system for analyzing the collected information. This add-on, which in the control loop plays the role of feedback between management and the control object, is an OLAP system or, in short, an OLAP cube.

As a software implementation of OLAP, we will consider the MS Analysis Services utility, which is part of the standard delivery of MS SQL Server, abbreviated SSAS. Note that, according to E. Codd’s plan, the OLAP cube in analytics should give the same comprehensive freedom of action that the OLTP system and the relational database (SQL Server) provide in storing and retrieving information.

OLAP Logistics

Now let's look at the specific configuration external devices, application programs and technological operations on which the automated operation of the OLAP cube is based.

We will assume that the corporation uses an ERP system, for example, 1C7 or 1C8, within which information is recorded as usual. The database of this ERP system is located on a certain server and is supported by MS SQL Server.

We will also assume that another server has software installed, including MS SQL Server with the MS Analysis Services (SSAS) utility, as well as MS SQL Server Management Studio, MS C#, MS Excel and MS Visual Studio. These programs together form the required context: the tools and necessary interfaces for the developer of OLAP cubes.

The SSAS server has a freely distributed program called blat, called (with parameters) from command line and providing postal service.

At employee workstations, within local network, among other things, MS Excel programs (versions no less than 2003) are installed, as well as, possibly, a special driver to ensure that MS Excel works with MS Analysis Services (unless the corresponding driver is already included in MS Excel).

For definiteness, we will assume that an operating system is installed at employee workstations. Windows system XP, and on servers - Windows Server 2008. In addition, let MS SQL Server 2005 be used as the SQL Server, with Enterprise Edition (EE) or Developer Edition (DE) installed on the server with the OLAP cube. In these editions it is possible to use the so-called. “semi-additive measures”, i.e. additional aggregate functions(statistics) other than ordinary sums (for example, extreme or average).

OLAP cube design (OLAP cubism)

Let's say a few words about the design of the OLAP cube itself. In the language of statistics, an OLAP cube is a set of performance indicators calculated in all necessary sections, for example, the shipment indicator in sections by customers, by goods, by dates, etc. Due to direct translation from English in Russian literature on OLAP cubes, indicators are called “measures”, and sections are called “dimensions”. This is a mathematically correct, but syntactically and semantically not very successful translation. The Russian words “measure”, “dimension”, “dimension” are almost the same in meaning and spelling, while the English “measure” and “dimension” are different in both spelling and meaning. Therefore, we give preference to the traditional Russian statistical terms “indicator” and “cut”, which are similar in meaning.

There are several options for software implementation of an OLAP cube in relation to the OLTP system where data is recorded. We will consider only one scheme, the simplest, most reliable and fastest.

In this design, OLAP and OLTP do not share tables, and OLAP analytics are calculated in as much detail as possible during the cube update (Process) stage, which precedes the usage stage. This scheme is called MOLAP (Multidimensional OLAP). Its disadvantages are asynchrony with ERP and high memory costs.

Although formally an OLAP cube can be built using all (thousands) of ERP system relational database tables as a data source and all (hundreds) of their fields as indicators or sections, in reality this should not be done. Vice versa. To load into a cube, it is more correct to prepare a separate database, called a “showcase” or “warehouse”.

Several reasons force us to do this.

• Firstly, Linking an OLAP cube to tables in a real database will certainly create technical problems. Changing data in a table can trigger a refresh of the cube, and refreshing a cube is not necessarily a fast process, so the cube will be in a state of constant rebuilding; At the same time, the cube update procedure can block (when reading) the data of the database tables, slowing down the work of users in registering data in the ERP system.
• Secondly, Having too many indicators and cuts will dramatically increase the storage area of ​​the cube on the server. Let’s not forget that the OLAP cube stores not only the source data, as in the OLTP system, but also all the indicators summed up over all possible sections (and even all combinations of all sections). In addition, the speed of updating the cube and, ultimately, the speed of building and updating analytics and user reports based on them will slow down accordingly.
• Third, too many fields (indicators and sections) will create problems in the OLAP developer interface, because the lists of elements will become immense.
• Fourthly, The OLAP cube is very sensitive to data integrity violations. The cube cannot be built if the key data is not located at the link specified in the structure of the cube field connections. Temporary or permanent integrity violations and blank fields are common in an ERP system database, but this is absolutely not suitable for OLAP.

You can also add that the ERP system and the OLAP cube should be located on different servers to share the load. But then, if there are common tables for OLAP and OLTP, the problem of network traffic also arises. Practically insoluble problems arise in this case when it is necessary to consolidate several disparate ERP systems (1C7, 1C8, MS Dynamics AX) into one OLAP cube.

Probably, we can continue to pile up technical problems. But most importantly, remember that, unlike OLTP, OLAP is not a means of recording and storing data, but an analytics tool. This means that there is no need to upload and download “dirty” data from ERP to OLAP “just in case.” On the contrary, you must first develop a concept for managing the company, at least at the level of the KPI system, and then design an application data warehouse (warehouse), located on the same server as the OLAP cube, and containing a small, refined amount of data from ERP necessary for management .

Without promoting bad habits, the OLAP cube in relation to OLTP can be likened to the well-known “still”, through which a “pure product” is extracted from the “fermented mass” of real registration.

So, we got that the data source for OLAP is a special database (warehouse), located on the same server as OLAP. Generally this means two things. First, there must be special procedures that will create a warehouse from ERP databases. Secondly, the OLAP cube is asynchronous with its ERP systems.

Taking into account the above, we propose the following version of the computing process architecture.

Solution architecture

Suppose there are many ERP systems of a certain corporation (holding) located on different servers, the analytical data for which we would like to see consolidated within one OLAP cube. We emphasize that in the technology described, we combine data from ERP systems at the warehouse level, leaving the design of the OLAP cube unchanged.

On the OLAP server we create images (blank copies) of the databases of all these ERP systems. We periodically (nightly) perform partial replication of the corresponding active ERP databases onto these empty copies.

Next, SP (stored procedure) is launched, which, on the same OLAP server without network traffic, based on partial replicas of ERP system databases, creates (or replenishes) a warehouse (warehouse) - the data source of the OLAP cube.

Then the standard procedure for updating/building a cube based on warehouse data is launched (Process operation in the SSAS interface).

Let us comment on certain aspects of the technology. What kind of work do SPs do?

As a result of partial replication, current data appears in the image of some ERP system on the OLAP server. By the way, partial replication can be performed in two ways.

Firstly, from all the tables in the ERP system database, during partial replication, only those that are needed to build a warehouse are copied. This is controlled by a fixed list of table names.

Secondly, partial replication may also mean that not all fields of the table are copied, but only those that are involved in building the warehouse. The list of fields to copy is either specified or dynamically created in SP in the image of the copy (if not all fields are initially present in the copy of the table).

Of course, it is possible not to copy entire table rows, but only to add new records. However, this creates serious inconveniences when accounting for ERP revisions “retroactively,” which is often the case in real-life systems. So it’s easier, without further ado, to copy all records (or update the “tail” starting from a certain date).

Next, the main task of SP is to convert ERP system data to warehouse format. If there is only one ERP system, then the task of conversion mainly comes down to copying and possibly reformatting the necessary data. But if it is necessary to consolidate several ERP systems of different structures in the same OLAP cube, then the transformations become more complicated.

The task of consolidating several different ERP systems in a cube is especially difficult if the sets of their objects (directories of goods, contractors, warehouses, etc.) partially overlap, the objects have the same meaning, but are naturally described differently in the directories of different systems (in sense of codes, identifiers, names, etc.).

In reality, such a picture arises in a large holding company, when several of its constituent autonomous companies of the same type carry out approximately the same types of activities in approximately the same territory, but use their own and non-agreed registration systems. In this case, when consolidating data at the warehouse level, you cannot do without auxiliary mapping tables.

Let's pay some attention to the warehouse storage architecture. Typically, an OLAP cube schema is represented in the form of a “star”, i.e. as a data table surrounded by “rays” of directories - tables of secondary key values. A table is a block of “indicators”; reference books are their sections. In this case, the directory, in turn, can be an arbitrary unbalanced tree or a balanced hierarchy, for example, a multi-level classification of goods or contractors. In an OLAP cube, the numeric fields of a data table from a warehouse automatically become “indicators” (or measures), and sections (or dimensions) can be defined using secondary key tables.

This is a visual “pedagogical” description. In fact, the architecture of an OLAP cube can be much more complex.

Firstly, a warehouse can consist of several “stars”, possibly connected through common directories. In this case, the OLAP cube will be a union of several cubes (several data blocks).

Secondly, the “ray” of an asterisk can be not just one directory, but an entire (hierarchical) file system.

Thirdly, on the basis of existing dimension sections, new hierarchical sections can be defined using the OLAP developer interface tools (say, with fewer levels, with a different order of levels, etc.)

Fourthly, based on existing indicators and sections, using MDX language expressions, new indicators (calculations) can be defined. It is important to note that new cubes, new indicators, new sections are automatically fully integrated with the original elements. It should also be noted that poorly formulated calculations and hierarchical sections can significantly slow down the operation of an OLAP cube.

MS Excel as an interface with OLAP

Of particular interest is the user interface with OLAP cubes. Naturally, the most complete interface is provided by the SSAS utility itself. This includes an OLAP cube developer toolkit, an interactive report designer, and a window interactive work with an OLAP cube using MDX queries.

In addition to SSAS itself, there are many programs that provide an interface to OLAP, covering their functionality to a greater or lesser extent. But among them there is one, which, in our opinion, has undeniable advantages. This is MS Excel.

The interface with MS Excel is provided by a special driver, downloadable separately or included in the Excel distribution. It does not cover all the functionality of OLAP, but with the growth of MS Excel version numbers, this coverage is becoming wider (for example, in MS Excel 2007 a graphical representation of KPI appears, which was not in MS Excel 2003, etc.).

Of course, in addition to its fairly complete functionality, the main advantage of MS Excel is the widespread distribution of this program and the close familiarity with it of the overwhelming number of office users. In this sense, unlike other interface programs, the company does not need to purchase anything additional and does not need to train anyone additionally.

The great advantage of MS Excel as an interface with OLAP is the ability to further independently process the data obtained in the OLAP report (i.e., continue to study data obtained from OLAP on other sheets of the same Excel, no longer using OLAP tools, but using regular Excel tools).

Facubi nightly treatment cycle

Now we will describe the daily (nightly) computational cycle of OLAP operation. The calculation is carried out under the control of the facubi program, written in C# 2005 and launched via Task Scheduler on a server with warehouse and SSAS. At the beginning, facubi goes to the Internet and reads current exchange rates (used to represent a number of indicators in a currency). Next, perform the following steps.

First, facubi launches SPs that perform partial replication of databases of various ERP systems (holding elements) available on the local network. Replication is performed, as we said, to pre-prepared “backgrounds” - images of remote ERP systems located on the SSAS server.

Secondly, through SP, a mapping is performed from ERP replicas to the warehouse storage - a special DB, which is the source of OLAP cube data and located on the SSAS server. In this case, three main tasks are solved:

• ERP data adjusted to the required cube formats; we're talking about both about tables and table fields. (Sometimes the required table needs to be “fashioned,” say, from several MS Excel sheets.) Similar data may have different formats in different ERPs, for example, key ID fields in 1C7 directories have a 36-digit character code of length 8, and _idrref fields in directories 1С8 – hexadecimal numbers of length 32;
• during processing logical data control is carried out (including writing “defaults” in place of missing data, where possible) and integrity control, i.e. checking the presence of primary and secondary keys in the corresponding classifiers;
• code consolidation objects that have the same meaning in different ERPs. For example, the corresponding elements of directories of different ERPs may have the same meaning, say, they are the same counterparty. The problem of consolidating codes is solved by constructing mapping tables, where various codes the same objects are brought to unity.

Thirdly, facubi launches standard procedure updating Process cube data (from the SSAS utility procedures).

Based on the checklists, facubi sends emails about the progress of processing steps.

After executing facubi, Task Scheduler launches several excel files in turn, in which reports have been pre-created based on OLAP cube indicators. As we said, MS Excel has a special software interface(separately downloadable or built-in driver) for working with OLAP cubes (with SSAS). When you start MS Excel, MS VBA programs (such as macros) are activated, which ensure that data in reports is updated; reports are modified if necessary and sent by mail (blat program) to users according to checklists.

Local network users with access to the SSAS server will receive “live” reports configured for the OLAP cube. (In principle, they themselves, without any mail, can update OLAP reports in MS Excel that are on their local computers.) Users outside the local network will either receive original reports, but with limited functionality, or for them (after updating OLAP reports in MS Excel) special “dead” reports will be calculated that do not access the SSAS server.

Evaluation of results

We talked above about the asynchrony of OLTP and OLAP. In the technology variant under consideration, the OLAP cube update cycle is performed at night (say, it starts at 1 am). This means that in the current working day, users are working with yesterday's data. Since OLAP is not a recording tool (look at the latest revision of the document), but a management tool (understand the trend of the process), such a lag is usually not critical. However, if necessary, even in the described version of the cube architecture (MOLAP), the update can be carried out several times a day.

The execution time of update procedures depends on the design features of the OLAP cube (more or less complexity, more or less successful definitions of indicators and sections) and on the volume of databases of external OLTP systems. According to experience, the warehouse construction procedure takes from several minutes to two hours, the cube update procedure (Process) takes from 1 to 20 minutes. We are talking about complex OLAP cubes that unite dozens of star-type structures, dozens of common “rays” (reference sections) for them, and hundreds of indicators. Estimating the volume of databases of external ERP systems based on shipping documents, we are talking about hundreds of thousands of documents and, accordingly, millions of product lines per year. The historical processing depth of interest to the user was three to five years.

The described technology has been used in a number of large corporations: since 2008 in the Russian Fish Company (RRK) and the Russian Sea company (RM), since 2012 in the Santa Bremor company (SB). Some corporations are primarily trading and purchasing firms (PPCs), others are production companies (fish and seafood processing plants in the Republic of Moldova and the Republic of Belarus). All corporations are large holdings, uniting several companies with independent and various computer accounting systems - ranging from standard ERP systems such as 1C7 and 1C8 to “relic” accounting systems based on DBF and Excel. I will add that the described technology for operating OLAP cubes (without taking into account the development stage) either does not require special employees at all, or is the responsibility of one full-time business analyst. The problem has been swirling around for years automatic mode, providing various categories of corporate employees with up-to-date reporting on a daily basis.

Pros and cons of the solution

Experience shows that the proposed solution is quite reliable and easy to use. It is easily modified (connection/disconnection of new ERPs, creation of new indicators and sections, creation and modification of Excel reports and their mailing lists) with invariance control program facubi.

MS Excel as an interface with OLAP provides sufficient expressiveness and allows different categories of office employees to quickly become familiar with OLAP technology. The user receives daily “standard” OLAP reports; using the MS Excel interface with OLAP, can independently create OLAP reports in MS Excel. In addition, the user can independently continue to study the information of OLAP reports using the usual capabilities of his MS Excel.

The “refined” warehouse database, in which several heterogeneous ERP systems are consolidated (during the construction of the cube), even without any OLAP, allows you to solve (on an SSAS server, using the query method in Transact SQL or the SP method, etc.) many applied management problems. Let us recall that the warehouse database structure is unified and much simpler (in terms of the number of tables and the number of table fields) than the database structures of the original ERP.

We especially note that in our proposed solution there is the possibility of consolidating various ERP systems in one OLAP cube. This allows you to obtain analytics for the entire holding and maintain long-term continuity in analytics when a corporation moves to another accounting ERP system, say, when moving from 1C7 to 1C8.

We used the MOLAP cube model. The advantages of this model are reliability in operation and high speed of processing user requests. Disadvantages: OLAP and OLTP are asynchronous, as well as large amounts of memory for storing OLAP.

In conclusion, here is another argument in favor of OLAP that might have been more appropriate in the Middle Ages. Because its evidentiary power rests on authority. A modest, clearly underrated British mathematician E. Codd developed the theory of relational databases in the late 60s. The power of this theory was such that now, after 50 years, it is already difficult to find a non-relational database and a database query language other than SQL.

OLTP technology, based on the theory of relational databases, was the first idea of ​​E. Codd. In fact, the concept of OLAP cubes is his second idea, expressed by him in the early 90s. Even without being a mathematician, you can quite expect that the second idea will be as effective as the first. That is, in terms of computer analytics, OLAP ideas will soon take over the world and displace all others. Simply because the topic of analytics finds its comprehensive mathematical solution in OLAP, and this solution is “adequate” (B. Spinoza’s term) to the practical problem of analytics. “Adequately” means in Spinoza that God himself could not have thought of anything better...

1. Larson B. Development of business analytics in Microsoft SQL Server 2005. – St. Petersburg: “Peter”, 2008.
2. Codd E. Relational Completeness of Data Base Sublanguages, Data Base Systems, Courant Computer Science Sumposia Series 1972, v. 6, Englwood cliffs, N.Y., Prentice – Hall.

In contact with

Perhaps for some, the use of OLAP technology (On-line Analytic Processing) when creating reports will seem somewhat exotic, so the use of OLAP-CUBE for them is not at all one of the most important requirements when automating budgeting and management accounting.

It's actually very convenient to use multidimensional CUBE when working with management reporting. When developing budget formats, you may encounter the problem of multivariate forms (you can read more about this in Book 8, “Technology for setting up budgeting in a company,” and in the book, “Setting up and automating management accounting”).

This is due to the fact that effective management of a company requires increasingly detailed management reporting. That is, the system uses more and more different analytical sections (in information systems analytics are defined by a set of reference books).

Naturally, this leads to the fact that managers want to receive reporting in all analytical sections that interest them. This means that the reports need to be made to “breathe” somehow. In other words, we can say that in this case we are talking about the fact that the meaning of the same report should provide information in different analytical aspects. Therefore, static reports no longer suit many modern managers. They need the dynamics that a multidimensional CUBE can provide.

Thus, OLAP technology has already become a mandatory element in modern and future information systems. Therefore, when choosing a software product, you need to pay attention to whether it uses OLAP technology.

Moreover, you need to be able to distinguish real CUBES from imitation ones. One such simulation is pivot tables in MS Excel. Yes, this tool looks like a CUBE, but in fact it is not one, since these are static, not dynamic tables. In addition, they have a much worse implementation of the ability to build reports using elements from hierarchical directories.

To confirm the relevance of using CUBE when constructing management reporting, we can give a simple example with a sales budget. In the example under consideration, the following analytical sections are relevant for the company: products, branches and sales channels. If these three analytics are important for the company, then the sales budget (or report) can be displayed in several versions.

It should be noted that if you create budget lines based on three analytical sections (as in the example under consideration), this allows you to create quite complex budget models and create detailed reports using CUBE.

For example, a sales budget can be compiled using only one analytics (directory). An example of a sales budget built on the basis of one analytics "Products" is presented at Figure 1.

Rice. 1. An example of a sales budget built on the basis of one analytics “Products” in OLAP-CUBE

The same sales budget can be compiled using two analytics (directories). An example of a sales budget built on the basis of two analytics “Products” and “Branches” is presented at Figure 2.

Rice. 2. An example of a sales budget built on the basis of two analytics “Products” and “Branches” in the OLAP-CUBE of the INTEGRAL software package

.

If there is a need to build more detailed reports, then the same sales budget can be compiled using three analytics (directories). An example of a sales budget built on the basis of three analytics “Products”, “Branches” and “Sales Channels” is presented at Figure 3.

Rice. 3. An example of a sales budget built on the basis of three analytics “Products”, “Branches” and “Sales Channels” in the OLAP-CUBE of the INTEGRAL software package

It should be recalled that the CUBE used to generate reports allows you to display data in different sequences. On Figure 3 The sales budget is first “expanded” by product, then by branch, and then by sales channel.

The same data can be presented in a different sequence. On Figure 4 the same sales budget is “expanded” first by product, then by sales channel, and then by branch.

Rice. 4. An example of a sales budget built on the basis of three analytics “Products”, “Distribution Channels” and “Branches” in the OLAP-CUBE of the INTEGRAL software package

On Figure 5 the same sales budget is “unfolded” first by branches, then by products, and then by sales channels.

Rice. 5. An example of a sales budget built on the basis of three analytics “Branches”, “Products” and “Sales Channels” in the OLAP-CUBE software package “INTEGRAL”

Actually that's not all possible options withdrawal of the sales budget.

In addition, you need to pay attention to the fact that the CUBE allows you to work with hierarchical structure reference books. In the examples presented, the hierarchical directories are “Products” and “Distribution Channels”.

From the user's point of view, in this example he receives several management reports (see. Rice. 1-5), and from the point of view of settings in software product- this is one report. Simply using the CUBE you can view it in several ways.

Naturally, in practice, a very large number of options for outputting various management reports is possible if their articles are based on one or more analysts. And the set of analytics itself depends on the users’ needs for detail. True, we should not forget that, on the one hand, the larger the analyst, the more detailed reports can be built. But, on the other hand, this means that the financial budgeting model will be more complex. In any case, if there is a KUB, the company will have the opportunity to view the necessary reporting in various versions, in accordance with the analytical sections of interest.

It is necessary to mention several more features of the OLAP-CUBE.

In a multidimensional hierarchical OLAP-CUBE there are several dimensions: row type, date, rows, directory 1, directory 2 and directory 3 (see. Rice. 6). Naturally, the report displays as many buttons with directories as there are in the budget line containing the maximum number of directories. If there is not a single reference book in any budget line, then the report will not have a single button with reference books.

Initially, the OLAP-CUBE is built along all dimensions. By default, when the report is initially built, the dimensions are located in exactly the areas shown in Figure 6. That is, a dimension such as “Date” is located in the area of ​​vertical dimensions (dimensions in the column area), dimensions “Rows”, “Directory 1”, “Directory 2” and “Directory 3” - in the area of ​​horizontal dimensions (dimensions in the area rows), and the “Row Type” dimension is in the area of ​​“unexpanded” dimensions (dimensions in the page area). If a dimension is in the last area, then the data in the report will not "expand" on that dimension.

Each of these dimensions can be placed in any of the three areas. Once measurements are transferred, the report is instantly rebuilt to match the new measurement configuration. For example, you can swap the date and lines with reference books. Or you can move one of the reference books to the vertical measurement area (see. Rice. 7). In other words, you can “twist” the report in the OLAP-CUBE and select the report output option that is most convenient for the user.

Rice. 7. An example of rebuilding a report after changing the measurement configuration of the INTEGRAL software package

The measurement configuration can be changed either in the main CUBE form or in the change map editor (see. Rice. 8). In this editor, you can also drag and drop measurements from one area to another with the mouse. In addition, you can swap measurements in one area.

In addition, in the same form you can configure some measurement parameters. For each dimension, you can customize the location of totals, the sorting order of elements, and the names of elements (see. Rice. 8). You can also specify which element name to display in the report: abbreviated (Name) or full (FullName).

Rice. 8. Measurement map editor of the INTEGRAL software package

You can edit measurement parameters directly in each of them (see. Rice. 9). To do this, click on the icon located on the button next to the measurement name.

Rice. 9. Example of editing directory 1 Products and services in

Using this editor, you can select the elements that you want to show in the report. By default, all elements are displayed in the report, but if necessary, some elements or folders can be omitted. For example, if you need to display only one product group in the report, then you need to uncheck all the others in the measurement editor. After that, the report will contain only one product group (see. Rice. 10).

You can also sort elements in this editor. In addition, elements can be rearranged different ways. After such a regrouping, the report is instantly rebuilt.

Rice. 10. Example of output in a report of only one product group (folder) in the INTEGRAL software package

In the dimension editor, you can quickly create your own groups, drag and drop elements from directories there, etc. By default, only the Other group is automatically created, but other groups can be created. Thus, using the dimension editor, you can configure which elements of the reference books and in what order should be displayed in the report.

It should be noted that all such rearrangements are not recorded. That is, after closing the report or after its recalculation, all directories will be displayed in the report in accordance with the configured methodology.

In fact, all such changes could have been made initially when setting up the lines.

For example, using restrictions you can also specify which elements or groups of directories should be displayed in the report and which should not.

Note: the topic of this article is discussed in more detail at workshops "Budget management of an enterprise" And "Organization and automation of management accounting" conducted by the author of this article, Alexander Karpov.

If the user almost regularly needs to display only certain elements or directory folders in the report, then it is better to make such settings in advance when creating report lines. If various combinations of directory elements in reports are important to the user, then there is no need to set any restrictions when setting up the methodology. All such restrictions can be quickly configured using the measurement editor.

04/07/2011 Derek Comingore

If you've worked in any technology-related field, you've probably heard the term "cube"; however, most ordinary database administrators and developers did not work with these objects. Cubes provide a powerful data architecture for quickly aggregating multidimensional information. If your organization needs to analyze large volumes of data, then ideal solution it will be a cube

What is a cube?

Relational databases were designed to handle thousands of concurrent transactions while maintaining performance and data integrity. By design, relational databases are not efficient at aggregating and searching large volumes of data. To aggregate and return large volumes of data, a relational database must receive a set-based query, the information for which will be collected and aggregated on the fly. Such relational queries are very expensive because they rely on multiple joins and aggregate functions; Aggregate relational queries are especially ineffective when working with large amounts of data.

Cubes are multidimensional entities designed to address this deficiency in relational databases. By using a cube, you can provide users with a data structure that provides fast response to queries with large aggregation volumes. Cubes perform this “aggregation magic” by first aggregating data (dimensions) across multiple dimensions. Pre-aggregation of the cube is usually carried out during processing. When you process a cube, you produce precomputed data aggregations that are stored in binary form on disk.

The cube is the central data structure in operating system SQL Server Analytical Services (SSAS) OLAP data analysis. Cubes are typically built from an underlying relational database called a dimensional model, but are separate technical entities. Logically, a cube is a data warehouse that is made up of dimensions (dimensions) and measurements (measures). Dimensions contain descriptive features and hierarchies, while dimensions are the facts that you describe in dimensions. Dimensions are grouped into logical combinations called dimension groups. You link dimensions to measurement groups based on a characteristic - the degree of detail.

IN file system a cube is implemented as a sequence of linked binary files. The binary architecture of the cube facilitates the rapid retrieval of large volumes of multidimensional data.

I mentioned that cubes are built from an underlying relational database called a dimensional model. The dimension model contains relational tables (fact and dimension) that connect it to the cube entities. Fact tables contain dimensions such as the quantity of a product sold. Dimension tables store descriptive attributes such as product names, dates, and employee names. Typically, fact tables and dimension tables are related through primary foreign key constraints, with the foreign keys located in the fact table (this relational relationship relates to the cube granularity attribute discussed above). When dimension tables are linked directly to a fact table, a star schema is formed. When dimension tables are not directly linked to a fact table, the result is a snowflake schema.

Please note that dimensional models are classified according to application. A data mart is a dimensional model that is designed for a single business process, such as sales or inventory management. A data warehouse is a dimensional model designed to capture component business processes so that it facilitates cross-business process analytics.

Software requirements

Now that you have a basic understanding of what cubes are and why they're important, I'll turn on the gears and take you on a step-by-step tour of building your first cube using SSAS. There are some basic components software, which you will need, so before you start building your first cube, make sure your system meets the requirements.

My example Internet Sales cube will be built from the AdventureWorksDW 2005 test database. I will build the test cube from a subset of the tables found in the test database that will be useful for analyzing Internet sales data. Figure 1 shows the basic layout of the database tables. Since I'm using version 2005, you can follow my instructions using either SQL Server 2005 or SQL Server 2008.

Figure 1. Subset of the Adventure Works Internet Sales data mart

The Adventure WorksDW 2005 training database can be found on the CodePlex website: msftdbprodsamples.codeplex.com. Find the link “SQL Server 2005 product sample databases are still available” (http://codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004). The training database is contained in the file AdventureWorksBI.msi (http://msftdbprodsamples.codeplex.com/releases/view/4004#DownloadId=11755).

As mentioned, you must have access to an instance of SQL Server 2008 or 2005, including SSAS and Business Intelligence Development Studio (BIDS) components. I'll be using SQL Server 2008, so you may see some subtle differences if you're using SQL Server 2005.

Creating an SSAS Project

The first thing you should do is create an SSAS project using BIDS. Find BIDS in the Start menu and then in the Microsoft SQL Server 2008/2005 menu, sub-item SQL Server Business Intelligence Development Studio. Clicking this button will launch BIDS with the default splash screen. Create new project SSAS by selecting File, New, Project. You'll see the New Project dialog box, which Figure 1 shows. Select the Analysis Services Project folder and set the project description to SQLMAG_MyFirstCube. Click OK.

Once the project is created, right-click on it in Solution Explorer and select context menu Properties item. Now select the Deployment section on the left side of the SQLMAG_MyFirstCube: Property Pages dialog box and review the Target Server and Database settings settings, as Figure 2 shows. If you're working in a distributed SQL Server environment, you'll need to qualify the Target Server property with the name of the server. to which you are going to deploy. Click OK when you are happy with the deployment settings for this SSAS project.

Defining the data source

The first object you need to create is the data source. A data source object provides the schema and data used to build the objects associated with and at the base of the cube. To create a data source object in BIDS, use the Source Wizard Data Source Wizard.

Start the Data Source Wizard by right-clicking on the Data Source folder in the Solution Explorer panel and selecting New Data Source. You will find that creating SSAS objects in BIDS has a developmental nature. First, the wizard walks you through the process of creating an object and General settings. And then you open the resulting SSAS object in the designer and customize it in detail if necessary. Once you get past the prompt screen, define a new data connection by clicking the New button. Select and create a new connection based on Native OLEDB\SQL Server Native Client 10 pointing to the one you want SQL server Server that owns the desired database instance. You can use either Windows or SQL Server authentication, depending on your SQL Server environment settings. Click the Test Connection button to ensure that you have correctly identified the database connection, and then click OK.

Next comes Impersonation Information, which, like data association, depends on how the SQL Server environment is structured. Privilege borrowing is the security context that SSAS relies on when processing its objects. If you're managing your deployment on a primary, single server (or laptop), as I assume most readers are, you can simply select the Use the service account option. Click Next to complete the Data Source Wizard and set AWDW2005 as the Data Source Name. It's very convenient that you can use this method for testing purposes, but in a real production environment it's not the most best practice- use a service account. It is better to specify domain Accounts to borrow SSAS connection rights to the data source.

Data Source View

For the data source you have defined, the next step in the SSAS cube building process is to create a Data Source View (DSV). DSV provides the ability to separate the schema that your cube expects from that of the underlying database. As a result, DSV can be used to extend the underlying relational schema when building a cube. Some of the key features of DSV for extending data source schemas include named queries, logical relationships between tables, and named calculated columns.

Let's go ahead and right-click on the DSV folder and select New Data Source View to launch the Create New DSV View wizard. In the dialog box, at the Select a Data Source step, select a relational database connection and click Next. Select the FactInternetSales, DimProduct, DimTime, DimCustomer tables and click the single right arrow button to move these tables to the Included column. Finally, click Next and complete the wizard by accepting the default name and clicking Finish.

At this point, you should have a DSV view located under the Data Source Views folder in Solution Explorer. Double click on the new DSV to launch the DSV designer. You should see all four tables for a given DSV, as shown in Figure 2.

Creating Database Dimensions

As I explained above, dimensions provide descriptive features of dimensions and hierarchies that are used to enable aggregation above the level of detail. It is important to understand the difference between a database dimension and a cube dimension: the dimensions from the database provide the underlying dimension objects for the several dimensions of the cube that will be used to build the cube.

Database and cube dimensions provide an elegant solution to a concept known as "role dimensions." Role-based dimensions are used when you need to use a single dimension in a cube multiple times. Date is a perfect example in this cube instance: you will construct a single date dimension and reference it once for each date for which you want to analyze online sales. The calendar date will be the first dimension you create. Right-click the Dimensions folder in Solution Explorer and select New Dimension to launch the Dimension Wizard. Select Use an existing table and click Next in the Select Creation Method step. At the Specify Source Information step, specify the DimTime table in the Main table drop-down list and click Next. Now, at the Select Dimension Attributes step, you need to select the attributes of the time dimension. Select each attribute, as Figure 3 shows.

Click Next. At the final step, enter Dim Date in the Name field and click Finish to complete the Dimension Wizard. You should now see the new Dim Date dimension located under the Dimensions folder in Solution Explorer.

Then use the Dimension Wizard to create product and customer dimensions. Follow the same steps to create the base dimension as before. When working with the Dimension Wizard, make sure that you select all potential attributes in the Select Dimension Attributes step. The default values ​​for the other settings are fine for a test cube instance.

Creating an Internet Sales Cube

Now that you have prepared the database dimensions, you can begin building the cube. In Solution Explorer, right-click the Cubes folder and select New Cube to launch the Cube Wizard. In the Select Creation Method window, select the Use existing tables option. Select the FactInternetSales table for Measure Group in the Select Measure Group Tables step. Uncheck the boxes next to the Promotion Key, Currency Key, Sales Territory Key, and Revision Number dimensions in the Select Measures step and click Next.

On the Select Existing Dimensions screen, ensure that all existing database dimensions are selected to be used as cube dimensions. Because I would like to keep this cube as simple as possible, deselect the FactInternetSales dimension in the Select New Dimensions step. By leaving the FactInternetSales dimension selected, you would create what is called a fact dimension or degenerate dimension. Fact dimensions are dimensions that were created using a basic fact table as opposed to a traditional dimension table.

Click Next to go to the Completing the Wizard step and enter “My First Cube” in the Cube Name field. Click the Finish button to complete the Create Cube Wizard process.

Expanding and Processing a Cube

Now you're ready to deploy and process the first cube. Right-click the new cube icon in Solution Explorer and select Process. You will see a message box stating that the content appears to be out of date. Click Yes to deploy the new cube to the target SSAS server. When you deploy a cube you send XML file for Analisis (XMLA) to the target SSAS server, which creates a cube on the server itself. As mentioned, processing a cube populates its binaries on disk with data from the main source, as well as additional metadata you've added (cube dimensions, dimensions, and settings).

Once the deployment process is complete, a new Process Cube dialog box appears. Click the Run button to begin processing the cube, which opens with the Process Progress window. When processing is complete, click Close (twice to close both dialog boxes) to complete the cube deployment and processing processes.

You have now built, deployed and processed your first cube. You can view this new cube by right-clicking on it in the Solution Explorer window and selecting Browse. Drag dimensions to the center of the pivot table and dimension attributes onto rows and columns to explore your new cube. Notice how quickly the cube processes various aggregation queries. Now you can appreciate the unlimited power and therefore business value, cube OLAP.

Derek Comingore ( [email protected]) is a senior architect at B.I. Voyage, which has Microsoft Partner status in the field of business analytics. Has the SQL Server MVP title and several Microsoft certifications

A stand-alone cube file (.cub) stores data in a form in an online analytical processing (OLAP) cube. This data may represent part of an OLAP database from an OLAP server, or it may have been created independently of any OLAP database. To continue working with PivotTable and PivotChart reports when the server is unavailable or when offline, use an offline cube file.

Learn more about offline cubes

When you work with a PivotTable or PivotChart report that is based on a data source from an OLAP server, use the Offline Cube Wizard to copy the source data to a separate offline cube file on your computer. To create these offline files, you must have an OLAP data provider that supports these capabilities, such as MSOLAP from Microsoft SQL Server Analysis Services, installed on your computer.

Note: Creating and using offline cube files from Microsoft SQL Server Analysis Services, subject to terms and licensing Microsoft installations SQL Server. Review the appropriate licensing information for your version of SQL Server.

Using the Offline Cube Wizard

To create an offline cube file, use the Offline Cube Wizard to select a subset of data in the OLAP database, and then save that set. The report does not have to include all the fields included in the file, and you can choose from any of its dimensions and data fields available in the OLAP database. To minimize file size, you can include only the data that you want to be able to display in the report. You can skip all dimensions and, for most types of dimensions, also skip lower-level details and features top level, which do not need to be displayed. For an offline file, all elements that can be included in the property fields that are available in the database for those elements are also saved.

Taking data offline and then bringing data back online

To do this, you must first create a PivotTable report or PivotChart report that is based on the server database, and then create a standalone cube file from the report. Subsequently, when working with a report, you can switch between the server database and the offline file at any time (for example, when working on a laptop at home or on the road and then reconnecting the computer to the network).

The following describes the basic steps for taking data offline and bringing it back online.

Note:

Click the PivotTable report. If this is a PivotChart report, select the associated PivotTable report.

On the "tab" Analysis" in Group calculations click the button OLAP service and press the button Offline OLAP.

Select an item OLAP with connectivity, and then click the button OK.

If prompted to find a data source, click Find source and find the OLAP server on the network.

Click the PivotTable report that is based on the offline cube file.

In Excel 2016: On the " tab data" in Group requests and connections Update all and press the button Update.

In Excel 2013: On the " tab data" in Group connections click the arrow next to the button Update all and press the button Update.

On the "tab" Analysis" in Group calculations click the button OLAP service and press the button Offline OLAP.

Click the button Offline OLAP mode, and then - .

Note: Stop in the dialog box.

Warning:

Creating an offline cube file from an OLAP server database

Note: If the OLAP database is large and the cube file is needed to provide access to a large subset of data, a lot of free space on disk, and saving the file may take a long time. To improve performance, it is recommended that you create stand-alone cube files using an MDX script.

Problem: My computer does not have enough disk space when saving a cube.

OLAP databases are designed to manage large amounts of detailed data, so a database hosted on a server can take up significantly more space than is available on your local hard drive. If you select a large amount of data for an offline data cube, you may not have enough free disk space. The following approach will help reduce the size of the offline cube file.

Free up disk space or select a different disk Before saving the cube file, remove it from disk. unnecessary files or save the file to a network drive.

Including less data in an offline cube file Consider how you can minimize the amount of data included in the file so that the file contains all the data needed for a PivotTable report or PivotChart. Try the steps below.

Connecting an offline cube file to an OLAP server database

Updating and re-creating an offline cube file

Updating an offline cube file that is created from the latest data obtained from a server cube or from a new offline cube file can take a significant amount of time and require a large amount of temporary disk space. Run this process when you don't need immediate access to other files, after making sure you have enough space on your hard drive.

Problem: New data does not appear in the report when refreshed.

Checking the availability of the source database The offline cube file may be unable to connect to the source server database to obtain new data. Make sure that the original database on the server that is the data source for the cube has not been renamed or moved to another location. Make sure the server is accessible and can be connected to.

Checking for new data Check with your database administrator to see if the data that should be included in the report has been updated.

Checking the immutability of the database organization If OLAP cube server has been changed, accessing the changed data may require reorganizing the report, creating an offline cube file, or running the Create OLAP Cube Wizard. To learn about database changes, contact your database administrator.

Including other data in the offline cube file

Saving a modified offline cube file can be time consuming and requires working in Microsoft Excel is not possible while saving the file. Run this process when you don't need immediate access to other files, after making sure you have enough space on your hard drive.

Verify that there is a network connection and that the source OLAP server database from which the offline cube file obtained data is accessible.

Click a PivotTable report created from a stand-alone cube file, or an associated PivotTable report for a PivotChart report.

On the tab Options in Group Service click the button OLAP service and press the button Offline OLAP mode.

Click the button Offline OLAP mode, and then - Edit Offline Data File.

Follow the Offline Cube Wizard to select other data to include in this file. In the last step, specify the name and path to the file to change.

Note: To cancel saving the file, click the button Stop in the dialog box Creating a cube file - progress.

Deleting an offline cube file

Warning: If you delete an offline cube file for a report, you can no longer use that report offline and you can no longer create an offline cube file for that report.

Close any workbooks that contain reports that use the offline cube file, or ensure that all such reports are deleted.

IN Microsoft Windows Locate and delete the offline cube file (CUB file).

additional information

You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the website

In a standard pivot table, the source data is stored on your local hard drive. This way, you can always manage and reorganize them, even without access to the network. But this in no way applies to OLAP pivot tables. In OLAP pivot tables, the cache is never stored on the local hard drive. Therefore, immediately after disconnecting from the local network, your pivot table will no longer work. You will not be able to move a single field in it.

If you still need to analyze OLAP data after going offline, create an offline data cube. An offline data cube is a separate file that is a pivot table cache and stores OLAP data that is viewed after disconnecting from the local network. OLAP data copied into a pivot table can be printed; this is described in detail on the website http://everest.ua.

To create a standalone data cube, first create an OLAP pivot table. Place the cursor within the pivot table and click on the OLAP Tools button on the Tools contextual tab, which is part of the PivotTable Tools contextual tab group. Select the Offline OLAP command (Fig. 9.8).

The Offline OLAP Data Cube Settings dialog box appears on the screen. Click on the Create Offline Data File button. You have launched the Create Data Cube File Wizard. Click the Next button to continue the procedure.

First you need to specify the dimensions and levels that will be included in the data cube. In the dialog box, you must select the data that will be imported from the OLAP database. The idea is to specify only those dimensions that will be needed after the computer is disconnected from the local network. The more dimensions you specify, the larger the autonomous data cube will be.

Click the Next button to move to the next wizard dialog box. This gives you the ability to specify members or data elements that will not be included in the cube. In particular, you won't need the Internet Sales-Extended Amount measure, so its checkbox will be cleared in the list. A cleared check box indicates that the specified item will not be imported and take up unnecessary space on your local hard drive.

In the last step, specify the location and name of the data cube. In our case, the cube file will be named MyOfflineCube.cub and will be located in the Work folder.

Data cube files have the extension .cub

After some time, Excel will save the offline data cube in the specified folder. To test it, double click on the file, which will automatically generate a working Excel workbooks, which contains a pivot table associated with the selected data cube. Once created, you can distribute the offline data cube to all interested users who are working in offline LAN mode.

Once connected to your local network, you can open the offline data cube file and update it and the corresponding data table. The main principle states that the offline data cube is used only to work when the local network is disconnected, but it is required to be updated after the connection is restored. Attempting to update an offline data cube after a connection failure will result in a failure.