Oracle BAM 12c supports a star schema. It supports joins through Logical Data Objects (LDOs). An LDO allows you to join one FACT table with multiple DIM or Dimesnsion tables, which allows you to run queries on top of the LDO. If Active Data is used on top of an LDO with Archived Relation, some considerations need to be taken into account to achieve the best performance.
These conditions are outlined as follows. Please note that these are best practices to help enhance your performance when you switch to 12c and must be treated as recommendations.
Oracle recommends that you mark your DIM table as ‘Slow Changing Dim’ if the changes to the DIM table are very infrequent (once in a day or so). When you mark it with ‘slow-changing dim’, a join-memory enhancement comes into play that reduces the amount of heap required and also increases the query start up time.
Oracle recommends that you keep the DIM table size reasonable (e.g. less than 10k – only used for lookup) and batch updates to DIM tables to avoid frequent updates so you can mark the DIM table as ‘Slow Changing Dim’. In the case where the dim table is growing at the same pace as the fact table, it is better to keep the dimension columns in the FACT table.
Oracle recommends that you only join the FACT table with required DIMs as multiple DIMS requires complex n joins. Consolidate DIM tables to reduce number of joins.
Oracle recommends that you design an LDO with filters to reduce the amount of data returned. This reduces the TEMP tablespace required to run the archiver query.
Oracle recommends that you use Calculated Fields in your query to reduce the number of columns required in the FACT DO. Keep only what’s absolutely required as columns in the FACT table. This will help Active and CQL template queries to be more efficient as they do not need to bring additional columns into memory.
If you are using 11g Oracle DB, Oracle recommends that you use Secure Files to improve performance. See Using Secure Files for more information.
Oracle recommends that you create indexes for key fields. You can use the BAM Data Object to create indexes. See Selecting an Index Strategy for more information.