Upload from 1s to microsoft power bi. BI - systems. Transferring data to your account

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 methods data mining. It is 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 the design mode. Arbitrary OLAP analysis is not the main focus, but it is also present. To a greater extent, the product is focused on the use of mathematical methods for solving problems such as sales forecasting, inventory optimization, analysis client base. Industry solutions are also offered.

For integration 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. (Relational Table Multidimensional Analysis System) 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 standalone cubes.

In addition to the OLAP module, the product includes modules: Econometrics, Combination Rule, Sales Maps, Decision Trees, Genetic Algorithms, etc. The directions of use are traditional for Data Mining: analysis of the client 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 integration 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 similar work is declared.

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

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 uploaded.

You can use Excel or any other OLAP client to work with OLAP cubes. 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 1C:Enterprise mode visually and without programming, and all stages of OLAP database formation are fully automated.

At the same time, the capabilities of the product are quite wide: cubes are formed according to documents, directories, accumulation registers and accounting registers. Multiple fact tables can be combined into a single cube with common dimensions, allowing 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, ready-made analytical models can be used.

The product is configured with roles and security restrictions. Access is configured per object. Role permissions are merged. This allows you to configure, for example, access to accounting data on subconto permits, which 1C does not allow.

The INTALEV: Corporate Analytics product 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 offered the companies representing them to evaluate the possibility of building an OLAP database for solving the following tasks:

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

2. Analysis of warehouse stocks with details up to days,

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

At the same time, we demanded 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 by time and cost are summarized in a table.

* Microsoft SQL Server Standard Edition for 50 users

** Microsoft Office 2007 Standard Russian OLP NL

Creating an OLAP outline for quick analysis 1C data today is no longer a problem. 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 allowable 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, the implementation is carried out by the consultants of the OLAP supplier, in the second case, by their 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 vendor, such as SharePoint, which allows you to create a DashBoard with KPIs calculated using OLAP. They can also use Data Mining Analysis Service. Bix's solution is more customizable, but 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 buying additional licenses for these products, saving on server software.

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

Briefly about the capabilities of Microsoft Power BI

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

• Power BI Desktop designed to develop a data model 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 are very limited there.

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

For users Microsoft Windows 10 has a standalone 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 required data set from various sources into the program in a matter of minutes, link them together and build consolidated reports and charts. At the time of this writing, all available connectors are divided into four groups:

1. Group "File":

As you can see, in Power BI you can import data from the most famous databases and services using various 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 a fairly powerful ETL functionality. Downloadable tables can be edited, and based on their columns, create calculated columns and measures - in this way, you can transform information arrays into data sets with the structure necessary to build a visualization.

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

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

All elements have a fairly wide range of settings aimed at changing the desired parameters: color, background, title, borders, and so on. If it's not enough for you standard set, you can upload custom visuals.

Smart 1C is a product of integration of 1C family programs with Power BI cloud business intelligence system. You could already click above and get acquainted with the demo version of one of our reports built on 1C data. 1C is a popular program for accounting operations in business. However, the reports that can be made in 1C cannot fully satisfy the needs of the manager in analytics.
Smart 1C solutions will be useful for entrepreneurs and businessmen who keep records in the 1C program. The benefit is that the manual work of collecting data from 1C and preparing reports will be automated. And customized reports - automatically updated up to 8 times a day. In addition, all reports will be in a single Power BI dashboard.

How to implement in your business

Integration of 1C and Power BI can be done both on your own and by involving third-party developers.

First option suitable for large businesses that have qualified specialists in the following areas: 1C programmer (1C language, information registers, oData protocol, http service, etc.), Power BI developer (M Query language, DAX queries, OLAP modeling, etc.). ) and an analyst who will report.

Second option- This is the involvement of external developers. For example, the company SMART METRIKA. In this case, BI is configured by our specialists who already have experience in this matter. Collecting, transforming and transferring data flow to the Power BI web service is essentially the basis of the future system. Data visualization, i.e. reporting is usually done for the tasks of each specific business. Reports such as PnL (profit and loss), CashFlow (money flow), receivables and payables dynamics, sales, cost and profit analysis, break-even point are most in demand by managers. Although you can display in Power BI any data that is in 1C.

How fast is implementation

The project implementation time is determined primarily by the analytics that the business needs. Understanding all analytical sections and indicators provides information about what data needs to be taken from 1C. Small projects can be completed in 2 weeks (3 specialists participate), complex projects - 1-2 months. An important factor influencing the project execution time is the correspondence of the requested analytics with the data source - 1C. The final date and deadline for the implementation of the project can be named definitely if there is a drafted terms of reference.

Service and analytical support

An important component of any of our implementation is the maintenance of the system after the delivery of the project on demand. For our clients, we always stay in touch and, if necessary, help to resolve the issue or make improvements.

Let's discuss?

We invite you to discuss with us the need for BI for your business right now. This will help you better understand whether you need this system or not. Just click the button Discuss and fill out the form. We are waiting for you!

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

What is it Microsoft Power BI ?

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

Naturally, if 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 useful only to us;
• 2. Web interface;
• 3. iOS devices;
• 4. Windows devices, can install in the subway 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'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 whole 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. But then again, even if we take a company of 500 people, how many employees will need such functionality?
• When registering, keep one point in mind - 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, form 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 with your account 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 get data?

Getting data

When you first open you will be met by an assistant. We close it. And we remove all unnecessary. We should only have this window:

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

In pursuit of an answer, we must press the button " Get data", - the one on the top left, we will be greeted by this 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. 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 learning, since I will not give my server to public access.
• 2. Go to site 1C , 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 site 1C , to look after some kind of base there, which has access through OData. And find one.

So, we 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 directory, for example, the goods 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 it doesn't change the essence.

If, when connecting to your database, you get a message stating that the entity was not found, then you are here.

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

So the first thing we need to understand is where sales are stored. They are exactly stored in the accumulation register, and this register is designated in OData as AccumulationRegister. Here we are looking for them. Basic knowledge of English will help us with this. And here is our AccumulationRegister_Sales register. 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. Must be specified. And pay attention, you can specify authorization both in general for the domain and for a specific address, I did not find how to specify authorization for a specific database, but this is not important for us now.

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

Available fields are displayed at the top right, now there are only two.

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

Top left - pages with reports, ie. on one page (a la layout 1C) there can be a lot of different data.

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

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

Top center - connection string. In fact, this is the text of the request, both selections, and paths, etc. are indicated here.

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

Here on the sequence we'll talk a little. 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 specify 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 it connected with? Let's take a close look at the xml that 1C gives us and find that place where the list starts, i.e. when OData we are returned not a line-by-line representation of the register, as we are used to, but first 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:

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

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

But this does not suit us, there are not enough columns for us, I think you guessed what needs to be done - click again on that button in the header:

And now we choose - what we want to add, i.e. by analogy with a request in 1C - select the fields that we then want to operate on:

And click OK:

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

• 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 satisfied with the name of the product in the form of UID . I doubt that such a view would be informative.

So, 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 request, but to the product directory http://apps.1c-dn.com/sb/odata/standard.odata/Catalog_Nomenclature , just like sales, and rename these tables so as not to get confused :

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

• 1. Got 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 requests that can be inserted into the top line and thus do 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 specify what we do with the data that we received in the first step. On the third - we indicate again the name of the step to which we will apply the rule for filtering.

Those. we always have to specify to which step we apply this or that rule.

• 1. It's easy to take and make joins by the type of links in 1C when we join two tables.
• 2. Add to one of the tables the data we need from the other.

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 set up connections:

And, finally, let's display any report ... and although no, it's too early. Let's rename the fields to make it clear what is responsible for what. To do this, we return to the queries and rename everything we need by simply right-clicking on the column and Rename or twice on it. Don't forget to check the table links afterwards.

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 groups here. In theory, it would be necessary to add another query with item groups and associate them with the NomenclatureGroup_Key field in the item request. However, we won't need it. We go to the very end, look at the last columns in the Nomenclature query.

And you will be met 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. So test for yourself. But the bottom line 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.

And now let's go to the iOS application and see how it looks there:

That's all.

Outcome

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

If you know OData well, then you can make this kind of report in a few minutes. And besides this - for ease of operation and less load on the database, you can simply create a separate information register, where to throw off all at once necessary information, and with 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 restrictions in free version, but I think 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 haven’t tested it, the main thing is that there are no bugs in the platform), extract the data and compose them.

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

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

I must say right away that this article does not claim to be complete guide. Rather, is personal experience management data visualization.

The concept of "analytics" has large quantity 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 the huge amount of data that is generated during the conduct of an enterprise.

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

At the same time, not all necessary data sources are always included in a single accounting system. The budget can be formed in Excel, information on shipments and requests from customers can be available in test formats, site visitor metrics - through a web portal, etc. How can all this be linked into a single "enterprise control panel"? Microsoft Power BI can help 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.
• An active Microsoft Power BI subscription.
• An additional data source that we will associate with data from 1C - take an Excel file.

Publication of information base 1C

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

We start 1C in the "Configurator" mode with local administrator rights and go to Administration - publishing a web extension. We enable authorization through 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 standard and included in all standard 1C configurations.

We search 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 IP address of the server.
BaseName - name information base, which we used when publishing.

Document_Realization of GoodsServices_Goods - data that is provided from 1C. These can be directories, registers, etc.

Connecting sources in Power BI

Now we 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 feed:

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

By clicking the "Edit" button, we will get 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, switch to the Relationship View mode.

Adding Other Sources

Similarly, we can add other data sources, for example, 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 sources and creating links between data, you can begin to visualize information.

Data visualization

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

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 its publication. To do this, simply click the "Publish" button and select workspace where 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.

On my own behalf, I would add that this tool allowed our company to gain a number of knowledge about us and our customers. An example is understanding the geography of customers, highlighting “gold” partners, overloaded employees, etc.