BAM fact table not created correctly during environment deployment

Hi,

I am trying to set up an Optimize environment and I’m running into some trouble when trying to Deploy a new environment in myWebmethods. Here are the steps that I do:

  1. Install webMethods product suite 7.1.2, IS, myWebmethods, Optimize, Broker, Central Configurator etc.
  2. Run dbConfigurator and create ALL tables using version 7.1.2
  3. Configure the Broker Server and Broker in myWebmethods
  4. Configure the WmOptimize package by going into the IS Administrator->Packages->management->WmOptimize homepage and then submit the default Broker URL.
  5. Create DB Pool in myWebmethods under Administration->System-Wide->Environments->Database Pool Configuration.
  6. Define the environment in myWebmethods under Administration->System-Wide->Environments->Define Environments with the settings described in the “Central Configuration and Deployment Guide 7.1-4”
  7. I deploy this environment and myWebmethods shows that deployment was successfull after I hit the deploy button.

Here is where I start getting errors in the Analytic Engine error log. Here are some of the errors I get:

COLOR=Blue [AE.EventMapHandler.evntMapHldrPreDeployError] 2009-09-09 16:15:16.578 CEST ERROR: Error pre-deploying event map AnalyticEngineLoad
(OPTIMIZE) [AE.EventMapHandler.evntMapHldrPreDeployError] 2009-09-09 16:15:16.578 CEST ERROR: java.sql.SQLException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-00955: name is already used by an existing object

FONT=Courier New [AE.EventMapHandler.evntMapHldrPreDeployError] 2009-09-09 16:15:17.703 CEST ERROR: Error pre-deploying event map AnalyticEnginePersistence
(OPTIMIZE) [AE.EventMapHandler.evntMapHldrPreDeployError] 2009-09-09 16:15:17.703 CEST ERROR: java.sql.SQLException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-00955: name is already used by an existing objec[/font]

FONT=Courier New [AE.TransactionWrapper.UnknownKey] 2009-09-09 16:18:17.296 CEST ERROR: Aborting transaction due to exception
(OPTIMIZE) [AE.TransactionWrapper.UnknownKey] 2009-09-09 16:18:17.296 CEST ERROR: com.wm.dd.jdbc.base.BaseBatchUpdateException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-00904: “PERSISTENCEQUAL_DIM”: invalid identifier[/font]

FONT=Courier New [AE.AggregationPopulator.UnknownKey] 2009-09-09 17:15:01.515 CEST ERROR: java.sql.SQLException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-00904: “FACT”.“CONNECTION_STAT_FACT”: invalid identifier[/font]

FONT=Courier New [AE.Query.UnknownKey] 2009-09-09 16:25:40.296 CEST ERROR: Unable to perform query: sql: SELECT BAM_FACT_ANALYTI_V1.Weight_FACT, ‘1’ AS Weight_FACT_INTERVAL, ‘1’ AS Weight_FACT_COUNT,BAM_DIM_ANALYTI_V1.LogicalServerName_COL AS BAM_DIM_ANALYTI_V1_Analyt,BAM_DIM_ANALYTI_V1.BAM_DIM_ANALYTI_V1_id,BAM_FACT_ANALYTI_V1.entry_time AS TIME,BAM_DIM_INTERNA_V1.Name_COL AS BAM_DIM_INTERNA_V1_Intern,BAM_DIM_INTERNA_V1.BAM_DIM_INTERNA_V1_id FROM AM_DIM_INTERNA_V1,BAM_FACT_ANALYTI_V1,BAM_DIM_ANALYTI_V1 WHERE BAM_FACT_ANALYTI_V1.Analytic_Engine_DIM = BAM_DIM_ANALYTI_V1.BAM_DIM_ANALYTI_V1_id AND BAM_FACT_ANALYTI_V1.ENTRY_TIME < ? AND BAM_FACT_ANALYTI_V1.ENTRY_TIME >= ? AND [/font]

BAM_FACT_ANALYTI_V1.InternalMonitor_DIM = BAM_DIM_INTERNA_V1.BAM_DIM_INTERNA_V1_id, bind variables: [1252506300000, 1252506000000] for EventQuery{dimensionalPath=HierarchySetting{dimensionName=‘InternalMonitorQualifier’, dimensionVersion=1, dimensionAttributeName=‘Name’, children=’[HierarchySetting{dimensionName=‘AnalyticEngine’, dimensionVersion=1, dimensionAttributeName=‘LogicalServerName’, children=’[]’, left=‘null’}]’, left=‘null’}, queryInterval=QueryTime{startDate=Wed Sep 09 16:20:00 CEST 2009, endDate=Wed Sep 09 16:25:00 CEST 2009, aggregate=true, interval=300000}, queryables=[Weight], queryFilters=[], aggregation
support=NONE}

FONT=Courier New [AE.Query.UnknownKey] 2009-09-09 16:25:40.296 CEST ERROR: java.sql.SQLException: [wm-cjdbc36-0007][Oracle JDBC Driver][Oracle]ORA-00904: “BAM_FACT_ANALYTI_V1”.“WEIGHT_FACT”: invalid identifier[/font]

[/COLOR]These errors regarding invalid identifier are thrown every 8 seconds.

Something is clearly wrong with the BAM_FACT_ANALYTI_V1 table. The event maps AnalyticEngineLoad and AnalyticEnginePersistence are not deployed correctly but I can see that the events exist in myWebmethods under Administration->Analytics->KPIs->System data.

When looking into BAM_FACT_ANALYTI_V1 I can see that the columns WEIGHT_FACT, CONNECTION_STAT_FACT, PERSISTENCEQUAL_DIM are missing which is also indicated by the error message. The entire BAM_FACT_ANALYTI_V1 table is unpopulated.

I have tried a fresh product install and DB install and I always run into the same errors. Any suggestions to what I should do?

I should mention that I have been using the same database user (SYSTEM) throughout the entire product suite installation without specifying any DB schema.

I tried this installation on two virtual machines, one running WindowsXP+OracleXE 10G and the other one with CentOS(Red Hat)+OracleXE 10G. Same error on both.

Any ideas on what I could have missed or how the BAM_FACT_ANALYTI_V1 can be created successfully after an Environment deployment? When looking into other webMethods installations (which were’nt made by me), I can see that the columns WEIGHT_FACT, CONNECTION_STAT_FACT and PERSISTENCEQUAL_DIM dont exist in BAM_FACT_ANALYTI_V1 but they do exist in BAM_FACT_ANALYTI_V11, BAM_FACT_ANALYTI_V12 BAM_FACT_ANALYTI_V13 which are tables that I dont have on my installation.

Best regards

Hi Rek,

I am also facing the same problem.Could you please let me know how could you resolve he issue.

Thanks for your help.

Maddy

Hi Maddy

It’s been almost a year since I was doing this so I dont remember the details but what I do remember is the following:

I got rid of the error by pressing the “Save” button while defining my environment in MWS. If I’m not mistaken, its the steps that are mentioned in:

6. Define the environment in myWebmethods under Administration->System-Wide->Environments->Define Environments with the settings described in the “Central Configuration and Deployment Guide 7.1-4”

For each tab (“Design Servers”, “Configure Servers”, etc.) inside “Edit Environment” after you have configured your settings you MUST press the Save button down the bottom.

This is how i resolved the problem however, once the error was there, there was no turning back, I had to do a complete new install to get rid of it and start all over and this time use the “Save”-button. Hope it helps!

Regards
Rek

I am also ahving similar issue but I can not find the table BAM_DIM_ANALYTI_V1 in database. I have searched but there is not script to generate the table, can anybody send me script to generate this table?

Hi Rek,

I am also facing exactly the same issue. Everything is working fine in MWS but in analytic engine I get these error repeatedly. I checked and in my schema I have also only table BAM_FACT_ANALYTI_V1 and these 3 columns (WEIGHT_FACT, CONNECTION_STAT_FACT and PERSISTENCEQUAL_DIM) don’t exist in this table. I also followed the approach as mentioned by you but no luck.

Do anybody have idea how to get this resolved.

My 1st post stands on queue. Any active listener plz reply. I am still facing this and don’t know how to get rid of this.