Data Doghouse - performance management, business intelligence, and data warehousing

Data Integration: Beyond ETL and Data Warehousing

Data integration suffers from an image problem. It has become synonymous with extract, transform and load (ETL). Likewise, ETL has been regarded as a data warehousing (DW) technology.  Both of these viewpoints do not reflect current capabilities and greatly inhibit enterprises in their attempt to integrate data to provide the information their business needs.

Because of this short-sightedness companies have lost opportunities to harness information as a corporate asset. It increases the cost of integrating data, encourages the creation of data silos and forces business people to spend an inordinate amount of time filling the information gaps themselves through data shadow systems or reconciling data.

>>> Continue to the rest of my article, Data Integration: Beyond ETL and Data Warehousing, on the Information Management website.

New Age Data Warehousing

Data warehousing (DW) and business intelligence (BI) have been evolving and getting more sophisticated over the years. As IT folks, consultants and analysts gain more experience, they often share those experiences when they work at other companies, publish articles or conduct training. By sharing their knowledge, they have helped to improve the overall intelligence of our industry. This has led to the creation of conventional wisdom about how to design, build and deploy DW and BI solutions. This wisdom is a great guide, especially for people who are just getting involved in implementations and don’t yet understand what they need to do.

But there is a dark side to conventional wisdom when people treat it like gospel. Too often, people blindly follow the usual advice without making sure that it actually applies to their particular situation. Sometimes you should challenge conventional wisdom.

>>> Read the rest of my article New Age Data Warehousing on the DM Review website.

Data warehouse architecture that's too narrowly focused

In my DM Review columns, “The Accidental Architecture” and “Recovering from the Accidental Architecture,” I discussed whether people understood the “why” of best practices, such as dimensional modeling and hub-and-spoke architecture. In “Don’t Stop at How, Learn Why,” I discussed the most common problems when people are too hub myopic, cloning their source systems or assuming the hub will support all reporting and analytic needs.

There are also dangers with being too spoke-centric (with data marts or cubes) and failing to establish the hub, or data warehouse (DW), as the enterprise data backbone.

>>> Read the rest of my article "Too Narrowly Focused" on the DM Review website.

Learn WHY you should design a data warehouse a certain way

People involved in business intelligence (BI) and data warehousing (DW) projects are very familiar with terms such as facts, dimensions, attributes, surrogate keys and slowly changing dimensions. But knowing the terms is not enough. Increasingly, there appears to be a significant disconnect between how BI or DW systems are actually built versus the best practices described in books and articles. Clients point out that the concepts in some books and articles are esoteric because they are not connected to real-life situations. They teach the how but not the why. Two best practices that people think they are implementing but often fall short of are dimensional modeling and the hub-and-spoke architecture.

When you don’t know why you should design a DW a certain way, it’s easy to make the common mistake of designing DW and BI systems to look like your source systems.

>>> Read the rest of my article Don't Stop at How, Learn Why on the DM Review website.

Start a Dialogue about Master Data Management

Just mentioning master data management (MDM) will cause businesspeople’s eyes to glaze over and most IT folks to run for the hills.

Why does MDM create puzzlement and apprehension? The principal reason is that they need an IT buzzword dictionary to interpret the rather lengthy and complicated definitions. Despite the inherent reluctance, a successful MDM program needs businesspeople to understand it, sponsor it and commit to being part of the solution, along with IT to design, build and deploy it. And before any of that happens, you need to have a two-way dialogue about it.

>>> Read the rest of my article Start a Dialogue about MDM on the DM Review website.

Recovering from the Accidental Architecture

In my article “The Accidental Architecture,” I discussed how enterprises have evolved their data warehousing (DW) and business intelligence (BI) projects without a planned architecture. This evolution was the result of developing tactical projects while chasing the latest technology proclaimed by analysts, columnists and the vendors offering these solutions.

The random walk that created this accidental architecture may have solved some immediate, tactical needs, but the expenditure of time, resources and budget didn’t deliver ROI. Even worse, each new project resulted in a new stovepipe of data and technology that required increasing resources to integrate. It also made business users more likely to develop data shadow systems to perform their own integration.

>>> Read the rest of my article Recovering from the Accidental Architecture on the DM Review website.

The Accidental Architecture

When you plan to build a house, you start with what you want and can afford. You decide on architectural style, size and number of rooms. Then you hire an architect to design the house and create a detailed blueprint. The architect gives you feedback on how the various rooms can fit together, what the building codes are and how to fit in the infrastructure such as wiring and plumbing. Most importantly, the architect advises you on what is really possible or practical given your location, house size, wishes, budget and timetable. And she gives you ideas for things you hadn’t thought of.

Had you skipped this step and gone straight to a builder with your wish list, you would likely go over budget and get a house not well sited on your lot, with poor aesthetics and limited usefulness.

Like the unfortunate house, many data warehouses are built without an architectural blueprint. Companies focus on individual data warehousing (DW)/business intelligence (BI) projects without seeing how they fit as part of an overall architecture. As a result, many DW/BI environments have become a collection of technology, product and data silos that are loosely connected and require an intensive commitment of resources to operate, upgrade, maintain and enhance.

Many businesspeople are frustrated with the state of their DW/BI environments, which take forever to enhance and are always a release away from becoming pervasive throughout the enterprise. They remember the investments of time, resources and budget, and they ask why they still have to use spreadsheets (i.e., data shadow systems or spreadmarts) as the superglue for reporting and analysis.

>>> Read the rest of my article The Accidental Architecture on the DM Review website.

The Song Remains the Same (Data Integration Framework)

In 2003, the inaugural column I wrote for DM Review discussed the Data Integration Framework (DIF). This approach is the foundation that I have used for more than 20 years to design, develop, deploy and maintain data integration solutions. Technology and products have changed, but as I’m always reminding my consulting clients, the basis for sound data integration remains. Always remember the following basics that comprise the DIF:

• Architectures that are used to stage data from data sources to information consumers;
• Processes that help gather, consolidate, transform, cleanse and aggregate data and metadata;
• Standards for ensuring consistency, accuracy, integrity and data quality;
• Resources and skills that are necessary to ensure success; and
• Tools for the creation, deployment and expansion of the framework.

Despite all the PR and sales presentations, tools are only a small piece of the framework. In fact, it is no coincidence that they are listed last on the DIF list.

>>> Read the rest of my article The Song Remains the Same on the DM Review website.

Hub and Spoke on the Wane?

Conventional wisdom states that you should build a data warehouse (DW) and then populate data marts from it. This data architecture is called a hub and spoke.

The central premise of the hub and spoke is that the heavy-duty data integration activities are completed as you move the data from source systems into the DW. Once these data integration tasks are done, you can propagate or "franchise" the data from the DW into the data marts. Data franchising takes the DW data and packages it via filtering, aggregation and transformations into data usable by business intelligence (BI) tools and understandable to business users (see Figure 1).

This approach works extremely well, but lately I am concerned because it appears this approach is being abandoned. I think this is happening because people have forgotten its fundamental aspects and why it works better than the alternatives. Due to advances in databases, storage, network and server capabilities, it is often more effective to deploy the hub and spoke all on the same database or instance rather than store the data mart "spokes" on separate servers and databases.

>>> Read the rest of my article Hub and Spoke on the Wane? on the DM Review website.

More Trends in Data Integration

My last column was about hyped trends. There are other industry trends that don't get all the hype but are happening quietly. These trends are taking hold because they either extend the business value of a company's data by leveraging their business intelligence (BI)/data warehousing (DW) investments or they are enabling more cost-effective delivery of information to businesses. Surprisingly, no software or hardware company directly benefits from some of the trends, which explains why they are happening without much buzz.

>>> Read the rest of my article More Trends in Data Integration on the DM Review site.