Sunday, 9 July 2017

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:

The Data Warehouse databases were explored in this post:

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} */
;


No comments:

Post a Comment