MySQL Programming for OCI Data Warehouse Advanced Reports

In order to create Advanced Reports using OnCommand Insight’s Data Warehouse, there are two main skills (at least as I see it):

1) IBM Cognos Report Studio - a good understanding of all the objects and their properties

2) MySQL programming (there’s only so far you can go using drag and drop from the Source Pane)

The IBM Cognos Report Studio GUI is overviewed in this post:

http://www.cosonok.com/2017/07/mapping-out-oci-73-dwh-guis-part-3-ibm.html

The Data Warehouse databases were explored in this post:

http://www.cosonok.com/2017/06/exploring-oci-73-data-warehouse-data_25.html

There are loads of MySQL tutorials out there (google “MySQL Tutorial”, for example: https://www.tutorialspoint.com/mysql/mysql-introduction.htm). Personally, I find the best way to learn is by looking at examples. There are sample reports that come with the OCI DWH, a selection of OnCommand Insight reports on the NetApp Storage Automation Store, and other sources; and these can be loaded into ‘IBM Cognos Report Studio’ and the MySQL code viewed in the Query Explorer. It’s always worth first looking to see if there’s a report that does what you want (or close to what you want).

Image: Example using the ‘NetApp Capacity by Aggregate - Daily Trending’ from the Automation Store

Some MySQL Programming Commands/Operators

Not really in any particular order.

SELECT

SELECT X,Y

AS

FROM

FROM ()

JOIN

LEFT JOIN ()

INNER JOIN

ON

AND

WHERE

GROUP BY

UNION

CASE ... END

WHEN ... THEN ...

WHEN ... THEN ... ELSE ...

DISTINCT

COUNT()

SUM()

AVG()

ROUND()

CEIL()

CAST()

SUBSTRING_INDEX()

SUBSTR()

LOCATE(,)

SELECT MAX()

- INTERVAL X DAY

IF({condition},{true},{false})

LIKE

CONCAT()

GROUP_CONCAT()

ORDER BY

ASC SEPARATOR

DATE_FORMAT({date},{format})

IFNULL()

NULL AS

=

<> 

>=

/

/* {comment here} */

;