How to handle degenerate dimension in IBM Cubing services

Check list of all posts

Problem: There is no easy way to create a fact dimension relationship in cubing services. For example, given the fact table with all dimension keys, measures and  about 80+ attribute columns, you can’t make this fact table as dimension as fact-fact join is NOT allowed, which can be done in SSAS and Cognos FM.

Solution: The following steps make it possible to make a fact dimension relationship:
1. Create view “Fact Information” as
SELECT * FROM fact table
2. Create a new dimension as “Fact Information”
3. Add existing table using view  “Fact Information”
4. Add fact-Dimension relationship as 1:1 between fact and view with the same key
5. Add all attribute from view
6… validate cube model
 
This approach opens a great deal of opportunities to resolve many modeling issues in cubing services, as it makes the Cognos FM functionalities possible in Cubing services. However,

Problem: We can build degenerate dimensions for fact information, regardless of using MQT, or views. As soon as the Cube Server starts, it will load all these degenerate dimensions into the member cache. There are two ways to load dimensions into member cache: 1) static cache, load to memory, 2) dynamic cache: load to compressed file. The bottom line is that these large degenerate dimensions won’t work as it requires too big memory and too big file for cube server to handle it. (Actually, we can monitor it from cube server)

Proposed solution 1: DO NOT build such large degenerate dimensions into cube model at all, instead, let Cognos studio handle it. Cube will only handle high level aggregated measure. In case when low level detail information needed, we can use report to drill through the detail, from OLAP package to DMR, or relational package. In this case, we can create Cognos FM relational packages for Drill-through target report, which is definitely durable.

Proposed solution 2: Add all these attributes (100 columns) into the fact table to avoid cube server loading huge amount of data. We can put such attributes into separated folder for user interface. However, it might require some effort to handle performance tuning to make cube services engine smart enough only load real measure, or grasp fact cache for most of time.   

Solution 1 is preferred option