‘Sanctify yourself’ is an unforgettable eighties pop song about self-empowerment. Fast-forward 30 years or so to today, and the song hasn’t lost any of its original musical appeal, although thematically we should add digitalization into the mix. Indeed, today we also want to empower our customers and business partners. Increasingly this requires the integration and dissemination of data from different parts of the company, often in a near real time fashion. And so, in homage to the original song of the Simple minds, the title of this post became Restify your(self) Data Warehouse.
At Sparkle, we focus on Data Warehouse automation, based on VaultSpeed for the generation of DataVault scripts (1), Attunity (2) for implementing near-real time data replication and the Microsoft technology stack for Data Warehouse and BI (3). At a customer of ours, we implemented such a solution which synchronized the Data Warehouse every five minutes all the way from the operational applications, through the staging, the Data Warehouse and up-to the data mart layer. Eager about the future potential of this architecture, the customer wanted to find out if and how external business partners could also access the Data Warehouse, by consuming REST APIs (4).
Imagine for example a B2B partner company, a reseller, which has ordered some products from you, and it wants to integrate the status of their shipment in their internal logistical process by consuming your API. Above you see a screen-shot for the result of such an API call, showing the first few lines.
As this was a proof-of-concept, we intentionally focused the scope on the translation of the SQL ‘arena’ to the API REST one (5). As it turns out, we found out the Microsoft technology provides low-code solutions built on readily available libraries to do just what we were looking for. What we were looking for was an adapter to communicate with our facts and dimension stored in SQL Server and simply re-use the defined table structure and data-types. Actually, the Entity framework available for Visual Studio ASP.NET is an ideal match for this task: it allows to import the table structure, cast it as a class and defines methods to perform a GET, POST or DELETE.
There was not much ‘coding’ to do. Use the entity framework to import the tables or views from SQL server into Visual Studio as classes. Generate an ‘Entity Controller’ per class, and get a default GET method. Comment out or remove the default POST and DELETE methods (we did, as we did not need them). Create additional GET methods for a class if it is based on a composite key or a non-primary key. Compile your coding and test it locally and then create a package and then deploy it to the IIS web server. Et voilà, you’re set-up in no time! The screen-shot below shows in yellow which parts needed minor rework. That being said, a production ready architecture would also need additional security components and would need to integrate with your current web infrastructure and middleware.
I am not a seasoned developer, but the Microsoft eco-system has a rich online documentation (6). So, as it turns out, it is not very complicated to ‘restify’ your Microsoft Enterprise Data Warehouse (7) and even further increase its business value.
(1) For a first introduction to the benefits and specifics of Datavault, you can read more at https://www.theagileinformationfactory.com/expertise/data-vault-2-0/
(2) We use Attunity for the Change Data Capture (CDC), to replicate all changes from the operational system to the Data Warehouse in near real-time fashion, without negatively impact the performance of these operational system. More information is available from the vendor web-site https://www.attunity.com/solutions/database/enterprise-data-replication/
(3) The Microsoft BI and Data Warehouse stack is composed of the SQL Server, SSAS (SQL Server Analysis Services, SSIS (SQL Server Integration Services) for ETL, complemented with Power BI for self-service BI and SSRS (SQL Server Reporting Services) for pixel-perfect reports.
(4) REST stands for RESTfull API, and is considered the de facto standard for internet application connectivity as opposed to web-services based on SOAP. API is the acronym for Application Programming Interface.
(5) As such, we did not implement security aspects for authentication and authorization. As there was no other activity in the DMZ (DeMilitarized Zone) besides static web pages and FTP communication, a POC for authentication and authorization, taking into account the company standards would have taken more time then what was relevant right now.
(6) Some of the references I used myself are using the ASP.NET web API, using the entity framework, dealing with foreign keys in entity framework and dealing with composite keys in entity framework
(7) Do you have specific experiences with this architecture or have you implemented other ones? Don’t hesitate to share your experiences in the comment. Sharing is caring!