Monthly Archives: May 2008
Ranking results from MDX queries
This post explains how you can create a ranking of data from an OLAP MDX query. This will take the results from the query, and assign a ranking to each row. i.e. 1st, 2nd, 3rd best rows etc.
The first thing to do is to decide two things.
1) What measure do you want to rank by
2) What data set are you returning
Let’s assume we want to rank all stores by sales value. The basic non-ranked MDX query would be something like this
SELECT
{[Measures].[Sales Value]} ON 0,
{[Store].[Store Name].members} ON 1
FROM
[SalesCube]
So our measure is Sales Value, and our data set (granularity) is Store Name. We now want to create an ordered set of this data, ordered by Sales Value. We do this with the ORDER() function, which takes a set, a measure and either ascending or descending as its parameters. Note that by specifying the attribute twice we remove the [All] member from the set.
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
SELECT
{[Measures].[Sales Value]} ON 0,
{[OrderedSet]} ON 1
FROM
[SalesCube]
The next stage is to apply a ranking to this ordered set. Helpfully, MDX provides us with a Rank() function, which takes a member and a set as its parameters. All it does is locate the member within the set and return its position. Because we have ordered the set, it will give us the ranking.
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Store].[Store Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value]} ON 0,
{[OrderedSet]} ON 1
FROM
[SalesCube]
You can now easily expand this to only show you the top x records, by using the Head() function on the ordered set. In this example we’re only showing the top 10. You could also use Tail() to find the bottom x records. Other functions you can use include TopPercent(), TopSum(), BottomPercent() and BottomSum().
WITH SET [OrderedSet] AS
ORDER([Store].[Store Name].[Store Name].MEMBERS,
[Measures].[Sales Value],
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Store].[Store Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[SalesCube]
SSRS Report Performance monitoring
Today’s Frog-Blog top-tips are quite simple ones, but ones that I always find very useful. SSRS Report Performance monitoring.
Once you start to build up a few Reporting Services reports, you need to find out how they’re performing, whether any particular reports are causing the server problems and even which users are running which reports. The following set of queries should point you in the right direction. They should all be run against the ReportServer database.
The 20 most recently run reports
SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart,
EL.[RowCount], EL.ByteCount,
(EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds,
EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
ORDER BY TimeStart DESC
The slowest reports (in the last 28 days)
SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun,
AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS AverageProcessingTime,
Max((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS MaximumProcessingTime,
Min((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS MinimumProcessingTime
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
GROUP BY C.Path,C.Name
ORDER BY AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)) DESC
The most active users
SELECT TOP 10 EL.UserName, Count(*) AS ReportsRun,
Count(DISTINCT C.[Path]) AS DistinctReportsRun
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
GROUP BY EL.UserName
ORDER BY Count(*) DESC
The most popular reports
SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun,
AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS AverageProcessingTime,
Max((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS MaximumProcessingTime,
Min((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering))
AS MinimumProcessingTime
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.TimeStart>Datediff(d,GetDate(),-28)
GROUP BY C.Path, C.Name
ORDER BY Count(*) DESC
Failed Reports
SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart,
EL.[RowCount], EL.ByteCount,
(EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds,
EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering
FROM ExecutionLog EL
INNER JOIN Catalog C ON EL.ReportID = C.ItemID
WHERE EL.Status <> 'rsSuccess'
ORDER BY TimeStart DESC
There are countless other variations, but this should be enough to get you going.
[Update: 05/09/2011 – Thanks to Jonathan [Twitter|Website] for pointing out a typo!]