An (Azure) SQL solution that makes it easy to create a data warehouse.
In my work I am always looking to make things easier. And I’m not alone: within the BI team we like to look for solutions to speed up our work. Whenever we have new ideas to automate things or think of features that are useful, they are added in a continuously expanding solution. With this approach we also came up with a solution that all our customers with a data warehouse can use.
What is now called the Data warehouse Accelerator started with a completely different approach. In many projects, we performed repetitive work setting up and maintaining a data warehouse. Of course, there are great data warehouse solutions that make maintaining your warehouse easier, but for many customers this is too expensive or the solution is too big for the purpose. To automate repetitive work and to save time, we have developed the Datawarehouse Accelerator. And this saved time can now be used to, for example, make reporting in Power BI even more beautiful.
How do you build such a thing?
To give you an insight into our process, I will take you through our steps.
As soon as we have agreed with the customer on the functional side of the data warehouse, determined the sources and made these available for the data warehouse, we start setting up the data warehouse according to the following steps.
- Determining the dimensions (context, for example customers) and facts (tables with values, for example sales orders) and the calculations that must be done in this
- Create views that prepare data
- Create a table in which the data that the view retrieves and transforms can be stored
- Create a stored procedure with which we can map and automate the loading of the data from view to table
The various components in this process must all fit together exactly, so a mistake is easily made. In addition, a small adjustment, such as adding an extra field, means a change in 3 places (view, table and stored procedure). This means that small adjustments quickly take a lot of time.
Because these relatively simple actions become complex together, you may wonder whether this can be automated? With the Dutch motto: “liever lui dan moe”, we wanted to try this. After a lot of “dynamic” SQL and many headaches, our idea has become reality. In the early versions of the development, we were able to make the steps much easier. Steps 3 and 4 have been replaced by automating it. With the data warehouse accelerator, only the view needs to be adjusted and with 1 push of a button, both the table and the stored procedure are adjusted.
DWH Accelerator
We have now expanded the data warehouse accelerator with the a few features. But the simple way of maintaining and building the data warehouse has remained the same.
New features include:
- Automatically rebuilt indexes on data warehouse
- Logging to be able to follow the steps in refreshing the data warehouse
- Separation of data into schemes for securing and limiting access in the data warehouse
- Incremental refresh of data so that the data transfer can be faster and more often (only new data instead of continuous everything)
Speed up your process
With this we wanted to make life easier, not only for our customers, but also for ourselves. Maintenance is not limited anymore to technical people, but it is accessible to (almost) everyone. The connection of the Data Warehouse Accelerator with SQL Jobs (on-premise) or the Azure Data Factory (Cloud), sets up a complete data warehouse in no time from the moment data is available. Due to the easy design it is possible to use the DWH Accelerator with basic SQL knowledge.
Self-service BI
At this moment, the DWH Accelerator is used by our customers to make extensions to the data available for their reports. This is fully in line with the vision of our BI team: “Self-service BI”. Our goal is to enable our customers to generate action-oriented insights from their own data. This way they can serve themselves in the field of Business Intelligence.
We also help with the initial set-up of the data integrations and the data warehouse, and enable our customers to expand themselves. And if no one is present who can / wants to / has time for this, we will of course be happy to make the requested adjustments.
More information?
For more information about BI, Data warehousing or the DWH Accelerator, you can always email biteam@dynamicpeople.com or call +31 (0) 20 303 24 70