Start here
Lessons and reference
Look things up
Start here
SQL
Power Query
Data modeling
Power BI and DAX
Finance playbooks
Run queries
Build and check
Big Bear Lake is a small mountain resort city in California, and this is municipal-style operating data for it, the way a city manager, finance director or public works superintendent would see it: 311 requests, snow plow operations, fire and sheriff dispatch, building permits and code enforcement, a city-run water utility, the lodging tax on short-term rentals, parcels and owners, fund accounting with budgets, and council agendas and votes. The records are invented; the place and the kind of work are real. July-June fiscal year, with coordinates on almost everything.
City government operations (bigbear): A small resort city: 311 requests, snow operations, dispatch, permits and code enforcement, a water utility, the lodging tax, parcels and fund accounting, on a map and a July-June fiscal year.
Write each as a query in the SQL editor, or ask an AI assistant to walk you through it.
Server learnsql.cpetoday.com,1433, database bigbear, in Excel or Power BI (Import mode) or
DBeaver. These tables are in the dbo schema, so once you have picked the database you can use plain names such as agenda_items.
Download the Excel connection (.odc)
How to use it: download the file and open it. Excel opens it as a connection to bigbear
and asks for the SQL Server username and password from your CPE Today course.
The file holds no password. The first table is selected by default, and you can pick others from the Data window.
Setup details for other tools are on Connect.
Click a table to preview it in the Playground.
What each column means, its type and keys, and for codes and statuses the values seen in this copy. 12 of 61 tables are described so far. Read it as Markdown.
Click a table in the diagram above to jump to it.
Business licenses issued to businesses, by year.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
license_id |
int | PK | Unique license ID. | |
business_id |
int | FK to businesses | Business (businesses). | |
license_number |
varchar(14) | License number. | ||
license_year |
smallint | Year the license covers. | 2026 859 2027 854 2025 834 2024 822 2023 803 2022 781 | |
issue_date |
date | Date issued. | ||
expiration_date |
date | Expiration date. | 2026-06-30 859 2027-06-30 854 2025-06-30 834 2024-06-30 822 2023-06-30 803 2022-06-30 781 | |
annual_fee |
decimal | Fee for the year. | ||
paid_date |
date | Date the fee was paid. | ||
status |
varchar(12) | License status (see the values). | Expired 3908 Active 815 Unpaid 162 Closed 68 | |
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Businesses that hold a city license, with their category and location.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
business_id |
int | PK | Unique business ID. | |
business_name |
varchar(120) | Business name. | ||
business_category |
varchar(40) | Category (see the values). | ||
naics_code |
char(6) | NAICS industry code. | ||
parcel_id |
int | FK to parcels | Location parcel (parcels). | |
address_line1 |
varchar(100) | Not described yet | ||
city |
varchar(60) | Not described yet | ||
state |
char(2) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
opened_date |
date | Date opened. | ||
closed_date |
date | Date closed, if closed. | ||
employee_count |
smallint | Employees. | ||
is_home_based |
bit | 1 if the business operates from a home. | False 784 True 132 | |
is_seasonal |
bit | Not described yet | ||
county |
varchar(60) | Not described yet | ||
country |
char(2) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
geocode_quality |
varchar(24) | Not described yet | ||
full_address |
varchar(220) | Not described yet |
Emergency dispatch calls: type, priority, the times for each step of the response, and the disposition.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
call_id |
int | PK | Unique call ID. | |
incident_number |
varchar(14) | Incident number. | ||
call_type_id |
int | FK to cad_call_types | Type of call (cad_call_types). | |
agency |
varchar(10) | Responding agency. | Sheriff 21574 Fire/EMS 8875 | |
priority |
tinyint | Priority of the call (see the values). | 3 13813 2 9456 4 4180 1 3000 | |
received_at |
datetime2 | When the call was received. | ||
dispatched_at |
datetime2 | When a unit was dispatched. | ||
enroute_at |
datetime2 | When the unit left the station. | ||
onscene_at |
datetime2 | When the unit arrived on scene. | ||
cleared_at |
datetime2 | When the unit cleared. | ||
unit_id |
int | FK to cad_units | Unit that responded (cad_units). | |
jurisdiction |
varchar(30) | Not described yet | ||
neighborhood_id |
int | FK to neighborhoods | Not described yet | |
parcel_id |
int | FK to parcels | Not described yet | |
address_text |
varchar(120) | Not described yet | ||
disposition |
varchar(24) | How the call ended (see the values). | ||
weather_snowfall_in |
decimal | Not described yet | ||
geocode_quality |
varchar(24) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
The chart of accounts.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
account_id |
int | PK | Unique account ID. | |
account_number |
varchar(10) | Account number. | ||
account_name |
varchar(80) | Account name. | ||
account_type |
varchar(20) | Account type (see the values). | Revenue 20 Expense 19 Asset 3 Fund Balance 1 Liability 1 | |
category |
varchar(40) | Reporting category. | ||
parent_account_id |
int | FK to gl_accounts | Not described yet |
Debit and credit lines of the city's general ledger entries, tagged by fund, department and project.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
line_id |
int | PK | Unique line ID. | |
entry_id |
int | FK to gl_journal_entries | Journal entry (gl_journal_entries). | |
account_id |
int | FK to gl_accounts | Account (gl_accounts). | |
fund_id |
int | FK to funds | Fund (funds). | 1 6534 8 1714 9 941 7 499 3 419 2 314 4 232 5 174 10 174 6 130 |
department_id |
int | FK to departments | Department (departments). | |
project_id |
int | FK to capital_projects | Capital project (capital_projects). | |
debit |
decimal | Debit amount. | ||
credit |
decimal | Credit amount. |
Bills the city has received from its vendors (accounts payable).
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
invoice_id |
int | PK | Unique invoice ID. | |
vendor_id |
int | FK to vendors | Vendor (vendors). | |
invoice_number |
varchar(30) | Vendor's invoice number. | ||
invoice_date |
date | Invoice date. | ||
due_date |
date | Due date. | ||
paid_date |
date | Date paid. | ||
amount |
decimal | Invoice amount. | ||
account_id |
int | FK to gl_accounts | Account charged (gl_accounts). | |
fund_id |
int | FK to funds | Fund charged (funds). | 1 2640 8 624 9 209 7 174 4 70 3 61 2 18 10 10 |
department_id |
int | FK to departments | Not described yet | |
project_id |
int | FK to capital_projects | Not described yet | |
grant_id |
int | FK to grants | Not described yet | |
status |
varchar(20) | Invoice status (see the values). | Paid 3734 Open 60 Overdue 12 | |
description |
varchar(200) | Not described yet | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Land parcels in the city, identified by assessor's parcel number (APN): address, land use and building facts.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
parcel_id |
int | PK | Unique parcel ID. | |
apn |
varchar(16) | Assessor's parcel number. | ||
address_line1 |
varchar(100) | Street address. | ||
city |
varchar(60) | Not described yet | ||
state |
char(2) | Not described yet | ||
postal_code |
varchar(10) | Not described yet | ||
street_id |
int | FK to streets | Not described yet | |
neighborhood_id |
int | FK to neighborhoods | Neighborhood (neighborhoods). | 8 1565 10 921 3 905 6 864 9 780 2 726 12 631 1 587 11 542 5 486 4 379 7 257 |
zone_id |
int | FK to zoning_districts | Zoning district (zoning_districts). | 1 3245 4 1178 3 1153 8 777 7 762 2 645 9 388 6 213 5 211 10 71 |
owner_id |
int | FK to property_owners | Owner (property_owners). | |
land_use |
varchar(30) | Land use category (see the values). | Single Family Residence 3514 Condominium 1231 Vacant Residential Lot 1138 Commercial Retail 570 Lodging 509 Townhome 435 Multi-Family (2-4 units) 313 Restaurant or Bar 313 Office or Services 259 Multi-Family (5+ units) 160 Mixed Use 130 Institutional or Public 71 | |
lot_sqft |
int | Lot size in square feet. | ||
year_built |
smallint | Year the building was built. | ||
bedrooms |
tinyint | Not described yet | ||
bathrooms |
decimal | Not described yet | ||
building_sqft |
int | Building size in square feet. | ||
is_owner_occupied |
bit | 1 if the owner lives on the parcel. | False 7336 True 1307 | |
miles_to_lake |
decimal | Not described yet | ||
elevation_ft |
smallint | Not described yet | ||
wildfire_zone |
varchar(12) | Not described yet | ||
has_water_service |
bit | Not described yet | ||
county |
varchar(60) | Not described yet | ||
country |
char(2) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
geocode_quality |
varchar(24) | Not described yet | ||
full_address |
varchar(220) | Not described yet |
Building and other permits: type, parcel, valuation, fee and status.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
permit_id |
int | PK | Unique permit ID. | |
permit_number |
varchar(14) | Permit number. | ||
permit_type_id |
int | FK to permit_types | Permit type (permit_types). | |
parcel_id |
int | FK to parcels | Parcel (parcels). | |
neighborhood_id |
int | FK to neighborhoods | Not described yet | |
applicant_type |
varchar(24) | Who applied (see the values). | Contractor 3063 Owner 2155 Architect or Engineer 369 Tenant 257 | |
description |
varchar(200) | Not described yet | ||
valuation |
decimal | Declared project value. | ||
square_feet |
int | Not described yet | ||
applied_date |
date | Date applied for. | ||
issued_date |
date | Date issued. | ||
finaled_date |
date | Date the work was signed off. | ||
expires_date |
date | Not described yet | ||
status |
varchar(12) | Permit status (see the values). | Finaled 5261 Expired 237 Withdrawn 186 Cancelled 86 Issued 61 In Review 13 | |
fee_assessed |
decimal | Fee charged. | ||
fee_paid |
decimal | Fee paid. | ||
reviewer_employee_id |
int | FK to employees | Not described yet | |
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Resident requests to the city, such as potholes or illegal dumping, from submission to closure.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
request_id |
int | PK | Unique request ID. | |
request_number |
varchar(14) | Public request number. | ||
type_id |
int | FK to service_request_types | Kind of request (service_request_types). | |
channel |
varchar(12) | How it was submitted (see the values). | Phone 5735 Mobile app 4748 Web form 3730 Email 1742 Staff 1646 Counter 1019 | |
submitted_at |
datetime2 | When it was submitted. | ||
parcel_id |
int | FK to parcels | Parcel it relates to (parcels). | |
street_id |
int | FK to streets | Not described yet | |
neighborhood_id |
int | FK to neighborhoods | Neighborhood (neighborhoods). | |
address_text |
varchar(120) | Not described yet | ||
description |
varchar(200) | Not described yet | ||
reporter_type |
varchar(20) | Not described yet | ||
reporter_postal_code |
varchar(10) | Not described yet | ||
priority |
tinyint | Priority (see the values). | 3 9788 4 4728 2 2512 1 1592 | |
status |
varchar(12) | Request status (see the values). | Closed 16019 Duplicate 1377 Referred 845 Cancelled 293 Open 77 In Progress 9 | |
assigned_employee_id |
int | FK to employees | Employee assigned (employees). | |
acknowledged_at |
datetime2 | When the city acknowledged it. | ||
closed_at |
datetime2 | When it was closed. | ||
resolution |
varchar(60) | Not described yet | ||
duplicate_of_request_id |
int | FK to service_requests | Earlier request this duplicates, if any. | |
satisfaction |
tinyint | Satisfaction score given at closure, if any. | (blank) 15878 2 603 3 581 1 556 5 513 4 489 | |
storm_id |
int | FK to storm_events | Storm event it relates to (storm_events). | |
geocode_quality |
varchar(24) | Not described yet | ||
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Monthly transient occupancy tax (TOT) returns filed by short-term rental operators.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
filing_id |
int | PK | Unique filing ID. | |
tot_operator_id |
int | FK to tot_operators | Operator (tot_operators). | |
period_month |
date | Month the return covers. | ||
due_date |
date | Due date. | ||
filed_date |
date | Date filed. | ||
available_nights |
smallint | Not described yet | ||
occupied_nights |
smallint | Nights occupied. | ||
gross_receipts |
decimal | Gross rental receipts. | ||
exempt_receipts |
decimal | Receipts exempt from the tax. | ||
taxable_receipts |
decimal | Receipts subject to the tax. | ||
tax_rate |
decimal | Tax rate. | 0.1 50960 0.12 27039 | |
tax_due |
decimal | Tax owed. | ||
penalty |
decimal | Late penalty. | ||
paid_date |
date | Not described yet | ||
paid_amount |
decimal | Amount paid. | ||
status |
varchar(16) | Filing status (see the values). | Filed on time 70195 Late 5937 Audit adjusted 1396 Delinquent 370 Overdue 101 | |
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Monthly water bills: meter readings, usage, charges and payment status.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
bill_id |
int | PK | Unique bill ID. | |
account_id |
int | FK to utility_accounts | Utility account (utility_accounts). | |
bill_date |
date | Bill date. | ||
service_from |
date | Start of the billed period. | ||
service_to |
date | End of the billed period. | ||
billing_days |
smallint | Not described yet | ||
read_previous |
int | Previous meter reading. | ||
read_current |
int | Current meter reading. | ||
usage_ccf |
decimal | Water used, in hundred cubic feet. | ||
read_type |
varchar(10) | How the reading was taken (see the values). | Actual 185231 AMI 71909 Estimated 6481 | |
leak_flag |
bit | 1 if the usage looks like a leak. | False 262434 True 1187 | |
base_charge |
decimal | Not described yet | ||
usage_charge |
decimal | Not described yet | ||
surcharge |
decimal | Not described yet | ||
late_fee |
decimal | Not described yet | ||
total_amount |
decimal | Total billed. | ||
due_date |
date | Not described yet | ||
paid_date |
date | Not described yet | ||
paid_amount |
decimal | Amount paid. | ||
status |
varchar(12) | Bill status (see the values). | Paid 257314 Open 4475 Written off 1536 Delinquent 296 | |
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |
Crew work orders for city assets and streets, from the request that started them to completion.
| Column | Type | Key | Meaning | Values seen |
|---|---|---|---|---|
work_order_id |
int | PK | Unique work order ID. | |
work_order_number |
varchar(14) | Work order number. | ||
request_id |
int | FK to service_requests | Service request that started it (service_requests). | |
asset_id |
int | FK to assets | Asset ID the crew worked on. | |
street_id |
int | FK to streets | Not described yet | |
neighborhood_id |
int | FK to neighborhoods | Not described yet | |
department_id |
int | FK to departments | Department (departments). | 5 6556 6 5971 |
work_type |
varchar(40) | Type of work (see the values). | ||
is_preventive |
bit | 1 for scheduled preventive work. | True 7159 False 5368 | |
crew |
varchar(24) | Not described yet | ||
priority |
tinyint | Priority (see the values). | 4 7697 3 3160 1 1098 2 572 | |
status |
varchar(12) | Order status (see the values). | Completed 12394 Cancelled 95 Open 26 Scheduled 9 In Progress 3 | |
opened_date |
date | Date opened. | ||
scheduled_date |
date | Not described yet | ||
completed_date |
date | Date completed. | ||
labor_hours |
decimal | Labor hours. | ||
labor_cost |
decimal | Labor cost. | ||
material_cost |
decimal | Material cost. | ||
equipment_cost |
decimal | Not described yet | ||
storm_id |
int | FK to storm_events | Not described yet | |
latitude |
decimal | Not described yet | ||
longitude |
decimal | Not described yet | ||
fiscal_year |
smallint | Not described yet | ||
fiscal_quarter |
tinyint | Not described yet | ||
fiscal_period |
tinyint | Not described yet |