Creating a reporting system for 1C: ERP based on OLAP and Excel

Both programmers and users know how long and difficult reports are generated in 1C. However, we can offer a non-standard solution for consideration - this is loading data into OLAP. OLAP - online analytical reporting for enterprises with the ability to build tables and graphs. For those who tried it, it turned out to be an extremely convenient means for building analytical reports in Microsoft Excel, as they say, in the usual way for ordinary users in enterprises using 1C: ERP.

In our example, we will use Visual Studio C #, 2 Microsoft SQL Server databases - one is 1C: ERP and the second for OLAP analytical reporting, as well as summary tables in Microsoft Excel.

To get information about the tables and the structure of the 1C database: ERP, we will use ready-made treatments that already exist on the Internet (GetDatabaseStructure.epf or base_structure.epf).

Using this tool, we will see a list of fields for 1C objects (reference books, documents, document lines, enumerations, etc.) and their purpose, which will be useful later for us in programming data loading in OLAP. Based on the data, we will prepare the tables in our separate database for analytical reporting in Microsoft SQL.

In Visual Studio, add the structure of 1C tables in Linq to SQL

Let us also expand the table structure of our analytical database in Linq to SQL and create links between them.

Create a class in Visual Studio to reload data from the 1C structure into the OLAP structure

public class DocProductionImporter { DataClasses1DataContext dataContext; s1_DataClasses1DataContext dataContextS1; public DocProductionImporter() { dataContext = new DataClasses1DataContext(); dataContextS1 = new s1_DataClasses1DataContext(); } .... } 

Let's load into memory a list of 1C document identifiers already available in the OLAP database. The uniqueness of documents in 1C is supported mainly with the help of IDRRef and Version. Tuple helps us create a composite index, if necessary.

Fill in the indexes from the SQL table of the OLAP database

  HashSet<Tuple<System.Data.Linq.Binary, // IDRRef, Version System.Data.Linq.Binary>> fndDocVer = new HashSet<Tuple<System.Data.Linq.Binary, System.Data.Linq.Binary>>(); foreach (DocProduction doc in docs) { fndDocVer.Add(Tuple.Create(doc.IDRRef, doc.Version)); } 

We make a selection of documents from 1C: ERP and check their presence in our OLAP database. The _Version field contains the value for checking the document modification in 1C

  var prodDocs = from c in dataContextS1._Document581s where c._Posted == posted select c; int i = 0; foreach (_Document581 doc in prodDocs) { if (fndDocVer.Contains(Tuple.Create(doc._IDRRef, doc._Version)) == true) { i++; continue; } .... 

Create a new or update an existing document.

  // NEW DocProduction dp = new DocProduction(); dp.NumberPrefix = doc._NumberPrefix; dp.DocNumber = doc._Number; 

Inside the document loop, references to references and other objects are selected from pre-filled dictionaries.

Fill out the dictionary (Dictionary) for subsequent search in memory (significantly speeds up the process, compared with a search from SQL database tables)

  Dictionary<System.Data.Linq.Binary, Int64> fndDepartments = new Dictionary<System.Data.Linq.Binary, Int64>(); var fDepartments = from g in dataContext.DimDepartments select g; foreach (DimDepartments fd in fDepartments) { fndDepartments.Add(fd.IDRRef, fd.ID); //   1    OLAP } 

Pick up a reference item in the process of loading documents

  if (fndDepartments.ContainsKey(doc._Fld15867RRef)) { Int64 val; fndDepartments.TryGetValue(doc._Fld15867RRef, out val); dp.DepartmentID = val; } else throw new Exception(); //      

At the end of the cycle, save the document in the OLAP database.

  dp.IDRRef = doc._IDRRef; dp.Version = doc._Version; if (doc._Posted == posted) //     1 dp.Active = true; else dp.Active = false; dataContext.DocProductions.InsertOnSubmit(dp); dataContext.SubmitChanges(); 

At this, the work in Visual Studio on filling out the plates is completed, go to the pivot tables in Excel. Add a new pivot table

We draw a table in Excel by adding the required fields to the table part, the result should be as follows (approximate version)

We reviewed a brief example of loading data into a separate SQL database for generating analytical reports in OLAP and Excel from any 1C configurations. The set of slices and indicators obtained in the course of such development is ready to be used by the end user in the Microsoft Excel pivot table, creating any report options convenient for the user.

Review the source code of the examples on GitHub in detail.