Practical work on OLAP technology in Excel. OLAP is easy! Some information about OLAP cubes and POWER Pivot data models

1 task (general) (2 points).

Using the spreadsheet of data processing using statistical functions.
1. Data information about class students (10 people), including estimates within one month in mathematics. Calculate the number of five, fours, twists and triples, find the average score of each student and the middle score of the entire group. Create a diagram illustrating the percentage rating in the group.

2.1 Task (2 points).

Four friends travel on three types of transport: train, plane and steamer. Nikolai looked 150 km on the steamer, drove 140 km by train and flew 1100 km by plane. Vasily sailed on a ship 200 km, drove 220 km by train and flew by plane 1160 km. Anatoly flew by the plane of 1200 km, drove by train 110 km and flew 125 km on a steamer. Maria drove by train 130 km, flew by the plane of 1,500 km and flew 160 km on the steamer.
Build on the basis of the above data spreadsheet.

Add a column to the table, in which the total number of kilometers will be displayed, which traveled each of the guys.

Calculate the total number of kilometers that the guys drove the train flew on the aircraft and flew over the steamer (on each form of transport separately).

Calculate the total number of kilometers of all friends.

Determine the maximum and minimum number of kilometers traveled by friends for all types of transport.

Determine the average number of kilometers in all types of transport.

2.2 Task (2 points).

Create a table "Lake Europe" using the following data on the area (sq. Km. Km) and the greatest depth (M): Ladoga 17 700 and 225; Onega 9510 and 110; Caspian Sea 371 000 and 995; Venern 5550 and 100; Moon with Pskov 3560 and 14; Balaton 591 and 11; Geneva 581 and 310; Wet 1900 and 119; Bodenskoye 538 and 252; Melogen 1140 and 64. Determine the largest and most small lake area, the deepest and smallest lake.

2.3 Task (2 points).

Create a table "River of Europe" using the following length data (km) and pool area (thousand square meters. Km): Volga 3688 and 1350; Danube 2850 and 817; Rhine 1330 and 224; Elba 1150 and 148; Vistula 1090 and 198; Loire 1020 and 120; Ural 2530 and 220; Don 1870 and 422; Hay 780 and 79; Thames 340 and 15. Determine the longest and shortest river, calculate the total area of \u200b\u200briver basins, the average length of the European part of Russia.

3 task (2 points).

The bank accounts for the timeliness of payments of loans issued to several organizations. The amount of the loan and the amount already paid by the Organization are known. Fine sanctions are installed for debtors: if the company paid a loan by more than 70 percent, then the fine will be 10 percent of the debt amount, otherwise the penalty will be 15 percent. Consider a fine for each organization, a medium fine, the total amount of money that the bank is going to receive additionally. Determine the average penalty of budgetary organizations.

Find the material for any lesson,

Analytical analytical processing (OLAP) is a technology that is used to streamline large business databases and business intelligence support. Base data OLAP. They are divided into one or more cubes, and each of them is organized by the Cuba administrator so that it corresponds to the method of obtaining and analyzing data to facilitate the creation and use of reports summary Table and reports of the summary chart that you need.

In this article

What is a business analyst?

The business analyst often wants to get a big picture of the business to view broader trends based on aggregated data, as well as view trends with a breakdown by any number of variables. Business Analytics is the process of extracting data from the OLAP database and analyzing this data for information that can be used to take informed business solutions and performing actions. For example, using OLAP and business analysts you can answer the following questions about business data.

How is the total sales amount of all products in 2007 compared with sales from 2006?

How does this compare with the date and time at a favorable period over the past five years?

How much money customers spent 35 in last year And how did this behavior changed over time?

How many products were sold in two specific countries / regions this month, in contrast to the same month of last year?

For each group of customer ages, what is the separation of profitability (as the percentage of margin and the result) by categories of goods?

Search for the best and lower vendors, distributors, suppliers, customers, partners and customers.

What is analytical processing on the Internet (OLAP)?

OLAP database (Online Analytical Processing) simplify business analytics requests. OLAP is a database technology optimized for queries and reports, and not for transaction processing. The data source for OLAP is operational transaction processing databases (OLTP), which are usually stored in data warehouses. OLAP data is extracted from these historical data and are combined into structures that make a complex analysis. OLAP data is also ordered hierarchically and stored in cubes, and not in tables. This is a complex technology that uses multidimensional structures to ensure rapid access to data for analysis. In this organization, the summary of the summary table or report of the summary diagram can be easily displayed high-level summary data, such as sales results across the country or region, as well as display information about sites where sales are especially high or weak.

OLAP databases are designed to speed up data loading. Since the OLAP server is not Microsoft Office. Excel calculates generalized values, when creating or changing a report, you must send smaller data to Excel. This approach allows you to work with a large amount of source data than if the data has been organized in the traditional database, where excel retrieves all individual records and calculates generalized values.

OLAP databases contain two basic data types: measures that are numeric data, quantities and mean values \u200b\u200bthat are used to take informed business solutions, and measurements that are categories used to streamline these measures. OLAP databases help streamline data using a plurality of detail levels using the same categories that you know to analyze data.

In the following sections, each component is described in detail below.

Cubic Data structure that combines measures to levels and hierarchies of each measurement you want to analyze. Cubes combine several measurements, such as time, geography and product lines, with final data, such as sales and stocks. Cubes are not "Cuba" in a strict mathematical sense, as they do not necessarily have the same sides. Nevertheless, they are a metaphor of the APT for a complex concept.

Measurements A set of values \u200b\u200bin a cube based on a column in a cube factory table and which is usually a numerical value. Measures are central values \u200b\u200bin Cuba, which are pre-processed, processed and analyzed. The most common examples are sales, income, income and costs.

Member An element in the hierarchy representing one or more data entries. The element can be both unique and unborn. For example, 2007 and 2008 represent unique elements at the level of the year of measurement of time, while January represents undelated elements at the level of the month, since there is more than one in the measurement of time, since it contains data for more than one year.

Calculated item Measurement element whose value is calculated during execution using an expression. The values \u200b\u200bof the calculated elements can be derived from the values \u200b\u200bof other members. For example, the calculated "Profit" element can be determined by subtracting the value of the element, as well as the costs of the value of the element, sale.

measure A set of one or more ordered hierarchies of cube levels that the user understands and uses as a basis for data analysis. For example, the geographical measurement may include levels for the country / region, state / district and city. In addition, time measurement may include a hierarchy with the level of "year", "Quarter", "Month" and "Day". In the summary table or report of the consolidated chart, each hierarchy becomes a set of fields that can be deployed and turning to show lower or more high levels.

Hierarchy The logical structure of the tree that streams the measurement elements, so that each element has one parent element and zero or more child elements. The child element is a member of the earlier group in the hierarchy directly related to the current member. For example, in the time hierarchy containing the levels of the quarter, month and day, January is a subsidiary of Qtr1. The parent element is a member of the lower level in the hierarchy directly related to the current member. Parental value is usually consolidating the values \u200b\u200bof all child elements. For example, in a time hierarchy containing the levels of "Quarter", "Month" and "Day", QTR1 is the parent for January.

Level In hierarchy, data can be organized into lower and higher levels of detail, such as years, quarters, months and day levels in the time hierarchy.

OLAP functions in Excel

Getting OLAP data You can connect to OLAP data sources in the same way as other external data sources. You can work with databases created using Microsoft Services SQL Server OLAP version 7.0, services Microsoft SQL. Server Analysis Services version 2000 and Microsoft SQL Server Analysis Services version 2005, Microsoft Olap server products. Excel can also work with third-party OLAP products that are compatible with Ole-DB for OLAP.

OLAP data can be displayed only as a summary of the summary table or report of the summary diagram or in the sheet function, converted from the consolidated table report, but not in the form of an external data range. You can save Consolidated OLAP tables and summary charts in report templates, as well as create Office data connection files (ODC) to connect to OLAP databases for OLAP requests. When opening an ODC file in Excel, an empty report of the consolidated table is displayed, which is ready for accommodation.

Creating Cube Files for Autonomous Use You can create an autonomous cube file (. CUB) with a subset of data from the OLAP server database. Offline Cube Files are used to work with OLAP data if you are not connected to the network. With the help of a cube you can work with a large amount of data in the report of the pivot table or the report of the consolidated chart than otherwise, and accelerate the data obtaining. You can create cube files only if you are using an OLAP provider, such as Microsoft SQL Analysis Services version 2005, which supports this feature.

Server Actions The server action is an optional function that the OLAP Cuba administrator can define on a server that uses the cube element or moderate as a parameter in the query to get information in Cuba, or to start another application, for example a browser. Excel supports URLs, reports, row sets, detailing and deployment of detailed server actions, but does not support your own own operator and data set.

KPE The key performance indicator is a special calculated measure defined on the server that allows you to track the "key performance indicators", including the state (the current value corresponds to a specific number). and the trend (values \u200b\u200bover time). When they are displayed, the server can send the corresponding icons similar to the new Excel icon so that they are built above or below the status levels (for example, for the stop icon), as well as promotion up or down (for example, direction arrow icon).

Formatting on the server Cube administrators can create measures and calculated items using color formatting, formatting font and conditional formatting rules that can be assigned as a corporate standard business rule. For example, the server format for income can be a numeric currency format, a green cell color, if the value is greater than or equal to 30,000, and red, if less than 30,000, and the font style is bold, if less than 30,000, and with a positive value - normal. More or equal to 30,000. Additional information can be found.

Office interface language Cuba administrator can define transfers for data and errors on the server for users who need to view a consolidated table information in another language. This feature is defined as a connection property with a file, and the regional parameters of the computer and the country must comply with the interface language.

Software components required to access OLAP data sources

OLAP supplier To configure OLAP data sources for Excel, one of the above OLAP providers is required.

Supplier of Microsoft OLAP. Excel includes a data source driver and client software To access the databases created using Microsoft SQL Server Olap Server OLAP version 7.0, Microsoft SQL Server Olap version 2000 (8.0) and Microsoft SQL Server Analysis Services version 2005 (9.0).

Third-party suppliers OLAP For other OLAP products, you need to install additional drivers and client software. To use excel features To work with OLAP data, a third-party product must comply with the OLE-DB standard for OLAP and be compatible with Microsoft Office. For information about the installation and use of third-party OLAP provider, refer to the OLAP system administrator or supplier.

Server databases and cube files Excel OLAP client software supports connections with two types of OLAP databases. If the database on the OLAP server is available on the network, you can receive source data from it directly from it. If you have a standalone cube file that contains OLAP data or file Definition of cubes, you can connect to this file and get the source data from it.

Data sources The data source provides access to all data in the OLAP database or an autonomous cube file. By creating an OLAP data source, you can base reports on it and return OLAP data to Excel in the form of a summary table or a summary report report, as well as a sheet function that has been converted from a consolidated table report.

Microsoft Query. With Query you can get data from external base Data, such as Microsoft SQL or Microsoft Access. To obtain data from the OLAP summary table associated with the Cuba file, use the query is not required. Additional information .

Differences in OLAP and non-OLAP source data functions

If you are working with reports of consolidated tables and summary diagrams from OLAP source data and other types of source data, you will see some of the features.

Extraction of data The OLAP server returns new data to Excel each time the report layout is changed. When using other types of external data sources, you request all source data at once or you can specify the parameters for a request only when different elements of the report filter fields are displayed. In addition, you have some more options for updating the report.

In reports based on OLAP source data, the report filter field parameters are not available, the background query is unavailable, and the memory optimization parameter is not available.

Note: The memory optimization parameter is also unavailable for OLEDB data sources and summary tableware reports based on the range of cells.

Types of fields OLAP source data. Fields Measurement can only be used as rows (rows), columns (category) or page field. Mural fields can only be used as fields of values. For other types of source data, all fields can be used in any part of the report.

Access to detailed data For source data OLAP server determines available levels Details and calculates summary values, so records with detailed information that make up the total values \u200b\u200bmay not be available. However, the server can provide field properties that you can display. Other types of source data do not have properties fields, but you can display basic information for data fields and items, and display items without data.

The OLAP report filter fields may not be everything Elements, and the team Show report filter pages Unavailable.

Initial order sorting For OLAP source data, items are first displayed in the order in which they are returned by the OLAP server. You can sort or manually change the order of the items. For other types of source data, the elements of the new report are first sorted by the name of the element in the increasing order.

Nimi. OLAP servers provide generalized values \u200b\u200bdirectly for the report, so it is impossible to change the final functions for the fields of the values. For other types of source data, you can change the statistical function for the value field and use multiple final functions for the same value field. You can not create calculated fields and calculated items in reports with OLAP source data.

Intermediate results In reports with source data, the OLAP cannot be changed a final function for intermediate results. With other types of source data, you can change the final functions for intermediate outcomes and display or hide the intermediate results for all fields of rows and columns.

For OLAP source data, you can enable or exclude hidden items when calculating intermediate and general results. For other types of source data, you can include the hidden elements of the report filter fields to the intermediate results, but the hidden items in other fields will be excluded by default.

In the standard summary table, the source data is stored on a local hard disk. Thus, you can always manage them and reorganize them, not even having access to the network. But this does not in any way concern the OLAP summary tables. In the consolidated OLAP tables, the cache is never stored on a local hard disk. Therefore, immediately after disconnecting from the local network, your consolidated table will lose performance. You will not be able to move in it not a single field.

If you still need to analyze OLAP data after disconnecting from the network, create an autonomous data cube. The autonomous data cube is a separate file that is a pivot table cache and stores OLAP data viewed after disconnecting from the local network. OLAP data copied to the consolidated table can be printed on the site http://everest.ua described in detail about it.

To create an autonomous data cube, first create a consolidated OLAP table. Place the cursor within the consolidated table and click on the OLAP Tools button (OLAP TOOLS) of the Context tab Settings (Tools), which is included in the context tab of the Context tab, work with consolidated tables (Pivottable Tools). Select the Offline OLAP mode (Offline OLAP) (Fig. 9.8).

Fig. 9.8. Creating an autonomous data cube

The OLAP Autonomous Cube Settings dialog box appears on the screen. Click it on the Create Offline Data File button. You launched a data cube file creation wizard. Click the Next button to continue the procedure.

It is necessary to specify the dimension and levels that will be included in the cube data. In the dialog box, select the data that will be imported from the OLAP database. The idea is to specify only those dimensions that will be needed after disconnecting the computer from the local network. The more dimensions indicate, the longer the size will have an autonomous cube of data.

Click the Next button to go to the next wizard dialog box. In it, you get the ability to specify members or data elements that will not be included in the cube. In particular, you will not need the Internet Sales-Extended Amount measure, so the checkbox for it will be discharged in the list. The freeze check box indicates that the specified item will not be imported and take an excess place on the local hard disk.

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

Data cubic files have an extension .cub

After some time, Excel will save the autonomous cube of the data in the specified folder. To test it, double-click on the file, which will lead to automatic generation of the Excel working book, which contains a consolidated table associated with the selected data cube. After creating, you can extend the autonomous data cube among all interested users who work in the disabled local network mode.

After connecting to the local network, you can open a standalone file of the data cube and update it, as well as the corresponding data table. The main principle states that the autonomous data cube applies only to work when the local network is disabled, but it is mandatory updated after the connection is restored. Attempting to update the autonomous cube after breaking the connection will cause a failure.

Working with autonomous cubes

a standalone Cuba file (. CUB) stores data in the form of the OLAP Cuba (Online Analytical Processing). These data can represent part of the OLAP database on the OLAP server or may be created regardless of the OLAP database. Use the standalone cube file to continue working with the pivot table and the summary chart reports if the server is not available or when you are disabled from the network.

Security Note: Be careful when using or distributing an autonomous cube file containing confidential or personal data. Instead of the Cuba file, it is recommended to save the data in the book so that you can manage access to data using the rights management function. For more information, you can find in the Office Data Rights section.

When working with a report of a pivot table or a summary diagram based on the OLAP server source data, you can copy the source data to a separate autonomous cube file using the Autonomous Cube Wizard on your computer. To create these offline files, it is necessary that the OLAP data provider supports such an opportunity, such as MSOLAP from Microsoft SQL Server Analysis Services installed on the computer.

Note: Creating and using autonomous cubic files from Microsoft SQL Server Analysis Services is regulated by the term and licensing. microsoft installations SQL Server. Check out the appropriate licensing information version of SQL Server.

Working with an autonomous cube master

To create an autonomous cube file, you can select a subset of data in the OLAP database using the autonomous cube wizard, and then save it a subset. The report does not need to include all fields included in the file, as well as choose from them any of them and the data fields available in the OLAP database. To save the file at least, you can enable only those data that should be displayed in the report. You can omit all measurements and for most measurement types, you can also eliminate the lower level information and top-level elements that do not need to display. For all the items that you turn on, properties fields available in the database for these elements are also stored in the offline file.

Translation of data in the offline mode and their reverse connection

To do this, first need to create a summary table or a summary diagram based on the server database, and then create an autonomous cube file from the report. After that, you can switch the report between the server database and the standalone file at any time. For example, if you use a laptop computer to work in domestic and video shops, and then connect the computer back to the network.

The following are the main steps to be executed for offline work with data, and then transfer data to the Internet again.

Create or open a summary table or a summary report based on OLAP data to which you want to access offline.

Creating a standalone cube file on a computer. In chapter Creating a standalone cube from the OLAP server database (Below in this article).

Disconnection from the network and working with the file of the autonomous cube.

Connect to the network and re-connect the Cuba file autonomously. Check out the section Re-connection of the autonomous cube file to the OLAP server database (Below, in this article).

Updating an autonomous cube file with new data and re-creating a standalone cube file. Check out the section update and re-create an autonomous cube file (Below in this article).

Blog

Only high-quality posts

What is Summer Tables Excel and Olap Cuba

Watch the video to the article:

OLAP. - This is the English. Online Analytical Processing, analytical data processing technology in real time. Simple language - Storage with multidimensional data (cube), even easier - just a database from which you can get data in Excel and analyze using the Excel tool - consolidated tables.

Consolidated tables - This is a user interface to display multidimensional data. In other words, a special type of tables with which almost any report can be made.

To be clear, let's compare the "ordinary table" with the "Consolidated Table"

Normal Table:

Summary Table:

The main difference Summary tables - This is the presence of the window " List of hollow-table fields"From which you can choose the right fields and get any table automatically!

How to use

Open excel filewhich is connected to OLAP Cuba, for example "BiWeb":

Now, what does this mean and how to use it?

Drag the right fields to get, for example, such a table:

« Pluses»Allow the report to detail. In this example, "brand" is detailed to "abbreviated names", and the "quarter" to the "month", i.e. So:

Analytical functions in Excel (cube functions)

Microsoft constantly adds new features to Excel in terms of analyzing and visualizing data. Working with information in Excel can be represented as relatively independent three layers:

• "Right" organized source data
• mathematics (logic) data processing
• data presentation

Fig. 1. Data analysis in Excel: a) source data, b) measure in Power Pivot, c) dashboard; To enlarge image Click on it right-click Mouse and select Open picture in a new tab

Download a note in Word or PDF format, examples in Excel format

Cube functions and summary tables

The most simple and at the same time a very powerful means of presenting data are summary tables. They can be built based on the data contained: a) on the Excel sheet, b) Cuba OLAP or C) POWER PIVOT data model. In the last two cases, in addition to a summary table, you can use analytical functions (cube functions) to generate a report on the Excel sheet. Summary tables are easier. Functions of cubes are more complicated, but provide more flexibility, especially in reporting, so they are widely used in dashboards.

Further statement relates to cubic formulas and consolidated tables based on the POWER PIVOT model and in several cases based on OLAP cubes.

Easy way to get functions of cubes

When (if), you started learning the VBA code, they learned that the easiest way to get the code using the macro record. Next, the code can be edited, add cycles, checks, etc. It is similar to the easiest way to get a set of cubes functions by converting a consolidated table (Fig. 2). Stand on any cell of the pivot table, go to the tab AnalysisClick on the button Funds OLAP., and press Convert to formula.

Fig. 2. Converting a consolidated table in a set of cube functions

Numbers will be saved, and this will not be values, but formulas that extract data from the POWER PIVOT data model (Fig. 3). The resulting table you can format. Including, you can delete and insert strings and columns inside the table. The cut remained, and it affects the data in the table. When updating the source data, the number in the table will also be updated.

Fig. 3. Table based on formulas cubes

Cube function ()

This is perhaps the main function of the cubes. It is equivalent to the region Values Summary table. Cube removes data from cube or POWER PIVOT model, and reflects them outside the pivot table. This means that you are not limited to the limits of the pivot table and you can create reports with countless possibilities.

Writing the formula "from scratch"

You do not need to convert a ready-made consolidated table. You can write any formula of the cube "from scratch". For example, the following formula was introduced into the C10 cell (Fig. 4):

Fig. 4. Function cubant () in the C10 cell Returns bicycle sales for all the years, as in the pivot table

Little trick. For more convenient, it is desirable to read the cube formulas, it is desirable that only one argument can be placed in each row. You can reduce the Excel window. To do this, click on the icon Close into the windowlocated in the upper right corner of the screen. And then adjust the window size horizontally. Alternative option - to force the text of the formula to forcibly new string. To do this, in the formula row, put the cursor in the place where you want to make the transfer and press Alt + Enter.

Fig. 5. Collapse the window

Syntax function cubation ()

Excel Help is absolutely accurate and absolutely useless for beginners:

Cubes (connection; [Expression_Element1]; [Expression_Element2]; ...)

Connection - mandatory argument; Text string representing the name of the connection to the cube.

Expression_Element - optional argument; A text string representing a multidimensional expression that returns an item or a motorcade in Cuba. In addition, the "Expression_Element" may be a set defined using the CZD function. Use "Expression_Element" as a cut to determine the part of the cube for which you want to return the aggregated value. If the "Expression_Element" argument does not specify a measure, a measure specified by default for this cube will be used.

Before switching to the explanation of the syntax function cubation, a few words about cubes, data models, and mysterious corgeta.

Some information about OLAP cubes and POWER Pivot data models

Cuba data OLAP ( O.n. l.ine A.nalytical P.rOCESSING - Operational data analysis) were developed specifically for analytical processing and rapidly extracting data. Imagine a three-dimensional space where the periods, cities and goods are postponed along the axes (Fig. 5a). In the nodes of such a coordinate grid are the values \u200b\u200bof various measures: sales, profits, costs, the number of units sold, etc. Now imagine that measurements of dozens, or even hundreds ... and a lot of measures are also very much. This will be a multi-member OLAP cube. Creating, setting up and maintaining in the current state of OLAP cubes is the work of IT professionals.

Fig. 5a. Three-dimensional cube OLAP.

Excel's analytical formulas (cube formulas) extract the names of the axes (for example, Time), the names of the elements on these axes (August, September), the values \u200b\u200bof the coordinate crossing. This is the structure and allows consolidated tables based on cubes and cubes formulas to be so flexible, and to adapt to the needs of users. Excel's consolidated tables are not used, so they are not so flexible to analyze data.

Power Pivot is a relatively new Microsoft chip. It is embedded in Excel and partly an independent environment with a familiar interface. Power Pivot significantly surpasses standard consolidated tables. At the same time, the development of cubes in Power Pivot is relatively simple, and most importantly - does not require the participation of an IT specialist. Microsoft sells its slogan: "Business analytics - in mass!". Although the POWER PIVOT models are not 100% cubes, you can also talk about them as cubes (for details, see the introductory course Mark Moore. Power Pivot and more voluminous edition Rob Collie. DAX formulas for Power Pivot).

The main components of the cube are measurements, hierarchies, levels, elements (or members; in English memters) and measures (Measures). Measurement - The main characteristic of the analyzed data. For example, the category of goods, time period, geography of sales. Measurement is what we can put on one of the axes of the consolidated table. Each measurement in addition to unique values \u200b\u200bincludes an element that performs the aggregation of all elements of this measurement.

Measurements are built on the basis of hierarchy. For example, the category of goods may be divided into subcategories, then on the model, and finally - on the names of goods (Fig. 5b) hierarchy allows you to create summary data and analyze them at various levels of the structure. In our example hierarchy Category Includes 4. Level.

Elements (Separate terms) are present at all levels. For example, at Category level there are four items: accessories, bikes, clothing, components. Other levels have their own elements.

Measures - These are calculated values, for example, sales. Measures in cubes are stored in its own dimension, called (see Fig. 9 below). Measures do not have hierarchies. Each measure calculates and stores the value for all measurements and all items, and is cut depending on which elements of measurements we place on the axis. They also say what to set the coordinates, or what to set the filter context. For example, in Fig. 5a in each small cube is calculated by the same measure - profits. And the returned measure value depends on the coordinates. On the right in Figure 5a, it was shown that profits (in three coordinates) in Moscow in October on apples \u003d 63 000 r. The measure can be interpreted, and as one of the measurements. For example, in Fig. 5a instead of axis Products, place an axis Measures with elements Volume of sales, Profit, Sold units. Then each cell and will be some kind of value, for example, Moscow, September, sales.

Court - Several elements of different measurements that specify the coordinates on the axes of the cube in which we are counting on the measure. For example, in Fig. 5A. Court Moscow, October, apples. Also permissible motorcade - Perm, apples. Another one is apples, August. Not included in the measurement tuction are implicitly present in it, and are represented by a member of the default. Thus, the cell of the multidimensional space is always determined by the total set of coordinates, even if some of them are omitted in the cortex. It is impossible to include two elements of one measurement in the tuple, will not allow the syntax. For example, an invalid motorcade Moscow and Perm, apples. To implement such a multidimensional expression, a set of two tuples will be required: Moscow and apples + Perm and apples.

Set of elements - Multiple elements of one measurement. For example, apples and pears. Set of tuples - Several tuples, each of which consists of identical measurements in the same sequence. For example, a set of two tuples: Moscow, apples and Perm, bananas.

Automotive to help

Let's return to the syntax of the Cube function. We use the autocarter. Start entering the formula in the cell:

Excel will offer all available in Excel connections:

Fig. 6. Connecting to the POWER PIVOT data model is always called thisworkbookDataModel

Fig. 7. Connections to cubes

Continue input formula (in our case for data model):

Autodulture will offer all available tables and data model measures:

Fig. 8. Available elements of the first level - table names and set of measures (highlighted)

Select the icon Measures.. Put the point:

Cubes ("ThisworkBookDatamodel"; ".

Automotive will offer all available measures:

Fig. 9. Available second elements in the set of measures

Select a measure. Add quotes closing brackets, press ENTER.

Cubes ("ThisworkBookDataModel"; ".")

Fig. 10. Cube Formula in Excel Cell

Similarly, you can add a third argument in the formula:

VBA in Excel Excel.Pivottable object and work with consolidated tables and OLAP cubes in Excel

10.8 Working with consolidated tables (PivotTable object)

Excel.Pivottable object, software work with consolidated tables and OLAP cubes in Excel VBA tools, PivotCache object, creating a pivot table layout

During the work of most enterprises, the so-called raw data (RAW DATA) are accumulated about activities. For example, data on sales of goods can be accumulated for the trading enterprise - for each purchase separately, for enterprises cellular communication - Load Statistics on Base Stations, etc. Very often, the management of the enterprise requires analytical information, which is generated on the basis of untreated - for example, to calculate the contribution of each type of product in the enterprise income or the quality of service in the zone of this station. From the raw information, such information is very hard: you need to perform very complex SQL queries, which are performed for a long time and often interfere with the current work. Therefore, more and more often, the raw data is at first in the archive data storage - Data Warehouse, and then in OLAP Cuba, which are very convenient for interactive analysis. The easiest way to imagine OLAP cubes as multidimensional tables, in which instead of the standard two measurements (columns and lines, as in conventional tables), measurements can be a lot. Usually, the term "in section" is used to describe measurements in Cuba. For example, the marketing department may need information in a temporary section, in a regional section, in the context of the types of product, in the context of sales channels, etc. With the help of cubes (in contrast to standard SQL queries), it is very easy to receive answers to questions like "how many of the goods such a type have been sold in the fourth quarter of last year in the North-West region through regional distributors.

Of course, in ordinary databases, such cubes do not create. For working with OLAP cubes, specialized software products. With SQL Server, an OLAP database from Microsoft is supplied, which is called Analysis Services. There are OLAP solutions from Oracle, IBM, Sybase, etc.

Special client has built into Excel to work with such cubes. In Russian he is called Summary Table (on the graphic screen it is available through the menu Data -> Summary Table), and in English - Pivot Table. Accordingly, the object that represents this client is called PivotTable. It should be noted that he knows how to work not only with OLAP cubes, but also with ordinary data in excel tables or databases, but many possibilities are lost.

The pivottable summary table and object is Panorama Software software products that were purchased by Microsoft and are integrated into Excel. Therefore, work with the PivotTable object is somewhat different from working with other Excel objects. Guess what to do is often not easy. Therefore, it is recommended to promptly use the macrorecoreder to receive prompts. At the same time, when working with consolidated tables, users often have to perform the same repeating operations, so automation in many situations is necessary.

What does software work with a consolidated table look like?

The first thing we need to do is to create a PivotCache object that will represent a set of records obtained from the OLAP source. Very conditionally, this PivotCache object can be compared with QueryTable. For each PIVOTTABLE object, you can only use one PivotCache object. The creation of the PivotCache object is made using the add () method of the PivotCaches collection:

DIM PC1 AS PIVOTCACHE

Set PC1 \u003d ActiveWorkBook.PivotCaches.add (XLEXTERNAL)

Pivotcaches - standard Collection, and from methods that deserve a detailed consideration, it can only be called the add () method. This method takes two parameters:

• SourceType. - Mandatory, defines the type of data source for the consolidated table. You can specify the creation of a PivotTable based on the range in Excel, data from the database, in an external data source, another pivottable, etc. In practice, OLAP usually makes sense to use only when the data is much - you need a specialized external storage (for example, Microsoft Analysis Services). In this situation, the value of Xlexternal is selected.
• Sourcedata. - Mandatory in all cases, except for those when the value of the first parameter is Xlexternal. Actually, it determines the data range based on which PivotTable will be created. Usually receives an Range object.

The following task is to configure the parameters of the PivotCache object. As already mentioned, this object is very similar to QueryTable, and the set of properties and methods is very similar. Some of the most important properties and methods:

• Adoconnection. - Ability to return the ADO Connection object, which is automatically created to connect to an external data source. Used to further configure the connection properties.
• Connection. - It works in the same way as the same name of the object QueryTable object. Can take a connection string, a ready-made Recordset object, a text file, a Web request. Microsoft Query file. Most often, when working with OLAP, the connection string is prescribed directly (since to receive the Recordset object, for example, no sense to change the data, there are plenty of OLAP data sources are almost always available for reading). For example, configuring this property to connect to the Foodmart database (Analysis Services database) on the London server may look like this:

PC1.Connection \u003d "OLEDB; provider \u003d MSOLAP.2; Data Source \u003d London1; initial catalog \u003d Foodmart 2000"

• properties CommandType. and CommandText Similarly, the type of command is described, which is transmitted to the database server, and the text of the team itself. For example, to refer to the SALES cube and get it entirely in the cache on the client, you can use the code of the form

• property LocalConnection. Allows you to connect to the local cube (* .cub file) created by Excel. Of course, such files for working with "production" data volumes are not recommended to use - only for the purpose of creating layouts, etc.
• property Memoryused. Returns the number of RAM used by PivotCache. If PivotTable based on this Pivotcache has not yet been created and not open, returns 0. You can use to check if your application will work on weak customers.
• property OLAP. Returns True if PivotCache is connected to the OLAP server.
• Optimizecache. - Ability to optimize the cache structure. The initial data load will be produced longer, but then the speed of work may increase. For OLE DB sources does not work.

The remaining properties of the PivotCache object coincide with the similar properties of the QueryTable object, and therefore will not be considered here.

The main method of the PivotCache object is the CreatePivottable () method. With this method, the following step is performed - creating a pivot table (PivotTable object). This method takes four parameters:

• TableDestination - the only mandatory parameter. Accepts the Range object to the upper left corner of which the summary table will be placed.
• Tablename. - Name of the consolidated table. Unless otherwise indicated, the name of the type "welded" is automatically generated.
• READDATA. - If you install in True, then all the contents of the cube will be automatically placed in the cache. With this parameter you need to be very careful, since its improper use can dramatically increase the load on the client.
• DefaultVersion. - This property is usually not specified. Allows you to define a version of the consolidated table being created. The default is the most recent version.

Creating a pivot table in the first cell of the first sheet of a book may look like this:

PC1.CREATEPIVOTTABLE RANGE ("A1")

We have a summary table, but immediately after creation it is empty. It provides four areas in which fields can be placed from the source (on the graphic screen, all this can be configured either using the window List of hollow-table fields - It opens automatically, or using the button Layout On the last screen of the collateral creation wizard):

• column area - those dimensions ("cut" are placed in it, in which data will be analyzed), members of which are less;
• row area - those dimensions whose members are more;
• page area - those dimensions for which you only need to filter (for example, show data only by such a region or only for such a year);
• data area - In fact, the central part of the table. Those numeric data (for example, sales amount) that we analyze.

Relying on the user in the fact that it will correctly post elements in all four areas, difficult. In addition, it may take a certain time. Therefore, it is often necessary to position the data in the consolidated table program. This operation is performed using the Cubefield object. The main property of this object is orientation, it determines where this or that field will be. For example, we place the measurement of Customers to the column area:

Pt1.cubefields ("") .orientation \u003d XLCOLUMNFIELD

Then - Measurement Time in the string area:

PT1.CUBEFIELDS ("") .orientation \u003d XLROWFIELD

Then - measurement Product to the page area:

PT1.CUBEFIELDS ("") .orientation \u003d XLPageField

Finally, the indicator (numeric analysis data) Unit Sales:

PT1.CUBEFIELDS ("."). Orientation \u003d XLDATAFIELD

Client OLAPs are applications that calculate aggregate data (sums, average values, maximum or minimum values) and their display, while the aggregate data itself is contained in the cache within the address space of such an OLAP.

If the initial data is contained in the desktop DBMS, the calculation of aggregate data is performed by the OLAP itself. If the source data source is a server DBMS, many of the client OLAP funds are sent to the SQL request server containing the GROUP BY statement, and as a result, aggregate data calculated on the server is obtained.

As a rule, OLAP functionality is implemented in the means of statistical data processing (products of Statsoft and SPSS companies in the Russian market are widespread in the Russian market) and in some spreadsheets. In particular, Microsoft Excel has a multidimensional analysis tools. With this product, you can create and save as a small local multidimensional OLAP cube file and display it two- or three-dimensional sections.

The add-in to the Microsoft Office application package for extracting and processing data is a number of functions that provide access to the possibilities for extracting and processing data from Microsoft Office applications, and thus allowing prognostic analysis on a local computer. Due to the fact that the Microsoft SQL Server platform services and data processing algorithms are available from Microsoft Office applications, business users can easily extract valuable information from complex data sets with just a few mouse clicks. Office application package for extracting and data processing gives end users with the ability to perform directly in Microsoft Excel and Microsoft Visio applications.

Microsoft Office 2007 includes three separate OLAP components:

1. excel extraction and processing client allows you to create projects for extracting and processing data based on SSAS services and manage them from Excel 2007;
2. tool analysis tools for the Excel application allow you to use the functions and processing information for analyzing data stored in Excel tables.
3. data Extraction and Processing Templates for the Visio application allow you to visualize solutions trees, regression trees, cluster charts and dependency networks on Visio diagrams.

Amazing - next ...

In the course of the work, I often needed to make complex reports, I always tried to find something in common with them to make them easier and universally, I even wrote and published the article "Tree Osipova" on this issue. However, my article criticized and said that all the problems I raised were already solved in MOLAP.RU V.2.4 (www.molap.rgtu.ru) and recommended to see the summary tables in Excel.
It turned out to be so simple that putting his brilliant handms to this, I got a very simple scheme for unloading data from 1C7 or any other database (later under 1C meant any database) and analysis in OLAP.
I think many unloading schemes in OLAP are too complicated, I choose simplicity.

Characteristics :

1. Only Excel 2000 is required for work.
2. The user itself can construct reports without programming.
3. Unloading from 1C7 in a simple text file format.
4. For accounting, there is already universal processing for unloading operating in any configuration. To unload other data there are sample processing.
5. You can construct reports in advance, and then apply them to different data without re-designing them.
6. Pray good performance. At the first long-lasting stage, the data is first imported into Excel from the text file and the OLAP cube is built, and then any report can be built quite quickly based on this cube. For example, data on sales of goods for 3 months with an assortment of 6000 products, loaded into Excel for 8 minutes on Cel600-128m, rating for goods and groups (OLAP report) is recalculated in 1 minute.
7. Data is discharged from 1C7 completely for the specified period (all movements, by all warehouses, firms, accounts). When importing to Excel, it is possible to use filters downloading for analysis only the desired data (for example, from all movements, only sales).
8. Currently, methods for analyzing movements or residues, but not movements and residues together, although it is in principle possible.

What is OLAP. : (www.molap.rgtu.ru)

Suppose you have a trading network. Let the trading data are unloaded into a text file or a table of the form:

Date - Date of operation
Month - month of operation
Week - week operation
View - purchase, sale, refund, write-off
Counterparty - an external organization participating in the operation
The author is a person who discharge the invoice

In 1C, for example, one line of this table will correspond to one line of the invoice, some fields (counterparty, date) are taken from the batch cap.

Data for analysis is usually unloaded into the OLAP system for a certain period of time, from which, in principle, you can select another period by using boot filters.

This table is source for OLAP analysis.

The user itself is determined which of the table fields will be measurements, which data and which filters are applied. The system itself builds a report in a visual tabular form. Measurements can be placed in the headings of the rows or columns of the report table.
As can be seen, from one simple table you can get a variety of data in the form of various reports.

How to use :

Data from the distribution to unpack in the C: \\ Fixin directory (for the trading system is possible in C: \\ Reports). Read README.TXT and perform all instructions in it.

First you must write processing that unloads data from 1C to the text file (table). You need to determine the composition of the fields that will be unloaded.
For example, ready-made universal processing that works in any configuration and unloads for OLAP analysis of wiring for the period, unloads the following fields for analysis:

Date | Dangened | week | year | quarter | month | document | Firm | Debit | DTNUNOMCOLATURE
| DTGRUPNENKOLATURE | DRAZEMENNECTURE | Credit | Amount | Valsum | Quantity
| Currency | DCTRENTS | DTGRUPPCTRENTERS | CONTRENTS | CTGRUPTRADRENTS |
Crazen objects

Where under the prefixes of DT (CT) are subconto debit (credit), the group is a group of this subconto (if any), section - group group, class - section group.

For the trading field, there may be such:

Direction | Vision | Dalled | Product | Quantity | Price | Amount | Date | Firm
| Warehouse | Currency | Document | Doubleded | week | year | quarter | month | Author
| CategoryCertovar | Categoryment | Category Protection | Group
| Valsumma | Cost | Counterparty

To analyze data, Tables "Analysis of Movements.xls" ("Analysis of Accounting.xls") are used. Opening them, do not disconnect macros, otherwise you will not be able to update reports (they are launched by macros in VBA). The source data These files take from files C: \\ Fixin \\ Motions.txt (C: \\ Fixin \\ buh.txt), otherwise they are the same. Therefore, you may have to copy your data to one of these files.
To use your data in Excel, select or write your filter and click the "Shape" button on the "Conditions" sheet.
Report sheets begin with the "Message" prefix. Scroll to the report list, click "Update" and report data will be changed in accordance with the latest downloaded data.
If you are not satisfied with the standard reports, there is a sheet of the focus. Copy it into a new sheet and configure the report view, working with a consolidated table on this sheet (about working with consolidated tables - in any book on Excel 2000). I recommend configuring reports on a small set of data, and then run them on a large array, because There is no way to disable the redraws of the tables each time the report layout change.

Technical comments :

When you upload data from 1C, the user selects the folder where to unload the file. I did it because it is quite likely to in the near future several files will be unloaded (residues and movements). Then by clicking in the Explorer, the "Send" button "-\u003e" on OLAP analysis in Excel 2000 "data is copied from the selected folder to the C: \\ Fixin folder. (So \u200b\u200bthat this command appears in the "Send" command list and you need to copy the file "to the OLAP analysis in Excel 2000.bat" in the C: \\ Windows \\ Sendto directory) so unload the data immediately giving names of the Motions.txt or buh.txt files.

Text file format:
The first line of the text file - the columns columns are "|", the remaining strings contain the values \u200b\u200bof these speakers, separated "|".

To import text files in Excel, Microsoft Query is used to work it. It is necessary to have the SHEMA.INI file in the import directory (C: \\ Fixin) containing the following information in the Import directory containing the following information:

COLNAMEHEADER \u003d TRUE.
Format \u003d Delimited (|)
MaxScanRews \u003d 3.
Characterset \u003d Ansi.
COLNAMEHEADER \u003d TRUE.
Format \u003d Delimited (|)
MaxScanRews \u003d 3.
Characterset \u003d Ansi.

Explanation: Motions.txt and Buh.txt is the name of the section, corresponds to the name of the imported file, describes how to import a text file into Excel. The remaining parameters mean that the first line contains the column names, the speaker separator is "|", the set of characters is Windows ANSI (for DOS - OEM).
The field type is determined automatically based on the data column (date, number, string).
The list of fields do not need to be described anywhere - Excel and OLAP will determine which fields are contained in the header file in the first row.

Attention, check your regional control panel -\u003e "Regional Settings". In my processings, the numbers are unloaded with a comma separator, and dates in the format "DD.MM.YYYY".

Data When you press the "Separate" button, you are loaded into the consolidated table on the "Base" sheet, and from this pivot table and take data all reports on the list sheets.

I understand that MS SQL Server lovers and powerful databases will begin to grumble that I have too much simplified that my processing will be broken on a one-year sample, but first of all I want to give the benefits of OLAP analysis for medium organizations. I would position this product as a tool for one-year analysis for wholesale companies, quarterly analysis for retail and operational analysis for any organization.

I had to tinker with VBA so that the data was taken from the file with any list of fields and it was possible to prepare reports in advance.

Description of work in Excel (for users):

Instructions for using reports:
1. Send a unloaded data on the analysis (check with the administrator). To do this, right-click on the folder in which you have discharged from 1C and select the "Submit" command, then "on the OLAP analysis in Excel 2000".
2. Open the "Movement Analysis" file .xls
3. Select the filter value you need the filters you can add on the "Values" tab.
4. Click the "Shape" button, while the unloaded data will be downloaded to Excel.
5. After downloading data to Excel, you can watch various reports. To do this, just click the "Update" button in the selected report. Sheets with reports begin on the inc.
Attention! After you change the filter value, you need to click on the "Shape" button again so that the data in Excel reboot from the unloading file to match the filters.

Processing from demo-example:

Processing MotionsBUH2011.ERT - the latest version of postings from the accounting 7.7 for analysis in Excel. It has a check mark "Attach to a file", which allows you to unload data from the periods by period, connecting them into the same file, and without unloading to the same file again:

Processing MotionSWork.ert unloads sales data for analysis in Excel.

Examples of reports:

Chess on wiring:

Uploading operators by type of overhead:

P.S. :

It is clear that by a similar scheme, you can organize the uploading of data from 1C8.
In 2011, a user who had to finalize this processing in 1C7 was addressed to me that it would unload large amounts of data, I found an outsourcer and fulfilled this work. So the development is quite relevant.

MotionSbuH2011.ert processing is improved to cope with unloading a large amount of data.