July 2014 Archives

Hi Folks:

To celebrate the eve of my nation's Independence Day, I free myself from only querying across the data of a single Analytics asfact (Analytics Server fact) table. As a reminder, in most Analytics installations, the system archives events each month to put events from the prior month into its own table. This makes it harder to get a view across a period greater than what is in a particular table.

Consider first this query that tells me the users who have logged into the system in the past week and the number of their logins, with extra info about when the first and last of those logins were:

select distinct
u.name user_name, count (u.name) total_logins,
min(occurred) earliest_login_of_period, max(occurred) last_login
from asdim_users u inner join asfact_logins logins on u.userid = logins.userid
where occurred > GETDATE()-7
group by u.name
order by last_login desc


That's fine, but what if I want to get the past 45 days? Or 90 days? I can change the query so that instead of treating "login" as an alias of a single table, it instead becomes the name of a derived table, and I create that derived table by joining the current asfact_login with the appropriate number of archived asfact_login_X tables:

select distinct
u.name user_name, count (u.name) total_logins,
min(occurred) earliest_login_of_period, max(occurred) last_login
from asdim_users u left join (
select occurred,userid from asfact_logins
union
select occurred, userid from asfact_logins_2014_05
union
select occurred, userid from asfact_logins_2014_04
) logins on u.userid = logins.userid
where occurred > GETDATE()-999
group by u.name
order by last_login desc


I don't know whether you're excited about that, but from my perspective:

fireworks.jpg