SQL Server 2008-2016

Dominique Verrière

Dialogue Formation Informatique

SQL Server 2008-2016

MDX : Ranking et cumul glissant

SQL Server : Le blog de Dominique Verrière 2

Introduction

Ce petit fascicule fait partie d’une liste de notes que je publie gratuitement sur mon site.

J’essaie de mettre dans ces notes mon expérience de terrain en toute indépendance de Microsoft.

Dominique Verrière

Table des matières Cumuls glissants et ranking .......................................................................................... 6

Cumuls glissants avec une période de temps ........................................................................................................ 6

Ranking des revendeurs ........................................................................................................................................ 8

Ranking et classement ABC ou 80/20 .................................................................................................................. 10

Conclusion ........................................................................................................................................................... 12

Contacter l’auteur ...................................................................................................... 13

Sommaire

SQL Server : Le blog de Dominique Verrière 5

MDX : Ranking et cumuls glissants

SQL Server Le blog de Dominique Verrière

Cumuls glissants et ranking Ce petit post pour mettre au propre les réponses aux questions d’un participant à l’une de mes formations

chez Orsys.

Cumuls glissants avec une période de temps C’est tout à fait classique, la fonction PeriodsToDate étant là pour ce rôle.

Attention, cette fonction est mal nommée et devrait plutôt s’appeler PeriodsInto ou quelque chose du genre

car il s’agit d’obtenir toutes les périodes ded temps à l’intérieur d’une autre.

Prenons ce genre de requête :

select [Measures].[Montant Ventes Revendeur] on columns, [Date Commande].[Mois].[Mois] on rows from [Analyse Ventes];

On peut très simplement obtenir un cumul glissant par année de cette manière :

with member [Measures].CumulGlissant as sum(periodstodate( [Date Commande].[CalendrierLegal].[Annee], [Date Commande].[CalendrierLegal].currentmember ), [Measures].[Montant Ventes Revendeur])

MDX : Ranking et cumuls glissants

SQL Server : Le blog de Dominique Verrière 7

select {[Measures].[Montant Ventes Revendeur],CumulGlissant} on columns, [Date Commande].[CalendrierLegal].[Trimestre] on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

Ici le cumul a été fait au niveau des trimestres :

Et il est très facile de faire la même chose au niveau des mois :

with member [Measures].CumulGlissant as sum(periodstodate( [Date Commande].[CalendrierLegal].[Annee], [Date Commande].[CalendrierLegal].currentmember ), [Measures].[Montant Ventes Revendeur]) select {[Measures].[Montant Ventes Revendeur],CumulGlissant} on columns, [Date Commande].[CalendrierLegal].[Mois] on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

On notera qu’un seul mot m’a permis d’obtenir ce nouveau résultat : c’est toute la magie du MDX !

MDX : Ranking et cumuls glissants

SQL Server Le blog de Dominique Verrière

Par ailleurs, on remarque qu’un sous-cube m’a permis de ‘slicer’ sur l’année 2017.

Ranking des revendeurs Nous souhaitons établir un classement des revendeurs selon leur chiffre d’affaires.

with member [Measures].CaRevendeur as case when isempty([Measures].[Montant Ventes Revendeur]) then 0 else [Measures].[Montant Ventes Revendeur] end, format_string="currency" member [Measures].Rang as RANK([Revendeur].[Revendeur].currentmember , [Revendeur].[Revendeur].[Revendeur],[Measures].CaRevendeur ) select {[Measures].CaRevendeur,[Measures].Rang} on columns, [Revendeur].[Revendeur].[Revendeur] on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

MDX : Ranking et cumuls glissants

SQL Server : Le blog de Dominique Verrière 9

Tout cela est un peu désordonné, un order y mettra bon ordre.

-- Ranking des revendeurs with member [Measures].CaRevendeur as case when isempty([Measures].[Montant Ventes Revendeur]) then 0 else [Measures].[Montant Ventes Revendeur] end, format_string="currency" member [Measures].Rang as RANK([Revendeur].[Revendeur].currentmember , [Revendeur].[Revendeur].[Revendeur],[Measures].CaRevendeur ) select {[Measures].CaRevendeur,[Measures].Rang} on columns, order ( [Revendeur].[Revendeur].[Revendeur], [Measures].Rang) on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

MDX : Ranking et cumuls glissants

SQL Server Le blog de Dominique Verrière

Ranking et classement ABC ou 80/20 -- Cumuls glissant et ranking with member [Measures].CaRevendeur as case when isempty([Measures].[Montant Ventes Revendeur]) then 0 else [Measures].[Montant Ventes Revendeur] end, format_string="currency" set RevendeursTries as order ( [Revendeur].[Revendeur].[Revendeur],[Measures].CaRevendeur, desc) member [Measures].Rang as RANK([Revendeur].[Revendeur].currentmember , RevendeursTries,[Measures].CaRevendeur ) member [Measures].CumulGlissant as sum(head(RevendeursTries,[Measures].Rang),[Measures].CaRevendeur) select {[Measures].CaRevendeur,[Measures].Rang,[Measures].CumulGlissant} on columns, RevendeursTries on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

MDX : Ranking et cumuls glissants

SQL Server : Le blog de Dominique Verrière 11

Il ne reste plus qu’à faire la part du grand total et le tour est joué :

-- Cumuls glissant et ranking with member [Measures].CaRevendeur as case when isempty([Measures].[Montant Ventes Revendeur]) then 0 else [Measures].[Montant Ventes Revendeur] end, format_string="currency" set RevendeursTries as order ( [Revendeur].[Revendeur].[Revendeur],[Measures].CaRevendeur, desc) member [Measures].Rang as RANK([Revendeur].[Revendeur].currentmember , RevendeursTries,[Measures].CaRevendeur ) member [Measures].CumulGlissant as sum(head(RevendeursTries,[Measures].Rang),[Measures].CaRevendeur) member [Measures].QuatreVingtVingt as [Measures].CumulGlissant / ([Revendeur].[Revendeur].[All],[Measures].CaRevendeur) ,format_string="percent" select {[Measures].CaRevendeur,[Measures].Rang,[Measures].CumulGlissant,[Measures].QuatreVingtVingt} on columns, RevendeursTries on rows from (select [Date Commande].[CalendrierLegal].[Annee].&[2017] on columns from [Analyse Ventes] ) ;

MDX : Ranking et cumuls glissants

SQL Server Le blog de Dominique Verrière

Conclusion Si besoin en était, on voit là la puissance du MDX …

Contacter l’auteur

SQL Server: Le blog de Dominique Verrière

Contacter l’auteur

Dominique Verrière est un consultant spécialisé sur les technologies SQL Server : moteur relationnel, SSIS, SSRS et SSAS. Il intervient dans les entreprises pour des missions d'audit, de suivi de performances, d'administration de bases de données. Les bases de données dont il s'occupe peuvent contenir plusieurs milliards de lignes et atteindre des Téra octets; son expérience du terrain est donc significative. Afin de compléter cet article, un blog www.dominiqueverriere.fr est régulièrement mis à jour avec des articles sur les nouveautés ou expériences nouvelles de l'auteur