3 Replies Latest reply: Jan 29, 2013 3:26 AM by ostiguy RSS

Report result shows abnormal behaviour

sunil-yadav
Currently Being Moderated

I am using OCI 6.3.2-141.

 

I have created a report with below headers. Intension was to generate a report of storage system connected to server with its HBA details:

I used report studio and selected "allow" for "Cross product allowed" in query properties.

 

StorageName HostName Manufacturer Model Firmware Driver

 

I was shocked when I saw the result. I have a total of 43 storage arrays in OCI server. And in result it was showing that each server is connected to all 43 storage arrays and model value is null for most of the servers. For eg:

 

StorageName HostName Manufacturer Model Firmware Driver

System1            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System2            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System3            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System4            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System5            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System6            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System7            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System8            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

System9            Host1           Emulex                 FV4.1.450.7 DV7.2.50.007

.

.

.

so on


Am I doing it correctly? Do I have to change any query properties?

  • Re: Report result shows abnormal behaviour
    ostiguy
    Currently Being Moderated

    I think the cross join is the problem. I am not a SQL guy by trade, so I am not the right guy to give you an explanation as to why that is the problem, but the behavior you are seeing is what happens when you cross join that which should not be cross joined.

     

    Do you have a sql query tool? I think the following hideous sql will do what you want:

     

    use dwh_inventory;

     

    SELECT

    storage.name AS 'Storage Name',

    host.name AS 'Host Name',

    host_adapter.model AS 'HBA Model',

    host_adapter.manufacturer AS 'HBA Manufacturer',

    host_adapter.driver AS 'HBA Driver',

    host_adapter.firmware AS 'HBA Firmware'

    FROM

    STORAGE STORAGE,

    HOST HOST,

    physical_path ppath

    LEFT JOIN host_adapter

    ON

    ppath.hostid = host_adapter.hostid

     

    WHERE

    storage.id = ppath.storageid  AND

    host.id = ppath.hostid AND

    ppath.hostid IS NOT NULL    

    GROUP BY

    ppath.storageId,ppath.hostid,host_adapter.Id

    ORDER BY

    Storage.name

    • Re: Report result shows abnormal behaviour
      sunil-yadav
      Currently Being Moderated

      Hi Ostiguy,

       

      The query you mentioned is working perfectly and gave perfect results. But I want same thing from OCI in automated reports.

       

      You said that "the behavior you are seeing is what happens when you cross join that which should not be cross joined".

       

      Why these should not be cross joined? I mean I can see that manual SQL query is working perfectly then how difficult is it to implement it in OCI reports.

       

      And these kind of cross-joins like connectivity between storage, switch, and servers or between different datamarts is expected when we generate cumtom reports.

More Like This

  • Retrieving data ...