Internal: When connected to the Mart Server 2020 R1 from erwin Data Modeler 2020 R1, clicking on (4284070)

Issue:

When connected to the Mart Server 2020 R1 from erwin Data Modeler 2020 R1, clicking on Open button, I receive the following error: Database/Mart Server Connection Failed.

Versions:

- erwin Data Modeler and Mart Server 2020 R1

- Oracle 11g/12c

Steps to Reproduce:

1. Upgrade erwin Data Modeler and Mart Server to 2020 R1 

2. Mart Server Upgrade Fails due to insufficient Privileges to Materialized Views

3. The following permissions are granted to the Mart User: GRANT CREATE MATERIALIZED VIEW TO MMINSTALL; 

4. Mart Server Upgrade is completed, configured and successfully connects to Mart Server Database

5. Open erwin Data Modeler and successfully connect to the Mart Server

Expected Result: After connecting successfully, click on the Open Button to bring up the Mart Server Catalog/Library; "Open" Window without an issue.

Actual Result: After connecting successfully, clicking on the Open Button prompts the following error: Database/Mart Server Connection Failed.

 Another form of this error is I can open a model from the mart but when I try to save that back I get "Could not commit JDBC transaction; nested exception is java.sql.SQLException: ORA-12008: error in materialized view refresh path
ORA-00904: invalid identifier

Resolution:

This occurs when the Mart Server Upgrade has not completed successfully. Since in this case, the Mart Server Upgrade stopped due to insufficient privileges, the upgrade did not fully complete. Although the privileges to Materialized Views were granted after the failure, the version in the m9Version table shows the Mart version: 9.90 and the Configuration to the Mart Server connects successfully to Database, the upgrade is still pending on upgrading the Materialized Views.

Run the following script in the Mart Server Database to complete the Mart Server Upgrade: 

UPDATE m9Version SET A_MinVersion = 90 where A_AppName = 'Mart Server';

UPDATE m9VersionCompatible SET VC_Version = '9.90.00';

CREATE TABLE M9REPORTLISTDATA
(
C_ID INTEGER NOT NULL,
O_ID INTEGER NOT NULL,
O_TYPE INTEGER NOT NULL,
O_STARTVERSION INTEGER NOT NULL,
O_ENDVERSION INTEGER,
O_PARENTID INTEGER,
O_NAME VARCHAR2(4000 BYTE),
O_PHYSICALNAME VARCHAR2(4000 BYTE) NULL,
O_PROPERTYBLOB1 CLOB NULL
);

CREATE INDEX AK1 ON m9ReportListData (O_TYPE, O_ENDVERSION, C_ID, O_ID);

CREATE INDEX AK2 ON m9ReportListData (C_ID, O_PARENTID, O_TYPE, O_ENDVERSION);

CREATE INDEX AK3 ON m9ReportListData (C_Id, O_Id, O_Type, O_ENDVERSION);

CREATE TABLE M9REPORTUPGRADE
(
C_ID INTEGER NOT NULL UNIQUE,
COMPLETED VARCHAR2(50) DEFAULT 'Y'
);

DROP MATERIALIZED VIEW LOG ON M9OBJECT;

CREATE MATERIALIZED VIEW LOG ON M9OBJECT WITH ROWID,SEQUENCE(C_ID,O_ID,O_TYPE,O_STARTVERSION,O_ENDVERSION,O_PARENTID) INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW M_1;

CREATE Materialized view m9objcnt refresh fast on commit as (
select c_id,o_type,count(*),count(O_Type) ocount from m9object where O_EndVersion=999999999 group by c_id,o_type );

CREATE VIEW m9vobjectcount as(
select C_Id,
sum ( case when O_Type = 1075838979 then ocount else 0 end ) ent_cnt,
sum ( case when O_Type = 1075838981 then ocount else 0 end ) att_cnt,
sum ( case when O_Type = 1075839016 then ocount else 0 end ) rel_cnt
from m9objcnt
where O_Type in (1075838979,1075838981,1075839016)
group by C_Id);

create index meteindx on m9objcnt (C_Id,O_Type,ocount);

This script could be found at the following directory in the Mart Server r9 folder: C:\Program Files\erwin\Mart Server r9\MartUpgrade\Oracle

Additional Information: 

https://erwin.com/bookshelf/public_html/2020R1/Content/Release%20Notes/Mart%20Server%20Release%20Notes/Configure%20the%20Mart%20Server.html