OEE Management with Navision

2014, we use RedZone (rzsoftware.com) OEE Management product, they build in iPad App to collect PLC data from production machine, after 1 year running, we found

1.       Data need to manually to import or export with our MS Navision Database.

2.       Maintenance fee is too high, especial we need to modify the report.

3.       Operation pages are not customizable per the process.

So Chris Asby and I use PLC + Touch Screen to build our own “Production Data Acquisition System” (PDAS).

1.       Industrial waterproof HMI’s with input/output boards are located on the production floor at predetermine locations.  Inputs are hardwired from the production machinery to these inputs to capture data from machine cycles, case counters, and any other production data that is needed.  Each HMI also has an external alarm light to notify employees that the HMI’s need attention (Log downtime or any alarms setup in the system). 

2.       HMI’s have a built in webserver and can have custom HTML websites to show live production data from any web browser.  They are also capable of communicating directly with many PLC’s on the market via many different communication protocols.

3.       All HMI’s are hardwired to the plant Ethernet network and all communicate with each other via Modbus TCP.  User login id’s restrict access to certain pages in the system or allow a supervisor to view & change any operation page from any panel in the system.

4.       Everyday 5:30am, SQL Job agent grab production order with production Line code from Navision to PDAS server.  This information also includes target rates, target OEE, cases to produce and other values used in the PDAS system to determine production line rate & performance.

5.       PLC imports this data into each HMI (Typically 2 per production line) as a production schedule for each line.

6.       Production foreman follow up production order that are list in touch screen to start and end button to record this order start & end time.

7.       PLC system will track downtime per OEE sub-category (Performance, Availability, Quality) and enforce logging of the reason for outage for all downtime per item number that is running in production.

8.       Reason for outage (RFO) lists are maintained in CSV format and sync with the panels daily.  This limits the items available to be selected by the operators.  RFO tables are unique to the OEE category as well as the production line.  All panels are capable of sending emails and the operators can send emails requesting an RFO be added to their list.

9.       Alarm or alert emails can be automatically generated and sent based on any “tag” information inside the PDAS.  For example if a line is down for xx minutes an email can be sent out to a preselected group of recipients letting them know that the line is down for xx minutes or the OEE score is below target, etc.

10.   Everyday 5:00 AM, PLC system will export the previous day’s records to PDAS server (CSV file format) then SQL Job Agent import into Navision database. 

11.   SQL SSAS server update data by schedule then export to SharePoint Business Intelligence services.

12.   User use browser to view BI in SharePoint PDAS site.

Our PDAS system detail information is this:

1.       What is OEE anyways?

a.       OEE is an acronym for Overall Equipment Effectiveness and is made up of 3 elements. Overall equipment effectiveness quantifies how well a manufacturing unit performs relative to its designed capacity, during the periods when it is scheduled to run. OEE allows us to easily measure and communicate equipment utilization against standards.

b.       OEE = Performance x Availability x Quality

c.        Performance = (Parts Produced*Ideal Cycle Time)/Operating Time

d.       Example: Multivac has produced 3,500 cycles over 8 hours of production with 1 hour of downtime. Target rate is 10 cycles per minute. (Performance = (3,500*(10/60=0.1667 minutes per cycle)/(8 hours minus 1 hour of DT=7 hours*60=420 minutes) so… Performance = (3,500*0.1667)/7 which equates to 83%

e.       Note: The “Operating Time” equals time that the machine is actually running, anytime the machine output is 0 (Stopped) is accounted for in the availability element and does not affect the performance element

f.         Availability= Uptime / Available Time

g.        Example: The Multivac has just completed an 8 hour run without change overs. Film changes and waiting for product have caused one hour of accumulated down time during this shift making the total uptime equal to 7 hours.

h.       Availability = 7 hours / 8 hours which equates to 87.5%

i.         Quality = (Units Produced – Defective Units) / Units Produced

j.         Example: The Multivac cycled 3,500 times during this shift which equated to 14,000 units (3,500 x 4) of retail product. At the end of the shift the total case count was 2,000 cases which at 6 units per case equates to 12,000 units. This means there was a 2,000 case loss during the run.

k.        Quality = (14,000-2,000) / 14,000 which equates to 85.7%

l.         The examples above would then be combined to calculate the Multivac OEE as follows:

m.     Multivac OEE = Performance (83%) x Availability (87.5%) x Quality (85.7%) which equates to 62.2% OEE

2.       Touch Screen Navigation Tree example (pages are unique to the input type). Pic1

3.     Table structure. Pic2

4.       Data example. Pic3

5.       SSAS measure & Dimension example. Pic 4

How to import Excel's Salrs Budget into Navision

In Navision, we use dimension "Region","SalesCode" and "CustomerCode". if we use Navision import function, it's difficult . and Excel format for Sales is more easier. but excel format is different with Navision. so I tried to use stored procedures to convert format from column to row.

1. Original Excel Format. Fig1

2. Import Excel File into table of VFFSalesBudgetRawIn by SSMS . Fig2 & 3

3. After import, use select command to check. Fig4

4. Run Stored Procedures to convert data format from columns to rows table of  VFFSalesBudgetOut

USE [VFFnav2013]

GO

/****** Object: Convert the table of dbo.VFFSalesBudgetRawIn to dbo.VFFSalesBudgetOut, then copy & Paste into Navision ****/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Delete from dbo.[VFFSalesBudgetOut]

Go

Declare        @id                 int,

@Item                Char(20),

@Region        Char(20),

@SalesCode        Char(10),

@Customer       Char(10),

@M1                Decimal(18,4),

@M2                Decimal(18,4),

@M3                Decimal(18,4),

@M4                Decimal(18,4),

@M5                Decimal(18,4),

@M6                Decimal(18,4),

@M7                Decimal(18,4),

@M8                Decimal(18,4),

@M9                Decimal(18,4),

@M10                Decimal(18,4),

@M11                Decimal(18,4),

@M12                Decimal(18,4),

@M1Q                Decimal(18,4),

@M2Q                Decimal(18,4),

@M3Q                Decimal(18,4),

@M4Q                Decimal(18,4),

@M5Q                Decimal(18,4),

@M6Q                Decimal(18,4),

@M7Q                Decimal(18,4),

@M8Q                Decimal(18,4),

@M9Q                Decimal(18,4),

@M10Q        Decimal(18,4),

@M11Q        Decimal(18,4),

@M12Q        Decimal(18,4),

@Descrip        Char(100),

@UPrice        Decimal(18, 4),

@IDCount        Int

Set        @id = 1

Set        @M1 = 0

set        @M2 = 0

Set        @M3 = 0

Set         @M4 = 0

Set        @M5 = 0

Set        @M6 = 0

set        @M7 = 0

Set        @M8 = 0

Set         @M9 = 0

Set        @M10 = 0

Set         @M11 = 0

Set        @M12 = 0

Set        @M1Q = 0

set        @M2Q = 0

Set        @M3Q = 0

Set         @M4Q = 0

Set        @M5Q = 0

Set        @M6Q = 0

set        @M7Q = 0

Set        @M8Q = 0

Set         @M9Q = 0

Set        @M10Q = 0

Set         @M11Q = 0

Set        @M12Q = 0

Set         @UPrice = 0

Select @IDCount = Max(IDNo) from dbo.[VFFSalesBudgetRawIn]

WHILE @id <= @IDCount

Begin

Select         @Item = Item,         @Region = Region, @SalesCode = SalesCode, @Customer = Customer

@M1 = M1Amt, @M2 = M2Amt, @M3 = M3Amt, @M4 = M4Amt, @M5 = M5Amt, @M6 = M6Amt, @M7 = M7Amt, @M8 = M8Amt, @M9 = M9Amt, @M10 = M10Amt, @M11 = M11Amt, @M12 = M12Amt,

@M1Q = M1Qty, @M2Q = M2Qty, @M3Q = M3Qty, @M4Q = M4Qty, @M5Q = M5Qty, @M6Q = M6Qty, @M7Q = M7Qty, @M8Q = M8Qty, @M9Q = M9Qty, @M10Q = M10Qty, @M11Q = M11Qty, @M12Q = M12Qty,

@Descrip = Description, @UPrice = Price

From         dbo.[VFFSalesBudgetRawIn]

where IDNo = @id

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, @Customer, '1/1/2015', @M1, @M1Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, @Customer, '2/1/2015', @M2, @M2Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '3/1/2015', @M3, @M3Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '4/1/2015', @M4, @M4Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '5/1/2015', @M5, @M5Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '6/1/2015', @M6, @M6Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '7/1/2015', @M7, @M7Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '8/1/2015', @M8, @M8Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '9/1/2015', @M9, @M9Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '10/1/2015', @M10, @M10Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, '11/1/2015', @M11, @M11Q, @Descrip, @UPrice)

          INSERT INTO [dbo].[VFFSalesBudgetOut]

                   (Item, Region, SalesCode, Customer, Month, MonthAmt, MonthQty, Descrip, UPrice)

        VALUES        (@Item, @Region, @SalesCode, Customer, '12/1/2015', @M12, @M12Q, @Descrip, @UPrice)

          Set @id = @id + 1

Print @id

End

5. Use Select command to check. Fig5

6. Copy All data and paste into excel ;Fig6

7. In Excel, Add 1st column to SALES2017

8. Open Navision then open Sales Budget >> New >> ; Fig7

9. Select "SALES2017" and click "Edit Budget", then click the Ellipsis icon that show Fig 8

A. Click "New" in "Item Budget Entries" page, select 1st line and right button . click "Paste Rows" Fig9

B. One time copy/paste 2000 records is better.

如何在Win Server 2012R2上面去裝SharePoint 2013

1. 首先先建立一個帳號有local admin的權限。在本例中為sp_admin
2. 有兩台伺服器分別是DBSVR & APSVR。資料庫是安裝在DBSVR(WIN SERVER 2012R2&SQL 2012 SP2).而SP2013則會裝在APSVR(WIN SERVER 2012R2)
3. 下載以下的程式分別置放於APSVR c:\STEP1&STEP2
4. Step1.
1. 1-sqlncli11_SQL2012
2. 2-WcfDataServices
3. 3-Setup_Msipc_x64
4. 4-Synchronization
5. 5-MicrosoftIdentityExtensions-64
5. Step2
1. AppFabric1.1-RTM-KB2671763-x64-ENU
2. WindowsServerAppFabricSetup
6. 使用Admin的身份開啓Windows Feature包括iis 以及App server(可從Sevrve manager的Add Role and Feature)安裝這功能
7. cd到step1的目錄後一一執行該目錄下的程式.
8. 完成後重開機
9. 使用Admin的身份開啓Window Powershell.再切換到step2目錄.
10. 執行下列命令去組態Appfabric.
11. .\windowserverappfabricsetup_x64.exe /i cacheclient "," cachingservice "," cacheadmin /gac
12. 等一段時間後程式執行完成後再執行KB2671763.
13. 重開機後即可安裝share point 2013

install OneNote 2013 in Win Server 2012 R2

After installed the Office 2013 in Windows server 2012 R2, we can run oneNote 2013 in this server, but when we open the OneNote, system show error that we need to install "Desktop Experience" , feature, but this feature does not show in feature.what OneNote is looking for is "Ink and Handwriting Services ". after install this feature, need to restart .

在win8.1下安裝Hyper-V AD server 以便 SharePoint 2013 可在同一台PC上運行

買了一部 Surface Pro 3, 8G Ram + 256G SSD. 因此希望在這台機器上建立一個模擬的Domain + Client. 安裝步驟如下
1. 開啟 WiFi 的設定, 將 "Sharing" 啟動.因此 Hyper-V server 可以設為一個固定的 IP 以便AD Server 使用.
2. 開啟 Hyper-V Setting,在 Virtual Switch Manager 下產生一個 "Internal only" 的網路.此時 Win 8.1 會有一個 vEthernet 生成.並內定為 192.168.137.1
3.在 vEthernet TCP/IPv4 的 DNS 部分需指定為 192.168.137.*, 此處的 * 需與
Hyper-V AD server 的 IP 一致.
4. 開啟 Hyper-V 安裝 Windows 2008 R2 server & SP1 (試過安裝 WinSVR 2012, 但安裝 SP2013 有問題) .
5. 安裝 AD, DNS 等在此 Hyper-V server. 並指定 IP 為 192.168.137.*然後建立DOMAIN, 例如 Contoso.com from dos command "dcpromo".
6. 安裝 SQL 2012 & SP1.
7. 安裝 SharePoint 2013 + SP1
8. 在此AD server DNS 須設定正向及反向的解析.
9.

10. 回到 Win 8.1開啟 IE, 輸入 portal.contoso.com or my.contoso.com 成功.

SharePoint 2013 新設的網站無法登入

1. 參考 http://kumarcblog.blogspot.com/2012/12/cannot-login-to-sharepoint-2013-site.html
2. 當我依照 "Professional SharePoint 2013 Administration"(出版商WROX)的例子建立起新的網站 portal.contoso.com 並且也在 DNS建立 Host, 但是在IE下輸入三次login name & password,IE 顯示一個空白的網頁.
3. 參考 step 1 的方式,發現我應該在 Registry 作一些修改.
4. 使用 regedit to open Registry.
5. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
6. 新增 "Multi-String" registy key, 並輸入名稱為 "BackConnectionHostNames".
7. 開啟modify 輸入新建網站名稱.

8. 開啟IE後即可登入新設的網站.

9. http://support.microsoft.com/kb/896861 .

SharePoint 2013 安裝 Search Application Service

1. 我在安裝SharePoint 2013 的 Search Application Service 時若是使用安裝精靈的話,資料庫會有GUID在DB Name 後端.非常不方便.因此使用指令的方式會清爽多了.
2. Open SharePoint 2013 Management Shell and type in or paste these command.
3. Copy from Todd Klindt Blogger .http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=378
4. # Based on scripts at http://www.harbar.net/articles/sp2013mt.aspx

# Thanks Spence!

# Get App Pool
$saAppPoolName = "Default SharePoint Service App Pool"

# Search Specifics, we are single server farm
$searchServerName = (Get-ChildItem env:computername).value
$serviceAppName = "Search Service Application"
$searchDBName = "SearchService_DB"


# Grab the Appplication Pool for Service Application Endpoint
$saAppPool = Get-SPServiceApplicationPool $saAppPoolName

# Start Search Service Instances
Write-Host "Starting Search Service Instances..."
Start-SPEnterpriseSearchServiceInstance $searchServerName
Start-SPEnterpriseSearchQueryAndSiteSettingsServiceInstance $searchServerName

# Create the Search Service Application and Proxy
Write-Host "Creating Search Service Application and Proxy..."
$searchServiceApp = New-SPEnterpriseSearchServiceApplication -Name $serviceAppName -ApplicationPool $saAppPoolName -DatabaseName $searchDBName
$searchProxy = New-SPEnterpriseSearchServiceApplicationProxy -Name "$serviceAppName Proxy" -SearchApplication $searchServiceApp

# Clone the default Topology (which is empty) and create a new one and then activate it
Write-Host "Configuring Search Component Topology..."
$clone = $searchServiceApp.ActiveTopology.Clone()
$searchServiceInstance = Get-SPEnterpriseSearchServiceInstance
New-SPEnterpriseSearchAdminComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance
New-SPEnterpriseSearchContentProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance
New-SPEnterpriseSearchAnalyticsProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance 
New-SPEnterpriseSearchCrawlComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance 
New-SPEnterpriseSearchIndexComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance
New-SPEnterpriseSearchQueryProcessingComponent –SearchTopology $clone -SearchServiceInstance $searchServiceInstance
$clone.Activate()

5. after running, check SQL DB name.