Data Analysis and Power BI Project

Overview

• Project focuses on building a database from Excel files, connecting to Power BI, and creating a dashboard.
• Central analysis question: Recommendations on raising prices next year.

Data Analysis Workflow

1. Create database
2. Develop SQL Creator
3. Connect Power BI to database
4. Build dashboard in Power BI
5. Answer analysis question

Dashboard Request from Stakeholders

• Request details:
  • Key performance metrics:
    • Hourly revenue analysis
    • Profit and revenue trends
    • Seasonal revenue
    • Rider demographics
  • Follow company colors and design aesthetics
  • Urgent delivery requested

Software Installation

• Install SQL Server and SQL Server Management Studio (SSMS):
  • Use Express version (free).
  • Basic installation recommended.

Steps to Install SQL Server Management Studio

1. Download SQL Server Management Studio (SSMS).
2. Install SSMS (accept agreements, initiate install).
3. Launch SSMS and connect to the database engine:
   • Use Windows authentication (no password).
   • Trust service certificate if prompted.

Creating the Database

• Right-click in the database section to create a new database (e.g., "Bike Data").
• Add tables to the database by importing flat files (Excel).

Importing Data Steps

1. Right-click on the database and select "Import Flat File".
2. Navigate to the Excel file location and follow the prompts to import.
3. Preview data and adjust settings as needed.
4. Complete the import and refresh the database to see the new table.

SQL Queries

• SQL query basics covered:
  • Selecting data from tables using SELECT command.
  • Using JOINs to combine tables (INNER JOIN, LEFT JOIN, UNION).
  • Creating Common Table Expressions (CTEs).
  • Calculating revenue and profit based on imported data.

Building the Power BI Dashboard

1. Download and install Power BI Desktop.
2. Import data from SQL Server:
   • Use Windows credentials for access.
   • Choose import mode for data.

Dashboard Components

• Dashboard must include:
  • Header with company logo
  • Animated GIFs for visual interest
  • Tables, charts, and metrics for key data points
  • Revenue and profit analysis, seasonal data, rider demographics.

Visual Formatting

• Use matrix for hour and revenue values.
• Implement conditional formatting to enhance visuals.
• Create slicers for data filtering (e.g., year).
• Ensure colors and style align with company branding.

Analysis and Recommendations

• Analyze revenue impact of potential price increases:
  • Calculate price elasticity using changes in demand and price.
  • Recommendation to increase prices conservatively by 10-15%.
• Monitor market response to changes and adjust strategies accordingly.

Conclusion

• Completed workflow for building a comprehensive data analysis project.
• Encouraged feedback and suggestions for dashboard improvements.