Active IQ Unified Manager Discussions

OnCommand Insight SQL View

berks
4,225 Views

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

3 REPLIES 3

ostiguy
4,225 Views

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

dvbarragan
4,225 Views

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

grissino
4,225 Views

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

Public