Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    Exp - 5

    Uploaded by

    20BBCS9266Harshit Raj
    Dara

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Exp - 5

    Uploaded by

    20BBCS9266Harshit Raj
    3 views3 pages
    Dara

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Dara

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    3 views3 pages

    Exp - 5

    Uploaded by

    20BBCS9266Harshit Raj
    Dara

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 3

    CourseName: Business Intelligence Lab Course Code: CSP-421

    Experiment:2.1

    Aim: Implement OLAP (Online Analytical Processing) for Business Insights.

    SOFTWARE REQUIRED: Python

    DESCRIPTION:

    The experiment involves setting up an OLAP system to analyze a dataset and extract meaningful insights. The
    dataset can be sourced from various data repositories within the organization, such as transactional databases or
    data warehouses. The OLAP system will enable users to explore the data from multiple dimensions or perspectives,
    facilitating deeper analysis and visualization.

    PSEUDO CODE/ALGORITHMS/FLOWCHART/STEPS:
    Step 1: Introduce and select the dataset.
    Step 2: Setup the OLAP system.
    Step 3: Import sqlite3 & panda’s libraries for performing the task.
    Step 4: Implementing logic or write code.
    Step 5: Perform the dimensional modelling and data loading. Step 6:
    Perform the OLAP analysis and explore the dataset.

    IMPLEMENTATION (CODE):
    import sqlite3
    conn = sqlite3.connect("sales_data.db") # Create an SQLite database cursor = conn.cursor()
    cursor.execute(''' # Create tables (sales and products) and insert sample
    data CREATE TABLE IF NOT EXISTS sales ( transaction_id
    INTEGER PRIMARY KEY AUTOINCREMENT, product_id
    INTEGER, sale_date DATE, quantity
    INTEGER,
    revenue REAL
    )

    Name: Abhigyan Pandey UID: 20BCS9334


    CourseName: Business Intelligence Lab Course Code: CSP-421

    ''')
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS products ( product_id
    INTEGER PRIMARY
    KEY, product_name TEXT
    )
    ''')
    cursor.executemany(''' # Insert some sample
    data INSERT INTO products (product_id, product_name) VALUES (?,
    ?)
    ''', [(1, 'Product A'), (2, 'Product B')])
    cursor.executemany('''
    INSERT INTO sales (product_id, sale_date, quantity, revenue) VALUES (?, ?, ?, ?) ''',
    [(1, '2023-01-01', 10, 100.0), (2, '2023-01-01', 5, 50.0)])
    cursor.executemany('''
    INSERT INTO sales (product_id, sale_date, quantity, revenue) VALUES (?, ?, ?, ?) ''',
    [(1, '2003-09-21', 50, 10.0), (3, '2003-09-21', 5, 50.0)])
    conn.commi t()
    conn.close()
    import pandas as pd
    conn = sqlite3.connect("sales_data.db") # Load data into a DataFrame query = '''
    SELECT p.product_name, strftime('%Y-%m', s.sale_date) as month, SUM(s.quantity) as total_quantity,
    SUM(s.revenue) as total_revenue
    FROM sales s
    JOIN products p ON s.product_id = p.product_id GROUP BY p.product_name, month
    ORDER BY p.product_name, month
    '''
    df = pd.read_sql_query(query, conn) conn.close()
    # Pivot the data for analysis

    Name: Abhigyan Pandey UID: 20BCS9334


    CourseName: Business Intelligence Lab Course Code: CSP-421

    pivot_table = pd.pivot_table(df, values=['total_quantity', 'total_revenue'], index='product_name',


    columns='month')
    # Display the result print(pivot_table)
    OUTPUT:

    LEARNING OUTCOMES:
    1. Understand the concept of OLAP.
    2. Demonstrate knowledge of analyze as a representation of dataset.
    3. Gain proficiency in programming and implementing OLAP algorithms using SQLite.
    4. Apply the measures to analyse dataset via OLAP.
    5.Visualize and communicate data patterns and distribution characteristics effectively.

    Name: Abhigyan Pandey UID: 20BCS9334

    You might also like