Enterprise Business Learning

Enterprise Business Learning is a place where my understanding of business models and data warehousing comes forth. It talks about the various steps involved in Dimension modeling. Focusing on the case study of a 'WarehouseStore', a dimensional model has been prepared, followed by the answers to the business queries asked by the WarehouseStore Management to understand the helpfulness of the data warehouse in their system. It also emphasizes the process involved in-depth, in their day-to-day business.

Understanding the Business

WarehouseStore is working on a widespread model for 4 business facets. They work as the segmented amalgamation of all these facets. These are-

• • • Point-of-Sales (POS)

    • E-commerce

    • Inventory Management

    • Pharmaceutical Information

Although WarehouseStore HQ focuses on efficient and effective business performance as a whole, the major target in the business processes is Order Management and Procurement Management. So, the POS and Inventory Management hold the key to their business processes.

Design Process: 4-Step Dimensional Design Process

As our goal is to design a dimensional model for the WarehouseStore’s business processes, we are going to render the 4-step approach of dimension modeling. This involves identifying the business processes as the initial step, followed by, declaring the grain, then, identifying the dimension, and lastly, identifying the facts.

Considering our WS’s HQ initiatives, we are aware of the business strategies instead of processes. So, we decompose these business initiatives to showcase the underlying operations in the form of a bus matrix.

Identify the Business Processes

In this case, managers are inclined to analyze consumer behavior based on purchase behavior. Thus, making our prime focus on the POS Retail Sales transactional data of the business. This includes the Retail Sales process and the Counter/Prescription Medicine Sales process. This will help us to understand the product sales across each store in addition to the effects of transactions on promotions and discounts offered.

Another agenda by the management is to analyze the location performance on the purchase behavior. This can be attained by pivoting on the orders placed in a location and the inventory involved for that location. Thereby, focusing on the Inventory management process and Order Management process.

Lastly, as management goals to understand the income and revenue generated in all areas as well as the effect of promotion, it is important to consider a separate Market Study process to be aligned as one of the business processes involved here in WarehouseStore.

To conclude, there are 5 business processes involved here:

1. Inventory Management process

2. Market Study process

3. Order Management process

4. Counter/Prescription Medicine Sales process

5. Retail Sales process

Bus Matrix

Now that, we have recognized all the key business processes, it is time to realize the dimensions involved for each business process fulfillment.

Declare the Grain

Identifying the fact tables and the details of measures in dimensions is a very important part of the design phase. The granularity of a fact table declares the coarseness of the details present in the table. Lower the granularity means more in-detail measures are present.

A grain is a fact table, and a fact row represents a single measurement at a particular interval of time.

For instance, consider the fact tables Store, Products, and Date, the finest level of detail that can be attained from this dimension model would be ‘ products sold in the stores by the day ’.

Identify the Dimensions

Now that the grains are understood, the description of how to store and assess these events into data is to be determined. This is achieved by setting contexts and values to each measurement as an attribute to the event, which is called a dimension. The dimensions help understand the what, how, where, why, where, and when of the event.

Identify the Facts

Now that we have understood our process, its dimensions, and its attributes, we can establish the facts involved in each of the processes. These facts must be in regard to the grains, hence the processes.

Schema Models

Other Design Features

What category and brand of products are the most popular this year? What particular product is most popular across the nation?

What products are short of supply in this month < 20 in quantity? Has this always been the case in whole year?

How many patients are regularly using our medications? Which medication is the most selling product?

Which age group of customers is most using our Vitamins across the nation? Does this vary across different locations or times of the year?

The promotion period in Christmas month has increased the sales compared with the same period in last year.

Any products have not been sold out during the promotion period?