WebI on BI platform CMS system database - Get Webi Report Error

I can see an error associated with a Webi Scheduled Document when I use BO Query. I cannot seem to find the error message when creating a Webi Report using the BI platform CMS system database Universe.
Hopefully someone can point me to the right objects, or would a Universe modification be needed.

So you can get the Information using a cms query with query builder ?

Yes, I see it in SI_STATUSINFO

SELECT top 100000 SI_NAME, SI_STARTTIME, SI_ENDTIME, SI_OWNER, SI_STATUSINFO, SI_SCHEDULE_STATUS
from CI_INFOOBJECTS
where SI_INSTANCE = 1 AND SI_SCHEDULE_STATUS = 3 AND SI_ENDTIME >= ‘2017.2.10’
ORDER BY SI_OWNER, SI_NAME

Have you tried looking in the Audit database? Errors are often recorded there also. We have a report that lists the report errors generated for the day and it is written against the Audit database not the CMS.

1 Like

As John suggested, the audit database is your best bet for this data. You can access using the CMS universe, but it’s ugly. Also, it’s only going to return records currently in the CMS. If failed instances have been deleted manually or by instance limits, you won’t get the data from the CMS. The Audit database will have that information, assuming you have auditing turned on and configured.

In the CMS, the data you are looking for isn’t in the SI_STATUSINFO field, it is in the child property SI_SUBST_STRINGS then in the child property 1.

In the CMS universe, you can access by using Technical Properties > Property Path, PropertyName, and PropertyValue or PropertyValueongText.

You can’t use PropertyPath or PropertyName in a query filter, so you’ll have to filter at the report level which means you may be pulling back A LOT of data.

When including the Property fields in the query, you’ll also loose the ability to pull in the End Timestamp, so you will need to write a separate query to return that data and then merge the Id dimensions to combine.

I created a report that pulls back Technical Properties > Property Path, PropertyName, and PropertyValue or PropertyValueongText, but I do not see the error message associated with the failed scehduled job. This is the SQL generated.

SELECT
int(Properties.si_id),
Properties.si_name,
datetime(Properties.si_creation_time),
datetime(Properties.si_update_ts),
text(Properties.si_webi_doc_properties,“500”),
PropertyValue(Properties.technical),
PropertyPath(Properties.technical),
Property(Properties.technical),
int(Properties.SI_SCHEDULE_STATUS),
int(Properties.si_scheduleinfo.SI_SCHEDULE_TYPE)
FROM
Properties
WHERE
(
datePart(Properties.si_creation_time) >= ‘2020-12-06 00:00:00’
AND
int(Properties.SI_SCHEDULE_STATUS) = 3
)

Here’s the basics of the query I used:

image

SQL:
SELECT
int(Properties.si_id),
PropertyPath(Properties.technical),
Property(Properties.technical),
PropertyValue(Properties.technical)
FROM
Properties
WHERE
int(Properties.SI_SCHEDULE_STATUS) = 3

Results:

Important for a technical query is the condition for the properties, which is missing in the screenshot and the SQL of the sample above.

Part of the SQL:
AND
Property(Properties.technical) = ‘si_id, si_name, si_kind, si_statusinfo, si_endtime’
AND

Very important: the condition must be PropertyValue Equal To

I have also created an object for si_stausinfo, which could be used in the condition also. In my case I’m looking for schedules that have a statusinfo. As the statusinfo is a property bag, this object cannot be used as a result object.

But as shown in the sample @dtolley, you will get the results in the technical property fields.

Also it is correct, that si_scheduleinfo cannot be used in the PropertyName Filter. And you cannot use objects from the universe which are part of this property. Like Scheduling - End Timestamp which is defined as datetime(Properties.si_scheduleinfo.si_endtime).But you can still use objects from the Timestamps (guess SI_ENDTIME is missing in the standard universe, but can be added).

In addition to I have published a blog with more information, how the standard functionality can be enhanced: Get more insights with BI CMS Data Access Driver – add Objects or Virtual Tables. Contains also samples which can be directly used without development.
The latest blog is about Metadata reporting for Web Intelligence Documents

Can you provide more information as to why this is necessary? Doesn’t seem to change the results.

Thank you for the question, and the a finding, I was also not aware of, that you can run this even without a property filter :slight_smile:
The Universe query is translated in a CMS Query. Without the condition this will become a select * from … and you will get all the properties and property bags. With the condition the result set is limited to the properties defined in the condition select si_id, si_name, si_kind, si_statusinfo, si_endtime from …. In both cases si_scheduleinfos and si_processinfo are not included in the result set (cause they are special property bags).
All properties:

Defined properties

1 Like