Data model with various level of data granularity

[Anonymous]

May I ask how measure with different granularity can be captured in QlikView data model? I have been researching and trying out different methods but all not working as intended.

Example: Location dimension with hierarchy Region > Country > City.

Hyperion Financial Management system captures reported sales amount at each level such as below. We cannot store only the most detail level and allow auto aggregated feature of qlikview as measure at higher level dimension is not the sum of its sub members. 

South East Asia - $10 mil

• Singapore - $6 mil
• Malaysia - $5.5mil
  • KL - $3 mil
  • JB - $2.9 mil

Is there a way to capture such data in QlikView Data Model? As the number of levels involved in dimension hierarchy can be changed frequently at HFM, dynamic script is needed as well. Attached is sample data set.

Thank you in advance!

[marcus_sommer]

Maybe this is helpful: Fact Table with Mixed Granularity

- Marcus

[Anonymous]

HI Marcus,

Thank you for your help.

I have tried that approach and it does not work as needed. Reason being the suggested solution is associating 2 sets of dimensions as mixed granularity while this requirement is to capture measure for 1 set of dimensions with different hierarchy level.

Regards

Thinn

[marcus_sommer]

For me it's not clear where the problem is - the hierachy, different granularity or the data-quality (if sum of details are different to consolidated levels)? If the data-quality is the problem respectively the data-structures is in some way unknown you need to handle on it at first.

Further this might be useful: Unbalanced, n-level hierarchies.

- Marcus