SQL Server – les columnstore index à la rescousse de la BI

Petit retour d’expérience sur l’usage des CCSI (Clustered Column Store Index) avec SQL Server 2014.

Avec un de nos clients, nous avons fait quelques bench sur le sujet. Sur une architecture équivalente, nous avons mis en place un index columnstore sur une table de fait contenant plus de 300 millions d’enregistrements. Malgré me dires et ceux de Microsoft, mes interlocuteurs étaient très sceptiques tout en fondant de grands espoirs. Lire la suite

Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

Avec l’ouverture des données Power BI Self-Service vers les utilisateurs (vu dans plusieurs de mes missions) les métiers veulent gérer eux même les droits ou périmètres sur de nombreux utilisateurs finaux (>1000).

En effet dans certains domaines, nous avons une forte contrainte de confidentialité des données contenues dans les cubes SSAS. Les juridictions qui doivent s’appliquer peuvent être changées avec des cadences variables allant de la journée, à des cadences inférieures (temps réel). Pour cela, l’ensemble de la sécurité mise en œuvre est stocké dans la source de données du cube, qui détermine qui peut voir quoi.

A travers cet article, nous allons voir comment rendre dynamique une sécurité de cube SSAS basée sur l’appel à une procédure stockée.

Lire la suite

[JSS 2014] Session : BI et déploiement automatique avec TFS

Speaker : Romuald COUTAUD & Khirdine HADDAR
Level : 200

Objectif : Déploiement des solutions BI (SQL, SSIS, SSAS, SSRS) automatiquement avec TFS

Introduction :
Dans tous les projets BI, il y a différentes façons de gérer l’industrialisation de nos projets (SQL, SSIS, SSAS, SSRS). Cette session va permettre d’illustrer TFS (Team Foundation Server), l’un des moyens d’industrialisation de ces projets en automatisant la génération et le déploiement des livrables dans les différents environnements (Dev, Intégration, Prod).
Ce qui va être présenté ici, ce sont juste le versioning des solutions ainsi que la génération et le déploiement automatique des différents projets (TFS Build).

TFS :

  • Gestion des versions des projets
  • Packager les livrables
  • Build TFS
    • Extraction et copie automatique sur le serveur
    • Génération des projets (se fait à l’aide de MSBuild, un framework dotNet)
    • Déploiement

Le déploiement se fait à l’aide de WWF (Windows Workflow Foundation). Il est possible d’utiliser des scripts PowerShell pour compléter ces tâches.
Beaucoup de DLL (codeplex) ont été développées par la communauté et seront utilisées.

Outils :
SSIS : MSBuildSSIS2012 (génération & déploiement)
SSAS : SSASHelper (génération)
SSRS : SSRSMSBuildTasks

Démo : Déploiement SQL Server

  • définition du Build avec les différents arguments (deploy, environment, …)
    • fait appel à TFSBuild.exe

>> Toute la base a été déployée sur l’instance DB indiquée, avec les schémas associés.

Démo : Déploiement SSIS

  • le fichier .proj a été modifié pour prendre en compte des options non natifs
  • possibilité d’ajouter un fichier .xaml (WWF) pour avoir des options en plus aussi (BuildSSIS, …)
  • lancement du Build
    • compilation du projet SSIS + déploiement
    • le build peut se lancer aussi en ligne de commande. En changeant les paramètres, on peut facilement déployer les mêmes sources mais sur des instances différentes

Démo : Déploiement SSAS

  • mêmes procédés que précédemment à l’exception qu’un fichier de config.xml peut permettre de customiser ses différentes sources
  • comme indiqué dans la partie Outils, le codeplex pour SSAS ne permet pas de déployer les solutions SSAS. Pour la démo, un script PowerShell a été développé, permettant les déploiements

Démo : Déploiement SSRS

  • pas de surprise par rapport aux précédentes démos. La DLL, récupérée sur codeplex, met à disposition plusieurs méthodes permettant de checker l’existance d’un rapport, l’ajout/suppression d’un rapport, la modification de la source, etc.
  • les étapes de génération et déploiement restent les mêmes que précédemment

Pré-requis :

  • avoir un server TFS configuré
  • adapter tous les codeplex récupérés

Conclusion :
On entend beaucoup parler de TFS (surtout chez les dotNetiens) mais durant mes différentes missions, je n’ai pas eu l’occasion de voir cette méthode mise en place.
Très bonne session, on a pu voir qu’une fois que toutes les configurations, pour chaque type de solution, ont été mises en place, on peut facilement déployer nos solutions sur chacun de nos environnements et du coup, faciliter la tâche à nos chers collègues du support (et nous même).
Reste à voir ce que TFS peut donner avec les tests unitaires par exemple…

[Sql Serveur] Backup de bases de données SQL Serveur

Voici différentes façons de faire des backups de bases de données SQL Serveur, et quelques scripts pour en obtenir les informations.

Par Sql Serveur Management Studio (SSMS)

  • Voici une vidéo qui explique comment faire un backup avec SSMS

Par des logiciel tiers

Il existe plusieurs logiciels qui permettent de faire des backups de bases de données sql serveur.Il y en a un que je trouve très intéressant c’est SQLBackupAndFTP  car il est très simple à configurer et à utiliser.Il a comme avantages :

  • de pouvoir stocker les backups sur plusieurs types de destinations: FTP , réseau, dropbox, skydrive, google drive, …
  • De ziper les backups
  • De les encrypter
  • d’envoyer un mail à la fin de la tache
  • De suivre l’historique
  • Il permet aussi de restaurer des backups

Au niveau des licences il possède une version gratuite et une version commerciale.

(Les certifiés Microsoft MCP ont droit à une licence gratuite de la version commerciale)

Lire la suite

70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012 – Novembre 2013

La préparation

J’ai passé les certifications liées au MCSA en me basant sur les excellent training kit, mon expérience et divers sites pouvant répondre à mes questions. Mais lorsque je me suis décidé à passer mon MCSE Business Intelligence, j’ai été surpris de ne pas trouver de training-kit officiel sur ce sujet.
En parcourant le programme de cet examen, trois parties se distinguent: l’administration multidimensionnel ( SSAS et SSRS), le tabulaire.
Pour le multidimensionnel, je me suis basé sur les le training kit 70-448. Concernant le tabulaire, j’ai regardé et pratiqué beaucoup de tutoriels ne pratiquant pas en mission ce nouveau mode.

L’examen

Ayant une forte expérience sur la partie multi-dimensionnel, j’abordais cet examen confiant. Lorsque l’on a un doute, il faut souvent se dire, quelle serait la réponse dans l’esprit Microsoft. En découvrant les 50 questions, l’examen est très complet avec comme d’habitude les questions à choix multiples sur 4 sujets différent, les drag& drop à mettre dans le bon sens.
L’examen s’étant bien déroulé et enchainant le 70-467, ma joie de réussite fut courte.

[PASS SUMMIT 2013] NY, London, Munich, Everybody’s talkin about Mobile BI

Speaker : Paul Turkey

Niveau de la session : 200

Le speaker commence la session en se filmant avec sa webcam depuis sa tablette Windows 8 qui est projeté à l’écran.

Ensuite il fait un sondage auprès des participants sur la région d’où ils viennent, leur fonction, est ce qu’ils sont « Cloud Ready » …..

Les infos sont saisies en live sur la tablette du speaker transférées dans un cube et présentées avec Power View.

 

Il présente ensuite les prérequis pour faire de la « BI Mobile », puis les différents outils MS de Reporting, Data Visualization et portail BI ainsi que leurs capacités à faire de la BI Mobile, puis fait des démos sur sa tablette avec ces outils.

 

Enfin il fait une démo de Power BI en mobilité.

On voit du Power View dans Office 365 s’afficher dans un navigateur en HTML 5.

 

Enfin il présente des outils tiers pour faire de la BI mobile « On Premise » et notamment DataZen qui s’appuie  sur l’API « ComponentArt »

Conclusion :

Speaker très pédagogue, le scénario et la progression des démos sont très inventifs.

Power View dans Office 365 (Power BI) s’affiche enfin dans un navigateur en HTML5. La BI mobile fonctionne bien pour une fois. Donc bravo Microsoft !!

Là on se dit que Silverlight devient obsolète et qu’afficher de l’Excel, du SSRS ou Power View avec  SharePoint depuis un mobile ne sert plus à rien. De toute façon c’était pas « Touch ready » et peu performant.

Enfin l’éditeur tiers DataZen est très bluffant !!

La question qui reste en suspens et c’est dommage : à quand la mobilité Microsoft sur les iPad du concurrent Apple ?

Sébastien Madar

[PASS SUMMIT 2013] CAT : High Performance, High Volume Data Movement in Azure

Speaker : Steve Howard

Niveau de la session : 300

Plusieurs outils associés pour le transfert de données :

  • BCP
  • Custom Code (BulkCopy API, TVP, .NET)
  • SSIS

Il existe 5 patterns du mouvement de données :

  • Telemetry Aggregation
  • Hierarchy Aggregation
  • Data Synchronisation et Bulk mouvement
  • Data Stream Multiplexing (Sharding)
  • Cloud ETL

Dans Sharding :

Le Principe : améliorer les performances du « Scale out »

Les datasets sont distribué dans plusieurs bases

Il existe un composant SSIS  dans codeplex « Database Sharding »

Démo

Script qui lance plusieurs Package et place en file d’attente les données

Sébastien Madar

[PASS SUMMIT 2013] Fast Performing SSAS hierarchies : tips and tricks

Speaker : Jose Chinchilla – President & CE AgileBay Inc.

Niveau de la session : 200

1. Types and classification of hierarchies :

* Natural and unnatural hierarchies :

  • natural : existe physiquement sur le disque. Offre de meilleure performance (exemple : year / quarter / month / day),
  • unnatural : non performante car relations logiques entre les attributs –> à éviter au maximum et attention aux problèmes de déploiement à cause des duplicate keys,

* Attribute and user hierarchies :

  • attribute : les attributs de la dimension sont des hiérarchies par défaut (All / liste des valeurs),
  • user : créé par l’utilisateur via des drags & drops,
  • IsAggregatable = False –> pour cacher le ALL dans la hiérarchie d’attributs,
  • Il faut définir les relations entre les attributs dans les user hierarchies afin d’optimiser le temps de réponse des requêtes. La définition de la relation se fait en glissant l’enfant vers le parent (Follow my daddy),

* Values Discretization :

  • Permet de créer des intervalles de valeur (ex. size range – size),
  • Mettre la propriété « DiscretizationMethod » du champ à Automatic puis spécifier le nombre d’intervalles à générer dans la propriété « DiscretizationBucketCount »,

* Bonne pratique : spécifier la propriété « Type » de la dimension et de ses attributs (ex. Type = ‘Time’ pour la dimension date, ‘Geography’ pour la dimension géographie, etc.),

2. Performance Optimizations :

* Modeling : un bon modèle multidimensionnel garantit une bonne performance du cube,

* Aggregations : Pré-aggrégation d’un groupe de mesures selon la granularité des dimensions avec lesquelles il est en relation (via des clés étrangères ou des références),

* Partitions : La stratégie classique de partitionnement  est de la faire en fonction du temps puis de processer uniquement celle relative au mois en cours, au 2 derniers mois, etc. (tout dépend du besoin métier),

* SSAS QueryLog : option disponible via clic droit sur l’instance SSAS, Propriétés, Général et activer l’option Log / Query Log / CreateQueryLogTable qui va créer une table de journalisation dans la base de données spécifiée. Cette table permettra de voir toutes les requêtes exécutées sur le cube avec des statistiques (Start Time, Duration, etc.),

* Usage-Based Optimisation Wizard,

Conclusion :

Session qui reprend les bases de mise en place des hiérarchies dans un cube OLAP et présente quelques astuces d’optimisation. Elle reste peu intéressante si on maîtrise déjà le sujet mais assez pédagogique sinon…

Ismaïl Ben Lamine

[PASS SUMMIT 2013] Advanced Analysis with Excel 2013

Speaker : Dejan Sarka, SolidQ

La session parle de data mining et de l’utilisation des outils Microsoft associés (Excel + PowerPivot)

Agenda :

  • Excel Data preparation and Data mining
  • Table analysis with Excel
  • Introducing PowerPivot
  • Combining data mining with PowerPivot

Introduction :

L’Addin Data Mining est à chercher avec la version SQL 2012

Il utilise en source une  connection à SSAS en multidim ou une Range Excel ou une Table Excel

Concrètement on peut utiliser 3 addins :

  • Le client Data mining pour Excel
  • Table Analysis Tool pour Excel
  • Les modèles de Data Mining pour Visio (pas encore pour excel 2013, pas montré par le speaker)

Le client Data mining pour Excel :

Tout se trouve sur la base (Ribbon)

La préparation des données se fait avec Explore Data tool + Clean Data Tool (nettoyage basique = re-nommage de colonne, suppression des valeurs aberrantes = outliers) + Sample Data tool (échantillonnage pour training du modèle)

Pour le modèle de données on peut utiliser :

  • Classify tool -> utilise Decision tree
  • Estimate tool -> utilize Regression tree
  • Cluster tool -> utilize Clustering
  • Associate tool -> utilize des règles d’association
  • Forecast tool -> utilize Times Series
  • Options avancées : créer une nouvelle mining structure ou pour ajoutter un modèle à une mining structure existante

Pour la précision et la validation des données on utilise dans la section correspondante du Ribbon :

  • Accuracy Chart
  • Classification Matrix
  • Profit Chart
  • Cross Validation tool

Les modèles que l’on veut valider doivent partager la même structure de mining

Le client de data mining sert aussi pour :

  • Model usage
  • Management
  • Connections

Démo :

  • analyser des données via Excel (connection a une bd sql 2012 adventureworksdw2012)
  • utiliser le Data mining client : explorer les données à partir d’une table de données d’Excel data et affichage de la distribution continue d’un champ (par ex du revenue)
  • Convertir la table en range puis classify (uses decision tree) pour analyser les achats de vélos : cela crée un  modèle et sa structure dans SSAS, on peut voir alors l’arbre de décision (par exemple depuis ssms ou excel : idem)
  • On peut aussi utiliser document model via Excel (option qui existe) et copier une des représentation graphique (image statique)

Table analysis tool :

Il apporte une vision orientée plus business et peut être utilisé pour :

  • Analyze key influencers (Native Bayes mining model)
  • Detect categories (Clustering mining model) and Fill From Example (Logistic Regression mining model)
  • Surligner les Exceptions categories (Clustering mining model)  et analyser le panier du consommateur (Règles d’association)
  • Outil de prévision (Forecast tool via Times Series model et Prediction calculator via Logistic Regression)
  • Goal seek (que modifier pour arriver à l’objectif) et What if (quell est l’impact d’une modification)

Démo :

  • Gool seek (target bike buyer) pas très utile car chiffre utilise en source pas top
  • What if (target bike buyer avec distance — > renvoie un niveau de confiance)
  • Hihlight Exceptions (lent c’est pour cela qu’on échantillonne) : marque les lignes sources et produit un rapport

Combining data mining with PowerPivot :

L’introduction sur PowerPivot décrit BISM puis explique différentes implémentations du modèle tabulaire de SSAS :

  • Personal BI  = Excel PowerPivot
  • Team BI = BI avec SharePoint
  • BI d’entreprise = SSAS en mode tabulaire

On peut utiliser PowerPivot pour du datamining. Il faut :

  1. Créer un rapport de table pivot à plat
  2. Supprimer les sous totaux et totaux
  3. Formater la zone de données en tant que table
  4. Utiliser l’outil Table Analysis pour explorer les données

On peut faire l’inverse, utiliser du datamining en tant que source de PowerPivot. Pas d’éditeur DMX dans PowerPivot mais voici la solution de contournement :

  1. Définir SSAS multidim database en source de données
  2. Ecrire dans un éditeur MDX (genre SSMS) la requête DMX manuellement (bonne chance)
  3. Importer les données
  4. Créer les relations

Démo :

Première démo

  • import de donnes dans PowerPivot via query sur source sql + measure total of bike buyer (sum) + count of bike byer + moyenne via sum/counta
  • création pivot table report
  • exclure subtotals et total pour avoir une table
  • convertir  en formules
  • analyser via tool d’excel

Deuxième démo

  • création dans Visual Studio d’un data mining model
  • Création  requête sql (en dmx avec prediction join) pour avoir des clusters par customerkey
  • Import du modèle dans PowerPivot via la requête préparée avant en dmx
  • Connexion dans le modelé de PowerPivot entre vTargetMail et la Query
  • Création d’un graphique (histogramme de prédiction des buyers by cluster)

Conclusion :

Bonne session si on aime le Data Mining.

Encore une utilisation de la self-service BI (on oublie un peu que le data mining existe ?).

Avantage Microsoft : la gratuité des outils que l’on a déjà pour du data mining (Excel + SSAS) quand on fait  de la BI.

Oui Microsoft n’est pas le standard pour le data mining …

Pascal Rouzé

[PASS SUMMIT 2013] Why is SQL Server slow right now ?

Speaker : Brent Ozar – MCM, MVP Brent Ozar Unlimited

Niveau de la session : 200

Troubleshooting :

1. sp_whoisactive :

Procédure stockée qui permet de voir la ou les requêtes en cours d’exécution sur le serveur –> disponible par défaut dans SQL Server,

2. sp_Blitz :

Procédure stockée qui priorise la liste des problèmes sur le serveur SQL par ordre décroissant (situation en général et non à l’instant T) –> disponible en téléchargement gratuit,

3. sp_AskBrent :

Procédure stockée qui liste tous les éléments qui ont mis plus de 5 secondes (durée paramétrable) à s’exécuter. Dans le résultat retourné, on retrouve également :

  • la consommation (CPU, disque, I/O) des requêtes,
  • liens URL vers la description du problème,
  • recommandation d’optimisation,
  • Query Plan (plus besoin de trace),

En rajoutant le paramètre @ExpertMode = 1 à la procédure, on peut voir tout ce qui se passe sur le serveur regroupé par activité.

Il est possible d’exporter le résultat de la procédure dans une table (en renseignant les paramètres @OutputDatabaseName, @OutputSchemaName et @OutputTableName) et de planifier auquel cas un job derrière –> penser quand même à vider la table périodiquement.

En rajoutant le paramètre @AsOf, on peut suivre les requêtes à plus ou moins 15 minutes de cette date.

4. OpServer :

Application web open source qui permet de monitorer l’activité serveur (jobs, espace disque occupé, espace mémoire, etc.). Elle doit être installée sur le serveur web interne de l’entreprise. Elle est sécurisée et les données récoltées restent internes.

Conclusion :

Excellente session. Peu de slides, beaucoup de démos. Speaker dynamique, drôle et qui maîtrise parfaitement son sujet. Les outils et procédures présentés sont intéressants. À tester / approfondir…

Ismaïl Ben Lamine