6 Replies Latest reply on Nov 11, 2010 11:59 PM by 812643

    API Help -- Extract method results in Catstrophic failure!

    812643
      Hi all,

      We're trying to implement the HFM api to pull data out into a CSV. When we run HsvData.Extract, the call returns an exception:

      Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))

      I'm not sure what this means and I can't find any help on the 'net. Our extract call looks like:

      hsvLoad.Extract(dataFilePath, logFilePath, settings);

      settings is an object array that looks like this before we call Extract:
      settings {object[20]}
      [0] 2
      [1] ";"
      [2] "LogFile.log"
      [3] false
      [4] false
      [5] false
      [6] 1
      [7] 2009
      [8] int[12] //selected periods, 67108864 - 67108875 (Jan - Dec)
      [9] int[2] // selected entities, 0 and 1
      [10] int[1] //selected parents, 0
      [11] int[4] //selected accounts 0 - 3
      [12] int[0]
      [13] int[0]
      [14] int[0]
      [15] int[0]
      [16] int[0]
      [17] int[0]
      [18] false
      [19] false

      items 12 - 19 are default, we don't change them. The data file is empty and the log file says "0 records extracted". If I try to Extract from the UI selecting the same items (accounts, years, application, etc), I get the full results.

      Can anyone tell me what the Catastrophic failure means? Are some of our settings mapped wrong? I can post additional code if that helps.

      Thanks,
      Shawn
        • 1. Re: API Help -- Extract method results in Catstrophic failure!
          WPaffhausen
          Hello,

          Have you tried the extract from the HFM Win32 client installed on the FDM Server?
          Maybe it isn't an FDM issue and it is instead an HFM issue and needs to be redirected?

          Thank you,
          • 2. Re: API Help -- Extract method results in Catstrophic failure!
            812643
            I believe we're trying that too (that would be HsvcDataLoad.Extract()?)

            We're essentially passing the same parameters as far as accounts, periods using HsvcDataLoad.ExtractOptions. This method doesn't result in any exceptions, but we don't get any data back. The target data file is empty and the log file says '0 Records'.
            • 3. Re: API Help -- Extract method results in Catstrophic failure!
              David Debish
              Hi Shawn,

              Could you post the API?

              -D2
              • 4. Re: API Help -- Extract method results in Catstrophic failure!
                812643
                Yup (in C#)
                (first one that throws the catastrophic failure, there are some helper methods that i'm not including, I can add those if necessary. Application is already connected at this point. )
                public bool TryExportDataFromHFMToAppServer(string dataFilePath, string logFilePath, string scenLabel, int year, out string error)
                {
                bool retval = false;
                error = string.Empty;
                HSVDATALib.HsvData hsvLoad = null;

                try
                {
                if (string.IsNullOrEmpty(scenLabel))
                {
                error = NO_SCENARIOS_SPECIFIED_MSG;
                return false;
                }

                if (year < 0)
                {
                error = NO_YEARS_SPECIFIED_MSG;
                return false;
                }

                int currScenMemberId;
                if (!TryGetMemberIdFromLabel((short)HFMCONSTANTSLib.tagHFMDIMENSIONS.DIMENSIONSCENARIO, scenLabel, out currScenMemberId, out error))
                {
                error = string.Format("Failed to find member Id for Scenario: {0}", scenLabel);
                return false;
                }

                int currYearMemberId;
                if (!TryGetMemberIdFromLabel((short)HFMCONSTANTSLib.tagHFMDIMENSIONS.DIMENSIONYEAR, year.ToString(), out currYearMemberId, out error))
                {
                error = string.Format("Failed to find member Id for Year: {0}", year.ToString());
                return false;
                }

                hsvLoad = new HSVDATALib.HsvData();

                object[] settings;
                if (!TryGetExtractOptions(out settings, out error))
                {
                error = string.Format("Failed to get settings: {0}", error);
                return false;
                }
                settings[6] = currScenMemberId;
                settings[7] = currYearMemberId;
                Dictionary<string, int> dictPerMemIds;
                if (!TryGetAllPeriodLabelsMemberIds(out dictPerMemIds, out error))
                {
                error = string.Format("Failed to get member Ids for periods. Error:{0}", error);
                return false;
                }

                //limit to just the 'months', testing
                List<int> periodList = new List<int>();
                foreach (string key in dictPerMemIds.Keys)
                {
                if (key.Equals("[Year]") || key.StartsWith("Quarter"))
                {
                continue;
                }
                else
                periodList.Add(dictPerMemIds[key]);
                }

                settings[8] = periodList.ToArray();
                Dictionary<string, int> dictEntLblMemId;
                if (!TryGetAllEntityLabelsMemberIds(out dictEntLblMemId, out error))
                {
                error = string.Format("Failed to get member Ids for entities. Error: {0}", error);
                return false;
                }

                // changed to 11 from 10 - testing
                settings[9] = dictEntLblMemId.Values.ToArray();

                Dictionary<string, int> dictAccntLabelMemberIds;
                if (!TryGetAllAccountLabelsMemberIds(out dictAccntLabelMemberIds, out error))
                {
                error = string.Format("Failed to get member Ids for accounts. Error: {0}", error);
                return false;
                }

                //set parent
                int[] parents = new int[1] { 0 };
                settings[10] = parents;

                //changed to 12 from 11
                settings[11] = dictAccntLabelMemberIds.Values.ToArray();

                hsvLoad.Extract(dataFilePath, logFilePath, settings);
                retval = true;
                }
                catch (Exception ex)
                {
                error = string.Format("Failed to export data to file from HFM. Error: {0}", ex.Message);
                retval = false;
                }
                finally
                {
                ReleaseCOMObject(hsvLoad);
                }
                return retval;

                private bool TryGetMemberIdFromLabel(short memberIdType, string memberLabel,out int memberId,out string error)
                {
                bool retval = false;
                memberId = -1;
                error = string.Empty;
                HSVMETADATALib.HsvMetadata oHsvMetadata = null;
                HSVMETADATALib.IHsvTreeInfo oHsvTreeInfo = null;
                try
                {
                oHsvMetadata = (HSVMETADATALib.HsvMetadata)_oHsvSession.Metadata;
                //use accessor method for Dimension
                oHsvTreeInfo = (HSVMETADATALib.IHsvTreeInfo)oHsvMetadata.get_Dimension(memberIdType);

                public bool TryGetExtractOptions(out object[] settings, out string error)
                {
                bool retval = false;
                error = string.Empty;
                HSVDATALib.HsvData hsvLoad = null;
                settings = new object[20];
                try
                {
                hsvLoad = new HSVDATALib.HsvData();
                object options;
                hsvLoad.EnumExtractOptions(out options);
                System.Array optionArray = (Array)options;

                for (int i = optionArray.GetLowerBound(0); i < optionArray.GetUpperBound(0); i++)
                {
                //settings[i] = optionArray.GetValue(i, 2);
                settings[i - 1] = optionArray.GetValue(i, 2);
                }
                retval = options != null;
                }
                catch (Exception ex)
                {
                error = ex.Message;
                retval = false;
                }
                finally
                {
                ReleaseCOMObject(hsvLoad);
                }
                return retval;
                }

                memberId = oHsvTreeInfo.GetItemID(memberLabel);
                retval = memberId >= 0;
                }
                catch (Exception ex)
                {
                error = ex.Message;
                retval = false;
                }
                finally
                {
                ReleaseCOMObject(oHsvMetadata);
                ReleaseCOMObject(oHsvTreeInfo);
                }
                return retval;
                }
                • 5. Re: API Help -- Extract method results in Catstrophic failure!
                  812643
                  Here's the method that just doesn't return data:

                  public bool TryExportDataFromHFM(string dataFilePath,string logFilePath, string scenLabel,int year, out string error)
                  {
                  bool retval = false;
                  error = string.Empty;
                  HSVCDATALOADLib.HsvcDataLoad oHsvcDataLoad = null;
                  HSVCDATALOADLib.IHsvLoadExtractOptions oHsvcDataLoadExtOptions = null;
                  HSVCDATALOADLib.IHsvLoadExtractOption oExtractOption = null;
                  try
                  {
                  if (string.IsNullOrEmpty(scenLabel))
                  {
                  error = NO_SCENARIOS_SPECIFIED_MSG;
                  return false;
                  }

                  if (year < 0)
                  {
                  error = NO_YEARS_SPECIFIED_MSG;
                  return false;
                  }

                  int currScenMemberId;
                  if (!TryGetMemberIdFromLabel((short)HFMCONSTANTSLib.tagHFMDIMENSIONS.DIMENSIONSCENARIO, scenLabel, out currScenMemberId, out error))
                  {
                  error = string.Format("Failed to find member Id for Scenario: {0}", scenLabel);
                  return false;
                  }

                  int currYearMemberId;
                  if (!TryGetMemberIdFromLabel((short)HFMCONSTANTSLib.tagHFMDIMENSIONS.DIMENSIONYEAR, year.ToString(), out currYearMemberId, out error))
                  {
                  error = string.Format("Failed to find member Id for Year: {0}", year.ToString());
                  return false;
                  }

                  oHsvcDataLoad = new HSVCDATALOADLib.HsvcDataLoad();

                  //set session
                  oHsvcDataLoad.SetSession(_oHsvSession);

                  //get extract options
                  oHsvcDataLoadExtOptions = oHsvcDataLoad.ExtractOptions;

                  //get extract option for scenario
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_SCENARIO_SUBSET);
                  //set scenarion member id
                  oExtractOption.CurrentValue = currScenMemberId;

                  //get extract option for year
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_YEAR_SUBSET);
                  oExtractOption.CurrentValue = currYearMemberId;

                  //get extract option for periods
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_PERIOD_SUBSET);

                  //COMMENTED OUT FOR TEST:
                  Dictionary<string, int> dictPerMemIds;
                  if (!TryGetAllPeriodLabelsMemberIds(out dictPerMemIds, out error))
                  {
                  error = string.Format("Failed to get member Ids for periods. Error:{0}", error);
                  return false;
                  }
                  //set currentValue with array of perion member ids
                  //int[] perArr = new int[] { dictPerMemIds["May"] };
                  //oExtractOption.CurrentValue = perArr; // periodMemberIds.ToArray();


                  //NEW TEST CODE
                  List<int> periods = new List<int>();
                  foreach (string key in dictPerMemIds.Keys)
                  {
                  if (key.Equals("[Year]") || key.StartsWith("Quarter"))
                  {
                  continue;
                  }
                  else
                  {
                  periods.Add(dictPerMemIds[key]);
                  }
                  }

                  oExtractOption.CurrentValue = periods.ToArray();

                  //get extract option for accounts
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_ACCOUNT_SUBSET);

                  Dictionary<string, int> dictAccntLabelMemberIds;
                  if (!TryGetAllAccountLabelsMemberIds(out dictAccntLabelMemberIds,out error))
                  {
                  error = string.Format("Failed to get member Ids for accounts. Error: {0}", error);
                  return false;
                  }

                  //int[] acctArr = new int[] { dictAccntLabelMemberIds["SalesRevenue"] };
                  //oExtractOption.CurrentValue = acctArr; // dictAccntLabelMemberIds.Values.ToArray();
                  oExtractOption.CurrentValue = dictAccntLabelMemberIds.Values.ToArray();

                  //Sets the entity children
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_ENTITY_SUBSET);

                  Dictionary<string, int> dictEntLblMemId;

                  if (!TryGetAllEntityLabelsMemberIds(out dictEntLblMemId,out error))
                  {
                  error = string.Format("Failed to get member Ids for entities. Error: {0}", error);
                  return false;
                  }
                  //int[] entArr = new int[] { dictEntLblMemId["Corporate"] };
                  //oExtractOption.CurrentValue = entArr; // dictEntLblMemId.Values.ToArray();
                  oExtractOption.CurrentValue = dictEntLblMemId.Values.ToArray();

                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION. HSV_DATAEXTRACT_OPT_VIEW);
                  oExtractOption.CurrentValue = HSVCDATALOADLib.HSV_DATA_VIEW.HSV_DATA_VIEW_YTD; //HSV_DATA_VIEW_PERIODIC; // HSV_DATA_VIEW_SCENARIO;

                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(
                  HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_EXTRACT_CALC);
                  oExtractOption.CurrentValue = true;

                  //Sets the delimiter
                  oExtractOption = oHsvcDataLoadExtOptions.get_Item(HSVCDATALOADLib.HSV_DATAEXTRACT_OPTION.HSV_DATAEXTRACT_OPT_DELIMITER_CHAR);
                  oExtractOption.CurrentValue = ";";

                  oHsvcDataLoad.Extract(dataFilePath, logFilePath);
                  retval = true;
                  }
                  catch (Exception ex)
                  {
                  error = string.Format("Failed to export data to file from HFM. Error: {0}", ex.Message);
                  retval = false;
                  }
                  finally
                  {
                  ReleaseCOMObject(oHsvcDataLoad);
                  ReleaseCOMObject(oHsvcDataLoadExtOptions);
                  ReleaseCOMObject(oExtractOption);
                  }
                  return retval;
                  }

                  Thanks for the help
                  • 6. Re: API Help -- Extract method results in Catstrophic failure!
                    812643
                    So I've simplified the code quite a bit in an effort to track down the issue:

                    public bool TryExport(string dataFilePath, string logFilePath, string scenLabel, long year, out string error)
                    {
                    object[] settings = new object[0];
                    bool retVal = true;
                    error = string.Empty;

                    HSVDATALib.HsvData hsvLoad = null;

                    try
                    {
                    if (!TryBuildSettingsArray(year, out settings, out error))
                    {
                    retVal = false;
                    return retVal;
                    }

                    hsvLoad = new HSVDATALib.HsvData();
                    hsvLoad.Extract(dataFilePath, logFilePath, settings);

                    }
                    catch (Exception ex)
                    {
                    retVal = false;
                    }

                    finally
                    {
                    ReleaseCOMObject(hsvLoad);
                    }

                    return retVal;
                    }


                    private bool TryBuildSettingsArray(long year, out object[] settings, out string error)
                    {
                    settings = new object[0];
                    bool retval = true;
                    error = string.Empty;

                    try
                    {
                    //defaults
                    TryGetExtractOptions(out settings, out error);

                    //Delimiter
                    settings[1] = ";";

                    //Append to log file
                    settings[3] = false;

                    //Scenario
                    settings[6] = getMemberID(0, "ACTUAL", out error);

                    //year
                    settings[7] = getMemberID(1, year.ToString(), out error);

                    //period subset
                    settings[8] = getMemberID(2, "JULY", out error);

                    //entity
                    settings[9] = getMemberID(4, "CORPORATE", out error);

                    //account subset
                    settings[11] = getMemberID(6, "REVENUE", out error);
                    }
                    catch (Exception ex)
                    {
                    retval = false;
                    error = ex.Message;
                    }
                    return retval;
                    }

                    However, this thing STILL hands me back the same catastrophic failure when I get to the Extract method. I'm at a loss here. I thought that cleaning up the settings array to the bare essentials may help, but it didn't.