-
Notifications
You must be signed in to change notification settings - Fork 0
/
Contoso 10K.kql
33 lines (33 loc) · 8.79 KB
/
Contoso 10K.kql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
// Contoso 10K
.execute database script <|
.drop tables (Data_CurrencyExchange, Data_Customer, Data_Date, Data_GeoLocations, Data_OrderRows, Data_Orders, Data_Product, Data_Store) ifexists
.create tables
Data_CurrencyExchange (Date: datetime, FromCurrency: string, ToCurrency: string, Exchange: real),
Data_Customer (CustomerKey: int, Gender: string, Title: string, GivenName: string, MiddleInitial: string, Surname: string, StreetAddress: string, City: string, State: string, StateFull: string, ZipCode: string, Country: string, CountryFull: string, Birthday: datetime, Age: int, Occupation: string, Company: string, Vehicle: string, Latitude: real, Longitude: real, Continent: string),
Data_Date (Date: datetime, DateKey: int, Year: int, ['Year Quarter']: string, ['Year Quarter Number']: int, Quarter: string, ['Year Month']: string, ['Year Month Short']: string, ['Year Month Number']: int, Month: string, ['Month Short']: string, ['Month Number']: int, ['Day of Week']: string, ['Day of Week Short']: string, ['Day of Week Number']: int, ['Working Day']: bool, ['Working Day Number']: int),
Data_GeoLocations (GeoLocationKey: int, CountryCode: string, Country: string, StateCode: string, State: string, NumCustomers: int),
Data_OrderRows (OrderKey: long, ['Line Number']: int, ProductKey: int, Quantity: int, ['Unit Price']: decimal, ['Net Price']: decimal, ['Unit Cost']: decimal),
Data_Orders (OrderKey: long, CustomerKey: int, StoreKey: int, ['Order Date']: datetime, ['Delivery Date']: datetime, ['Currency Code']: string),
Data_Product (ProductKey: int, ['Product Code']: string, ['Product Name']: string, Manufacturer: string, Brand: string, Color: string, ['Weight Unit Measure']: string, Weight: real, ['Unit Cost']: decimal, ['Unit Price']: decimal, ['Subcategory Code']: string, Subcategory: string, ['Category Code']: string, Category: string),
Data_Store (StoreKey: int, ['Store Code']: int, Country: string, State: string, Name: string, ['Square Meters']: int, ['Open Date']: datetime, ['Close Date']: datetime, Status: string)
.drop functions (['Currency Exchange'], Customer, Date, Product, Sales, Store, ['Currency Exchange Perpetual'], ['Customer Perpetual'], ['Date Perpetual'], ['Product Perpetual'], ['Sales Perpetual'], ['Store Perpetual']) ifexists
.create function ['Currency Exchange']() {Data_CurrencyExchange | project Date, FromCurrency, ToCurrency, Exchange}
.create function Customer() {Data_Customer | project CustomerKey, Gender, Name = strcat(GivenName, ' ', Surname), Address = StreetAddress, City, ['State Code'] = State, State = StateFull, ['Zip Code'] = ZipCode, ['Country Code'] = Country, Country = CountryFull, Continent, Age}
.create function Date() {Data_Date | project Date, Year, ['Year Quarter'], ['Year Quarter Number'], Quarter, ['Year Month'], ['Year Month Short'], ['Year Month Number'], Month, ['Month Short'], ['Month Number'], ['Day of Week'], ['Day of Week Short'], ['Day of Week Number'], ['Working Day'], ['Working Day Number']}
.create function Product() {Data_Product | project ProductKey, ['Product Code'], ['Product Name'], Manufacturer, Brand, Color, ['Weight Unit Measure'], Weight, ['Unit Cost'], ['Unit Price'], ['Subcategory Code'], Subcategory, ['Category Code'], Category}
.create function Sales() {Data_Orders | join kind=leftouter Data_OrderRows on OrderKey | join kind=leftouter (Data_CurrencyExchange | where FromCurrency == 'USD') on $left.['Order Date'] == $right.Date, $left.['Currency Code'] == $right.ToCurrency | project ['Order Number'] = OrderKey, ['Line Number'], ['Order Date'], ['Delivery Date'], CustomerKey, StoreKey, ProductKey, Quantity, ['Unit Price'], ['Net Price'], ['Unit Cost'], ['Currency Code'], ['Exchange Rate'] = Exchange}
.create function Store() {Data_Store | project StoreKey, ['Store Code'], Country, State, Name, ['Square Meters'], ['Open Date'], ['Close Date'], Status}
.create function ['Currency Exchange Perpetual']() {let maxDate = toscalar(Data_Orders | summarize max(['Order Date'])); let dayOffset = datetime_diff('day', now(), maxDate); Data_CurrencyExchange | project Date = datetime_add('day', dayOffset, Date), FromCurrency, ToCurrency, Exchange}
.create function ['Customer Perpetual']() {Data_Customer | project CustomerKey, Gender, Name = strcat(GivenName, ' ', Surname), Address = StreetAddress, City, ['State Code'] = State, State = StateFull, ['Zip Code'] = ZipCode, ['Country Code'] = Country, Country = CountryFull, Continent, Birthday, Age}
.create function ['Date Perpetual']() {let minDate = toscalar(Data_Date | summarize min(['Date'])); let maxDate = toscalar(Data_Date | summarize max(['Date'])); let yearOffset = datetime_diff('year', minDate, maxDate) - 1; range ['Date'] from startofyear(now(), yearOffset) to endofyear(now(), 0) step 1d | extend ['Year'] = datetime_part('year', ['Date']), ['Quarter Number'] = datetime_part('quarter', ['Date']), ['Month Number'] = datetime_part('month', ['Date']), ['Day of Week Number'] = toint(dayofweek(['Date']) / 1d) | extend ['Year Quarter Number'] = (['Year'] * 4) + ['Quarter Number'], ['Year Month Number'] = (['Year'] * 12) + ['Month Number'], ['Quarter'] = strcat("Q", tostring(['Quarter Number'])), ['Month'] = case(['Month Number'] == 1, "Jan", ['Month Number'] == 2, "Feb", ['Month Number'] == 3, "Mar", ['Month Number'] == 4, "Apr", ['Month Number'] == 5, "May", ['Month Number'] == 6, "Jun", ['Month Number'] == 7, "Jul", ['Month Number'] == 8, "Aug", ['Month Number'] == 9, "Sep", ['Month Number'] == 10, "Oct", ['Month Number'] == 11, "Nov", ['Month Number'] == 12, "Dec", "--"), ['Day of Week'] = case(['Day of Week Number'] == 0, "Sun", ['Day of Week Number'] == 1, "Mon", ['Day of Week Number'] == 2, "Tue", ['Day of Week Number'] == 3, "Wed", ['Day of Week Number'] == 4, "Thu", ['Day of Week Number'] == 5, "Fri", ['Day of Week Number'] == 6, "Sat", "--") | extend ['Year Quarter'] = strcat(Quarter, "-", tostring(Year)), ['Year Month'] = strcat(Month, " ", tostring(Year)) | project ['Date'], ['Year'], ['Year Quarter'], ['Year Quarter Number'], ['Quarter'], ['Year Month'], ['Year Month Number'], ['Month'], ['Month Number'], ['Day of Week'], ['Day of Week Number']}
.create function ['Product Perpetual']() {Data_Product | project ProductKey, ['Product Code'], ['Product Name'], Manufacturer, Brand, Color, ['Weight Unit Measure'], Weight, ['Unit Cost'], ['Unit Price'], ['Subcategory Code'], Subcategory, ['Category Code'], Category}
.create function ['Sales Perpetual']() {let maxDate = toscalar(Data_Orders | summarize max(['Order Date'])); let dayOffset = datetime_diff('day', now(), maxDate); Data_Orders | join kind=leftouter Data_OrderRows on OrderKey | join kind=leftouter (Data_CurrencyExchange | where FromCurrency == 'USD') on $left.['Order Date'] == $right.Date, $left.['Currency Code'] == $right.ToCurrency | project ['Order Number'] = OrderKey, ['Line Number'], ['Order Date'] = datetime_add('day', dayOffset, ['Order Date']), ['Delivery Date'] = datetime_add('day', dayOffset, ['Delivery Date']), CustomerKey, StoreKey, ProductKey, Quantity, ['Unit Price'], ['Net Price'], ['Unit Cost'], ['Currency Code'], ['Exchange Rate'] = Exchange}
.create function ['Store Perpetual']() {let maxDate = toscalar(Data_Orders | summarize max(['Order Date'])); let yearOffset = datetime_diff('year', now(), maxDate); Data_Store | project StoreKey, ['Store Code'], Country, State, Name, ['Square Meters'], ['Open Date'] = datetime_add('year', yearOffset, ['Open Date']), ['Close Date'] = datetime_add('year', yearOffset, ['Close Date']), Status}
.ingest into table Data_CurrencyExchange (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_CurrencyExchange_1.parquet') with (format="parquet")
.ingest into table Data_Customer (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_Customer_1.parquet') with (format="parquet")
.ingest into table Data_Date (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_Date_1.parquet') with (format="parquet")
.ingest into table Data_GeoLocations (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_GeoLocations_1.parquet') with (format="parquet")
.ingest into table Data_OrderRows (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_OrderRows_1.parquet') with (format="parquet")
.ingest into table Data_Orders (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_Orders_1.parquet') with (format="parquet")
.ingest into table Data_Product (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_Product_1.parquet') with (format="parquet")
.ingest into table Data_Store (h'https://raw.githubusercontent.com/samaguire/Sqlbi-Contoso-As-Parquet/main/Contoso%2010K/Data_Store_1.parquet') with (format="parquet")