6/7/2025 M. Washington (Admin)

Quick Start (Personal Data Warehouse)

See the video at: Personal Data Warehouse: QuickStart

This tutorial demonstrates how to import multiple Excel files into the Personal Data Warehouse, join the data using a view, and create a report.

To begin, download and save the following sample files:

Create the Database

Note: For installation and upgrade details, see Installing and Upgrading.

Settings page

It's recommended to create separate databases for each project. This makes it easier to back up and restore data.

In the Personal Data Warehouse, go to the Settings page, select the Database Management tab, and click New Database.

New Database

Enter ACME as the database name and click Submit.

Submit New Database

Configure AI (Optional)

Enabling AI allows you to generate code for views, create KQL visualizations, and edit reports with assistance.

AI tab

On the Settings page, go to the AI tab, select an AI provider, configure the settings, and click Save.

Configure AI

Tip: For optimal performance, use OpenAI with the `03-mini` model or higher.

Import the Data

Tables Page

Navigate to the Tables page and click New.

New Table

Select Choose File.

Choose File

Locate the AcmeCustomers.xlsx file, and click Open.

The data will load into a preview grid. Make any necessary changes, then click Save.

Save Table

When prompted, select the ACME database, enter Customer as the table name, and click Submit.

Submit Customer Table

Repeat the steps for AcmeSales.xlsx, saving the table as Sales.

Create the View

Views Page

Go to the Views page and click New.

New View

Choose Python as the language and click Select.

Select Language

If AI is enabled, click the AI button.

AI Prompt

Enter the following prompt:

In the ACME database, join the ACMECustomer and ACMESales tables. Return the top 10 customers by total sales. Include customer name, total sales, and the number of orders.

The AI will generate code similar to this:

 		        # Import the pandas library used for data manipulation		
			import pandas as pd	
			
			# Define a function named load_data		
			def load_data():
		
			    # Construct file paths for the ACME Customer and Sales tables		
			    customers_filepath = '/ACME/Customer.parquet'		
			    sales_filepath = '/ACME/Sales.parquet'
			
			    # Read the ACME Customer table from its Parquet file			
			    df_customers = pd.read_parquet(customers_filepath)			
			    # Read the ACME Sales table from its Parquet file			
			    df_sales = pd.read_parquet(sales_filepath)			
			    # Merge the customer and sales data on the common 'CustomerId' column			
			    df_merged = pd.merge(df_customers, df_sales, on='CustomerId', how='inner')
				
			    # Convert the 'TotalDue' column to numeric, in case it is stored as a string		
			    df_merged['TotalDue'] = pd.to_numeric(df_merged['TotalDue'], errors='coerce')
				
			    # Group by customer information to calculate total sales and order count		
			    df_grouped = df_merged.groupby(['CustomerId', 'FirstName', 'LastName']).agg(
			        TotalSales=('TotalDue', 'sum'),      # Sum of TotalDue for each customer
			        OrderCount=('SalesOrderID', 'count')  # Count of orders for each customer		
			    ).reset_index()
				
			    # Create a full customer name by concatenating FirstName and LastName		
			    df_grouped['CustomerName'] = df_grouped['FirstName'] + ' ' + df_grouped['LastName']
				
			    # Sort the customers by TotalSales in descending order to get the highest total sales at the top					
			    df_sorted = df_grouped.sort_values(by='TotalSales', ascending=False)
				
			    # Select the top 10 customers		
			    top10_customers = df_sorted.head(10)
				
			    # Select only the desired columns for the final output		
			    result_df = top10_customers[['CustomerName', 'TotalSales', 'OrderCount']]
			
			    # Return the resulting DataFrame		
			    return result_df