Data modeling

Thu, 08 Oct 2015 09:27:57 GMT

Hello

I am trying to create a data model and Qliksense architecture for an application, the requirement has several KPIs (some of which are yet to be defined) , in some indicators I have the famous 'count(distinct IDs)' and the dimensions are multiple , some are related some not.The data volume is huge (necessary input tables vary from 1000 rows to 200 millions rows) and the data keeps on growing by the triple each year. so the first instinct I got was to aggregate (gather all dimensions keys and Facts into one big fact table, aggregate and then have like 400 000 rows fact table with small dimension tables around) , tried to aggergate the KPIS with 'count(distinct IDs)' but the result is different from the actual correct result

example: count(distinct IDs) OF year=2015

non aggregated: 100 000

aggregated: 127 000

I have attached a cross table of indicators and dimensions to show which indicator is calculated at wich level, (the Dimension Dim1... Dim4 are a hierarchy Dime4 being the Macro level, the others O, A, X.. are not related)

Any idea Would be very helpful

Regards

Re: Data modeling

Thu, 08 Oct 2015 15:04:30 GMT

what is your aggregated data granularity?

While aggregating data , create new field with Count( Distinct ID) as Counter

Use Sum(Counter) as your final expression.

Re: Data modeling

Fri, 09 Oct 2015 07:52:28 GMT

That's the thing about sum(flag) gives the wrong values as I mentionned before so the only thing I can do is to load Data as it is and then do the county(distinct) directly

topic Re: Data modeling in QlikView

Data modeling

Re: Data modeling

Re: Data modeling