Practical robots with olap technology in excel. Olap cubi in excel

Clients OLAP-based data, so that the calculation of aggregate data (sums, average values, maximum or minimum values) and those images, given the aggregate data itself, can be found in such an address in the OLAP cache.

As soon as the given data can be found in the real-time DBMS, the aggregate data can be calculated by the OLAP-system itself. Well, dzherelo vikhidnih danih - server DBMS, a lot of clerical OLAP-inputs are sent to the server with SQL-feed, and the GROUP BY operator is inserted, and as a result, aggregate data transferred to the server will be recognized.

As a rule, OLAP-functionality is implemented in the way of statistical data processing (from the product class on the Russian market, the products of StatSoft and SPSS companies are widely expanded) and in some electronic tables. Zokrem, with the help of bagatovimirny analysis of Microsoft Excel. For an additional product, you can save a small local, large-scale OLAP-cube in the view of the file and view it doubly trivially.

Look for the Microsoft Office software package for data processing є there are a number of functions, so that you will not have access to the data processing options from Microsoft Office programs, and you allow yourself to use the predictive analysis on your own. local computer... Given the fact that Microsoft SQL Server platforms have been introduced into the service by algorithms for data processing and data accessibility from the middle of Microsoft Office documents, business-related information can be easily removed from the folding set of data from the mouse clicker. Naddovi to the Office program package for removing and processing of the data give the Kintsevs the ability to analyze without the need for Microsoft Excel and Microsoft Visio programs.

Prior to the Microsoft Office 2007 warehouse, there were three OLAP components:

1. Client for Data Processing for Excel allows you to design data processing and data processing on the basis of SSAS and Keruvati for Excel 2007;
2. Add an analysis of tables for Excel programs; allow you to use the SSAS service for the function of learning and processing information for analyzing data that can be stored in Excel tables;
3. templates for drawing and processing data for Visio programs allow you to visualize a tree, a regression tree, cluster diagrams and hedges of deposits on Visio diagrams.

Analytical Analytical Processing (OLAP) is a whole technology, which is used to order the databases of great business and business analysts. The base of the OLAP data can be added to one or more cubes, and the skin is organized from them by the administrator of the cube, so that you will be guided by the method of rejecting and analyzing the data, so that you know how to use the tables of information about the data.

In tsy statty

What about business analytics?

A business analyst often wants to reject a larger picture of business, to re-examine broad trends based on aggregated data, and also to re-examine trends with a breakdown on whether or not a number of snakes. Business analytics - the process of obtaining data from the OLAP data base and analyzing data from the OLAP data base, and the analysis of data from the OLAP data base, and the analysis of the data for the removal of properties, as it is possible to obtain information for the purpose of receiving informational business solutions and business analysis. For example, with the help of OLAP and business analysts, you can go to the next day.

How big is the amount of sales in our products in 2007 How to compete with the overall sales in 2006?

How can you compare with the date that hour after the previous period for the rest of the five rockies?

Did the few pennies of the clientech turn into the 35th last rock and what rank changed the behavior in an hour?

How many products have been sold in two countries / regions of this month, for the last month from the same month?

For a skin group of different groups of clients, what is the profitability level (like a lot of margins, and so on) for categories of goods?

Prompt for short and low sellers, distributors, suppliers, customers, partners and customers.

So is there an analytical processing in the Internet (OLAP)?

Base danih OLAP (Online Analytical Processing) to lay down on the power of business analytics. OLAP is a database technology that is optimized for powering and not processing transactions. Dzherelo tribute for OLAP is a centralized base of data processing transactions (OLTP), as you want to receive money from tributes. OLAP's tribute is drawn from a series of historical tributes, which is combined in a structure, which allows for a folding analysis. OLAP data can also be organized in a hierarchical manner and stored in cubes rather than tables. The price is foldable technology, yak vikoristovuє bagatovymіrnі structures for securing quick access to the tribute for analysis. From the Organizational organization for the production of tables or the sound of the produced diagrams, you can easily visualize the production of high-quality data, such as sales bags throughout the country or the region, as well as a great presentation of the website in the design of sales.

Bazi danih OLAP is designated for the accelerated import of tributes. Oskіlki OLAP server, not Microsoft Office Excel, counting the relevant values, if you change the call, you need to supervise in Excel less data. Tsei pidkhid allows for the process of doing a lot of outgoing dues, but not for the fall, since the dues are organized at the traditional base of danikhs, de Excel are all about the records and counting the general values.

The basis of OLAP data is two main types of tributes: come in, as they are numerical tributes, some of them are of the middle value, so that you can take advantage of the information-based business decisions, that you go in, that you can enter the category. The OLAP data base will help you to order the data for the additional information on the details, vicorist data and categories, as you know for the analysis of the data.

In the onset of skin breakdowns, the component of the detailed descriptions is below.

Cubichesk The structure of the tribute, go one by one for the ryvnya and the hierarchies of the skin vimir, which you want to analyze. Cubes will be able to eat a little bit of vimiryuvan, such as an hour, geography and rows of a product, with bag data, such as sales and stocks. Kubi is not a "Kubi" in a suvorom mathematical sense, so the stench does not necessarily obscure the same sides. Tim is not mensch, stench є Apt metaphor for a smart learner.

Vimiryuvan The numerical value of the Cuba, which is based on 100% in the tables of the facts of the cube and the numerical values. Come in - the central meaning of the Cuba, which in front of the front is crumbled, crumbled and analyzed. Naybilsh widened butts є sales, come, come that vitrati.

Member An element in the ієrarchії, which is represented by one abo kilka danih. An element can be either unique or non-unique. For example, 2007 and 2008 are unique elements at the same time, while the current one represents non-unique elements at the same time, since at least one hour is more than one day.

Counting element An element of vimir, the meaning of which is counted for the hour of the visit for an additional viraz. The value of the numbered elements can be the same as the value of the other members. For example, calculating the item "arrival" can be used as a way to change the item's value, as well as the item's value, for sale.

vimir A set of one or a few well-ordered іnrarchіy іvnіv Cuba, such as koristuvach rasumіє and vikoristovuє as the basis for the analysis of the data. For example, geographic scope can be included for the country / region, state / district and city. Besides, in the hour, you can include the hierarchy with the rіvnem "rіk", "quarter", "month" and "day". At the top of the table, or at the bottom of the developed diagrams, the skin architecture becomes a set of fields, as it is possible to display the lower or higher visoki pivnі.

Ієrarchіya The structure of the tree is logical, like in the order of the elements of vimiryuvannya, so the leather element is only one father's element and zero or more daughter elements. A daughter element is a member of a large early group in the Araarchy, without dressing with a full member. For example, in the іnrarchії hour, to revenge the іvnі quarter, every month that day, Sіchen є daughter element Qtr1. Batkivsky element is a member of the lower level of the hierarchy, without being tied with a regular member. Fatherland values ​​є consolidation of the meaning of all daughter elements. For example, in the ієrarchії hour, to revenge іvnі "quarter", "mіsyats" that "day", Qtr1 є Batkivskiy for the present.

Rivn In the architecture of the day, there can be organized in a great deal of low detail, such as rock, quarters, monsters and days in the hour.

OLAP functions in Excel

Otrimannya tribute OLAP You can connect to the OLAP tribute server the same way as before the most recent tribute dzherel. You can see the data bases running for the additional Microsoft SQL Server OLAP services version 7.0, Microsoft SQL Server Analysis Services version 2000 and Microsoft SQL Server Analysis Services version 2005, server Microsoft products OLAP. Excel can also be used with third-party OLAP products, such as OLE-DB for OLAP.

OLAP data can be displayed only at the view of the built tables, or the view of the built diagrams, or the function of the sheet, transformed from the set of tables, but not at the view of the range of the data. You can save the generated OLAP tables and the generated diagrams from the call templates, as well as the connection files to the Office Data (ODC) to connect to the OLAP databases to power the OLAP. When the ODC file is opened, Excel displays an empty sound of the created tables, which is ready for distribution.

Cube file creation for standalone vikoristannya You can open a standalone cube file (. Cub) with a subdivision from the OLAP server database. Standalone cube files can be used for robots with OLAP data, even if they are not connected to the fancy. For an additional cube, you can practice with a large collection of tributes at the completed table, for example, in the constructed diagrams, below in the last vipad, that will speed up the collection of tributes. You can open the Cube files if you want to use an OLAP manager, for example Microsoft SQL Analysis Services version 2005, to add a function.

Server diy Server dialer is a non-binding function, as an administrator of an OLAP cube can be started on a server, as a vicarist element of a cube, or as a parameter at the power supply for rejecting views from Cuba, or for launching other programs, for example, a browser. In Excel, URL-addresses are generated, see, type in rows, details and distribution of reports from the server, but not the authoritative operator and type of data.

KPE The key indicator of efficiency is the whole world, which can be counted, is assigned to the server, which allows the display of "key indicators of efficiency", including the song number). that tendency (the value of one hour). If the stench appears, the server can display icons similar to the new Excel icon;

Server format Administrator's office of cubes can be opened and entered and enumerated elements for additional color formatting, formatting for the font and rules smart format which can be used as a corporate standard business rule. For example, a server-side format for income can be a numeric currency format, a color in the middle of a green color, which is more than or more expensive than 30,000, and a chervony, which is less than 30,000; - Zvychainy. more or more expensive 30,000. additional views you can know.

Move to Office interface The administrator of the cube can start the transfer for tributes and pardons on the servers for the criminals, as they may look at the views of the tables made by me. This function is designated as the power of the file, and the regional parameters of the computer of the clerk of the country may be presented to the interface.

Software components required for access to OLAP dzherel data

OLAP supervisor To set up an OLAP databank for Excel, one of the OLAP providers below is required.

Microsoft OLAP Manager Excel includes a driver for danikh and client software for accessing data bases, running behind the addition of Microsoft SQL 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 OLAP Leaders For other OLAP products, it is necessary to install additional drivers and client protection software. For Excel capabilities for OLAP data robots, the third-party provider product is guilty of conforming to the OLE-DB standard for OLAP and being the sum of Microsoft Office. To correct the view of the installation of a third-party OLAP master, to return to system administrator or the head of the OLAP product.

Base danih servers and files Cuba Klієntske software for the protection of Excel OLAP can be used to create a database with two types of OLAP databases. As far as the database on OLAP servers is available in stock, you can trim out the data from it. As long as you have an offline cube file, which reveals the OLAP data, or the file of the value of the cubes, you can connect to the whole file and trim the output data.

Dzherela danih Dzherelo gave access to all the data from the OLAP data base or the file of the autonomous cube. Once you have created OLAP data, you can download OLAP data in Excel to a new view and turn it over to the built tables, or the built diagrams, as well as the sheet function, rewrite from the created tables.

Microsoft Query With the help of Query, you can remove data from the original data base, for example, Microsoft SQL or Microsoft Access... To remove the data from the created OLAP tables, linked to the cube file, you do not need to be powered up. Dodatkovі vіdomostі.

OLAP and non-OLAP features

As soon as you can see the results of the tables and the diagrams from the OLAP, the data and the other types of the data, you need to know how to do it.

Danikh viluchennya The OLAP server rotates new data in Excel for an hour of skin change to the layout. When you select one of the other types of data, you can power up all of the data at a time, or you can set the parameters for powering up only when you see the new elements of the filter in the sound. In addition, you have a few options to improve the sound.

For calls based on OLAP data, the filter field parameters are unavailable, background power is unavailable, and memory optimization is unavailable.

Note: The parameter of memory optimization is also inaccessible for the OLEDB dzherel and in the tables, but it is based on the middle range.

Type of poliv Latest OLAP data. fields vimir can be vicoristovuvatisya only yak rows (rows), hundred (category) or fields of a side. Fields of entry can be victorious; For other types of outgoing tributes, the field can be victorious at any part of the sound.

Access to detailed tributes For outgoing reports, the OLAP server makes available available details and numbers, so records with report items, which can become total values, may be unavailable. Tim is not the man, the server can provide fields of authority that can be imagined. Some types of financial gifts do not conceal the fields of authority, but it is possible to visualize the basic views for the meaning of the fields given and to the elements, as well as to visualize the elements without data.

OLAP filter fields may not have all elements, and the command Show side to filter sound unavailable.

Cob sorting order For OLAP output data, items are displayed in the order that the OLAP server turns on. You can change the order of the items. For other types of outgoing tributes, new items can be sorted from one item to another in the order of growth.

Nimi OLAP servers provide the best value for the call, so it’s hard to change the functions for the fields. For other types of output data, you can change the statistical function of the value field and list the number of value fields for one and the same value field. It is not possible to display the numbered fields and numbered elements in the calls with the OLAP output data.

Industrial bags In the case of OLAP data, it is not possible to change the function for industrial students. With the other types of data, it is possible to change the functions for industrial children and display or to provide industrial bags for all watering rows and hundreds.

For outgoing OLAP data, it is possible to turn off or turn off the hooked up elements when opening the intermediate and out-of-the-box submissions. For other types of field data, you can turn on filter fields in the industrial pods, or turn on the elements of the other fields to be included in the selection.

The first interface for making tables, also called as making calls, has been included in the Excel warehouse until 1993 (version Excel 5.0). Uninvolved on the bezlich brown functional possibilities, it’s practical not to get stuck in a robot with a large number of Excel. Navit admonished Most of the time, the term "sounds like" is prompted for the help of folding formulas. We will try to popularize victorious tables among all robotic economies. The statti is negotiated theoretical basis There are practical recommendations on how to get started, as well as to guide the butt to access data on the basis of several tables.

Terms of Bagatovimir Analysis of Danikh

Lots of economies chuli terms "bagatomirnі dani", "virtual cube", "OLAP-technologies" just. Ale, with detailed roses, beckon to start, but it’s hard to find out, but to go. So that people think of respect as a foldable one, and do not want to be seen as good as ever. As a matter of fact, it’s not so.

Bagatovimirni Dani, Vimiri

It is possible to improve the value of money, so that the economy can depend on the generous data, or to pay taxes from the designated viewer for additional electronic tables. For the bagatomirnist here rely on the respect for the power of the introduced, the glance or the analysis of the same the most recent viglyad, zasosuvannyam raznykh ugrupovan and sortuvan danih. For example, the sales plan can be analyzed according to the following criteria:

• vidi chi group of goods;
• brandy with categories of goods;
• period (month, quarter, rik);
• buyers and groups of buyers;
• region sale
• etc.

From the indicative criteria, the terms of the complex analysis are called "vimiryuvannyam". It is possible to say that Vimir characterizes the information that is typical for a set of values. A special type of visualization of rich information and data. Our sales plan can include:

• about'єm sale;
• sale price;
• individual record
• etc.

Theoretically, the data can be standardized to the generic information (for example, it is possible to group the data for the sale price), but you can still give the data to a special type of value.

In such a rank, it can be said, that practical robots of economy have two types of information: bagatovimіrnі danі ( the factual and planned dates, which may be without a sign) and adversaries (characteristics of chi vimiruvannya danih).

OLAP

The abbreviation of OLAP (online analytical processing) sounds like "analogous processing of a real hour". It is not even more specific that it is possible to introduce any kind of software product. Behind the OLAP technology is the transfer of robotics technology with special calls, including software security, for rejecting and analyzing the structured data. One of the most popular software products that implements OLAP technologies is SQL Server Analysis Server. Deyakі navіt pomilkovo vvazhayut yogo yogo one representative of the software implementation of the concept.

Virtual cube of Danikh

"Virtual cube" (bagatomirny cube, OLAP-cube) - a special term, proponated by the leaders of the special cube software security... OLAP systems are ready to use and save data from structures, and special interfaces for analysis (for example, Excel data) are turned up to a given number of virtual cubes. At the same time, such a vision of such a schematic is not necessary for processing a large amount of information. Have a fancy vipad virtual cube- a whole array of specially optimized and rich tributes, which are vikorystovuyutsya for the establishment of events. You can get rid of it through special software programs, as well as through easy access to the database tables, or be it just dumb, for example to Excel tables.

The table has been set

"Zvedeniy zvіt" (the table is set up, Pivot Table) - the central interface for displaying large-scale tributes. For an additional interface, you can group, sortuvati, filter and change the data from the method of rejecting different analytical vibrations. The update will be connected by simple means to the interface of the user, the data will automatically be aggregated according to the set rules, if no additional information is required or re-entered. Excel spreadsheet interface є, mabut, the most popular software product up to robots with a lot of data. Win danikh as dzherelo danih names dzherela danih (OLAP cubes and relational databases), internal ranges and electronic tables. Fixing since version 2000 (9.0), Excel will also adapt a graphical form for displaying large amounts of money - a Pivot Chart has been created.

Implementations in Excel, the interface of the data tables, allows you to change the size of the data in the data areas of the work sheet. For simplicity, it is possible to display a table, as it sounds, to lie at the top of the middle range (as a matter of fact, there is a good link between the formats in the middle to the fields of the table). An Excel spreadsheet has been created for the display area of ​​information: filter, 100%, rows and data. Vimiryuvannya danih called fields of the created tables... The fields may be of power and the format of the image.

Once again, I would like to brutalize those who have created an Excel spreadsheet and are designated for the analysis of data without the ability to edit information. Closer to the zmіstom bulo, the term “Pivot Report” (Pivot Report) was introduced everywhere, and the same interface was named before 2000 rock. However, in the upcoming versions, the rozrobniki have changed.

Reduction table

For its value OLAP-technology, in principle, cannot respect the power of change from previous days, from every hour of robotics to calls. Tim is not mensh, a tsiliy class has formed on the market software systems, as well as realizing the possibilities for analysis, as well as without a priori editing of the data in the large-scale tables of the budgetary system.

Vikoristovuchi vbudovanі zobudovanі zoby automatizії Excel, it is possible to revise a lot of non-standard projects. The application of the implementation of the editing for the Excel tables based on the data of the work sheet can be found on our website.

Preparation of bagatovimirnyh tributes

Let's go to the practical storing of building tables. I will try to analyze the data about sales in the winter. File pivottableexample.xls store from a few arkushiv. Arkush butt to revenge the main information about sales for the singing period. For simplicity, butt is analizuvatimo One numerical indicator - sales in kg. Є These are the keys to vimir danih: products, the buyer and the recipient (transport company). In addition, there is a collection of dodatkovykh vimiriv danikhs, which are indicative of the product: type, brand, category, post-manager, and also the type of purchase. Tsі danі zіbranі on arkushі Dovіdniki. On the practical side of the road, there can be more of them.

Arkush butt revenge the standard analysis of the given autofilters. Wonder at the butt of zapovnennya tables, obviously, for a normal analysis, the data about sales for dates are received (the stink of the roztashovani a hundred percent). In addition, you can use an autofilter to test the results of one or the other key criteria. Daily information about brandy, category and tipi. There is a lack of flexibility in grouping the data with automatic payments for the singing key (for example, many buyers). In addition, the set of dates has been recorded, so take a look at the information for the singing period, for example, 3 days, automatic grease do not give in.

Vzagal, the appearance of a zoomed roztashuvannya date in given application- The head part of the table. Having rocked the dates in a hundred and fifty, we appointed the revision of the table, with such a rank, allowing themselves the flexibility of vikoristovuvat analysis for the additional creation of the table.

First of all, it’s necessary to shake off some part of it - tobto. Put down the zoom of the rosary of one of the vimіryuvannyh tributes. Appendix of correct tables - sheet Sales.

The table is in the form of the journal entered information. Here is the date є equal to vimir danih. It’s also a slid of respect, for a general analysis in the tables it is absolutely safe to put one row to another (tobto sorting). The power of authority may have records in the relational databases. The very analysis of the great associations of the bases of the data orders on the front of the interface of the tables. To that, it is necessary to follow these rules and for an hour of robots in a dzherel given how the ranges are in the middle. At the same time, it is not necessary to fence in the robotic interfaces using Excel - creating tables, analyzing data, but formatting, filtering, grouping and sorting out some medium-sized ones can be quite reliable.

View autofilter to set sound

Theoretically, on the given Sales sheet, it is possible to carry out an analysis of three people: comrades, buyers and relocators. The data about the power of the products and the buyers on the given sheets on the day, obviously, do not allow the display of them in the data tables. In the normal mode, the entry of the created tables for the export data, Excel does not allow the entry of the data of some tables for the singing fields. You can get around the tse obmezhennya program zasobami - div. butt-add-on to the set of statistics on our site. Schob do not go into software methods processing information (Tim is bigger, but the smell is not universal) next to date additional characteristics bezposrednyo form introduced to the magazine - div. sheet Sales & Analyzes.

The stagnation of the VLOOKUP functions allows you to easily add to these data with deficient characteristics. Now, when the autofilter is locked, it is possible to analyze the data from the youngsters. Ale zalishaєtsya nevirіshenoy problem ugrupovan. For example, it is problematic to supply the money for the brands on the date. Iakshcho obmezhuvatisya Excel formulas, it will be necessary to add additional vibrations, vikoristovuchi function SUMIF.

Now we are surprised, as the possibilities are given the interface of the tables. On arkushi ZvedennyaAnaliz motivated by the number of sounds based on the range of intermediate values ​​from the sheet data Sales & Analysis.

The analysis is prompted through the Excel 2007 interface. Line \ Insert \ Completed table(In Excel 2000-2003, the menu Dani \ Completed table).

Another that third table of leaves is through a copy that is further adjusted. Dzherelo danih for all tables are the same. You can change the data, changing the data, and then you will need to update the data of the events.

In our opinion, the passes are obvious in the information and information. You can minify the filters, one hundred and fifty rows and prikhovuvati groups, the meaning of be-like vimiryuvans, stasovuvati manually overtightening and automatically grading.

Power and format

In addition to the bezposerednogo representation of the tributes, there is a great set of possibilities for the representation of the most famous tables. You can get danis to get help for additional filters. For a single element of the field, simply use the context menu item seenlity(Version 2000-2003 Prikhovati).

Set the display of the other elements of the built tables, not through the format of the commercial, but through the adjustment of the field, or the element of the built tables. For the sake of it, it is necessary to bring the indicator of Misha to the required element; For viewing, you can change the view through the line, the context menu, or display a standard dialogue in the format of the commercial:

In addition, in Excel 2007, the impossibility of zooming in styles for displaying created tables:

Beast of respect, how in the diagrams of active filters, how to manage, and areas of over-tightening.

Access to zovnіshnіkh danih

As it was already meant, mabut, the greatest effect from the storage of the data tables can be edited for access to the given data dzherel - OLAP-cubes and feeds to the data bases. Such dzherela make sure to save great information and information, and also create a clear relational structure, which allows you to easily visually check for rich and well-known tributes (fields of the created tables).

Excel pіdtrimu bаgato typіv dzherel zovnіshnіkh danikh:

You can reach the most effective results from the latest information dzherel if you stop using automation (with VBA programs) as you reject the given data, so it’s in front of the foreground processing in the tables.

Problems of analytics, OLAP, danikh schemas are getting more and more interest in Russian IT-faculties. In our computer friend and on the Internet, there are a lot of good, academic materials based on the method of vicarious materials on a wide range of topics, including an introductory character. We will propose to your respect for the article, in which you will explain OLAP “on your fingers”, on a specific application. The practice shows, as well, it is necessary to explain the necessary things to the IT workers and especially to the Kintsev koristuvachs.

Otzhe, OLAP * 1 at the first close "on the fingers" can be seen as a special way of analyzing the data and rejection of the results. This is the essence of the field in the given tables, which automatically generate data in the new development and allows you to interactively control the numbers of that form of sound. The statistics will tell you about the technology and the main OLAP operations on the application of analysis of rakhunkiv-invoices of the enterprise engaged in the wholesale trade of commodity products.

*one. OLAP - On-Line Analytical Processing

As a tool, an OLAP-system of the simplest and inexpensive class - OLAP-client * 1 will be displayed. For example, the simplest product at the OLAP-clients warehouse is “Kontur Standard” by Intersoft Lab. (For the sake of clarity, the statistics for the foreign-accepted terms OLAP are designated in bold and super-juvenile for the English-language counterparts.)

*one. A report on the classification of OLAP-systems is given in the article "OLAP, broken in Russia" in PC Week / RE №3 / 2001.

Otzhe, start to the robot using the system. For the cob, it is necessary to describe Dzherelo danih (data source) - go to the table and field. Tse zavdannya koristuvach, who know the physical implementation of the base danih. To translate the name of the table and the field of the field in the term of the subject area for Kintsev koristuvachiv. Behind the "dzherelom tribute" there is a local table, the table is either represented (view) of the SQL server, or the procedure that is taken care of.

Shvidshe for everything at the specific base of the given rakhunki-invoices are taken over one, but a few tables. In addition, a part of the fields or recordings may not be victorious for analysis. To that, the Vibirka (result set or query) is set, - in which it will be adjusted: the algorithm for combining tables by key fields, clean filter and set of fields, how to rotate. We call our vibirka "Rakhunki-fakturi" and in it all the fields are dzherela danih "Rakhunki-fakturi". In such a rank, IT-fahіvets, a flashing semantic sphere, prikhovu physical realization of the basis of danikh kintsevoy koristuvach.

Then OLAP-calls are tuned up. Tsim can take care of the subject matter. A collection of the field of a flat vibration is given to be broken into two groups - facts and measures and dimensions. Facts are numbers, and vimiri are “razrizi”, for those who have a lot of facts. At the application, the vimirs will become: “Region”, “Misto”, “Buyer”, “Product”, “Date”, and there will be one fact - the “Sum” field of the rakhunka-invoice. For a fact, you need to vibrate one or more algorithms in the aggregation. OLAP is a building block of pidsumki, and a simple calculation, up to statistical analysis. When you select a number of algorithms in the aggregation, there will be virtual facts that are calculated facts. The application has one aggregation algorithm - "Sum".

The power of OLAP-systems is special - the generation of money for the older hour periods from the date is automatically calculated for the number of periods. Viberemo period "Rik", "Kvartal" and "Misyats", with a tribute for a skin day, you will not be called, but there will be generated vimiri "Rik", "Quarter" and "Misyats". They call it “Analysis sale” and save it. The robot is finished with the interface of the analytic add-on.

Now, every now and then, every now and then, I launch an interface, a table and a graph, in which invoices are sent for goods, buyers and periods.

Manipulation with the data of the bullet intuitively intelligently, with the tools for managing the dynamical table є elements of the table itself - ї columns and rows. Coristinous can move, see, filter and display OLAP operations. In general, the table will automatically calculate new industrial and residual results.

For example, by dragging (operation "move") the column "Goods" on the first place, we can read about the order - "Porivnyannya obsyagiv sale of goods for the price". To aggregate the tribute by stretching out to fate, it is enough to pull the columns “Quarter” and “Misyats” at the top of the table - “the area of ​​inactive vimiriv”. Vimiri "Quarter" and "Misyats", transferred to the whole region, will be closed (operation "close dimension"), so that it will turn on from the sound; at one's own facts it is necessary to stretch out to rock. Unimportant to those who are closed, after them you can set specific rocks, quarters and months for filtering tributes (operation "filter").

For greater accuracy, the type of graph is variable, but also a visual OLAP table, and the same rostashuvannya on the screen.

Going into danі (operation "drill down") will allow you to review more detailed information about the sale of goods, so that you can tell us. Clicking on the "+" sign next to the "Kava" product, we can also help you on sales in the regions. Opened the "Ural" region, took off the sales obscenities at the retail sites of the Ural region, having stuck with the data on "Yekaterinburg", it is possible to look back at the tributes from the wholesale buyers of the central place.

For the establishment of filters, you can choose to display the results. In order to increase the dynamics of sales of tsukeroks from Moscow and Yekaterinburg, a filter will be set up for the sale of “Tovar” and “Misto”.

Zakrєmo not used vimiri and the type of graph "Line". On the graph it is possible to simulate the dynamics of sales, assess the season, and the calls of the fall and the growth of goods in the new regions.

In such a rank, we have changed how OLAP-technology allows dozens of types of young calls from one interface to display dozens of types of young calls, running a dynamic OLAP-table behind the help of Misha. To the head of the programmer, who will be such an instrument, the old routine of coding the extreme forms, and the adjustment of the OLAP client on the basis of the data. With a wide range of methods for managing the sound, it is intuitively intelligent and intelligent.

Fair, OLAP - tse naturally advanced that development of the idea of ​​electronic tables. By the way, the OLAP visual interface is a chain of electronic table (spreadsheet), but it is equipped with a forced calculating machine and a special standard for presenting data and managing them. Moreover, OLAP clients have implemented add-in to MS Excel. To that, a million army of "bilikh comics", as played by Volodya's electronic tables, will learn more and more OLAP-tools. Oh, the "Oxamite revolution", which is necessary for new opportunities, is tied to the need to retrain.

As a reader, having read the article, without losing interest to OLAP, you can turn to guessing on the cob of materials. Lists of such materials are distributed on low sites in the Internet, including the Intersoft lab site - www.iso.ru. You can also add a demo version of the Kontur Standard system to a demo version of the Kontur Standard system.

The standard data tables are stored on the local hard disk. In such a rank, if you can, you can cheruvati with them and reorganize them, you cannot navigate to the hedge. Ale tse zhodnoyu world does not hesitate to build OLAP tables. In OLAP tables, the cache is not stored on the local hard disk. To that immediately after connecting from local framing your spreadsheet is paid to receive praise. You will not be able to move to the new railway field.

I mean, you still need to analyze OLAP data when you connect from a grid, open an autonomous cube of data. Autonomous cube danih - tse ok file, such as a cache of created tables and a collection of OLAP data, so you can look at each other when you connect from a local grid. OLAP-data, copied into the table, can be handled, on the site http://everest.ua it is described in detail.

To open the autonomous cube of the data, I first open the OLAP table. Place the cursor near the created tables and click on the OLAP Tools button of the contextual tab Parameters (Tools), go to the group of contextual tabs of the Robot with the created tables (PivotTable Tools). Viberit Offline OLAP mode (small 9.8).

Small. 9.8. Part of the autonomous Danih cube

On the screen there will be a dialog box showing the parameters of the stand-alone OLAP data cube. Click on the buttons Create an offline data file (Create Offline Data File). We launched the danih cube file mister. Press the Next button to continue the procedure.

A collection of it is necessary to specify the size and size, as it is included in the cube of tributes. The dialogue window needs to vibrate data, as it can be imported from the OLAP database. The idea of ​​\ u200b \ u200bpolagє is that you will be deprived of the space, as it is necessary to write the computer from the local hedge. If you have more space, you can say that there is more space for an autonomous cube.

Press the Dal button, go to the offensive dialogue window maystri. In a new way, you can deny the ability to use a member or elements of the given, which do not turn on until the cube. Zokrema, you don’t know the world of Internet Sales-Extended Amount; Throwing off the great priest in order to those who do not import the item and use the memory on the hard disk.

At the end of the stage, it is necessary to create a tribute to the cube. Our vipad will have a cube file named MyOfflineCube.cub and will be added to the Work folder.

Files kubiv danih mayut expansion .cub

In a dozen hour Excel saves the autonomous cube of tributes from the signified papa. To protest yogo, two click the file, before the automatic generation of an Excel workbook, like revealing a table, tied with a vibrated cube of dues. For the stem, you can expand the autonomous cube of the given middle of the networked coristors, which can be applied to the mode of the connected local mesh.

When connecting to a local grid, you can view a stand-alone file of a cube of data and innovations, as well as a view of a table of data. The main principle is to say that the autonomous cube is given to stagnate only for the robot when it is connected to the local hedge, but it is necessary to update it. After the renovation of the autonomous cube, we gave it to the razryvu z'єdnannya prizvede to the slaughter.

Robot with autonomous cube files

an offline cube file (.cub) zberіgaє data from the OLAP cube form (Online Analytical Processing). These data can be part of the OLAP database on OLAP servers, or they can be installed independently from the OLAP database. Vikorist the offline cube file, so that the robot can be promoted with the help of the built tables and the built diagrams, if the server is unavailable or if you are connected from the grid.

Note of schodo bezpeka: Be respectful when you submit an autonomous cube file to avenge confidential or special data. To replace the cube file, it is recommended to save the data in the bottom, so that you can get keruvati access to the data for the additional function of keruvannya rights. Schob otrimati dodatkovі vіdomostі, div. Managing access rights to Office tributes.

After an hour of robotics with the help of the generated tables or the generated diagrams, based on the output data of the OLAP server, it is possible, with the help of the master of the autonomous cube, to copy the output data into the file of the autonomous cube on the computers. For the installation of these offline files, it is necessary that the OLAP provider has provided such a possibility, for example, MSOLAP from Microsoft SQL Server Analysis Services installed on computers.

Note: File and file registrations in stand-alone cubes from Microsoft SQL Server Analysis Services are governed by terms of licenses Microsoft installations SQL Server. Check out the latest information about the licensed version of SQL Server.

A robot with a maister of an autonomous cube

To root the file of the autonomous cube, you can vibrate the data base at the OLAP data base for the additional master of the autonomous cube, and then save the data base. You do not need to include all the fields included before the file, as well as select from them and the data fields available in the OLAP database. Save the file as a minimum, you can see the loss of the data, which may appear at the sight. You can omit all types and for more types of types, and you can also turn on the views about the lower level and elements Verkhnyaya Rivnya, which does not need to be displayed. For all elements, such as include, the power fields are available in the database, for these elements can also be stored in an offline file.

Transferring tributes to offline mode and turning them on

For a whole collection of information, it is necessary to open the built tables or the built diagrams, which is based on the data server, and then open the file of the autonomous cube for the call. If you want to, you can switch between the database of the given server and the standalone file at any hour. For example, if you are looking for a portable computer for robots in home and video trips, and then again connect the computer to the net.

Bottom, the main lines are pointed, like a visonati for autonomous robots with data, and then again transfer the data to the Internet.

Create or open a table or read the generated diagrams based on OLAP data, before you want to restore offline access.

Creation of a stand-alone cube file on a computer. In razdіlі Opening a file of an autonomous cube from the database of an OLAP server(Lower in the tsy statty).

Connecting from a grid and a robot with a file of an autonomous cube.

Connect to the fence and re-connect the cube file offline. Get to know the razdіlom Reconnecting the offline cube file to the OLAP server database(lower, at the tsy statty).

Updating the offline cube file with the new data and then redoing the offline cube file. Get to know the razdіlom Updating and redirecting an offline cube file(Lower in the tsy statty).

BLOG

Tilki yakisnі post

Also Excel spreadsheets and OLAP cubes

Marvel at video to statistics:

OLAP- Tse English. online analytical processing I’m just simple - a schema with rich data (Cube), even simpler - just a database of data, from which you can edit the data in Excel and analyze it for the additional Excel tool - Creation of tables.

Established tables- tse interface for displaying large amounts of money. In other words - special view a table, with the help of those who can make a sound.

Schob bulo zrozumіlo, sometimes "Zvychaynu table" zі "Established table"

Click on the table:

The table is compiled:

Main insight Compiled the table- the price of the window " List of fields in the table”, For whom you can select the required fields and trim the table automatically!

Yak koristuvatisya

View Excel file, such as connections to OLAP-cube, for example "BIWEB":

Now does it mean yak tsim koristuvatisya?

Drag the fields, trim them out, for example, the following table:

« Plus signs»Allow details to be heard. At the same time, the whole application of the "Brand" is detailed to "Quick names", and "Kvartal" to "Misyats", tobto. So:

Analytical functions for Excel (functions of cubes)

Microsoft has consistently added to Excel new capabilities for analyzing and visualizing data. A robot with Excel information can be represented at the viewer of apparently independent three balls:

• "Correct" organization of the given data
• mathematics (logic) processing of danikh
• tribute

Small. 1. Analysis of the data in Excel: a) of the data; b) the world in Power Pivot; c) dashboard; click on the new right button to change the image click on the right button View a picture in a new contribution

Add a note to Word format or pdf, add in Excel format

Functions of cubes and tables

We will forgive naybіlsh і for a second hour we will present the tributes to the tables. The stench can be prompted on the basis of tributes, to take revenge: a) on Excel sheets; b) OLAP cubes or c) Power Pivot models. In the last two vipadkakh, surrounded by the created tables, you can use the analytical functions (function of cubes) for the formulation of the sound on the Excel archives. The tables are simple. The functions of the cubes are folded, a little more nagging, especially in the decorated sounds, so the stench is widespread in the dashboards.

The given wiklade is applied to cube formulas and tables based on the Power Pivot model and in some versions on the basis of OLAP cubes.

A simple way to trim the function of cubes

If (yaksho) you have repaired the VBA code, then you know that it is simpler to correct the code, and write it to the macro. The given code can be edited, added to cycles, conversions and in. Similarly, it is simpler to edit the set of functions of cubes, reworking the table (Fig. 2). Stand on the be-yak comic book of made tables, go to the tab Analysis, click on the buttons Cautie OLAP, і natisnіt Convert to formulas.

Small. 2. Revision of the created tables on the set of functions of the cube

Take care of the numbers, and the price is not meaningful, but the formulas that match the data from the Power Pivot models (Fig. 3). You can see the table that has appeared, you can see it. In addition, you can see that insert rows that are in the middle of the tables. Zrіz zalishivya, i vіn pouring data into the tables. When new data is available, the number of tables can also be updated.

Small. 3. Table based on cubic formulas

CUBE VALUE () function

Tse, mabut, is the main function of cubes. Won equivalent of the region Value created tables. CUBE VITAGU data from the cube or the Power Pivot model, and depicts the posture with a built-in table. Tse means that you are not interconnected between the established tables, and you can use the number of possibilities.

Writing the formula "from scratch"

You are not obligated to rework and I am ready to create a table. You can write down the cube formula "from zero". For example, the following formula has been introduced into the C10 marketplace (Fig. 4):

Small. 4. CUBE-VALUE function in the С10 commercial turnover of sales of bicycles for all the rocks, as in the information tables

A little trick. It’s easy to read the formulas of cubes, bazhano, and it’s just one argument for the skin row. You can change Windows Excel... Click on the icon Gornuti at Vіkno, which is located at the upper right codend of the screen. Then adjust the size of the window horizontally. Alternative option- transfer the text of the formula to a new row with primus. For a row of formulas, put the cursor there, if you want to change the transfer and press Alt + Enter.

Small. 5. Sign up

The syntax for the CUBEVALUE () function

The Excel complement is absolutely accurate and absolutely marnous for pohatkivts:

CUBE (connection; [viraz_element1]; [viraz_element2]; ...)

Connection- Obov'yazkovy argument; text row, which represents the connection to the cube.

Viraz_element- Non-binding argument; a text row, which is a bagatovimirny viraz, which is a rotation element of a cube's cortege. In addition, the "viraz_element" can be without power, assigned for the additional function of CUBE. Vikoristovyte "viraz_elementa" yak zr_z, so that you can use a part of the cube, for which it is necessary to rotate the aggregate value. Even though the argument of "viraz_element" is not indicated to the world, the vicoristvatie there is a world, it is given for the arguments for the whole cube.

Persh, go to the explanation of the syntax of the CUBE VALUE function, a couple of words about cubes, models danih, that riddle tuples.

Deyakі vіdomostі about cubes OLAP and models danih Power Pivot

Kubi Danih OLAP ( O n l ine A nalytical P rokssing - operative analysis of the data) they were broken up specially for the analytical processing of that quick data from them. Reveal the trivial space, de along the axes laid down for an hour, place that comrade (Fig. 5a). At universities, there are such coordinate networks for new visits: sales, arrival, vitrati, number of sold units and in. Now it’s obvious that we’ve seen dozens, or hundreds… and even more often. Tse will be a large OLAP cube. The setting, adjusting and adjusting to the current stand of OLAP cubes - on the right, IT-fahivts.

Small. 5a. OLAP trivial cube

Analytical Excel formulas (cubic formulas) read the names of the axes (for example, Hour), name the elements in the cich axes (serpen, spring), the meaning of the entry on the coordinates. This structure itself allows us to create tables based on cubes and formulas for cubes that are too dull, and for food consumption. Building tables on the basis of Excel sheets do not come in viciously, because the stench is not so nasty with the analysis of the data.

Power Pivot is apparently a new feature of Microsoft. The price is added to Excel and a part of the square with a single interface. Power Pivot vastly outperforms standard spreadsheets in its capabilities. At the same time, the distribution of cubes in Power Pivot is remarkably simple, and at the same time it does not require the participation of IT-functions. Microsoft realized its own extinguished: "Business-analytics - in masi!". I want Power Pivot models not є by 100% cubes, you can also talk about them, like about cubes (presentation of the div. Introductory course by Mark Moore. Power Pivot and more about Rob Call. DAX formulas for Power Pivot).

The main components of the cube are tse vimiri, ієrarchії, рівні, elements (or members; English members) and come in (measures). Vimiryuvannya - the main characteristic of the analyzed tributes. For example, category of goods, period of an hour, geography of sales. Vimir is a set that can be placed on one of the axes of the created table. Skin vimir is a unique value including an element that will aggregate all elements of a vimir.

Vimіryuvannya prompted on the basis ієраrhії... For example, the category of goods can be broken into categories, further - on the model, and nareshty - on the names of the goods (Fig.5b). The architecture allows the creation of data and analysis on the old structures of the structure. At prikі ієrarrhіya Category including 4 rivnya.

Elementi(near the limbs) are present in all rіvnya. For example, in the category є chotiri items: Accessories, Bikes, Clothing, Components. Інші rіvnі mayut elements.

Come in- the value, which is calculated, for example, sales obsyag. Miri in cubes is taken from the vlasnoe vimiri, which is called (div. Below Fig. 9). Come on in, mum ієrarkhіy. The skin world of rozrakhovu and zberig meaning for all vimiryuvan and all elements, and grow dearly because of the fact that elements vimiryuvane are supported on the axis. It seems like the coordinates are given, or the context of the filter. For example, in Fig. 5a for a small skin cube, it grows to the same level - Pributok. And the meaning is to turn around in the world, to lie in coordinates. On the right, on little 5a, it is shown that Pributok (at three coordinates) across Moscow in zhovtni on apples = 63,000 UAH. The world can be treated, as one from vimiriv. For example, in Fig. 5a replace axle Goods, rozmіstiti віс Come in with elements About sale, Arrival, Single units sold... Todi kozhen oseredok i will be meaningful, for example, Moscow, spring, obsyag sale.

Tuple- a number of elements rіznykh vimіrіv, scho set the coordinates along the axes of the cube, in the same world. For example, in Fig. 5a Tuple= Moscow, zhovten, yabluka. Also a valid cortege is Perm, Yabluka. Another one - an apple, a serpen. Vimiri, they didn’t enter the cortege, they were implicitly present in the new one, but represented by a member of the delegates. With such a rank, the middle of the vast expanse depends on recruitment coordinates, navigate where they are at the cortege omitted. It is not possible to include two elements of the same vimir before the tuple, not to allow the syntax. For example, the unacceptable motorcade Moscow and Perm, Yabluka. In order to realize such a bagatovimirny viraz, there are two corteges: Moscow і apple + Perm і apple.

Set of elements- A number of elements of one vimiryuvannya. For example, an apple and a pear. Number of corteges- a number of corteges, leather from which are stored from the same vimirs at the same time. For example, a set of two corteges: Moscow, Yabluka and Perm, bananas.

Auto completion for help

Let's turn to the syntax of the CUBE VALUE function. Skoristaєmosya auto-completion. Read the introduced formulas in the comic:

Excel will propose all available in the Excel plug-in book:

Small. 6. The connection to the Power Pivot model should be called ThisWorkbookDataModel

Small. 7. Connection to cubes

Progressively introduced formulas (in our vipadku for model danih):

Auto-completion proponed all available tables and enter the models:

Small. 8. Available items of the first level - the name of the tables and the set of entries (seen)

Vibrate pictogram Measures... Place the speck:

CUBEVALUE ("ThisWorkbookDataModel"; ".

Auto-completion proponen all available come in:

Small. 9. Accessibility of elements of another level in the set of entries

Vibe the world. Give the paws, closing the bow, press the Enter key.

CUBEVALUE ("ThisWorkbookDataModel"; ".")

Small. 10. CUBE VALUE formula in Excel

With a similar rank, you can add the third argument to the formula:

VBA in Excel Object Excel.PivotTable and Robot with Build Tables and OLAP Cubes in Excel

10.8 Robot with data tables (PivotTable object)

About'єkt Excel.PivotTable, robot software with the creation of tables and OLAP cubes in Excel using VBA, the PivotCache object, the root of the layout of the created tables

The process of robotics of large enterprises accumulates raw data about the activity. For example, for a commercial enterprise, you can accumulate data about the sales of goods - for leather purchases of an okremo, for enterprises stylized sound- statistics on new options base stations etc. Most often, the management of the enterprise needs analytical information to generate from the urahuvannya unproblematic - for example, to protect the appearance of the skin type of goods in the income of the enterprise and the quality of the service in the zones of the given station. It is even more important to consider the lack of information about such types of information: it is necessary to use even more foldable SQL-power, as it is possible to display more and often use flowing robots. That is why all parts of the non-crumbled data are created from the collection of archiv dans - the Data Warehouse, and then - from the OLAP cube, which is even more handy for interactive analysis. It is simpler to display OLAP cubes as large and small tables, in which there are replacements of standard two types (hundreds of rows, such as extraordinary tables), which may be even more rich. Call for a description of vimiryuvan in cuba vikoristovuyutsya term "in rozrizi". For example, marketing may need information from hourly sales, from regional sales, from product types, from sales channels to sales. For additional cubes (as per standard SQL feeds), you can simply cut off some of these types of products sold in the fourth quarter of the last year in Pivnichno-Zakhі through a regular regional distribution.

Obviously, these cubes can not be installed at the ancient databases. For robots with OLAP cubes, specialization is required software products... At the same time, from SQL Server, a Microsoft OLAP database is supplied, which is called Analysis Services. Є OLAP solution from Oracle, IBM, Sybase, etc.

For robots with such Excel cubes, there is a special client. Russian wine to be called The table has been set(On the graphical screen, available through the menu Dani -> The table has been set), and in English - Pivot table... Apparently, the object that represents the client is called PivotTable. It is necessary to note that it’s not just OLAP cubes, but the extraordinary data in Excel spreadsheets, or databases, and a little bit of power.

The table and the PivotTable object - the whole program of the Panorama Software company, such as Microsoft add-ons and integration into Excel - has been created. The robot with the PivotTable object can be easily seen from the robots with the Excel objects. Zdogadatsya, it’s necessary to get through, it’s often not easy. It is recommended to use the macro recorder actively for rejection of cues. At the same hour before the hour, the robots often have to see the same repeated operations with the help of tables, so automation is necessary in many situations.

Yak viglyadaє program of the robot with a set table?

The first thing we need to do is to open the PivotCache object, which is a set of records that are taken from the OLAP dzherel. Another clever PivotCache object can be derived from QueryTable. For the skin object PivotTable, you can choose only one PivotCache object. For the PivotCache object, use the additional Add () method of the PivotCaches collection:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add (xlExternal)

PivotCaches - a standard collection of methods, such as merit for a presentation, in which you can name only the Add () method. Tsey method of acceptance two parameters:

• SourceType- obovazkovy viznachaє type of dzherel danih for the created table. You can set the PivotTable stem on the basis of the Excel range, the data from the data base, the most popular data base, the other PivotTable, etc. In practice, make use of OLAP є sens vikoristovuvati if you don’t have to, if you have a lot of money, it’s probably necessary to have special calls (for example, Microsoft Analysis Services). The xlExternal value is selected for the situation.
• SourceData- common for all types of messages, but quiet, if the value of the first parameter is xlExternal. Vlasne, based on that range of data, based on how and where the PivotTable is. Name acceptє object Range.

Step on the job - adjust the parameters of the PivotCache object. Like moving before, the whole object is still nagadu QueryTable, and the set of powers and methods is even similar. Deyakіvіdіvіvіlіvіvіstі and methods:

• ADOConnection- You can turn the ADO Connection object, which is automatically turned off for connecting the new danih. Get victorious for dodatkovogo nalashtuvannya authorities connected
• Connection- it’s just like that, as the power of the QueryTable object is the same. You can take a row of connections, a ready-made Recordset object, text file, Web feed. file Microsoft Query. Most often it’s an hour of robots from OLAP to register a row of connections without a middle (some scraps of the Recordset object, for example, the tribute to the great senses, it’s not enough for the great sense of the OLAP - make sure that OLAP is practically available if it’s readable). For example, setting up the power system for connecting to the Foodmart database (the main database of Analysis Services) on the LONDON servers can be viewed as follows:

PC1.Connection = "OLEDB; Provider = MSOLAP.2; Data Source = LONDON1; Initial Catalog = FoodMart 2000"

• power CommandTypeі CommandText so to describe the type of command itself, how to be transferred to the database server, and the text of the command itself. For example, if you want to turn on the Sales cube and revert it to the cache on the client, you can vikoristovuvati the code of the form

• power LocalConnection allow to connect to the local cube (* .cub file), opened by Excel. Obviously, such files for robots with "rogue" liabilities are not recommended anymore - only for prototyping, etc.
• power MemoryUsed turn number operational memory, scho vikoristovutsya PivotCache. PivotTable based on the PivotCache is not set yet, nor is it open, turned 0. You can choose to change it if your program is working on weak clients.
• power OLAP turn True as PivotCache is connected to the OLAP server.
• OptimizeCache- You can optimize the cache structure. Pochatkovo zavantazhennya danikh is carried out more, a little less the speed of the robot and more age. For dzherel OLE DB is not practicalє.

The power of the PivotCache object is based on the analogous power of the QueryTable object, and it will not be seen here.

The head method of the PivotCache object is the CreatePivotTable () method. For additional help, an offensive stage is carried out - the opening of the created table (the PivotTable object). Tsey method of accepting chotiri parameters:

• TableDestination- One obovisky parameter. Accept Range, the table will be placed at the top of the lines.
• TableName- Ім'я created tables. It is not specified, it is automatically generated in the form of "Made Table1".
• ReadData- if you set it to True, the whole cube will be automatically put into the cache. This parameter will need to be taken care of even more, because there may be a lot of wrong storage on the client's side.
• DefaultVersion- tsya powerliness will not interfere. Allows you to see a version of the created tables. The best version will be awarded for the vicarious candidates.

The setting of the table in the first center of the first arkush of the book can be seen as follows:

PC1.CreatePivotTable Range ("A1")

The table is set up at us, protest immediately after the start is empty. Some regions have been transferred to them, in which it is possible to change the fields from the dzherel List of fields in the table- it will be displayed automatically, for an additional button Layout on the rest of the screen of the master of the list of tables):

• region of hundred- they have to take revenge on those vimiri ("rozriz", in which to analyze the data), members of which are less;
• row area- those vimiri, members of which are larger;
• side area- tі vimіryuvannya, for which it is necessary it is not necessary to carry out filtering (for example, to show the data only for such a region, or only for such a rіk);
• Danih area- Vlasne, the central part of the table. Tі numerical danі (for example, the sum of sale), like mi analizєmo.

Pay for the koristuvach, it’s important to place the elements correctly in all areas. Besides, it can be a trivial hour. It is often necessary for the development of data from the production tables by the program rank. Qia operation will help you with the CubeField object. The head of the power of the whole object is Orientation, it’s a matter of fact, if there’s a field. For example, we are helping vimir Customers in the region of stovptsiv:

PT1.CubeFields ("") .Orientation = xlColumnField

Poten - vimir Time to the area of ​​rows:

PT1.CubeFields ("") .Orientation = xlRowField

Poten - vimir Product to the sideline area:

PT1.CubeFields ("") .Orientation = xlPageField

I nareshty, indicator (numerical data for analysis) Unit Sales:

PT1.CubeFields ("."). Orientation = xlDataField