How to work with Microsoft Power BI - detailed guide. BI - systems Power bi report with 1c

I will make a reservation right away that this article does not claim to be a complete guide. Rather, it is a personal experience in management data visualization.

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 get a lot of new information from a huge amount of data that is formed during the conduct of an enterprise's activities.

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

At the same time, not always all the necessary data sources are included in a single accounting system. The budget can be formed in Excel, information on shipments and upon requests from customers can be available in test formats, metrics of site visitors - through a web portal, etc. How can all of this be tied into a single "enterprise control panel"? Microsoft Power BI can help you with this task.

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

We will need:

• 1C: Trade Management, published and accessible from the Internet - we will use 1C deployed in Azure.
• Active subscription to Microsoft Power BI.
• An additional data source that we will associate with data from 1C - let's take an Excel file.

1C information base publication

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

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

Now you need to start 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 typical 1C configurations.

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

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

Document_RealizationGoodsServices_Goods - data that is 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 run.

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 the authorization data. Next, we will see a part of the received data.

By clicking the "Modify" button, we will be taken to the query editor, where we can update the data and add the necessary calculations using the DAX language.

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

Adding other sources

In a similar way, we can add other data sources, such as an Excel file. This is also done through the Get data menu.

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

After connecting all the sources and creating links between the data, you can start visualizing the information.

Data visualization

In the main Power BI window, on the toolbar, select the visualization item.

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

After creating the required report, you can proceed to its publication. 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 over, 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 number of knowledge about us and our clients. An example is understanding the geography of customers, identifying "golden" partners, overworked employees, etc.

Microsoft Power BI is a suite of business intelligence tools for analyzing data and providing insight. Monitor your business from any device anywhere in the world in real time!

The Power BI concept is based on the following ideas:

1. Colorful modern graphics Modern technologies taking into account the latest trends in data analysis.		2. Self-BI Business users can self-analyze and create interactive dashboards without IT assistance.		3. Mobility Applications for mobile devices based on Windows, Android and iOS.
4. EVERYTHING AND IMMEDIATELY! Analyze all your data at once in a convenient format. Built-in connectors to most systems and services.		5. Presentation of data A large set of visual representations.		6. In the same language as you Just ask Power BI: “What are the margins for all projects in Q2 2018?”, And the system will immediately give an answer in the form of an interactive dashboard.
7. Single center of analysis You no longer need to search for information about the affairs of the company in different folders and files. All analytics are generated in ONE interactive interface.		8. Sharing Share all apps from any device.

Interactive (clickable) demo example

Get a demo

Power BI cost and functionality

Get a demo

Why do you need Power BI?

With Power BI, you can analyze all your company data, both cloud and on-premises. By using your imagination and using Power BI visual tools, you will create interactive reports in seconds in real time. The system will automatically fix problems with data formatting. With Power BI, you can connect SQL Server databases, Analysis Services models, and other data sources to the same dashboards in Power BI.

What knowledge do you need to work with Power BI?

After the implementation of the system, a business user does not need special knowledge to work effectively with data. A convenient, intuitive interface allows you to quickly and easily change or create analytics in the desired view.

Power BI Implementation Project

The BI team of First BIT is a team of industry experts with extensive experience in implementing projects on various platforms. We comprehensively cover all the client's needs as part of the implementation of the Power BI business intelligence system:

• Survey of your business processes
• Formation and coordination of requirements for the BI-system
• Connecting to a data source and consolidating them
• Building a data model and developing reporting
• Training your employees (business analysts and developers)
• Professional technological support for your company in the future

Examples of Power BI solutions

The material was prepared by the 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 ability to multi-stage information transformations, which are described in design mode. Free OLAP analysis is not a mainstream, but it 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-specific solutions are also offered.

To integrate with 1C, the product uses a COM adapter. Because Since this technology works slowly, 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 reporting and analytical calculations.

S.M.A.R.T. (Multivariate Relational Table Analysis System) is another well-known analytical development. The product is built on the basis of its own server and client software, and it can also operate on a three-tier model through a Web module. It is possible to create stand-alone cubes.

In addition to the OLAP module, the product includes 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 stock balances, identification of sales trends, etc.

The product is positioned as a system for large businesses and has experience in integrating with SAP, Oracle and other systems. As for the use of 1C as a data source, no special tools are offered, but the experience of such work is declared.

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 at least Standard version, since minor versions do not include Analysis Service.

The product allows you to build cubes on documents and their tabular sections, with reference books and enumerations as dimensions. For directories, their attributes can be unloaded.

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 INTALEV: Enterprise Analytics for 1C product 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 the OLAP database formation are fully automated.

At the same time, the product's capabilities are quite wide: cubes are formed according to documents, reference books, 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 roles and security restrictions. Access is configured on an object-by-object basis. Role permissions are merged. This allows you to configure, for example, access to accounting data on permissions for subconto, which 1C does not allow.

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

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 tasks:

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

2. Analysis of warehouse stocks with detailing to days,

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

At the same time, we demanded the ability to analyze by attributes and role-based 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 answers in terms of time and cost are summarized in the form of a table.

* Microsoft SQL Server Standard Edition for 50 users

** Microsoft Office 2007 Standard Russian OLP NL

The creation of an OLAP contour 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 wants to use also the developments of the companies' data 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 warehouse 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 of the OLAP provider, in the second - by its own employees after training.

The advantage of solutions based on the Microsoft platform (Bix BI and INTALEV: Corporate analytics for 1C) is easier integration with other products of this supplier, for example, with SharePoint, which allows you to create DashBoard with KPIs calculated using OLAP. They can also use the Data Mining Analysis Service. The Bix solution requires a lot of customization, but it is closer to an individual 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 only to purchasing additional licenses for these products, saving on server software.

Each manager was faced with reports in the form of an extensive table with figures of turnovers and totals, printed on paper. Sometimes it is several pages of A4 size, fastened with a stapler, sometimes A3 format speckled with a scanty font. Armed with a marker and a ruler, you can begin to understand the intricacies of rows and columns. Or send the contractor to redo the report after giving a long lecture about which metrics are really important (and in the next reporting period, you may want to see the same data in a different context). Both paths are at least not optimal.

An interesting option is to study reports generated using accounting IT products (1C is a prime example). In this case, you may encounter incomprehensible terminology and excessive data detail.

What if a decision requires quick access and correlation of data from marketing, warehouse and finance?

A comprehensive solution to these problems is the implementation of an analytical system, the tasks of which do not include accounting, but data analysis. Such a system serves to present information in a selective, filtered form, taking into account the requirements of a particular user, with the necessary detail.

An example of visual data visualization using BI

Complex information systems contain many functions and interfaces. As a rule, even a simple listing of their characteristics can take a lot of time. Therefore, before delving into a detailed study of the product, you need to check that the selected IT solution meets the main requirements. These unambiguously include:

1. Data analysis. The system should "be able" to make a selection from the database in a few mouse clicks, compare information by periods, determine the current level of indicators, create scenarios and make forecasts for the future.
2. Data visualization with all popular charts- histograms, pie charts, funnels, bubble, radar and so on. It is very desirable to quickly change the view of the diagram, as well as convenient access to their selection.
3. Collecting data from various sources. As a rule, the company has several information storages: various databases, spreadsheets, in some cases also downloads from third-party databases. For example, the following situation is possible: the corporate accounting system 1C uses SQL Server, the company's website stores information in a MySQL database, and employees actively use Excel files. The analytical system must connect and import all these types of data, and then operate them in a complex manner as a whole.
4. Access via internet browser. If you can use any device (PC, tablet, smartphone) to work with the application, this radically expands the workspace. Employees of the company will be able to work not only in the office, but also on business trips, on the road, at home - anywhere.
5. Customizable appearance. The more adaptive the software interface to the requirements of specific users, the more convenient it will be for them to use the system. The fewer mistakes they make, the faster they receive data, and the faster they complete their work tasks.
6. Differentiation of access rights. This is an obvious but critical aspect. Ideally, an IT solution should fully and literally reflect the entire authority structure of the organization.

Criteria for assessing the quality of an IT solution

From the point of view of the head of the organization-customer of the information system, it must meet several key requirements. It is more about the qualities of the system than about quality. Namely:

1. Fast implementation. The world situation is changing rapidly, and this process is only accelerating. Therefore, an important feature of modern IT products is their rapid implementation and the ability to modify in a fairly short time.

This problem is not acute when the accounting system model already includes BI services. Or the company can afford highly qualified specialists who create OLAP cubes and applications that can work with them.

Organizations with multiple accounting systems and, accordingly, data sources need IT solutions that are ready for use immediately after installation. Otherwise, the effectiveness of automation will have to be divided into the time and cost of implementing and configuring the software.

2. Ease of use and customization(revision for the needs of the customer). The further introduction of mobile devices into our lives and the ability to work remotely require software products to be easier to use. Setting filters and selecting analysts should preferably be done using a mouse or touch screen. Preparation and modification of reports - using various "constructors" that do not require knowledge of programming, but only familiarity with the program interface.

3. Power- instant response. Some IT solutions are architecture based on database queries that take time, sometimes very long. Systems that load data into RAM provide a tangible speed advantage. On the scale of the organization, taking into account the number of employees, departments, facilities, contractors, this is a very significant indicator.

4. Full-featured free client... To get acquainted with the capabilities of the information system, it is highly desirable to have access to all its functions at once, even before installation or implementation. Of course, on the part of a potential customer of an IT solution, it will be necessary to distract their employees to study and test work with the program, but compared to the costs of purchasing and a complete transition, this is really almost free.

Perhaps the greatest effect is given by observing the simplest and most commonplace rules. Everyone knows that preventing problems is much cheaper than eliminating their consequences. Here are some tips to ignore, in the truest sense of the word. Customers of IT solutions should check that they have:

1. Clear data storage regulations... Places for storing information of each type, names of documents, database tables from which information should be obtained should be approved. If the employees of the company miss something in the terms of reference, the program itself will not "guess", and this will lead at least to inaccuracies, and at most to errors in reports.
2. Ensuring data interconnection... The relationship is assumed to have a unique identifier, name for each accounting element. This is critical for combining multiple information streams and storages. Part of the data can be in the form of product names on the site, another part in an accounting program, in a warehouse, somewhere else. All SKUs and unique product codes must be the same or at least unambiguously matched according to the previously described rules.
3. Lack of duplicate information... A common mistake is the presence of duplicates in corporate information systems. Often, the same customer or vendor has a different name in different databases and files. Naturally, this leads to confusion and reduces the quality of analytics.
4. Fixing calculation formulas... It is logical that all indicators used in data analysis should be unified - that is, they should completely coincide for samples and situations of the same type. If this condition is not met, the advantages of a single information space and comprehensive analysis will be devalued.

The use of analytical systems allows you to see the business as a whole. The better the visualization is developed in the IT solution, the less unnecessary, distracting information on the screen, the clearer and clearer the data in analytical reports. This makes it possible to spot bottlenecks in time, make high-quality management decisions and solve problems in advance, proactively.

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

What is Microsoft Power BI ?

And this 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, then 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 only useful to us;
• 2. Web interface;
• 3. iOS devices;
• 4. Windows devices can be installed in the subway style. Those who have Win 8 / 8.1. Looks really futuristic, especially on large monitors.
• 5. Android devices. "Android app is on the way". We wait.

Work logic

Let's 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 broken down 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 a lot. Although, again, even if you take a firm of 500 people, how many employees would need such functionality?
• When registering, take into account one point - it is performed at the bottom left, where you indicate your mail. If you go to Sign in - there is only authorization.
• 2. You need to run the Power BI Designer configurator, the one you downloaded earlier.
• 3. In the configurator, we do whatever we want, shape 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. Logging in under your account on a mobile device or in a 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 get data?

Retrieving data

At the first opening, you will be greeted by an assistant. We close it. And we delete all unnecessary things. We should only have 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 on the top left, we will be greeted with the following dialogue:

As you can see, the list of potential sources is enormous. However, we will take the option of obtaining data using OData, see 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 base from which we will drag all this data.

There are several options:

• 1. Raise your web server, and drag data from there. But this option is inconvenient for training, since I will not give my server to 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 is open OData ... Make sure that this is not the case and close the site.
• 3. Go to the English-language site 1C , look there for some kind of database that has access through OData. And find one.

So, let's stop at 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 reference, for example the product reference:

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 an xml that will describe each element of the directory, however, it all depends on the browser, FireFox displays this data in the form of an RSS feed. But this does not change the essence.

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

Let's go further. It is necessary to set some kind of task so that there is an ultimate goal. However, until we get acquainted with the functionality, it is difficult. Therefore, I propose for now to limit ourselves to one elementary thing - we will display sales in terms of goods and contractors. Well, maybe something else. For ACS, this is the simplest task, but there are some nuances.

So, first of all, we need to understand where the sales are stored. They are exactly 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 have not specified authorization. It should be pointed out. Moreover, note that you can specify authorization both for the domain as a whole and for a specific address, how to specify authorization for a specific base - I did not find it, but this is not important to us now.

We indicate a login without a password and click connect. Data download should start. After it, you will have fields on the left:

The available fields are displayed in the upper right, now there are only two of them.

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

Top left - 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 have only two fields, see measurements, details, resources, we obviously have more. Let's move on to queries:

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

At the top center is the connection string. In fact, this is the text of the request, and 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 boils 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. Further it will become clearer.

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

Well, we are interested in these lines. Let's now select any element in the middle column and at the bottom we will see this message:

We should have seen it, but we won't. In order to see - we need to expand the array into strings. To do this, we press the button, which is in the upper right in the header of the middle column, I highlighted it in yellow on the screen, and remember - we now have 18 lines, we press:

We now have 50 lines. Those. we expanded everything line by line, and earlier it was grouped by the registrar, in the words of 1C.

But this does not suit us, we have few columns, I think you guessed what needs to be done - click again on that button in the header:

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:

Pay attention - we have filled in the list of actions 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 notice the formula above.

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

So, now we need to display information on the product, let's do it by combining the product and sales tables.

To do this, add a new query, but already to the product catalog http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature, in the same way as for 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. Filtered records, removed all groups:
• = Table.SelectRows (# "Removed Columns", each (= false))

Above are queries that can be inserted into the top line and thus not do it all by hand. Pay attention to the structure of all this - in the first step, we just get the 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 rule for filtering.

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

• 1. Just take and make associations by the type of links in 1C, when we combine two tables.
• 2. Add the data we need from another to one of the tables.

We will analyze both of them. So. First, we just do the union. To do this, go to the Reports tab (bottom left) and click the Manage button:

And we set up the connections:

And finally, let's print any report ... but no, it's too early. Let's rename the fields so that it is clear what is responsible for what. To do this, go back to queries and rename everything that we need, simply by right-clicking on the column and Rename or double-clicking on it. Don't forget to recheck the table relationships later.

And now we are going to build reports:

As you can see, the reports look very nice, especially the very idea that you can just take and link a bunch of charts to each other.

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

And you will be greeted with 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. Therefore, test it yourself. But the point is that the names of the 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 for this article.

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

Now let's go to the iOS app and see how it looks there:

That's all.

Outcome

In summary, I want to say only one thing - you shouldn't be limited only by the capabilities of 1C, because 1C is, first of all, a database. So beauty is for other solutions.

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 register of information, where to drop all the necessary information at once, and by this decision to 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 that it will be enough for many people).

All you need to get started is to install a web server, update the platform to 8.3.6 (maybe it will work with 8.3.5, I didn’t test, the main thing is that there are no bugs in the platform), pull the data and arrange them.

Those. for a programmer it will take several hours at most. And the result is excellent.

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