MM020 – The Repository, a gold mine for an Analytics Geek

Progress and new technologies made life easier, in many fields. Also in the Information Technology, of course.

One of these improvements, from a programmer’s point of view, is definetely having a new, visual set of ready-to-go software to develop and manage coding easier and faster than before. You drag & drop to design the project, and stop. Software does everything.

Everything awesome, isn’t it? But, what if I must look at the “automatic” code developed by the software, e.g. to change a specific rule, or word, or whatever is.

That’s why a smart programmer does not only know how to drag & drop. He (She) also knows the underlying REPOSITORY.

Repository is an archive, embedded into each visual programming software, where all “metadata” are stored. Metadata are “tables of tables”, i.e. information concerning the technical environment, the output developed, the database architecture, etc. Here you get for example the list of people to send the email of the end of a process, the default value for some fields in the absence of information, and so on.

The more you know the tables where metadata are, the better you will get into code in case of need.

Let’s make an example by using Cognos, the Business Intelligence software developed by IBM. like all the other BI tools, Cognos has a well-defined model.

Suppose you need to switch from Italian to English in the reports developed, and unfortunately your admin software does not propagate the change to all objects who use that attribute.

In this example, all the reports with this attribute would cause an error.

A solution could be editing each report and manually changing the attribute. But what if the reports are thousands?

Thanks to the repository, it is possible to propagate the change of the model to all objects.

Just write a simple query.

Suppose we want to change “Unità” to “Units”:

Saved reports contain an XML in which you can search the attribute named “Unità” and you get all the reports they use that information.

There is typically no documentation on Repositories, but its knowledge makes us able to build the query and search all reports which use the attribute.

In this case, you need to know the following information:

•             the CMOBJPROPS7 table contains all the XML of the reports and models.

•             the CMOBJNAMES table contains all the names of the objects in the repository (called content store), all the other tables are linked to it with the CMID field.

--The XML contained in the CMOBJPROPS7 table uses the namespace ns
--to be able use xQuery, which is a method of extracting information from an XML,
--need declare what the namespace is
WITH XMLNAMESPACES
  ('http://developer.cognos.com/schemas/report/14.1/' AS ns)
select
  REPORTNAME.NAME,
  REPORTXML.spec,
--In order to use the field as XML, need to cast as nvarchar first
--a replace is made on the utf to use all special characters
--finally an xQuery is made to get the value of the modelPath element which is the package to which it refers
  CAST(REPLACE(CAST(REPORTXML.spec AS NVARCHAR(MAX)),'utf-8','utf-16') as xml).value('(/ns:report/ns:modelPath)[1]', 'varchar(max)') MODELPATH,
--The expression extracts all the elements where the field is used (in this case the filtered field "Unità")
  CAST(REPLACE(CAST(REPORTXML.spec AS NVARCHAR(MAX)),'utf-8','utf-16') as xml).query('//ns:expression')                              EXPRESSION
from CMOBJPROPS7 REPORTXML
  join CMOBJNAMES REPORTNAME on (REPORTXML.CMID = REPORTNAME.CMID)
--this WHERE condition makes an xQuery looking for all elements with attribute equal to "name" containing the string "Unità"
where CAST(REPLACE(CAST(REPORTXML.spec AS NVARCHAR(MAX)),'utf-8','utf-16') as xml).query('//*[@name = "Unità"]') is not null;

The knowledge of the repository has allowed us to save precious time and avoid tedious work, being able to take action only if necessary.