AIM Business Driven Data Solutions Logo

Our Planning, Developing, and Integration Methodology for Business Intelligence and Analytics
Microsoft tools such as Power BI, Excel, SSIS, SSRS, Azure, Cloud

AIM Data Methodology

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 and applications.

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 by providing 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 implementations.

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 approach. 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 enterprise.

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 relationship modeling.

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 intelligence initiative:

  1. Information Clarity, Cleanliness, and Usability
  2. 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.