Process of developing the dimensional model involves four
phases
- Designing: In this process we generally identify
the business or a business process, understand what the requirements are, which
helps us to identify and define the grain. Once the grain is defines we move
towards defining the dimensions and identifying the measurement
- Testing: In this phase SQL or other query
languages are used to query the model created and see whether it adheres to the
initial design requirements or not
- Transforming: Once developed and rigorously
tested, the data model is transformed to a generic level so that other query
languages can also be used to over it.
- Deploying: This process deploy the data model created
into production environment
The focus of this blog will be
towards the designing phase:
Design Phase
There are four major steps involved
Step 1: Identify Business and business
process
Step2: Identify the grain
Step3: Identify the dimension
Step4: Identify the measure
Step1: identify the business and Business
process: Select the business process for which we need to develop the
model. This business process must not always correspond to a business
department. For example Operations department in a Consumer lending performs various
business process such as Welcome calling, File management, Tele verification,
Home Visits, Collection Calling where each process can be well established as a
business process within a business department.
Along
with identifying the business process we need to collect the following metadata
such as
·
Business owner
·
Business requirements
·
Business inputs and dependencies
·
Business process output
·
Definitions of business terms used
Step2: identify the grain: The grain is
the most atomic information that defines the business process. This helps understand
to what detailed level the user wants to store data. Let’s assume Welcome
calling process as one business process from the Operations department. Now in
this, the calling agent places the Welcome call the customers to whom the
company has lend the money, this way company greets them for being on board.
Now for this process the input would be the calling list to call every day with
name and numbers of client and the output would be the list of numbers that are
called.
To
measure any statistics in this process we need grain level information of every
call made. To explain better we would like to know at the end of the day
·
How many calls were made?
·
How many agents were present that day?
·
How many call were allocated to each agent?
·
How many call did each agent made that day?
Thus
to answer these basic question we need every single call made in day to be stored
in the database.
Step3: identify the dimension:
Dimensions are created in a way that fall in syc with the grain. Extending the
above example of Welcome calling process, the probable dimensions would be
·
Calling List with attributes such as Contract_number,
Client Name, Phone_1, Phone_2, Date of Loan, Amount of Loan, and Commodity financed etc…
·
Calling Agent : Agent_Name, Agent_ID, Date of
Joining, Current status of employment
·
Communication Dimension: Call_ID,
Calling_number, Calling result, notes
Step4 Identify the fact:
This process helps determining what we need to measure in this process. For
example in the Welcome calling process we need to measure how much time is
spent in each call and how much time is spent in moving to next call. The fact table
would contain the following information such as Call_ID, Agetnt_ID, Contract_number,
Clinet_ID, Calling_Begin_date_time, Clling_End_date_time, Calling duration (End
time – begin time), Waiting time (Call_begin_time – last_call_end_time).
Such a table will help us in understanding the Average
calling time and waiting time by agent, by client or by call result.
In my perspective following this for step methodology gives
a developer a clear idea as to how to look to a process, understand the
dependencies, the micro level of information required and the process outputs. But
since this process once developed would be used by business personals who may
not be well versed with the technical definition so we need to develop the
metatdata along with the process development to make process more
transformable.
References:
Regards,
Aman
