Data Warehouse

The Kimball Lifestyle is the recommended approach to design, develop & deploy he DO/BI, as described in aforementioned text book (eBook). The illustration at Figure 1 summarizes main stages from the Lifestyle. Figure 1: The Kimball DO/BI Lifestyle The Kimball method helps to explain business requirements adding value to an organization. The company must realize this value add, acknowledge it and then decide to implement the solution. (MS. Com, n. D. ).

Issues and Process involved in Implementation of DO/BI system Dimensions Business Processes Date Transaction Client outlet Category Type Product Venue Budget Events_organize x Celebrity Booking Advertisement Books Sale Book Distribution Commission payment The Data Warehouse Bus Matrix The main process involved is the development of the Data warehouse Bus Matrix, as it was highlighted in the earlier section. The Enterprise Data Warehouse Bus Matrix is the data framework for the enterprise data warehouse.

The Figure below shows the bus matrix for Jar Bookstore: Data Model Design The Kimball method provides practical approach land selects the right physical model based upon how usable and flexible it is and how is its performance & maintenance. Almost all these type of models are classic star schemas, as shown in Figure 3. The complete information of a process is its central fact table. Its like a first normal form or ‘De-normalized’ dimension tables, which surrounds the fact table.

Then there are details or dimension tables which can be joined with integer keys called ‘surrogate keys’. This is shown in the below figure. Some researchers call for the normalized, third normal form model at lowest level in data warehouse as this provides Max flexibility. The dimension or detail table bear an analogy to this as will be shown in the subsequent analysis in the later sections. (MS. Com n. . ) Figure 3: An orders business process star schema Star Schema Model Here is a design of star schema, with clear dimensions with Primary an Foreign Keys.

As per the Kimball Method, the star schema represents the Normalized Source Tables. As per the model, the “dimensions are the objects that participate in an organization’s business processes. We generally model these as one table per object. Building the dimension in the TTL system involves joining the various normalized description and hierarchy tables that populate the dimension attributes and writing the results into a single table. (Thorniest, W, 201 1) RED Diagram to provide a complete overview of the Online Bookstore.

Data warehouse requires designing Fact tables and dimension tables. As provided earlier the fact table is the central table within star schema of a data warehouse. A fact table contains basic, raw and De-normalized data. For the Jar Bookstore there are three types of facts: (Thorniest, W, 2011) 1. Additive: these are summed up data 2. Semi-Additive: Semi-additive facts that can be summed up for some data only 3. Non-Additive: Non-additive facts are facts that cannot be summed up. In the context of Jar Bookstore we illustrate each these three types of facts.

The first example assumes that we are a retailer, and we have a fact table with the follows Eng columns: Date Store Sales Amount This table records the sales amount for each product in each store on a daily basis. This data is extracted in the excel sheet as well and will be analyzed in the later sections. The Jar Bookstore has the following fact table: Date Books Current Balance Profit_Margin The object of this table is to record the current balance for each account at the end of each day (DOD Balance).