How to build a data warehouse? I used to work with SSIS on Microsoft SQL Server to generate all my pipelines. This was fast and convenient and a lot of people in the industry are working like that. Define a source, do some transformations and load them to a target table. Very easy, very fast.
But what can we do, if we don’t want to work over and over again on transformations which are looking always the same? Dan Linstedt pointed me to the Capability Maturity Model to find out how mature a data warehouse is. Translated to BI this would mean:
| Maturity Level | Name | Definition |
| 1 | Initial | Process unpredictable
Process poorly controlled |
| 2 | Managed | Implementation Standards
Pattern Based Architecture |
| 3 | Defined | Defined Development Process
Defined Deployment Process
Defined Other Processes |
| 4 | Measured | Estimates and Actuals Captured
Measured Lead Times
Measured Complexity
Measured Defects
|
| 5 | Optimized | Automation Tools
Parallel Teams
Rapid Delivery |
After some work I reached level 3 easily. But my automating efforts to deploy fast my data flow according to my pattern, turned out to be very time consuming and difficult. My ambitions where very high.
Many people have been trying to accomplish that too and some of them have sold there software what they found out about automating things. All are building a meta data repository and deriving from that what needs to be done to get to a desired result.
So I did an extensive research on that topic to find out, that such a meta data tool doesn’t exist and my own definitions were never finished and I added more and more „exceptions“ to that. Why should I spend time researching it while others have already solved it?
So I turned to commercial tools and tested some. Finally I decided to go with WhereScape. When we bought it, it had only a couple of supported databases and wizards helping to get the patterns right. But it is turning more and more into a „target-less“ meta data repository which can talk to any database and any data structure through templates. In the following posts I’m going to share some of my findings, how to process data fast.
Nov 11 2017
Business Intelligence Automation
How to build a data warehouse? I used to work with SSIS on Microsoft SQL Server to generate all my pipelines. This was fast and convenient and a lot of people in the industry are working like that. Define a source, do some transformations and load them to a target table. Very easy, very fast.
But what can we do, if we don’t want to work over and over again on transformations which are looking always the same? Dan Linstedt pointed me to the Capability Maturity Model to find out how mature a data warehouse is. Translated to BI this would mean:
Process poorly controlled
Pattern Based Architecture
Defined Deployment Process
Defined Other Processes
Measured Lead Times
Measured Complexity
Measured Defects
Parallel Teams
Rapid Delivery
After some work I reached level 3 easily. But my automating efforts to deploy fast my data flow according to my pattern, turned out to be very time consuming and difficult. My ambitions where very high.
Many people have been trying to accomplish that too and some of them have sold there software what they found out about automating things. All are building a meta data repository and deriving from that what needs to be done to get to a desired result.
So I did an extensive research on that topic to find out, that such a meta data tool doesn’t exist and my own definitions were never finished and I added more and more „exceptions“ to that. Why should I spend time researching it while others have already solved it?
So I turned to commercial tools and tested some. Finally I decided to go with WhereScape. When we bought it, it had only a couple of supported databases and wizards helping to get the patterns right. But it is turning more and more into a „target-less“ meta data repository which can talk to any database and any data structure through templates. In the following posts I’m going to share some of my findings, how to process data fast.
By ecki • Business Intelligence 0