The initial purchase of Dimensional Insight software traditionally includes consulting time to implement an initial project or application that satisfies a pressing need for information. Such a project is limited in scope, but broad enough to deliver real value. This effort proves the software's worth, helps with user adoption, demonstrates the possibilities to solve other problems, and establishes a pattern for development, validation, testing, and delivery of a live, self sustaining system for data delivery to end-users.
This topic describes the high-level steps traditionally followed by Dimensional Insight consultants to help customers turn their mountains of data into useful information—ETL in a nutshell.
Customer Requirements
The consultant needs to become familiar with your data and understand your vision for end-user interaction with that data. For example:
- What are the data sets? What are the applications involved?
- How is their data arriving? Collected nightly as a text file or through ODBC connection?
- What is required to validate?
- What types of questions do you have for your data that you have not been able to answer with current tools?
- Who are the audiences—corner office, salesperson approaching a stop, analyst deciding on inventory requirements?
Knowing how you want the data to appear in the end-user applications helps the consultant best configure the Dimensional Insight ETL tools to manipulate the incoming raw data.
Raw Data Collection
This stage is the E (extract) of ETL. Dimensional Insight tools can draw in data from a variety of sources and prepare that data to be run through the transformation tools. Developers typically:
- Develop an initial handshake with your data systems to get a dump of data
- Analyze the raw data to understand how best to manipulate it before loading it into the data transformation tools
- Codify a long-term connection between your data systems and the data extraction tools
The ultimate goal is the smooth transition of data housed in your databases into the DI project on a regular schedule.
Data Manipulation
This stage is the T (transform) of ETL. After the raw data is loaded, the Dimensional Insight tools can now begin the work of transforming the raw data into more useful forms. There are several ways to manipulate and transform the raw data, such as:
- Using lookups to pull in auxiliary data
- Joining tables from different data sets
- Validating internal data
- Calculating control counts and totals
- Squashing and summarizing
- Building time series patterns
The end result is data primed and positioned to be used with DI clients.
Data Presentation
This stage is the L (load) of ETL. After the data is prepared, the work of building the end-user presentations begins. This can include:
- Building reports and scheduling them for delivery
- Designing a portal with different functional areas
- Constructing portal pages with intuitive summary dashboards
- Giving users easy access to dive down to detailed transactions
- Configuring security so user access is finely controlled
The objective is to deliver information to your end users that answers their questions, but is also organized so it is scalable and maintainable.
Iteration
Working through each stage in a project is a learning experience for all involved. The general best practice is to iterate your work through the following stages:
- Development—Develop new content and maintain existing projects
- Test—Validate the data, check performance, and provide acceptance
- Production—Deploy accepted content to end users
Use the development and test stages to iterate your ETL steps. This allows you to thoroughly test and accept newly developed content before you present the final production environment to end users.
See also More Information on Clients.