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

Both programmers and users know how long and complicated reports are created in 1C. However, we can offer for consideration a non-standard solution - this is loading data into OLAP. OLAP - online analytical reporting for enterprises with the ability to build tables and graphs. For those who tried this, it turned out to be an extremely convenient tool for building analytical reporting 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 pivot tables in Microsoft Excel.

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

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

In Visual Studio, add 1C table structure to Linq to SQL

We will also add the table structure of our analytical database in Linq to SQL and create the relationships between them.

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

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

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

Fill the indexes from the SQL table of the OLAP database

            HashSet> fndDocVer = new HashSet>();
            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 availability in our OLAP database. The _Version field contains a value for reconciling a document’s 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, we select links to directories and other objects from pre-filled dictionaries.

Fill in the dictionary for subsequent search in memory (significantly speeds up the process compared to searching from SQL database tables)

            Dictionary fndDepartments = new Dictionary();
            var fDepartments = from g in dataContext.DimDepartments
                               select g;
            foreach (DimDepartments fd in fDepartments)
            {
                fndDepartments.Add(fd.IDRRef, fd.ID); // ИД в 1С и ИД в OLAP
            }

We select the directory element in the process of downloading 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();

On this, the work in Visual Studio on filling in the plates is finished, we turn to the pivot tables in Excel. Add a new pivot table

We draw the table in Excel by adding the necessary fields to the tabular part, the result should be as follows (example)

We examined a brief example of loading data into a separate SQL database for generating analytical reporting 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, generating any report options that are convenient for the user.

Take a closer look at the source code for examples on GitHub