Practical work on olap technology in excel. Olap cubes in excel. Writing a formula from scratch

1 task (general) (2 points).

Using a spreadsheet, process data using statistical functions.
1. Information is given about the students of the class (10 people), including grades for one month in mathematics. Count the number of fives, fours, twos and threes, find the average score of each student and the average score of the entire group. Create a chart illustrating the percentage of grades in a group.

2.1 task (2 points).

Four friends travel by three modes of transport: train, plane and ship. Nikolai sailed 150 km by boat, traveled 140 km by train and flew 1100 km by plane. Vasily sailed 200 km by boat, traveled 220 km by train and flew 1160 km by plane. Anatoly flew 1200 km by plane, traveled 110 km by train and sailed 125 km by boat. Maria traveled 130 km by train, flew 1500 km by plane and sailed 160 km by boat.
Build a spreadsheet based on the above data.

Add a column to the table that will display the total number of kilometers that each of the guys traveled.

Calculate the total number of kilometers that the children traveled by train, flew by plane and sailed by boat (on each type of transport separately).

Calculate the total number of kilometers of all friends.

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

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

2.2 task (2 points).

Create a table “Lakes of Europe” using the following data on area (sq. km) and greatest depth (m): Ladoga 17,700 and 225; Onega 9510 and 110; Caspian Sea 371,000 and 995; Wenern 5550 and 100; Chudskoye with Pskovsky 3560 and 14; Balaton 591 and 11; Geneva 581 and 310; Wettern 1900 and 119; Constance 538 and 252; Mälaren 1140 and 64. Determine the largest and smallest lake in area, the deepest and shallowest lake.

2.3 task (2 points).

Create a table “Rivers of Europe” using the following data for length (km) and basin area (thousand sq. km): Volga 3688 and 1350; Danube 2850 and 817; Rhine 1330 and 224; Elbe 1150 and 148; Vistula 1090 and 198; Loire 1020 and 120; Ural 2530 and 220; Don 1870 and 422; Sena 780 and 79; Thames 340 and 15. Determine the longest and shortest river, calculate the total area of ​​river basins, the average length of rivers in the European part of Russia.

Task 3 (2 points).

The bank records the timeliness of payments of loans issued to several organizations. The loan amount and the amount already paid by the organization are known. Penalties are established for debtors: if the company has repaid the loan by more than 70 percent, the fine will be 10 percent of the debt amount, otherwise the fine will be 15 percent. Calculate the fine for each organization, the average fine, the total amount of money that the bank is going to receive additionally. Determine the average fine of budgetary organizations.

Find material for any lesson,

OLAP client tools are applications that calculate aggregate data (sums, averages, maximum or minimum values) and display them, while the aggregate data itself is contained in a cache within the address space of such an OLAP tool.

If the source data is contained in a desktop DBMS, the calculation of aggregate data is performed by the OLAP tool itself. If the source of the initial data is a server DBMS, many of the client OLAP tools send SQL queries containing the GROUP BY statement to the server, and as a result receive aggregate data calculated on the server.

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

Application package add-ons Microsoft Office data mining are a set of functions that provide access to data mining and processing capabilities from Microsoft Office applications, thereby enabling predictive analysis on local computer. Thanks to the fact that the services are built into Microsoft platforms SQL Server With data mining and processing algorithms available from within the Microsoft Office application environment, business users can easily extract valuable information from complex data sets with just a few clicks. Office data extraction and manipulation add-ins enable end users to perform analysis directly in Microsoft Excel and Microsoft Visio.

IN Microsoft composition Office 2007 includes three separate OLAP components:

1. Data Mining Client for Excel lets you create and manage SSAS-based data mining projects from within Excel 2007;
2. table analysis tools for Excel applications Allows you to use SSAS's built-in information extraction and processing capabilities to analyze data stored in Excel spreadsheets.
3. Visio Data Mining Templates let you visualize decision trees, regression trees, cluster diagrams, and dependency networks in Visio diagrams.

Product The first interface for pivot tables, also called pivot reports, was included in Excel back in 1993 (Excel version 5.0). Despite the many useful functionality , it is practically not used in work by most Excel users. Even experienced users often mean by the term “summary report” something built using complex formulas. Let's try to popularize the use of pivot tables in the daily work of economists. The article discusses theoretical basis

create summary reports, provide practical recommendations for their use, and also provide an example of accessing data based on multiple tables.

Multivariate data analysis terms Most economists have heard the terms “multidimensional data”, “virtual cube”, “OLAP technologies”, etc. But a detailed conversation usually turns out that almost everyone has no idea what they are talking about. we're talking about

. That is, people mean something complex and usually not related to their daily activities. Actually this is not true.

Multidimensional data, measurements It is safe to say that economists almost constantly encounter multidimensional data, but they try to present it in a predefined form using spreadsheets. Multidimensionality here means the ability to enter, view or analyze the same information with changes appearance

• , using various groupings and sorting of data. For example, a sales plan can be analyzed using the following criteria:
• types or groups of goods;
• brands or product categories;
• periods (month, quarter, year);
• buyers or groups of buyers;
• sales regions

Each of the above criteria is called a “dimension” in terms of multidimensional data analysis. We can say that a measurement characterizes information according to a specific set of values. A special type of measurement of multidimensional information is “data”. In our example, the sales plan data could be:

• volume of sales;
• Selling price;
• individual discount
• sales regions

In theory, data can also be a standard dimension of multidimensional information (for example, you could group data by sales price), but data is usually still a special type of value.

Thus, it can be said that in practical work economists use two types of information: multidimensional data ( actual and planned numbers that have many characteristics) and reference books (characteristics or data measurements).

OLAP

The abbreviation OLAP (online analytical processing) literally means “real-time analytical processing”. The definition is not very specific; almost any report of any software product can be subsumed under it. In its meaning, OLAP implies a technology for working with special reports, including software, for obtaining and analyzing multidimensional structured data. One of the popular software products that implement OLAP technologies is SQL Server Analysis Server. Some even mistakenly consider him the only representative of the software implementation of this concept.

Virtual data cube

"Virtual Cube" ( multidimensional cube, OLAP cube) is a special term proposed by some specialized software providers. OLAP systems typically prepare and store data in their own structures, and special analysis interfaces (such as Excel summary reports) access the data in these virtual cubes. Moreover, the use of such a dedicated storage is not at all necessary for processing multidimensional information. In general, virtual cube– this is an array of specially optimized multidimensional data that is used to create summary reports. It can be obtained either through specialized software or through simple access to database tables or any other source, such as an Excel spreadsheet.

Pivot table

Pivot Table is a user interface for displaying multidimensional data. Using this interface, you can group, sort, filter, and rearrange data to obtain different analytical samples. Updating the report is done using simple means user interface, data is automatically aggregated according to specified rules, without requiring additional or repeated entry of any information. Excel's pivot table interface is perhaps the most popular software product for working with multidimensional data. It supports both external data sources (OLAP cubes and relational databases) and internal spreadsheet ranges as a data source. Starting with version 2000 (9.0), Excel also supports a graphical form of displaying multidimensional data - a Pivot Chart.

Excel's PivotTable interface allows you to arrange dimensions of multidimensional data in a worksheet area. For simplicity, you can think of a pivot table as a report lying on top of a range of cells (in fact, there is a certain binding of cell formats to the fields of the pivot table). An Excel PivotTable has four areas to display information: filter, columns, rows, and data. Data dimensions are called Pivot table fields. These fields have their own properties and display format.

Once again, I would like to point out that the Excel pivot table is intended solely for data analysis without the ability to edit the information. A closer meaning would be the widespread use of the term “pivot report”, and this is exactly what this interface was called until 2000. But for some reason, in subsequent versions the developers abandoned it.

Editing PivotTables

By its definition, OLAP technology, in principle, does not imply the ability to change source data when working with reports. However, a whole class has formed on the market software systems, implementing the capabilities of both analysis and direct editing of data in multidimensional tables. Basically, such systems are focused on solving budgeting problems.

Using Excel's built-in automation tools, you can solve many non-standard problems. An example of editing implementation for Excel pivot tables based on worksheet data can be found on our website.

Preparing multidimensional data

Let's come to practical application Pivot tables. Let's try to analyze sales data in various directions. File pivottableexample.xls consists of several sheets. Sheet Example contains basic information about sales for a certain period. To simplify the example, we will analyze a single numerical indicator – sales volume in kg. The key data dimensions are: product, buyer and carrier (shipping company). In addition, there are several additional dimensions data that are characteristics of the product: type, brand, category, supplier, as well as the buyer: type. This data is collected on the Directories sheet. In practice, there can be much more such measurements.

Sheet Example contains standard remedy data analysis – autofilter. Looking at the example of filling out the table, it is obvious that sales data by date (they are arranged in columns) lends itself to normal analysis. In addition, using an autofilter, you can try to summarize data based on combinations of one or more key criteria. There is absolutely no information about brands, categories and types. There is no way to group data with automatic summation by a specific key (for example, by customers). In addition, the set of dates is fixed, and it will not be possible to view summary information for a certain period, for example, 3 days, using automatic means.

In general, the presence of a predefined date location in this example is the main drawback of the table. By arranging dates in columns, we predetermined the dimension of this table, thus depriving ourselves of the opportunity to use analysis using pivot tables.

Firstly, we need to get rid of this shortcoming - i.e. remove the predefined location of one of the source data dimensions. Example of a correct table - sheet Sales.

The table has the form of a log of information entry. Here, date is an equal dimension of data. It should also be noted that for subsequent analysis in pivot tables, the relative position of the rows relative to each other (in other words, sorting) is completely indifferent. Records in relational databases have these properties. The interface of pivot tables is primarily aimed at analyzing large volumes of databases. Therefore, you must adhere to these rules when working with a data source in the form of cell ranges. At the same time, no one prohibits the use of Excel interface tools in their work - pivot tables analyze only data, and formatting, filters, groupings and sorting of source cells can be arbitrary.

From autofilter to summary report

Theoretically, it is already possible to carry out analysis in three dimensions using the data from the Sales sheet: goods, customers and carriers. There is no data on the properties of products and customers on this sheet, which, accordingly, will not allow them to be shown in the summary table. In the normal mode of creating a pivot table for the source Excel data does not allow you to link data from multiple tables using certain fields. You can get around this limitation software– see the example supplement to this article on our website. In order not to resort to software methods for processing information (especially since they are not universal), you should add additional characteristics directly to the journal entry form - see the SalesAnalysis sheet.

The use of VLOOKUP functions makes it easy to supplement the original data with missing characteristics. Now, using AutoFilter, you can analyze data in different dimensions. But the problem of groupings remains unresolved. For example, tracking the amount only by brand for certain dates is quite problematic. If you limit yourself Excel formulas, then you need to build additional samples using the SUMIF function.

Now let's see what capabilities the pivot table interface provides. On a sheet SummaryAnalysis built several reports based on a range of cells with sheet data SalesAnalysis.

The first analysis table was built through the Excel 2007 interface Ribbon\Insert\PivotTable(in Excel 2000-2003 menu Data\PivotTable).

The second and third tables were created through copying and subsequent configuration. The data source for all tables is the same. You can check this by changing the source data, then you need to update the summary report data.

From our point of view, the advantages in the visibility of information are obvious. You can swap filters, columns and rows and hide certain groups of values ​​of any dimensions, use manual drag and drop and automatic sorting.

Properties and Formatting

In addition to directly displaying data, there is a wide range of options for displaying the appearance of pivot tables. You can hide unnecessary data using filters. For a single element or field it is easier to use the context menu item Delete(in version 2000-2003 Hide).

It is also advisable to set the display of other elements of the pivot table not through cell formatting, but through setting a field or element of the pivot table. To do this, you need to move the mouse pointer to the desired element, wait for a special cursor shape (in the form of an arrow) to appear, then select the selected element with a single click. After selection, you can change the view through the ribbon, context menu, or call the standard cell format dialog:

In addition, Excel 2007 introduced many predefined PivotTable display styles:

Notice that control filters and drag areas are active in the chart.

Access to external data

As already noted, perhaps the greatest effect from using pivot tables can be obtained when accessing data external sources– OLAP cubes and database queries. Such sources typically store large amounts of information and also have a predefined relational structure that makes it easy to define dimensions of multidimensional data (pivot table fields).

Excel supports many types of external data sources:

The greatest effect from using external sources of information can be achieved by using automation tools (VBA programs) both to obtain data and to pre-process it in pivot tables.

Online Analytical Processing (OLAP) is a technology that is used to organize large business databases and support business intelligence. OLAP databases are divided into one or more cubes, and each cube is organized by the cube administrator to fit the way the data is retrieved and analyzed to make it easier to create and use the PivotTable reports and PivotChart reports you'll need.

In this article

What is business analytics?

A business analyst often wants to gain a larger picture of the business to view broader trends based on aggregated data, as well as view trends broken down into any number of variables. Business intelligence is the process of extracting data from an OLAP database and analyzing that data to produce insights that can be used to make informed business decisions and take actions. For example, using OLAP and business analytics, you can answer the following questions about business data.

How does the total sales of all products in 2007 compare with sales since 2006?

How does this compare to the date and time by benefit period of the last five years?

How much money did customers spend on 35 last year and how has this behavior changed over time?

How many products were sold in two specific countries/regions this month as opposed to the same month last year?

For each customer age group, what is the breakdown of profitability (both margin percentage and total) by product category?

Search for top and bottom sellers, distributors, suppliers, clients, partners and clients.

What is Online Analytical Processing (OLAP)?

OLAP (Online Analytical Processing) databases simplify business intelligence queries. OLAP is a database technology optimized for queries and reports rather than transaction processing. The data source for OLAP is online transaction processing (OLTP) databases, which are typically stored in data warehouses. OLAP data is extracted from this historical data and combined into structures that allow for complex analysis. OLAP data is also organized hierarchically and stored in cubes rather than tables. It is a complex technology that uses multi-dimensional structures to provide quick access to data for analysis. In this organization, a PivotTable report or a PivotChart report can easily display high-level summary data, such as sales totals for an entire country or region, and also display information about sites where sales are particularly strong or weak.

OLAP databases are designed to speed up data loading. Because it's an OLAP server, not Microsoft Office Excel, calculates aggregated values, requiring smaller data to be sent to Excel when creating or editing a report. This approach allows you to work with more raw data than if the data were organized in a traditional database, where Excel retrieves all the individual records and calculates aggregate values.

OLAP databases contain two main types of data: measures, which are numeric data, quantities, and averages that are used to make informed business decisions, and dimensions, which are categories used to organize those measures. OLAP databases help you organize data through multiple levels of detail, using the same categories you know to analyze data.

The following sections describe each component in detail below.

Cubic A data structure that groups measures into levels and hierarchies of each dimension you want to analyze. Cubes combine multiple dimensions such as time, geography, and product lines with summary data such as sales and inventory. Cubes are not "Cubes" in the strict mathematical sense, since they do not necessarily have the same sides. However, they represent an Apt metaphor for a complex concept.

Measurements A set of values ​​in a Cube that are based on a column in the cube's fact table and which is typically a numeric value. Measures are the central values ​​in a Cube that are preprocessed, processed, and analyzed. The most common examples are sales, revenue, income and expenses.

Member An element in a hierarchy that represents one or more occurrences of data. An element can be either unique or non-unique. For example, 2007 and 2008 represent unique members at the year level of a time dimension, while January represents non-unique members at the month level because there is more than one January in a time dimension because it contains data for more than one year.

Calculated element A dimension member whose value is calculated at run time using an expression. The values ​​of calculated members can be derived from the values ​​of other members. For example, a calculated element, profit, can be determined by subtracting the value of the element, plus costs, from the value of the element, sales.

measurement 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, a geographic dimension might include country/region, state/region, and city levels. In addition, a time dimension can include a hierarchy with levels of year, quarter, month, and day. In a PivotTable report or PivotChart report, each hierarchy becomes a set of fields that you can expand and collapse to show lower or higher levels.

Hierarchy A logical tree structure that arranges the members of a dimension so that each member has one parent member and zero or more children. A child is a member of an earlier group in the hierarchy that is directly related to the current member. For example, in a time hierarchy containing the levels quarter, month, and day, January is a child of Qtr1. A parent element is a lower-level member in a hierarchy that is directly related to the current member. The parent value is usually the consolidation of the values ​​of all child elements. For example, in a time hierarchy containing the levels quarter, month, and day, Qtr1 is the parent of January.

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

OLAP functions in Excel

Retrieving OLAP Data You can connect to OLAP data sources in the same way as you connect to other external data sources. You can work with databases created by using Microsoft SQL Server OLAP Services version 7.0, 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 only be displayed as a PivotTable report or PivotChart report, or in a worksheet function converted from a PivotTable report, but not as an external data range. You can save OLAP PivotTable and PivotChart reports in report templates, and create Office Data Connection (ODC) files to connect to OLAP databases for OLAP queries. When you open an ODC file in Excel, you see a blank PivotTable report ready to be placed.

Creating cube files for offline use You can create a stand-alone cube (.cub) file with a subset of the data from the OLAP server database. Offline cube files are used to work with OLAP data when you are not connected to a network. With a cube, you can work with more data in a PivotTable report or PivotChart report than you otherwise would and get data faster. You can create cube files only if you are using an OLAP provider, such as Microsoft SQL Analysis Services version 2005, that supports this feature.

Server actions A server action is an optional function that an OLAP cube administrator can define on a server that uses a cube element or measure as a parameter in a query to retrieve information in the cube, or to launch another application, such as a browser. Excel supports URLs, reports, rowsets, drill-down, and server-side drilldown, but does not support its own native statement and data set.

KPI A key performance indicator is a special calculated measure defined on the server that allows you to track "key performance indicators", including status (the current value corresponds to a specific number). and trend (values ​​over time). When they are displayed, the server can send corresponding icons, similar to the new Excel icon, to line up above or below status levels (for example, for a stop icon), as well as spinning a value up or down (for example, a directional arrow icon).

Formatting on the server Cube administrators can create measures and calculated members using color formatting, font formatting, and conditional formatting rules that can be assigned as an enterprise standard business rule. For example, the server format for income might be a currency number format, the cell color is green if the value is greater than or equal to 30,000 and red if the value is less than 30,000, and the font style is bold if the value is less than or equal to 30,000 and if the value is positive - ordinary. greater than or equal to 30,000. Find more information.

Office interface language The cube administrator can define translations for data and errors on the server for users who must view PivotTable information in a different language. This function is defined as a file connection property, and the user's computer locale and country must match the interface language.

Software components required to access OLAP data sources

OLAP Provider To configure OLAP data sources for Excel, you need one of the following OLAP providers.

Microsoft OLAP Provider Excel includes a data source driver and client software for accessing databases created with Microsoft SQL Server olap services version 7.0, Microsoft SQL Server olap version 2000 (8.0), and Microsoft SQL Server Analysis services version 2005 (9 ,0).

Third Party OLAP Providers Other OLAP products require additional drivers and client software to be installed. To use Excel's capabilities to work with OLAP data, the third-party product must conform to the OLE-DB standard for OLAP and be compatible with Microsoft Office. For information about installing and using a third-party OLAP provider, see system administrator or your OLAP product vendor.

Server databases and cube files The Excel OLAP client software supports connections to two types of OLAP databases. If the database on the OLAP server is online, you can retrieve source data directly from it. If you have a stand-alone cube file that contains OLAP data or a cube definition file, you can connect to that file and get source data from it.

Data sources A data source provides access to all the data in an OLAP database or offline cube file. Once you create an OLAP data source, you can base reports on it and return OLAP data to Excel as a PivotTable report or PivotChart report, or in a worksheet function converted from a PivotTable report.

Microsoft Query Using Query, you can retrieve data from an external database, such as Microsoft SQL or Microsoft Access. You do not need to use a query to retrieve data from an OLAP pivot table that is associated with a cube file. Additional information .

Differences in OLAP and non-OLAP source data features

If you work with PivotTable reports and PivotCharts from OLAP source data and other source data types, you will see some feature differences.

Data extraction The OLAP server returns new data to Excel whenever the report layout changes. With other types of external data sources, you query all the source data at one time, or you can specify parameters to query only when displaying different report filter field elements. Additionally, you have several other options for updating your report.

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

Note: The memory optimization option is also not available for OLEDB data sources and PivotTable reports based on a range of cells.

Field types OLAP source data. dimension fields can only be used as rows (rows), columns (category), or page fields. Measure fields can only be used as value fields. For other source data types, all fields can be used in any part of the report.

Access to detailed data For OLAP source data, the server determines the available levels of detail and calculates summary values, so the detail records that make up the summary values ​​may not be available. However, the server may provide property fields that you can display. Other source data types do not have property fields, but you can display basic information for data field and element values, and also display elements without data.

OLAP report filter fields may not have All elements, and the team Show report filter pages not available.

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

Nimi OLAP servers provide summary values ​​directly to the report, so you cannot change summary functions for value fields. For other source data types, you can change the aggregation function for a value field and use multiple summary functions for the same value field. You cannot create calculated fields and calculated members in reports with OLAP source data.

Subtotals In reports with OLAP source data, you cannot change the summary function for subtotals. With other source data types, you can change the total functions for subtotals and show or hide subtotals for all row and column fields.

For OLAP source data, you can include or exclude hidden members when calculating subtotals and grand totals. For other source data types, you can include hidden elements of report filter fields in subtotals, but hidden elements in other fields will be excluded by default.