I have a question related to creating a data cube in ODI 12c. I have 2000 distinct trx_id and 190 distinct customer_id. I have created a data cube in the Oracle database using this query:
create table test as
(select * from (SELECT trx ,customer,sum(amount) as sum_amount FROM total
GROUP BY CUBE(trx,customer)))
In the above query, trx and customer are dimensions. After running the query you will have 2^n subtotals for all the possible combinations of dimensions, here we have 4 subtotals. Cube has 4191 records.
In ODI 12c, I have two dimensions like trx with 2000 records and customer with 190 records. Each dimension has one level and one column. In creating a cube, I do group by on both trx and customer. Cube is created, but when I check its record count it does not have 2^n subtotals. Cube has 2000 records. Would you please tell me why the results are different and how to configure ODI to create a cube-like Oracle database? Maybe it is related to OLAP model. Since for example, relational OLAP, or ROLAP, stores all data, including aggregations, in relational databases rather than cubes, and doesn’t use pre-computation. Would you please tell me what OLAP model is in the oracle data integrator?
Any help is really appreciated.