Archive & Purge, la double peine

cest-du-propre-jl-bulcao_m6Quand il s’agit de purger des données, on y va souvent à la serpette avec un bon vieux DELETE et une clause WHERE.

Oui, c’est long.
Mais c’est encore plus long quand on vous demande de copier les données dans une table à côté. Au cas où…

Là, vous vous taper la double-peine : un bon gros SCAN et une insertion suivie d’un bon gros DELETE.

Heureusement, il y a la clause OUTPUT qui permet de chainer les opérations. Ce qui est supprimé est directement injecté dans l’insertion.
Vous ajoutez à cela une petite boucle pour ne pas exploser votre LOG (attention, pas de ROLLBACK sauf pour le dernier lot !!).

Et voila comment gagner quelques minutes de votre vendredi après-midi.

declare @rc int = 1
declare @i int = 1
declare @batchsize int = 100000
while @rc > 0
begin
  insert into MaTable_Archve
  select * from
  (
    delete top (@batchsize)
    from MaTable
    OUTPUT deleted.*
    where MaTable.Date > convert(datetime, '2016-01-01', 120)
  ) deleted
  set @rc = @@rowcount
  print @i * @batchsize
  set @i += 1
end

Agrégations "Usage-Based" dans SSAS sans utiliser le QueryLog

C’est quoi les agrégations Usage-Based  ?

Quand on développe un cube multidimensionnel, les agrégations sont essentielles. Une bonne pratique est de mettre en place ces agrégations en se basant sur l’usage réel du cube.

C’est ce qu’on appelle Usage-Based Optimization. On place des agrégations uniquement sur les dimensions (attributs) et les granularités réellement utilisées par les utilisateurs.

Pour le mettre en place il faut :

  1. Activer les logs (QueryLog)
  2. Lancer l’assistant Usage Based Optimization qui va créer les bonnes agrégations

Pour la première étape, il suffit d’aller dans les propriétés de votre instance SSAS et de configurer le QueryLog. Si SSAS trouve une chaine de connexion, il tracera l’usage, sur la base d’une requête tracée toutes les [QueryLogSampling] requêtes exécutées.

clip_image001
Source Technet : https://technet.microsoft.com/library/Cc917676

Le paramétrage du QueryLog est clé car il est utilisé également par le wizard d’optimisation qui va lire la table indiquée.

clip_image002

Peut-on se passer du QueryLog ?

La réponse est simple : non. Dans tous les cas, l’assistant ne sait pas récupérer les données d’usage autre part que dans cette table. Mais souvent, on conçoit ces agrégations sur un environnement d’intégration ou de développement (pour ne déployer que les agrégations dans un XMLA assez simple). On peut par exemple copier la table ou les données du QueryLog depuis la production pour la lire sur un autre environnement.

Voici le script pour créer la table :

CREATE TABLE OlapUsageLog
(
  MSOLAP_Database Nvarchar(255),
  MSOLAP_ObjectPath Nvarchar(400),
  MSOLAP_User Nvarchar(255),
  Dataset Nvarchar(4000),
  StartTime datetime,
  Duration Bigint
)

Et si on utilisait le profiler ?

Ok, j’ai mon QueryLog configuré pour mon assistant d’optimisation mais disons que je ne veux pas l’activer en production pour collecter les données. L’idée est d’utiliser le Profiler pour collecter les données et ensuite de les réinjecter dans la table en développement.

Contrairement à ce qu’on pourrait penser, on de trace pas les requêtes; ce n’est pas ce qui sert à l’optimiseur. En fait, c’est une vision « interne » de la requête qui nous intéresse (un peu comme le plan d’exécution côté SQL Server). Ce qu’il nous faut c’est “quelles dimensions sont utilisées.”

Pour cela, on trace l’événement Query Subcube dans Query Processing. Les champs à récupérer sont :

  • DatabaseName correspond à la colonne MSOLAP_Database
  • Duration
  • ObjectPath correspond à la colonne MSOLAP_ObjectPath
  • StartTime
  • TextData correspond à la colonne Dataset
  • NTCanonicalUserName correspond à la colonne MSOLAP_User

Le champ important est Textdata (Dataset) qui est le vecteur dimensionnel de la requête (ie. quels attributs sont positionnés). Le champ ObjectPath servira à pointer sur le bon groupe de mesure.

clip_image003

On peut voir qu’on a le même résultat que le QueryLog sauf qu’on a la souplesse du Profiler.

clip_image004

 

Les avantages :

  • Vous pouvez mettre des filtres (utilisateur, base, requêtes longues, etc.)
  • Vous pouvez démarrer/arrêter une séquence de profiling facilement (pour capturer une séquence de test particulière)
  • Vous n’avez plus besoin du QueryLog (et d’une base SQL Server) en production.

Tester une connexion OLEDB avec un fichier UDL en 32 bits

fichier UDLOn connait tous l’astuce pour tester simplement un provider OLE DB ou une chaîne de connexion (du moins je l’espère). Pour rappel, il suffit de créer un fichier UDL (ie. avec un extension .udl) et de double-cliquer dessus. Un petit assistant vous permet de choisir votre provider OLE DB et tester votre connexion.

Le problème, c’est que ce petit outil tourne en 64 bits. Même si le processus s’appelle rundll32.exe, il tourne bien en 64bits. La conséquence, c’est que vous ne voyez donc QUE les providers OLEDB 64bits installés sur la machine.

Difficile donc de vérifier le bon fonctionnement d’une connexion OLEDB en 32bits, comme celles utilisées par Visual Studio par exemple (–sic–).

Heureusement, il est possible de faire tourner un DLL 32bits (votre driver) avec rundll32.exe. Voici la commande à exécuter :

C:\Windows\syswow64\rundll32.exe « C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll »,OpenDSLFile <votre fichier UDL ici>

Power Query : regrouper des valeurs de plusieurs colonnes en une

Ceci est un post invité.

Power Query est un outil puissant et pratique permettant de matcher des données de diverses sources de manière rapide. Bref, je l’utilise presque quotidiennement.

Cependant, il ne dispose pas de toutes les fonctions qu’un DBA utilise en SQL, par exemple COALESCE. Cette fonction permet de retourner la première valeur non null de ces arguments.

En Power Query, on peut utiliser une série de « if then else » en M, mais ça devient très fastidieux lorsqu’il y a 15 colonnes à traiter.

Imaginons, le cas suivant :

Soit les colonnes A, B et C, dans une requête Power Query, de type string, et dont une seule à une valeur non null. Le besoin est de créer une nouvelle colonne avec la valeur non null d’entres les 3 valeurs disponibles.

snip_20160205110204

Un des hacks permettant d’éviter de fastidieux « if then else » est le suivant :

  • Créer une liste des valeurs de chaque colonne : List.Distinct( { [A], [B], [C] })
  • Retirer les nulls : List.RemoveNulls( <list>)
  • Retourner la première valeur : List.First( <list>)

Ce qui nous donne la formule M suivante (après ajout d’une colonne custom) :

List.First(List.RemoveNulls(List.Distinct( { [A], [B], [C] } ) ) )

snip_20160205110710

Ce qui nous donne au final, le résultat escompté.

snip_20160205110738

S’il le besoin est de concaténer toutes les valeurs non nulls, alors il suffit de changer List.First par Text.Combine. Cette dernière fonction prend comme 2ième argument le séparateur entre chaque valeur (vide par défaut).

Par exemple, Text.Combine ( {« a », « b », « c »}, « | ») retourne « a|b|c ».

PASS Summit – les premiers retours – J1

Difficile de trouver un peu de temps pour vous remonter les annonces, nouveautés, sessions, discussions du PASS Summit. On enchaîne les sessions et entre chaque, on passe du temps à en débattre avec des professionnels du monde entier (mais principalement la #SQLFamily européenne).

Podcast en direct live du PASS Summit

Nous avons déjà fait ce matin avec d’autres experts (Charly, Christophe, Arian, Christian) un podcast pour le GUSS où nous avons pu discuter sur nos impressions de la première keynote. Vous pourrez retrouver très bientôt ce podcast sur notre chaîne Youtube. Les infos ci-dessous :

La Keynote

J’ai trouvé la Keynote un peu molle cette année. Que ce soit la partie communautaire du PASS ou la partie « annonces » de Microsoft. Evidemment, il y a eu des annonces et des démos mais le style, le rythme étaient…bizarre.

Au niveau du contenu, côté communautaire, on a eu droit aux chiffres habituels qui restent impressionnants : 16ème PASS Summit, 5,5k participants, 58 pays représentés… Cela confirme la qualité et la place de cette conférence dans l’écosystème Data.

Côté technique, les annonces se sont concentrées sur SQL Server 2016. Base de données numéro 1 sur l’OLTP (classement Gartner 2015), base de données la plus sûre depuis 6 ans, base de données la plus performante au TPC-H, coût le plus bas du marché… Bref, SQL Server 2016 est là pour devenir la meilleure « plate-forme de données » hybride (important, c’est un différenciant énorme).

sql2016-summary

Côté annonce, on n’est pas dans la nouveauté car on connait le périmètre depuis un moment mais en parler lors d’une keynote et présenter des démos fait toujours sont petit effet. Mais l’info la plus importante a pourtant été diffusée via le blog de SQL Server : la CTP3 est disponible. Enfin…dans quelques jours (histoire de préserver la bande passante du Summit ?)

SQL Server 2016 CTP3 => http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-community-technology-preview-3-0-is-available.aspx

Les démos de la keynote concernaient :

  • Always Encrypted : c’est la capacité de chiffrer les données sur toute la chaine, du client au stockage en passant par les buffers mémoire. Cela nécessite la dernière version du client ADO.NET (et quelques certificats ;-)).
  • Stretch Database : c’est la capacité à étendre une table dans SQL Azure sur la base d’un « clé d’historique ». Evidemment, c’est plus long quand vous requêtez des données stockées sur Azure mais c’est totalement natif et transparent pour les applications. De quoi envisager des architectures avec des coûts optimisés (et d’en finir avec des purge ou des historisations applicatives ou des restaurations compliquées).
  • Operational Analytics : c’est en fait Hekaton + CSI (ColumnStore Index), le tout « updatable », qui permet (en théorie) de mixer de la BI et des workload OLTP sur la même base.
  • R intégré à SQL Server : impressionnant, on exécute une routine R à l’intérieur d’une requête SQL, sur des données relationnelles. La démo appliquait la loi de Benford sur des données relationnelles afin de faire une projection.

Pas de présentation de fonctionnalités BI… j’étais un peu déçu mais James Phillips a présenté tout cela dans la session suivante.

Des nouveautés en vrac

Evidemment, on a vu et appris plein de choses pendant les sessions de la journée (en particulier celle consacrée aux nouveautés et à la roadmap BI de Microsoft de James Phillips). Je vais vous le restituer en mode bullet-points

Power BI

  • Live Query sur SSAS Multidim dans Power BI Desktop : on peut créer des rapports Power BI branchés en direct sur SSAS. Plus besoin de charger les données dans un modèle Power Pivot avec Power Query pour jouer avec les données. Evidemment, vous vous doutez bien que ce sera (très) bientôt disponible dans la Data Management Gateway pour pouvoir déployer sur PowerBI.com (pas encore disponible à cette heure)
  • Tile interactive : actuellement, quand vous touchez une tuile d’un tableau de bord Power BI, vous êtes redirigé vers le rapport sous-jacent. Microsoft a travaillé à avoir des tuiles (Tiles) interactives avec notamment des filtres. Cela améliore grandement l’ergonomie de PowerBI. (pas encore disponible à cette heure)
  • Tile Excel : actuellement, vous pouvez mettre des classeurs Excel dans Power BI (via OneDrive). Le problème est que le seul usage est de cliquer pour l’ouvrir dans Excel Online en mode plein écran, comme dans SharePoint. Maintenant, on va pouvoir épingler des parties de l’Excel (TCD, graphes) comme élément d’un tableau de bord. (pas encore disponible à cette heure)

excel

  • 3D Maps (Power Map?) dans Power BI : on regrettait l’absence de Power Map dans Power BI (uniquement présent dans Excel), une démo montrant des 3D Maps dans Power BI a été présentée. Et cela répond à la question d’avoir Power Map en mode web (dans SharePoint ?) car cela semble fonctionner dans Power BI Desktop et dans le service PowerBI.com (pas encore disponible à cette heure)
  • Visuals : l’annonce n’est pas d’aujourd’hui sur la possibilité d’étendre Power BI avec des composants graphiques custom (développé en D3.js, CSS, etc.). Aujourd’hui est disponible la galerie des composants dans laquelle vous pouvez piocher des éléments graphiques tout prêts. On y retrouve les participants du concours de DataViz organisé en septembre => https://app.powerbi.com/visuals
  • Automated Insights : je n’ai pas beaucoup d’info mais l’idée de cette fonctionnalité est de laisser Power BI vous générer un (ou des) rapport automatiquement à partir de votre modèle. Je ne suis pas fan de ce type de fonctionnalité mais quand je vois la compréhension des modèles par Q&A, je suis très curieux de voir le résultat. (pas encore disponible à cette heure)
  • R dans Power BI Desktop : je n’ai pas vu la démo mais on va pouvoir exécuter un script R depuis Power BI Desktop. De ce que j’ai compris, on est plutôt en restitution du modèle que dans la récupération des données avec Power Query (pas encore disponible à cette heure)

R-PBID

  • Enterprise Gateway : la réunification des Gateway est amorcée. Une Enterprise Gateway (par opposition à la Personal Gateway) va voir le jour et gérer LiveQuery (tabular et multidim ?), DirectQuery et le classique Data Refresh (Scheduled Refresh). Le slide parlait de « Central Management », à voir. (pas encore disponible à cette heure)

SSAS 2016

  • Meilleur support et meilleures performances de DirectQuery (avec support complet du MDX) : cela permet tout simplement de faire des TCD dans Excel sur un modèle tabulaire en DirectQuery. Certes, on n’en croise pas tous les matins mais c’est important de le savoir🙂.
  • 50 nouvelles fonctions DAX, Many-to-Many et relations bidirectionnelles en tabulaire
  • SSAS Compatibility level pour cibler une version de déploiement : obligatoire en raison des différentes fonctionnalités ajoutées
  • Nouveau format de description du modèle (JSON) et donc de déploiement : en remplacement du XMLA, pas adapté au tabulaire. Cela permet également le merge de code source (ALM)

json-tabular

SSRS 2016

  • Nouveau look & feel de Report Manager, un mix entre PowerBI.com et Datazen

ssrs16

  • HTML5, Modern Rendering
  • Nouveaux composants : Treemap et Sunburst
  • Multi form-factor pour le mode interactif (comme dans Datazen)
  • Intégration dans Power BI : il est possible d’épingler un rapport SSRS dans un dashboard Power BI. En cliquant dessus on revient sur SSRS
  • Intégration des technologies Datazen pour le mobile/déconnecté, plus besoin de Datazen Server, c’est SSRS qui exécute tout. On garde toutefois Datazen Publisher (pour le moment ?)
  • Nouveau layout pour les paramètres
  • Plus besoin de plug-in pour l’impression
  • Export PowerPoint
  • Les annonces => http://blogs.msdn.com/b/sqlrsteamblog/archive/2015/10/28/pin-reporting-services-charts-to-power-bi-dashboards-with-sql-server-2016-ctp-3-0.aspx

SSIS 2016

  • Templates pour les Control Flow
  • Des nouveaux rôles de sécurité dont ssis_logreader pour avoir accès aux rapports

SharePoint BI

  • Edit mode pour PowerPivot (?!?)
  • Support de Power Query
  • Support des nouveautés SSRS 2016 (sauf les paramètres)
  • Pas un mot sur Power View (Silverlight ?)
  • Note : On ne parle que de SharePoint 2016 ici

Divers

Voila, je pense que j’ai fait le tour des annonces du jour. Une journée bien remplie.

–

Turin, Seattle, Paris. (mon) actualité Data & BI

Turin, Seattle et Paris (Issy, c’est Paris) sont mes prochains rendez-vous autour de la #SQLFamily, la communauté Data Microsoft au sens large.

Ça c’est pour l’info résumée. Je vais essayer de développer un peu mais une petite réflexion philosophique avant. Je m’aperçois que le format Blog est de plus en plus compliqué pour moi. Est-ce le format qui perd de la vitesse ou bien mes activités business et communautaires qui deviennent incompatibles ? Je n’ai pas la réponse. Même les 3 semaines de vacances que je viens de prendre (en Alaska) ne m’ont pas permis de poster un petit billet.

Alors, je vous invite plutôt à me suivre sur Twitter (@djeepy1) si vous voulez toutes les news (et même des tips techniques). J’y suis plus assidu…même si c’est plus court.

Allez, j’ai commencé cet article, je continue.

SQLSaturday Turin 2015

imageJe ne sais plus si je l’avais annoncé sur ce blog mais j’avais postulé comme speaker pour le SQLSaturday de Turin et j’ai été retenu.

Je n’avais pas prévu de participer à 3 SQLSat cette année – quota atteint – mais quand Marco Russo m’en a parlé lors de sa venue en mai dernier, j’ai été séduit.

Le format, 100% en anglais, même pour les speakers italiens, est très intéressant et favorise les échanges et la dimension européenne. Il faut vraiment qu’on tende vers ça en France même si je sais que c’est plus difficile qu’ailleurs (les français et les langues étrangères…et je m’inscris dedans…).

Bref, samedi (oui, ce samedi), je serai à Turin pour le 454ème SQLSaturday.

J’y parlerai de Data Management Gateway(s) ou plutôt de Gateway au sens large car entre la classique, la personnelle, celle pour SSAS, on la retrouve à tous les parfums. Je pense rejouer cette session lors d’un prochain webcast pour ceux qui ne pourraient pas y assister (et vu le délai de prévenance, je dirai tous les lecteurs de ce blog).

Pour finir sur Turin, je remercie Davide Mauri (@mauridb) et Marco Russo (@marcorus), organisateurs de cet événement.

PASS Summit à Seattle

logo_event[1]A la fin du mois, je redécolle pour les États-Unis. Comme chaque année, j’ai la chance de participer à la plus grande conférence Data & BI au monde (orienté Microsoft), le PASS Summit organisé par le PASS. Près de 300 sessions, les meilleurs speakers, la #SQLFamily des 4 coins du monde, c’est un événement important et je prends plaisir à y être assidu.

Lors de cet événement se tiennent toutes les réunions de l’organisation PASS et en tant que représentant pour la France, j’y vais surtout pour échanger avec mes homologues. Le réseau se crée là-bas.

Mais rassurez-vous, j’y vais aussi pour assister à quelques sessions . J’avais proposé une session cette année mais je n’ai malheureusement (ou heureusement?) pas été retenu.

Et vous, vous y allez ? J’espère que vous avez tous pensé à la réduction GUSS lors de votre inscription et j’espère vous retrouvez nombreux sur place. N’hésitez pas à vous manifester pour qu’on n’oublie pas de vous inviter pour le désormais réputé « French Dinner« .

MVP Summit à Seattle

MVP_Horizontal_BlueOnlyLa semaine qui suit le PASS Summit se tient, toujours à Seattle, un autre Summit que je ne manque jamais : le MVP Summit.

C’est notre grande messe annuelle avec tous les MVP. Moments d’échanges privilégiés entre MVP et avec les équipes de Microsoft. Sorte de piqure de rappel, on en ressort avec une bonne vision de ce qui arrive et encore plus de passion pour nos technos préférées. Evidemment, tout est sous NDA* donc n’attendez pas à avoir plus d’infos.

*NDA : Non-Disclosure Agreement

Les Journées SQL Server 2015

HTag_JSS2015_WhiteComme le temps passe… Oui, on est déjà bien avancé sur le chemin des prochaines JSS. Et oui, c’est bien la 5ème édition. Mais prenons les choses dans l’ordre.

C’est quand ? : 30 novembre et 1er décembre.

C’est où ? : Comme les 4 dernières éditions, ce sera au centre de conférences de Microsoft à Issy-les-Moulineaux (et Issy, c’est Paris).

Y’a quoi à voir ? : Le planning n’est pas encore sorti mais on s’oriente vers 40 sessions sur les 2 jours que durent la conférence.

Et ?: Oui, c’est gratuit !

Le call-to-speaker est encore en cours. Vous avez jusqu’au 18 octobre pour proposer vos sessions. Gageons qu’il y aura plein de sujets SQL Server 2016 ou Azure. Pour ma part, je n’ai pas encore soumis. Power BI, Azure Data Factory, Azure Data Lake…je ne me suis pas encore décidé. Si vous avez des suggestions à me soumettre.

Inscription + Toutes les infos des Journées SQL Server 201 sur le site du GUSS

Webcast d’octobre, en direct du PASS Summit

imagePour le prochain webcast du GUSS, je serai avec quelques autres (Christophe Laporte, Charles-Henri Sauget et d’autres) au PASS Summit pour débriefer à chaud des annonces qui seront faites lors de la première Keynote. A cette heure, je ne peux pas vous dire ce qu’il y aura au programme mais tous les ans, Microsoft profite de l’occasion pour faire quelques annonces majeures.

Toutes les infos et inscriptions : http://guss.pro/?p=3410

Afterwork d’automne à Montparnasse

imageEnfin, pour terminer cet article, sachez qu’il y a un petit Afterwork demain, mercredi 7 octobre. Une bonne occasion de se réunir entre professionnels de la données. On parlera du PASS Summit à venir, des dernières nouveautés comme la CTP 2.4 de SQL Server 2016 mais aussi des JSS.

Venez nous dire ce que vous en pensez.

–

Power BI en présentation privée chez AZEO

Le SQLSaturday Paris 2015 a lieu la semaine prochaine mais vous souhaitez découvrir les nouveautés Power BI en avant-première et en session privée ?

AZEO organise un événement interne le 2 septembre à 19h00 sur Power BI. A cette occasion, j’ai 2 invitations pour venir le partager avec nous.

 

image

Au programme, une session de 90 minutes sur Power BI mais aussi l’occasion d’échanger avec les équipes AZEO, les consultants de mon équipe mais aussi d’autres équipes (et de voir l’ambiance ici).

Si vous êtes intéressé, contactez moi jean-pierre.riehl@azeo.com

 

 

.