[SSAS][PowerShell] Vérifier la cohérence de type entre DSV et Attribut de dimensions

Bien que l’on puisse imaginer qu’avec les investissements faits par MS sur Tabular, techno alternative, lemultidim SSAS que l’on a connu jusqu’à présent pourrait disparaitre dans une version prochaine, j’ai tout de même envie de continuer à bloguer sur le sujet pour les nombreux projets existants qui restent encore à maintenir et à faire évoluer.

Dans un projet décisionnel incluant la brique multidim de Microsoft, il est important de mettre tous les garde-fous pour prévenir tout problème de traitement des cubes qui pourrait engendrer soit une interruption de service ou un éventuel retard de livraison des données. Sur les projets sur lesquels je suis intervenu, il m’est arrivé suffisamment souvent d’être confronté à un problème de traitement de cube dû à un mauvais type de données sur les attributs de dimensions pour que je partage une solution pour éviter ce problème avec vous.

Les causes du problème d’incohérence de type:

Les évolutions successives que peut connaitre une application décisionnelle sur ses sources de données peuvent amener à modifier les types de données des données sources.

Par exemple changement d’un type smallint pour pour un int sur une clé primaire de dimension car le dimensionnement n’avais pas prévu de stocker au-delà de 32767.

SSAS permet de mettre à jour le DSV en fonction des modifications sur les sources de données. Cependant les modifications du DSV ne s’appliquent pas sur la structure du cube. Il est donc important de vérifier que les tous les types soit cohérent.

Ce qu’il faut vérifier :

Dans une dimension chaque attribut possède 3 propriétés : KeyColumns, NameColumn et ValueColumn.

Ce qui importe pour nous c’est de savoir si le type de chaque colonne de la collection KeyColumns possède des types permettant de stocker les données sources.

Il peut aussi être utile de vérifier pour ValueColumn.

Vérifier NameColumn est inutile cette propriété n’accepte que le type WChar qui peut accueillir aussi bien des int, que des dates, que du textes …

Une solution (parmi d’autres) :

Voici une implémentation à base de Powershell pour manipuler l’API AMO. L’objectif est d’intérroger un cube déployer sur une instance SSAS et d’afficher dans la console les différences (s’il y en a) entre type d’attributs et des colonnes sources du DSV.

La difficulté de cet exercice est de comparer le type du DSV de type OLEDB avec celui des attributs de dimensions de type system.

$CorrespondanceType=@{
      "BigInt"="Int64"
      "Binary"="Byte[]"
      "Boolean"="Boolean"
      "BSTR"="String"
      "Char"="String"
      "Currency"="Decimal"
      "Date"="DateTime"
      "DBDate"="DateTime"
      "DBTime"="TimeSpan"
      "DBTimeStamp"="DateTime"
      "Decimal"="Decimal"
      "Double"="Decimal"
      "Empty"=""
      "Error"="Exception"
      "Filetime"="DateTime"
      "Guid"="Guid"
      "IDispatch"="Object"
      "Integer"="Int32"
      "IUnknown"="Object"
      "LongVarBinary"="Byte[]"
      "LongVarChar"="String"
      "LongVarWChar"="String"
      "Numeric"="Decimal"
      "PropVariant"="Object"
      "Single"="Single"
      "SmallInt"="Int16"
      "TinyInt"="SByte"
      "UnsignedBigInt"="UInt64"
      "UnsignedInt"="UInt32"
      "UnsignedSmallInt"="UInt16"
      "UnsignedTinyInt"="Byte"
      "VarBinary"="Byte[]"
      "VarChar"="String"
      "Variant"="Object"
      "VarNumeric"="Decimal"
      "VarWChar"="String"
      "WChar"="String"
}

N’ayant pas trouvé de meilleur solution, je suis parti d’un hash table associant les types OleDB avec les types system. J’avoue que l’élégance de cette méthode est discutable. Si quelqu’un à une meilleure solution je suis preneur.

Afin de pouvoir utiliser l’API il faut commencer par charger l’assembly Microsoft.AnalysisServices, comme suit :

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL
 
$ServerSSAS="Nomduserveur"
$DatabaseName="NomdelabaseSSAS"

Declaration d'une fonction

function get_colonne_info([Microsoft.AnalysisServices.Database] $db, [string] $DataSourceViewID, [string] $TableId,[string] $ColonneId)
{
      $dsv=$db.DataSourceViews.get_Item($DataSourceViewID)
      $table=$dsv.Schema.Tables.get_Item($TableId)
      $colonne=$table.Columns.get_Item($ColonneId)
      return $colonne
}

Code principale

# instanciation de l'objet serveur afin de se connecter à l'instance.
$srv = new-Object Microsoft.AnalysisServices.Server
$srv.Connect($ServerSSAS)
 
# si la base passé en paramètre existe alors on continue sinon on abandonne
if($srv.Databases.Contains("$DatabaseName") -eq $true)
{
$db=$srv.Databases.get_Item($DatabaseName)
 
# parcours des de toutes les dims de la base
      foreach($dim in $db.Dimensions)
      {
            # parcours de chaque attributs
            foreach($attribute in $dim.Attributes)
            {
                  # parcours de toutes les colonnes de la collectionKeyColumn
                  foreach($keycolumn in $attribute.KeyColumns)
                  {    
                        # récupération des types et size des colonnes composant la clés à partir du DSV
                        $DataSourceViewID=$dim.Source.get_DataSourceViewID()
                        $colonne = get_colonne_info -db $db$DataSourceViewID -TableId $keycolumn.Source.TableID -ColonneId$keycolumn.Source.ColumnID
                        $Type_Dsv=$colonne.DataType.Name
                        $Size_DSV=$colonne.MaxLength
                       
                        # récupération des types et size des clés
                        $type_dim =$CorrespondanceType[$keycolumn.get_DataType().ToString()]
                        $Size_Dim=$keycolumn.DataSize
                       
                       
                        # affichage dans la console des types qui ne matchent pas
                        if($Size_DSV -eq 0){$Size_DSV=-1}
                       
                        if ( $Type_Dsv -ne $type_dim -or ($Size_DSV -ne $Size_Dim -and ($Type_Dsv -eq "String" -or $type_dim -eq "String")) )
                        {
                             '**** ' + $dim.Name.ToString() + ' | ' +$keycolumn.Parent.Name +' | ' + " KeyColumn " +' | ' + " Type DSV : " +$Type_Dsv + ", Taille DSV : " + $Size_DSV + " | Type Attribut : " +$keycolumn.get_DataType().ToString() + ", Taille Attribut : " +$Size_Dim                            
                        }
                  }
 
                 
                  # Si la propriété ValueColumn a été définit alors
                  if($attribute.ValueColumn -ne $null)
                  {
                        # même mécanisme que précédemment, on commence par les types du DSV
                        $valuecolonne= $attribute.ValueColumn
                        $DataSourceViewID=$dim.Source.get_DataSourceViewID()
                        $colonne = get_colonne_info -db $db$DataSourceViewID -TableId $valuecolonne.Source.TableID -ColonneId$valuecolonne.Source.ColumnID
                        $Type_Dsv=$colonne.DataType.Name
                        $Size_DSV=$colonne.MaxLength
                       
                         # puis ceux de la propriété de l'attribut
                        $type_dim =$CorrespondanceType[$valuecolonne.get_DataType().ToString()]
                        $Size_Dim=$valuecolonne.DataSize
                       
                        # puis on termine par l'affichage dans la console
                        if($Size_DSV -eq 0){$Size_DSV=-1}
                       
                        if ( $Type_Dsv -ne $type_dim -or ($Size_DSV -ne $Size_Dim -and ($Type_Dsv -eq "String" -or $type_dim -eq "String")) )
                        {
                             '**** ' + $dim.Name.ToString() + ' | ' +$valuecolonne.Parent.Name +' | ' + " ValueColumn " +' | ' + " Type DSV : " +$Type_Dsv + ", Taille DSV : " + $Size_DSV + " | Type Attribut : " +$valuecolonne.get_DataType().ToString() + ", Taille Attribut : " +$Size_Dim                         
                        }
                  }
            }
      }
}

[SSRS] [PowerShell] Désactiver les caches de tous les datasets partagés d'une instance Reporting Services.

Quand on est développeur, il peut parfois être agaçant de chercher à comprendre des heures durant l'origine d'un bug dans le code. Il arrive parfois que notre développement soit juste et que le résultat affiché dans les rapports soit faux. Une des causes courante peut-être une version ancienne du cache qui n'a pas été rafraichi. Pour cette raison, je recommande à tous les développeurs de travailler avec cache désactivé. (Pour cette raison et aussi pour être capable de détecter une requête mal écrite ou non optimisée).

Lorsqu'on travaille en intégration continue, il est commun de synchroniser l'environnement de production avec l'environnement de développement. Cependant, ce qui est souhaitable en production ne l'est pas forcément en développement. C'est pourquoi j'ai trouvé un moyen automatique, planifié à la suite de la synchronisation, pour désactiver en masse tous les caches des datasets partagés.

Grâce à PowerShell et à la cmdlet New-WebServiceProxy, on va pouvoir attaquer le web service de Reporting Services et manipuler les objets déployés sur l'instance en question. Il suffit d'instancier un objet de type WebServiceProxy et de récupérer l'objet dans une variable.

$ReportServerUri = "http://MonServeur/ReportServer/ReportService2010.asmx"
 
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;

Puis, il suffit de parcourir tous les objets de type DataSet à partir de la racine. La commande SetCacheOptions va permettre de désactiver le cache du dataset.

foreach($item in $Proxy.ListChildren("/",$true) | Where-Object{$_.TypeName -eq "DataSet"})
{
      $Proxy.SetCacheOptions($item.Path,$false,$null)
}

Voilà il reste plus qu'à appeler ce petit bout de code dans une step Powershell de l'agent à la suite des autres step de synchronisation. J'espère que cet article vous aura été utile.

[SMO][PowerShell] Automatiser la restauration des bases de données avec fichiers de données multiples

Bien sûr, il est tout à fait possible d’utiliser Transact-SQL pour restaurer une base de données, néanmoins lorsque vous souhaiterez restaurer une base qui dispose d’un nombre pluriel et variable de fichiers de données sur un environnement différent que celui où le backup a été réalisé, la restauration risque d’être un peu laborieuse du fait de devoir réallouer les fichiers vers un emplacement existant du serveur cible.

Comme je vous le disais, l’automatisation en Transact-SQL reste et Christophe Laporte le prouve ici :

http://conseilit.wordpress.com/2009/06/24/la-galere-des-restore-database-with-move-%E2%80%A6/

Cependant, cette méthode oblige à avoir accès sur le serveur où sont déployés les bases à backuper et à utiliser un curseur pour générer le scripts de restore.

En mission chez un client, j’ai dû trouver une solution pour répondre au besoin d’ « automatiser la restauration de bases de données sur un environnement de DEV à partir de fichiers .bak sans avoir accès au serveur de PROD. » En effet, on cherche à cloisonner l’environnement de PROD de celui de développement donc pas d’accès en PROD à partir de la DEV.

Pour répondre à ce besoin, j’ai fait le choix d’utiliser SMO via un script PowerShell en exécutant le fichier ps1 partir d'une step PowerShell de l’agent SQL.

Le gros avantage que j’ai trouvé à utiliser SMO est de pouvoir lire le contenu d’un fichier de backup et de pouvoir réallouer tous les fichiers avant restauration, grâce aux classes Restore et RelocateFile. Ainsi, si la base à restaurer évolue, que de nouveaux fichiers sont ajoutés ou supprimés, du fait de l'allocation dynamique la base sera restaurer sans devoir modifier le script.

Attention, par défaut, le processus de restore généré par le script PowerShell est considéré comme processus distant et le Timeout par défaut de 600 secondes (10min) s'applique. Ce qui amène les requêtes de restauration à être interrompu et à laisser les bases de données dans l'état Restoring.

La parade est de forcer la propriété StatementTimeout à 0 grâce à la commande.$server.ConnectionContext.set_StatementTimeout(0). Le script ci-dessous applique ce paramètre.

param(
        ## Serveur où l'on souhaite restaurer le backup
        [string]$ServerDestination = $(throw "-ServerDestination est requis")
       ## La restauration se fera à partir de cette emplacement
       ,[string]$BackupLocalPath = $(throw "-BackupLocalPath est requis")
       ## Listes des base à restaurer
       ,[array]$DBtoRestore = $(throw "-DBtoRestore est requis")
)

##Chargement des assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  
## Fonction de restauration
## Prends en charge les fichiers multiple et les relocalise en fonction du DataPath et LogPath par défaut du serveur

function Restore([string]$srv,[string]$bck)
{

try{
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $srv
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $bck, "File"
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore

## permet d'outrepasser le timeout du serveur afin de pouvoir restaurer les grosses bases
$server.ConnectionContext.set_StatementTimeout(0)

## Paramétrage du restore
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"

$smoRestore.Devices.Add($backupDevice)

#Gestion de la relocalisation des fichiers
$filelist=$smoRestore.ReadFileList($server)
foreach( $file in $filelist.get_DefaultView())
{
       if($file.PhysicalName -like "*mdf")
       {
             $physicalpath=$server.DefaultFile + "\" + $file.LogicalName + ".mdf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
       elseif($file.Type -like "D")
       {
             $physicalpath=$server.DefaultFile + "\" + $file.LogicalName + ".ndf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
       else{
             $physicalpath=$server.DefaultLog + "\" + $file.LogicalName + ".ldf"
             $relocateFile = new-object Microsoft.SqlServer.Management.Smo.RelocateFile $file.LogicalName, $physicalpath
             $smoRestore.RelocateFiles.Add($relocateFile)
       }
}

# On lit le contenu du backup
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)

#Afin de donner à la base que l'on va restaurer le même nom qu'elle avait sur le serveur source
$smoRestore.Database =$smoRestoreDetails.Rows[0]["DatabaseName"]
  
#Commande de restauration de la base
$smoRestore.SqlRestore($server)

"La base " + $smoRestoreDetails.Rows[0]["DatabaseName"] + " a été restaurée avec succès"
}
catch {
           $ex = $_.Exception
           Write-Output $ex.message
           $ex = $ex.InnerException
           while ($ex.InnerException)
           {
               Write-Output $ex.InnerException.message
               $ex = $ex.InnerException
           };
           continue
       }
}

$bckpath = New-Object System.IO.DirectoryInfo $BackupLocalPath

foreach($file in $bckpath.GetFiles())
{
       $exist=$false
       $bck=""
       foreach($value in $DBtoRestore)
       {
             if($file.Name -eq $value)
             {
                    $exist=$true
                    $bck=$file.FullName
                    break
             }
       }
       if($exist)
       {     
             Restore -srv $ServerDestination -bck $bck
       }
}