How to connect Microsoft SQL-Server and SSAS to Jethro

Traditionally, Jethro lacked an interface between Microsoft tools such as Excel, SQL Server, and SQL Analysis Services; making the task ever more challenging when considering a migration out of these systems and into a Big Data BI Acceleration database such as Jethro. However, with the usage of the MSDASQL driver, which replaces the usual OLE DB method, you can make direct connections to an underlying Jethro database and even power existing SSAS cubes from it.

In this document we will illustrate the pre-requisites, setup, and deployment of an SSAS cube using Jethro.

Prerequisites

There are two major caveats when beginning this venture:

1. You must be an administrator, or have an administrator, willing to enable Ad Hoc Distributed Queries within SQL Server. The commands will be illustrated in subsequent sections below. 
2. The Jethro ODBC driver must be installed and configured on the machine hosting SQL Server. Very important because MSDASQL queries won’t work without being able to access the system DSN for Jethro. Instructions can be found here.

SQL Server Setup

If the caveats mentioned do not impact you then you make move into the first part of the setup, which, was hinted at in caveat #2 above. By default, Ad Hoc Distributed Queries are not enabled within SQL Server, so you must do this with the following commands:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO 

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

These commands will display something like the following within SQL Management Studio:

Please note, you must have administrative rights on the SQL Server to execute the sp_configure procedure, as mentioned in caveat #2.

Querying Jethro

Once you have configured your SQL Server for Ad Hoc Distributed Querying you are ready to pull data directly from Jethro! This can easily be tested by creating an OPENROWSET query against an existing Jethro table (https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-2017).

For example, let’s assume the following about our Jethro environment that our DSN is pointing to:

Then a query would look like the following:

SELECT * 
FROM OPENROWSET (
		'MSDASQL', 
		'DSN=JethroODBC; DATABASE=jethroprod; UID=jethro; PWD=jethro', 
		'SELECT * FROM def_schema.fact_sales'
);

Results:

Analysis Services

Now that we’ve discussed setup and examples of querying Jethro with MSDASQL and OPENROWSET we are ready to build an SSAS cube. This section will focus on creating the DataSourceView from OPENROWSET queries and then highlight any additional settings that may need to be changed within the Visual Studio SSAS designer. At the very minimum, by the end of the steps outlined below, you will be able to import data into any type of cube storage, MOLAP, etc.

First, let’s look at the DSV…

From the image above, you can see we have a very simple cube, one fact and two dimensions, all sourced from Named Queries. A Named Query within SSAS is simply a T-SQL statement executed on the DSV versus referencing a table or even a view. The image illustrates the named query used behind the table named Jethro_fact_sales.

Once you have your DSV built you can generate a cube from this (or construct it manually) and define any specifics within your dimensions. Finally, after this, you are ready to process and deploy – pulling the data directly from Jethro.