Data modeling

[#1]

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

[#2]

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.

[#3]

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