Data model design for mobile app subscribers

[Anonymous]

Hello,

I have been working with a mobile applications subscriber data.

In the mobile app, a new user would first register with an email address and some personal details. Once this step is completed, a registration date is recorded.

The next step would allow the subscriber to link his/her customer profile if they already have one. Once this step is completed a customer linked date is recorded.

All this information is stored in an Oracle data warehouse in a dimension table which holds 1 records per subscriber along with the registration and customer linked dates.

My company also has several calendars and I need to be able to report weekly and monthly between these calendars.

I have tried several options on my data model, some to a degree of success however I don't think I have the best data model. I want to avoid doing COUNT(DISTINCT ....) in my expressions as this seems to slow down my model (especially with the model I created with AsOf table).

I have attached my latest version of the model which uses an AsOf table, however performance is very slow when rendering the charts on the sheet named "Subscribers". The figures in these charts are correct however performance is very poor when switching between views with the buttons I have. It might also be worth noting that I have a calculated dimension based on a variables value.

I need to report on:

Cumulative number of subscriber to date

Cumulative number of customers to date

New subscribers for selected period

New Customers for selected period

The period could be Weekly or Monthly and across different financial calendars.

[marcus_sommer]

A count(distinct FIELD) itself is not a performance issue itself but if your expression contained fields from different tables the virtual table which Qlik needs to create to perform the aggregations might become very large - and this table-creation is not multi-threaded like the count() else it's just single-threaded. More background to it could you find here: Logical-Inference-and-Aggregations.

And I think this happens in your case - the as-of-table is linked per calendar to the facts - and creates quite huge virtual tables. To handle multiple dates is definitiv not an easy job and I'm not sure that I understand how they are related to eachother and what do you want to calculate with them but I think your approach with this kind of as-of-table isn't suitable at all .
Of course there might be some optimization here and there but especially because there are just 1M of records in the facts and the charts are really slow I suggest to consider a re-design of the whole datamodel.

For this I recommend to read the postings about using multiple and canonical calendars and creating YTD, MTD, ..., rolling periods and so on with flags and considering an as-of-table only as the last option if no other/better way is possible: How-to-use-Master-Calendar-and-Date-Values.

- Marcus