We need to display counts (as zeroes) for dimensions whose actual data is missing.
We have the following original selection query:
SELECT a.dim1, a.dim2, a.dim3, COUNT(*) DATA_COUNT, SUM(dim10) DATA_SUM FROM tabX a, ref_tabY b, ref_tabZ c WHERE a.date_field BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE)-30 AND a.dim11 = b.d16(+) AND a.dim12 = c.d15(+) AND a.dim9 IN (1,2,4);
We have created a trend report based on the above query. At present, if we do not have data in tabX for any particular day, say day3, day4, day5 of the last week, the trend graph still shows a continuos line. As per recent requirement, the plot should show BREAKS in those places displaying No data available during these days.
Another requirement is to be able to drill down on each dimensions. For example, if the user selects dim1 in first level drill the corresponding data needed to be displayed. In case, there's no available records, we need to display zero (0).
As a solution, we were thinking of creating a time dimension table and CROSS (cartesian) join the same with the available combinations of all these drillable dimensions. Next, LEFT OUTER JOIN the above set with the original query with Joining Keys on each of the drillable dimensions.
Can anyone suggest or guide us in any other possible solutions. Our approach does not have configurable options so would be unmanageable later if new dim came up. Plus, it will take huge time.
We are using Oracle 11g R2 on Linux RHL5.
The tabX have 5 GB data / day, range partitioned on date_field. The ref tables are reference tables for lookups (indexed).