With the new BO CMS Data Access Driver from SAP(available from BI 4.2 SP3), we can use the familiar BO reporting interfaces to connect to the CMS data through the customizable BI Platform CMS system database universe. We no longer need to create complicated queries in Query Builder to know more about the objects in the BI Platform – users, connections, custom access roles, reports, dashboards etc.
While the Query Builder is a powerful tool, it has a few disadvantages.
- It is difficult for end users to learn how to query the CMS metadata virtual tables using SQL.
- It is also a hassle to export and distribute the results of the queries without SDKs/macros.
With this new driver and universe,
- We use familiar reporting interfaces like WebI that requires little user retraining
- We also leverage the powerful distribution capabilities of the BI platform.
An earlier blog on our site, BI 4.2 SP3 New feature – CMS Database Data Access Driver, provided an initial look at the driver, how to set it up and execute the ready-to-use queries that are available in the BI Platform CMS system database universe.
This blog will be a deep dive, about how the BI Platform CMS system database universe is structured and how to build your own queries on it to get the required information from the CMS in an effective and efficient manner.
We will first see how the universe is structured.
Universe Data Foundation:
The BI Platform CMS system database universe consists of a single table called Properties along with four alias tables on the main table.
Universe Business Layer:
The objects from each level reside in a separate folder in the universe. To navigate from one level to another using a query, we need to use an object from the Relationship folder to connect the two levels, and to define what category of data we are looking from the lower level.
Building a Query on the universe using WebI
The query in the screenshot below will list the objects in the Favorites folder for all users.
The query uses objects from two different levels – Level 0 and Level 1.
By adding the query filter Ancestor = 18, we get the all the objects that only belong to User’s personal folder.
1. InfoObjects (Level 0)
We get the following objects from Level 0.
- Object name
- Object type
- Its owner
- Folder path.
The object FolderPath only gives the folder path if the object is a folder. For eg., if the user’s personal folder has subfolders, this object will give its actual folder path.
But for any other type of document (e.g. WebI report or publication) inside the Favorites folder, the object FolderPath would be return a blank value.
For these details, we would need to go one or more levels deeper. But that doesn’t mean we can simply drag and drop objects from the other level (That would throw an error),we would need to define the relationship between Properties table and Level 1 table, and whichever fields we require from the Level 1 table of the BI Platform CMS system database universe.
2. Relationship to L1
We use the relationship object ParentFolder to communicate to the universe that we are looking for the Parent folder of all the objects that we have retrieved from Level 0/ InfoObjects folder.
We would be getting the following error message if we do not use any relationship object, and instead try to directly use the objects from Level 0 and Level 1 folders.
The order of objects in the query is also important. We need to use the Relationship object between objects from the InfoObjects folder and the InfoObjects L1 folder in the Query Panel. Otherwise, no data would be returned from the Level 1 folder.
The type of objects retrieved in Level 1 would be “folder”.
3. InfoObjects L1 (Level 1)
We can get the name of the folder and then the folder path for these folders.
If we want to move down another level to Level 2, we need to use an object from the folder Relationships L1-To-L2 and then use objects from Level 2 and so on.
Summing it up..
We can replicate the queries which used to be built in Query Builder using this universe on the familiar WebI interface.
The biggest advantage of the BI Platform CMS System Database universe is that, queries which were previously built on the Query Builder to retrieve information can now be consumed from this universe and analysed on the WebI interface itself.
These WebI reports can also be easily shared and distributed to the users rather than giving the users access to Query Builder. To use the Query builder, the user also requires additional training about the tables in the system and how to retrieve the details of the objects by using SQL.
This bypasses the process of Query builder seamlessly with a much more user-friendly interface alongside a much easier approach.
Got questions? Click here to get in touch.