Report result shows abnormal behaviour

[sunilyadav]

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?

[ostiguy]

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

[sunilyadav]

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.

[ostiguy]

Hey Sunil,

I am travelling for the next few days, but I will try to get a member of the brain trust to elaborate on cross joins.

In the interim, here is a video on how to import a raw sql query into a Report Studio report