I'm trying to create a DMR model for a view created over top an Oralce OLAP cube. The issue is that when one creates a view of the cube, it is fully solve i.e. it contains both detail level and aggregate level data in the same view. Records have a level tag for each hierarchy defining the aggregation level for the row wrt the specific hierarchy.
The SQL that Cognos 8 needs to generate must include filters for each hierarchy level in adition to any other dimension filters that are required when creating a report or drilling down/through a report.
select * CUBEVIEW a
where ( a.PRODUCT_LEVEL = 'FAMILY' and
a.TIME_LEVEL = 'ALL_YEARS' and
a.CHANNEL_LEVEL = 'TOTAL_CHANNEL' and
a.CUSTOMER_LEVEL = 'TOTAL_MARKET' and
a.PRODUCT_CLASS_DESC = 'Software/Other' )
Does anyone have any ideas on how I might model this in Framework Manager so that every query created will automatically include the correct dimension level filters.
Thanks