Business Intelligence and Data Warehousing — Informed decisions

Data refining
Data refining is process of creating subsets of the enterprise data warehouse, which have either a multidimensional or a relational organization format for optimized performance. This process usually lies within the entire BI architecture. The atomic level of information from the logical data model needs to be aggregated, summarized, and modified for specific requirements.

This data refining process generates aggregates, data marts and OLAP cubes that:
• Create a subset of the data in the model suitable for analysis.
• Create calculated and derived attributes.
• Summarize the information at a granularity as per requirement for analysis
• Aggregate the information.

Physical database model
In BI, talking about the physical data model is talking about relational or Multidimensional data models. Both database architectures can be selected to create departmental data marts, but the way to access the data in the databases is different:
• To access data from a relational database, common access methods like SQL or middleware products like ODBC can be used.
• Multidimensional databases require specialized APIs to access the usually proprietary database architecture.

Physical data model has following key attributes:
o Specification of all tables and columns.
o Foreign keys are used to identify relationships between tables.
o Denormalization may occur based on user requirements.
o Physical considerations may cause the physical data model to be quite different from the logical data model.
o At this level, the data modeler will specify how the logical data model will be realized in the database schema.

The steps for physical data model design are as follows:
1. Convert entities into tables.
2. Convert relationships into foreign keys.
3. Convert attributes into columns.
4. Modify the physical data model based on physical constraints / requirements.

Logical data model
A logical data model is a graphical representation of the information requirements of a business area, it is not a database. The word logical is very critical because it modifies the phrase data modeling to a more specific activity. A logical data model is independent of a physical, data storage device.

This is the key concept of the logical data model. The reason that a logical data model must be independent of technology is simply because technology is changing so rapidly.

In addition to the previously mentioned physical database model, a logical data model plays key pivotal role in assuring the real world objects/processes being mapped into BI solutions. There are different approaches for Logical Data Modeling.

• Star-Join Schema
• 3 NF Schema

Start-Join Schema is the most commonly used logical database model. The Star-Join Schema consists of two components:
• Fact tables
• Dimension tables

A very generic definition for those two components of the Star-Join
Schema is:
• Fact Tables — “what are we measuring?”
Contain the basic transaction-level information of the business that is of interest to a particular application. In marketing analysis, for example, this is the basic sales transaction data. Fact tables are large, often holding millions of rows, and mainly numerical.
• Dimension Tables — “by what are we measuring?”
Contain descriptive information and are small in comparison to the fact tables. In a marketing analysis application, for example, typical dimension tables include time period, marketing region, product type etc.

A Logical data model has following key attributes:
o Includes all entities and relationships among them.
o All attributes for each entity are specified.
o The primary key for each entity specified.
o Foreign keys (keys identifying the relationship between different entities) are specified.
o Normalization occurs at this level.

In Logical Data Model, the data modeler attempts to describe the data in as much detail as possible, without regard to how they will be physically implemented in the database.
In data warehousing, it is common for the conceptual data model and the logical data model to be combined into a single step (deliverable).

Steps for designing the logical data model are as follows:
1. Identify all entities.
2. Specify primary keys for all entities.
3. Find the relationships between different entities.
4. Find all attributes for each entity.
5. Resolve many-to-many relationships.
6. Normalization.

Metadata information
Metadata is information that describes another set of data. In a business intelligence (BI) platform, metadata links information in a data store to business entities and rules that define a BI application. Metadata structures the information in the data warehouse in categories, topics, groups, hierarchies and so on. It helps us by providing information about the data within a data warehouse. Few examples are as follows:
• It can be “Subject oriented”, based on abstractions of real-world entities like (’project’, ‘customer’, organization’, ‘MSISDN’, ‘Billing Account’)
• It defines the way in which the transformed data is to be interpreted, for example (’5/9/99′ = 5th September 1999 or 9th May 1999 — British or US, 0 for Female, 1 for Male etc.)
• Gives information about related data in the Data Warehouse.
• Estimates response time by showing the number of records to be processed in a query.
• Holds calculated fields and pre-calculated formulas to avoid misinterpretation, and contains historical changes of a view.

From a data warehouse administrator’s perspective, metadata is a full repository and documentation of all contents and all processes in the data warehouse, whereas, from an end user perspective, metadata is the roadmap through the information in the data warehouse.

Stay tunned…..

By nature a Data Warehouse is complex project, composed of number of architectural components. Each of these building blocks of a Data Warehouse has clearly defined boundaries and domain.
On a very high level, there are three major areas of a Data Warehouse.
- A set of processes required to keep the data Warehouse up to date. This consists of extraction/propagation, transformation/cleansing, data refining/enrichment, presentation and analysis tools
- The different logical and physical models to retain data in the form of OLTP/ODS, staging, transformed and aggregated/presentable form.
- The Metadata that describes the data and outcome of each of key processes.

Taking a further deeper view you can easily identify that each of these high level components are actually composed of discrete elements. We shall discuss each of these elements in more detail bellow.

Data Sources
Since Data Warehouse never generates data itself, instead it is populated with data from different operational systems like line of business applications (CRM, Billing, Sales, Purchases, Orders etc.), historical data (usually archived on tapes), external data (for example, from market research companies or from the Internet). The data sources can be relational databases, encoded format data files, or plain text data.

This data may be residing on many different platforms, and can are categorized as structured information, such as tables or spreadsheets, or unstructured information, such as plain text files or pictures and other multimedia information.

Data Extraction/Propagation

Data extraction / data propagation is the process of collecting data from various sources and different platforms to move it into the data warehouse. Data extraction in a data warehouse environment is a selective process to import decision-relevant information into the data warehouse. Data extraction / data propagation is much more than mirroring or copying data from one database system to another. Depending on the technique, this process is either:

• Pulling or
• Pushing

Following key points needs to be considered before deciding on any mechanism for Data extraction.
• Source data will be collected as delta of daily changes or as an summed up set of data that keeps growing every day as a result of business transactions
• There has to be some Change Data Capture (CDC) mechanism in place for a delta extract (the daily new additions/modifications) through which tracking of all changes shall be detected on source.
• If all major business rules shall be dealt at the source system during the extraction process or these shall be implemented once data is inside Data Warehouse environment.
• If sufficient free time window be made available for full or delta extract from source system.

Transformation/cleansing

Transformation of data usually involves code resolution with business rules and mapping tables (for example, changing 0 to pre paid and 1 to postpaid in the Package type field) and the resolution of hidden business rules in data fields, such as account numbers.

Also the structure and relationships of the data are adjusted to the analysis domain data model. Transformations occur throughout the population process, usually in more than one step. In the early stages of the process, the transformations are used more to consolidate the data from different sources, whereas, in the later stages the data is transformed to suit a specific analysis problem and/or data model.

Data warehousing turns data into information, on the other hand, cleansing ensures that the data warehouse will have valid, useful, and meaningful information. Data cleansing can also be described as standardization of data.

Through careful review of the data contents, the following criteria are matched:
• Correct business and customer names collected from different sources.
• Correct and valid addresses such as mailing, billing, incentives campaigns.
• Normalized/Usable phone numbers and contact information.
• Valid data codes and abbreviations.
• Consistent and standard representation of the data.
• Domestic and international addresses.
• Data consolidation (single, enterprise view), such as subscriptions and billing addresses correction.

Continued……Stay tunned!

‘Data warehousing’ is a collection of decision support technologies that enables the knowledge worker, the statistician, the business manager and the executive in processing the information contained in a data warehouse meaningfully and making informed decisions based on outputs.

The Data warehousing system includes backend tools for extracting, cleansing and loading data from Online Transaction Processing (OLTP) Databases and historical repositories of data. It also consists of the Data storage area–composed of the Data warehouse, the data marts and the Data store. It also provides for tools like OLAP for organizing, partitioning and summarizing data in the data warehouse and data marts and finally contains front end tools for mining, querying, reporting on data.

It is important to distinguish between a “Data warehouse” and “Data warehousing”.

A ‘Data warehouse’ is a component of the data warehousing system. It is a facility that provides for a consolidated, flexible and accessible collection of data for end user reporting and analysis.

As I already mentioned in my last article, data warehouse as defined by Inmon is a “subject-oriented, integrated, time-varying, non-volatile collection of data that is primarily used in organizational decision making.”

The data in a data warehouse is categorized on the basis of the subject area and hence it is “subject oriented”.
Universal naming conventions, measurements, classifications and so on used in the data warehouse, provide an enterprise consolidated view of data and therefore it is designated as integrated.
The data once loaded can only be read. Users cannot make changes to the data and this makes it non-volatile.
Finally data is stored for long periods of time quantified in years and bears a time and date stamp and therefore it is described as “time variant”.

Up till this point, I intentionally focused on Business Intelligence from Business’s perspective. And we have developed an insight on what Business Intelligence is, how it is/can be utilized by business people as a tool for strategic as well as tactical (short term) goals.

We had a glimpse of Business Intelligence in action in different business domains at different levels in Telecom, Aviation, Retail, Manufacturing, and Hospitality Industries. Then we had a high level view of Business Intelligence Strategy, high level components and its vital role for a successful BI implementation. As a deeper step we stepped into area of Analytics, which is the real value adding tool for any business; while taking high level view of analytics we have highlight of almost all major analytical application areas that are necessary for any Business that want to reap the real value of Business Intelligence.

Our Journey of exploring and exploiting Business Intelligence from Business point of view is not going to finish here, but instead we are going to park the Business view of Business Intelligence for next few posts and we will shift our focus to the underlying concepts and technical stuff that acts as a real workhorse for all the BI applications.

Starting from this post I am venturing into journey of Data Warehousing. Here I will explore what Data Warehouse is, its value for Business Intelligence applications, architecture, Logical Data Model, Physical Data Model, Value of Data Integration, Logical and Physical Data Modeling tools, Data Marts, Operational Data Sources, OLAP, Data Integration technologies and related concepts.

Lets start this journey of Data Warehousing by exploring what a Data Warehouse is and how Data Warehousing gurus define a Data Warehouse.

In simplest and comprehensive definition; a data warehouse is ” a centralized repository of diversified but integrated set of data that is maintained in such a way that it can be best utilized to derive information and actionable knowledge for business”.

“Centralized”, “diversified”, “integrated” and “actionable knowledge” is the keywords for a Data Warehouse.

Bill Inmon coined the term “data warehouse” in 1990. His definition is:
“A (data) warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision-making process.”

• Subject-oriented — Data that gives information about a particular subject instead of about a company’s on-going operations.
• Integrated — Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
• Time-variant — all data in the data warehouse is identified with a particular time period.
• Non-Volatile — Data is stable in a data warehouse. More data is added but existing data is never modified. This enables management to gain a consistent picture of the business.

Ralph Kimball provided another definition for a data warehouse. As stated in his book, “The Data Warehouse Toolkit”, a data warehouse is “a copy of transaction data specifically structured for query and analysis”. This definition provides less insight and depth than Mr. Inmon’s, but is no less accurate.

Stay tuned…

What is Business Analytics?

Business Analytics is a set of front-end-applications (made up of different graphs, charts and tabular representation of data) that are used within Business Intelligence environment to enable enterprises in analyzing their past performance, existing state and forecast and predict future status of business.

A key difference in Business Analytics and regular reporting applications is that regular reporting is a tool for day to day running of business whereas Business Analytics provides an insight on how to improve the business.

Business Analytics are based on key business metrics that help in better understanding business processes, customer relationship, organizational performance and future direction.

Some of key Business Analytics for almost every Business Intelligence initiative undertaken by every business organizations are:
- Customer Analytics
- Product Analytics
- Sales Analytics
- Marketing Analytics
- Financial Performance Analytics
- Customer Services Analytics
- Market Share Analytics

Customer Analytics is a broader set of analytics that enables an organization in understanding:
- Who their customers are, and segmenting them into groups/circles
- What is their behavior?
- What are their preferred interaction touch points/channels?
- What is their Wallet Value?
- Who can be possibly targeted for cross sell, up sell?
- And many more…

Product Analytics helps organizations in understanding
- Product profitability
- Product cross sell, up-sell
- Product bundling and customization based on customer preferences.
- Market penetration for products
- Product success/failure analysis

Sales Analytics covers complete Sales chain for an organization and helps it in understanding and coming up with actionable outcome. Sales Profitability enables organizations in
- Sales Force performance evaluation
- Sales Channel effectiveness analytics
- Sales Profit analytics
- Sales Forecasting and planning analytics
- Sales Opportunity Analytics

Marketing Analytics enables organizations in streamlining, re-organizing and initiating proactive marketing efforts through following analytics.
- Marketing Insight analytics (enables you to have a deep insight of marketing efforts, current trends and their outcomes).
- Marketing Channels effectiveness analytics
- Demographical Marketing analytics
- Marketing Campaigns Analytics

Financial Performance Analytics gives organizations insight on
- Credit and collection analytics
- Credit Risks analytics
- Revenue analytics
- Portfolio analytics
Customer Services Analytics provides insight on performance, areas of improvement, and new possibilities to enable organizations for better service delivery, complaints handling, new campaigns moves through:
- Services delivery analytics
- Service delivery agents turn around time analytics
- Service centers work load and human capital productivity analytics

For any of the analytics there are three major attributes that makes analytics useful or totally useless for any organization, these three attributes are
- Purposeful
- Insightful
- Actionable

There are many analytics that are pre-built solutions designed for fast deployment at a lower cost, at a lower risk, and with better business results. These pre-built analytics includes pre-built data model, metrics, and best practices based on cross industries in thousands of implementations.

These robust enterprise analytics platforms enable users to easily customize and extend the applications without the need for programming. The intuitive, Web-based user interface enables rapid end-user adoption and requires very little training.

In the upcoming episodes I will be giving more insight on each of these analytics one by one and evaluating these for effectiveness, and action ability to be used by business executives, managers, or even front line workers to respond to either long term or day to day challenges faced during their business activities.

In last few episodes of my blog we had a glimpse of how Business Intelligence is helping out in various sectors in managing our strategic as well as tactical objectives. Now, in this episod and upcoming episodes I want to write about what is business Strategy, what are its components and how it helps us in achiving the results that are promised by Business Intelligence.

Like any other business initiative to achieve strategic competitive advantage and maximize Return on Investment (ROI) a strategic plan has to be chalked out. To achieve a strategic competitive advantage and maximize ROI through Business Intelligence a Business Intelligence Strategy has to be chalked out. BI Strategy guides organizations in aligning their BI initiatives with business strategies and includes an evaluation of the effectives of current technology to achieve those goals and objectives

A well outlined BI Strategy enables organizations in defining strategic and tactical business objectives evaluate existing technological inventory, define BI initiatives, roadmap and BI governance structure to constantly evaluate.
A Business Intelligence strategy is a living document and it is continuously updated as the organization evolves and embarks on new projects. A BI Strategy usually covers following areas:
• Strategic Business Goals
• Tactical Business Requirements
• Assessment of Current Operational and Managerial reporting
• Identifying and documenting gaps in current information accessibility and business processes
• Business Intelligence Initiatives and Roadmap
• Data Architecture
• Technical architecture
o Software environment
o Hardware environment
o Database environment
o Network environment

Business Intelligence Strategy provides following benefits:
• Increase in visibility and understanding of strategic business initiatives with a high ROI.
• Evaluate existing BI initiatives and re-aligning projects with strategic business objectives.
• Evaluating and determining if existing infrastructure supports strategic BI initiatives and recommending changes.
• Capitalizing on BI strategy roadmap that covers business and technology implementation considerations

Hospitality Industry and Business Intelligence
Hospitality Industry has evolved to one of leading Industries in world with over millions and billions dollars business. These companies are facing increased challenges to strengthen and establish their brand, flurish their business in emerging markets, and develope differentiating marketing strategies to increase profits, enhance customer experiences and service delivery excellence.

Hospitality Industry is very sensitive to changes in the economy, seasonal, sociological and atmospheric effects. Strategies that sounds sueperb in one geoghraphical location might not be as effective in different geoghraphical locations.

Key challenge in applying Business Intelligence to Hospitality Industry lies in the identification of key
business processes and how to collect their related data. Key Areas of Focuse for Hospitality Industry are:
. Marketing
. Daily Revenues
. Journals & Payables
. Budgeting
. Purchasing
. Forcasting
. Labour & Staffing
. Tangible assets management and planning

Once in place, Business Intelligence solutions in Hospitality Industry helps to automate key financial reporting and analysis functions,implement flexible budgets and forecasts, centrally managing the planning process, and set objectives to increase profitability and achieve business goals.

Some of key benifits of applying Business Intelligence enables you to have insight of whats going on, where the business is heading and
what should be done additionally to have competitive edge on competion.
KNOW WHAT IS HAPPENING: In-depth sales and marketing history analysis

Get accurate insights from the Hospitality BI dashboards.
- Analyze period-to-date figures, year-over-year changes, and budget variances.
- Compare property sales with those of other properties, brands, chains, and regions.
- Identify your high yielding market segments, rate plans, and business sources.
- Analyze the effectiveness of marketing campaigns, promotions and package plans.
- Identify your most / least profitable guests, corporate accounts, and travel agents.


UNDERSTAND WHY IT IS HAPPENING: Pinpoint the facts with flexible analytics


Perform ad-hoc analysis with the ease of drag-and-drop.
- Measure the impact of various business drivers with multi-perspective analysis, such as
market segment by rate plan, or corporate account by sales manager.
- Drill down to details to uncover trends and relationships, understand anomalies, and
investigate lost opportunities.
- Create your own pixel-perfect reports and charts.


UNCOVER WHAT IS GOING TO HAPPEN: Discover future market trends and anticipate demand
Forecast accurately with fully-automated Pickup and Pace reporting.


- Analyze business on the books with or without the group blocks.
- Compare period-over-period pickups to determine the booking trend.
- Compare period-over-period business on the books to determine the booking pace.


DETERMINE WHAT YOU WANT TO HAPPEN: Stay ahead with a timely action
Act on the intelligence and leverage your knowledge.


- Respond to demand fluctuations early on.
- Discover and seize opportunities before they are gone.
- Optimize marketing funds by targeting the likely responders.
- Reward your most profitable customers to ensure repeat business.

In Daniel J. Connolly, PH.D., Assitant Professor, University of Denver’s words

“Business intelligence gives a hospitality company the
ability to respond more quickly to correct things that
may be problematic. Not only can companies meet problems
in a more timely manner, they can take advantage
of new market opportunities by seeing things faster
than their competitors and being able to act on them.”

Business Intelligence and Aviation Industry

Aviation Industry is one of industries that have an unprecedented growth in this decade. Aviation Industry is unique in terms of its services and diverse nature of inventory of HR, Air fleet, Equipments, and related tangible materials. For air carriers, air port authorities and other related services providers.

While developing business models, every carrier needs to take into account the short term as well as long term dynamics and it’s really a ‘War for the skies’ that is constantly fought among carriers to increase profit margins, customer satisfaction and grabbing more and more routes.

Aviation is a very specialized Industry and it has its own specific challenges. Some of key characteristics of this Industry are:
Service Industry
Because of all the equipment and facilities involved, it is easy to lose sight of the fact that this is, fundamentally, a service industry. Airlines perform a service for their customers-transporting them and their belongings (or their products, in the case of cargo customers) from one point to another for an agreed price. In that sense, the airline business is similar to other service businesses like banks, insurance or even barbershops. There is no physical product given in return for the money paid by the customer, nor inventory is created and stored for sale at some later date.
Capital Intensive
Unlike many service businesses, aviation is most capital intensive; even more than store-fronts and telephones (land line/mobile) to get started. They need an enormous range of expensive equipment and facilities, from airplanes to flight simulators to maintenance hangars. As a result, the airline industry is a capital-intensive business, requiring large sums of money to operate effectively.
High Cash Flow
Because airlines own large fleets of expensive aircraft which depreciate in value over time, they typically generate a substantial positive cash flow (profits plus depreciation)
Most airlines use their cash flow to replay debt or acquire new aircraft. When profits and cash flow decline an airline’s ability to repay debt and acquire new aircraft is jeopardized.
Labor intensive
Airlines also are labor intensive. Each major airline employs a virtual army of pilots, flight attendants, mechanics, baggage handlers, reservation agents, gage agents, security personnel, cooks, cleaners, managers, accountants, lawyers, etc. IT has enabled airlines to automate many tasks, but there is no changing the fact that they are a service business, where customers require personal attention. More than one-third of the revenue generated each day by the airlines goes to pay its workforce. Labor costs per employee are among the highest of any industry.
Thin Profit Margins
The bottom line result of high HR costs, Costly Equipments is thin profit margins, even in the best of times.
Seasonal Effects
The airline business historically has been very seasonal. The summer months were extremely busy, as many people took vacations at that time of the year. Winter, on the other hand, was slower, with the exception of the holidays. The result of such peaks and valleys in travel patterns was that airline revenues also rose and fell significantly through the course of the year. This pattern continues today as well.

Today, Aviation industry is most dynamic and fierce competitive and challenging industry and automation is critical; challenging timely decisions about selection of profitable routes, flight scheduling, air fleet maintenance and operations, effective and marginal ticketing, quick and accurate reservations and on time departures and arrivals. For all this, Business Intelligence is very essential tool.

An airline schedules database provides the number of operations, seats and cargo capacity offered in the scheduled airline market, and is therefore an essential component of the aviation analyst’s armory.

Bookings data sourced from the world’s GDS’ provide the aviation analyst with the demand perspective on a true flight line O&D basis. Aviation Analysis can supply and adjust or calibrate bookings data, which will include total passengers by full itinerary, class of service, average fare, airline, and point of sale country and airport of origin.

Ticket data sourced from IATA provides another important perspective on traffic demand. Based on ticket rather than booking information, a variety of analysis are produced which helps analyze and further improve reservations and confirmed bookings.

In short, Aviation Industry is one of key sectors that are and will continue to further grow to utilize the Business Intelligence in a direct or indirect way to increase profit margins, enhance customer satisfaction and streamlined and improved operations.

Business Intelligence in work for Manufacturing Industry:

Manufacturing sector has very specific challenges for timely and accurate information availability about manufacturing units, assembly units, work load scheduling, Warehouse Management for raw materials and finished products. It requires keeping up-to-date information about product and raw material and shipment orders.

Application of Business Intelligence to manufacturing sector is some time termed as Manufacturing Intelligence (MI). MI enables companies in the manufacturing business by keeping 360 degree view of its operations.

Using Business Intelligence tools Raw materials and supply Managers keeps track of optimized materials balance levels and enables them to place orders to suppliers for delivery of materials to relevant plants and assembly units, This helps them avoid out of stock or over stock situation.

Production unit Managers can make rapid and accurate decisions right on factory floors that enables them to:
- Execute production plans on time and within budget
- review and take measures to reduce production costs
- Pre-schedule maintenance and replacements of manufacturing and assembling unit equipments
- Increase productivity and efficiency by applying different work scheduling techniques and taking into account each work process work time, delays and outages.

Addressing Retail Industry challenges through Business Intelligence :
Retailers today are operating in a sophisticated and challenging environment, the fierce competition and highly demanding customers are pushing them to stay on their toes and always come up with better offers than competitors. This pushes retailers to gain more deep insight into all aspects of their business. For better insight, information has to be delivered quickly, accurately and it must be provided at right time.

In Retail Industry there are four major areas which can make or break your business; Customer Management, Marketing, Store operations and Merchandise Planning . By applying Business Intelligence to these four key areas you can gain edge on competitors in terms enhanced profits, satisfied customers and streamlined operations. The Retail Business Intelligence Solutions provides you an enterprise view of data and it can be applied to all areas of retail business.

Marketing and Brand Promotion Executives can build more successful, targeted promotions on customer segmentation and specific location bases segmentation. They can enhance new product introductions by understanding purchasing trends and give a better forecast as how different markets and customer segments will respond.
Customer Relationship Management Executives can provide consistent, multi-channel services to customers by enabling all customer touch points
to access the same, up-to-date information about customers and their buying patterns.

Store Operations Managers can streamline the store ordering process and speeding up the flow of goods to stores by using up-to-date inventory and sales information to determine when to replenish merchandise. They can have more effective and timely product assortment plans based on store demographics and market the right products in the right stores at the right time. Monitor and improve the customer checkout points processing time and reducing out of stock and back orders.

Merchandise planning executives utilizes information to analyze and manage inventory levels and increased productivity to help ensure promotional inventory levels are properly forecasted and delivered to stores. They can analyze and develop accurate allocation models for new stores and replenishment at existing locations. It works as a best tool for vendor performance evaluation, their response time and offered profit margins.

We shall be covering specific details in upcoming posts.