Automated SQL Data Warehouse project done in 3 weeks with minimal prior experience
Data warehouse implementation - easier than expected
As a junior BI consultant with 10 months of work experience, I was given the task to build a complete data warehouse, data marketplace and populate it with data.
Considering that I had little experience in the field, this task promised to be a time and energy consuming challenge. I did have some help from a senior colleague who monitored and supported me during the process, but this did not change the fact that I had to understand and complete a complex process without any prior experience.
All I had to do was:
- understand the steps of the process,
- define the structure of the data warehouse, the objects to be fetched from the source system,
- and put them into a simple Excel-based configuration file.
By setting up the configuration file properly, the creation of the new data warehouse could be almost completely automated.
The parameters I needed to set up the configuration were the availability of the different source systems, the objects to be fetched from them, any changes to the objects (e.g. columns of a table, data type of a field, name), how they are loaded, how they are historized and other optional parameters, the steps of the ETL processes that load the data, and the availability of the target system. Having all this in one place allowed me to quickly check and modify the structure if changes were needed.
After definig the metadata, the Rapid Platform was able to automatically create the different database layers (stage, data warehouse, data marketplace), by running a PowerShell command, on the specified target server, which in my case was an MS SQL database server.
The benefit of the solution lies not only in the ease of use for Business Intelligence developers, but also in the significant time saving (and therefore cost reduction) that it brings. The time and energy freed up through the automations could be spent on deeper analysis of the task and the data, and through that on tasks that actually create business value, such as designing and refining the right data model and subsequently producing analytical reports on those models.
A few words about the environment
In a Microsoft stack-based development environment, a data warehouse automation solution can be easily implemented with the Abylon Rapid Platform.
Quick overview of process of generating a data warehouse and the software used during the project:
- Using MS Visual Studio, the configuration files for the data structure, the server connections and the project environments, can be easily accessed and modified. There is also a configuration file that stores the connections and environments.
- After loading a specific tool from the Rapid platform, I used a simple PowerShell command to generate the files defining the STAGE, DWH layers as part of the solution.
- After creating the build in Visual Studio, running another Rapid Platform command, the given data model structure is deployed to the given SQL server (if the connections are defined correctly). I then created the different OLAP models, which I could build in Visual Studio as well as in Tabular editor. I could then deploy this to an Analysis Services server using an separate command.
- After that, I only needed to transfer the data. The “how” I already defined in the configuration file. The stored procedures for transferring data from the STAGE layer to the DWH layer were automatically generated. The invocation of these stored procedures can be simply added as “execution plan items” to the configuration file as part of various “Execution Plans” that can be easily invoked and scheduled by a SQL job. The manually created stored procedures that fill the DM layer can also be added to the part defining the ETL process inside the configuration file and then can be run as part of the “Execution Plan” when runing the SQL job. This allows the loading of the entire data warehouse with a single job execution.
This created an automatically updating data warehouse with the necessary data historization available, as well as the OLAP cubes. After connecting to the latter I could starts generating the various analytical reports.
Despite the fact that this was the first time I was using the Rapid Platform, and generally speaking I did not have much DWH experience, thanks to the flexibility and simplicity of the platform, the whole process, including the subsequent improvements required by the client, did not take more than 2-3 weeks.
The main benefits I have experienced
In addition to taking a huge burden off the shoulders of the developers, data warehouse automation is also a significant benefit for the customer, as it can greatly reduce the time and effort spent on an DWH implementation.
The benefits of automatisations
- Massive time and energy saving
- Reducing the number of human errors
- No SSIS knowledge required, automatic mapping
Ease of use
- Even a beginner with little experience in data warehouse building can quickly understand the user-friendly interface and find it easy to navigate.
- It helps to understand complex processes in their entirety
- Test explorer supports development, debugging
Flexibility
- The Rapid Analytics platform is highly flexible. It can be quickly optimised for specific customer needs with a few minor changes.
More on the Abylon Rapid Platform
f you’re interested in our data warehouse automation solution built on the Abylon Rapid Platform, you may also find the following post, customer interview and case study interesting:
Author of the post
Zsombor Tamásovics - Junior BI consultant at Abylon Consulting Linkedin Profile
Found this post interesting?
Subscribe to our newsletter to receive updates of similar post and news!
