Establishing the Analysis and Modeling team
This team will include:
- Business Domain Experts- The people who have been in the business world for some years
- Business Systems Analysts- People who can connect the systems and Business World
- MIS representative- People who have been responsible for MIS and Reports generation and they know on what information we are currently generating and from which system
- Dimensional modeling specialist- These people integrate expertise of the other roles and do (or help them do) the dimensional modeling
- ETL specialists- These people will help in mapping the source systems and also to do the high level scoping of the ETL
Dimensional Modeling
NOTE- You can refer Dimensional Modeling concepts for the subject matter. The topics covered under that chapter are:
- Dimension Modeling Components
- Dimensional Modeling Schemas
- Dimensional Modeling vs OLTP modeling
- Special Situations in Dimensional Modeling
- Foundation Facts and Dimensions
Dimensional Modeling Process is covered in a separate chapter, which includes the following deliverable
- Building Data Marts+ Business Themes Matrix
- Building Data-Marts + Business themes matrix
- Building Data Marts+ Dimensions + facts matrix
- Building Marts + Fact Table Grain matrix
- Building Facts+ Derived Facts matrix
- Building Dimensions + Attributes+ Derived Attributes matrix
- Building Dimension + Attributes + Facts + Source table matrix
Identify Aggregates in the logical dimensional model
Once your detailed dimensional model is complete, the assumption here is that the dimensional model till now is dealing with the most granular data. Therefore, now is the time, we get onto creating aggregate dimensional model for speedy response time from the Data Warehouse. Technically speaking, the aggregate dimensional model has the summary data. Therefore, when a query is looking for summary data, instead of dynamic additivity operations, the pre-cooked aggregate data is made available to the query. You can also refer Minimize Aggregates when using OLAP
User Review & Acceptance of dimensional model
Dimensional Modeling as a process should be including the business users from the day one. The ideal situation would be when the entire dimensional modeling is driven by business system analyst with the support from IT. Dimensional modeling is pretty much a business subject (just like functional specs and logical data model in an OLTP project).
Therefore, as the dimensional model for user acceptance, it should not be difficult to sell. The whole documentation of Dimensional model should be in business friendly language
Analyze the Data Sources
The detailed level extraction design is going to be done during the design phase. At this stage, a high level mapping will be done which will tell us on which data elements will be sourced from which source-systems, and what is the level of complexity
Identify Candidate Data Sources
Knowing the data elements and data entities, which you have to extract from the source system, you can provide the first level of list of the systems which could be providing that data. Generally at this stage you will be discarding the obvious exclusions. For example, you will not take the MS Access based system used by marketing function as the source of customer master.
Analysis of the robustness and quality of the data in the source system
In this stage you will be picking up each of these candidate sources and do a high level review of the quality of the data and robustness/stability of that system. One does not need to do a detailed data mapping and assessment here. However, some of the doubtful areas can be taken through little more in-depth study (especially in case you have two close choices).
Estimate the high level effort for extraction and transformation
This is the stage when you do an analysis of the level of complexity involved in extracting and transforming the data. This will firstly allow you to review the efficacy and economics of your choice of the source system. Secondly, it will provide the cost estimates for the ETL design phase.
Decide about the high-level architecture
This is the stage, when you take a call on what will be your architecture scenario around which you will be building your BI environment and which end-user tools will be sitting on top of this core architecture. You can refer BI components and BI architecture scenarios.
Share the analyze phase with BI vendor
This is not a step but done throughout the analyze phase. We assume that you have identified or at least short-listed the BI vendor. If you have already selected the BI vendor, the representative should be an integral part of your analyze team. In some cases the consulting partner (or a sister organization) may itself be driving the analyze phase.
Project re-scoping and re-estimation
Analyze phase will be throwing up the new and more detailed information to enable a more realistic view of the project economics. Please refer Data Warehouse requirements assessment
Final Sign-off on the analyze phase
After the Data Warehouse project re-scoping, re-estimation, the stakeholder review is closed with a sign-off and confirmation for funding for design phase.
|