Business Intelligence enhancing Retail Industry

In the current age of Information technology, the retail and wholesale industries have never been more fragmented. From discount warehouse clubs to upscale specialty stores, corner convenience chains to mega e-commerce sites, buyers are better informed, less loyal, and highly cautious about discretionary spending.  Organizations are always striving to balance product, price and service sensitivities, while protecting margins and trying to ensure inventory availability while maintaining reduced carrying costs. Now more than ever information must be leveraged in new ways to gain competitive advantage and renew stability in these uncertain times. And that’s where BI plays a huge part.

Retail Industry Overview:

Increasing competitors: There has been an explosion of products, markets and stores. There is also always the constantly looming threat of new entrants, who look to take away the market share.

Increasing Customer Expectations: There is the ever increasing demand from customers that have to be met. Innovation in technology has brought the services and the products to the fingertips of the customers.

3.     Customer/Supplier bargaining power: The retail industry has to often deal with the pressure of the customers or suppliers influencing the pricing decisions of products.

Retail Industries use the following BI framework for establishing and integrating their Hardware and Software components.

Retail Industries use the following BI framework for establishing and integrating their Hardware and Software components.

The source systems capture the data from the daily transactions and store them. The ETL layer is responsible for data extraction, cleansing, staging, transformation and loading. The Data Warehouse stores the data depending on the needs of the business. The BI Layer is responsible for the number crunching by applying business logic and generates relevant metrics.

Evolution of BI in Retail Industry

The evolution of BI in the retail industry is staggering, from static reports to dynamic dashboards. Insights can be given into data with root cause analysis to determine the functionality and the effectiveness of decision making. These advancements in BI with respect to retail industry has helped in providing the following advantages,

Aligning the business with client needs: Identifying opportunities and enhancing decision making.

Gaining competitive advantage: Analyzing the data and generating reports to increase business functionality and effectiveness.

Resource allocation optimizations: The technical and the financial data needs to be integrated with the operational data in order to maximize the resource allocation and enhance productivity.

In the retail industry, using Business Intelligence (BI) is an obvious choice. But before that, one must understand buying behavior, customer requirements, exact price points for products, shipping and inventory timing and changing trends. Getting the right product on the store shelves at the right time, for the right price is the essence and what retail is all about.

References: http://www.elegantjbi.com/solutions/industry_bi_for_retail.htm

                  www.wikepedia.com

                  http://www.slideshare.net/shwetaj/business-intelligence-in-retail-7642339

Enterprise Data Warehouse : First look

An Enterprise Data Warehouse is a centralized data warehouse, which serves the entire enterprise. It contains a large database repository containing old and current transactions of an organization. The history, or the emergence of the Enterprise Data Warehouse, came about to address the need for data which is increasing exponentially in the last few years. There has been an opportunity and a necessity for Business Enterprises to stay updated with current advancements in technology as well as the industry competition.

The main rules while modeling an Enterprise Data Warehouse are

1. The grain should be clearly defined. The goal of the warehouse design is to essentially come up with a clear representation of the Enterprise’s business data as well as the rules that govern the organization. The more departments and functions of the enterprise, the more definite and defined the Warehouse should be.

2. An enterprise can never contain just a single functional area. Therefore, the Enterprise Data Warehouse should contain multiple areas which may be related to Marketing, Sales, HR, Retail etc.

3. The Enterprise Data Warehouse almost always contains denormalized models like Star or Snowflake, but this is generally not a rule. Many database designers also prefer the normalized design. The key point to note here is the emphasis on flexibility, performance and speed.

4. Every Enterprise Data Warehouse infrastructure should always be able to handle unforeseen and unexpected situations which might potentially result in a loss of income. The emphasis should be on availability, which means that disaster recovery features and security should always be in place.


5. An Enterprise Data Warehouse should always be scalable across many dimensions. This essentially means that it should be able to handle the exponential growth of data as well as increasing complexity with the development and growth of any Business Enterprise.

In this age of Information Technology, all processes are data driven. Hence a successful Enterprise Data Warehouse should be adaptable to change and focus on the above mentioned features.

References : Kimballgroup.com
Wikipedia

Of keys and dimensions!!

After settling down into a routine (classes and quizzes), its now time to dig a little deeper into some concepts. Similar to what was dealt with in class, two of the main concepts that I feel need some discussion are keys and dimensions ( having considerably spoken about facts in my previous post ). I will list some of the types of keys and dimensions that we may come across with examples.

1. Junk dimensions: We will frequently encounter business processes that may involve a number of miscellaneous attributes and indicators (yes/no) along with some flags. While we would normally like to create separate dimensions for each of these, it makes it easier to manage if a single dimension is created and all the data is entered into it.
For example in a transaction involving a item delivery, we might have several indicators like packaged, shipped, delivered, received, refunded etc. All of these attributes can be put into a junk dimension.

2. Outrigger dimensions: Very simply put, this is nothing but a dimension which has a reference to another dimension. For example car models dimensions can reference a separate dimension which represents a specific type of car. We should normally restrict the use of these dimensions and both of these should be in the fact table as separate foreign keys.

3. Conformed dimensions: When the attributes in separate dimensions have the same column names and the same meaning. A conformed dimension can refer to multiple tables. Two tables are said to be conformed if they are identical, or if one is the subset of the other. For example, in most organizations, the date dimension is the most common conformed dimension.

4. Shrunken dimensions: Shrunken dimensions are essentially conformed dimensions which contain a subset of rows and/or columns of a base dimension. This normally occurs when two dimensions are at the same level of detail but one contains only a small portion (subset) of the rows of the other table.  For example, a month dimension could be a shrunken dimension of a date dimension.

Now having spoken about some of the dimensions that we would encounter, let us discuss some keys.

1. Degenerate dimension keys: We may face circumstances where a dimension key has no dimension table associated with it. This is most commonly seen when a fact tables grain is a single transaction. For example, if we have an invoice with no content associated with it, but we still need to model a particular invoice no as a key for the fact table.

2. Dimension surrogate keys: In essence, surrogate keys are those keys that join the dimension tables with the facts. However, sometimes, a single key can be used by multiple instances of the same entity. Therefore this key cannot be used since, over a period of time, there will be multiple rows in the dimension table that use this single key. Therefore, an integer system is used to keep track of keys, starting from 1, every time a new key is assigned. For example, when we model companies who are trying to integrate information from different sources, or after mergers and acquisitions, we need to use surrogate keys because we might have conflicting keys across dimensions.

3. Natural keys: A natural key is a key that exists in the real world. Where this is different from surrogate keys discussed above is that the surrogate keys have no meaning outside the database environments, while a natural key does. For example, a persons SSN could be a natural key.

4. Durable keys/ Durable Supernatural keys: Time to introduce a new concept (dimension ?) in data warehousing, time. A durable key is one that uniquely identifies a record over time. When we integrate data from multiple sources, the natural key might change. The way to tackle this is having keys in a format that is totally independent of the process model. For example, these could be simple integers whose values begin from 1.

I believe with these concepts we will gain a foothold in the vast data warehousing universe. in the words of Ronald Case, a Nobel Prize Laureate, torture the data, and it will confess to anything.

Reference : Wikipedia
                  http://www.kimballgroup.com/