Transpose in CMS Query Builder 1.3

In a standard database, each field of a row contains a single value. This is not the case for BO CMS database. A property can have a quite complex structure. A simple example is SI_DSL_UNIVERSE property for Web Intelligence documents that indicate what Unx universes are used by the document.

SELECT si_name, si_universe, si_dsl_universe
FROM ci_infoobjects
WHERE si_kind = 'Webi'
AND si_instance = 0
AND si_name = 'Customer Profitability Comparison, by Customer'

The result in the SAP BO Query Builder will look like:

Exporting metadata for such object using “Include containers” in Biclever CMS Query Builder will create a single row and the fields for the property will be SI_DSL_UNIVERSE.1, SI_DSL_UNIVERSE.2, SI_DSL_UNIVERSE.3, SI_DSL_UNIVERSE.SI_TOTAL

The wide format is sometimes inconvenient. “Transpose” will create a row for each value of each array.

CMS Query Builder 1.3

This version includes the following improvements:

  • Undo with Ctrl-Z, Redo with Ctrl-Y.
  • Execution of the query in the selection.
  • Possibility to create new, edit and delete templates from the Template menu.
  • Corrections in the default templates.
  • Tool tip for column names.
  • Perceiving of the value type in Excel (previously dates were exported as strings)
  • Possibility to select Name when selecting an object (previosly ID and CUID were only possibilities. Name is useful for names of groups and users etc)
  • A new transpose feature for exporting containers.

How to Enable Semantic Layer Java SDK

The prerequisite to using Unx Documenter is to have Semantic Layer Java SDK enabled. However it is not enabled during default installation of SAP BusinessObjects client tools. Here is how you can enable it:

Open Program and Features.

Find the client tools and double click on them (or right click and select Uninstall/Change)

Select Modify

Click Next on language selection. Add Semantic Layer Java SDK and complete installation with Next.

You are ready to go.

 

 

SAP BI Platform Support Tool

If you are a SAP BO administrator or a consultant, you will find the following SAP tool very helpful.

SAP BI Platform Tool is designed for supporting BI Platform administration. It simplifies the process of reporting on the landscape configuration, performing root cause analysis tasks, and delivering performance optimization and go-live services.

This tool is free of charge and is maintained by SAP.

You can find more details and download the tool from the tool’s page on SAP Community Wiki

Unx Universe Documentation

You can quickly create PDF documentation of a Unx universe from IDT (Information Design Tool). But PDF is not good if you need to analyze universe in some way. For this purpose, it is better to have Unx metadata in Excel spreadsheet. You can use Biclever Unx Documenter (UnxDoc) to extract Unx information to Excel spreadsheet. In this post we will describe both ways of documenting Unx universe.

Creating PDF documentation from IDT

You can create separate PDFs for business layer, data foundation, and connection.

Open IDT and login.

Retrieve Unx universe to IDT.

Right click on the item you want to document (for instance, business layer), and select Save as.

Creating PDF documentation of UNX in IDT

After this, you will be able to select the destination and metadata elements to be exported:

Selection of elements for PDF documentation of UNX in IDT

The documentation looks good and ready for printing. If you need to create some documentation of your universes quickly, this is the easiest way.

PDF documentation of UNX created in IDT

However if you need to use the documentation for any practical purpose, you will need a more structured format.

Creating XLSX documentation using Biclever Unx Documenter

Download Biclever Unx Documenter from Software > Unx Documenter. Unpack it to a preferred folder. Check if you have Semantic Layer SDK enabled.

Start the tool using unxdoc.bat and log in.

Select a universe

Click Process and then Export result. Select destination XLSX file.

The universe metadata will be exported to Excel spreadsheet. Now you can easily filter, search and analyze the information in Excel.

BI-on-BI in BO 4.2 SP3

In BO 4.2 SP3, you are finally able to run queries against CMS database without 3rd party tools! SAP has introduced a new CMS data access driver. So it is now possible to use a universe and standard BO tools to query the metadata of the CMS repository database.

Download “The LCMBIAR contains the connection to CMS database “SAP BI platform CMS system database” and a sample universe universe “SAP BI platform CMS system database”.

Now you can easily get a list of all your Web Intelligence documents:

Or you can find dependency between universe and Webi document: