topic Re: Data model with various level of data granularity in QlikView

Data model with various level of data granularity

Anonymous — Tue, 16 Feb 2016 03:52:23 GMT

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!

Re: Data model with various level of data granularity

marcus_sommer — Tue, 16 Feb 2016 10:03:17 GMT

Maybe this is helpful: Fact Table with Mixed Granularity

- Marcus

Re: Data model with various level of data granularity

Anonymous — Tue, 16 Feb 2016 10:11:35 GMT

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

Re: Data model with various level of data granularity

marcus_sommer — Tue, 16 Feb 2016 11:08:40 GMT

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