An Analytics Mini-Methodology

Assumption: You are building a business-area focused OLAP cube.

Requirements Phase:

1. Identify the processing requirements for this decision-support system. What analysis do you need to do? Are trend reporting, forecasting, and so on necessary? These can often be represented in Use Case form (via UML).

   1. Ask each user what business decision questions he needs to have answered .

   2. Ask each user how often he needs these questions answered and exactly when the questions must be answered.

   3. Ask each user how current the data must be to get accurate answers. This is data latency.

2. Identify the data needed to fulfill these requirements. What must be touched to provide answers? The best way to capture this type of information is a logical data model. Even a rough model is better than none at all. It is here that you focus on the "facts" that need to be analyzed .

3. Identify all possible hierarchies and level representations (aggregations). This is the "how data is used" question. Most users will tell you that they want to see product data in the product hierarchy structure that has already been set up (such as product family, product groups, and so on).

4. Identify the time hierarchies that the users need. Because time is usually implicit, it will just need to be clarified in terms of levels of aggregation ( years , quarters , months, weeks, days, and so on) and if they need fiscal versus Gregorian calendar, both, or others.

5. Understand the data that each user can view from a security point of view.

Design Phase:

1. Analyze which datasources are needed to fulfill the requirements. See if dimensions or OLAP cubes already in existence can be shared.

2. Understand what data transformations need to be done to the source data to provide it to the OLAP world. This might include pre-aggregation, reformatting, data integrity verifications, and so on.

3. Translate these requirements into an OLAP model design:

   1. Translate to MOLAP if your datasources are not going to be leveraged at all and you will be taking full advantage of OLAP storage.

   2. Translate to ROLAP if you are going to leverage off of an existing relational design and storage.

   3. Translate to HOLAP if you are going to partially utilize the source data storage and partially utilize OLAP storage. This is the most frequently used approach.

Construction Phase:

1. Implement data extract/transformation logic (via SQL, DTS, or other methods ).

2. Create the data sources to be used.

3. Create the dimensions.

4. Create the cube.

5. Select data measures (the data "facts") for the cube.

6. Design the storage and aggregations.

7. Process the cube. This brings the data into the OLAP environment.

8. Verify data integrity.

Implementation Phase:

1. Define the security roles in the cube.

2. Train the user to use the system.

3. Process the data into the OLAP environment (from production datasources).

4. Verify data integrity.

5. Turn the OLAP cube loose to the user.

Maintenance Phase:

1. Evaluate access optimization in the OLAP cube via usage analysis.

2. Do data mining discovery if desired.

3. Make schema changes/enhancements as necessary.