First, to understand how this had begun, let me ask a question - "How Data Warehousing (DWH) had started"?
Today, Organizations and Corporations have large amounts of data in their operational systems. But how do the senior management and executives make strategic decisions for the growth of the company? The strategic decisions are largely based on the analysis of various performance reports, customer trends, historic operational information reports etc., But, how are these reports generated? The answer is through a "Data Warehouse".
Ok, So how are these reports generated through a DWH? Before answering this, let me talk about "Business Intelligence" (BI).
In a ten thousand foot level, BI is a methodology, technology, architecture that transfers raw data into meaningful information for making business decisions.
Relation between ETL, DWH and BI
ETL (Extract Transform Load) system extracts and transforms data from operational systems where all transactions of business operations are stored and loads it into the DWH. With the help of various BI applications, reports are generated, queries are triggered as requested by business users to make strategic decisions and other analytic applications are used by managers based on the business requirements.
Dimensional Modeling (DM)
The logical data design of the DWH evolved from the end user requirements and this technique is known as DM. This technique to deliver data is preferred because of its fast query performance, simplicity and understandability - these are essential tenets of BI for end users.
DM is a four step process as shown below:-
1) Selecting the business process:- In this step, we select a business process of an organization to use in the Dimensional Modeling. For Example- If we want to analyze how a product is being sold in different markets, to make strategic decision about that product, choose sales data as a business process.
2) Declaring the grain:- Second step is to determine granularity (grain) of the business process. This is the lowest level of detail for measurement of the process and dimensions depend on this level. Example, for sales data, grain could be sales per month, per year, per day etc.,
3) Identifying the dimensions for dimension tables:- Dimensions determine the context associated with the business process. For sales example, the dimensions could be product, location, store information, customer info etc,,
4) Identifying the facts for fact table:- Facts are business measures for an event. A single row in a fact table must relate to one row from all associated dimension tables. Example- Sales Amount of a product of a location of a customer.(Sales Amount is the Fact ; Product, Location, Customer are dimensions)
The above Dimensional Model is connected together in the form of a star with Fact table in the center and Dimension tables surrounding the fact table. Hence, it is known as Star Schema.
In conclusion, today, BI is being used by organizations to understand the performance of business, observe market trends to make faster, meaningful strategic decisions. These capabilities are derived by integrating and standardizing the data into an enterprise data warehouse, and by using analytical methods to extract information. Dimensional Modeling is a design technique to represent data in DWH system.
We will talk about other BI topics in my future posts.
References:-
- Dimensional databases. (n.d.). Retrieved from http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.whse.doc/ids_ddi_354.htm
- Kimball, R., & Ross, M. (2013). The data warehouse toolkit. (3rd ed.). Indianapolis, Indiana: John Wiley & Sons, Inc.,.
- Ponniah, P. (2001). Date warehousing fundamentals. New York, NY: John Wiley & Sons, Inc.,.
"It's not what happens to us is important; it's how we handle what happens to us is important"
No comments:
Post a Comment