Design Data Warehouse for Real Time Applications

SCREENSHOTS

1. Open the Power BI

2. Load the CSV or Excel sheet using Import data from Microsoft Excel workbook
3. Select the dataset from the respective folder.

4. Once loaded we now have our view of all the columns of data in the Fields viewing pane on
the right. From here we can build our visuals.
ANALYSE THE DIMENSIONAL MODELING

Screenshots

1. Open the Power BI

2. Load the excel sheet using Import data from Microsoft Excel workbook

3. Drag and drop a categorical column

4. Drag and drop a numerical column.

5. Select a visualization method from visualization tab.

6. Create a valuable visualization.

7. Create more visualization for deriving various insights.

EX: NO:7 CASE STUDY USING OLAP

Aim:

To conduct a study on an OLAP-based analytical CRM system aimed at enhancing Business

Intelligence (BI) through customer data analysis

Introduction:

Online Analytical Processing (OLAP) is a powerful technology used for multidimensional

analysis of data. It enables users to interactively analyze large volumes of data from multiple
perspectives, facilitating decision-making and business intelligence. Unlike traditional
relational databases that store data in two-dimensional tables, OLAP systems organize data into
multidimensional structures known as cubes. These cubes allow users to analyze data along
multiple dimensions, such as time, geography, product, and customer.

OLAP cubes store pre-aggregated data at various levels of granularity, allowing users to
quickly access summarized information without the need for complex queries or calculations.
This facilitates faster analysis and decision-making.

OLAP systems support drill-down and roll-up operations, enabling users to navigate through
different levels of detail within the data hierarchy. Users can drill down from higher-level
summaries to detailed data or roll up from detailed data to higher-level summaries to gain
insights at different levels of granularity.

OLAP allows users to slice and dice data by selecting specific dimensions or subsets of data
for analysis. Slicing involves selecting a single dimension value or range, while dicing involves
selecting multiple dimensions for cross-tabulation analysis.

OLAP systems provide interactive interfaces for users to explore data dynamically. Users can
manipulate dimensions, measures, and other parameters to analyze data in real-time,
facilitating ad-hoc analysis and exploration of data trends and patterns.

OLAP is widely used in various industries, including finance, retail, healthcare, and
telecommunications, for a range of applications such as sales analysis, financial reporting,
customer segmentation, and inventory management. In the hospitality industry, OLAP is
particularly valuable for analyzing customer data, optimizing hotel operations, and enhancing
guest experiences.
Literature Review:

 Review existing literature on OLAP, CRM, BI, and their integration in the hospitality
industry.
 Identify studies that have explored the use of OLAP for customer data analysis and BI
in hospitality settings.

Methodology

 Describe the methodology for the study, including data collection, analysis techniques,
and evaluation criteria. Specify the data analysis techniques, including OLAP cube
operations, segmentation analysis, and statistical analysis.
 Detail the sample selection criteria and data sources, ensuring they are representative
of the hospitality industry.

Fig 1: Methodology

 Data Collection and Preparation:

 Gather data from relevant sources within the hospitality industry, such as hotel
transactional systems, customer databases, and CRM platforms.
 Ensure data quality and integrity through validation and cleansing processes.
 Cleanse and preprocess the data to ensure accuracy and consistency.
 Transform the data into a format suitable for OLAP analysis, designing a star
schema or snowflake schema if necessary.
 Data Transformation and Modeling:
 Transform raw data into a format suitable for OLAP analysis.
 Design a star schema or snowflake schema to organize the data, including fact
tables and dimension tables.
 Implement ETL processes to populate the data warehouse with transformed
data.
 OLAP Cube Development:
 Develop OLAP cubes based on the prepared data, incorporating dimensions
representing customer attributes and measures representing key performance
indicators.
 Develop OLAP cubes based on the defined data model. Construct cubes
representing different aspects of the business, such as sales, finance, or customer
analysis. Define dimensions, hierarchies, and measures within each cube to
enable multidimensional analysis.
 Implement OLAP operations to enable multidimensional analysis, such as
slicing, dicing, drilling down, and pivoting.
 Cube Processing:
 Process the OLAP cubes to populate them with data. This may involve
aggregating data at various levels of granularity, pre-calculating summary
measures, and building indexes to optimize query performance.
 Analysis and Results:
 Analyze the OLAP cubes to extract valuable insights into customer behavior,
preferences, and trends.
 Conduct segmentation analysis to identify distinct customer segments based on
demographic, geographic, and behavioral factors.
 Present the findings using visualizations such as charts, graphs, and dashboards
to facilitate interpretation.

Fig 2: OLAP Operations

 Fig 3: Star Schema for Hospitality Industry

Result:

Thus the conducted a comprehensive study on an OLAP-based analytical CRM system

to analyse customer data and derive valuable insights for enhancing Business
Intelligence in the hospitality industry.
Ex: No: 8 Case Study Using OLTP

Aim:

To Enhancing Transaction Processing in the Retail Industry through OLTP Systems

Introduction:

OLTP is a form of data processing specifically suited to real-time database transactions

that must be always correct and consistent. Database transactions refer to any change made in
a database. OLTP databases allow businesses to manage large volumes of transactions
concurrently from many users while maintaining data integrity at all times. In the highly
competitive retail industry, efficient transaction processing is essential for delivering seamless
customer experiences and optimizing business operations. This case study examines how
Retailer XYZ, a leading brick-and-mortar and e-commerce retailer, leveraged OLTP
technology to streamline its retail operations and enhance customer satisfaction. The objective
of this study is to demonstrate how Retailer XYZ implemented OLTP systems to handle high
volumes of transactions across multiple sales channels, ensuring real-time processing, data
accuracy, and operational efficiency.

Methodology:

System Architecture Design:

Retailer XYZ designed a robust and scalable OLTP system architecture capable of
processing transactions from various sales channels, including physical stores, online stores,
mobile apps, and call centres.

The OLTP system architecture employed redundant hardware configurations, load

balancers, and failover mechanisms to ensure high availability and fault tolerance.

Data Modelling and Database Design:

Retailer XYZ implemented a relational database management system (RDBMS) as the

backend infrastructure for OLTP processing.

The database schema was designed to support normalized data structures optimized for
transactional processing, with tables organized to represent entities such as products,
customers, orders, and transactions.
 Proper indexing and constraints were applied to enforce data integrity and optimize
query performance.

Transaction Processing Workflow:

Retailer XYZ developed transaction processing workflows tailored to different sales

channels, ensuring consistent data capture and synchronization across the retail ecosystem.

Real-time transaction processing capabilities were implemented to update inventory levels,

process payments, and generate order confirmations instantly.

Automated validation and authorization mechanisms were deployed to enforce business rules,
validate customer orders, and prevent fraud.
Scalability and Performance Optimization:

Retailer XYZ conducted performance testing and optimization to ensure the OLTP
system could handle increasing transaction volumes and peak loads without degradation in
performance.

Scalability measures, such as horizontal and vertical scaling, were implemented to

accommodate growing demand and maintain optimal system responsiveness.

Database performance tuning techniques, including query optimization, index optimization,

and caching strategies, were employed to improve transaction processing throughput and
reduce latency.

Result:

Thus the implementation of OLTP systems, Retailer XYZ successfully streamlined its retail
operations, enhanced customer experiences, and improved operational efficiency. The OLTP
infrastructure provided the foundation for real-time transaction processing, data accuracy, and
scalability, empowering Retailer XYZ to meet the dynamic demands of the retail market and
drive business growth.
 EX:NO: 9 IMPLEMENTATION OF WAREHOUSING TESTING

AIM:

To conduct testing in the warehouse and evaluate its effectiveness.

PROCEDURE:

Integrate Dataset

 Prepare Car CSV Dataset using Excel.

Car ID Car Name Fuel Type Car body Car Length Car Width
66.9
101 BMW X4 Gas Sedan 189
66.2
102 Audi 100LS Gas Sedan 176
Chevrolet
103 Gas Hatchback 141 60.3
Dodge d200
104 Gas Hatchback 155 62.0
64.2
105 Jaguar XF Gas Hatchback 159

Extract Data into Power BI

 Open Power BI Desktop.

 Click on "Home" in the ribbon.
 Click on "Get Data" and choose "Text/CSV" if your data is in a CSV file.
 Navigate to your CSV file and click "Open."

Load Data into Power BI

 Once selected your CSV file, you will see a preview of your data.
 Click on "Load" to import the data into Power BI.

Performance Analyzer Tool

 Power BI has a built-in Performance Analyzer tool that helps identify performance
bottlenecks in reports.

Enable Performance Analyzer:

  Open the report you want to analyze in Power BI Desktop. Go to the "View" tab in the
ribbon and select "Performance Analyzer" from the "Diagnostic" dropdown.
 Click on the "Start Recording" button to begin the performance analysis.
 Interact with the report as you normally would, including applying filters and changing
visuals.
 The Performance Analyzer window will capture performance data. When you are
finished, click on the "Stop Recording" button to end the performance analysis.
 Review the results in the Performance Analyzer window to identify any issues or areas
for optimization.

Analyze Results:

 Examine the Performance Analyzer window to identify the duration of each query and
visualization.
 Identify visuals or queries that contribute significantly to the overall report rendering
time.

Optimization:

 Based on the results, optimize the report by simplifying complex visuals, reducing
unnecessary calculations, or improving data model efficiency.

Monitor and Iterate

 Regularly monitor the performance of your warehouse testing reports and gather
feedback from users. Iterate on your reports based on insights and changing business
needs.

RESULT:

Thus, the testing in the warehouse and evaluate its effectiveness has been completed
successfully.