Data Analyst Portfolio Project #1 | Build a Database | Develop SQL | Create a Dashboard

Introduction

In this tutorial, you'll learn how to create a data analyst portfolio project from scratch. This project involves building a database, analyzing data with SQL, connecting Power BI to your database, and visualizing data through a dashboard. It's suitable for beginners and provides a solid foundation for showcasing your skills in data analysis.

Step 1: Build a Database

To start, you need to create a database that will house your data.

1. Download SQL Server Management Studio (SSMS):

   • Go to the official Microsoft site to download SSMS: Download SSMS.
   • Install the software on your computer.

2. Create a New Database:

   • Open SSMS and connect to your SQL Server instance.
   • Right-click on the "Databases" folder in the Object Explorer.
   • Choose "New Database" and provide a name for your database (e.g., HotelRevenueDB).
   • Click "OK" to create the database.

3. Import the Dataset:

   • Download the Hotel Revenue dataset from here.
   • In SSMS, right-click on your database, select "Tasks," then "Import Data."
   • Follow the Import Wizard to load the dataset into your database.

Step 2: Analyze and Retrieve Data with SQL

Once your database is set up, you can start analyzing the data using SQL queries.

1. Write Basic SQL Queries:

   • Open a new query window in SSMS.
   • Start with simple SELECT statements to retrieve data. For example:

     SELECT * FROM HotelRevenue;
     

2. Perform Data Analysis:

   • Use aggregate functions to summarize data. For example:

     SELECT 
       SUM(Revenue) AS TotalRevenue,
       AVG(OccupancyRate) AS AverageOccupancy
     FROM HotelRevenue;
     

3. Filter and Sort Data:

   • Add WHERE clauses to filter results:

     SELECT * FROM HotelRevenue WHERE City = 'New York';
     

   • Use ORDER BY to sort the results:

     SELECT * FROM HotelRevenue ORDER BY Revenue DESC;
     

Step 3: Connect Power BI to Your Database

Next, you will connect Power BI to your SQL database for visualization.

1. Open Power BI Desktop:

   • Download and install Power BI from the official site if you haven't already.

2. Connect to SQL Server:

   • In Power BI, click on "Get Data" and select "SQL Server."
   • Enter your server name and database name, then click "OK."

3. Load Data into Power BI:

   • Once connected, select the tables you want to import.
   • Click "Load" to bring the data into Power BI.

Step 4: Visualize Data in Power BI

Now that your data is in Power BI, you can create visualizations.

1. Create a New Report:

   • In Power BI, click on "Report" view to start building your dashboard.

2. Add Visualizations:

   • Use the visualizations pane to drag and drop charts, tables, and other visuals onto your report.
   • Customize each visual by selecting it and using the formatting options.

3. Publish Your Dashboard:

   • Once you are satisfied with your visuals, click on "Publish" to share your dashboard to the Power BI service.

Conclusion

In this tutorial, you learned how to build a database, analyze data with SQL, and create a dashboard in Power BI. This project not only enhances your skills but also adds a valuable piece to your portfolio. As next steps, consider exploring more complex SQL queries, experimenting with different visualizations in Power BI, or tackling additional data analysis projects to further hone your skills.