How To: Get data from OLAP cubes

cuberubikToday I had to read some data from different OLAP cubes and show this information in our application. This could be accomplished using ADOMD.NET client library, which is the multidimensional equivalent to the ADO.NET libraries.

This library provides a set of classes (Connections, Commands and Readers) that allows developers to connect, query and get data from these multidimensional sources in the same way the classical ADO.NET library does.

Maybe the easiest way to read data is using the class AdomdDataReader. This object retrieves a read-only, forward-only, flattened stream of data from an analytical data source, and also implements the IDataReader interface (like the good-old DataReader).

In non dynamic scenarios, we developers, tend to use ORMs like NHibertate, EF or Dapper (my favorite), to translate data from our models to classes. But these ORMs doesn’t support multidimensional sources, because after all, they are only supersets or extensions of the ADO.NET model and are based on this existing object model.

So, our goal here will be replicate in a multidimensional environment the way Dapper allows you to connect to a server, read some data and (the most important part) return these data as a typed List<T> of objects.

Let’s see how it works before dig into the details:

using (var conn = new AdomdConnection(connectionString))
{
conn.Open();
var commandText = @"SELECT
{
[Measures].[Gains Period],
[Measures].[Gains YTD],
[Measures].[Amount]
} ON COLUMNS,
[Valuation Dates Accumulated].[Hierarchy].[Year].Members ON ROWS
FROM Accumulated
Where ({ @PortfolioId })";
var data = conn.Query<MyResultDTO>(commandText,
new AdomdParameterMultipleValues<int>()
{
ParameterName = "@PortfolioId",
MemberName = "[Portfolios].[Port Code]",
Values = new List<int>() { 282, 185 }
}
);
conn.Close();
}

First of all we have a SQL script that returns some sample data of 3 different measures year by year (gains in period, gains year-to-date and the amount of money).

mddata

And we’d like to translate these data into a generic List of my own DTO:

internal class MyResultDTO
{
[OLAPMemberNameAttribute("[Valuation Dates Accumulated].[Hierarchy].[Year].[MEMBER_CAPTION]")]
public string Year { get; set; }
[OLAPMemberNameAttribute("[Measures].[Gains Period]")]
public decimal? GainsPeriod { get; set; }
[OLAPMemberNameAttribute("[Measures].[Gains YTD]")]
public decimal? GainsYTD { get; set; }
[OLAPMemberNameAttribute("[Measures].[Amount]")]
public decimal? Amount { get; set; }
}

view raw
Adomd_DTO.cs
hosted with ❤ by GitHub

Notice that in C# properties can not contain spaces so we previously have to ‘map’ the DataReader column names into valid property names. The trick here is create an attribute for decorating each property with the equivalent column name in the reader.

The magic here is inside the generic Query<T> Method. This method is just an extension method of the AdomdConnection object (Dapper style) and it’s mission will be:

  1. Replace the parameters in the command string “…Where ({ @PortfolioId })”
  2. Execute the command against a valid connection
  3. Transform the returned IDataReader into a List<MyResultDTO>
public static List<T> Query<T>(this AdomdConnection conn, string commandText, params object[] parameters)
{
var commandTextParameters = ReplaceCommandTextParameters(commandText, parameters);
var cmd = new AdomdCommand(commandTextParameters, conn);
var dr = cmd.ExecuteReader();
return dr.ToList<T>();
}

view raw
Adomd_Query.cs
hosted with ❤ by GitHub

We’ll focus on the third one. Let’s create another extension method of the IDataReader interface. OMG! Code on interfaces? Yes… I’ll burn in hell, I know it… :D

This is the extension method with some auxiliary functions:

public static List<T> ToList<T>(this IDataReader dr)
{
var list = new List<T>();
var obj = default(T);
while (dr.Read())
{
obj = Activator.CreateInstance<T>();
foreach (var prop in obj.GetType().GetProperties())
{
var columnDescription = getPropertyDescription(obj, prop.Name);
if (existsDataReaderColumn(dr, columnDescription)) copyColumnValueToProperty(dr, obj, prop);
}
list.Add(obj);
}
return list;
}
static void copyColumnValueToProperty<T>(IDataReader dr, T obj, PropertyInfo prop)
{
try
{
var columnDescription = getPropertyDescription(obj, prop.Name);
var columnOrdinal = dr.GetOrdinal(columnDescription);
var value = dr[columnOrdinal];
var canBeNull = !prop.PropertyType.IsValueType || (Nullable.GetUnderlyingType(prop.PropertyType) != null);
var castToType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
if (canBeNull && value == null)
prop.SetValue(obj, null, null);
else
prop.SetValue(obj, Convert.ChangeType(value, castToType, CultureInfo.InvariantCulture), null);
}
catch { }
}
static bool existsDataReaderColumn(IDataReader dr, string propertyName)
{
try
{
var obj = dr[propertyName];
return true;
}
catch { return false; }
}
private static string getPropertyDescription(object value, string propname)
{
var propinfo = value.GetType().GetProperty(propname);
var attributes =
(OLAPMemberNameAttribute[])propinfo.GetCustomAttributes(
typeof(OLAPMemberNameAttribute), false);
if (attributes != null && attributes.Length > 0)
return attributes[0].Description;
else
return value.ToString();
}

EDIT (2018-12-11) : Added custom attribute to the post, used to decorate our DTO.

public class OLAPMemberNameAttribute : System.Attribute
{
string _Description = string.Empty;
public OLAPMemberNameAttribute(string description)
{
_Description = description;
}
public string Description { get { return _Description; } }
}

As you can see the method iterates over the reader’s data and uses reflection to create an object per each row, map each column’s value to the correct property and then add the new object to the returned collection (*). Easy!

adomd_result

(*) image courtesy of oz-code, the best debugger addin for visual studio #imho ;)

Hope this helps! ;)

How To: Connect via SFTP using SSH.NET

Overview

In these times of APIs-everywhere, it may sound like an anachronism the use SFTP to connect to a remote server and get a list of files for exchanging information, but in the financial world (sadly) it’s more common than you think.

In my current project I’ve to connect to a remote server via Secure File Transfer Protocol (aka SFTP) using a user name, a RSA private key and a phassphrase. Once connected, the goal is to read some files from a remote folder and download them to a local folder.

To accomplish this I recommend you to use SSH.NET, one of the most popular SSH libraries for .NET, available on nuget.

sshnet

More info about SSH.NET here: https://github.com/sshnet/SSH.NET/

Using SSH.NET to connect to the server

The most difficult part here is configure the connection, as usual. We need to provide the server url and port, in combination with the username and a file that contains the private key of the RSA certificate, and -of course- the passphrase.

Here’s the final code, and it works :)

private static void SFTP_Connect_And_Download_Sample()
{
var host = "sftp_server_address.com";
var port = 999;
var username = "your_username";
var passphrase = "your_passphrase";
var privateKeyLocalFilePath = @"your_localpath\…\PrivateKeyOpenSSH.ppk";
var remoteFolderPath = "/";
var localFolferPath = @"D:\locafiles\";
var key = File.ReadAllText(privateKeyLocalFilePath);
var buf = new MemoryStream(Encoding.UTF8.GetBytes(key));
var privateKeyFile = new PrivateKeyFile(buf, passphrase);
var connectionInfo = new ConnectionInfo(host, port, username,
new PrivateKeyAuthenticationMethod(username, privateKeyFile));
using (var client = new SftpClient(connectionInfo))
{
client.Connect();
var files = client.ListDirectory(remoteFolderPath);
foreach (var file in files)
{
Console.WriteLine(file);
using (var targetFile = new FileStream(Path.Combine(localFolferPath, file.Name), FileMode.Create))
{
client.DownloadFile(file.FullName, targetFile);
targetFile.Close();
}
}
client.Disconnect();
}
}

Bonus * the trick *

I’ve spent a lot of time before it works, receiving a SSH exception “Invalid private key file”, but the same key file works fine when using the app Filezilla to connect, so… #WhatTheHell :S

PuTTY-User-Key-File-2: ssh-rsa
Encryption: aes256-cbc
Comment: rsa-key-KBL-20171006
Public-Lines: 6
AAB3NzaC1yc2EAAAJQAAAQEAnHp1CA4xF04ZdOQ/rsxJoW9fPJ2RD
FgMNVIqsUsjeRbIoZ2y8SMD9b7MMB0lpKXgJ2dYDgOnh2q
j4VTpEoI2JWh4NdQgSH0O+2oLmQnwgDPT7Kva095ggEQiqScX4+31aY02/nz
mK86sxq/sUsW/UqgS+pPViRQLVzDXFf8XIYSSZngmV+Rk108BQ==
Private-Lines: 14
khHfZWB0vBIFtKc4s98xGDFhwZNJQByUTtE7um0tcU4cwy1QTPf3GIuN
vyhHxIGx3LBtFJqzbZVJtOJVSYjkBTGbNc62D0uJCxYVf8PUUStI6GbOkkyDW/Vt
beWZ3s/DugsImjcbPxdEz/2X86uzd5U5v4/wGKQr8GWJtNksMcJ
k7JgRYGA/t0cSE2980MxhZOBg2Gn7+0A6mWgSf2Rr7hpcqsou1
hmc1HYtN7Oj4WT7hvRt8ZAC3/ekTdJ4K3K7vKglSHoQ
tnimHaanJHz4RGBb78Alllk+OYk3TN0Etcwod3401cLpjYYeq6veZLA/KfCHuiJ+
+Zqoy//NY9egfXd1hB0kmiemwO8wGfLS7ppS/WvPOknW9I8SNMllR1vmO3Hk6S3x
KfAG03ZWNoKDLvAIUllNyMpf9p8oKLF2ny4bJKsfNtr4Y0ejQJUFkC
uNz1S4VJ8j1fRcIjx4yT121B9BDfp486RUmnEgsOFEtmVyHPfNxYzDXq2MjTf4l/
MI5cKWHrDbDC/Cu3YvkF3gekLAb/j9Cie/feHmSnbuZ2VEr6zUt10yaH4hPejCOw
FYDZb0I8xxxWJZ6BbpLWDqeD0Oiss8UnDhha/iKvodA9LIG0T
VRlScvGvuzClGYkc7UIWIoARvdxp46YlGMu4mWGeVkNcrxXnmUkdKyNqGjAGJoK/
Private-MAC: d288fffe72914eb62ed60a7e50fd8ce775

The key file (*.ppk) looks something similar to this

This is because the private key must be compatible with SshNet, so we have to convert the private key using PuTTY key Generator (the same app we used to create the certificate). Once opened in PuTTY, just export your file key to OpenSSH and use this new ppk file instead of the previous one.

exportosk

If you open the new file you will see that the key file now begins with

—–BEGIN RSA PRIVATE KEY—–

And ends with

—–END RSA PRIVATE KEY—–

Finally, this is a valid SSH private key and it works like a charm ;)

Hope you enjoy it!

Upgrade SQL Server Assemblies .NET version

(This is a personal post, just a reminder)

note-to-self

Damn! :/

Each time a new version of .NET Framework is installed (or upgraded) in our SQL Server I need to update some assemblies we’re using in some projects.

Related posts

HowTo: Send Notifications from SQL Server using SignalR

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)

The issue

Executing the send message to hub stored procedure throws an exception:

EXEC clr.SendMessageToHub 'http://THESERVER:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

‘Could not load file or assembly ‘System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC.

Solution (it works for me):

1 – Remove clr stored procedure

2 – Remove assemblies (removing Falcon_SqlProxy should remove other dependences, if not, remove manually)

3 – Go to the GAC folder.: C:\Windows\Microsoft.NET\assembly\GAC_MSIL

4 – Find the assemblies (in this case only ‘System.Runtime.Serialization.dll and System.Neh.Http.dll were needed) and copy them to the SQL Assemblies folder (C:\Falcon\SqlServerAssemblies)

5 – Create assembly Falcon_SqlProxy again:

CREATE ASSEMBLY Falcon_SqlProxy from
 'C:\Falcon\SqlServerAssemblies\Falcon.NotificationsClient.SqlProxy.dll'
WITH PERMISSION_SET = UNSAFE

6 – Create clr stored procedure again:

CREATE PROCEDURE [clr].[SendMessageToHub]
 @url [NCHAR](125),
 @hubName [NCHAR](75),
 @hubMethod [NCHAR](75),
 @message [NVARCHAR](MAX)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].[SendMessageToHub]
GO

7 – Check if SP is working:

DECLARE @jsonmsg VARCHAR(MAX)
SET @jsonmsg = '{"message": "GenerateProposalProgress", "description": "Resolving Portfolios", "userid": "' +
 REPLACE(ORIGINAL_LOGIN(), '\', '\\') +
 '", "formName" : "Falcon.Client.Strategies.Proposals.Forms.ObjectSelectorForm", "value" : '
 + CAST(5 AS VARCHAR(MAX)) +'}'
EXEC clr.SendMessageToHub 'http://elsuper:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

Hope this helps to my future me :P


Note: Today (12/07/2017) after moving some databases to other disks I had to do it again, so I’ve discovered other related issue.

It seems sometimes the USER SID changes after a backup/restore and throws an error:

The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘Falcon’. You should correct this situation by resetting the owner of database ‘Falcon’ using the ALTER AUTHORIZATION statement.

So, before create the assemblies you (I mean me) you’ll need to change the database owner to sa.


EXEC sp_changedbowner 'sa'

ALTER DATABASE [database_name] SET TRUSTWORTHY ON
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO