7 Replies Latest reply on Apr 28, 2016 7:31 AM by gregor

    'Calc Failed' with interpolated values

    eradwan

      Hi There,

       

      I'm using the following code to get the hourly values of some calculated tags. the strange behavior is, I get 'Calc Failed' as a value for the more recent dates, while same tags and time frame works fine from Data Link! In the beginning, I thought its a date format issue as I'm using UK culture but that didn't help. Your help here is appreciated. Screenshot attached.

       

       

       

      try

                  {

                      IWorkbook workBook = SpreadsheetExtension.GetCurrentDocument("Spreadsheet");

       

       

       

       

                      Worksheet dataSheet = workBook.Worksheets[0];

       

       

                      AFDatabase eMDatabase = (AFDatabase) System.Web.HttpContext.Current.Application["eMDatabase"];

       

       

                      AFCategory nOxWeeklyEmissions = eMDatabase.AttributeCategories["NOxWeeklyEmissions"];

       

       

                      AFAttributeList nOxAttribList = AFAttribute.FindElementAttributes(eMDatabase,null, "*",null, null, AFElementType.Any, "*", nOxWeeklyEmissions, TypeCode.Empty, true, AFSortField.Name, AFSortOrder.Ascending, 100);

       

       

       

       

                      CultureInfo culture = CultureInfo.CreateSpecificCulture("en-GB");

                      var strDate = fromDate.Substring(0, 24);

                      DateTime startSearchDate = DateTime.ParseExact(strDate, "ddd MMM d yyyy HH:mm:ss", culture);

                      string sDate = startSearchDate.ToString("dd/MMM/yyyy HH:mm:ss",culture);

                      string eDate = (startSearchDate.AddDays(7)).ToString("dd/MMM/yyyy HH:mm:ss", culture);

       

       

                      string[] dates = new string[169];

                      for (int i = 0; i < dates.Length; i++)

                      {

                          dates[i] = startSearchDate.ToString("dd/MM/yyyy HH:mm");

                          startSearchDate = startSearchDate.AddHours(1);

                      }

                      WorksheetExtensions.Import(dataSheet, dates, 5, 0, true);

       

       

                      timeRange = new AFTimeRange(new AFTime(sDate), new AFTime(eDate));

       

       

                      PIPagingConfiguration config = new PIPagingConfiguration(PIPageType.TagCount,100);

       

       

       

       

                      IEnumerable<AFValues> interpolatedValues = nOxAttribList.Data.InterpolatedValues(timeRange, AFTimeSpan.Parse("1h"), null,false, config);

       

       

       

       

                      foreach (var afValues in interpolatedValues)

                      {

                          string[] readingsConverted = new string[afValues.Count];

       

       

                          for (int i = 0; i < afValues.Count; i++)

                          {

                              readingsConverted[i] = afValues[i].ToString();

                          }

       

       

                          AFValue firstRowIndexValue = afValues.Attribute.Attributes["firstRowIndex"].GetValue();

                          int firstRowIndex = Convert.ToInt16(firstRowIndexValue.Value);

       

       

                          AFValue firstColumnIndexValue = afValues.Attribute.Attributes["firstColumnIndex"].GetValue();

                          int firstColumnIndex = Convert.ToInt16(firstColumnIndexValue.Value);

       

       

                         

       

       

                          WorksheetExtensions.Import(dataSheet, readingsConverted, firstRowIndex, firstColumnIndex, true);

       

       

                          Cell cell = dataSheet.Cells[firstRowIndex, firstColumnIndex];

                          Range startCell = cell.GetRangeWithRelativeReference();

       

       

                          for (int i = 1; i < 170; i++)

                          {

                              decimal n;

                              bool isDecimal = decimal.TryParse(startCell.Value.ToString(), out n);

       

       

                              if (isDecimal)

                              {

                                  if (n > 55)

                                  {

                                      startCell.FillColor = System.Drawing.Color.Yellow;

                                  }

                                  else

                                  {

                                      startCell.FillColor = System.Drawing.Color.White;

                                  }

                                  startCell.Value = Math.Round(n, 3).ToString();

                                  startCell.NumberFormat = "0.###";

                              }

                              else

                              {

                                  startCell.FillColor = System.Drawing.Color.OrangeRed;

                              }

       

       

                              startCell = startCell.Offset(1, 0);

                             

                          }

                         

                      }

       

       

                  }

       

       

                  catch (Exception ex)

                  {

                      Logger.Error(ex);

                  }

        • Re: 'Calc Failed' with interpolated values
          bpayne

          Hi Emad,

           

          I have tried your code using the current version of AFSDK but have not seen it fail. Have you verified the raw data is available when you execute the interpolation? You may want to consider opening a support case.

            • Re: 'Calc Failed' with interpolated values
              eradwan

              Hello Butch,

              After some troubleshooting, I found that the problem is my code is reading from server A in the collection while Data Link reads from the another server; when I did 'Switch Member' while in Data Link, I got the same results as of the above code. I checked with our PI admin who explained to me that data is transferred from 2 API nodes to both PI servers and it happens sometime that something wrong may happen that leads to invalid data on on of the servers specially that we're talking about calculated  tags. Now, what is the best recommendations on this regard specially that 'bad readings' is a valid sometimes due to shutdown or similar situation.

                • Re: 'Calc Failed' with interpolated values
                  bpayne

                  I don't know if there is a best practice on how to handle bad values/readings. You can do a search on our tech support site and find multiple solutions that customers have come up with to handle bad values. It all depends on the data, where it comes from and what you want to do with it.  Also, you would want to check the PercentGood attribute of the returned value, to make sure the calculation result is suitable for you to use.

                  • Re: 'Calc Failed' with interpolated values
                    gregor

                    Hello Emad,

                     

                    I would suggest using PI Buffer Subsystem for buffering and fanning against your PI Data Archive Collective.

                      • Re: 'Calc Failed' with interpolated values
                        eradwan

                        Will that help in readings as well? As all examples I saw for this were for updating values only.

                          • Re: 'Calc Failed' with interpolated values
                            gregor

                            Hello Emad,

                             

                            PI Buffer Subsystem maintains a separate queue for each member of a PI Data Archive Collective. It takes care for the compression and send events as compressed. Even if there are temporary connection issues against one of the PI Data Archive Collective nodes, tag data is supposed being received from the queue. I wouldn't know a better mechanism to ensure all members of a PI Data Archive Collective are having the same events archived rather than setting up each data source with PI Buffer Subsystem.

                            I am a little uncertain what your question for the readings refers to but readings are not going through PI Buffer Subsystem but having PI Buffer Subsystem in place to serve all Collective members with the same data is the basis for reliable readings, independent of what PI Data Archive Collective member is servicing the particular reading.