OCI DWH Cognos join error

[sunilyadav]

Hi,

I am working out to create some reports for our requirements which are not available in templates, so I have to customize it.

I am logged in DWH reporting.

Queries:

1. I want to generate report on how many and which servers are connected to my storage box. So the columns I want to add will be storage name from storage asset and hostname from host asset. But when I drag the hostname, I get error:

RQP-DEF-0103

       Cross joins (between query subjects: [Host Assets].[Host], [Storage Assets].[Storage]) are not permitted for the user who has the identity '{All Authenticated Users, Everyone, Portal Administrators, Report Administrators, Readers, PowerPlay Administrators, Adaptive Analytics Administrators, Controller Administrators, Directory Administrators, Authors, Metrics Administrators, Server Administrators, Express Authors, Query Users, Consumers, Planning Rights Administrators, ReportingAdminCM, ReportingProAuthorCM}'.

I have to create report which is combination on multiple assets. So what is the solution for it.

regards.

[davec]

What Data Model are you using?   are you looking for NFS, FC and/or iscsi?  are you looking for physical or VM's?

[sunilyadav]

Hi,

I am using inventory model, looking for FC anf iSCSI and for physical machines.

[sunilyadav]

Am I asking this question at wrong place or it is so difficult to answer?

Let me know if there is any other place to ask about cognos queries.

[davec]

Hi sunil-yadav,

Nope,  you are in the right place.   I'm researching the problem.  As a general rule you can't cross Datamarts or you will get the join error.  But the two elements (storage name and host name) that you are trying to put into the same report should be OK.   I've recreated the problem and working with engineering to understand why the issue exist.   I'll get back to you on that shortly.  (depending on engineering)

To your other email question,   Business Insight Advanced Is incorporated in OnCommand Insight 6.3 or higher.  It is a part of the new Cognos 10  which is the underlying technology for the Data Warehouse.  As you see from my videos, it’s a great tool.   It provides you much more flexibility in creating reports as well as adding columns, and other information you need....

Back to you shortly.

[sunilyadav]

Hi Dave,

Yes, getting storage and hostnames in a single report is very generic requirement so I also expected that it should work in query/report studio. And I appreciate that you are working on it.

After seeing your videos and comparing 6.2 and 6.3 docs, I identified that Business Insight Advanced is part of 6.3 or higher that is why I removed my question from the list.  I watched all you videos, they are really good and helpful.

I also need your suggestion. I tested OCI 6.2 version in PoC phase. Now I am mid-phase of implementing OCI into production, but I want to prefer 6.3 due to good reporting features. But 6.3 is still in RC. My sales guys gave me link to download 6.3 version but they also recommended to wait until 6.3.1. I don't have much time to wait as this project is already behind the schedule.

Is there any major bugs found in 6.3 so far or how safe is it to put into production?

Thanks in advance.

[davec]

ok, I see the issue.  

As I said before, you can't cross DMs.  Each DM is designed to provide you all the data elements that are relative within each DM.  Remember they are a subset of the database NOT direct access to the database.     So, when you try to create a report from the Host Assets DM and Storage Assets DM, you get the join error because we can't join across DMs.   For the report you need, stay within the specific DM.  Here's how:

In Query Studio (or Business Insight Advanced if you have OCInsight 6.3), select Inventory DM

Then expand (in your case) iSCSI Assets.  

Expand Storage and drag Storage name to the work area

Then Expand Host under iSCSI Path and drag Host Name to the work area. 

You can then group them any way you want (show how many hosts are connected to each array or how many arrays are used by each host...)  and you can add other elements like number of connections, etc... But you have to drag elements from under the same DM (ie:  iSCIS path )

When you are done, save the report and create another report using the same elements from the FC Path DM.  

You can add the two reports to a Dashboard later if you want them both on the same view. 

BTW,  if you know sql you can go directly to the database and extract the elements you want but you need to remember to make sure they are relative to eachother and your report is accurate.  the DMs try to do that for you.

Hopefully that helps.

Please confirm.

Thanks

Dave

[davec]

6.3 is safe for production.  I recommend you implement 6.3 and make sure you also download any patches and implement.  (no special reason but that's normal practice!)

[sunilyadav]

Dave,

Draging storage name from iSCSI asset is fine, but when I drag hostname from host under iSCSI assets, I get "No data available" error.

Same error is appearing even if I use storage name and volume name from storage and volume resp. under iSCSI asset. (surprised).

But if I drag storage name and volume name from storage and volume resp. under storage assets, it is working fine. Looks like iSCSI doesn't have all data available related to volumes etc.

[gmilazzoitag]

I've facing myself with the same issue. Attempting to cross storage-host informations.

The suggested method does not work at all. There are no infos about host in the iSCSI section and this in my opinion is normal for I'm using an FCP SAN.

It seems to me anyway very strange that there are no choice to obtain a simple linear table with these columns (bold what I can have from one DM and italic what I cannot):

host name - wwn - wwpn - status - switch blade - switch port - volume - internal volume

Any help?

[ostiguy]

The reason this isn't drag and drop:

wwn -wwpn - this could be a 1:1 relationship, or 1:>1 relationship (a dual port HBA is often 1 WWNN, and 2 WWPN)

wwpn - status/switch blade/switch port - this is a 1:1 relationship if the HBA is plugged in, otherwise 1:0

wwn - status/switch blade/switch port - this can be a 0-many: to 0/1 relationship, depending on how many WWPNs the WWN has, and whether they are plugged in or not

WWPN to volume/internal volume - this is potentially a many to many relationship

So, there are a ton of variances in terms of how the relationships play out. What you seek to do almost assuredly can be written, but it will require some sql

[gmilazzoitag]

I agree with scripting but I'm facing myself with a wild and unknown territory 

I agree with the other part of you answer but: too many relationships but if I can obtain a table where, in caso of 2 WWPN for 1 WWNN the table is automatically splitted in two rows below the host name...

...knowing that OCI "knows" everythings form paths and others why I cannot easily obtain other infos suchs (i.e) all the LUN attached to that host?

Of corse these infos together with the other about host and its connectivity in one report (we all know that using two or more reports we'll have them)

Regards,

[ostiguy]

Hey Giacomo,

Does the attached report do what you are looking to do? It contains a .xml report definition that you can import into Cognos with Report Studio. I spent a little bit of time with this, and I think it is roughly what you are after

[gmilazzoitag]

I will try it asap. In the mean time I thank you very much for the time you've spent on it and because this will probably be useful as sample.

regards

[sunilyadav]

Hi gmilazzoitag,

First thing is that you must know the relationship between multiple tables. If you are sure on that, I would suggest to model the metadata using framework manager. I, myself using the same approach in solving some cross joining issues between many required reports.

But metadata modeling is not that easy specially for beginners, a single mistake result in wrong report results. If your requirements are limited then writing SQL queries will solve the purpose, as suggested by ostiguy.