BI systems. BI systems and their functions Connecting power bi to 1c

The material was prepared by specialists of the company "Abis Soft"

An analytical platform consisting of a number of components (client, server, “Studio configurator”) and focused on solving business problems using Data Mining methods. Based on its own developments and its own analytical engine. An interesting feature of the product is the possibility of multi-stage information transformations, which are described in design mode. Custom OLAP analysis is not the main focus, but is also present. To a greater extent, the product is focused on the use of mathematical methods to solve problems such as sales forecasting, inventory optimization, and customer base analysis. Industry solutions are also offered.

To integrate with 1C, the product uses a COM adapter. Because This technology works slowly, but on large databases it is proposed to use it to transfer changes, and additionally configure the initial unloading using SQL methods.

Note also that this product uses its own client software for generating reports and analytical calculations.

S.M.A.R.T. (system for multidimensional analysis of relational tables) is another well-known analytical development. The product is built on the basis of its own server and client software; it can also work on a three-level model through a Web module. It is possible to create autonomous cubes.

In addition to the OLAP module, the product includes the following modules: “Econometrics”, “Combination Rule”, “Sales Maps”, “Decision Trees”, “Genetic Algorithms”, etc. Areas of use are traditional for Data Mining: analysis of the customer base, calculations of ideal warehouse balances, identifying sales trends, etc.

The product is positioned as a system for large businesses and has experience in integration with SAP, Oracle and other systems. As for using 1C as a data source, no special tools are offered, but experience in similar work is stated.

BIX offers a comprehensive solution based on the Microsoft SQL Sever platform. Because MS SQL Sever, as a rule, is used as a server for 1C databases, it may turn out that the organization already has an OLAP server product. True, it must be a version no lower than Standard, because Minor versions do not include Analysis Services.

The product allows you to build cubes on documents and their tabular parts, with directories and enumerations as analytics. For directories, their attributes can be downloaded.

To work with OLAP cubes, you can use Excel or any other OLAP client. In addition to building cubes, it is proposed to use MS SQL Reporting Service tools to generate reports.

The product "INTALEV: Corporate Analytics for 1C" uses Microsoft SQL Server as an OLAP server. All settings are carried out in the 1C:Enterprise mode visually and without programming, and all stages of creating an OLAP database are fully automated.

At the same time, the capabilities of the product are quite wide: cubes are formed using documents, directories, accumulation registers and accounting registers. Several fact tables can be collected into one cube with common dimensions, which allows them to be analyzed together. For dimensions, you can specify analysis attributes, both primitive types and reference ones (the “snowflake” topology is implemented). For typical configurations, you can use ready-made analytical models.

The product configures security roles and restrictions. Access is configured on an object-by-object basis. Role permissions are merged. This allows you to set up, for example, access to accounting data using subaccount permissions, which 1C does not allow.

The product “INTALEV: Corporate Analytics” is easy to implement and can be recommended to those who want to quickly and at minimal cost obtain an OLAP system for 1C 8.1.

Product Comparison

In order to compare these products, we invited the companies representing them to evaluate the possibility of building an OLAP database to solve the following problems:

1. Analysis of product sales (50 million records in the table),

2. Analysis of warehouse stocks with detail down to days,

3. Analysis of accounting data (30 million entries in the table of entries in the accounting register, number of analytics 40) with the ability to build turnover and chess sheets.

At the same time, we required the ability to analyze by attributes and role security. Another condition is that 50 people must work with the base.

Representatives of all surveyed companies confirmed the feasibility of these tasks in full. Their responses regarding timing and cost are summarized in table form.

* Microsoft SQL Server Standard Edition for 50 users

** Microsoft Office 2007 Standard Russian OLP NL

Creating an OLAP circuit for quick analysis of 1C data is no longer a problem today. From the point of view of the authors of the review, it makes sense to choose an OLAP product based on the required functionality, the capabilities of your own IT team and the acceptable budget.

It makes sense to choose products from BaseGroup Labs and M-Logix in cases where OLAP analysis is not enough and the organization also wants to use the developments of these companies in the field of Data Mining. Also, these products have mechanisms for consolidating heterogeneous sources, so if there is a desire to build a corporate repository that combines information from 1C 8.1, 1C 7.7 databases or, for example, Oracle databases, we recommend considering these products, among others.

When choosing between Deductor and S.M.A.R.T. It makes sense to pay attention to the difference in approaches to product implementation. In the first case, implementation is carried out by consultants from the OLAP vendor, in the second - by its own employees after training.

The advantage of solutions on the Microsoft platform (Bix BI and INTALEV: Corporate Analytics for 1C) is simpler integration with other products of this supplier, for example with SharePoint, which allows you to create a DashBoard with KPIs calculated using OLAP. They can also use Data Mining Analysis Service. Bix's solution requires more setup, but is closer to a custom project.

It should be noted that if an organization already uses Microsoft SQL Server and Excel, then products from Bix and INTALEV allow you to limit yourself to only purchasing additional licenses for these products, saving on server software.

Let me make a reservation right away that this article does not pretend to be a complete guide. Rather, it is a personal experience in visualizing management data.

The concept of “analytics” has a large number of definitions. One of them: “Analytics is the process of obtaining knowledge and new data based on facts.” From a business point of view, analytics allows you to obtain a lot of new information from the huge amount of data that is generated during the conduct of an enterprise.

Visualization of this data allows you to simplify the perception process.

However, not all necessary data sources are always included in a single accounting system. The budget can be generated in Excel, information on shipments and requests from clients can be available in test formats, website visitor metrics can be accessed through a web portal, etc. How can all this be linked into a single “enterprise control panel”? Microsoft Power BI can help solve this problem.

The process of connecting 1C as a data source and setting up relationships with other information systems is described below.

We will need:

• 1C: Trade Management, published and accessible from the Internet - we will use 1C deployed in Azure.
• Current Microsoft Power BI subscription.
• An additional data source that we will connect with data from 1C is an Excel file.

Publication of the 1C information base

Before we make the data available to Power BI, we need to provide web access to the infobase, or more precisely, to the OData interface. To do this, we will publish an information base with authentication through the operating system.

We launch 1C in “Configurator” mode with local administrator rights and go to Administration - publishing a web extension. Enable authorization via Windows.

Now you need to launch 1C:Enterprise and go to “All functions” - “Processing” - “Setting up an automatic REST service”.

In this processing, you just need to mark the desired objects and click the “Save and Close” button. This processing is standard and is included in all standard 1C configurations.

We search for and mark in the list the data that we plan to analyze in Power BI. For example, Directories - Partners, Documents - Customer Orders, etc.

Public-host-name - external DNS name or server IP address.
BaseName - the name of the infobase that we used when publishing.

Document_Realization of Goods and Services_Goods - data provided from 1C. These can be directories, registers, etc.

Connecting sources in Power BI

Now let's launch Power BI Desktop. The program is available through the portal https://powerbi.microsoft.com/ after successful registration and authorization.

Download, install and launch.

Let's connect the necessary data sources. To do this, click “Connect Data” on the toolbar and select the OData channel:

By clicking the “Ok” button, you must specify authorization data. Next we will see some of the obtained data.

Clicking the "Edit" button will take us to the query editor, where we can modernize the data and add the necessary calculations using the DAX language.

After adding multiple sources, you need to set up relationships between the data. To do this, go to the Relationship View mode.

Adding other sources

Similarly, we can add other data sources, such as an Excel file. This can also be done through the Get data menu.

As other sources we can use a huge number of options:

After connecting all the sources and creating connections between the data, you can begin to visualize the information.

Data visualization

In the main Power BI window, select a visualization element in the toolbar.

By dragging the required fields into the element parameters, we get a visual representation of the data.

After creating the required report, you can proceed to publishing it. To do this, simply click the “Publish” button and select the workspace in which you want to place the report.

When the publishing process is completed, the system will provide a link to view the report in a browser. Now the report can be made available to everyone or only to a certain circle of people.

I would like to add that this tool allowed our company to gain a range of knowledge about us and our clients. An example would be understanding the geography of clients, identifying “golden” partners, overworked employees, etc.

We continue our epic with the mobile world. And today I want to talk about this product Microsoft Power BI.

What is it Microsoft Power BI ?

And it is a business intelligence service for sharing, managing, and using data queries and Excel workbooks that contain data queries, data models, and reports.

Naturally, since there is a place where we write reports, it means there must be a place where we watch them. And there are several such places:

• 1. The very environment where we write them. Well, this is logical, but it is only useful for us;
• 2. Web interface;
• 3. iOS devices;
• 4. Windows devices can be installed in metro style. Those who have Win 8/8.1. It looks really futuristic, especially on large monitors.
• 5. Android devices. "Android app is on the way." We wait.

Operation logic

Let me first explain the logic of the work, so that later it will be clearer why we do it this way and not otherwise. The entire development can be divided into several stages:

• 1. Account registration. You can see the prices, but there is also a free version, so you can test it. Of course, for the West, the price of $10 per user per month is a trifle, but for our countries it is still a lot. Although, again, even if we take a company of 500 people, how many employees will need such functionality?
• When registering, keep one thing in mind - it is done at the bottom left, where you indicate your email. If you go to Sign in - there is only authorization.
• 2. You need to launch the Power BI Designer configurator, the one you downloaded earlier.
• 3. In the configurator we do everything we want, create the appearance, etc., then save the project file.
• 4. Now we need to pull the project file into our account so that the changes are saved there.
• 5. By logging in to your account on a mobile device or in the client for Windows - your settings are picked up.
• 6. Enjoy.

Working with the configurator

Now let's figure out how to build reports and how to obtain data?

Receiving data

When you first open it, you will be greeted by an assistant. Let's close it. And we remove everything unnecessary. We should only be left with a window like this:

For now, we will not go into details about the purpose of all the buttons. Let's try to answer the question - how do we get the data?

In pursuit of an answer, we must click the “Get Data” button - the one at the top left, we will be greeted with the following dialogue:

As you can see, the list of potential sources is simply huge. However, we will take the option of obtaining data using OData, since we have it in 1C, but we usually do not use it. Let's apply it here. You can read more about it here:

• 1. ITS

The first thing we need to do is, of course, find a donor, i.e. the database from which we will drag all this data.

There are several options:

• 1. Set up your web server and pull the data from there. But this option is inconvenient for training, since I will not give my server public access.
• 2. Go to the 1C website , where there are demo configurations, and try your luck there, in the hope that at least one of them will open OData . Make sure that this is not the case and close the site.
• 3. Go to the English-language 1C website , look for some kind of database there that has access through OData. And find one.

So, let’s focus on UNF; you can check that OData is available in this way:

If you see a list of metadata there, then everything is ok.

Now let's check the directory, for example, the product directory:

As you can see, we simply added the name of the directory and the designation that this is a directory to the first address; in Russian-language configurators it would look like this:

When we go to this URL, we should see xml that will describe each element of the directory, however, it all depends on the browser, FireFox displays this data as an RSS feed. But this does not change the essence.

If, when connecting to your database, you received a message that the entity was not found, then this is for you.

Let's move on. We need to set some kind of task so that there is an end goal. However, until we get acquainted with the functionality, it is difficult. Therefore, I propose to limit ourselves to one elementary thing for now - let’s display sales by product and contractor. Well, maybe something else. For SKD, this is a simple task, but there are some nuances.

So, first of all, we need to understand where sales are stored. They are precisely stored in the accumulation register, and this register is designated in OData as AccumulationRegister. So we are looking for them. Basic knowledge of English will help us with this. And here is our register AccumulationRegister_Sales. We check:

If we see xml, then everything is ok.

Now we go to our BI configurator, select the line with OData in it, the one that I highlighted in yellow in the screenshot above and click Connect, then fill in everything that is needed:

Now we will be greeted by the following window:

Here we are informed that we do not have authorization specified. It must be indicated. Please note that you can specify authorization both for the domain as a whole and for a specific address; I couldn’t find how to specify authorization for a specific database, but this is not important to us now.

Specify the login without a password and click connect. Data loading should begin. After it, you will have fields on the left:

The available fields are displayed at the top right, currently there are only two.

Bottom left - you switch between reports and the data from which the reports are built. In our case, these are requests.

At the top left are pages with reports, i.e. on one page (a la 1C layout) there can be a lot of different data.

Now we need to figure out why we only have two fields, since we clearly have more dimensions, details, and resources. Let's move on to queries:

This is the window for working with requests. All requests that you add will be available at the top left.

In the top center is the connection line. In essence, this is the text of the request; selections, paths, etc. are indicated here.

On the right we have the name of the request and the sequence of steps.

We'll talk a little about the sequence. Its essence comes down to the fact that we describe in it step by step the rules that we want to apply to our data. Those. if you want to delete a column, then you need to indicate at what time - this will be step number 2, or number 10. It will become clearer later.

So we only have three columns. What is this connected with? Let's take a close look at the xml that 1C gives us and find that place , where the list begins, i.e. when OData What is returned to us is not a line-by-line representation of the register, as we are used to, but information about the recorder is first displayed, and then its movements are embedded in it.

Well, we are interested in these lines. Let's now select any element in the middle column and see the following message below:

We should see the meaning, but we won’t see it. In order to see, we need to expand the array into strings. To do this, click the button at the top right of the middle column header, I highlighted it in yellow on the screenshot, and remember - we now have 18 lines, click:

Now we have 50 lines. Those. We expanded everything line by line, and previously it was grouped by registrar, in 1C terms.

But this doesn’t suit us, we don’t have enough columns, I think you guessed what needs to be done - click on that button in the header again:

But now we choose what we want to add, i.e. By analogy with a request in 1C, we select the fields with which we then want to operate:

And click OK:

Please note that the list of actions has been filled out on the right, i.e. in our case the actions are as follows:

• 1. To get data;
• 2. Expand data;
• 3. Expand data.

And immediately pay attention to the formula above.

Now we need to solve the problem, because we are not at all happy with the product name in the form of UID. I doubt that this view will be informative.

This means that we now need to display information on the product, let’s do this by combining the product and sales tables.

To do this, we add a new query, but to the product directory http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature, in the same way as sales, and rename these tables so as not to get confused :

Please note - I immediately applied several steps to the product data, namely:

• 1. Received data:
• = OData.Feed(“http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature”)
• 2. Removed extra columns:
• = Table.RemoveColumns(Source ,(“SKU”, “ChangeDate”, “WriteOffMethod”, “DescriptionFull”, “BusinessActivity_Key”, “Vendor_Key”, “Cell_Key”, “Comment”, “OrderCompletionTerm”, “TimeNorm”, “FixedCost”, “CountryOfOrigin_Key”, “PictureFile_Key”, “Parent_Key”, “DataVersion”))
• 3. I filtered the records and removed all groups:
• = Table.SelectRows(#”Removed Columns” , each ( = false))

Above are queries that can be inserted into the top line and thus not have to do it all manually. Pay attention to the structure of this whole thing - in the first step we simply receive data. In the second step, we indicate what we do with the data we received in the first step. On the third, we again indicate the name of the step to which we will apply the filtering rule.

Those. we must always indicate to which step we apply a particular rule.

• 1. It’s easy to take and make joins similar to the type of connections in 1C, when we join two tables.
• 2. Add the data we need from another to one of the tables.

We'll look at both of them. So. First we just do the join. To do this, go to the Reports tab (bottom left) and click the Manage button:

And we set up connections:

Well, finally, let's display any report... but no, it's still early. Let's rename the fields so that it is clear what is responsible for what. To do this, we return to queries and rename everything we need, simply by right-clicking on the column and Rename or twice on it. Don’t forget to double-check the table connections later.

Now let’s start building reports:

As you can see, the reports look very nice, especially the very idea that you can simply take a bunch of graphs and connect them with each other.

Now let's try to add product product groups here. In theory, it would be necessary to add another request with item groups and associate them with the NomenclatureGroup_Key field in the item request. However, we won't need this. Let's go to the very end, look at the last columns in the Nomenclature query.

And you will encounter an error, and this is most likely a bug of the platform on which this database is published, since on the latest platforms everything is ok. So test it yourself. But the point is that the names of nomenclature groups will appear here.

Transferring data to your account

After all this, we save the report we received. By the way, it is attached to the article. So you can download it from the attached files to this article.

Click Get Data, select Power BI Designer File and click Connect, then select the file and click Connect again.

Now let's go to the iOS application and see what it looks like there:

That's all.

Bottom line

In conclusion, I want to say only one thing - do not limit yourself only to the capabilities of 1C, because 1C is, firstly, a database. So beauty is for other decisions.

If you know OData well, then you can make this kind of report in a few minutes. And besides this - for simplicity of work and less load on the database, you can simply create a separate information register where you can dump all the necessary information at once, and with this solution you can take it and display it.

What is the advantage of using this solution? Everything is very simple: you can get the finished result in a couple of hours. Everything is free (of course, there are a number of limitations in the free version, but I think it will be enough for many people).

All you need to get started is install a web server, update the platform to 8.3.6 (it might work with 8.3.5, I haven’t tested it, the main thing is that there are no bugs in the platform), pull out the data and put them together.

Those. For a programmer this will take a few hours at most. And the result is excellent.

P.S. Don't forget about data security!

To create clear reports and update them in real time, modern businesses need a powerful service for processing information. In the comments to my previous ones, readers have repeatedly asked me to write a manual for working with Microsoft Power BI. In this article I will tell you how to get started with this platform.

Briefly about the capabilities of Microsoft Power BI

Power BI is a fairly powerful and free BI platform. Microsoft invests a lot of money in the development of this product, and therefore updates are often released that expand its capabilities. You can use the following versions of the program:

• Power BI Desktop designed for developing data models and reports;
• Power BI Service- an online analogue that specializes in monitoring and analyzing ready-made reports, so the possibilities for designing and working with data there are very limited.

As a rule, the desktop version is used as a designer, after which the developed file is published in Power BI Service. For further work, download Power BI Desktop from this link.

For Microsoft Windows 10 users, there is a separate version of Power BI Desktop, which you can find by following this link.

Where can I download data from?

Power BI has many built-in connectors to various services and databases, with the help of which you can load the desired set of data from various sources into the program in a matter of minutes, link them together and build consolidated reports and charts. At the time of writing, all available connectors are divided into four groups:

1. “File” group:

As you can see, you can import data into Power BI from the most well-known databases and services using a variety of file formats. After loading information from available sources into Power BI, you have great opportunities for cleaning and transforming data, due to the fact that the platform has quite powerful ETL functionality. Loadable tables can be edited, and calculated columns and measures can be created based on their columns - this way you can transform arrays of information into data sets with the structure necessary for building visualizations.

Having generated a set of data downloaded from various sources, many built-in visualization elements will become available to you:

• stacked bar chart;
• stacked histogram;
• bar chart with grouping;
• histogram with grouping;
• normalized bar chart;
• normalized histogram;
• schedule;
• area chart;
• stacked area chart;
• linear and stacked histogram;
• linear histogram and histogram with grouping;
• waterfall chart;
• scatter plot;
• pie chart;
• tree diagram;
• map;
• table;
• matrix;
• completed card;
• funnel;
• sensor;
• multi-line card;
• card;
• key performance indicator;
• slice;
• ring graph;
• r-script visual element (currently enabled in the program parameters).

All elements have a fairly wide range of settings aimed at changing the necessary parameters: color, background, name, borders, and so on. If the standard set is not enough for you, you can upload custom visual elements.