Practical work on computer science MS. Excel. Finance in Excel Syntax Cube Functions ()

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,

Working with OLAP cube in MS Excel

1. Get permission to access OLAP Cuba SQL Server Analysis Services (SSAS)
2. On your computer, MS Excel 2016/2013/2010 must be installed (you can and MS Excel 2007, but it is not convenient to work in it, and completely poor functionality MS Excel 2003)
3. Open MS Excel, launch the Connection Setup Wizard with Analytical Service:

3.1 Indicate the name or IP address of the operating OLAP server (sometimes you can specify the open port number, for example, 192.25.25.102:80); Domain authentication is used:

3.2 Choose a multidimensional database and analytical cube (in case of access to Cuba):

3.3 Connection settings with the analytical service will be saved in the ODC file on your computer:

3.4 Select the report type (summary table / graph) and indicate the place to accommodate it:

If a connection has already been created in the Excel book, you can use again: Main Menu "Data" -\u003e "Existing Connections" -\u003e Select Connection in this book -\u003e Insert the consolidated table into the specified cell.

4. Successfully connected to Cuba, you can proceed to interactive data analysis:

Starting an interactive data analysis, it is necessary to determine which of the fields will be involved in the formation of rows, columns and filters (pages) of the consolidated table. In the general case, the summary table is three-dimensional, and it can be assumed that the third dimension is perpendicular to the screen, and we observe the sections, parallel plane of the screen and defined by what "page" is selected to display. Filtering can be done by dragging the corresponding measurement attributes to the report filters area. Filtration limits the space of the cube, reducing the load on the OLAP server, so preferably first install the necessary filters. Then place the measurement attributes in the row areas, columns and indicators to the data area of \u200b\u200bthe consolidated table.

Each time a summary table changes, the MDX instruction is automatically sent to the OLAP server, which is returned to the data. The larger and more complex the amount of data being processed, calculated indicators, the longer the time execution time. You can cancel the execution of the query by pressing the key Escape.. The last operations performed can be canceled (Ctrl + Z) or return (Ctrl + Y).

As a rule, for the most commonly used combinations of measurement attributes in Cuba, pre-calculated aggregated data are stored, so the response time of such requests for a few seconds is stored. However, it is impossible to calculate the entire possible combination of aggregations, as it may take a lot of time and storage space for this. To execute massive requests to data at the level of detail, significant server computing resources may be required, so the time of their execution may be long. After reading data from disk drives, the server places them in the RAM cache, which allows subsequent such requests to be performed instantly, since the data will be removed from the cache.

If you think your request will often be used and the time of its execution is unsatisfactory, you can contact the service of analytical developments to optimize the execution of the query.

After placing the hierarchy in the area of \u200b\u200brows / columns it is possible to hide individual levels:

Key attributes (less often - for attributes above the hierarchy) of measurements can be properties - descriptive characteristics that can be displayed both in pop-up tips and in the form of fields:

If you want to display several field properties at once, you can use the corresponding dialog list:

User-defined sets

In Excel 2010, it was possible to interactive creating its own (user-defined) sets from measurement elements:

Unlike the sets of created and stored centrally on the side of the cube, custom sets are saved locally in the Excel book and can be used hereinafter:

Advanced users can create sets using MDX designs:

Setting the properties of the pivot table

By means of the "Consolidated Table Settings ..." of the context menu (right-clicking on the summary table), it is possible to configure a consolidated table, for example:
- Conclusion tab, "Classic Classic Table Layout" parameter - the consolidated table becomes interactive, you can drag the fields (Drag & Drop);
- Tab "Conclusion", "Show items without data in strings" parameter - Empty strings that do not contain any values \u200b\u200bof the indicator on the appropriate measurement elements will be displayed in the pivot table;
- Tab "Marking and format", "Save the formatting of the cell format when updating" - you can override and save the cell format when updating the data;

Creating consolidated diagrams

For the existing summary OLAP table, you can create a summary diagram - a circular, line, histogram, schedule, point and other types of diagrams:

In this case, the summary chart will be synchronized with a consolidated table - when changing the composition of indicators, filters, measurements in the summary table, a summary table is also updated.

Creating information panels

We highlight the source consolidated table, copy it to the clipboard (Ctrl + C) and insert it with a copy (Ctrl + V), in which the composition of the indicators will be changed:

To simultaneously manage multiple consolidated tables insertion (new functionality, available, starting from MS Excel 2010). We connect our Slicer to the consolidated tables - click right-click on the cut, select in the context menu of the connection to the "Connection Table ...". It should be noted that there can be several panels of sections that can serve simultaneously consolidated tables on different sheets, which allows you to create coordinated information panels (Dashboard).

Cut panels can be configured: you need to highlight the panel, then see "Size and Properties ...", "Circuit Settings", "Assign Macro" in the context menu activated by right-click or "Parameters" item of the main menu. It is possible to establish the clumility of columns for the elements (buttons) of the cut, the size of the cut buttons and the panel buttons, determine the color gamut and the design of the design from the available dial (or create your own style), define your panel heading, assign a software macro by which you can expand Functional panel.

Excel query MDX execution

1. First of all, you need to perform the DRILLTHROUGH operation on some indicator, i.e. descend to detailed data (detailed data is displayed on a separate sheet), and open a list of connections;
2. Open connection properties, go to the "Definition" tab;
3. Select the default command type, and in the Text field, add pre-prepared MDX inquiry;
4. When you press the button after checking the correctness of the query syntax and the availability of the appropriate access rights, the request will be turned on the server, and the result will be presented in the current sheet as a conventional flat table.
   View the text of the MDX query generated by Excel is possible by installing a free addition, which also provides other additional features.

Translation into other languages

Analytical cube supports localization into Russian and English (if necessary, localization is possible to other languages). Transfers are distributed to the names of measurements, hierarchies, attributes, folders, measures, as well as elements of individual hierarchies in the event that they have translations on the side of the accounting system / data warehouses. To change the language, you must open the Connection Properties and in the Connection Row Add the following option:
Extended Properties \u003d "Locale \u003d 1033"
Where 1033 - Localization into English
1049 - Localization into Russian

Additional Excel Extensions for Microsoft Olap

Opportunities for working with Microsoft OLAP cubes will increase, if you use additional extensions, for example, OLAP Pivottable Extensions, thanks to which you can use the rapid search for measurement:

Problems of analysts, OLAP, data warehouses are increasingly interested in Russian IT professionals. To date, there are many good, academic in the way of presenting materials on this subject, including input in our computer printing and the Internet. We bring to your attention an article in which we are consciously trying to explain OLAP "on the fingers", on a specific example. Practice shows that such an explanation is necessary for some IT professionals and especially end users.

So, OLAP * 1 in the first approximation, "on the fingers", can be defined as a special way to analyze data and obtain reports. Its essence is to provide the user a multidimensional table that automatically summarizes the data in various cuts and allows you to interactively manage calculations and a report form. This article will tell about the technology and main OLAP operations on the example of an example analysis of the invoices of an enterprise employed wholesale food trafficking.

*one. OLAP - On-Line Analytical Processing, operational data analysis.

As a tool, the OLAP system of the simplest and inexpensive class - OLAP client * 1 will be considered. For example, the simplest product of OLAP clients is selected - "Contour Standard" company INTERSOFT LAB. (For clarity further in the article, the generally accepted OLAP terms will be denoted by bold and accompanied by English-speaking analogues.)

*one. More information about the classification of OLAP systems is described in the article "OLAP, Made in Russia" in PC WEEK / RE, №3 / 2001.

So, proceed to work with the system. To begin with, you will need to describe the data source (Data Source) - the path to the table and its fields. This is a user task that knows the physical implementation of the database. For end users, it translates the name of the table and its fields in terms of the subject area. For the "data source" there is a local table, a table or presentation (view) of the SQL server or stored procedure.

Most likely, in a specific accounting database, it is not stored in one, but in several tables. In addition, some of the fields or records may not be used for analysis. Therefore, the sample (result SET or Query) is created, - in which configured: algorithm for combining tables on key fields, filtering conditions and a set of returned fields. We call our sample "invoices" and put in it all fields of the source of data "invoices". Thus, an IT specialist, creating a semantic layer, hides the physical implementation of the database from the end user.

Then the OLAP report is configured. This may involve a specialist in the subject area. First, the fields of the flat sampling of data are broken into two groups - Facts (Facts or Measures) and measurements (Dimensions). Facts are numbers, and measurements are "cuts", in which the facts will be summed up. In our example, measurements will be: "region", "city", "buyer", "product", "date", and the fact will be one - the "amount" field of the invoice. For the fact you need to choose one or more aggregation algorithms. OLAP is capable not only to summarize the results, but also to perform more complex calculations, up to statistical analysis. When selecting multiple aggregation algorithms, virtual, calculated facts (Calculated Facts) will be created. In the example, a single aggregation algorithm is selected - "Amount".

A special property of OLAP systems is the generation of measurements and data on older time periods from the date and automatic calculation of the results for these periods. We choose the periods of the Year, the Quarter and the Month, while the data for every day will not be in the report, but the generated measurements of the Year, the Quarter and the Month will appear. Let's call the sales analysis report and save it. Work on creating an interface of an analytical application is completed.

Now, daily or monthly starting this interface, the user will see a table and a schedule in which invoices are summed up by goods, buyers and periods.

In order for manipulations with the data intuitively understandable, the dynamic table management tools are the elements of the table itself - its columns and strings. The user can move them, delete, filter and perform other OLAP operations. In this case, the table automatically calculates new intermediate and final results.

For example, dragging (operation "Move") "Product" column for first place, we will receive a comparison report - "Comparison of sales of goods for the year." To aggregate the data for the year, it is enough to drag the "quarter" columns and the "month" into the top part of the table - "the area of \u200b\u200binactive measurements". Measurements "Quarter" and "Month" transferred to this area will be closed (Operation "Close Dimension"), i.e., excluded from the report; At the same time, the facts are summed up for the year. Despite the fact that the measurements are closed, it is possible to set specific years, quarters and months to filter data (operation "Filter").

For greater clarity, you will change the type of graph illustrating the OLAP table, and its location on the screen.

Deepening to the data (DRILL DOWN operation) allows you to get more detailed information about the sales of the goods you are interested in. By clicking on the "+" sign opposite the goods "Coffee", we will see the volume of its sales in the context of the regions. Overlook the Ural region, we will receive sales in the context of the cities of the Ural region, delving into the data on "Yekaterinburg", we will be able to view data on the wholesale buyers of this city.

You can use open measurements to install filters. To compare the dynamics of candy sales in Moscow and Yekaterinburg, install filters for the "product" and "city" measurement filters.

Close the unnecessary measurements and select the type of line graphics. On the resulting schedule, you can trace the dynamics of sales, evaluate seasonal fluctuations and the connection of drops and the growth of goods in different cities.

Thus, we made sure that the OLAP technology allows the user from a single interface to release dozens of species of a wide variety of reports by driving a dynamic OLAP table with a mouse. The task of a programmer owning such a tool becomes not a routine coding of reporting forms, but setting up an OLAP client to databases. At the same time, the reporting methods intuitively understandable to the end user.

Indeed, OLAP is a natural continuation and development of the idea of \u200b\u200bspreadsheets. In essence, the visual interface of OLAP is also a spreadsheet, but equipped with a powerful machine computing machine and a special standard of data representation and management. Moreover, some OLAP clients are implemented as ADD-IN to MS Excel. Therefore, the magnificent army of "white collar", confidently owning spreadsheets, very quickly masters and OLAP tools. For them, this is a "velvet revolution", providing new opportunities, but not conjugate with the need to retrain.

If the reader, after reading this article, has not lost interest in OLAP, it can refer to the materials mentioned at the beginning. Collections of such materials are posted on a number of sites on the Internet, including the site interSoft Lab - www.iso.ru. You can also download a demonstration version of the "Contour Standard" system with the example described in the article.

Analytical analytical processing (OLAP) is a technology that is used to streamline large business databases and business intelligence support. OLAP databases are divided into one or more cubes, and each of them is organized by the Cuba administrator so that it corresponds to a method for obtaining and analyzing data to facilitate the creation and use of reports of a 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 base data OLAP. and analyzing this data for information that can be used to adopt 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 quick 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, and not Microsoft Office Excel, calculates generalized values, when creating or changing the 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 a summary table or report report, each hierarchy becomes a set of fields that can be deployed and folding to show lower or higher 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. Parent element is a member of more low 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, Microsoft SQL Server Analysis Services Server Analysis Server Analysis 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 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 need to be installed additional drivers and client software. To use Excel features to work with OLAP data, third-party product must comply with 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 Client software Excel Olap 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.

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 of extracting and processing data from Microsoft Office applications, and thus allowing prognostic analysis to 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. Add-on package office applications To extract and process data, give end users with the ability to perform the analysis directly in Microsoft Excel and Microsoft Visio applications.

IN microsoft composition 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.