A real-time production yield monitoring system developed based on PowerBI

Through a real-time production yield monitoring system based on PowerBI, Excel and PowerBIReportServer are used to realize automated summary and visualization of production data, solving the problems of high interactiveness and cost of production data, and achieving efficient and secure data updates and decision-making support.

CN118796959BActive Publication Date: 2024-11-19XIAN EF TECH CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411281116.7
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-09-13
Publication Date
2024-11-19
Estimated Expiration
2044-09-13

AI Technical Summary

Technical Problem

Different audience groups that produce data in the prior art cannot interact effectively, and rely on specialized personnel to perform repeated statistics and data processing, resulting in inefficient information updates, and traditional methods require expensive data management systems and high maintenance costs.

Method used

Using a real-time production yield monitoring system developed based on PowerBI, the VBA program and Windows instructions of the Excel module can be automatically summarized and visualized, and the local web publishing environment is built using PowerBIReportServer and SQL SERVER to realize automatic update and seamless integration of data sources. Generate interactive production data web portal.

Benefits of technology

It realizes automated summary and visualization of production data, reduces data statistics dependence, avoids information lag, reduces personnel workload, reduces costs, and ensures information security and rapid decision-making capabilities.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118796959B_ABST
    Figure CN118796959B_ABST
Patent Text Reader

Abstract

The present invention discloses a real-time production yield monitoring system developed based on PowerBI, which belongs to the field of production yield monitoring and includes a collection module for collecting real-time production data; an Excel module for configuring a VBA program to record and analyze local production data and generate a production yield statistics table in summary; a PowerBI module for building a model based on production data converted by the Excel module to generate a visual statistics page and a PBIX execution file; a server, which is respectively associated with the Excel module and the PowerBI module, and is used for hosting production data, automatic refresh in the later stage, deployment and creation of user login permissions, and information query of production data by logged-in users and information interaction with production data. The present application solves the problem that enterprises rely too much on statisticians for statistics of production data, and different audience groups cannot effectively interact with the published information.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of production yield monitoring, and in particular to a real-time production yield monitoring system developed based on PowerBI. Background Art

[0002] With the advent of the big data era, the culture of data-driven decision-making is booming. As a result, the data supporting enterprise decision-making is becoming increasingly detailed and the amount of data is becoming increasingly large. This leads to an increasing demand for data processing, analysis, visualization, and timeliness. The main technologies currently used include:

[0003] 1) Some large enterprises have introduced online data management systems such as ERP and MES, but these have only achieved cloud-based data digitization or deployed some query and aggregation functions, without effectively integrating data query and digitization. Consequently, specialized personnel are required for subsequent query download, processing, statistics, visualization, and publishing. Furthermore, these ERP and MES systems incur high initial construction costs and substantial annual maintenance and management fees.

[0004] 2) Some companies use offline data management systems and adopt traditional data processing methods to deploy production data as local data. To obtain production data, such companies need specialized personnel to regularly and manually perform repeated statistics and releases. The timeliness, accuracy, and comprehensiveness of production data transmission are overly dependent on statisticians, resulting in inefficient information updates. What's worse, because production data is stored on the local computers of managers, it is isolated from external information, and other relevant personnel cannot obtain the information. As a result, different audiences cannot effectively interact with the published production data, and different personnel need to repeat statistics, resulting in a waste of personnel work. Summary of the Invention

[0005] In view of this, this application provides a real-time production yield monitoring system developed based on PowerBI to solve the problem in the above-mentioned prior art that different audience groups of production information cannot effectively interact with the published information. The specific solution is as follows:

[0006] A real-time production yield monitoring system developed based on PowerBI, including:

[0007] Collection module, used to collect real-time local production data;

[0008] Excel module, equipped with VBA program, is used to record and analyze local production data and automatically generate production yield statistics;

[0009] The PowerBI module builds a model based on the production data converted by the Excel module, generates a visualization statistics page and a PBIX execution file, and sends the PBIX execution file to the server;

[0010] The server is respectively associated with the Excel module and the PowerBI module, and is used for the initial deployment of production data, the hosting of production data, the automatic refresh of later production data, the deployment and creation of login user permissions, the real-time information query of production data by logged-in users, and the interaction of information with production data.

[0011] Preferably, the server has a built-in local database unit, a link unit, an automatic refresh unit, and a login user authority deployment and creation unit;

[0012] The local database unit and the automatic refresh unit are linked to the creation unit;

[0013] The login user rights deployment and creation unit is linked to the PowerBI module and the Excel module respectively;

[0014] The link unit includes a local link unit and a public local area network link unit;

[0015] The local link unit and the public LAN link unit are respectively linked to the login user rights deployment and creation unit and the local user terminal. The local link unit and the public LAN link unit are also respectively connected to the Excel module and the PowerBI module for publishing the design page of the PBIX execution file to the local user terminal;

[0016] The automatic refresh unit is respectively connected to the acquisition module, Excel module, local link unit and public LAN link unit, and is used to enable the local user end to check the updated production data in time.

[0017] Preferably, the login user rights deployment and creation unit manages the local user terminal login rights in the form of a Web address.

[0018] Preferably, the PowerBI module is configured with a data modeling unit, a visualization statistics page generation unit, and a PBIX execution file generation unit;

[0019] The data modeling unit is connected to the Excel module, associates the production yield statistical data with the PowerBI module, and constructs a data model through the production yield statistical table generated by the Excel module;

[0020] The visualization statistics page generation unit is connected to the data modeling unit to perform visualization design on the constructed data model;

[0021] The PBIX execution file generation unit is connected to the visualization statistics page generation unit and the local link unit and the public LAN link unit respectively, generates a PBIX execution file from the received visualization data model, and shares it with the local user terminal through the local link unit and the public LAN link unit.

[0022] Preferably, data refresh can be set in the automatic refresh unit.

[0023] Preferably, the setting of the automatic refresh unit is realized by creating a Windows instruction dat file and a VBA program in an Excel module.

[0024] Compared with the prior art, the present invention has the following advantages:

[0025] This system uses VBA programs and Windows instructions in the Excel module to summarize and process Excel production data deployed in the local area network, and completes data aggregation and update on a regular basis, without the need for personnel to perform repeated statistics. The production data summary information is modeled through the Power BI module to generate a visual design file to display the production information. SQL SERVERE is used to build a SQL SERVERE database, and the local production data is hosted by the PowerBIReportserver report server. A local Web publishing environment and permission control are built to link, deploy and automatically refresh local production data, and generate an interactive production data Web portal address. After completion, the system will automatically complete the work from data statistics to scheduled data refresh and release. This application uses Excel's built-in Visual Basic For Application (ie VBA program) to develop the background, write VBA code with events to summarize production information, complete the aggregation and update of relevant production data, and generate a production yield data statistics table that can be used for table viewing and Web publishing; deploy PowerBIReportServer, SQL SERVER and Power through VBA programs and Windows instructions. The BI module can achieve automatic updating and aggregation of data sources, seamless integration of data sources and visualization page links, and automatic data refresh to avoid information lags, assisting relevant enterprise decision makers in making quick responses and decisions, and no longer requiring statisticians and publishers. This solves the problem in the prior art that different audiences of production data cannot query production data information and effectively interact with production data information. At the same time, the data statistics and aggregation of this application do not rely on the Internet and expensive production management systems, and can complete the visualization and publishing of local LAN production data; for confidential units, it avoids the information exchange between production data and the Internet, fully ensuring the security and confidentiality of information, and at the same time, in the local LAN, it realizes the real-time interactive visualization requirements with production data information; for small and medium-sized enterprises that cannot afford the high cost of using production management systems, the present invention realizes the low-cost and high-yield data visualization requirements;

[0026] In this application, only Excel, SQL Server, Power BI desktop RS, and Power BI Report Server software are used. There is no need to introduce professional data management systems such as online ERP and MES. Data automatic aggregation and data visualization can be quickly and easily completed on the local LAN Web. There is no need for system construction and system maintenance costs, which has obvious cost advantages. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1This is a schematic diagram of the visual webpage process of Power BI publishing production yield in real time in an embodiment of the present application. DETAILED DESCRIPTION

[0028] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0029] The present invention provides a real-time production yield monitoring system developed based on PowerBI, comprising:

[0030] Collection module, used to collect real-time local production data;

[0031] The Excel module is equipped with an Excel summary query unit, a storage unit, and a VBA program, which is used to analyze, classify, summarize, and store the production data transmitted by the acquisition module;

[0032] The PowerBI module builds a model based on the production data converted by the Excel module, generates a visualization statistics page and a PBIX execution file, and sends the PBIX execution file to the server;

[0033] The server is connected to the Excel module, PowerBI module and each local user terminal respectively, and is used for the initial deployment of production data, the hosting of production data, the automatic refresh of later production data, the deployment and creation of login user permissions, and the real-time information query of production data and the interaction of information with production data by logged-in users.

[0034] The Excel modules are connected to the acquisition modules to count and store the real-time local production data. The Excel modules utilize the built-in Visual Basic for Applications (VBA) development background to write VBA code with events to summarize production information. This summarizes the real-time production data collected and automatically generates a production yield statistics table that can be viewed in a table and published on the Web. The main VBA code is as follows:

[0035] {

[0036] Private Sub Workbook_BeforeClose(Cancel As Boolean)

[0037] With Sheets("xxxx data")

[0038] th = .Cells(.Rows.Count, 4).End(xlUp).Row

[0039] If Sheets("xxx data query").Cells(5, "ee").Value<>th = th Then

[0040] Call Summary

[0041] End If

[0042] End With

[0043] End Sub

[0044] Private Sub Workbook_Open()

[0045] With Sheets("xx data")

[0046] Sheets("xxx data query").Cells(5, "ee").Value = .Cells(.Rows.Count, 4).End(xlUp).Row

[0047] End With

[0048] End Sub

[0049] Public Sub SumAll()

[0050] Call Delete Data

[0051] Set d = CreateObject("scripting.dictionary")

[0052] With Sheets("xx data")

[0053] hh = .Cells(.Rows.Count, 4).End(xlUp).Row

[0054] For i = 2 To hh

[0055] d(.Cells(i, "l").Value) = ""

[0056] Next

[0057] End With

[0058] Cells(2, 1).Resize(d.Count, 1) = Application.WorksheetFunction.Transpose(d.keys)

[0059] Call to query the latest test data

[0060] MsgBox "Query completed!"

[0061] End Sub

[0062] Public Sub Query the latest test data () 'HW owner

[0063] Dim i%, h%, j%

[0064] n = 0

[0065] h = Cells(Rows.Count, 1).End(xlUp).Row

[0066] If h = 1 Then Exit Sub

[0067] With Sheets("xx data")

[0068] Cells(1, 4).Resize(1, 30) = .Range(.Cells(1, 2), .Cells(1, "ae")).Value

[0069] th = .Cells(.Rows.Count, 4).End(xlUp).Row

[0070] For i = 2 To h

[0071] For j = th To 2 Step -1

[0072] If Cells(i, 1).Value = .Cells(j, "l").Value Then

[0073] n = n + 1

[0074] Cells(i, 4).Resize(1, 30) = .Range(.Cells(j, 2), .Cells(j,"ae")).Value

[0075] Cells(i, 2).Value = "=left(d"&i&",4)"

[0076] Cells(i, 3).Value = "=mid(d"&i&",5,2)"

[0077] Exit For

[0078] End If

[0079] Next

[0080] If n = 0 Then Cells(i, 2).Value = "No record"

[0081] n = 0

[0082] Next

[0083] End With

[0084] Call to delete invalid data

[0085] End Sub

[0086] Public Sub DeleteInvalidData()

[0087] h = Cells(Rows.Count, 1).End(xlUp).Row

[0088] For i = h To 2 Step -1

[0089] If Cells(i, 3).Value>4 And Cells(i, "h").Value = "xxxxx" Then

[0090] Cells(i, 3).EntireRow.Delete

[0091] End If

[0092] Next

[0093] ThisWorkbook.Save

[0094] End Sub

[0095] Public Sub DeleteData()

[0096] lh = Cells(Rows.Count, 1).End(xlUp).Row

[0097] If lh>1 Then

[0098] Range(Cells(2, 1), Cells(lh, "cz")).ClearContents

[0099] End If

[0100] End Sub

[0101] }

[0102] Main Program 2

[0103] {

[0104] Private Sub Workbook_BeforeClose(Cancel As Boolean)

[0105] With Sheets("xxxx Data")

[0106] th = .Cells(.Rows.Count, 4).End(xlUp).Row

[0107] If Sheets("xxx Data Query").Cells(5, "ee").Value<>th = th Then

[0108] Call xxxxxx Data

[0109] End If

[0110] End With

[0111] End Sub

[0112] Private Sub Workbook_Open()

[0113] With Sheets("xx Data")

[0114] Sheets("xxx Data Query").Cells(5, "ee").Value = .Cells(.Rows.Count, 4).End(xlUp).Row

[0115] End With

[0116] End Sub

[0117] Public Sub xxxxxx Data()

[0118] Sheets(xxx").Range("b2:ee100000").ClearContents

[0119] Set d = CreateObject("scripting.dictionary")

[0120] '*********************************************************** xxxxxx

[0121] Workbooks.Open Filename:="xxxxx", writeresPassword:="xxx"

[0122] ActiveWorkbook.Sheets("xxx").Activate

[0123] h = Cells(Rows.Count, 5).End(xlUp).Row

[0124] arr = Range(Cells(2, 3), Cells(h, "o"))

[0125] Workbooks("xxxx.xlsx").Close False

[0126] '*********************************************************** xxxxxx

[0127] '*********************************************************** xxxxxx

[0128] Workbooks.Open Filename:="xxx.xlsm"

[0129] ActiveWorkbook.Sheets("xxx数据").Activate

[0130] ddh = Cells(Rows.Count, 3).End(xlUp).Row

[0131] drr = Range(Cells(2, 2), Cells(ddh, "h"))

[0132] Workbooks("xxx.xlsm").Close False

[0133] '*********************************************************** xxxxxx

[0134] ThisWorkbook.Sheets(xxxx").Activate

[0135] '*********************************************************** xxxxxx

[0136] For i = 1 To UBound(drr, 1)

[0137] Cells(i + 1, "g").Value = drr(i, 1)

[0138] Cells(i + 1, "h").Value = drr(i, 2)

[0139] Cells(i + 1, "i").Value = drr(i, 6)

[0140] Cells(i + 1, "j").Value = drr(i, 3)

[0141] Cells(i + 1, "k").Value = drr(i, 4)

[0142] Cells(i + 1, "l").Value = drr(i, 5)

[0143] Cells(i + 1, "m").Value = drr(i, 7)

[0144] Next

[0145] '*********************************************************** xxxxxx

[0146] '*********************************************************** xxxxxx

[0147] h = Cells(Rows.Count, "g").End(xlUp).Row

[0148] For i = 1 To UBound(arr, 1)

[0149] For j = 2 To h

[0150] If arr(i, 4)&arr(i, 5) = Cells(j, "g")&Cells(j, "h") Then

[0151] Cells(j, 2).Value = arr(i, 7)

[0152] Cells(j, 3).Value = arr(i, 3)

[0153] Cells(j, 4).Value = arr(i, 1)

[0154] Cells(j, 5).Value = Left(arr(i, 1), 4)

[0155] Cells(j, 6).Value = Mid(arr(i, 1), 5, 2)

[0156] End If

[0157] Next

[0158] Next

[0159] '*********************************************************** xxxxxx

[0160] Call MatchingVerificationData

[0161] End Sub

[0162] Public Sub xxxxx()

[0163] h = Cells(Rows.Count, "g").End(xlUp).Row

[0164] With Sheets("xxxx")

[0165] ch = .Cells(.Rows.Count, 1).End(xlUp).Row

[0166] For i = 2 To h

[0167] If Cells(i, 3).Value<>"" Then

[0168] For j = 2 To ch

[0169] If Cells(i, 3).Value = .Cells(j, 1).Value Then

[0170] Cells(i, "n").Value = .Cells(j, 2).Value

[0171] Cells(i, "o").Value = .Cells(j, 4).Value

[0172] Cells(i, "p").Resize(1, 103) = .Range(.Cells(j, "u"),.Cells(j, "ds")).Value

[0173] End If

[0174] Next

[0175] ElseIf Cells(i, "n").Value = "" And Cells(i, 3).Value<>"" Then

[0176] Cells(i, "o").Value = "xxx"

[0177] Cells(i, "Ao").Value = -1

[0178] ElseIf Cells(i, "n").Value = "" And Cells(i, 3).Value = "" Then

[0179] Cells(i, "o").Value = "xxx"

[0180] Cells(i, "Ao").Value = -22

[0181] End If

[0182] Next

[0183] End With

[0184] For i = 2 To h

[0185] If Left(Cells(i, "g").Value, 1) = "A" Then

[0186] Select Case Left(Cells(i, "h").Value, 1)

[0187] Case "1"

[0188] Cells(i, "do").Value = 14

[0189] Case "2"

[0190] Cells(i, "do").Value = 13

[0191] Case "3"

[0192] Cells(i, "do").Value = 12

[0193] Case "4"

[0194] Cells(i, "do").Value = 11

[0195] Case "5"

[0196] Cells(i, "do").Value = 10

[0197] Case "6"

[0198] Cells(i, "do").Value = 9

[0199] Case "7"

[0200] Cells(i, "do").Value = 8

[0201] Case "8"

[0202] Cells(i, "do").Value = 7

[0203] Case "9"

[0204] Cells(i, "do").Value = 6

[0205] Case "A"

[0206] Cells(i, "do").Value = 5

[0207] Case "B"

[0208] Cells(i, "do").Value = 4

[0209] Case "C"

[0210] Cells(i, "do").Value = 3

[0211] Case "D"

[0212] Cells(i, "do").Value = 2

[0213] Case "E"

[0214] Cells(i, "do").Value = 1

[0215] End Select

[0216] Select Case Right(Cells(i, "h").Value, 1)

[0217] Case "1"

[0218] Cells(i, "dp").Value = 1

[0219] Case "2"

[0220] Cells(i, "dp").Value = 2

[0221] Case "3"

[0222] Cells(i, "dp").Value = 3

[0223] Case "4"

[0224] Cells(i, "dp").Value = 4

[0225] Case "5"

[0226] Cells(i, "dp").Value = 5

[0227] Case "6"

[0228] Cells(i, "dp").Value = 6

[0229] Case "7"

[0230] Cells(i, "dp").Value = 7

[0231] Case "8"

[0232] Cells(i, "dp").Value = 8

[0233] Case "9"

[0234] Cells(i, "dp").Value = 9

[0235] Case "A"

[0236] Cells(i, "dp").Value = 10

[0237] Case "B"

[0238] Cells(i, "dp").Value = 11

[0239] Case "C"

[0240] Cells(i, "dp").Value = 12

[0241] Case "D"

[0242] Cells(i, "dp").Value = 13

[0243] Case "E"

[0244] Cells(i, "dp").Value = 14

[0245] End Select

[0246] Else

[0247] Select Case Left(Cells(i, "h").Value, 1)

[0248] Case "1"

[0249] Cells(i, "do").Value = 10

[0250] Case "2"

[0251] Cells(i, "do").Value = 9

[0252] Case "3"

[0253] Cells(i, "do").Value = 8

[0254] Case "4"

[0255] Cells(i, "do").Value = 7

[0256] Case "5"

[0257] Cells(i, "do").Value = 6

[0258] Case "6"

[0259] Cells(i, "do").Value = 5

[0260] Case "7"

[0261] Cells(i, "do").Value = 4

[0262] Case "8"

[0263] Cells(i, "do").Value = 3

[0264] Case "9"

[0265] Cells(i, "do").Value = 2

[0266] Case "A"

[0267] Cells(i, "do").Value = 1

[0268] End Select

[0269] Select Case Right(Cells(i, "h").Value, 1)

[0270] Case "1"

[0271] Cells(i, "dp").Value = 1

[0272] Case "2"

[0273] Cells(i, "dp").Value = 2

[0274] Case "3"

[0275] Cells(i, "dp").Value = 3

[0276] Case "4"

[0277] Cells(i, "dp").Value = 4

[0278] Case "5"

[0279] Cells(i, "dp").Value = 5

[0280] Case "6"

[0281] Cells(i, "dp").Value = 6

[0282] Case "7"

[0283] Cells(i, "dp").Value = 7

[0284] Case "8"

[0285] Cells(i, "dp").Value = 8

[0286] Case "9"

[0287] Cells(i, "dp").Value = 9

[0288] Case "A"

[0289] Cells(i, "dp").Value = 10

[0290] End Select

[0291] End If

[0292] Next

[0293] ThisWorkbook.Sheets("xxxxxx").Activate

[0294] End Sub

[0295] }

[0296] The PowerBI module is connected to the Excel module to associate and import real-time production data from the Excel module. In the PowerBI module, the imported production yield statistics table data is sequentially modeled and designed using Power BI desktop RS software. This ultimately generates a PBIX executable file (after the initial data deployment is complete, there's no need to import data again later). After the design is complete, the PBIX executable file is hosted on the server.

[0297] In the server, through the scheduled refresh setting, the command dat file through Windows is automatically obtained, the report with VBA summary production information code is opened regularly, and then the VBA program is automatically run according to the VBA event to complete the summary and update of production data;

[0298] For login user permission deployment and creation units, build a local database server and PowerBIReportServer report server by installing SQL Server, and jointly build a local Web publishing environment and permission control; publish the data model of the PBIX executable file by installing PowerBIReportServer, and generate a local Web portal address for viewing (referred to as the local Web address);

[0299] Check the local web address and deploy the account. On the computer where the database is deployed, create an administrator user and password to assign the account and password in preparation for the subsequent login.

[0300] In the link unit, the local link unit and the public LAN link unit are local Web, where the system administrator enters the PowerBIReportServer management interface through the local Web address (where the local Web address is presented in the form of a URL);

[0301] After entering the Power BI Report Server management interface through the visual local Web address, the Power BI Report Server management interface sets the refresh plan and data source verification of the visual design report, uses the created administrator password and account, and assigns the login account, password, and access permissions; after all settings are completed, the local Web will regularly refresh and update the summarized local production data through the Power BI Report Server, and update the visual interactive page. Enterprise personnel can log in to the local Web through the portal address given by the local Web according to the assigned account and password, and can query production data and interact with production data.

[0302] The present application is further described below through the following examples.

[0303] Example 1:

[0304] This embodiment is based on the deployment of the first production data, and the specific process is as follows:

[0305] refer to Figure 1 , the administrator enters the local production data into the Excel summary query system, automatically summarizes and generates a production yield statistics table through Excel, and imports the data into the Power BI desktop software;

[0306] In Power BI desktop software, perform data modeling, generate visualization statistics pages and PBIX executable files, then publish the data model through PowerBIReportserver and generate the viewing web portal address (URL).

[0307] Example 2:

[0308] The process of periodic refresh and review based on the post-production data in this embodiment is as follows:

[0309] The server is linked to the acquisition module and Excel data summary table. The Windows task scheduler is set through the created Windows instructions to set the period for regularly refreshing data. The Windows instructions are linked to the Excel module to regularly open the report with VBA summary code according to the set period. The VBA program is automatically run according to VBA events to complete the summary and update of production data. The updated data is automatically refreshed to the server for local network publishing, realizing the entire update process without manual intervention.

[0310] Local users can access the real-time production yield data table through the local web address. On the one hand, this allows company employees to access the real-time production yield data from the local network through a specific URL (i.e., the local web address). On the other hand, this allows company employees to update the real-time production yield data from the local network through a specific URL.

[0311] At the same time, local users can export the real-time data obtained by the acquisition module into Excel data files through the local Web address.

[0312] It should be noted that:

[0313] The Excel production data table deploys error-proofing functions through data validity and conditional formatting to avoid data entry errors. This application inputs the real-time collected production data into the Excel module, and uses the Visual Basic For Application (VBA program) that comes with the Excel module to develop the background to write production information statistics summary code, automatically identify changes in production data and automatically complete the statistics of data information, realize automatic summary of production data, and generate a production yield statistics table.

[0314] The production yield statistics table is imported into Power BI desktop software for data modeling and visualization design, generating the final PBIX executable file. The PBIX executable file is hosted on the server, where the access account and password are deployed. The refresh schedule and access permissions are set in the Power BI Report Server management interface. Once the settings are complete, the visualization page is published to a local web portal address within the service. Enterprise personnel only need to open the local web portal address through their computers to view production data and interact with production data. This achieves:

[0315] 1) Reduced data operation costs: This system uses Excel software (i.e., the Excel module) and Power BI desktop software (the Power BI module). This eliminates the need for online data management systems such as ERP and MES. It can quickly and easily complete automatic data aggregation and data visualization for cloud-based online and local web publishing, eliminating the need for system construction and maintenance costs, resulting in significant cost advantages.

[0316] 2) Excellent data quality and accuracy: The system operates automatically, eliminating the need for manual data collection and production, thus avoiding data errors caused by traditional manual statistics.

[0317] 3) System Automation: Through the combined design of VBA programs, Windows commands, PowerBIReportServer, SQL Server, and page design Power BI desktop, automatic data source updates, seamless integration of data source and visualization page links, automatic data refresh, avoiding information lags, and helping enterprise decision makers make quick responses and decisions;

[0318] 4) Fully relieve employees' statistical workload: System automation significantly reduces the overall cost and time consumption of data analysis, freeing up a large amount of workload compared to the manpower and time investment of traditional methods;

[0319] 5) Local data cloudification: Through Power BI and Power BI Report Server, data will be displayed in the form of the web. Enterprise personnel can view it in real time through computers, ignoring the restrictions of data isolation, and grasp production information;

[0320] 6) Excellent interactivity and dynamism: This system is implemented in a web-based visualization interface, which allows online filtering, sorting, and drilling of production data, allowing dynamic exploration of different dimensions and details of the data. This is different from traditional static charts (statisticians can only see the information they publish).

Claims

1. A real-time production yield monitoring system developed based on PowerBI, It is characterized in that include: Collection module, used to collect real-time local production data; Excel module, equipped with VBA program, is used to record and analyze local production data and automatically summarize production data. The production yield statistics table; The PowerBI module builds a model based on the production data converted by the Excel module, generates a visualization statistics page and a PBIX execution file, and sends the PBIX execution file to the server; The server is respectively associated with the Excel module and the PowerBI module, and is used for the initial deployment of production data, the hosting of production data, the automatic refresh of later production data, the deployment and creation of login user permissions, and the real-time information query of production data by the logged-in user and the interaction of information with the production data; The server has a built-in local database unit, a link unit, an automatic refresh unit, and a login user rights deployment and creation unit; The local database unit and the automatic refresh unit are linked to the creation unit; The login user rights deployment and creation unit links the PowerBI module and the Excel module respectively; The link unit includes a local link unit and a public local area network link unit; The local link unit and the public LAN link unit are respectively linked to the login user rights deployment and creation unit and the local user terminal, and the local link unit and the public LAN link unit are also respectively connected to the Excel module and the PowerBI module, for publishing the design page of the PBIX execution file to the local user terminal; The automatic refresh unit is respectively connected to the acquisition module, the Excel module, the local link unit and the public LAN link unit, so as to enable the local user end to check the updated production data in time; The PowerBI module is configured with a data modeling unit, a visualization statistics page generation unit, and a PBIX execution file generation unit; The data modeling unit is connected to the Excel module, associates the production yield statistical data with the PowerBI module, and constructs a data model through the production yield statistical table generated by the Excel module; The visualization statistics page generation unit is connected to the data modeling unit to perform visualization design on the constructed data model; The PBIX execution file generation unit is connected to the visualization statistics page generation unit and the local link unit and the public LAN link unit respectively, generates a PBIX execution file from the received visualization data model, and shares it with the local user terminal through the local link unit and the public LAN link unit.

2. According to claim 1, a real-time production yield monitoring system developed based on PowerBI, It is characterized in that The login user rights deployment and creation unit manages the local user's login rights in the form of a Web address.

3. According to claim 1, a real-time production yield monitoring system developed based on PowerBI, It is characterized in that The automatic refresh unit can be used to set data refresh.

4. According to claim 1, a real-time production yield monitoring system developed based on PowerBI, It is characterized in that The setting of the automatic refresh unit is realized by creating a Windows instruction dat file and a VBA program in an Excel module.