CMS Database --> Querybuilder vs. SQL vs. FHSQL

[arno_huerlimann56]

Hi

Is there any detailed Documentation how I can Connect the CMS (Audit as well) with normal SQL Statements? QueryBuilder ist not really comfortable.

QueryBuilder:

Direct SQL

WEBI FHSQL like:

How can I see CMS Systemobjects with a SQL IDE like "SQL Developer" (I Only see CMS_InfoObjects7)

There are some good Documentation for QueryBuilder. Also for Virtual and Physical Tables. But is there a ER Diagram for the Systemdatabases? And how is the relationship between the Objects in QueryBuilder, SQL and FHSQL in Webi?

I appreciate your support,

Best regards, Arno

[Joe_Peters1]

As you've seen, there isn't much in cms_infoobjects7 that can be easily queried. The majority of usable object properties are in an encrypted column and can't be retrieved with SQL.

Your "FHSQL" queries are actually queries that use the "CMS System Database" connection, which is a layer on top of Query Builder. It only works within BO; you can connect to it from an external querying tool like SQL Developer.

Audit, however, is different. The audit tables are regular relational tables, and can be queried with any native querying tool.

[JohnClark]

What joe.peters2 said.

In addition, there is a schematic of the data structure for the Audit database in the Administrator's Guide.

[arno_huerlimann56]

Thank you for the answer
"Your "FHSQL" queries are actually queries that use the "CMS System Database" connection, which is a layer on top of Query Builder. It only works within BO; you can connect to it from an external querying tool like SQL Developer."

Is there any Documentation of Relation for this two Layers?

[Joe_Peters1]

There is some here: Unlock the CMS database with new data access driver for BI 4.2 - Business Intelligence (BusinessObje...