6 Replies Latest reply on Jan 19, 2022 2:07 AM by Yuka Oshige

    ERROR: DAA cannot be displayed

    Yuka Oshige New Member

      Hello Everyone,

       

      We are tring to set up Enterprise Data Catalog v10.5.1.1, and the Data Asset Analytics(DAA) is failing with the below error in the daa.log.

      Some parts of DAA such as "Top Assets Viewed" cannot be displayed.

      We've configured Repository Services with SQL Server 2017 on Windows Server 2012 R2.

       

      [2022-01-07 10:17:37,365] [ERROR] [catalina-exec-5] [com.infa.daa.dao.cache.DaoCacheService] [reqid=] - failed to get aggregated query data for:WITH ASSET_USAGE as (SELECT  TOP 25  ASSET_KEY, COUNT(1) ASSET_USAGE_COUNT FROM DAA_EVENT_ASSET_USAGE WHERE EVENT_TIME BETWEEN FORMAT(CAST('2021-02-01 0:0:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt') AND FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt') AND (ASSET_DELETE_TIME IS NULL OR ASSET_DELETE_TIME > FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt')) GROUP BY ASSET_KEY ORDER BY ASSET_USAGE_COUNT DESC)SELECT * FROM(SELECT A.ASSET_KEY, ASSET_ID,ASSET_NAME,ASSET_TYPE,ASSET_USAGE_COUNT FROM DAA_EVENT_ASSET A, ASSET_USAGE au WHERE A.ASSET_KEY=AU.ASSET_KEY)Q

      java.sql.SQLDataException: [informatica][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string.

       

      Any ideas on what could be going on?

        • 1. Re: ERROR: DAA cannot be displayed
          Srini Pai Active Member

          Hello Yuka

           

          From the log snippet provided I see the below query fired to the database

           

          SELECT  TOP 25  ASSET_KEY, COUNT(1) ASSET_USAGE_COUNT FROM DAA_EVENT_ASSET_USAGE WHERE EVENT_TIME BETWEEN FORMAT(CAST('2021-02-01 0:0:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt') AND FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt') AND (ASSET_DELETE_TIME IS NULL OR ASSET_DELETE_TIME > FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt')) GROUP BY ASSET_KEY ORDER BY ASSET_USAGE_COUNT DESC)SELECT * FROM(SELECT A.ASSET_KEY, ASSET_ID,ASSET_NAME,ASSET_TYPE,ASSET_USAGE_COUNT FROM DAA_EVENT_ASSET A, ASSET_USAGE au WHERE A.ASSET_KEY=AU.ASSET_KEY

           

          Could you run this query from an SQL Client or SSMS and confirm if we are able to execute it standalone

           

           

          Regards

          Srinivas

          • 2. Re: ERROR: DAA cannot be displayed
            Yuka Oshige New Member

            Hi Srinivas,

             

            Two queries below were executed from SSMS, and both failed. 

             

            1

            SELECT  TOP 25  ASSET_KEY, COUNT(1) ASSET_USAGE_COUNT

            FROM DAA_EVENT_ASSET_USAGE

            WHERE EVENT_TIME BETWEEN FORMAT(CAST('2021-02-01 0:0:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt')

            AND FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt')

            AND (ASSET_DELETE_TIME IS NULL OR ASSET_DELETE_TIME > FORMAT(CAST('2022-01-07 10:20:0' AS DATETIME), 'yyyy-MM-dd hh:mm:ss tt'))

            GROUP BY ASSET_KEY

            ORDER BY ASSET_USAGE_COUNT DESC

             

            →Conversion failed when converting date and/or time from character string.

             

            2

            SELECT A.ASSET_KEY, ASSET_ID,ASSET_NAME,ASSET_TYPE,ASSET_USAGE_COUNT

            FROM DAA_EVENT_ASSET A, ASSET_USAGE au

            WHERE A.ASSET_KEY=AU.ASSET_KEY

             

            →Invalid object name 'ASSET_USAGE'.

             

             

            Thanks,

            Yuka

            • 3. Re: ERROR: DAA cannot be displayed
              Darren Wrigley Guru

              have you created a support ticket.  the table referenced should be DAA_EVENT_ASSET_USAGE not ASSET_USAGE.

              • 4. Re: ERROR: DAA cannot be displayed
                inuser496239 New Member

                SELECT

                name,

                is_ansi_nulls_on,

                is_ansi_padding_on,

                is_ansi_warnings_on,

                is_arithabort_on,

                is_concat_null_yields_null_on,

                is_numeric_roundabort_on,

                is_quoted_identifier_on

                FROM sys.databases where name='DAA_DATABASE_NAME' ;

                 

                 

                Output should be all values 0 here. We had similar issue with Azure SQL in one environment.

                • 5. Re: ERROR: DAA cannot be displayed
                  Yuka Oshige New Member

                  Hi Darren,

                   

                  We've already created a support ticket, but it's not solved yet.

                  Queries are generated and executed automatically by EDC so they might not be modified.

                   

                  Thanks,

                  Yuka

                  • 6. Re: ERROR: DAA cannot be displayed
                    Yuka Oshige New Member

                    Hi Sohan,

                     

                    We got all values 0 from DAA database.

                    Have you tried any other solutions?

                     

                    Thanks,

                    Yuka