topic OnCommand Insight SQL View in Active IQ Unified Manager Discussions

OnCommand Insight SQL View

berks — Thu, 05 Jun 2025 06:31:33 GMT

Hi Everyone,

My customer is using OnCommand Insight and Report.

Instead of creating a "Custom Report", he would like to directly query the database (without using the Cognos GUI) using a customer defined view in the database.

For example, he would like to run a query "select * from <customer_defined_view>".

He is able to see the native SQL commands in Reporting Studio but they are complicated and the SQL commands change when a report is changed.

So, my question is, can you create a custom view in the database that is not coupled with the Cognos GUI?

Thanks in advance

Jeff

Re: OnCommand Insight SQL View

ostiguy — Thu, 22 Mar 2012 13:06:08 GMT

OnCommand Insight Assure/Perform/Plan does not allow for custom database views - only read only access to the DWH datamarts is granted. No access to the OCI APP (SANscreen Server) database is granted.

Use of cognos is not required - you can connect to the DWH database through the aforementioned read only user account.

Is the problem the customer has a sql skillset, but not a Cognos? If that is the case, they want want to look at IBM's training options for Report Studio

Re: OnCommand Insight SQL View

dvbarragan — Tue, 28 Aug 2012 13:07:42 GMT

From the OCI Data Ware House user guide:

Note:

Using this user name and password, you can log into the remote

OnCommand Insight database and perform queries on the data. You should

change the default user name and password as soon as possible.

You cannot create custom views but you can connect to SanScreen/OCI database directly

Re: OnCommand Insight SQL View

grissino — Tue, 04 Sep 2012 13:29:02 GMT

Additionally, it might help to see some sample SQL queries.

For each data mart, you can find sample SQL queries in the OCI Data Warehouse (DWH) User Guide at https://support.netapp.com/documentation/productlibrary/index.html?productID=60983.

Here's a sample SQL query taken from the DWH User Guide for the Storage and Storage Pool Capacity data mart. This retrieves capacity and raw capacity for all storage arrays.

SELECT
sd.name AS 'Storage Name',

SUM(spcf.capacityMB) AS 'Capacity MB',

SUM(spcf.rawCapacityMB) AS 'RawCapacity MB'

FROM

dwh_capacity.storage_and_storage_pool_capacity_fact spcf

JOIN dwh_capacity.storage_dimension sd

ON spcf.storageTk = sd.tk

JOIN dwh_capacity.date_dimension d

ON d.tk=spcf.dateTk

AND d.latest = 1

GROUP BY sd.name