Meilleur outils gratuit pour développeur MS BI

Il est possible de trouver de nombreux TOP 10 pour SQL Server sur le net mais la plus par d’entre eux s’adresse à des DBA. En tant que développeur/Consultant MS BI certains de ces outils seront également très utile car il faut toujours analyser et optimiser sa base pour un reporting optimal, par contre de nombreux autres outils plus spécifique à la BI sont également gratuit et très précieux. Voici donc mon TOP des meilleurs outils.

Cette liste me permettra également de retrouver facilement les outils lorsque j’arrive chez un nouveau client pour pouvoir préparer mon poste 😉

Note – Cette liste prend en compte d’avoir déjà une licence SQL Server ainsi qu’Excel, la base de la BI Microsoft.

BIDS Helper
Un add-in pour Visual Studio incluant de nombreuses fonctionnalités comme l’expression highlighter, le dimension usage, le déploiement simplifié…
http://bidshelper.codeplex.com/

MDX Studio
Permet d’écrire et formater des requêtes MDX et analyser les performances des requêtes.
http://www.sqlbi.com/tools/mdx-studio/

DAX Studio
Un add-in Excel et SSAS – Permet d’écrire et formater des requêtes DAX et analyser les performances des requêtes.
http://www.sqlbi.com/tools/dax-studio/

OLAP PivotTable Extensions
Un add-in Excel permettant d’ajouter des fonctionnalités à un TCD connecté à un cube SSAS
https://olappivottableextend.codeplex.com/

SSMS Add-In
Permet d’ajouter de nombreuses fonctionalitées à SSMS
SSMS Boost: http://www.ssmsboost.com/
SSMS Tool Pack: http://www.ssmstoolspack.com/
DbForge SQL: http://www.devart.com/dbforge/sql/sqlcomplete/

SSMS – SQL Execution Plan
Améliore grandement l’interface du plan d’exécution qui n’a pas beaucoup évolué dans le plan d’explorer SSMS
https://www.sqlsentry.com/products/plan-explorer/sql-server-query-view

SSMS – SQL Search
Permet entre autre de rechercher un objet ou du texte dans une base de donnée. Egalement de voir les dépendances d’un objet sous forme graphique et de renommer un objet ainsi que toutes ces dépendances.
ApecSQL Search: http://www.apexsql.com/sql_tools_search.aspx
Red Gate SQL Search: http://www.red-gate.com/products/sql-development/sql-search/

SSMS – SQL Server Management Data Warehouse
http://msdn.microsoft.com/en-us/library/dd939169(v=sql.100).aspx

Database Comparaison
Permet de comparer la structure de 2 bases de données très facilement
DBComparer: http://dbcomparer.com/Download/Default.aspx

Notepad++
Très pratique pour rechercher du texte comme un nom d’objet dans les packages SSIS, (la recherche dans SSDT n’étant pas intuitif)
Permet de bien formater en 1 clique une requêtes SQL ainsi que convertir la requête dans un autre langage comme VB.Net (pour l’inclure en expression dans SSRS par exemple)
http://notepad-plus-plus.org/fr

Maintenance Solutions
Maintenance des bases, Back up, intégrité, Statistique..
SQL Server Maintenance Solution: https://ola.hallengren.com/

Maintenance des Index et Defrag
http://www.brentozar.com/blitzindex/
http://sqlfool.com/2011/06/index-defrag-script-v4-1/

I/O capacity Benchmark
Microsoft SQLIO Disk Subsystem Benchmark Tool: http://www.microsoft.com/en-us/download/details.aspx?id=20163

Trace
Permet de gérer et visualiser plusieurs traces
http://www.scalesql.com/cleartrace/default.aspx

DMV – Data Management Views
Liste des DMV pour les Cubes SSAS: https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

Documentation Cube Automatisée
Parce que la documentation c’est notre passion
http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/
https://sqlbits.com/Sessions/Event8/Automating_SSAS_cube_documentation_using_SSRS_DMV_and_Spatial_Data
http://www.ssas-info.com/analysis-services-tools/1365-ssrs-reports-to-document-ssas-2008-db-structure-using-dmvs

Minimum install to execute SSIS Packages

A couple of days ago one of my client asked me what needs to be install on the ETL server in order to execute SSIS Packages. My first though was to install the SSIS instance but after some other thoughs i decided to look into it in more details.
Here is a summary of my research on the subject – the goal was to install the minimum of component as possible as we are in a PCI environnent which must be highly secure.

dtexec is the command prompt utility that needs to be install to run your SSIS packages.
dtexec is installed when you install SQL Server DB Engine. However this is *NOT* the full version of dtexec. It is only install in order to use:
– Import and export data wizard (within SQL Server)
– Support maintenance plans (Since SQL Server 2005 sp2 or SQL Server 2008 CU1/sp1, it is not necessary to install SSIS to create maintenance plans)
=> So You *cannot* run your own SSIS packages with this version of dtexec
In order to fully use dtexec you need to install « Integration services » feature. Installing this feature install the necessary binaries (and also the service) to run your own packages.

Note:
– dtexecui is not install when installing only Integration services (it’s a client component tool)
– You can stop the SSIS Service to run packages – the SSIS service only extends the functionality of SSMS
– You also might need to install the « client Tools connectivity » feature tools as well and any other components you might need to connect to your sources.
Client Tools includes components for communication between clients and servers, including network libraries for DB-Library, OLEDB for OLAP, ODBC, ADODB, and ADOMD+

64-Bit consideration
In a 64-Bit computer you need to install BIDS or SSMS in order to also get dtexec in 32-Bit
So when installing SSIS in a 64-bit computer to ensure a 32-bit installation is also required, you must install SSIS and BIDS. Installing SSIS and not BIDS will only install the 64-bit engine.
If you have a 64-Bit computer with both 32-Bit and 64-Bit dtexec installed:
– when using dtexecui it will use the 32-Bit version (it’s a 32-Bit tool)
– when using the dtexec it will use the 32-Bit by default (because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version)
– when using SQL Agent it will use the 64-Bit by default ( because SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility)

Conclusion:
You do need to install the « Integration services » feature in order to run SSIS packages. This feature is enough to be installed on its own to run packages and you can even stop the related service. (you do not need the DB Engine services feature).
However, if you have a 64-Bit computer and you need to run packages in 32-Bit mode then you also need to install BIDS or SSMS (which install dtexec 32-Bit)
MS – SQL Server installation features: https://technet.microsoft.com/en-us/library/ms143786.aspx
MS – 64 bit Considerations for Integration Services: https://msdn.microsoft.com/en-us/library/ms141766.aspx

SSRS – DESIGN LAYOUT FOR EXECUTIVE DASHBOARD

A year ago I had the opportunity to work on an executive dashboard re-design project. The current dashboard was developed few years back using SSRS 2005; it was quiet basic and only included small tables to show KPIs without any nice layout. The project was therefore to keep the existing KPIs but produce a “modern design” dashboard using SSRS 2012.

Before starting I’d like to mention that this article is not meant to be technical; it’s mostly my thoughts about report design after working on this project.

I must say that I was skeptical about being able to produce good looking dashboard with SSRS – I am talking here not only about showing a fancy gauge and a graph, but being able to see the whole report as a smooth and modern design layout. Ok we are developers, design is not our job but when working on a dashboard project, I think that is becoming part of the job.
I also often hear these days that SSRS is a dying tool as there is no much development made by Microsoft in the last year(s); the focus being mostly on Power BI. I agree with the fact that SSRS needs a bit of a refresh but having worked with it for a while I know that with a bit of work you can archive a lot.

So I started to look around SSRS blogs to see what actually people can archive visually with this technology. There are a lot of SSRS Blogs out there with good examples. Going through the reading I found a great article from Jason Thomas on how he managed to reproduce a Dundas dashboard. I must say that this article was a relief for me, seeing that you can reproduce all those Dundas chart convinced me that “modern” design dashboard with SSRS was feasible!
So I downloaded the .rdl file from his article, I realized that all the charts were somehow complex or needed lots of expressions to be managed properly, nevertheless it was still possible.

 

PROJECT
I first started to draft with my client how they wanted the dashboard to look like. This is a crucial step in order to get a good visualization of what is expected from the customers / users. Very often the customers don’t really know what they want until this step is done so it does help to get an overall picture for both the client and the developer.

We came up with this result:

  • 6 different parts, each of them including a main KPI (with an arrow for value trend comparing to last year value
  • Couple of graph to show the value trend for the last 4 years, all charts must use the same Vertical axis range, not starting from 0.
  • Maximum of 4 seconds for rendering
  • Good looking report and easily readable
  • Readable on Ipad

1stDraftOfTheDashBoard
1st draft of the Dashboard

 

GENERAL DESIGN
What I mostly realized when working on the design of this report is that you should change most of the default SSRS layout setting. And first of all, do not use black color, use a nice gray. I know that this seems like a detail but black color is aggressive for the eye and you will not have a smooth report with black in it (unless it’s your main color like using a black background). So go to properties and change all the text color, font color, line color, border color, etc… to Dim Gray + Bold instead of Black. This is also/mostly valuable for chart: change the label, data label, Axis line, background line, Tick Marks color, etc… to Dim Gray or even Silver color. You can also use report properties custom code in order to write a function to assign a default color.
For colors, use light colors and colors that match, I know that seems logical but how many reports I have seen that looks sooo terrible only because of wrong colors chosen. You can go on internet and check for colors on website you like and you can get the color code using some of the nice and free tools on the internet like instant Eye dropper or ColorZilla. This will save you a bit of time to find the right colors. I’m sure you have already some websites in mind!

InstantEyeDropper

Instant Eye dropper selecting a green color on a webpage

Then in order to make the report easily readable, I started removing all the visual pollutions => exit the huge logo, decorative lines or background color “for decoration”. The actual trend for website design is “flat design” so we should also use this technique, and it makes a dashboard much more readable! Take a minute and read the Top 10 web design trends 2014, especially the number 1.
Therefore exit also the 3D chart that looks like 1995… It is better to use a nice flat 2D chart. No, SSRS cannot render properly 3D …
I found a lot of nice dashboards on the internet very complicated or with black background, I definitely think that when working daily with numbers you need an as clean as possible visualization. Demos are one thing, daily work is another… (The wow effect does not work every day…

 

KPI BOX
I do not like the default KPI box in SSRS so I used Jason’s article way to build my own box. It does take a bit of time but it looks better at the end. Simply draw something first and then reproduce it inside a table by hiding the unnecessary lines and borders. Hide as much lines as possible, keep only the necessary.

Here is an example of KPI boxes created from a table:

Design:
Design View
Rendering :
RenderingView
For the trend arrow I use my own image and added a custom code property to display the correct image depending on the percent increased vs. last year (Percent already calculated in the query).

CustomCodeWindow

PropertiesChart

 

You can also implement the logic directly as an expression for the Image value, however for good practice and future references it is better to add all code in one place so the custom code window is a good candidate.

 

CHARTS
One of the requirements was to use the same vertical axis range for all charts in order to compare them easily. The best way I found was to calculate the Min, Max and intervals in advance in the T-SQL query using this logic:
Take the Max value +20% and round it up: 3012621 + 20%= 3615146, rounded up= 4000000
Take the Min value -20% and round it down: 1087495 – 20%= 906246, rounded down= 900000
Take the difference divided by 4 (for 4 intervals) then round it up: 4000000 – 900000 =3100000 / 4 = 775000, rounded up = 800000.
So the interval will have a value of 800000.

SELECT
*
,ROUND( (MaxTotalModifiedRound – MinTotalModifiedRound) /4 , – (LEN((MaxTotalModifiedRound – MinTotalModifiedRound) /4)-1)) as interval — Dif Min and Max value then divided by 4 then rounded up

FROM (
SELECT
id, YearNum, Total

,CAST( Min(Total) OVER() as int) as MinTotal
,CAST( MAX(Total) OVER() as int) as MaxTotal

,CAST( Min(Total) OVER() / 1.2 as int) as MinTotalModified
,CAST( MAX(Total) OVER() * 1.2 as int) as MaxTotalModified

,ROUND( CAST( Min(Total) OVER() / 1.2 as int) , -(len( CAST((Min(Total) OVER())/1.2 as int))-1) ) as MinTotalModifiedRound — Min Total rounded : Total – 20% then rounded down
,ROUND( CAST( MAX(Total) OVER() * 1.2 as int) , -(len( CAST((MAX(Total) OVER())*1.2 as int))-1) ) as MaxTotalModifiedRound — Max Total rounded : Total + 20% then rounded up

FROM …

DataChartAxis

You can then add these values into the vertical axis properties of the charts:

ChartAxisProperties

It will then display like this:

ChartView

 

PERFORMANCE
The goal was a maximum of 4 seconds for rendering. Having lots of dataset and going back until 4 years of data, I decided to create Indexed views that will include only the grouping level necessary for the report.
There are 2 parameters in the report to choose the team and the date of analyze, I have therefore grouped my data this way in the indexed view. You can get a very high increase of performance with indexed views (x10+) ; however there is a lot of drawback with these particular views. See recommendation here.

 

IPAD
One of the goals of the dashboard was to be able to visualize it from an Ipad. With SQL Server 2012 SP1, you can now view SSRS reports on the Ipad and Windows surface device, however the report will not necessary be resized correctly. One of the options is to use a command in the URL which will resize the report: &rc:Zoom=Whole%20Page. Using this command the report will render in full page weather you use your Ipad vertically or horizontally. « Whole Page » will attempt to zoom the report to such a level that the entire page is displayed at once in your report viewing frame – whether than means scaling it up or down.
Being able to visualize report from an Ipad is a great improvement, however it is still not easy to navigate or change parameters in small devises. I did a bit or research on internet and found that there are a lot of app. or tools to help visualized SSRS reports on mobile devises.

Here are my best 3:

  • Forerunners, they basically redeveloped the Report Manager in HTML5 so you can visualize SSRS reports on all devises. It is also much easier to use the parameters on small screen. It has been created by Jason Carlson, previous SSRS architect in MS (read interview here) and the new V3 seems promising:
    – Allowing designers to add client side JavaScript on text boxes or images
    – Define fixed size tables and matrixes
    – Scroll within a fixed region instead of expanding the page size
    – Enable editing of data with updatable controls
    – Easily post data to another application using HTML forms in your reports
    – Include information from other applications in your report via an IFrame
  • MobiWave, This App display SSRS report and also SharePoint documents
  • SSRS Report Viewer, This App display SSRS report and also SharePoint documents

My best bet is Forerunners so far as the license cost is low comparing to other products, and implementation very simple – no need to install any app. on each devises. There is also lots of other good tools, maybe better, but usually much more expensive. My goal here was only to display existing reports on mobile devises without any extra development.
Please let me know if you tried other / better tools!

 

FINAL REPORT
Finally here is a screenshot of the dashboard:

DashBoard

 

Overall it is not a complicated dashboard to produce, it only takes some time to build the table/boxes for your KPIs (and make them rendering correctly and in-line once deployed!). I also add to work with the requirement of the customer which does not seem logical to me sometimes… like using histograms instead of line chart for sales trend…

What I mostly wanted to show here is that you can show data that does not look like simple tables with only few tricks in SSRS.

You will find the .rdl file here for VS 2010, I have modified and hardcoded all values so simply point the DataSource to the master db and you should be able to execute the report.

Let me know what you think and if you have other ideas please share with me!

Power BI Pour Office 365

Suite à la Présentation IT CAMP de SQL 2014 et Power BI Pour Office 365 par Franck Mercier, voici un résumé des fonctionnalités de Power BI Pour Office 365.

PowerBI1

POWER BI pour Office 365 est composé de plusieurs éléments qui constituent la suite BI incluse dans Excel et qui permet de stocker vos Workbooks dans le Cloud grâce au Site Power BI.

A noter que certaines fonctionnalités ci-dessous ne sont pas présentes seulement dans Power BI pour Office 365, elles sont en faites présentes dans Excel qui lui-même est l’élément principal de la suite Power BI.

On peut donc séparer les éléments en 2 Parties:
1. Excel : rapatrier des données, les modéliser et les visualiser/ analyser.
2. Cloud : partager des rapports sur le Site Power BI qui est une option d’Office 365. Accessibilité via mobile, tablette. Trouver des infos grâce à Q&A, automatiser le rafraichissement des rapports…

Voici un résumé des différents éléments que regroupe Power BI pour Office 365, ainsi que les versions minimum nécessaire :

EXCEL

POWER QUERY « ETL » pouvant   se connecter à tout type de données interne ou externe Excel 2010
POWER PIVOT Modélisation des données pouvant   se connecter à de nombreuses sources de données utilisant un moteur en   mémoire très performant Excel 2010
POWER VIEW Visualisation des données principalement   de type charte / histogramme en mode “Dynamique” Excel 2013
POWER MAP Visualisation des données géographique   sur carte 3D en mode “Dynamique” Excel 2013

Cloud

POWER BI SITE Stockage et partage des   rapports, support bientôt HTML5 (SharePoint Online Amélioré) Office 365 Option Power BI
POWER BI MOBILE App Visualisation des rapports   sur tablette et téléphone Office 365 Option Power BI
POWER BI Q & A Outil inclus dans le site   web pour poser des questions en « langage naturel » (ex : Français   courant) avec réponse en graphique Office 365 Option Power BI
DATA MANAGEMENT GATEWAY / DATA REFRESH Data Catalog ET passerelle entre   les documents dans le Cloud et les sources de données « On Promise »   pour rafraichir les données. Office 365 Option Power BI

Je ne reviendrai pas sur les différents modules d’Excel dont nous avons déjà parlé (Power Query, Power Pivot, Power View and Power Map) et pour lequel nous pouvons déjà trouver pas mal d’articles, Tutos et autre Webcasts.

Je vais faire un focus sur les éléments de Power BI inclus dans Office 365 qui vous permettent de faire de la BI Online et/ou en Cloud Hybride.

Voici tout d’abord l’architecture de Power BI qui est je le rappelle encore une version Preview et qui peut donc être modifié, il vient d’ailleurs d’être enrichi du DATA REFRESH :

PowerBI2

Comme d’écrit plus haut, nous voyons bien les deux parties – Excel sur la droite et Cloud sur la gauche – qui complète la Suite Power BI dans Office 365.

Passons donc à chaque éléments :

POWER BI SITE

Power BI Site est une app pour SharePoint Online, il permet de visualiser et organiser les documents Excel d’une meilleur façon qu’une simple librairie SharePoint. Il inclut également toutes les autres fonctionnalités propres à Power BI dont nous allons parler ci-dessous.

POWER BI MOBILE Apps

Une application propre à Power BI pour Office 365 qui permet de visualiser les rapports stockés sur le site Power BI. L’application est pour le moment seulement disponible sur le Windows Store, une application IPad serait en cour de développement. Power View est actuellement le meilleur outil pour la visualisation sur App grâce à sa visualisation Dynamique.
L’application est gratuite sur le Windows Store, cependant elle permet de visualiser seulement des rapports stockés dans le Site Power BI.

POWER BI Q & A

Cette fonctionnalité permet de poser des questions sur le Site Power BI en langage courant et de visualisé la réponse sous forme de rapport graphique. Pour le moment Q&A est basé seulement sur quelques Workbooks proposés par Microsoft ce qui ne permet pas de vraiment tester la qualité des résultats. Nous reviendrons donc sur ce module lorsqu’une version plus complète sera disponible.

DATA MANAGEMENT GATEWAY

Ce module permet de faire du Reporting en mode Cloud Hybride – j’entends par là le fait d’avoir vos rapports dans Office 365 donc dans le Cloud et d’avoir vos sources de données sur site donc « On Promise » – sans créer une infrastructure complexe.

Le DMG vous permet de créer une passerelle entre les deux environnements.

Pour faire simple, à partir du centre d’administration du Site Power BI vous aller créer une « Passerelle » sécurisée entre votre rapport et le server de donnée source. La création de la passerelle sur le Site Power BI génère une clé unique qu’il faudra installer manuellement sur le Server de donnée source à l’aide d’un agent (une seule fois).

Une fois la passerelle installée il est donc possible de visionner en mode Excel web App un rapport stocké dans Power Bi for Office 365 (un SharePoint Online amélioré) et de rafraichir directement les données!

*A Noter que les crédentials de connections au source qui sont « On Promise » peuvent être stockés dans le Cloud ou « On Promise », donc aucune données ou crédential ne seront stockés dans le Cloud si vous le désirez.
Par contre si votre Workbook contient un modèle Power Pivot celui ci sera stocké dans le Cloud, privilégié donc un modèle SSAS Tabular dans ce cas.

Le DMG permet également de pouvoir se connecter grâce à Power Query à des données « On Promise » qui seront mis à disposition des utilisateurs sous la forme d’un flux Odata. On peut imaginer des Vues qui seront créées par l’IT pour les utilisateurs, par exemple Vue Client, Vente… et mis à disposition sur le Site Power BI pour la Self BI !
Grace à la passerelle créée, les utilisateurs pourront importer les données et créer leurs propres rapports, et ce, même en étant en dehors du réseau de l’entreprise.

Data refresh

Process du flow de donnée au travers du DMG (décrit ci-dessus) qui permet le rafraichissement manuel ou automatique des données des rapports comme dans SharePoint, fonctionnalité très attendu par la communauté et qui vient d’être ajoutée à la Suite Power BI. Il permet de planifier le Refresh automatique des Workbooks et Data Catalog sur le Site Power BI.

DataRefresh

CONCLUSION

Power BI est aujourd’hui une suite complète qui permet de créer ses propres rapports entièrement dans Excel, partager les rapports et automatiser le refresh. Nous sommes donc bien dans le Self-Service BI !
Il y a encore des limitations concernant la taille maximum des fichiers Excel mais cette limitation tant à diminuer (aujourd’hui 250MB pour la visualisation d’un Workbook, 30MB pour le Refresh des données via Power BI).

L’interface du Site Power BI étant différent de SharePoint Online il peut être déroutant au premier abord mais comme tous les nouveaux outils quelques minutes de prise en main seront nécessaires. Espérons que les utilisateurs prendront le temps nécessaire car le Site est avant tout fait pour eux.

D’autres articles suivront dès que possible pour détailler les modules ci-dessus avec quelques tutoriaux.

Essayer Power Bi c’est ici : http://office.microsoft.com/en-ca/excel/power-bi-FX104080667.aspx