CODEWITHSUNDEEP
ssasmdx
binary01
binary01

Reputation: 1898

MDX/SSAS sum of certain values over totals - calculate success/failure rate

I have a simplified example cube used for learning purposes, and to try to figure out a more complex problem.

The cube represents a small web server log,

I can get a breakdown on number of hits per host and http status code with the MDX

SELECT NON EMPTY { [Measures].[CNT HITS] } ON COLUMNS, 
       NON EMPTY { ([DIM NOS STATUSCODE].[Statuscode].[Statuscode].ALLMEMBERS * 
                    [DIM NOS HOST].[HOST].[HOST].ALLMEMBERS ) }  ON ROWS 
FROM [DW]

enter image description here

Now what I would like is to make groups over various HTTP status codes to e.g. show the percentage of successful hits (all 2xx status codes), the percentage unsuccessful hits (all non 2xx status codes).

I can do this with SQL, but I'm at a loss on how to do it with MDX. e.g. with SQL I'd do:

select HOST,  
       sum(CNT_HITS) as HITS ,
       SUM(CASE WHEN s.statuscode div 100 = 2 THEN CNT_HITS ELSE 0 END)/sum(CNT_HITS) * 100 as success_percent,
       SUM(CASE WHEN s.statuscode div 100 = 2 THEN 0 ELSE CNT_HITS END)/sum(CNT_HITS) * 100 as failed_percent,
       sum(CASE WHEN s.statuscode = 401 THEN CNT_HITS ELSE 0 END)/sum(CNT_HITS) * 100 as auth_fail_percent
       from FACT_NOS_HTTPLOG fact  
group by HOST;

And for the data shown in the above screenshot, I'd get

+-----------------+------+-----------------+----------------+-------------------+
| HOST            | HITS | success_percent | failed_percent | auth_fail_percent |
+-----------------+------+-----------------+----------------+-------------------+
| www.example.com | 1610 |         93.1677 |         6.8323 |            6.2112 |
| www.test.com    |   50 |          0.0000 |       100.0000 |            0.0000 |
+-----------------+------+-----------------+----------------+-------------------+

But how can I accomplish this with MDX ?

Upvotes: 1

Views: 358

Answers (3)

whytheq
whytheq

Reputation: 35605

It will involve a certain amount of hard coding - although you could add these measures into your cube script.

WITH 
  MEMBER [Measures].[failed_percent] AS
    DIVIDE(
       (
          [DIM NOS STATUSCODE].[Status].&[Failed]
         ,[DIM NOS HOST].[HOST].currentmember
         ,[Measures].[CNT HITS]
       )
      , (
          [DIM NOS STATUSCODE].[Status].[All]
         ,[DIM NOS HOST].[HOST].currentmember
         ,[Measures].[CNT HITS]
       )
    )
SELECT 
NON EMPTY 
   { 
     [Measures].[CNT HITS] 
    ,[Measures].[failed_percent]   
   } ON COLUMNS, 
NON EMPTY 
   [DIM NOS HOST].[HOST].[HOST].ALLMEMBERS
  ON ROWS 
FROM [DW];

Upvotes: 0

Danylo Korostil
Danylo Korostil

Reputation: 1484

Add an extra attribute [Status] to your [DIM NOS STATUSCODE] dimension and use MDX for percentage, like this:

([DIM NOS STATUSCODE].[Status].&[Failed],[Measures].[CNT HITS]) / [Measures].[CNT HITS]

Upvotes: 1

The Dumb Radish
The Dumb Radish

Reputation: 896

I think the easiest way to accomplish this is to add a column to your fact table (or view/query) that would contain keys for either success_percent, failed_percent or auth_fail_percent. Then create a new dimension with these 3 members. Join to the fact and you have your solution without the need for any MDX at all.

Upvotes: 1

Related Questions