14 Replies Latest reply: Apr 2, 2013 10:08 PM by sunil-yadav RSS

OCI DWH Cognos join error

sunil-yadav
Currently Being Moderated

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.

  • Re: OCI DWH Cognos join error
    davec
    Currently Being Moderated

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

  • Re: OCI DWH Cognos join error
    davec
    Currently Being Moderated

    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.

     

     

    • Re: OCI DWH Cognos join error
      sunil-yadav
      Currently Being Moderated

      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.

    • Re: OCI DWH Cognos join error
      davec
      Currently Being Moderated

      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

      • Re: OCI DWH Cognos join error
        sunil-yadav
        Currently Being Moderated

        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.

        • Re: OCI DWH Cognos join error
          gmilazzoitag
          Currently Being Moderated

          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?

          • Re: OCI DWH Cognos join error
            ostiguy
            Currently Being Moderated

            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

            • Re: OCI DWH Cognos join error
              gmilazzoitag
              Currently Being Moderated

              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,

              • Re: OCI DWH Cognos join error
                ostiguy
                Currently Being Moderated

                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

                • Re: OCI DWH Cognos join error
                  gmilazzoitag
                  Currently Being Moderated

                  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

                  • Re: OCI DWH Cognos join error
                    sunil-yadav
                    Currently Being Moderated

                    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.

More Like This

  • Retrieving data ...