Question Re: CMS Database --> Querybuilder vs. SQL vs. FHSQL in Technology Q&A

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

arno_huerlimann56 — Thu, 22 Dec 2022 12:21:12 GMT

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

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

Joe_Peters1 — Thu, 22 Dec 2022 13:42:18 GMT

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.

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

JohnClark — Thu, 22 Dec 2022 13:45:38 GMT

What joe.peters2 said.

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

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

arno_huerlimann56 — Fri, 23 Dec 2022 07:19:51 GMT

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?

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

Joe_Peters1 — Fri, 23 Dec 2022 19:28:54 GMT

There is some here: Unlock the CMS database with new data access driver for BI 4.2 - Business Intelligence (BusinessObjects) - Support Wiki (sap.com)