Å lage mitt eige eksempeldatasett med Visual Studio, T-SQL og PowerShell

Du har høyrt om AdventureWorks, ikkje sant? Det er eksempeldatabasen for SQL Server som Microsoft har publisert, og som òg er tilgjengeleg som døme for Azure SQL Database. For nokre månader sidan fekk eg ideen om å lage mitt eige eksempeldatasett som eg kunne bruke i komande prosjekt. Det tok litt lengre tid enn planlagt å få ferdig – livet kom i vegen – men no er det endeleg klart.

Dette blogginnlegget gir ei overordna innføring i korleis det blei laga. For meir teknisk informasjon og kjeldekode, sjå GitHub-repositoriet mitt.

Lat oss først sjå på caset bak datasettet: WingIt Airlines. WingIt Airlines er eit fiktivt, mellomstort langdistanseflyselskap som flyg mellom basane sine i San Diego i California og London Heathrow i Storbritannia. Hovudkontoret ligg i Los Angeles i California. Målet er å lage ein rapporteringsdatabase som kan gi både interne og eksterne interessentar betre innsikt i korleis flyselskapet presterer. For enkelheits skuld ser databaseskjemaet om lag slik ut:

Med SQL Database Projects i Visual Studio bygde eg skjemaet over som ei DACPAC-løysing. Ho kan lastast ned frå repository-lenkja øvst på sida. Eit skjema åleine er likevel ikkje nok; for å gjere databasen meir realistisk må constraints som foreign keys, check constraints og default values handhevast. Alt dette ligg i DACPAC-en. Eg har òg skrive scalar functions som mellom anna reknar ut provisjonen eit reisebyrå får per bestilling, og kontrollerer at det er nok kapasitet før transaksjonen commits til databasen. Arbeidet var stort sett rett fram, og gav meg nyttig erfaring med scalar functions og ulike måtar å rekne ut verdiar i kolonnar på. I Airport-tabellen bruker eg dessutan datatypen GEOGRAPHY til å lagre den nøyaktige plasseringa til kvar flyplass, basert på lengde- og breiddegrad, slik at flydistansar kan reknast ut. Kjeldekoden finn du her.

Skjemaet er på plass, og no må det fyllast med data. Vi kunne ha brukt T-SQL-skript som blir køyrde manuelt i eit verktøy som SQL Server Management Studio (SSMS), men ei av utfordringane eg sette meg i dette prosjektet var å automatisere genereringa av datasettet så mykje som mogleg.

Korleis gjorde eg det? Eg laga framleis dei same T-SQL-skripta, men lèt dei no køyre via PowerShell med SqlServer-modulen installert. Du må kanskje òg installere NuGet package provider dersom han ikkje alt er tilgjengeleg.

Du finn modulen i PowerShell Gallery:

Install-Module SqlServer

Du må òg opprette ein SQLAuth-brukar med rette rettar i databasen. Brukaren skal vere medlem av db_spexecutor, db_datareader og db_datawriter. Skripta føreset dessutan at SQL-instansen er standardinstansen, altså MSSQLSERVER. Bruker du ein namngitt instans, må du tilpasse verdien som blir sendt til parameteren -ServerInstance.

Først køyrer vi skriptet som fyller oppslagsdataa:

Set-Location '<path of cloned repository>\sample-datasets\WingItAirlines-Reporting\Data Population Scripts'
.\PopulateLookupData.ps1

Du blir beden om brukarnamn og passord for SQL Authentication-brukaren. Deretter fyller skriptet desse tabellane. Du kan endre datointervallet i FlightSchedule-skriptet for å lage eit mindre eller større datasett.

Agency
AgencyUser
AgencyCommission
Airplane
Airport
PassengerFareRate
TicketStatus
TicketType
Route
FlightSchedule

Dette bør vere ferdig i løpet av eit par minutt.

Deretter fyller vi faktadataa med:

.\CreateBulkBookings.ps1

Du blir beden om brukarnamn og passord for SQL Authentication-brukaren, og skriptet fyller deretter TicketSale-tabellen.

Lat oss sjå på kva skriptet gjer:

$databaseName = 'WingItAirlines-Reporting'

# Dette er vanlegvis avhengig av kor mykje reknekraft vertsmaskina har
$maxConcurrentJobs = 2
$sqlServerCredential = Get-Credential

# Hent ID-en til reisebyråbrukaren som sel billettar på business class
$businessClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Christopher Columbus' "`
-ServerInstance $env:COMPUTERNAME

$businessClassAgencyUser = $businessClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for business class
$businessClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Business Class' "`
-ServerInstance $env:COMPUTERNAME

$businessClassTicketType = $businessClassTicketType.Ticket_Type_Id

# Hent ID-en til reisebyråbrukaren som sel billettar på economy class
$economyClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Sunchasers Ltd' "`
-ServerInstance $env:COMPUTERNAME

$economyClassAgencyUser = $economyClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for economy class
$economyClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Economy Class' "`
-ServerInstance $env:COMPUTERNAME

$economyClassTicketType = $economyClassTicketType.Ticket_Type_Id

# Hent alle flygingar utan bestillingar i TicketSales. Denne funksjonen gjer at du kan halde fram der du slapp, utan å slette alt og byrje på nytt
$flightSchedule = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-OutputAs DataTables `
-Query "SELECT `
[Flight_Schedule_Id] `
FROM [dbo].[FlightSchedule] `
WHERE NOT EXISTS ( `
SELECT `
[Flight_Schedule_Id] `
FROM [dbo].[TicketSale])" `
-ServerInstance $env:COMPUTERNAME

$flightSchedule = $flightSchedule.Flight_Schedule_Id

# Hent ID-en til reisebyråbrukaren som sel billettar på premium economy class
$premiumEconomyClassAgencyUser = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT TOP 1`
AU.[Agency_User_Id] `
FROM [dbo].[AgencyUser] AU  `
INNER JOIN [dbo].[Agency] A ON AU.[Agency_Id] = A.[Agency_Id] `
WHERE A.[Agency_Name] = 'Suntours Vacaction LLC' "`
-ServerInstance $env:COMPUTERNAME

$premiumEconomyClassAgencyUser = $premiumEconomyClassAgencyUser.Agency_User_Id

# Hent ID-en til billettypen for premium economy class
$premiumEconomyClassTicketType = `
Invoke-Sqlcmd `
-Credential $sqlServerCredential `
-Database $databaseName `
-Query "SELECT `
[Ticket_Type_Id] `
FROM [dbo].[TicketType]  `
WHERE [Ticket_Type] = 'Premium Economy Class' "`
-ServerInstance $env:COMPUTERNAME

$premiumEconomyClassTicketType = $premiumEconomyClassTicketType.Ticket_Type_Id

# For kvar flyging i FlightSchedule-lista som ikkje allereie har bestillingar
foreach($flight in $flightSchedule)
{
    # Kontroller om det køyrer nokre bakgrunnsjobbar
    $running = @(Get-Job -State Running)
    {
        # Kontroller om grensa for maksimalt tal bakgrunnsjobbar er nådd; vent i så fall til ein plass blir ledig
        if($running.Count -ge $maxConcurrentJobs)
        {
            $null = $running | Wait-Job Any
        }
            # Start ein bakgrunnsjobb som opprettar bestillingar for flyginga med verdiar frå tidlegare spørringar
            Start-Job -Name CreateBulkBookings -ArgumentList $businessClassAgencyUser, $businessClassTicketType, $databaseName, $economyClassAgencyUser, $economyClassTicketType, $flight, $premiumEconomyClassAgencyUser, $premiumEconomyClassTicketType, $sqlServerCredential -ScriptBlock {
                param
                (
                    [int]$businessClassAgencyUser,
                    [int]$businessClassTicketType,
                    [string]$databaseName,
                    [int]$economyClassAgencyUser,
                    [int]$economyClassTicketType,
                    [int]$flight,
                    [int]$premiumEconomyClassAgencyUser,
                    [int]$premiumEconomyClassTicketType,
                    [pscredential]$sqlServerCredential
                )

                # Definer ein funksjon som kan hente tilfeldige datoar mellom to oppgitte datoar
                function Get-RandomDateBetween{
                    [Cmdletbinding()]
                    param(
                        [parameter(Mandatory=$True)][DateTime]$StartDate,
                        [parameter(Mandatory=$True)][DateTime]$EndDate
                        )

                    process{
                       return Get-Random -Minimum $StartDate.Ticks -Maximum $EndDate.Ticks | Get-Date -Format "dd/MM/yyyy HH:mm:ss"
                    }
                }

                # Definer ein funksjon som kan hente tilfeldige klokkeslett mellom to oppgitte tidspunkt
                function Get-RandomTimeBetween{
                       [Cmdletbinding()]
                      param(
                          [parameter(Mandatory=$True)][string]$StartTime,
                          [parameter(Mandatory=$True)][string]$EndTime
                          )
                      begin{
                          $minuteTimeArray = @("00","15","30","45")
                      }
                      process{
                          $rangeHours = @($StartTime.Split(":")[0],$EndTime.Split(":")[0])
                          $hourTime = Get-Random -Minimum $rangeHours[0] -Maximum $rangeHours[1]
                          $minuteTime = "00"
                          if($hourTime -ne $rangeHours[0] -and $hourTime -ne $rangeHours[1]){
                              $minuteTime = Get-Random $minuteTimeArray
                              return "${hourTime}:${minuteTime}"
                          }
                          elseif ($hourTime -eq $rangeHours[0]) { # hour is the same as the start time so we ensure the minute time is higher
                              $minuteTime = $minuteTimeArray | ?{ [int]$_ -ge [int]$StartTime.Split(":")[1] } | Get-Random # Pick the next quarter
                              # Viss ingen heile kvarter er tilgjengelege (t.d. 09:50), går vi vidare til neste time (10:00)
                              return (.{If(-not $minuteTime){ "${[int]hourTime+1}:00" }else{ "${hourTime}:${minuteTime}" }})

                          }else { # hour is the same as the end time
                              # Ved å sortere arrayet blir 00 valt viss det ikkje finst eit nærliggjande heilt kvarter
                              $minuteTime = $minuteTimeArray | Sort-Object -Descending | ?{ [int]$_ -le [int]$EndTime.Split(":")[1] } | Get-Random
                              return "${hourTime}:${minuteTime}"
                          }
                      }
                  }

                # Hent flyet som er tildelt flyginga; dette trengst for å kontrollere om bestillinga kan gjennomførast ut frå kapasiteten i allereie selde billettar
                $airplaneFlight = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Airplane_Id] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $airplaneFlight = $airplaneFlight.Airplane_Id

                # Hent dato og klokkeslett for planlagd avgang
                $dateTimeFlight = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Scheduled_Date_Time_Of_Departure_UTC] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $dateTimeFlight = $dateTimeFlight.Scheduled_Date_Time_Of_Departure_UTC

                # Hent ruta for flyginga
                $routeToBook = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                [Route_Id] `
                FROM [dbo].[FlightSchedule] `
                WHERE [Flight_Schedule_Id] = $flight" `
                -ServerInstance $env:COMPUTERNAME

                $routeToBook = $routeToBook.Route_Id

                # Sørg for at minst 65 % av setene på business class for flyginga er selde
                $businessClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $businessClassBookingsCapacityUtilisationRounded = [Math]::Round($businessClassBookingsCapacityUtilisation, 2)

                # Rekn ut kor mange bestillingar på business class som skal opprettast, basert på den førre verdien
                $businessClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Business_Class_Seat_Count] * $businessClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $businessClassBookingsToCreate = $businessClassBookingsToCreate.Bookings_To_Create

                # Sørg for at minst 65 % av setene på economy class for flyginga er selde
                $economyClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $economyClassBookingsCapacityUtilisationRounded = [Math]::Round($economyClassBookingsCapacityUtilisation, 2)

                # Rekn ut kor mange bestillingar på economy class som skal opprettast, basert på den førre verdien
                $economyClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Economy_Class_Seat_Count] * $economyClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $economyClassBookingsToCreate = $economyClassBookingsToCreate.Bookings_To_Create

                # Sørg for at minst 65 % av setene på premium economy class for flyginga er selde
                $premiumEconomyClassBookingsCapacityUtilisation = Get-Random -Minimum 0.65 -Maximum 1
                $premiumEconomyClassBookingsCapacityUtilisationRounded = [Math]::Round($premiumEconomyClassBookingsCapacityUtilisation, 2)

                # Rekn ut kor mange bestillingar på premium economy class som skal opprettast, basert på den førre verdien
                $premiumEconomyClassBookingsToCreate = `
                Invoke-SqlCmd `
                -Credential $sqlServerCredential `
                -Database $databaseName `
                -Query "SELECT `
                CAST(ROUND([Premium_Economy_Class_Seat_Count] * $premiumEconomyClassBookingsCapacityUtilisationRounded, 0) AS INT) AS [Bookings_To_Create] `
                FROM [dbo].[Airplane] `
                WHERE [Airplane_Id] = $airplaneFlight" `
                -ServerInstance $env:COMPUTERNAME

                $premiumEconomyClassBookingsToCreate = $premiumEconomyClassBookingsToCreate.Bookings_To_Create

                # Opprett bestillingar på business class
                while($businessClassBookingsToCreate -gt 0)
                {
                    # Ei bestilling kan opprettast opptil 272 dagar før flyginga og seinast éin dag før, med funksjonen Get-RandomDateBetween som blei definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # Ei bestilling kan berre opprettast i arbeidstida. Det er ikkje heilt realistisk, men eg ville ikkje at dataa skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $businessClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $businessClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $businessClassBookingsToCreate = $businessClassBookingsToCreate - 1
                }

                # Opprett bestillingar på economy class
                while($economyClassBookingsToCreate -gt 0)
                {
                    # Ei bestilling kan opprettast opptil 272 dagar før flyginga og seinast éin dag før, med funksjonen Get-RandomDateBetween som blei definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # Ei bestilling kan berre opprettast i arbeidstida. Det er ikkje heilt realistisk, men eg ville ikkje at dataa skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $economyClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $economyClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $economyClassBookingsToCreate = $economyClassBookingsToCreate -1
                }

                # Opprett bestillingar på premium economy class
                while($premiumEconomyClassBookingsToCreate -gt 0)
                {
                    # Ei bestilling kan opprettast opptil 272 dagar før flyginga og seinast éin dag før, med funksjonen Get-RandomDateBetween som blei definert over
                    $dateOfBooking = Get-RandomDateBetween -StartDate $dateTimeFlight.AddDays(-272) -EndDate $dateTimeFlight.AddDays(-1)
                    $dateOfBooking = [Datetime]::ParseExact($dateOfBooking, 'dd/MM/yyyy HH:mm:ss', $null)

                    # Ei bestilling kan berre opprettast i arbeidstida. Det er ikkje heilt realistisk, men eg ville ikkje at dataa skulle bli for tilfeldige
                    $timeOfBooking = Get-RandomTimeBetween -StartTime "08:00" -EndTime "18:00"
                    $timeOfBooking = [System.Timespan]::Parse($timeOfBooking)

                    [datetime]$dateTimeTicketSale = $dateOfBooking.Add($timeOfBooking)

                    Invoke-SqlCmd `
                    -Credential $sqlServerCredential `
                    -Database $databaseName `
                    -Query " `
                    DECLARE @ticketSaleDateTime DATETIME2 `
                    SET @ticketSaleDateTime = (SELECT CAST('$dateTimeTicketSale' AS DATETIME2)) `
                    DECLARE @travelDateTime DATETIME2 `
                    SET @travelDateTime = (SELECT CAST('$dateTimeFlight' AS DATETIME2)) `
                    `
                    EXEC [dbo].[CreateBooking] @agencyUserId = $premiumEconomyClassAgencyUser, `
                    @routeId = $routeToBook, `
                    @ticketSaleDateTime = @ticketSaleDateTime, `
                    @ticketTypeId = $premiumEconomyClassTicketType, `
                    @travelDateTime = @travelDateTime" `
                    -ServerInstance $env:COMPUTERNAME

                    $premiumEconomyClassBookingsToCreate = $premiumEconomyClassBookingsToCreate -1
                }
        }
    }
}

# Vent til alle bakgrunnsjobbar er ferdige før skriptet blir avslutta
Get-Job | Wait-Job

Full ansvarsfråskriving: Dette er sannsynlegvis ein svært lite effektiv måte å generere denne mengda data på. For betre yting burde det truleg køyrast i eit fleirtråda verktøy som Azure Databricks.

Eg lét dette køyre på ein Azure VM i eit par månader. Opphavleg brukte eg ti samtidige jobbar, men det gjekk for sakte, så eg reduserte talet.

Ressursar

Dersom du ikkje vil følgje alle stega og vente på at eit større datasett skal genererast, har eg publisert ein databasebackup som kan takast i bruk med éin gong. Du kan laste han ned her. Merk at du må bruke SQL Server 2022, sidan backupen blei oppretta på ein SQL Server 2022-instans.

GitHub repository

Takk

Takk til @emyann på GitHub for Gist-startkoden for å generere tilfeldige datoar og klokkeslett i PowerShell. Eg har tilpassa han i koden som opprettar bulkbestillingar.

Takk til @farkoo på GitHub for eksempel-koden for å kontrollere kapasiteten på idrettsarenaer. Han gav inspirasjon til logikken for flykapasitet.

Å lage mitt eige eksempeldatasett med Visual Studio, T-SQL og PowerShell

Ressursar

Takk

Relatert innhald

Del 1: Rapportering på Log Analytics-data | Setje opp det første miljøet