Can I use aggregate tables for distinct counts? 1

[26point2]

Hi there,

I'd like to create a report with a distinct count of users, sum of their pageviews by month, and a derived metric like this:

Time Unique Users Page Views PageViews/User
Oct 2003 1,000,000 5,000,000 5
Nov 2003 2,000,000 9,000,000 4.5

...and then allow users to drill to week or day, again showing a distinct count of users and the sum of pageviews.

Can I implement weekly and monthly aggegate tables, with MSTR being smart enough not to sum on unique users as I drill up by date, since I need a distinct count? Otherwise, I could have the queries run dynamically each time, but that involves millions of rows. Any thoughts?

Much appreciated,
26point2

 

[entaroadun]

It depends on your reporting requirements. In order to do something like this, you will most likely have to modify your COUNT metric into a SUM metric, and embed the count(distinct) expression in the fact. Unfortunately, it's hard to generate reliable SQL for this metric for every report you could possibly create.

My advice is to leave the current COUNT metric alone; create a new fact and new metric using a SUM function that you call a COUNT. Use this metric only in production reports that you know it will work in. For adhoc reports, use the generic COUNT metric. You won't get the performance lift for adhoc reports, but they will at least work.

Of course, if you have no adhoc reporting requirements, then you can tweak the SQL for your production reports however you want.

Let me and the rest of the forum know if you need help setting up this custom metric and fact.

 

[26point2]

Thanks, entaroadun. Not sure what I'd do without folks like you...

26point2