When referring to a data warehouse, data is transformed through stages of usefulness.
In the first stage of usefulness, raw data is collected from source systems and applications.
This raw data is of little use to the business except to the users of these specific systems
Information is the second stage of usefulness.
Once business intelligence services extract raw data from the source systems (ERP, Flat Files,
XML, Applications) and transforms the data according to the defined business rules (processes that are
useful to the business as a whole), the transformed data is scheduled to be loaded into a specialized
reporting environment called a data warehouse. This transformed data is now useful to the entire
organization and we call this kind of data information.
Distribution is the third stage of usefulness
However, this information is not useful if it is not distributed to the person, people, and
departments responsible for the performance and maintenance of the related business processes and
rules. Delivering the information to the appropriate business roles facilitates business users
with role specific information useful to their specific responsibilities and individual decision making.
Once business intelligence services
deliver the information to the appropriate business role, we need to make sure that this information
was also delivered in a timely manner.
The fourth stage of usefulness is Timely manner.
If the information is received by the proper person, people, and departments, but it took 3 weeks for
the information to be received, most likely, the information is out dated and not very useful to the
business. In the modern business world, the term "timely manner" may actually be in the last 2 hours,
or every morning with three updates daily.
Finally, the fifth stage of usefulness is making the information actionable
highlights about who to contact, what department(s) is/are under-performing, and hyper-links to
related information enabling further investigation and ad-hoc analysis allowing answers to question
types of who, what, where, when, and why.
In order to ensure your data is truly useful by being distributed to the proper business roles, in
a timely manner, and in an actionable format, AIM leverages ETL methods and tools that
represent our intellectual property that has been accumulated through past projects and
Features of the tool set include:
the eBI (3.0) ETL Framework, an eBI Environment Map,
an eBI Server Configuration diagram, and a questions for decision makers worksheet.
These toolbox features are described in the links below.
Other sample documents including dimension best practices, SSAS cube finishing, and
a sample data model (eLogging 3.0), are provided in the links below:
The AIM tool set will help speed the planning, requirements gathering, development of
the ETL packages, assist in logging, and management and maintenance of the business intelligence services.
Our methodology for implementing these business intelligence services is described below.
AIM follows a Kimball
Our data warehouse methodology is a bottom-to-top delivery approach designed to fulfill
the reporting and analytical needs of the entire organization.
In the bottom-to-top delivery approach, the data warehouse is built in phases, with each phase
comprising the data respective to one or more business processes. The data that these processes
generate then become the subject of each respective build phase of the data warehouse. These data
warehouse building blocks divide and conquer what otherwise would be risky as a single "Goliath."
This idea of bottom-to-top delivery not only applies to the building blocks of a data warehouse,
but it also refers to the technical layers of business intelligence required to deliver information.
These technical layers of business intelligence start at the bottom with the infrastructure layer
(storage and visualization), then the ETL and ELT data layer (data warehouse and bus, data marts, star schema),
and finally the reporting and analytical (presentation) layer, the top. This idea of the bottom-to-top
delivery can be seen in the diagram (below) titled bottom-to-top delivery. When considering performance
and reliability, planning and decision making needs to consider these separate technology layers
(from the bottom-to-top) to eliminate potential bottlenecks and maintain desired performance.
In both concepts of this bottom-to-top delivery approach, the most important
consideration is the data warehouse bus. The data warehouse bus ensures that starting
with one, or two business process data marts, will evolve into one "master",
data warehouse that represents one vision of the truth. The bus is developed simultaneously
with each phase ensuring that all of the data marts come together to form a consolidated
data warehouse. Data consistency is guaranteed by sourcing all data marts from the
"master" data warehouse. This sourcing of data marts from the data warehouse
is called the data warehouse bus.
Through proper maintenance of the data warehouse bus, throughout the phased building
blocks of the data warehouse, conformed dimensions may be maintained that allow users
the ability to compare data across multiple business processes and get a consistent view of the
Another advantage of the bottom-to-top delivery approach is speed in delivery.
By focusing on one, or two business processes at a time, improved turn around times
can be achieved. Comparing how long it takes stake owners of specific business processes
to achieve consensus on defining important data points (metrics) and the reference information that
describes these metrics derived from a particular process, versus how long it takes the entire
enterprise to do the same, demonstrates one way turn around times are improved.
One of the core deliverables of this methodology is the dimensional model.
Dimensional modeling is a logical technique for organizing the information in a
data warehouse that is very different from the techniques used in entity
This design technique, also called a star schema, is oriented around the end user
interaction and focuses on the data model being easily understood, rather than the
typical design factors that drive database normalization. In short, dimensional
modeling helps achieve the two primary goals of a data warehouse/business
- Information Clarity, Cleanliness, and Usability
- Fast Query Performance
Dimensional modeling puts the data into the concepts of measurements and context. Measurements,
often called facts, are usually numerical values that can be aggregated. Facts are surrounded by context
that describes the numeric or measurable event; this context is grouped together into logical subsets
called dimensions. Dimensions answer the questions similar to what happened, where, why, who was
involved and how was it done.
Our approach at AIM provides a framework for organizing all of the tasks and activities
required to implement a successful Data Warehouse and Business Intelligence Services.
In short, our framework's primary tenets are as follows:
- Business Focus
- Kimball Approach
- Bottom-to-Top Delivery
- Dimensional Modeling
AIM's overall approach to implementing Warehouse and Business Intelligence Services are
illustrated in the diagram below. This diagram describes the relationships between the project / program,
business requirements, design tasks (application, technical architecture, physical), product
selection and implementation, as well as dimensional modeling.