Speaking engagements at the end of summer

During the last months, I’ve received a few public or private messages from people asking me to speak at more events. Good news for them, they will have a couple of opportunities to listen to me at the end of this summer.

The 29th of August, I’ll be at the Oslo SQL Saturday. The topic will cover a good introduction to NBi, the open-sourced testing framework that I’m developing.

This event has a lot of sessions about BI and I’m really interested to assist at some of them. The DBA/development track is also interesting with some deep dives … and all sessions are in English. It will be difficult to make a choice.

The 5th of September, I’ll be at Paris for another SQL Saturday. I’ll be speaking (in french) about SSDT-DB (SQL Server Data Tools). The idea of the session is to demonstrate to skeptical (potential) users that SSDT is effectively covering “complex” use-cases. When I’m informally discussing about SSDT, I often receive a remark such as “yes, but it’s not working for my case because …” and most of the time my answer is “SSDT supports this by …”. The session will have a lot of demo where I’ll explain how to solve these “complex” cases. I’ll also show you some hidden benefits of using SSDT.

The list of sessions is extremely interesting and it will be difficult to make a choice. Hopefully, a couple of sessions are proposed at Oslo and Paris. I’ll have two opportunities to assist to them.

Running database-related tests on AppVeyor

A few days ago, I’ve pushed a new project on AppVeyor. AppVeyor is a continuous integration service and it’s free for open-source projects. It’s not the first time that I configure a project on this CI platform but it was the first time that I wanted to execute tests on a custom database (part of the project).

My solution is composed of three distinct projects. The main library project, another project with the tests and finally a SSDT project with the database. Most of the tests need to connect to the database and execute queries (they are not unit tests but integration tests).

The tasks list is not complex:

1. Build the three projects
2. Deploy the database to an instance of SQL Server
3. Run the tests

It’s extremely easy to build the solution on AppVeyor, you just need the sln file. First task done!

Deploying the database sounded also relatively easy. AppVeyor comes with a step “Deploy” where you can ask for the deployment of a dacpac in just a few clicks. Unfortunately the “Deployment” step is executed after the “Run tests” step … and it’s not what we’re expecting here. It’s also impossible to change the order of the steps. We’ll need to use some hacks to perform this.

First, we need to provision an instance of SQL Server by clicking on “Environment” and “Add Service”. This instance will be hosted on the same instance than the build agent and supports a SQL Server Login named “sa” with its password set to “Password12!”. A few pages of AppVeyor’s documentation also mention the support of “Integrated Security=True” but it hasn’t work on my side. The name of the instance is “(local)\SQL2014”.

Now that the SQL Server instance is provisioned, we need to deploy the database before running the tests. As previously said, impossible to use the “Deployment” step because it will occur after the “Tests” step. On the other hand, AppVeyor supports to configure events “before the tests”. We’ll use this feature to request the deployment of our dacpac on the previously provisioned SQL Server instance.

The trick is to use the full path for SqlPackage.exe (The software effectively managing the deployment of your dacpac to an instance of SQL Server). This path is the same on all build agents of AppVeyor.

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"YourProject.Database\bin\Debug\\YourProject.Database.dacpac" /TargetDatabaseName:YourProject

When done, we still need to adapt the connectionString of our tests to redirect to the correct instance/database on AppVeyor environment. Naturally on my own laptop, the connection-string is different and on my own build machine same issue: the connection-string is also different. Personally, my solution is to provide a generic file connectionString.config and a few other files such as connectionString.appVeyor.config Each of these files contains the correct connectionString for a specific environment. The trick is to override the file connectionString.config by another one (in this case connectionString.appVeyor.config) after the build. You can configure after-build events in your Visual Studio project but this event is only required when building on appVeyor, so I prefer to setup it directly on the platform.

The following CMD commands will do the job: if file ConnectionString.config exists then delete it, rename “ConnectionString.appVeyor.config” to “ConnectionString.config”. Note that all path are relative to the root of your solution.

if exist "YourProject.Testing\bin\Debug\ConnectionString.config" del "YourProject.Testing\bin\Debug\ConnectionString.config"
copy "YourProject.Testing.Testing\bin\Debug\ConnectionString.appVeyor.config" "YourProject.Testing.Testing\bin\Debug\ConnectionString.config"

It’s done, you now have a correctly configured CI on AppVeyor executing your database-related tests.

Detect Tabular or Multidimensional mode with AdomdClient

In this excellent blog post, Marco Russo explains how to programmatically detect if an instance of SSAS is in Tabular or Multidimensional mode.

This post is a great resource but didn’t fully resolve my own issue. The project I’m working on already had a reference to Microsoft.AnalysisServices.AdomdClient.dll and if I could avoid a second dll of this kind it would be a nice achievement. Seriously, if you can avoid to package and deploy dlls related to SQL Server … do it!

The second enlisted option in Marco’s post is based on Analysis Management Objects and means that you need to reference Microsoft.AnalysisServices.dll. As previously explained: no way!

So the only remaining option viable for my case is to use the XMLA approach (with the Microsoft.AnalysisServices.AdomdClient.dll). The AdomdCommand class has a nice method ExecuteXmlReader. This method returns an XML reader, it sounded like a nice point to start. But it’s definitely not a valid option, the AdomdCommand doesn’t support discovery request. If you try to provide the xml discovery request in the property CommandText then, at the execution, you’ll receive this kind of message.

Executing the query ...

The Discover element at line 7, column 72 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Execution complete

How to perform a discovery request on a cube with just the AdomdClient library? Simply by using a nice method of the AdmodConnection (Yes connection, not command) class named GetSchemaDataSet. This method expects the name of the AdomdSchema that you want to query: In our case DISCOVER_XML_METADATA. You also need to provide some restrictions to avoid a gigantic response from SSAS. In this case the restriction is “ObjectExpansion” with value “ReferenceOnly”.

using (var conn = new AdomdConnection(connectionString))
{
   conn.Open();
   var restrictions = new AdomdRestrictionCollection();
   restrictions.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
   var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
}

Next task is to decrypt the response sent by SSAS. This response is stored in a dataset with one table and one row. The unique field contains the whole xml string. The best option is to load this string into an XmlDocument before parsing it.

var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
var doc = new XmlDocument();
doc.LoadXml(xml);

You need to query this xml document to extract the ServerMode node’s text. Don’t forget to correctly configure the namespaces before querying the document!

protected string ParseXmlaResponse(XmlDocument doc)
{
   var root = doc.DocumentElement;
   var nm = new XmlNamespaceManager(doc.NameTable);
   nm.AddNamespace("ddl300", "http://schemas.microsoft.com/analysisservices/2011/engine/300");
   var node = root.SelectSingleNode("//ddl300:ServerMode", nm);
   if (node == null)
   throw new ArgumentException("Unable to locate the node for ServerMode.");
   return node.InnerText;
}

Note that you’ll need, at least, the version 11.0 of the AdomdClient library, previous versions will not return the node ServerMode.

Side note: This discovery request also returns the version of the SSAS instance. Even with the AdomdClient library 10.0 (or previous), you can parse this field “version” and if its content is less than 11.0, you can take the conclusion than this is surely a multidimensional instance. 😉