mercredi 12 mars 2014

SSIS : Paramétrer un Lookup en mode Full Cache

Utiliser une requête dynamique (expression SSIS) dans un Lookup en mode Full Cache, cela peut s’avérer indispensable si l’on veut réduire dynamiquement le volume de données à monter en cache. Notamment, dans le cas de traitements itératifs, et que l’on ne veut pas monter en cache tout le contenu de la table/requête à chaque itération, mais juste ce qu’il faut.

En effet, même si l'interface du composant Lookup n'expose pas directement la possibilité d'utiliser des variables comme dans les sources OLEDB ou une expression dans la propriété "SqlCommand" comme dans les tâches d’exécution SQL. Le composant Lookup dispose bien d'une propriété "SqlCommand" paramétrable, sauf que celle-ci est exposée au niveau du flux de données (DFT), comme le montre la capture d'écran suivante :


Pour le mode opératoire rien de plus simple :

  • Configurer le Lookup avec une requête sans variable afin de l'initialiser et faire le mapping. 
  • Sélectionner au niveau du DFT la propriété "[Nom du Lookup].[SqlCommand]" du Lookup à paramétrer et spécifier la requête à utiliser avec la bonne variable dans la partie "Expression".




mardi 4 mars 2014

[SQL] : Suppression de tous les utilisateurs d'une base SQL Server

Dans cet article, je vais parler d'un sujet que je viens de traiter et qui consiste à faire le ménage au niveau des bases de données récupérées d'un serveur et restaurées sur un autre. Et cela en supprimant tous les utilisateurs de ces bases de données, afin d'y appliquer une sécurité un peu plus restrictive.

Avant de pouvoir supprimer des utilisateurs ou des rôles SQL, il faut allouer tous les schémas au rôle "dbo". En effet, on ne peut pas supprimer un utilisateur ou un rôle qui est "owner" d'un schéma.

Pour cela, un curseur sur tous les schémas :

DECLARE @SchemaName SYSNAME,@SQL NVARCHAR(1000)
DECLARE SchemaNames CURSOR FOR
      SELECT s.name FROM sys.schemas s
      WHERE s.principal_id NOT IN (SELECT dp.principal_id 
                                   FROM sys.database_principals dp 
                                   WHERE dp.is_fixed_role=1 OR name IN                                         ('dbo','guest','INFORMATION_SCHEMA','sys','public'))
OPEN SchemaNames
FETCH NEXT FROM SchemaNames INTO @SchemaName
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @SQL = 'ALTER AUTHORIZATION ON SCHEMA::[' + @SchemaName + '] TO dbo'
      EXEC sp_executesql @SQL
      FETCH NEXT FROM SchemaNames INTO @SchemaName
END
CLOSE SchemaNames

DEALLOCATE SchemaNames;

La deuxième étape consiste à supprimer les utilisateurs ainsi que les rôles de base de données, et là aussi il y a une contrainte, c'est qu'on ne peut pas supprimer un utilisateur référencé dans un rôle.
Pour cela, la suppression sera faite en deux étapes:
  • Suppression des utilisateurs référencés dans chaque rôle via un "sp_droprolemember" avant de supprimer le rôle même.
  • Puis suppression des utilisateurs.

Voici le script complet :

DECLARE @LoginRoleName SYSNAME,@SQL NVARCHAR(1000),@Type VARCHAR(1)
DECLARE DBROLELOGINS CURSOR FOR

SELECT NAME,[type] FROM sys.database_principals
WHERE name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND is_fixed_role = 0

OPEN DBROLELOGINS
FETCH NEXT FROM DBROLELOGINS INTO @LoginRoleName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Type = 'R'
BEGIN
--DECLARE @RoleName sysname
--set @RoleName = @LoginRoleName
BEGIN
      DECLARE @RoleMemberName sysname
      DECLARE Member_Cursor CURSOR FOR
      select [name]
      from sys.database_principals
      where principal_id in (
            select member_principal_id
            from sys.database_role_members
            where role_principal_id in (
                  select principal_id
                  FROM sys.database_principals where [name] = @LoginRoleName  AND type = 'R' ))

      OPEN Member_Cursor;

      FETCH NEXT FROM Member_Cursor
      into @RoleMemberName

      WHILE @@FETCH_STATUS = 0
      BEGIN

            exec sp_droprolemember @rolename=@LoginRoleName, @membername= @RoleMemberName
            PRINT @LoginRoleName
            FETCH NEXT FROM Member_Cursor
            into @RoleMemberName
      END;

      CLOSE Member_Cursor;
      DEALLOCATE Member_Cursor;
END
SET @SQL = 'DROP ROLE [' + @LoginRoleName + ']'
EXEC sp_executesql @SQL
PRINT @SQL
END
IF @Type <> 'R'
BEGIN
SET @SQL = 'DROP USER [' + @LoginRoleName + ']'
PRINT @SQL
EXEC sp_executesql @SQL
END
FETCH NEXT FROM DBROLELOGINS INTO @LoginRoleName,@Type
END
CLOSE DBROLELOGINS
DEALLOCATE DBROLELOGINS;

[SQL] : Suppression de plusieurs tables sans utilisation de curseur

Un petit post rapide pour montrer comment supprimer plusieurs tables sans passer par un curseur.

Dans mon cas, le but était de supprimer toutes les tables du schéma "staging" afin d'en recréer, que celles qui sont vraiment utilisées.

Pour cela, une façon rapide consiste à utiliser le script suivant :

USE MyDB
GO
DECLARE @cmd VARCHAR(MAX)=''
SELECT
@cmd = @cmd + 'DROP TABLE ' + TABLE_NAME + ';
'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'stg'
PRINT (@cmd)

EXEC (@cmd)


Ce script peut être adapté pour d'autres cas d'usages.

lundi 10 février 2014

SSRS : Export Excel et fusion de cellules

Un petit post rapide afin de parler d'un sujet que tous les développeurs SSRS connaissent...ou pas, et qui est le problème de fusion de cellules au moment de l'export Excel.

La solution que tout le monde connait et qui consiste à aligner des colonnes des tableaux/Matrice et des zones de textes, peut s'avérer, parfois, insuffisante, et cela s'explique par le fait, qu'Excel utilise en interne les points comme unité de mesures et qu'au moment de l'export converti les pouces, les pixels, les centimètres en points.

Donc, pour éviter toute complication et s'assurer que cela fonctionne du premier coup, spécifier toutes les mesures "position" et "taille" en points.

Et en bonus, un centimètre est égal à 28,35 points.

samedi 13 avril 2013

[SSAS] : Formatage des mesures

Un post rapide pour voir comment généraliser le formatage des mesures "FORMAT_STRING" dans SSAS. Cette tâche qui est souvent réalisée de manière manuelle, peut devenir fastidieuse dans le cas ou nous avons un grand nombre de mesures, et peut être automatisé via un Script MDX.

Afin de réaliser cette tâche de manière aisée il faut mettre en place au préalable une règle de nommage des mesures, en y ajoutant un préfixe/suffixe, par exemple:

  • Montant : pour toutes les mesures de type Currency
  • Nombre : pour toutes les mesures de type Standard
  • % :  pour toutes les mesures de type Percent
Il ne reste plus qu'à définir le script MDX dans l'onglet Calculations du Cube de la manière suivante:

Format_String( Filter (Measures.ALLMEMBERS,
                       Instr(Measures.CurrentMember.Name, "Montant") > 0
                       )
             ) = "Currency";
Format_String( Filter (Measures.ALLMEMBERS,
                       Instr(Measures.CurrentMember.Name, "Nombre") > 0
                       )
             ) = "Standard";
Format_String( Filter (Measures.ALLMEMBERS,
                       Instr(Measures.CurrentMember.Name, "%") > 0
                       )
             ) = "Percent";


Et voici le résultat obtenu:


En espérant que ça pourrait vous aider...



vendredi 29 mars 2013

[SSRS] : Concaténation de plusieurs lignes en une seule

Un petit post rapide, pour montrer comment concaténer plusieurs lignes d'une requête en une seule, par exemple en affichant pour chaque gamme de produit, la liste des produits dans la même cellule, séparés par une virgule.

Le but est de passer de cette représentation :


 A quelque chose qui ressemble à :


Pour cela, la version 2008 R2 a apporté une nouvelle fonction le "LookupSet" qui est, un peu similaire à la fonction "Lookup" mais qui permet de récupérer un jeu de données au lieu d'une seule valeur, à partir d'un autre Dataset.

Dans mon cas les données proviennent du même dataset, donc je me suis livré au jeu et j'ai testé le "LookupSet" en faisant une jointure sur le même champ (Gamme) du Dataset du tableau, et ce qui fonctionne très bien, puisque la requête est généré avant l'évaluation de l'expression SSRS.

Et techniquement, la syntaxe de l'expression SSRS est la suivante :

Join(LookupSet(Fields!GammeDesc.Value, Fields!GammeDesc.Value, Fields!ProduitDesc.Value, "DataSet1"),", ")

Avec en vert, la clé de jointure qui pointe sur le même champ et en bleu le champ qu'on veut récupérer et puis la fonction "Join" pour concaténer le résultat retourné par le "LookupSet".

En espérant que ça pourrait aider.



lundi 18 mars 2013

[SSRS] : Temps d’exécution de rapport

Parfois, pour debugguer un rapport, on peut avoir besoin d'afficher le temps global d’exécution du rapport en pied de page ou à l’intérieur même du rapport, et cela sans faire appel à la vue ExecutionLog de la base ReportServer.

La solution consiste à utiliser la fonction Now() et la variable globale SSRS Global!ExecutionTime, la première permettant de récupérer l'heure d'affichage effectif du rapport et la deuxième permettant de récupérer l'heure de génération du rapport et plus exactement à partir du moment où l'on clique sur le bouton "Afficher le rapport".

Donc, en faisant la différence entre Now() et Global!ExecutionTime nous pouvons avoir le temps d’exécution global du rapport, techniquement cela se traduit de la manière suivante :

System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds

Et pour faire quelque chose de plus complet, pour les rapports qui dépasserais une minute de temps d’exécution voir une heure, même si rare, voici l'expression complète :

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "< 1 seconde(s)",

(

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " heure(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +

IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " seconde(s)", ""))

)

Et pour tester le rapport, nous ajoutons un Dataset avec un WAITFOR DELAY '00:00:03' pour simuler un temps d’exécution de 3 secondes.
Ce qui donne le résultat suivant :


jeudi 27 décembre 2012

[SQL] : Limitation comptage distinct sur une fenêtre de données

SQL Server propose un ensemble de fonctions d’agrégation comme la somme, la moyenne, le comptage, etc..., ces fonctions combinées aux clauses OVER, offrent des possibilités de calcul et d'analyse très intéressantes.

Pour répondre à une problématique métier dont la solution technique devait, initialement, s'appuyer sur un : 
COUNT (DISTINCT MaColonne1) OVER (PARTITION BY MaColonne2), j'ai été confronté à l'erreur suivante :

Incorrect syntax near 'distinct'

Après quelques recherches, il s’avère que c'est une limitation liée au moteur de base de données SQL Server.

En testant la requête sur une instance SQL 2012, cette limitation est toujours présente, la seule chose qui a été améliorée est le message d'erreur, qui est beaucoup plus explicite :

Use of DISTINCT is not allowed with the OVER clause

Une solution de contournement, consiste à combiner la fonction DENSE_RANK : qui permet de caclculer le rang de la ligne par valeur distincte, et la fonction MAX pour récupérer la valeur la plus élevée de ce rang.

/* Requête initialement utilisée*/
SELECT DISTINCT [CalendarYear]
      ,[FrenchMonthName]
      ,COUNT(DISTINCT([WeekNumberOfYear])) OVER (PARTITION BY [CalendarYear]) AS NombreSemaineParAnnee
FROM [dbo].[DimDate]

/* Solution de contournement basée sur le Dense_Rank et le Max*/

WITH cte AS
(
SELECT [CalendarYear]
      ,[FrenchMonthName]
      ,DENSE_RANK() OVER (PARTITION BY [CalendarYear] ORDER BY [WeekNumberOfYear]) AS NombreSemaineParAnnee
FROM [dbo].[DimDate]
)
SELECT DISTINCT [CalendarYear]
      ,[FrenchMonthName]
      ,MAX(NombreSemaineParAnnee) OVER (PARTITION BY [CalendarYear]) AS NombreSemaineParAnnee
FROM cte


Source : Connect

lundi 24 décembre 2012

[SSAS] : Modifications nécessitant un ProcessFull

Pour qu'un cube soit accessible aux utilisateurs, il faut qu'il soit dans un état "Processed".
Parfois, on peut être amené à y apporter des modifications/corrections mais sans devoir re-processer tout le cube.

Avant de réaliser ces modifications, certains points sont à prendre en compte, comme les opérations ayant un impact sur l'état du cube et pouvant le rendre inaccessible et donc en conséquence nécessitant un PorcessFull.

Les opérations mettant le cube dans un état "Unprocessed" sont les suivantes:
  • L'ajout d'une hiérarchie d'attribut dans une dimension.
  • Modification des relations entre les dimensions et les groupes de mesures.
  • Modification des relations entre les hiérarchies d'attributs.
  • Ajout d'une mesure ou d'un groupe de mesure.
  • Changement de la fonction d’agrégation d'une mesure.
  • Modification de la propriété OrderBy ou OrderByAttribute.
  • Modification de la propriété UnknownMember.
  • Ajout/suppression de la traduction.
  • ProcessUpdate d'une dimension avec des relations de type rigide et dont les données ont changées (Exemple: Produit A appartenant à la Catégorie A et ayant été affecté, entre temps, à la Catégorie B).
  • Et bien évidemment un ProcessFull au niveau d'une dimension, pour cela, SSMS et BIDS dispose d'un bouton d'analyse d'impact et qui est accessible au moment du processing :

Donc avant de réaliser des modifications, analysez bien les impacts de celles-ci sur l'état du cube et ainsi, le cas échéant, les planifier en dehors des horaires d'utilisation.

vendredi 14 décembre 2012

[TAB] : Compatibilité PowerPivot pour Excel 2013 et SSAS Tabulaire

Importer un classeur PowerPivot pour Excel 2013 vers une instance SSAS Tabulaire, est une opération qui s’avère très simple à première vue.

Ainsi, en voulant réaliser cette opération, j'ai été confronté à l'erreur suivantes:
"A connection corresponding to the embedded PowerPivot data was not found in the Excel workbook"
Ce message d'erreur qui laisse penser à un problème de connexion aux sources de données, n'est qu'un problème de compatibilité entre le moteur AS utilisé par PowerPivot pour Excel 2013 est celui de la version SSAS 2012 RTM.

Pour résoudre ce problème, il suffit d'installer le Service Pack 1.

Source : Connect