WEBI report - CDMB structure

[#1]

Team,

I'm supposed to deliver webi report which displays simple CMDB structure - something similar to the picture below.

All the CI's (services, products, applications, ...) are stored in cmdb_ci table and all the relations are in cmdb_rel table. In cmdb_rel table, there are two columns (parent, child) which represent parent and child CI's of the current relationship.

It means that the CI "Product 1" is a child in "Service 1 - Product 1" relationship and parent in "Product 1 - Application 1" relationship.

So far I'm able to display each level separately, but I have no idea how to put the whole structure in one table as requested.

Could you please give me some hints?

Thank you,

Jakub

[Former Member]

Hi,

Can you share some sample data  of  each level  , By using breaks we can  achieve the above layout.

thanks.

[mhmohammed]

Hi Jakub,

Do you have the Universe created with appropriate join between the 2 tables (cmdb_ci and cmdb_rel)? What does the join look like? Do you already have the objects created for Level 1, 2, 3, 4, 5 and so on in the Universe?

I don't understand what you mean by "one table" when you say "... how to put the whole structure in one table as requested." Does "one table" here mean a table in the database or table in the Webi report?

If it means, table in the Webi report, then you just have to add all the Level 1, 2, 3, 4, 5 and other objects in the report (as a table) and create a break on all of those 5 levels, go to break properties of all those 5 levels and hide the Break Headers and just show the Table Header by right click on the table -> Format table -> check Show table headers.

Hope that makes sense. Also, please answer my questions in the para.

Thanks,

Mahboob Mohammed

[Former Member]

Hi,

sorry - I've red my question again and it is quite confusing So please let me start again..

The source for the report is CA CDMB which is a part of CA Service Desk Manager solution. As a part of this solution there is complete BOXI Universe with all the objects, joins, etc.

It means I can easily create query containing both parent and child objects

and I can get similar result.

where S005.2 is child for AMM, AMM is child for AMM-BI Publisher etc.

What I need is to transform this table into following structure (just example for the object in the red frame).

I played with merged dimensions, multiple queries, but no success so far..

Thank you for sharing your experiences and knowledge,

Jakub

[Former Member]

Hi,

How many LOV values you have for Parent & child name if  these LOV values are fixed (Amm,S005.2 etc) then you can create variables as below ;

v1= if([parent name]="AMM" then "S005.2" elseif [parentname]="AMM-BI Publisher" then "AMM". ....

Thanks

[Former Member]

Hi,

unfortunately this is not the solution - threre are thousands of CI's and the whole structure is changing regularly. It means the report must be dynamic and automatic..

Thanks,

Jakub

[Former Member]

Hi,

Try to use all available dim objects in  universe and create report and there must  be some object which shows relationship b/w parent & child like it might be type or indicator flag etc...

Thanks.

[Former Member]

Hi all,

I've created separate queries for each CMDB level.. I'm not sure if this is the first step for the right solution, but why not to try..

For example this is query for level 3 - return parent and child CI's where child is in parent objects from level 2 query.

And this is the whole report - each table represents one level of CMDB structure. You can see the relations between levels.

'What I'm trying to do now is to merge these 5 tables into one as indicated in my previous post..

Any ideas?