"Ni har fått data som innehåller alla kommersiella inrikesflygningar i USA mellan 2003-2008; totalt cirka 4GB data. Studentportalen klarar inte så mycket data, så jag lade upp datat här istället: http://www.skg.nu/AirlineData.zip (denna web har inget certifikat, så den klarar tyvärr inte krypterad https). Tänk på att ni alltså behöver utrymme att lagra csv-filerna både när de hämtas och packas upp, men då de även ska tankas in i databasen så måste det även finnas utrymme för databas och dess mdf- och ldf-filer...
..."
- https://carlson-hoo.medium.com/data-expo-2009-airline-on-time-performance-analysis-bb2e5bcf3042
- http://stat-computing.org/dataexpo/2009/the-data.html
- START
- 0.Databaser
- I.SourceSystems
- II.StagingArea
- III.DW
- I.SourceSystems
- II.StagingArea
- III.DW
För varje DB skapade jag ett package i SSIS med sina respektiva dataflow.
OBS! Path av Data är: C:\Airlines\Airlines och för SuppData är: C:\Airlines\Suppdata
I varje package började jag med en Truncate table för att undvika dubbletter. Efter det skapade jag en ForeachLoop som anropar tabeller genom åren. I den här delen använde jag suggest type för att räkna och bestämma vilken datatype jag skulle använda. Totalt undersökes 200,000 rader för att nå lite mer noggrannhet.
Efter det körde jag samtidigt de andra tabellerna för att effektivisera processen. Jag gjorde en extra process i Data-plane för den tabellen hade citattecken i några kolumner medan andra inte hade det. Då behövde jag hantera Flat File Connection Manager Editor column delimiter: Mixed. Men den räckte inte och för att testa datan skapades en flat file med dem som inte följde strukturen genom att redirect row. Sedan valdes de igen och jag fixade datatyperna och raderade citattecken om dem forfarande fanns kvar. Likadan process gjorde jag med Carrier. Obs! Man behöver inte skapa en till .txt file, men jag ville prova det.
I varje package började jag med en Truncate table som undviker dubbletter. Efter det hämtade jag data från SourceSystems DB och började redigera enligt inlämningsuppgiften:
Jag ritade några boxar för att tydliggöra min process och jag försökte att i varje Package hålla en struktur med nivåer för att ha ordning. Grön box är transformationer, blå box är conditional split och lila är ”NA”- tabell.
Jag satte igång transformationen genom att kontrollera CancellationCode i Derived Column. Först nu kontrollerades alla null-värde och konverterades till ”N” och sen kontrollerades alla ”NA” till ”N”(Jag kunde göra det i samma element men jag ville göra så för att kunna använda Data viewer och kolla att allt stämde). Efter det använde jag conditional split för att filtrera, de som hade Null eller NA skulle gå till ”NA-Tabell” (lila boxen).
CRSDepTime, CRSArrTime, ArrTime, och DepTime förvandlades till datatyp TIME samt datum DATE och jag ville också att ha dem som INT för att kunna använda dem som en PK i nästa steg. Då först kontrollerades att de hade korrekt längd och att de var String (för att jag ville addera ':' i Derived Column och en ifsats med substring för att ha den rätta strukturen innan jag konverterade till tidtype). Jag gjorde något likande med Datum, som hanterades samtidigt som tidtype (men istället för att addera ':' adderade jag '-' så att datumet blev yyyy-mm-dd). De som hade en annan struktur plockades till min slasktabell "NA". Efter det var min data redo att skapa en ny tabell som var grunden till Faktatabell. Det var tid att transformera och fingöra de andra tabellerna som användes som dimensioner i nästa steg. I Carrier data kontrollerade jag att alla NULL-VÄRDE ändrades till 'NA'. Jag bytte namn från kolumn Code till CarrierID för att tydliggöra nästa steg med PK och FK. Jag hanterade på samma sätt Airport data och bytte namn på några kolumner till:
I Planedata skedde samma process och jag bytte några år som hade 'NONE' till 'NA'. RoutesData var en ny tabell som kom fram med hjälp av två Sqlcomander. Efter det upptäckte jag att mina null-värde i CancellationCode inte hade ändrats till "N" så jag behövde skriva en kod till i det element som heter "Detaljer" och nu var jag redo!
Jag började som vanligt att tömma tabellerna och den här gången behövde jag radera PK som jag framkallade när jag skapade nya tabeller. Jag använde Sequence Container för att kunna optimera processen med Factatabell och Dimensioner. Processen började med FactaTabell som inte hade RoutesID så då behövde jag använda en Lookup för att addera till FactaTabell kolumnen RoutesID Efter det lagrade jag tabellen som FactFlights och skapade en till kolumn med Indentity(1,1). Jag skapade DimDate med SQLkod. För att kunna göra en bra DimRoutes använde jag två lookup med Airports. Första lookup var för att hantera ursprung och andra var för att hantera destinationer.
När jag trodde att var redo började jag framkalla Foreing keys men upptäckte jag att DimPlanes inte var klar, dvs att inte alla *
flygplan* var i Factatabell och därför kunde jag inte skapa en relation mellan dem. Då kunde jag använda Lookup men det var enklare att skriva koden
Jag skapade en tabell i DB StagingArea och sen adderade jag kolumner för att kunna göra en union med min gamla tabell Planes och framkallade den nya tabellen som DimRoutes som hade koppling med min Faktatabell. Jag skrev några rader för att identifiera den nya datan som inte var med i DimPlane. Sedan framkallade jag alla mina FK med SQLkoden och mitt StarSchema ser ut så här:
