THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

  • Custom Data Provider in Datazen

    Playing with Datazen in the last days, I had to solve a quite interesting problem that took me some time but also allowed me to dig deeper into Datazen architecture in order to find a way to go past its (apparent) limits.

    Here’s the story, as I’m sure will be useful to someone else too.

    One of our current customer has a quite complex Analysis Services dynamic security. Beside applying security on who is accessing the data, they also want to apply security based on how someone access such data. In order to satisfy this requirement a specific extension to Excel (their chosen client) has been developed, and it uses the CustomData() MDX Fuction.

    So, here’s the problem: how can I specify values for CustomData property in the SSAS connection string in DataZen, as there is no such property exposed by default from the native SSAS data provider?

    Luckily DataZen support custom data providers, so it’s quite easy to create a new one that exposes the properties you need:

    I’ve tried to go for the “Overriding built-in data providers” road but I wasn’t able to make it work. I tried to add the “CustomData” property to a file that overrides the default SSAS data provider setting but at the end the “CustomData” property was the only option I was available to see in the overridden native provider. So I created a new SSAS Data Provider and that’s it, everything works perfectly:

                <name>Data Source</name>           
                <name>Initial Catalog</name>

    Be aware that Datazen do *a lot* a caching so you’ll have to stop the Core service BEFORE you edit/create the XML file, otherwise you may find it overwritten with cached data, and also be sure to IISRESET your web server otherwise you can easily get mad trying to understand why what you’ve just done is not showing up in the UI.

    Beside the caching madness, everything works great.

    Hope this helps!

  • Configuring Pass-Through Windows Authentication in Datazen

    I’ve been working with Datazen lately (I’m working with a customer that literally felt in love with it) and one of the last thing we tried as a port of a POC before going into real development, is integration with Windows Authentication.

    It’s really easy to do that, you just follow instructions here (in the section “Authentication Mode”)

    and it just works. As documentation suggest, you just have to specify the domain name and that’s it.

    Of course, after that, you may also want to enable pass-through authentication, so that once a user tries to access a dashboard via HTML interface, Datazen will use the logon credential, without going through and additional logon screen.

    Here things can be tricky if you just follow that documentation here:

    which is correct but only to a certain degree. Everything is correct, it’s only missing to say a *very* important thing that you have to know to make sure that it works as expected: you have to provide ALL FOUR SETTINGS (Server, UserName, Domain, Password) in order to make it work.

    If you forgot to do it during installation, no problem, you can do it later setting the

    • ad_server
    • ad_username
    • ad_domain
    • ad_password

    configuration values as explained here:

    After that, the magic happens, and everything works perfectly


    Of course you have to have configured Kerberos Authentication and Delegation correctly, but that’s another story.

  • SQL Server 2016 CTP 2.3: Management Studio and Data Tools Updates

    After SQL Server 2016 CTP 2.3 has been released, also Management Studio and SQL Server Data Tools has been updated too. Having three different teams working on three different products, means three different places one has to look for to become aware of the updates, so make it easier for everyone who’s asking, here the complete set of link to have a full SQL Server 2016 CTP 2.3 installation (SQL Server Platform + All the Clients):

    SQL Server 2016 CTP 2.3

    SQL Server 2016 Management Studio Preview August 2015 Update

    SQL Server 2016 Data Tools (with support for both Database & Business Intelligence) Preview August 2015 Update


  • SQL Saturday in Italy…English version!

    The 2015 is a special year for Italy, because the country hosts Expo 2015, which is the current Universal Exposition. For this reason, the Italian PASS chapter promotes a special edition of SQL Saturday, a free training event for SQL Server professionals. The SQL Saturday #454 in Turin on October 10, 2015 has the following characteristics:

    • More than 20 sessions, on SQL Server, Business Intelligence and Azure Data Platform.
    • All the sessions will be in English language.
    • The venue is in the center of Turin, close to the train station:
      • You can be at the expo in 40 minute
      • You can travel to Milan in less than 1 hour
    • Turin is usually less expensive than Milan and you might stop for at least one night, dedicating the Sunday after SQL Saturday to visiting the Expo or Milan.

      We want to provide the best experience to the attendees, and we also want to help those of you traveling with family and/or friends that might not interested to technical content. For this reason, we are planning a web page containing information for side and/or alternative activity during the SQL Saturday. You will get more information about that starting in July.

      However, we first need a good estimation of the number of attendees, in order to correctly size the venue and to verify the interest in side activities, so we will module the time to allocate in such a section of the upcoming web site. These operations have to be completed months ahead of the event.

      For this reason, we ask you to fill the survey at, providing us important information about your intention of visiting Expo 2015 and about the number of people who will travel with you.

      If you are a speaker, please submit your sessions, considering that the agenda will prioritize three topics: SQL Server 2016, Power BI, and Azure Data Platform.

      See you in Turin!

    • SQL Konferenz 2015 Slide & Demo

      Last week I spoke at the SQL Konferenz in Darmstad near Frankfurt. The conference was great and I meet a lot of good SQL friends over there. For anyone interested here you can find slide & demos of the session I delivered:

      (Near) Real-Time Data Integration with SQL Server, On-Premises & Cloud

      Schema-Less Table & Dynamic Schema

      You’ll find a link to evaluated the session on SpeakerScore and to download the slides in the last slide of each deck.


    • Iris Multi-Class Classifier with Azure ML

      As many of us I’m passionate about informatics *and* mathematics which, of course, lead me to be passionate about the outcome of their marriage: Databases and Machine Learning.

      Now that Machine Learning is becoming a kind of a “commodity” thanks to AzureML I can finally start to use it in any projects, even the not-so-big-ones.

      AzureML, for those who doesn’t yet know it, is the Machine Learning offer for the cloud by Microsoft. You can freely start to use it just activating your subscription here:

      Once activated you’ll find a lot of ready-to-be-used stuff. From “experiments” (kind of “programs”) and dataset and components and models (algorithms).

      One thing I noticed is missing is the full Iris Dataset, one of the most famous and used dataset to start to learn machine learning. In AzureML you can find a subset of it, usable for binary classification, but the original one is much more interesting since it can be used to do a multiclass classification.

      In order to fill this little gap and to create an easy tutorial to help everyone to start to get confident with AzureML and machine learning in general, I’ve created a 10-Step (well…Italian way of 10 steps Winking smile) tutorial that can be found here: 

      or here

      choose the website you prefer Smile and start to play!

      As usual, comments and feedbacks are more than welcome!

    • SSISDB Monitoring Queries on GitHub

      I’ve moved my SSISDB scripts from Gist to GitHub where I can maintain them more comfortably. So far, I’ve published 6 scripts:

      • ssis-execution-status: Latest executed packages
      • ssis-execution-breakdown: Execution breakdown for a specific execution
      • ssis-execution-dataflow-info: Data Flow information for a specific execution
      • ssis-execution-log: Information/Warning/Error messages found in the log for a specific execution
      • ssis-execution-lookup-cache-usage: Lookup usage for a specific package/execution
      • ssis-execution-package-history: Execution historical data

      I used them almost every day when I need to have a quick glance to what’s going on on Integration Services and when I need to do some deep analysis of errors and problems.

      You can find them here:

      If you’re also wondering what happened to the SSIS Dashboard project

      …don’t fear, it’s not dead. I’m still working on it, but since I’m working on it only in my free time, updates are taking much more time than expected.


      Funny enough, Andy Leonard published a script to analyze lookups just couple of hours before me. You may also want to take a look at his post:

    • SQL Konferenz 2015

      On the first days of February I’ll be speaking at the German SQL Konferenz 2015 in Darmstad (Frankfurt) with a lot of other friends:

      I’ll be talking about two topics that my developer side love at most : Service Broker and “Dynamic Schema”:

      Schema-less table & Dynamic Schema
      How to manage a system in which the schema of data cannot be defined "a priori"? How to quickly search for entities whose data is on multiple lines? In this session we are going to address all these issues, historically among the most complex for those who find themselves having to manage yet very common and very delicate with regard to performance. From EAV to Sparse Columns, we'll see all the possible techniques to do it in the best way possible, from a usability, performance and maintenance points of view.

      Real Time Data Integration (in the Cloud or not)
      Service Broker and Integration Services can work so well together that they allow the creation of high-performance Real Time Data Integration solution with just a few days of work. No matter if you're on premise or on Azure, a real-time integration will open up new opportunities to deliver data and information faster and more efficiently, empowering the end user with all they need to do a great job. Let's say that your ERP software is on premise and you need to create a real-time dashboard in the Cloud...or that you have to integrate with your cloud-based sales force management solution. Do you really think that a batch update every 15 minutes can be solution, while for the same price you can have something done in real-time? In this session we'll see how to build such solution (that allowed one of our customer to completely replace TIBCO), from start to end.

      See you there!

    • Expired Account Password on a Azure VM

      Today I faced a really nasty problem. I’m really getting in love with Azure and especially with SQL Server hosted in Azure VM. It opens up a huge amount of opportunities, for small, medium and big companies, since they can have everything they ask for but without the burden of having to maintain a server factory.

      That’s very cool, but the inability to physically log into server can give you some headaches if RDP doesn’t work as expected. For example when you’re not in a domain and your password expires. It seems that no-one in Microsoft cared to fix the problem, since is still there even if people reported it back in 2013

      Today I had exactly the same problem. At some point the RDP client started to return me the error

      “The Local Security Authority Cannot be Contacted”

      After having spent some time trying to find out what could be the cause of the error (even following some wrong roads, given the fact that the error is just too generic), I thought that could be due to the fact that the password was expired. And that was exactly the problem. This post (even older than 2013, so the problem is even older….) confirmed me that my idea could be correct.

      Unfortunately the aforementioned posts states the problem, but doesn’t really describe how to solve it in my specific case. The main problem is that if the server requires the Network Level Authentication, the RDP client won’t show you the “Password Expired” screen, so you won’t be able to change the password. This means that you cannot access your VM anymore, which is not fair. By default NLA is enabled on Windows Server 2012 R2 and since I couldn’t log in, I couldn’t even disable it, so I was stuck with my problem.

      Anyway, at least now I know where to look for. Still, I had to solve another problem: how do I change a password for an Azure VM to which I cannot connect using RDP? Luckily it seems that there are a lot of people that forgot their passwords, and so they need to reset it, so the problem is well known. Here there are two post that explain how to do it using PowerShell and the related Azure PowerShell Module.

      The PowerShell script works if and only if the VM Agent is installed. Luckily this is the default option when you provision a new Azure VM, so you haven’t anything special do to in order to have it installed.

      Well, now you know it, keep it in mind in case you find yourself in the same situation.

    • Using NLog With BIDS Helper to add logging to BIML Script executions

      When using BIML within BIDS Helper, if your BIML Script files get complex it may be quite hard to debug BIML Script Execution in order to understand what’s going on behind the scenes.

      On Windows Server 2008 it was possible to use the System.Diagnostic.Debug.WriteLine and DebugViewer from SysInternal to do the trick but it seems that this approach doesn’t work anymore on Windows Server 2012. Or, at least, I wasn’t able to make it work. Anyway, in addition to that, I was also trying to have everything logged to a file so one doesn’t have to use and configure DebugViewer to do its job. DebugViewer is great tool, but it’s not really suitable for junior developers.

      So I tried to use the fantastic NLog framework in order to create a “standard” way of logging BIML. First of all download it (just get the standard version, no need to grab the “Extended” one) and unpack into a folder named “NLog-3.0” where you prefer.

      Now in your SSIS Solution create a BIML file named “BimlLogger.biml” and copy and paste the following code:

      <#@ template language="C#" #>
      <#@ assembly name="C:\Work\Lab\BIML-Debug\NLog\NLog-3.0\net40\NLog.dll" #>
      <#@ import namespace="System.Diagnostics" #>
      <#@ import namespace="NLog" #>
      <#@ import namespace="NLog.Config" #>
      <#@ import namespace="NLog.Targets" #>

          Logger logger = LogManager.GetLogger("BIML");

          LoggingConfiguration config = new LoggingConfiguration();

          FileTarget fileTarget = new FileTarget();
          config.AddTarget("file", fileTarget);

          fileTarget.FileName = @"${nlogdir}\..\..\biml.log";
          fileTarget.Layout = "${longdate}|${level:uppercase=true}|${message}";
          fileTarget.DeleteOldFileOnStartup = true;

          LoggingRule loggingRule = new LoggingRule("*", LogLevel.Trace, fileTarget);

          LogManager.Configuration = config;   

      Change the second line (the “assembly name” line) in order to reflect the path where you unpacked NLog and then you can start to log anything you think it can help you just referencing this file from any other BIML files and then using the “logger” object to write to the log file. Here’s an example of a BIML script file that creates a test package.

      <#@ template language="C#" #>
      <#@ include file="BimlLogger.biml" #>

          logger.Info("Generating Package...");

      <Biml xmlns="">
          <Package Name="Test" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" />


      The log file will be created in the “NLog-3.0” folder you created before. Of course you can change this and many other option, since NLog is really flexible and powerful. Documentation and tutorial are here:

      2014-12-13 Update

      In order to have the trick working, you have to be sure that the NLog assembly is *not* blocked…which is something will happen automatically if you download the zip from internet. In order to unblock the assembly you have to right-click on it and then select “Unblock”:


    • PASS Summit 2014 Pre-Con Preview: Davide Mauri

      If you’re into Data Warehousing, you may be interested in attending to the workshop I’ll deliver at PASS Summit 2014 in Seattle on November 4th.

      The workshop is entirely dedicated to explaining why and how a *successful* Data Warehouse can be thought, designed, architected, built, loaded and tested, using the  Agile approach that, so far, has mainly be applied to the application development field and in the last year has gained traction also (and finally I would say) in the BI field. Both Gartner and Forrester also underline that the Agile is a key factor for success in modern BI world, since has been verified that 50% of the requirement change in the first year in a BI project.

      If you want to read more about the workshop, we you read the Q&A just published here:

      In addition to that I’d also like to share the agenda of the workshop, that will give you even more information on what we’ll discuss on that day:

      • Why a Data Warehouse?
      • The Agile Approach
      • Modeling the Data Warehouse
        • Kimball, Inmon & Data Vault
        • Dimensional Modeling
        • Dimension, Fact, Measures
        • Star & Snowflake Schema
        • Transactional, Snapshot and Temporal Fact Tables
        • Slowly Changing Dimensions
      • Engineering the Solution
        • Building the Data Warehouse
          • Solution Architecture
          • Naming conventions, mandatory columns and other rules
          • Views and Stored Procedure usage
        • Loading the Data Warehouse
          • ETL Patterns
          • Best Practices
        • Automating Extraction and Loading
          • Making the solution automatable
          • BIML
      • Unit Testing Data
      • The Complete Picture
        • Where Big Data comes into play?
      • After the Data Warehouse
        • Optimized Hardware & Software
      • Conclusions

      As you can see it will be a fully packed brings two cups of coffee and you'll be good :)

      See you in Seattle!

    • Sketch notes from 24 Hours of PASS

      24 Hours of PASS has passed and, beside the slides, demo and video (that will come soon on, this time, thanks to Matt Penny (@salisbury_matt) you can also have very nice and well done sketch notes that summarizes the concept of the sessions Matt attended to, in a very nice, quick, effective and friendly way. Here’s what Matt did for my session:


      I love it! I must say I’m a fan on sketch notes. It’s quite an art on its own IMHO, ‘cause good sketch notes mix written and visual language such in a way that make much more easier to the read to get the message and memorize it. I’ll be using the notes that Matt took for my session quite a lot in future, for sure.

      Beside notes of my session, you can find here

      sketch notes for the following sessions:

      • Brent Ozar on ‘Developers: Who Needs a DBA?’
      • Brian Knight on ‘Performance Tuning SQL Server Integration Services (SSIS)’
      • Allan Hirt on ‘Availability Groups vs. Failover Cluster Instances: What’s the Difference?’
      • Erin Stellato, Jonathan Kehayias on ‘Everything You Never Wanted to Know about Extended Events’
      • Gail Shaw on ‘Guessing Games: Statistics, Heuristics, and Row Estimations’
      • Tim Chapman, Denzil Ribeiro on ‘Troubleshoot Customer Performance Problems Like a Microsoft Engineer’
      • Argenis Fernandez on ‘Secure Your SQL Server Instance without Changing Any Code’
      • Joe Webb on ‘Hiring the Right People: Interviewing and Selecting the Right Team’
      • Robert Cain, Bradley Ball, Jason Strate on ‘Zero to Hero with PowerShell and SQL Server'
      • Chris Shaw, John Morehouse on ‘Real World SQL 2014 Migration Path Decisions’
      • Julie Koesmarno on ‘”I Want It NOW!” Data Visualization with Power View’
      • Jen Stirrup on ‘Business Intelligence Toolkit Overview: Microsoft Power BI and R’
      • Ryan Adams on ‘SQL Server AlwaysOn Quickstart’

      Thanks Matt!

    • SSIS Dashboard v 0.6.1

      Yesterday I’ve released the latest SSIS Dashboard update. There quite a lot of new features included that I found to very useful when you have a server full of packages and logs. Here the complete list: Highlighted the feature I think worth the most:

      • Updated Morris.js to v 0.5.1
      • Updated MetisMenu to v 1.1.1
      • Added information on "Child" Packages
      • Added more detail to the "Package Execution History" page. Also added an estimated end time / elapsed time for running packages, using a moving average of 7 steps.
      • Added navigation sidebar in the main page that shows available folders and projects
      • Added support for folders and project filtering
      • Changed configuration file in order to comply with Python/Flask standards
      • Cleaned Up code in order to follow Python best practices (still a lot to do :))

      Have you had a chance to give it a try? What features you’d like to see added?

      My plans for the next releases is to

      • Add a configuration page so that you can choose the maximum number of rows return (now set to 15) and the time interval you want to analyze (not set to 72 hours in the config file)
      • Use a EWMA instead of the simple Moving Average
      • Do a video to show how to install and use the Dashboard
      • Package everything in only one executable file / directory / VM (I want to be able to offer a xcopy deplyment “all-included”…not only the .py files)
      • Include additional information taken from [event_message_context], [executable_statistics], [execution_parameters_values]
      • Fix the layout for small / medium screens (smartphones / tablet)
      • Add historical / average elapsed time also for Child Packages and Executables
      • Include DataFlow informations

      Once all those things will be do, version 1.0 will be ready

      If you want to help, fork the code from Github:

      if you want to try it go here

    • On Agile Data Warehousing

      In the last month, I’ve been working on the slide deck of my Agile Data Warehousing workshop. In order to give to it additional value that goes beyond the pure technical aspects, and since now the “Agile” approach is becoming more and more mainstream also (and finally!) on BI, I did a small research to check what one can find on the web regarding this topic. Many things happened from the very first time I presented at PASS 2010, where I first mentioned the need to be Agile (or “Adaptive” as I prefer to say when talking about BI & Agility). In 2011 Gartner, at their BI Summit, stated through the voice of Andy Bitterer that

      50% of requirements change in the first year of a BI project

      and, as a result, the only possible way to succeed in a BI project is to be able to adapt quickly to the new requirements and requests. The doors to Agile BI were opened.

      Agile BI as grown from that point on, until the point that Forrester even started to evaluate Agile Business Intelligence Platform, even nominating Microsoft as one of the Leaders:

      Microsoft named a Leader in Agile Business Intelligence by Forrester

      I must say I’m not 100% with the definition of Agile BI the Forrester gives, since it puts together to many things (Data Visualization, Automation, Self-Service BI just to name a few), but I understand that they see the things from the end user perspective, that simply wants to “do everything, immediately, easily and nicely” with its data. There is also a definition on Wikipedia (page created on January 2013) that is better, more complete and less marketing-oriented:

      Beside those definitions, terms like Agile BI and Lean BI became quite common. Of course, with them, came also the idea of Agile Project Management and Agile Modeling. Especially this latter subject seems to be very hot and of course is something that is also close to my interests. Now, I won’t want to go into a deep discussion of the topic, telling you what it good and what is bad. There is already a lot on the web for or against any possible modeling solutions. Data Vault, BEAM, Model-Storming…a simple search on the web and you’ll find thousands of articles. Who’s the best? Should we go for Data Vault? Or for an Inmon-style DWH? Or Kimball? Or something else?

      Well…I don’t really care. Or, to be honest, a care just a little bit.

      Now, since “Ideas without execution are hallucinations”, and models are ideas after all, it’s my strong opinion that you don’t model the be agile: you “engineer” to be agile. Why? It’s simple: all models are agile…since they are models, and nothing more. Is not a problem to change a model, since it’s “just” a formal definition of a system…(of course, I’m bringing the idea to the extreme here)  and, since we’re assuming that business requirement will be changing, you known in advance that no model that will satisfy them all (immediately) exists (yeah, you can try to model the “Universal Data Model” but it’s going to be *very* complex…). So, the main point is to be able to bring changes quickly, with a measurable quality, in a controlled and economic way.

      We all know that the one and only one model that should be presented to the end user is the Dimensional Model. This is how your Data Mart should look like. But how do you model your Data Warehouse is completely up to you. And it will change over time, for sure. So how you implement the process in order to extract, transform and load the data, is the key point. That implementation must be agile. What lies behind the scenes, following the information hiding principle, should be considered a simple “technical enabler” that could change at any time. So, if one prefer to use Data Vault, or Inmon, or just store anything in some hundreds Hadoop server…I don’t see any problem with that. As soon as you have defined an engineered approach with naming conventions, design pattern automation, quality checks, metadata and all the stuff in order to make sure that when you have to change something, you can do the smallest change possible, measure its impact, and test the result.

      I’ve been trying to apply Agile principles to BI since 2003…I’ve been through any possible changes that you can imagine (even a complete change of an ERP that was the main source of data) and the most important thing I’ve learned is that the only model that works is the one that is liquid and is able to adapt quickly to changing requirements. I usually start modeling in the easiest way possible, and thus I apply the Dimension Model, and then I make all the changes to it in order to be able to keep

      • All the data at the highest granularity
      • Optimal performances
      • Historical Informations (that may not be visible to end user, but may be needed to correctly transform data)

      Which, for complex DWH, means that at the beginning the DWH and the Data Mart overlaps, and that they diverge as the project goes on. In one project we even decided to go for a normalized model of data since the DWH became the source not only for reporting and analysis but also for other, more operative, duties.

      Now, in order to be really agile, it’s mandatory to have an engineered approach that make sure that from agility the project doesn’t fall into anarchy. Because this is the biggest risk. The line that separates the two realities it’s very thin and crossing it is very easy. When you have a team of people, or they work as one, or Agile BI is not for you. Otherwise chaos will reign. And to make sure this does not happen, you have to have a streamlined building process, tools and methods (design patterns, frameworks and so on) so that everyone can technically do a good job and technical quality of the outcome is not only proportional to the experience of the person doing it.

      It’s really important that everyone who wants to approach Agile BI understand the “engineering” part. I found it always underestimated and in all post I’ve found on the web, I never read someone stressing the importance of that part. That’s why I felt the urge to write this post, and that’s why I’ll go very deep in this topic during my PASS Workshop.

      Now, before finishing the post, there is still one thing missing, but vital, for the success of an Agile VI solution: testing. Agility cannot exist if you don’t have an automated (or semi-automated) testing framework that assures you and your users that no errors will be introduced in the data as a result of a change done to satisfy some new or changed requirements. This is mandatory and I’m quite disappointed to see that almost no-one underline this point enough. Forrester doesn’t even took into consideration this point when evaluating the existing “Agile BI Platforms”. That’s a very big mistake in my opinion…since everyone give for granted data quality, but it’s actually the most difficult thing to obtain and maintain.

      Testing frameworks are quite common in development, even Visual Studio has a testing engine integrated, and they should become common in BI to. Something is starting to appear (, but I wish that also big players (Microsoft above all) start to take this subject more seriously. How cool and useful will be a strong integration of testing in SSIS? After DWH/DM/Cube loading one could launch all the tests (maybe done right from Excel, from a power-user, or even created automatically if certain conditions are met…say the generation of year balance) and make sure that the freshly produced data are of good quality.

      Just like water. Because data IS water. I won’t drink it if not tested.

    • SSIS Dashboard 0.5.2 and Live Demo Website

      In the last days I’ve worked again on the SQL Server Integration Service Dashboard and I did some updates:

      Beta Added support for "*" wildcard in project names. Now you can filter a specific project name using an url like:


      Added initial support for Package Execution History. Just click on a package name and you'll see its latest 15 executions

      and I’ve also created a live demo website for all those who want to give it a try before downloading and using it:

    More Posts Next page »

    This Blog


    Powered by Community Server (Commercial Edition), by Telligent Systems
      Privacy Statement