Yellowfin Database Documentation

Flávio Crisler shared this idea ago

Idea Logged

Having more detailed documentation for the Yellowfin database, or at least well-defined metadata for its tables and columns, would be extremely valuable. A recurring challenge I face is the difficulty of structuring queries to retrieve information directly from the database, especially when implementing new functionalities in the tool. The absence of robust documentation or clear metadata makes the process of locating the necessary information highly complex and time-consuming.

I often spend hours trying to determine which tables contain the data I need and how to correctly relate them using JOINs. This issue is further compounded by the lack of standardization in column naming. In many cases, a column in one table will have a completely different name in another, even when both contain related information that should be joined, such as in an INNER JOIN, for example.

I like this idea 0

Replies (2)

Flávio Crisler ●

I would like to share a recent experience that might be useful for other Yellowfin users. I needed to create an audit report to monitor the files in the transformation flow, as I work with a large volume of transformations running daily on replica databases to update tables in the Data Warehouse. One of the main challenges was having more efficient control over processes that encountered errors.Although I had set up email notifications for these cases, I realized that a dashboard would be much more useful, as it automates the task and centralizes the information in a visual and accessible way. With that in mind, I developed a query and a dashboard for monitoring. I will attach them below for reference. While they are in Portuguese, they can be easily adapted to any language if they are useful to other users.

WITH latest_event AS (
    SELECT 
        event.*,
       MIN(event.gmtdatetime) OVER (PARTITION BY event) AS max_gmtdatetime
    FROM event
    WHERE event.eventdata LIKE '%processId=%'
)
SELECT 
    etlprocess.scheduletypecode AS tipo_carga,
    etlprocess.shortdescription AS nome_arquivo,
    etlprocess.etlprocessid,
    CASE latest_event.eventcode
        WHEN 'ETLPROCESSRUNFAILED' THEN 'FAILED'
        WHEN 'ETLPROCESSRUNSUCCEEDED' THEN 'SUCCEEDED'
        ELSE NULL
    END AS status,
    latest_event.eventdate AS data_carga
FROM etlprocess
INNER JOIN latest_event 
    ON etlprocess.etlprocessid::varchar = substring(latest_event.eventdata FROM 'processId=([0-9]+)')
    AND etlprocess.publishuuid = substring(latest_event.eventdata FROM 'publishUUID=([a-f0-9-]+)')
    AND latest_event.gmtdatetime = latest_event.max_gmtdatetime
ORDER BY data_carga DESC

WITH latest_event AS (
    SELECT 
        event.*,
       MIN(event.gmtdatetime) OVER (PARTITION BY event) AS max_gmtdatetime
    FROM event
    WHERE event.eventdata LIKE '%processId=%'
)
SELECT 
    etlprocess.scheduletypecode AS tipo_carga,
    etlprocess.shortdescription AS nome_arquivo,
    etlprocess.etlprocessid,
    CASE latest_event.eventcode
        WHEN 'ETLPROCESSRUNFAILED' THEN 'FAILED'
        WHEN 'ETLPROCESSRUNSUCCEEDED' THEN 'SUCCEEDED'
        ELSE NULL
    END AS status,
    latest_event.eventdate AS data_carga
FROM etlprocess
INNER JOIN latest_event 
    ON etlprocess.etlprocessid::varchar = substring(latest_event.eventdata FROM 'processId=([0-9]+)')
    AND etlprocess.publishuuid = substring(latest_event.eventdata FROM 'publishUUID=([a-f0-9-]+)')
    AND latest_event.gmtdatetime = latest_event.max_gmtdatetime
ORDER BY data_carga DESC

Files:

Reply URL

Chegudi Priya ●

Hello Flávio Crisler,

My name is Chegudi Priya from the Yellowfin Technical Support Team. We have received your support request, and I will be your primary contact on the following ticket:

Ticket Number: #31592-11-29
Case Title: Yellowfin Database Documentation

The Dev team has previously been informed of this as an Enhancement. I have added your note and organization to the existing Enhancement for tracking purposes. I'll let you know when the development team responds, If you have any additional questions, please feel free to respond here.

Sincerely,

Chegudi Priya

Yellowfin Technical Support Engineer

Reply URL