L'utilisation des attributs de membres d'un cube SSAS dans un rapport SSRS ne se fait pas de manière transparente.

En effet, alors que les attributs sont visibles dans l'éditeur graphique de source de données SSAS de Reporting Services, ils ne sont pas sélectionnables.

Pour y avoir accès dans un rapport, il est nécessaire de basculer en mode Requête MDX, et de modifier sa requête de la manière suivante:

SELECT

NON EMPTY { [Measures].[...]} ON COLUMNS,
NON EMPTY { ([Dimension].[Hierarchy].[Level].ALLMEMBERS * [Dimension].[Hierarhcy].[Level].ALLMEMBERS * ... ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [Dimension].[Hierarchy].[Level].[Attribute]
ON ROWS

FROM [Cube]
WHERE ...

Les mots clefs DIMENSION PROPERTIES permettent de passer les valeurs des attributs directement dans le résultat de la requête MDX, les rendant accessibles dans l'onglet mise en page de SSRS.

Ainsi, dans le rapport, la syntaxe à utiliser sera : Fields!######("Nom de l'attribut")

Sources:
Blog de Braulio Malaga (Avanade)
MSDN

- Gestion de projet Agile et Microsoft BI
- SCD sur mesure (Type II)
- Retour d'expériences BO XI R2 et DataStage

Lors de l'utilisation de la commande OSQL,
vous avez la possibilité de lancer des fichiers de commandes .sql, très pratique pour mettre en production l'initialisation d'un Datawarehouse.

Ces fichiers de requêtes SQL utilisent la commande INSERT dans des champs de type varchar.
si le fichier contient des "ï,é..." il seront passés en caractères spéciaux du type "U,É...".
Cette erreur vient de l'interprétation du fichier via la commande OSQL.

la solution que j'utilise aujourd'hui, est la convertion du fichier SQL de ANSI en unicode.

Passé ce problème l'utilisation de OSQL reste très simple : depuis command ou dans un .bat la ligne suivante permet le lancement d'un fichier SQL
osql -E -S "votre serveur" -i Script.sql

Afin de disposer des propriétés ParentUniqueName ou Level dans les rapports de SSRS, il peut être utile de formuler sa requête MDX comme suit.

En effet dans le cas de dimensions parent-enfant SSRS arrive à communiquer avec SSAS pour obtenir ces infos. Pour toutes les autres dimensions cela ne fonctionne pas, et l'utilisation de cette syntaxe permet de contourner ce manque et de disposer des propriétés attendues. Pour certains rapports tordus cela peut être bien utile!


WITH

MEMBER [Measures].[UniqueName] as axis(1).item(0).item(0).hierarchy.currentmember.uniquename
MEMBER [Measures].[ParentUniqueName] as axis(1).item(0).item(0).hierarchy.currentmember.parent.uniquename
MEMBER [Measures].[P2UniqueName] as axis(1).item(0).item(0).hierarchy.currentmember.parent.parent.uniquename
MEMBER [Measures].[ParentName] as axis(1).item(0).item(0).hierarchy.currentmember.parent.name
MEMBER [Measures].[Name] as axis(1).item(0).item(0).hierarchy.currentmember.name
MEMBER [Measures].[Level] as axis(1).item(0).item(0).hierarchy.currentmember.level.ordinal

SELECT

{[Measures].[UniqueName],[Measures].[ParentUniqueName], [Measures].[Name], [Measures].[ParentName], [Measures].[P2UniqueName] , [Measures].[Level], [Measures].[Nb Employés] } ON COLUMNS,

NonEmpty(
{ STRTOMEMBER(@DimOrganisationOrganisation, CONSTRAINED).CHILDREN
}) ON ROWS

FROM [CubeDWH]
WHERE (STRTOSET(@DimTempsTemps, CONSTRAINED))

La problématique est de proposer un portail sur SSRS qui dispose de :

* 2 datasets, un SSAS et un SQL Server
* une page d’accueil classique à base de requête SQL : A
* une navigation dans un cube en format matrice : MM
* une navigation dans un cube en format liste : ML
* une page de détail à base de requête SQL : D

Les navigations RS détaillées dans ce post sont : A > MM > ML > D.

En comprenant les syntaxes utilisées pour ces sauts il est possible de monter un réseau complet depuis n'importe quel type de rapport vers n'importe quel autre.

La principale difficulté dans ce type d’architecture repose sur le passage des paramètres entre les 2 technologies.

Il est à noter que le sujet de la sécurité ne sera pas abordé. En effet selon les best practices il est nécessaire de définir la sécurité des données au niveau des bases (SSAS ou SQL Server) et non au niveau de l’outil de reporting, afin de garantir le même niveau de sécurité selon tous les modes d’accès.

Toutes les syntaxes détaillées ci-dessous sont à définir dans la fenêtre paramètre de l’onglet navigation de la zone devant disposer du lien.

1 - A > MM
Dans ce saut de rapport, une valeur SQL est passée à un rapport branché sur cube. Le paramètre attendu par le cube est une coordonnée de dimension permettant de filtrer les données. Il est donc nécessaire de transformer la valeur pour qu’elle adopte le format attendu :

ParamètreAttendu
="[Dimension].[Hiérarchie].[Niveau].&["+ Fields!ChampSQL.Value +"]"

2 - MM > ML
Dans notre exemple 2 valeurs sont transmises à ML :

La première provient du dataset de MM. Attention au fait que ce n’est pas la valeur du champ qu’il faut transmettre mais bien sa coordonnée dans le cube. On ne transmet donc pas à ML une Value mais un UniqueName qui correspond à cette coordonnée:
ParamètreAttendu

=Fields!ChampDeLaDimensionAttendue("UniqueName")

Pour la seconde valeur, qui est fournie par le paramètre de DimTemps de MM, la syntaxe est la Value, car dans cette value est stockée une coordonnée MDX qui dans notre cas provient du point 1 :
ParamètreAttendu
=Parameters!NomDuParametre.Value


3 - ML > D
Enfin, pour passer de ML à D, il suffit simplement de transmettre la valeur du champ attendu et non ses coordonnées comme précédemment. La propriété Value suffit donc :
ParamètreAttendu
=Fields!ChampDeLaDimensionAttendue.Value


Et voilà !
Facile en fin de compte ;)

Voici une bonne solution pour traiter des fichiers dont le nombre de colonnes est variable.
Ce type de fichier est classique quand Excel est utilisé pour générer des CSV à traiter dans SSIS.

Solution sans script:
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/16/handling-varying-columns-part-2.aspx


Solution avec script:
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Suite à une demande, l'objectif de ce post est de décrire la méthode d'alimentation des Tables AuditPkgExecution,AuditTableProcessing et AuditDimension que J'utilise dans mes projets avec SSIS:

• Description des tables

AuditPkgExecution est utiliser pour le controle des Packages donc lors de l'exection de mon Etl il y aura autant d'insertion de lignes que de packages.
AuditTableProcessing represente Les flux de données (DataFlow), dans certains cas j'ai réalisé plusieurs insertions dans cette table pour un DataFlowAuditDimension est utiliser dans le cadre ou la table Alimenté est une dimension d'un Datawarehouse

• Alimentation des Tables

Dans chaque Package je crée des conteneur de sequence Pré-Execute et Post-Execute, il est aussi important dasn les DataFlow d'utiliser le composant "Nombre de lignes" pour réaliser des comptages aux endroit stategiques :
- ExtractRowCnt (Nombre de ligne extraite de la source)
- ExtractCheckValueXxxxx (Nombre de ligne après un traitement specifique)
- InsertRowCnt (Nombres de lignes Insérés)
- UpdateRowCnt (Nombres de lignes Updatés)
- ErrorRowCnt(Nombre de lignes en Erreur)
Les variables TableInitialRowCnt et TableFinalRowCnt seront alimentés avec des requettes

AuditPkgExecution : Principalement alimentée avec des variables systemes, à noté que le Master n'a pas de ParentPkgExeckey, les autres packages prendront comme valeur de ParentPkgExeckey le PkgExecKey du master. ce point sera traité avec l'utilisation des configurations dans SSIS et les variables parent.
AuditTableProcessing : pour chaque ETL cette table sera diferente mais on y retrouvera souvant les standart: Nom de la table, Nombre de lignes inserrées,Mises à jour, Suprimmées, en Erreur... pour chaque cas metier traité ou pour la gestion de rejets.
AuditDimension : personnelement je ne l'utilise que dans le cadre ou mes dimensions sont alimentés en plusieurs phases ou sur des Flocons.

Utilisez un composant script comprenant :
-----------
Public Sub Main()
'
MsgBox("Texte" & Dts.Variables("Nom_de_la_variable").Value.ToString)
'
Dts.TaskResult = Dts.Results.Success
End Sub
-----------
Script Language : Microsoft Visual Basic .NET
PrecompileScriptIntoBinaryCode : False
Entrypoint: MainScript
ReadOnlyVariable : Nom_de_la_variable

le composant Merge join demande un composant de tri ou alors des données déjà triés.
Il est possible de ne pas utiliser le composant tri avec deux methodes:

1. Le composant d'extraction OleDb procède une propriété (voir les propriétés de "OleDb source Output" dans l'éditeur avancé, onglet propriété d'entrée ou de sortie)
   Passez le paramètre IsSorted à True.
2. Il est aussi possible de forcer le flag de sortie de chaque composant de lecture (requete SQL contenant un order by) à 1

Vous le savez sans doute deja, mais chaque outil de SQL Serveur 2005 à son propre mode de deployement, je me suis donc attaché à trouver un mode pour chacun. Dans le cadre ou je souhaite laisser un livrable pret à installer par mon client ou mon service d'exploitation.
Très vite on trouvera le generateur de "deployement manifest" de SSIS ainsi que les fichier de publication de SSAS à utiliser avec l'assitant de deployement.
Pour SSRS ce point est bien plus complexe.
Les choix qui s'offre à nous :
- Deploiement via Visual Studio
- Deplacement des fichiers RDL
...
Après quelques recherche je suis tomber sur le pilotage de l'application RS.exe via un fichier RSS.
il nous faut donc plusieurs elements pour effectuer un livraison avec ce mode
1 Fichier .RSS
1 Fichier .Bat (pour lancer le tout)
x fichiers .Rdl

l'utilisation de l'option "Fast Parse" du composant "Flat File" ou Fichier plat permet de gagner en performance de lecture des fichiers:
Voir les paramètres avancés du composant "Flat File" dans l'onglet proprietes> Parametrès des input/output> passer l'option Fast Parse à True.

Test de performance Par Mladen Prajdić
http://weblogs.sqlteam.com/mladenp/archive/2006/07/17/10634.aspx
Pour aller plus loin l'auteur de ce blog à utilisé les options Tablock pour gagner en performance mais dans ce cadre nous n'utilisons plus les standards de SSIS.

Afin de permettre aux packages SSIS d'être portables sous différents environnements sans avoir à re-parametrer l'ensemble des composants (je vous rappelle qu'un environnement de production ne contient pas visual studio... :) ) il est nécessaire d'utiliser un fichier de configuration.
mon choix est le fichier de configuration XML,
1- Plus flexible, il permet aux administrateurs de paramétrer les variables des différents environnements sans pour autant connaître SSIS,SQL serveur.
2- Lors de l'installation via le "Deployement manifest" il est possible d'éditer directement les configurations
3- Les paramètres gérés par ce fichiers sont sélectionnés manuellement


Tutorial:

• http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm
• http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/4982759f-29a9-4554-b44e-72b46cd52b83
• http://www.sqlis.com/post/Easy-Package-Configuration.aspx
  

Best practices:

• Sélectionner uniquement les données variables d'un environnement à un autre, nom de serveur,
• Utiliser un fichier par source de données (dans le cas ou vous avez plusieurs packages, ils ne portent pas tous les même sources)
  

Dans ce post nous allons donc parler de versionning:

Definition:

nous n'utiliserons pas SourceSafe puisque nous avons deja un serveur Subversion en place.

Après quelques recherches et un test, 2 produits semblent interressant VisualSVN et AnkhSVN.
VisualSVN est payant (environ 50$ la licence) alors que AnkhSVN est gratuit mais en cours de developpement pour le module Visual Studio 2005 et 2008.

Après quelques jours de test, la version actuelle de AnkhSVN(dev) n'est pas encore assez stable (la version stable n'etant pas compatible avec la derniere version de SUBVERSION) alors que VisualSVN est stable. le site de VisualSVN propose une documentation simple et pragmatique sur les cas d'utilisation de l'application.

Integration de Subversion avec VisualStudio avec l'add-on VisualSVN ou AnkhSVN
Tutoriel: http://www.devx.com/codemag/Article/38974/0/page/1
VisualSVN: http://www.visualsvn.com/visualsvn/download/
AnkhSVN: http://ankhsvn.open.collab.net/

1. Utiliser le bon "Protection Level", Pourquoi utiliser vous ce mode ? J'ai vu beaucoup de développeurs ne pas regarder ce paramètre alors que sa fonction est vitale pour travailler en équipe ou pour déployer sont travail.
2. Utiliser un fichier de configuration pour L'ensemble des données variables des Packages.
   Sources,Destinations, noms des serveurs...(1 par sources afin de les reutiliser dans plusieurs Packages)
3. Utiliser les transactions pour gerer l'arret des chargements en cas d'echec sans pour autant etre obliger de recharger l'ensemble des données
4. Minimiser l'appel à des programmes externes. fichiers Bat,Cmd...
5. Minimiser l'appel à des procedures stockées.(j'ai reussit beaucoup de projets sans!)
6. Découper vos flux en arborecence de packages (1 Flux = 1 Package, 1 Dimension = 1 Package)
7. Auditer les actions de vos ETL (voir methode Kimball)
8. Pourquoi ne pas utiliser les bonnes pratiques du developpement logiciel (XP, SVN...)

Sur SQLCAT:
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx
le blog de Matthew Roche:
http://bi-polar23.blogspot.com/2007/11/ssis-best-practices-part-2.html

Apres deux années passées à gérer une base de KM et des formations autour des technologies Microsoft, l’ouverture de ce blog va me permettre de partager avec vous mes expériences sur les technologies SQL serveur 2005 et 2008. L’objet principal et d’ouvrir des débats sur
- Les meilleures pratiques
- L’architecture des systèmes
- La gestion de projet