Make the Data Work for You with New Data Warehouse Automation Technology
Make the Data Work for You with New Data Warehouse Automation Technology
by Erez Zeevi,Executive Vice President, Research and Development and Global Technical Operations, Attunity.
It’s no secret that Business Intelligence projects are only as effective as the data on which they are built. BI specialists and data/ETL architects must harness the “three Vs” of data – volume, variety and velocity – with analytics in order to deliver value to the business.
This is getting harder by the day as the three Vs increase. The typical process for creating a data model, setting up a data warehouse and populating it, has a well-earned reputation for being complex, time-consuming, expensive and prone to error.
But BI and data warehouse teams are starting to take advantage of new automation tools that streamline all this by eliminating manual coding, speeding EDW setup and reducing risk of error. The result is a broader-based, more easily managed, more flexible analytics foundation that is purpose built for the business.
Let’s examine what this means for the three phases of BI project preparation: data modeling, data warehouse creation/updates and the extract, transform and load (ETL) process.
Data Modeling
BI specialists are often tempted to short-cut the creation of a formal data model, and just start building reports based on available source systems. But a formal data model is required to support business decisions with well-structured analysis. KPIs and metadata must correlate to your business process, rules and standards in order for the resulting reports, visualization and documentation to meet the needs of the business. Simply adopting the complex structures of commonly used ERP systems, with their thousands of tables and ill-defined fields, will prevent you from making clear-eyed decisions about your customers, competitors and operations.
In short, you need your data to fit your model, and not vice versa. Once you have the right data model in place, you have a springboard to create a Data Warehouse and Data Marts that use the right data from all relevant sources.
Data Warehouse Creation and Updates
Years of trial and error have proven the effectiveness of a normalized data warehouse that integrates various sources into a “single version of truth,” storing all necessary organizational data in a consistent and non-redundant form. We typically design data warehouses with techniques such as third normal form (3NF) and data vaults to reduce duplication, maintain referential integrity, etc. The data warehouse often supports an “access layer” of data marts that feed datasets to BI teams for analysis with graphical tools like Tableau and Qlik.
Skilled enterprise developers typically spend a lot of time setting up the data warehouse and data marts, and continuously updating them to support new BI user activities. Successful data warehousing teams must stay agile, forever adjusting the underlying data model to evolving business /requirements, data sources and analytics methods. Today they struggle to do so within staff and budget constraints.
Attunity Compose software is one tool that re-defines the economics of implementing data models. Compose empowers you to design and refine the data model with an intuitive drag-and-drop interface that generates the table structures and other model components of your data warehouse and data marts rather than manual coding. So you need less time and training to put the right foundation in place for your BI teams.
Extract, Transform and Load (ETL)
Integrating multi-sourced data into a useable format in the data warehouse is by far the biggest bottleneck in BI preparation. The essential work of moving data from source to EDW target, then transforming it in accordance with defined rules is relatively basic. But the coding required to enact these processes drains the time of skilled developers and raises the risk of error. The scope of BI projects, along with data volume, variety and velocity, all are increasing faster than IT staff or budgets, straining resources and leading to failure.
In sum, ETL also is ripe for automation. We believe successful enterprises will deploy intuitive software to set up and execute all aspects of ETL (moves, technical transformations and business rule implementation) based on the underlying data model that integrates their data warehouse, data marts and source systems in a consistent and reliable structure. IT personnel can apply the business rules they defined in their model one time, then automate the ETL generation so that transformations will adapt to subsequent data warehouse and data mart changes.
Our industry is built on the power of algorithms to improve how we organize information more effectively for business advantage. The intersection of data growth, business demand and IT budgets all makes data warehousing one of the most compelling opportunities for automation that we have seen in a generation. We at Attunity are excited to work with enterprises that capitalize on this opportunity and shift their talented workers from drudgery to innovation that enables real business results
Sponsored by Attunity.