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?