[SSAS] Quick notes on logging queries run against the cube

There's a lot of details out there about OlapQueryLog, but here are the 3 things I ran into setting it up:


Tips and tricks from setting this up:

1. Do it on whatever server hosts the cube, since it will determine what version of the SQL driver it needs.
   
2. Event Viewer will tell you the errors you’re having, be they permissions, bad version of the SQL driver, etc.
   
3. The key settings:
   
   • Log\QueryLog\CreateQueryLogTable = true
     
   • QueryLogConnectionString (click and set it)
     
   • QueryLogSampling is "every X queries, save the query to table". So the default means every 10th query gets saved.
     

[Jobs] Search job history for a particular date range

Pieces cribbed off several people. Enjoy.

select job_name, run_datetime, run_duration from (
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
WHERE run_datetime between '2009/07/24 01:00' AND '2009/07/24 01:59'
order by job_name, run_datetime